新版stdf处理

import pandas as pd
import time

read_file = 'stdf原始(1).xlsx'

color_out_file = 'Color_V2.xlsx'
devices_out_file = 'Devices_V2.xlsx'
fixed_out_file = 'Devices_Fixed_Item_V2.xlsx'
other_out_file = 'Devices_Other_Item_V2.xlsx'
parameter_out_file = 'Parameter_V2.xlsx'


def process_to_color():
    df_mir = pd.read_excel(read_file, sheet_name='MIR')
    df_sbr = pd.read_excel(read_file, sheet_name='SBR')
    # print(df_mir.columns)
    part_type = df_mir['RART_TYP'].values[0]
    process = df_mir['TEST_COD'].values[0]
    test_program = df_mir['SPEC_NAM'].values[0]
    color = ''
    to_datas = []
    sbin_names = []
    for idx, rows in df_sbr.iterrows():
        sbin_num = rows['SBIN_NUM']
        sbin_nam = rows['SBIN_NAM']
        if sbin_nam in sbin_names:
            continue
        sbin_names.append(sbin_nam)
        project_type = 1
        to_line = [sbin_num, sbin_nam, project_type, part_type, process, test_program, color]
        to_datas.append(to_line)
    columns = ['SOFT_BIN', 'BIN_NAME', 'PROJECT_TYPE', 'PART_NO', 'PROCESS', 'TEST_PROGRAM',
               'COLOR']
    out_df = pd.DataFrame(to_datas, columns=columns)
    out_df.to_excel(color_out_file, index=None)


def process_devices():
    df_mir = pd.read_excel(read_file, sheet_name='MIR')
    df_prr = pd.read_excel(read_file, sheet_name='PRR')
    df_wir = pd.read_excel(read_file, sheet_name='WIR', converters={'WAFER_ID': str})

    to_datas = []
    lot_id = df_mir['LOT_ID'].values[0]
    wafer_id = df_wir['WAFER_ID'].values[0]

    flow_id = df_mir['FLOW_ID'].values[0]
    sblot_id = df_mir['SBLOT_ID'].values[0]

    program = df_mir['SPEC_NAM'].values[0]
    ending_time = df_mir['START_T'].values[0]
    time_array = time.localtime(ending_time)
    ending_time = time.strftime("%Y-%m-%d %H:%M:%S", time_array)
    if 'P1' in flow_id:
        retest = 0
    else:
        retest = flow_id.strip('R')
    for idx, rows in df_prr.iterrows():
        locate_x = rows['X_COORD']
        locate_y = rows['Y_COORD']
        t_time = rows['TEST_T']
        soft_bin = rows['SOFT_BIN']
        hard_bin = rows['HARD_BIN']
        part_id = rows['PART_ID']
        site_num = rows['SITE_NUM']

        to_line = [lot_id + '#' + str(wafer_id), locate_x, locate_y, retest, t_time, soft_bin, hard_bin, sblot_id,
                   part_id, site_num, program, ending_time]
        to_datas.append(to_line)
    columns = ['Wafer_id', 'Locate_X', 'Locate_Y', 'Retest', 'T_Time', 'Soft_Bin', 'Hard_Bin', 'lot_id',
               'part_id', 'site_num', 'Program', 'Ending_Time']
    out_df = pd.DataFrame(to_datas, columns=columns)
    out_df.to_excel(devices_out_file, index=None)


def process_to_fixed():
    df_mir = pd.read_excel(read_file, sheet_name='MIR')
    df_wrr = pd.read_excel(read_file, sheet_name='WRR')
    df_wir = pd.read_excel(read_file, sheet_name='WIR', converters={'WAFER_ID': str})

    to_datas = []
    lot_id = df_mir['LOT_ID'].values[0]
    wafer_id = df_wir['WAFER_ID'].values[0]
    sblot_id = df_mir['SBLOT_ID'].values[0]
    part_type = df_mir['RART_TYP'].values[0]

    ending_time = df_mir['START_T'].values[0]
    time_array = time.localtime(ending_time)
    ending_time = time.strftime("%Y-%m-%d %H:%M:%S", time_array)

    node_name = df_mir['NODE_NAM'].values[0]
    spec_name = df_mir['SPEC_NAM'].values[0]
    test_cod = df_mir['TEST_COD'].values[0]
    tst_temp = df_mir['TST_TEMP'].values[0]
    flow_id = df_mir['FLOW_ID'].values[0]

    for idx, rows in df_wrr.iterrows():
        part_cnt = rows['PART_CNT']
        good_cnt = rows['GOOD_CNT']

        to_line = [read_file, ending_time, lot_id + '#' + str(wafer_id), sblot_id, sblot_id,
                   part_type, part_cnt, part_cnt, 'null', node_name,
                   spec_name, test_cod, tst_temp, flow_id, 'null', 'null', 'null', 'F1', wafer_id, good_cnt]
        to_datas.append(to_line)

    columns = ['File_name', 'Ending_time', 'Wafer_id', 'lot_id', 'C_lot', 'Part_no', 'Records', 'Insert_num',
               'Update_num', 'Machine_Name', 'Program', 'Step', 'Tempreature', 'FLOW', 'PARA_RECORDS',
               'PARA_INSERT', 'PARA_UPDATE', 'VENDOR', 'RAW_WAFERID', 'PASS_DIE']
    out_df = pd.DataFrame(to_datas, columns=columns)
    out_df.to_excel(fixed_out_file, index=None)


def process_to_parameter():
    df_mir = pd.read_excel(read_file, sheet_name='MIR')
    df_ptr = pd.read_excel(read_file, sheet_name='PTR')

    to_datas = []
    part_type = df_mir['RART_TYP'].values[0]
    test_code = df_mir['TEST_COD'].values[0]
    spec_name = df_mir['SPEC_NAM'].values[0]
    test_num_list = []
    for idx, rows in df_ptr.iterrows():
        test_txt = rows['TEST_TXT']
        lo_limit = rows['LO_LIMIT']
        hi_limit = rows['HI_LIMIT']
        units = rows['UNITS']
        test_num = rows['TEST_NUM']

        if test_num in test_num_list:
            continue
        test_num_list.append(test_num)

        to_line = ['I', part_type, test_code, test_txt, lo_limit, hi_limit, units, test_num, spec_name, idx + 1, '', 1]
        to_datas.append(to_line)

    columns = ['PROJECT_TYPE', 'PART_NO', 'PROCESS', 'PARAMETER', 'LIMIT_L', 'LIMIT_H', 'UNIT', 'TEST_NUM',
               'TEST_PROGRAM', 'PARAMETER_ID', 'DISPLAY_UNIT', 'IS_CHART']
    out_df = pd.DataFrame(to_datas, columns=columns)
    out_df.to_excel(parameter_out_file, index=None)


def process_to_other():
    df_mir = pd.read_excel(read_file, sheet_name='MIR')
    df_prr = pd.read_excel(read_file, sheet_name='PRR')
    df_wir = pd.read_excel(read_file, sheet_name='WIR', converters={'WAFER_ID': str})
    df_ptr = pd.read_excel(read_file, sheet_name='PTR')

    to_datas = []
    lot_id = df_mir['LOT_ID'].values[0]
    wafer_id = df_wir['WAFER_ID'].values[0]

    sblot_id = df_mir['SBLOT_ID'].values[0]
    spec_name = df_mir['SPEC_NAM'].values[0]
    flow_id = df_mir['FLOW_ID'].values[0]
    if 'P1' in flow_id:
        retest = 0
    else:
        retest = flow_id.strip('R')
    ending_time = df_mir['START_T'].values[0]
    time_array = time.localtime(ending_time)
    ending_time = time.strftime("%Y-%m-%d %H:%M:%S", time_array)

    start = 0
    ti = 0
    for idx, rows in df_prr.iterrows():
        x_coord = rows['X_COORD']
        y_coord = rows['Y_COORD']
        num_test = rows['NUM_TEST']

        for ptr_idx, ptr_rows in df_ptr[start:start + num_test].iterrows():
            test_txt = ptr_rows['TEST_TXT']
            result = ptr_rows['RESULT']
            to_line = [lot_id + '#' + str(wafer_id), x_coord, y_coord, retest,
                       test_txt, result, spec_name, sblot_id, ending_time]
            to_datas.append(to_line)
        start += num_test

    columns = ['wafer_id', 'Locate_X', 'Locate_Y', 'Retest', 'Test_Name', 'Value', 'Program', 'Lot_id', 'Ending_time']
    out_df = pd.DataFrame(to_datas, columns=columns)
    out_df.to_excel(other_out_file, index=None)


process_to_color()
process_devices()
process_to_fixed()
process_to_parameter()
process_to_other()