In [1]:
from dist_time import *
import pandas as pd
from collections.abc import Iterator, Iterable, Generator
from collections import defaultdict
import re
from sqlalchemy import create_engine
from sqlalchemy.types import Integer, Text, DateTime, Float, VARCHAR
from sqlalchemy import Table, MetaData, Column
import itertools as it
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'}
UNLOAD_MAP = {'D': 'a', 'I': 'a', 'R': 'r'}
UNLOAD_AREA_MAP = {
'R_Unload1':'R','R_Unload2':'R','R_Unload3':'R','Unload1':'D','Unload2':'D','Unload3':'D',
'Unload4':'D','Unload5':'D','Unload6':'D','Unload7':'D','Unload8':'D','Unload9':'D','Unload10':'D',
'Unload11':'D','Unload12':'D','Unload13':'D','Unload14':'D','Unload22':'D','Unload23':'D',
'Unload15':'I','Unload16':'I','Unload17':'I','Unload18':'I','Unload19':'I','Unload20':'I','Unload21':'I'}
RELOAD_AREA_MAP = {
'Reload30': 'R', 'Reload31': 'R', 'Reload32': 'R', 'Reload33': 'R', 'Reload34': 'R', 'Reload1': 'D', 'Reload2': 'D',
'Reload3': 'D', 'Reload4': 'D', 'Reload5': 'D', 'Reload6': 'D', 'Reload7': 'D', 'Reload8': 'D', 'Reload9': 'D',
'Reload10': 'D', 'Reload11': 'D', 'Reload12': 'D', 'Reload13': 'D', 'Reload14': 'D', 'Reload15': 'D', 'Reload29': 'D',
'Reload16': 'I', 'Reload17': 'I', 'Reload18': 'I', 'Reload19': 'I', 'Reload20': 'I', 'Reload21': 'I', 'Reload22': 'I',
'Reload23': 'I', 'Reload24': 'I', 'Reload25': 'I', 'Reload26': 'I', 'Reload27': 'I', 'Reload28': 'I'}
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_machine_distance_table",
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("source_type", VARCHAR(length=32, )),
Column("process_time", Float(precision=2)),
Column("parameter_name", VARCHAR(length=32, )),
)
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(sheet, df):
file_name = "d:/Git/git.jupyter/jupyter_app/ipython/py36_erzhou_input/dist_time/out/outdata.xlsx"
df.to_excel(file_name, sheet_name=sheet, index=False)
v = R_Dock_dist.values()
k = R_Dock_dist.keys()
dict_list = {
'UU': [Unload_dist],
'NC': [NC_dist],
'R': [R_Dock_R_PreSort_dist,R_PreSort_R_Secondary_dist,R_PreSort_D_Primary_dist,R_PreSort_S_Primary_dist ,R_PreSort_I_Export_dist],
'D': [D_Dock_D_Primary_dist, D_Dock_S_Primary_dist, D_Primary_D_Secondary_dist, D_Primary_I_Export_dist, D_Primary_R_Secondary_dist],
'S': [S_Primary_S_Secondary_dist, S_Secondary_D_Primary_dist, S_Secondary_I_PreSort_dist],
'I': [I_Dock_I_PreSort_dist, I_PreSort_I_Secondary_dist, I_Export_I_Secondary_dist, I_PreSort_S_Secondary_dist,
I_PreSort_S_Primary_dist, I_PreSort_D_Primary_dist, I_PreSort_R_Secondary_dist]
}
In [2]:
nc_df = dict_to_dataframe(pd, parameter_list=dict_list['NC'])
nc_df['process_time'] = nc_df.process_time.apply(lambda x : '{:.1f}'.format(x/1000))
nc_df['source_type'] ='NC'
nc_df['equipment_port_last'], nc_df['equipment_port_next'] = nc_df.parameter_name.str.split('-', 1).str
nc_df['equipment_area_last'] = nc_df.equipment_port_last.apply(lambda x : UNLOAD_AREA_MAP[x])
nc_df['equipment_area_next'] = nc_df.equipment_port_next.apply(lambda x : RELOAD_AREA_MAP[x])
nc_df.equipment_port_last = nc_df.equipment_port_last.apply(lambda x : x.replace(re.sub('\d+','',x), UNLOAD_MAP[UNLOAD_AREA_MAP[x]]))
nc_df.equipment_port_next = nc_df.equipment_port_next.apply(lambda x : x.replace(re.sub('\d+','',x), MACHINE_MAP[re.sub('\d+','',x)]))
nc_df = nc_df.loc[:,['equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'source_type',
'process_time','parameter_name'
]]
nc_df = nc_df.sort_values(['equipment_port_last'])
nc_df = nc_df.reset_index(drop=True)
nc_df.sample(10)
Out[2]:
In [3]:
r_df = dict_to_dataframe(pd, parameter_list=dict_list['R'])
r_df['process_time'] = r_df.process_time.apply(lambda x : '{:.1f}'.format(x/1000))
r_df['equipment_port_last'], r_df['equipment_port_next'] = r_df.parameter_name.str.split('-', 1).str
r_df['equipment_area_last'], r_df['equipment_port_last'] = r_df.equipment_port_last.str.split('_', 1).str
r_df['equipment_area_next'], r_df['equipment_port_next'] = r_df.equipment_port_next.str.split('_', 1).str
r_df.equipment_port_last = r_df.equipment_port_last.apply(lambda x : x.replace(re.sub('\d+','',x), MACHINE_MAP[re.sub('\d+','',x)]))
r_df.equipment_port_next = r_df.equipment_port_next.apply(lambda x : x.replace(re.sub('\d+','',x), MACHINE_MAP[re.sub('\d+','',x)]))
r_df['source_type'] = 'R'
r_df = r_df.loc[:,['equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'source_type',
'process_time', 'parameter_name'
]]
r_df.sample(10)
Out[3]:
In [4]:
d_df = dict_to_dataframe(pd, parameter_list=dict_list['D'])
d_df['process_time'] = d_df.process_time.apply(lambda x : '{:.1f}'.format(x/1000))
d_df['equipment_port_last'], d_df['equipment_port_next'] = d_df.parameter_name.str.split('-', 1).str
# add D_ flag to unload area
d_df.equipment_port_last = d_df.equipment_port_last.where(d_df.equipment_port_last.str.startswith('D_'),
'D_'+d_df.equipment_port_last)
d_df['equipment_area_last'], d_df['equipment_port_last'] = d_df.equipment_port_last.str.split('_', 1).str
d_df['equipment_area_next'], d_df['equipment_port_next'] = d_df.equipment_port_next.str.split('_', 1).str
d_df.equipment_port_last = d_df.equipment_port_last.apply(lambda x : x.replace(re.sub('\d+','',x), MACHINE_MAP_ID[re.sub('\d+','',x)]))
d_df.equipment_port_next = d_df.equipment_port_next.apply(lambda x : x.replace(re.sub('\d+','',x), MACHINE_MAP_ID[re.sub('\d+','',x)]))
d_df['source_type'] = 'D'
d_df = d_df.loc[:,['equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'source_type',
'process_time', 'parameter_name'
]]
d_df.sample(10)
Out[4]:
In [5]:
s_df = dict_to_dataframe(pd, parameter_list=dict_list['S'])
s_df['process_time'] = s_df.process_time.apply(lambda x : '{:.1f}'.format(x/1000))
s_df['equipment_port_last'], s_df['equipment_port_next'] = s_df.parameter_name.str.split('-', 1).str
# check if hav D_ flag else add D_
s_df.equipment_port_last = s_df.equipment_port_last.where(s_df.equipment_port_last.str.startswith('S_'),
'S_'+s_df.equipment_port_last)
s_df['equipment_area_last'], s_df['equipment_port_last'] = s_df.equipment_port_last.str.split('_', 1).str
s_df['equipment_area_next'], s_df['equipment_port_next'] = s_df.equipment_port_next.str.split('_', 1).str
s_df.equipment_port_last = s_df.equipment_port_last.apply(lambda x : x.replace(re.sub('\d+','',x), MACHINE_MAP_ID[re.sub('\d+','',x)]))
s_df.equipment_port_next = s_df.equipment_port_next.apply(lambda x : x.replace(re.sub('\d+','',x), MACHINE_MAP_ID[re.sub('\d+','',x)]))
s_df['source_type'] = 'S'
s_df = s_df.loc[:,['equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'source_type',
'process_time', 'parameter_name'
]]
s_df.head(10)
Out[5]:
In [6]:
i_df = dict_to_dataframe(pd, parameter_list=dict_list['I'])
i_df['source_type'] = 'I'
i_df['process_time'] = i_df.process_time.apply(lambda x : '{:.1f}'.format(x/1000))
i_df['equipment_port_last'], i_df['equipment_port_next'] = i_df.parameter_name.str.split('-', 1).str
# check if hav D_ flag else add D_
i_df.equipment_port_last = i_df.equipment_port_last.where(i_df.equipment_port_last.str.startswith('I_'),
'I_'+i_df.equipment_port_last)
i_df['equipment_area_last'], i_df['equipment_port_last'] = i_df.equipment_port_last.str.split('_', 1).str
i_df['equipment_area_next'], i_df['equipment_port_next'] = i_df.equipment_port_next.str.split('_', 1).str
i_df.equipment_port_last = i_df.equipment_port_last.apply(lambda x : x.replace(re.sub('\d+','',x), MACHINE_MAP_ID[re.sub('\d+','',x)]))
i_df.equipment_port_next = i_df.equipment_port_next.apply(lambda x : x.replace(re.sub('\d+','',x), MACHINE_MAP_ID[re.sub('\d+','',x)]))
i_df = i_df.loc[:,['equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'source_type',
'process_time', 'parameter_name'
]]
i_df.head(10)
Out[6]:
In [7]:
uu_df = dict_to_dataframe(pd, parameter_list=dict_list['UU'])
uu_df['source_type'] = 'AA'
uu_df['equipment_port_last'], uu_df['equipment_port_next'] = uu_df.parameter_name.str.split('-', 1).str
uu_df['equipment_area_last'] = uu_df.equipment_port_last.apply(lambda x : UNLOAD_AREA_MAP[x])
uu_df['equipment_area_next'] = uu_df.equipment_port_next.apply(lambda x : UNLOAD_AREA_MAP[x])
uu_df.equipment_port_last = uu_df.equipment_port_last.apply(lambda x : x.replace(re.sub('\d+','',x), UNLOAD_MAP[UNLOAD_AREA_MAP[x]]))
uu_df.equipment_port_next = uu_df.equipment_port_next.apply(lambda x : x.replace(re.sub('\d+','',x), UNLOAD_MAP[UNLOAD_AREA_MAP[x]]))
uu_df = uu_df.loc[:,['equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'source_type',
'process_time','parameter_name'
]]
uu_df = uu_df.sort_values(['equipment_port_last', 'equipment_port_next']).reset_index(drop=True)
uu_df.head(10)
Out[7]:
In [9]:
dist_concat = pd.concat([nc_df, r_df, d_df, s_df, i_df, uu_df], axis=0)
dist_concat = dist_concat.loc[:, ['equipment_port_last', 'equipment_port_next', 'equipment_area_last', 'equipment_area_next', 'source_type',
'process_time','parameter_name']]
# dist_concat[dist_concat.equipment_port_next.str.startswith('RE')]
dist_concat.head(10)
Out[9]: