海事数据的格式
标签:
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 = 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课程基础上做出改动