我无法使用python在excel中循环浏览整行

Question

我有两个EXCEL，我试图从exel1中获取第二列值，并通过在excel2中使用下划线将这些值连接在第二列中

Excel1：

^{tb1}$

Excel2：

^{tb2}$

循环时，我不会看到整排人

     import pandas as pd
     import os

     file1= 'C:/Users/madhu/Desktop/Excel1.xlsx'
     file2= 'C:/Users/madhu/Desktop/Book1.xlsx'

     df1 = pd.read_excel(file1)
     df2 = pd.read_excel(file2)

     #df1.to_dict()
     #df2.to_dict()
     var=[]
     print(df1)
     print(df2)
     for row,col in range(len(df1)):
     for row1,col in range(len(df2)):
     if row.isspace() == True:
         var.append(df1[row])
         return '_'.join(var)
     elif row == row1:
         var.append(df1[row])
         return '_'.join(var)
     else:
         pass

有人能帮我吗？谢谢

Answer 1

IIUC！从df1中创建dict，并从df2中映射拆分的项目（S1和S2）。参考下面的代码

df1 = pd.read_excel(file1)
df2 = pd.read_excel(file2)

Map = dict(zip(df1.Word, df1.Variable))

pat='('+'|'.join(Map.keys())+')'
df2['S1']= df2['Word'].str.extract(pat=pat,expand=False).fillna('')
df2['S2'] = df2.apply(lambda x: x.Word.replace(x['S1'],''), axis =1)

df2['S2'] = df2['S2'].apply(lambda x: x.strip())
cols = ['S1', 'S2']

for col in cols:
    df2[col] = df2[col].replace(Map)

df2['Variable Should be'] = df2['S1'] +'_'+ df2['S2']
df2.drop(columns = ['S1', 'S2'], inplace = True)

Answer 2

我假设“用户电话号码”需要“us_pn”作为变量。我还假设代码不需要返回任何值

import pandas as pd
import os

file1= 'C:/Users/madhu/Desktop/Excel1.xlsx'
file2= 'C:/Users/madhu/Desktop/Book1.xlsx'

df1 = pd.read_excel(file1)
df2 = pd.read_excel(file2)

# Uncomment the following piece of code if the excel files have nan.
# df2.fillna('0', inplace=True)

print(df2)

for row2 in df2.values:
    word_list = list(row2[0].split(' '))
    
    # This is to handle the special case of 'user phone number'
    # with output of 'us_pn'. 
    # If the desired output is otherwise
    # 'us_ph_num', then this piece of code is not needed.
    if 'phone number' in row2[0]:
        word_list[word_list.index('phone')] = 'phone number'
        word_list[word_list.index('number')] = ''
    
    var_list = []
    for word in word_list:
        for row1 in df1.values:
            if word == row1[0]:
                var_list.append(row1[1])
    row2[1] = "_".join(var_list)

如果我的假设有任何错误，那么一定要让我知道，我会相应地修改代码