实战：使用py2neo和pandas处理海事数据

海事数据的格式

标签：
ship_ShipName,ship_MMSI,ship_BuildDate,ship_ShipTypeGroup,ship_ShipTypeLevel5SubGroup,ship_ShipType,ship_GroupCompany,ship_GroupCompanyCountry,ship_OperatorCompany,ship_OperatorCompanyCountry,ship_CountryOfEconomicBenefit,ship_DeadWeight,ship_GrossTonnage,ship_LengthLOA,ship_MouldWidth,ship_Draught,ship_LiquidCapacity

首先使用excel根据标签中的如下五个类别创建三元组形成ships.csv文件：

1. ship_GroupCompany,
2. ship_GroupCompanyCountry,
3. ship_OperatorCompany,
4. ship_OperatorCompanyCountry,
5. ship_CountryOfEconomicBenefit

ships.csv文件的格式如下图：

有了三元组和原表格我们可以很方便添加数据到neo4j的数据库中。

函数

1. createNode：在neo4j中创建节点
2. createRelationship：在neo4j中创建节点的关系
3. matchNode：在neo4j中匹配数据
4. get_ship_properties：得到所有船的属性
5. csv2df：将csv转为df
6. df2neo：将df（从df中提取的数据）转为neo4j

代码

from py2neo import *
import os
import pandas as pd
import numpy as np

# 数据库
graph = Graph('http://localhost:7474', username='neo4j', password='myneo4j')


# 创建节点
def createNode(m_graph, m_label, m_attrs):
    # m_n = "_.name=" + "\'" + m_attrs['name'] + "\'"
    m_n = None
    if m_label == 'SHIP':
        m_n = "_.MMSI=" + "\'" + m_attrs['MMSI'] + "\'"
    elif m_label == 'COMPANY' or m_label == 'COUNTRY/REGION':
        m_n = "_.Name=" + "\'" + m_attrs['Name'] + "\'"
    matcher = NodeMatcher(m_graph)
    re_value = matcher.match(m_label).where(m_n).first()
    print(re_value)
    if re_value is None:
        m_node = Node(m_label, **m_attrs)
        n = graph.create(m_node)
        return n
    # print('Fail to create Node!!')
    return None


# 创建两个节点的关系，如果节点不存在就不创建关系
def createRelationship(m_graph, m_label1, m_attrs1, m_label2, m_attrs2, m_r_name):
    reValue1 = matchNode(m_graph, m_label1, m_attrs1)
    reValue2 = matchNode(m_graph, m_label2, m_attrs2)
    if reValue1 is None or reValue2 is None:
        # print('reValue1: ', reValue1, 'reValue2: ', reValue2)
        # print('Fail to create relationship!!')
        return False
    m_r = Relationship(reValue1, m_r_name, reValue2)
    n = graph.create(m_r)
    return n


# 查询节点，按照ID查询，无返回None
# def matchNodeById(m_graph, m_id):
#    matcher = NodeMatcher(m_graph)
#    re_value = matcher.get(m_id)
#    return re_value


# 查询节点，按照name查询，无返回None
def matchNode(m_graph, m_label, m_attrs):
    # m_n = "_.name=" + "\'" + m_attrs['name'] + "\'"
    m_n = None
    if m_label == 'SHIP':
        m_n = "_.MMSI=" + "\'" + m_attrs['MMSI'] + "\'"
    elif m_label == 'COMPANY' or m_label == 'COUNTRY/REGION':
        m_n = "_.Name=" + "\'" + m_attrs['Name'] + "\'"
    # print(m_n)
    matcher = NodeMatcher(m_graph)
    re_value = matcher.match(m_label).where(m_n).first()
    return re_value


# 查询节点，按照标签查询，无返回None
# def matchNodeByLabel(m_graph, m_label):
#     matcher = NodeMatcher(m_graph)
#     re_value = matcher.match(m_label)
#     return re_value

# 从原本的表格中直接拿到数据并保存在字典中
def get_ship_properties(original_df):
    ships = {}
    for i in range(original_df.shape[0]):
        ship_properties = {'Name': str(original_df.loc[i, 'ship_ShipName']),
                           'MMSI': str(original_df.loc[i, 'ship_MMSI']),
                           'BuildDate': str(original_df.loc[i, 'ship_BuildDate']),
                           'ShipTypeGroup': str(original_df.loc[i, 'ship_ShipTypeGroup']),
                           'ShipTypeLevel5SubGroup': str(original_df.loc[i, 'ship_ShipTypeLevel5SubGroup']),
                           'ShipType': str(original_df.loc[i, 'ship_ShipType']),
                           'DeadWeight': str(original_df.loc[i, 'ship_DeadWeight']),
                           'GrossTonnage': str(original_df.loc[i, 'ship_GrossTonnage']),
                           'LengthLOA': str(original_df.loc[i, 'ship_LengthLOA']),
                           'MouldWidth': str(original_df.loc[i, 'ship_MouldWidth']),
                           'Draught': str(original_df.loc[i, 'ship_Draught']),
                           'LiquidCapacity': str(original_df.loc[i, 'ship_LiquidCapacity'])}
        ships[ship_properties['MMSI']] = ship_properties
    # print(ships)
    return ships

# csv转为df
def csv2df(file_name):
    df = pd.read_csv(file_name)
    # print(df.head())
    return df

# df转到neo4j的数据库中
def df2neo(df, ships):
    diff_group = df.groupby('relation')
    for relation, df in diff_group:
        head = None
        tail = None
        head_property = None
        tail_property = None
        # print('relation:', relation)
        df.reset_index(drop=True, inplace=True)
        # print(df)
        for i in range(df.shape[0]):
            if relation == 'GroupCompany' or relation == 'OperatorCompany':
                head = 'SHIP'
                head_property = ships[df.loc[i, 'head']]
                tail = 'COMPANY'
                tail_property = {'Name': df.loc[i, 'tail']}
            elif relation == 'CountryOfEconomicBenefit':
                head = 'SHIP'
                head_property = ships[df.loc[i, 'head']]
                tail = 'COUNTRY/REGION'
                tail_property = {'Name': df.loc[i, 'tail']}
            elif relation == 'GroupCompanyCountry' or 'CountryOfEconomicBenefit':
                head = 'COMPANY'
                head_property = {'Name': df.loc[i, 'head']}
                tail = 'COUNTRY/REGION'
                tail_property = {'Name': df.loc[i, 'tail']}
			
			# 本来是想对有单引号的数据进行处理，但发现很容易混乱，于是最后用excel把数据中的单引号全部替换掉了
            # head_property['Name'] = head_property['Name'].replace("'", '\\\'') 
            # tail_property['Name'] = tail_property['Name'].replace("'",'\\\'')
            
            if tail_property['Name'] == 'Unknown' or tail_property['Name'] in (None, '', np.nan):
                continue # 空数据不进行添加

            # print('head: ', head, ', head_property: ', head_property, ', tail: ', tail, ', tail_property',
            #       tail_property)
            createNode(graph, head, head_property)
            createNode(graph, tail, tail_property)
            createRelationship(graph, head, head_property, tail, tail_property, relation)


ship_df = csv2df('./ships.csv')
original_df = csv2df('./abc.csv')
ships = get_ship_properties(original_df)
df2neo(ship_df, ships)

# 以下代码是对createNode函数和createRelationship函数的测试！
# label1 = 'Stock'
# attrs1 = {'name': '招商银行', 'code': '600036'}
# label2 = 'SecuritiesExchange'
# attrs2 = {'name': '上海证券交易所'}
# # 1. 创建节点
# createNode(graph, label1, attrs1)
# createNode(graph, label2, attrs2)
# m_r_name = '证券交易所'
# # 2. 添加关系
# reValue = createRelationship(graph, label1, attrs1, label2, attrs2, m_r_name)

参考

部分代码在张曙光老师的bilibili的neo4j课程基础上做出改动