<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 时间距离统计dist.py
Step1:  NC件
NC_dist
Step2:  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
Step3:  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
Step4:  s类
S_Primary_S_Secondary_dist, S_Secondary_D_Primary_dist, S_Secondary_I_PreSort_dist
Step5:  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
Step6:  U-U类
Step7:  concat
nc_df,r_df,d_df,s_df,i_df,uu_df
<ASSISTANT_TASK:>
Python Code:
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]
}
Explanation: 时间距离统计dist.py
End of explanation
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)
Explanation: NC件
NC_dist
End of explanation
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)
Explanation: 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
End of explanation
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)
Explanation: 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
End of explanation
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)
Explanation: s类
S_Primary_S_Secondary_dist, S_Secondary_D_Primary_dist, S_Secondary_I_PreSort_dist
End of explanation
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)
Explanation: 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
End of explanation
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)
Explanation: U-U类
End of explanation
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)
Explanation: concat
nc_df,r_df,d_df,s_df,i_df,uu_df
End of explanation
<END_TASK>