In [56]:
from dist_time import *
import pandas as pd
from collections.abc import Iterator, Iterable, Generator
from collections import defaultdict
import re
import os
from sqlalchemy import create_engine
from sqlalchemy.types import Integer, Text, DateTime, Float, VARCHAR
from sqlalchemy import Table, MetaData, Column
import itertools as it
import pickle
MACHINE_MAP = {'PreSort': 'm', 'Unload': 'r', 'Secondary': 'c', 'Primary': 'm', 'Export':'e', 'Reload': 'RE'}
MACHINE_MAP_ID = {'PreSort': 'm', 'Unload': 'a', 'Secondary': 'c', 'Primary': 'm', 'Export':'e', 'Reload': 'RE'}
FILE_DEFAULT_PATH = "d:\\Git\\git.jupyter\\jupyter_app\\ipython\\py36_erzhou_input\\dist_time\\out"
class MySQLConfig:
HOST = '10.0.149.62'
USER = "root"
PASS = "root123"
DB = "cm_erzhou_v1_1"
CHARSET = 'utf8'
engine = create_engine(
f'mysql+pymysql://{USER}:{PASS}@{HOST}/{DB}?charset={CHARSET}',
isolation_level="READ UNCOMMITTED")
def creat_table():
db_eng = MetaData(bind=MySQLConfig.engine)
machine_table_sche = \
Table(
"i_queue_io",
db_eng,
Column("equipment_port_last", VARCHAR(length=32, )),
Column("equipment_port_next", VARCHAR(length=32, )),
Column("equipment_area_last", VARCHAR(length=32, )),
Column("equipment_area_next", VARCHAR(length=32, )),
Column("parameter_name", VARCHAR(length=64, )),
)
machine_table_sche.create(checkfirst=True)
def to_mysql(table_name: str, data: pd.DataFrame):
try:
data.to_sql(name=table_name,
con=MySQLConfig.engine,
if_exists='append',
index=0)
print(f"mysql write table {table_name} succeed!")
except Exception as exc:
print(f"mysql write table {table_name} failed, error: {exc}.")
raise Exception
def dict_to_dataframe(pd: pd, *, parameter_list: list=None):
data = defaultdict(list)
for paramer in parameter_list:
data['process_time'].extend(list(paramer.values()))
data ['parameter_name'].extend(list(paramer.keys()))
return pd.DataFrame(data)
def df_to_excel(file_name, sheet, df):
file = f"d:\\Git\\git.jupyter\\jupyter_app\\ipython\\py36_erzhou_input\\dist_time\\out\\{file_name}.xlsx"
df.to_excel(file, sheet_name=sheet, index=False)
def df_to_pickle(df, path_file=None, file_name: str='data', compression: str='infer'):
""""""
if path_file is None:
path_file = f"d:\\Git\\git.jupyter\\jupyter_app\\ipython\\py36_erzhou_input\\dist_time\\out\\{file_name}"
else:
path_file = os.path.join(path_file, file_name)
if compression is None:
df.to_pickle(path_file)
else:
df.to_pickle(path_file, compression)
return path_file
def check_dic_value(data: dict=None):
type_dic_value = set()
if data is None:
return
else:
for _,v in data.items():
if isinstance(v, list):
type_dic_value.add('L')
elif isinstance(v, str):
type_dic_value.add('S')
elif isinstance(v, int):
type_dic_value.add('I')
else:
raise ValueError('字典值类型不是字符或列表形式！')
if len(type_dic_value) !=1:
raise ValueError('字典值类型既有字符也有列表！')
return {type_dic_value.pop(): data}
def dict_list_to_dataframe(pd: pd.DataFrame, parameter_list: list=None):
if parameter_list:
re_list = []
for data in parameter_list:
# print(data[1])
ckeck_data = check_dic_value(data[0])
for k, _ in ckeck_data.items():
if k == 'L': # L 表示字典存储值类型为列表
re_data = it.chain(
*[i for i in map(
lambda x, y : [[x, v, data[1]] for v in y if isinstance(y, list)],
ckeck_data[k].keys(), ckeck_data[k].values())]
)
re_list.extend(re_data)
# return pd.DataFrame(list(re_data), columns=['equipment_port_last', 'equipment_port_next'])
elif k == 'S': # S表示字典存储值类型为字符串
re_data = [[k, v, data[1]] for k, v in ckeck_data[k].items()]
re_list.extend(re_data)
return pd.DataFrame(re_list, columns=['equipment_port_last', 'equipment_port_next', 'parameter_name'])
else:
return parameter_list
# def add_area_tag(data: dict=None, tag: str=''):
# if data:
# return {_: v for k, v in data}
In [2]:
QUEUE_IO_DICT = {
'R': [
(R_Dock_Unload, 'R_Dock_Unload'), # 卸货口Dock与卸货区对应关系
(R_Dock_R_PreSort_rule, 'R_Dock_R_PreSort_rule'), # 卸货区与初分拣机对应关系
(R_PreSort_infeed_rule, 'R_PreSort_infeed_rule'), # 初分拣机入口与初分拣机对应关系
(R_PreSort_D_Primary_rule, 'R_PreSort_D_Primary_rule'), # 初分拣机与国内初分拣机链接信息
(R_PreSort_S_Primary_rule, 'R_PreSort_S_Primary_rule'), # 初分拣机与小件初分拣机链接信息
(R_PreSort_I_Export_rule, 'R_PreSort_I_Export_rule'), # 初分拣机与海关机器入口链接信息
(R_Secondary_infeed_rule, 'R_Secondary_infeed_rule'), # 终分拣机入口信息
(R_Secondary_R_Reload_rule, 'R_Secondary_R_Reload_rule'), # 终分拣机出口与装货区出口对应信息
],
'D': [
(D_Dock_Unload, 'D_Dock_Unload'), # 卸货口Dock与卸货区对应关系
(D_Dock_D_Primary_rule, 'D_Dock_D_Primary_rule'), # 卸货区与初分拣机对应关系
(D_Dock_S_Primary_rule, 'D_Dock_S_Primary_rule'), # 初分拣机与小件初分拣机链接信息
(D_Primary_infeed_rule, 'D_Primary_infeed_rule'), # 初分拣机入口与初分拣机对应关系
(D_Secondary_infeed_rule, 'D_Secondary_infeed_rule'), # 终分拣机入口与终分拣机对应关系
(D_Secondary_D_Reload_rule, 'D_Secondary_D_Reload_rule'), # 终分拣机出口与装货区出口链接信息
],
'I': [
(I_Dock_Unload, 'I_Dock_Unload'), # 卸货口Dock与卸货区对应关系
(I_Dock_I_PreSort_rule, 'I_Dock_I_PreSort_rule'), # 卸货区与初分拣机对应关系
(I_PreSort_infeed_rule, 'I_PreSort_infeed_rule'), # 初分拣机入口与初分拣机对应关系
(I_PreSort_D_Primary_rule, 'I_PreSort_D_Primary_rule'), # 国际初分拣与国内初分拣机对应关系
(I_PreSort_S_Primary_rule, 'I_PreSort_S_Primary_rule'), # 国际初分拣与小件初分拣机对应关系
(I_Export_infeed_rule, 'I_Export_infeed_rule'), # 海关机入口与海关机器对应关系
(I_Export_I_Export_rule, 'I_Export_I_Export_rule'), # 海关区与海关机对应关系
(I_Secondary_infeed_rule, 'I_Secondary_infeed_rule'), # 终分拣机入口与终分拣机对应关系
(I_Secondary_I_Reload_rule, 'I_Secondary_I_Reload_rule') # 终分拣机出口与装货区出口链接信息
],
'S': [
(S_Secondary_infeed_rule, 'S_Secondary_infeed_rule'), # 小件终分拣机入口与小件终分拣机对应关系
(S_Secondary_D_Primary_rule, 'S_Secondary_D_Primary_rule'), # 小件终分拣机与国内初分拣机对应关系
(S_Secondary_I_PreSort_rule, 'S_Secondary_I_PreSort_rule'), # 小件终分拣机与国际初分拣机对应关系
(S_Secondary_S_Secondary_Pack_rule, 'S_Secondary_S_Secondary_Pack_rule'), # 小件终分拣出口与打包机对应关系
(S_Primary_infeed_rule, 'S_Primary_infeed_rule') # 小件初分拣机入口与小件初分拣机对应关系
]
}
QUEUE_RELOAD_DICT = {
'D': [
(R_Secondary_rule, 'R_PreSort_infeed_rule'),
(R_Reload_R_Secondary, 'R_PreSort_infeed_rule'),
]
}
In [3]:
r_queue_df = dict_list_to_dataframe(pd, parameter_list=QUEUE_IO_DICT['R'])
r_queue_df['equipment_area_last'], r_queue_df['equipment_port_last'] = r_queue_df.equipment_port_last.str.split('_', 1).str
r_queue_df.equipment_port_next = r_queue_df.equipment_port_next.where(
r_queue_df.equipment_port_next.str.contains('^[A-Z]_'),
'R_'+r_queue_df.equipment_port_next)
r_queue_df['equipment_area_next'], r_queue_df['equipment_port_next'] = r_queue_df.equipment_port_next.str.split('_', 1).str
r_queue_data = r_queue_df.loc[:,[
'equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'parameter_name']]
# r_queue_data.equipment_port_next
r_queue_data.head(10)
Out[3]:
In [4]:
d_queue_df = dict_list_to_dataframe(pd, parameter_list=QUEUE_IO_DICT['D'])
d_queue_df.equipment_port_last = d_queue_df.equipment_port_last.where(
d_queue_df.equipment_port_last.str.contains('^[A-Z]_'),
'D_'+d_queue_df.equipment_port_last)
d_queue_df['equipment_area_last'], d_queue_df['equipment_port_last'] = d_queue_df.equipment_port_last.str.split('_', 1).str
d_queue_df.equipment_port_next = d_queue_df.equipment_port_next.where(
d_queue_df.equipment_port_next.str.contains('^[A-Z]_'),
'D_'+d_queue_df.equipment_port_next)
d_queue_df['equipment_area_next'], d_queue_df['equipment_port_next'] = d_queue_df.equipment_port_next.str.split('_', 1).str
d_queue_data = d_queue_df.loc[:,[
'equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'parameter_name']]
d_queue_data.sample(10)
Out[4]:
In [5]:
i_queue_df = dict_list_to_dataframe(pd, parameter_list=QUEUE_IO_DICT['I'])
i_queue_df.equipment_port_last = i_queue_df.equipment_port_last.where(
i_queue_df.equipment_port_last.str.contains('^[A-Z]_'),
'I_'+i_queue_df.equipment_port_last)
i_queue_df['equipment_area_last'], i_queue_df['equipment_port_last'] = i_queue_df.equipment_port_last.str.split('_', 1).str
i_queue_df.equipment_port_next = i_queue_df.equipment_port_next.where(
i_queue_df.equipment_port_next.str.contains('^[A-Z]_'),
'I_'+i_queue_df.equipment_port_next)
i_queue_df['equipment_area_next'], i_queue_df['equipment_port_next'] = i_queue_df.equipment_port_next.str.split('_', 1).str
i_queue_data = i_queue_df.loc[:,[
'equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'parameter_name']]
i_queue_data.sample(10)
# i_queue_df[i_queue_df.equipment_port_last=='I_Dock72']#[70:]
Out[5]:
In [6]:
s_queue_df = dict_list_to_dataframe(pd, parameter_list=QUEUE_IO_DICT['S'])
s_queue_df.equipment_port_last = s_queue_df.equipment_port_last.where(
s_queue_df.equipment_port_last.str.contains('^[A-Z]_'),
'S_'+s_queue_df.equipment_port_last)
s_queue_df['equipment_area_last'], s_queue_df['equipment_port_last'] = s_queue_df.equipment_port_last.str.split('_', 1).str
s_queue_df.equipment_port_next = s_queue_df.equipment_port_next.where(
s_queue_df.equipment_port_next.str.contains('^[A-Z]_'),
'S_'+s_queue_df.equipment_port_next)
s_queue_df['equipment_area_next'], s_queue_df['equipment_port_next'] = s_queue_df.equipment_port_next.str.split('_', 1).str
s_queue_data = s_queue_df.loc[:,[
'equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'parameter_name']]
s_queue_data.sample(10) # [s_queue_data.equipment_port_next.str.contains('Secondary16_Pack100')]
Out[6]:
In [55]:
queue_concat = pd.concat([r_queue_data, d_queue_data, i_queue_data, s_queue_data], axis=0)
path_pkl = df_to_pickle(
queue_concat,
# path_file = FILE_DEFAULT_PATH,
file_name='i_queue_io.xz',
compression='xz'
)
df_pkl = pd.read_pickle(
path=path_pkl,
compression='xz'
)
df_pkl.sample(10)
Out[55]:
In [40]:
q_secondary_out = queue_concat[
queue_concat.equipment_port_next.str.startswith('Unload') & queue_concat.parameter_name.str.endswith('Dock_Unload')
# & queue_concat.equipment_area_next.str.startswith('I') & queue_concat.equipment_area_last.str.startswith('I')
]
# q_unload.parameter_name.str.contains
# q_secondary_out['equipment_id'], _ = q_secondary_out.equipment_port_last.str.split('_ot', 1).str
# q_secondary_out = q_secondary_out.loc[:,[
# 'equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'parameter_name']]
# i_t_group = q_secondary_out.groupby(
# ['equipment_id', 'equipment_area_last']).equipment_port_last.apply(set).apply(len).to_frame().reset_index()
# df_to_excel(file_name='unload_set', sheet='unload_set', df=q_secondary_out)
# q_secondary_out
# i_t_group #.sort_values([ 'equipment_port_next'])