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

本文主要是介绍实战：使用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文件：

ship_GroupCompany,
ship_GroupCompanyCountry,
ship_OperatorCompany,
ship_OperatorCompanyCountry,
ship_CountryOfEconomicBenefit

ships.csv文件的格式如下图：
在这里插入图片描述
有了三元组和原表格我们可以很方便添加数据到neo4j的数据库中。

函数

createNode：在neo4j中创建节点
createRelationship：在neo4j中创建节点的关系
matchNode：在neo4j中匹配数据
get_ship_properties：得到所有船的属性
csv2df：将csv转为df
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 = Noneif 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 Falsem_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 = Noneif 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 = Nonetail = Nonehead_property = Nonetail_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)