In [1]:
# ecoding = utf8
import networkx as nx
import pandas as pd
import re
from operator import itemgetter, attrgetter
import matplotlib.pyplot as plt
from collections import defaultdict
import itertools
import numpy as np
import random
from tools import *
db = MySql(db='LOCAL', db_name='ezhouhub')
DOC_DICT = {
'sf_eq': "E:\\sf-doc\\鄂州机场项目\E\\方案设备数据\\i_equipment_name_rule.xlsx",
'self_eq': "D:\doc\sf-doc\doc.sf\ezhouhub\equipmnet_param\新版设备命名规则20171115.xlsx"
}
In [2]:
queue_io_df = pd.read_sql_table(table_name='i_queue_io_default',
con=db.sql_con,
columns=['equipment_port_last',
'equipment_port_next',
'equipment_type',
'queue_id', 'parcel_type', 'src_type', 'dest_type'])
# -----------Get Queue Data--------------------
all_queue_data = queue_io_df
small_queue_data = queue_io_df.loc[queue_io_df.parcel_type.isin(['small']),:]
parcel_queue_data = queue_io_df.loc[queue_io_df.parcel_type.isin(['parcel']),:]
# -----------Digraph and Nodes Generate--------
di_graph = nx.DiGraph()
In [3]:
all_graphs = nx.from_pandas_dataframe(df=queue_io_df,
source='equipment_port_last',
target='equipment_port_next',
create_using=di_graph)
all_nodes = list({x for x in map(lambda x : re.split(pattern=r'[0-9]', string=x)[0], all_graphs.nodes())})
print(f"all_nodes length:{len(all_nodes)}\n{sorted(all_nodes, key=itemgetter(0,-1))}")
In [4]:
small_graphs = nx.from_pandas_dataframe(df=small_queue_data,
source='equipment_port_last',
target='equipment_port_next',
create_using=di_graph)
small_nodes = list({x for x in map(lambda x : re.split(pattern=r'[0-9]', string=x)[0], small_graphs.nodes())})
print(f"small_nodes length:{len(small_nodes)}\n{sorted(small_nodes, key=itemgetter(0,-1))}")
In [5]:
parcel_graphs = nx.from_pandas_dataframe(df=parcel_queue_data,
source='equipment_port_last',
target='equipment_port_next',
create_using=di_graph)
parcel_nodes = list({x for x in map(lambda x : re.split(pattern=r'[0-9]', string=x)[0], parcel_graphs.nodes())})
print(f"parcel_nodes lenght:{len(parcel_nodes)}\n{sorted(parcel_nodes, key=itemgetter(0,-1))}")
In [6]:
def _one_map_reg(df, col, reg):
return df[col].map(lambda x : True if reg.search(x)is not None else False)
def reg_map(df, colums, reg):
if len(colums):
filter_list = _one_map_reg(df, colums[0], reg)
for col in colums[1:]:
filter_list = filter_list | _one_map_reg(df, col, reg)
return filter_list
reg_nodes = ['equipment_port_next']
In [7]:
# 小件预分拣滑槽
reg_spc = re.compile("^c[idr]b\w*") #['cib', 'crb', 'cdb']
# 小件终分拣滑槽
reg_ssc = re.compile("^c[idr]m\w*")
# 小件导入台
reg_u = re.compile("^u[idr]\w*")
# 小件终分拣导入口
reg_im = re.compile("i[idr]m\w*")
# 小件内部汇流点 30-45 16个国内小件初分拣至终分拣汇流点
# ------------------------------------------------------------!!!应将cdb1-cdb16 换成cdm1-cdm16 链接至x68-x83----！！！
# x68-x83 小件终分拣滑槽至16个汇流点. queue_io 表中错误将cdb1-cdb16的4800个出口链接到x68-x83
reg_x = re.compile("^x(3[0-9]|4[0-5])$")
# ---------------------------------------------------------------小件分拣机外围的x点---------------------------------
# 海关入口至国内拆包台汇流点 gi1-gi10 -> x20-x29 -> ud1-ud10
# 国际小件滑槽至国际预分入口汇流点 cim1->x46->iip7_10
# 路侧小件滑槽至路侧预分拣入口汇流点 crm->x57->irp
# ----------------------------------------------------------错误！！！！------------------
# queue 内存在异常边界：(cip3_23, x3), (cip3_24, x3) 国际预分拣滑槽流至x3，导致小件分拣条数多2条
# 正确的路径为：国际预分拣滑槽至国际终分拣导入 应全部由x1链接 cip-> x1->iis-------------！！！！！
reg_x_out = re.compile("^x(3|2[0-9]|46|57|6[8-9]|7[0-9]|8[0-3])$")
# --------------------------------------------------all small queue---------------
all_small_reg = (reg_map(all_queue_data, reg_nodes, reg_spc) |
reg_map(all_queue_data, reg_nodes, reg_ssc) |
reg_map(all_queue_data, reg_nodes, reg_u) |
reg_map(all_queue_data, reg_nodes, reg_x) |
reg_map(all_queue_data, reg_nodes, reg_im) |
reg_map(all_queue_data, reg_nodes, reg_x_out))
queue_io_small_sample = all_queue_data.loc[all_small_reg, :]
queue_io_small_sample.index.size
Out[7]:
In [8]:
# parcel unload
unload_node_col = ['equipment_port_last']
reg_unload = re.compile("^(a[id]|r)\w*") #['ai', 'ad 'r']
# parcel primary input
reg_parcel_primary_i = re.compile("^i[idr]p\w*")
# parcel primary output c
reg_parcel_primary_c = re.compile("^c[idr]p\w*")
# parcel secondary input
reg_parcel_secondary_i = re.compile("^i[idr]s\w*")
# parcel secondary output
# ----------------------------------------------------!!!!错误---路侧的终分拣滑槽错误的弄成cis, 应为crs
reg_parcel_secondary_c = re.compile("^c[idr]s\w*")
# parcel custom
reg_custom = re.compile("^g[ie]\w*")
# parcel cross ---------------------------------------!!!!错误cip-> (x1-x10) ->iis 应该为cip->x1->iis
# 剔除x3的行数，少算2行数据：cip3_23->x3, cip3_24->x3
reg_parcel_cross = re.compile("^x([1-24-9]|1[0-9]|4[7-9]|5[0-6])$")
all_aprcel_reg = (reg_map(all_queue_data, reg_nodes, reg_parcel_primary_i)|
reg_map(all_queue_data, reg_nodes, reg_parcel_primary_c)|
reg_map(all_queue_data, reg_nodes, reg_parcel_secondary_i)|
reg_map(all_queue_data, reg_nodes, reg_parcel_secondary_c)|
reg_map(all_queue_data, reg_nodes, reg_custom)|
reg_map(all_queue_data, reg_nodes, reg_parcel_cross))
queue_io_parcel_sample = all_queue_data.loc[all_aprcel_reg, :]
queue_io_parcel_sample.index.size
# (queue_io_parcel_sample.loc[queue_io_parcel_sample.equipment_port_next.str.startswith('x'),:].index.size,
# set(queue_io_parcel_sample.loc[queue_io_parcel_sample.equipment_port_next.str.startswith('x'),:
# ].equipment_port_last.apply(lambda x : re.split(pattern=r'[0-9]', string=x)[0])))
Out[8]:
In [9]:
repair_queue_io_data = all_queue_data.copy()
# #---------------------------------------小件cdm 错误的搞成 cdb-------------------------
reg_err_cdb_cdm = re.compile("^x(6[8-9]|7[0-9]|8[0-3])$")
repair_queue_io_data.loc[reg_map(repair_queue_io_data, reg_nodes, reg_err_cdb_cdm), ['equipment_port_last']] = repair_queue_io_data.loc[
reg_map(repair_queue_io_data, reg_nodes, reg_err_cdb_cdm), ['equipment_port_last']].equipment_port_last.str.replace('cdb', 'cdm')
# #---------------------------------------将cip-x的节点全部替换为x1----------------------
_reg_cip_x_filter = re.compile("^cip\w*")
_reg_cip_x = reg_map(repair_queue_io_data, ['equipment_port_last'], _reg_cip_x_filter) & repair_queue_io_data.equipment_type.isin(['x'])
repair_queue_io_data.loc[_reg_cip_x, ['equipment_port_next']] = 'x1'
# #--------------------------------------将x-iis的所有x节点替换为x1----------------------
_reg_err_x1_iis_filter = re.compile("^iis\w*")
_reg_err_x1_iis = reg_map(repair_queue_io_data, ['equipment_port_next'], _reg_err_x1_iis_filter)
# 修改x-iis 的点
repair_queue_io_data.loc[_reg_err_x1_iis, ['equipment_port_last']] = 'x1'
# 修改队列id
repair_queue_io_data.loc[_reg_err_x1_iis, ['queue_id']] = repair_queue_io_data.loc[
_reg_err_x1_iis, :].queue_id.str.replace(re.compile("x(10|[0-9])"), "x1")
# #--------------------------------------将gi-x[47,48,49,50,51]-irs汇流点全部换成 x47----------
_reg_err_gi_x_filter = re.compile("^x(4[7-9]|5[0-1])$")
_reg_err_gi_x = reg_map(repair_queue_io_data, ['equipment_port_next'], _reg_err_gi_x_filter)
# gi-x替换47-51 为 47
repair_queue_io_data.loc[_reg_err_gi_x, ['equipment_port_next']] = 'x47'
# x-irs 替换47-51 为 47
_reg_err_x_irs_filter = re.compile("^x(4[7-9]|5[0-1])$")
_reg_err_x_irs = reg_map(repair_queue_io_data, ['equipment_port_last'], _reg_err_x_irs_filter)
repair_queue_io_data.loc[_reg_err_x_irs, ['equipment_port_last']] = 'x47'
# #--------------------------------------将irs至crs错误弄成cis的 换成crs-----------------
_reg_err_iis_crs_filter = re.compile("^irs\w*")
_reg_err_iis_crs = reg_map(repair_queue_io_data, ['equipment_port_last'], _reg_err_iis_crs_filter)
repair_queue_io_data.loc[_reg_err_iis_crs, ['equipment_port_next']] = repair_queue_io_data.loc[
_reg_err_iis_crs, :].equipment_port_next.str.replace('cis', 'crs')
# repair_queue_io_data.loc[_reg_err_x_irs, :] #.queue_id #.str.replace(re.compile("x(4[7-9]|5[0-1])"), "x47")
In [10]:
repair_queue_io_data.loc[_reg_err_iis_crs, :].tail(2) #.equipment_port_next.apply(lambda x: x[0:4]).tolist()
Out[10]:
In [11]:
def get_all_simple_paths(base_graphs, source_nodes, target_nodes):
simple_paths = defaultdict(list)
for k,v in map(lambda x : (x, nx.all_simple_paths(base_graphs, *x)), itertools.product(source_nodes, target_nodes)):
simple_paths[k]=v
return simple_paths
def get_path(paths):
yield from paths
def get_key_path(paths, key):
return base_paths[key]
re_all_graphs = nx.from_pandas_dataframe(df=repair_queue_io_data,
source='equipment_port_last',
target='equipment_port_next',
create_using=di_graph)
# #---------------------------------parcel 基础路由规划-------------------
# ----------------------------剔除：{u, c[idr]b, x, i[idr]m}--------------
# u nodes
small_u_nodes = [i for i in re_all_graphs.nodes() if reg_u.search(i) is not None]
# x30-x45 nodes
small_inner_x_nodes = [i for i in re_all_graphs.nodes() if reg_x.search(i) is not None]
# c[idr]b nodes
small_presort_c_nodes = [i for i in re_all_graphs.nodes() if reg_spc.search(i) is not None]
# i[idr]m nodes
small_secondary_i_nodes = [i for i in re_all_graphs.nodes() if reg_im.search(i) is not None]
# ---------------------------卸货区， 装货区nodes ------------------------
unload_nodes = [i for i in re_all_graphs.nodes() if reg_unload.search(i) is not None]
reload_nodes = [i for i in re_all_graphs.nodes() if reg_parcel_secondary_c.search(i) is not None]
base_graphs = re_all_graphs.subgraph(
list(set(re_all_graphs.nodes())-
set(small_u_nodes)-
set(small_presort_c_nodes)-
set(small_inner_x_nodes)-
set(small_secondary_i_nodes)))
In [91]:
reg_unload_i = re.compile("^ai\w*")
unload_i_nodes = [i for i in re_all_graphs.nodes() if reg_unload_i.search(i) is not None]
reg_parcel_secondary_c_r = re.compile("^crs\w*")
reload_r_nodes = [i for i in re_all_graphs.nodes() if reg_parcel_secondary_c_r.search(i) is not None]
base_paths = get_all_simple_paths(base_graphs, unload_i_nodes, reload_r_nodes)
# df = pd.DataFrame
path_data = []
keys_array = []
count = 0
for k, v in base_paths.items():
count += 1
now_data = list(v)
path_data.extend(now_data)
keys_array.extend([str(k)]*len(now_data))
if count >=10:
break
df=pd.DataFrame(data=path_data, index=keys_array)
df = df.reset_index()
df_to_excel(df=df, file_dir="D:\\Git\\git.jupyter\\jupyter_app\\ipython\\py36_erzhou_input\\data\\path\\", file_name='base_path.xlsx')
In [92]:
# print(len(keys_array))
len(base_paths.keys())
Out[92]:
In [126]:
G = nx.from_pandas_dataframe(repair_queue_io_data.head(10), 'equipment_port_last', 'equipment_port_next', create_using=nx.DiGraph())
# node_df = pd.DataFrame(data=G.nodes())
# node_df = node_df.rename_axis(mapper={0: 'node'}, axis=1)
# node_df = node_df.node.apply(lambda x: x.split('_')[0]).to_frame()
# node_df.node.str.replace('[0-9]*?$', '')
# sorted(node_df.loc[node_df.node.str.startswith('x'),:].node.tolist())
# nx.draw(G)
# plt.show()
ai_nodes_str, crs_nodes_str
random.randint(1,7)
Out[126]:
In [15]:
df_aq_type = pd.read_excel(io=DOC_DICT['self_eq'], )
df_aq_type.loc[df_aq_type.equipment_id.isin(['ui', 'ud', 'ur', 'cib']), :]
Out[15]:
In [199]:
small_pc_re = re.compile("^c[idr]b\w*$")
xx = True if small_pc_re.search('cib7_8_2') is not None else False
xx
Out[199]:
In [33]:
a = {'a':[2,3], 'b': [3,4]}
mm = map(lambda x : x , a.values())
list(mm)
Out[33]: