从一个Excel中获取数据，并使用相同的格式写入另一个Excel

Question

我正在用python读取两个Excel文件。从其中一个获取数据，然后在某个条件下将数据写入另一个。让我们调用文件sourcefile1.xlsx和sourcefile2.xlsx。你知道吗

以下是Excel文件的内容：

1. 源文件1.xlsx Pair和Field结果是合并的主标题。 cable\u type、cable\u name、cable\u pair、caller\u id和result是表示每一列的辅助标头。你知道吗

   -----------------Pair---------------   -----Field Result-----
   cable_type   cable_name   cable_pair   caller_id   result
   primary      2            103          n/a         not match
   primary      1            33           22222222    match
   primary      5            342          22222222    match 
   secondary    2            12           n/a         not match
   secondary    4            144          44444444    match
   

2. 源文件2.xlsx

   -blank-     -----Secondary Pairs----    ------Primary Pairs------
   caller_id   caller_id  adsl  result       caller_id   adsl   result
   11111111               4/144                          2/103  
   22222222               2/12                           4/144 
   44444444               7/55                           4/144
   NULL                   8/123                          1/11
   NULL                   NULL                           2/22
   

预期输出将基于以下伪代码写入sourcefile2：

if caller_id(sourcefile1) != 'N/A':
    if cable_type(sourcefile1) = 'primary':
        if caller_id(sourcefile1) = caller_id(sourcefile2) - primary pairs:
            write caller_id(sourcefile1) to caller_id(sourcefile2) - primary pairs
            write result(sourcefile1) to result(sourcefile2) - primary pairs

elif caller_id(sourcefile1) != 'N/A':
    if cable_type(sourcefile1) = 'secondary':
        if caller_id(sourcefile1) = caller_id(sourcefile2) - secondary pairs:
            write caller_id(sourcefile1) to caller_id(sourcefile2) - secondary pairs
            write result(sourcefile1) to result(sourcefile2) - secondary pairs

elif caller_id(sourcefile1) = 'N/A':
    if cable_type(sourcefile1) = 'primary':
        if cable_name + cable_pair(sourcefile1) = adsl(sourcefile2) - primary pairs:
            write caller_id(sourcefile1) to caller_id(sourcefile2) - primary pairs
            write result(sourcefile1) to result(sourcefile2) - primary pairs

elif caller(sourcefile1) = 'N/A':
    if cable_type(sourcefile1) = 'secondary':
        if cable_name + cable_pair(sourcefile1) = adsl(sourcefile2) - secondary pairs:
            write caller_id(sourcefile1) to caller_id(sourcefile2) - secondary pairs
            write result(sourcefile1) to result(sourcefile2) - secondary pairs

这是我想要得到的结果。你知道吗

-blank-     -----Secondary Pairs----    ------Primary Pairs------
caller_id   caller_id  adsl   result       caller_id   adsl   result
11111111               4/144              n/a         2/103  not match
22222222    n/a        2/12   not match    22222222    4/144  match
44444444    44444444   7/55   match                    4/144
NULL                   8/123                          1/11
NULL                   NULL                           2/22

我正在尝试将源文件1的呼叫者id与源文件2匹配，并根据其电缆类型将其写入主要对或次要对。如果呼叫者id是n/a，那么我需要匹配的是adsl。结果是给定的数据，我只需要获得与呼叫者id或adsl在同一行中的任何内容。你知道吗

到目前为止，我能够匹配调用方id，但是我重新创建了sourcefile1和sourcefile2并删除了主头。这是我的密码：

import pandas as pd

df1 = pd.read_excel('sourcefile2.xlsx')
df2 = pd.read_excel('sourcefile1.xlsx', 'v0.02')

forPrimary1 = df1.columns[40]
forSecondary1 = df1.columns[23]
ComparisonResult = df2.columns[22]

forAdsl = df1.columns[39]
CallerID = df2.columns[13]
forPrimary = df1.columns[37]
forSecondary = df1.columns[16]

df3 = pd.read_excel('PrimarySecondary.xlsx')
df4 = pd.read_excel('adslFile.xlsx')
df5 = pd.read_excel('PrimarySecondary2.xlsx')

# df1['svc_no'] = df1['svc_no']
df2['Adsl'] = df2[['cable_name', 'pair']].apply(lambda x: '/'.join(x.astype(str)), axis=1)
newPrim = df2[[caller_id, 'result', 'Adsl']] [(df2['cable_type'] == 'Primary')]
newSec = df2[[caller_id, 'result']] [(df2['cable_type'] == 'Secondary')]
newPrim.to_excel('newPrimary.xlsx')
newSec.to_excel('newSecondary.xlsx')

frame = pd.read_excel('newPrimary.xlsx')
frame1 = pd.read_excel('newSecondary.xlsx')

df1['b_line_stat'] = df1['b_line_stat'].fillna('NULL')
df1['DP_e_pr'] = df1['DP_e_pr'].fillna('NULL')
df1['DP_e_st'] = df1['DP_e_st'].fillna('NULL')
df1['DP'] = df1['DP'].fillna('NULL')
df1['CAB_d_st'] = df1['CAB_d_st'].fillna('NULL')
df1['CAB_d_pr'] = df1['CAB_d_pr'].fillna('NULL')
df1['port_status'] = df1['port_status'].fillna('NULL')

name1 = df1.columns[17]
name2 = df1.columns[18]
name3 = df1.columns[19]
name4 = df1.columns[20]
name5 = df1.columns[21]
name6 = df1.columns[22]
name7 = df1.columns[38]

df1[name1] = df1['b_line_stat']
df1[name2] = df1['CAB_d_st']
df1[name3] = df1['CAB_d_pr']
df1[name4] = df1['DP_e_st']
df1[name5] = df1['DP_e_pr']
df1[name6] = df1['DP']
df1[name7] = df1['port_status']

frame = frame[frame['caller_id'].isin(df1['caller_id'])]
df1[forPrimary1] = frame['result']

frame1 = frame1[frame1['caller_id'].isin(df1['caller_id'])]
df1[forSecondary1] = frame1['result']

df1[df1['caller_id'].isin(df3['Primary'])]
df1[forPrimary] = df1['caller_id'].fillna('n/a')

df1[df1['adsl'].isin(df2['Adsl'])]
df1[forAdsl] = df1['adsl'].fillna('NULL')

df1[df1['caller_id'].isin(df3['Secondary'])]
df1[forSecondary] = df1['caller_id'].fillna('n/a')

df1['caller_id'] = df1['caller_id'].fillna('NULL')
df1['adsl'] = df1['adsl'].fillna('NULL')

df1.to_excel('dp_util_ANT715-M.xlsx', index=False)

writer = pd.ExcelWriter('dp_util_ANT715-M.xlsx', engine='xlsxwriter')
df1.to_excel(writer, sheet_name='Sheet1')

编辑：我在脚本中使用的变量，以匹配我的问题。你知道吗