Moooocha

导航

python 对比两个excel文件中的列，如果相同，提取相同值所在行的另一列值 

 1 import pandas as pd
 2 from openpyxl import load_workbook
 3 
 4 def compare(data1, sheetname1, data2, sheetname2):
 5     # 读取两个表
 6     dt1 = pd.read_excel(data1, sheet_name=sheetname1, engine='openpyxl')
 7     dt2 = pd.read_excel(data2, sheet_name=sheetname2, engine='openpyxl')
 8     # 确定基准列
 9     dt1_file_path = dt1['file path'].values.tolist()
10     dt2_file_path = dt2['file path'].values.tolist()
11 
12     for i in dt1_file_path:
13         if i in dt2_file_path:
14             dt2_row = dt2.loc[dt2['file path'] == i]
15             # dt1_row = dt1.loc[dt1['file path'] == i]
16 
17             rowid = dt1.index[dt1['file path'] == i].tolist()
18 
19         dt1.loc[rowid, 'Remark'] = dt2_row['Remark'].tolist()
20     # print(dt1)
21 
22     book = load_workbook('E:/test/mod1a.xlsx')
23     write = pd.ExcelWriter(r'E:/test/mod1a.xlsx', engine='openpyxl')
24     write.book = book
25     write.sheets = {ws.title: ws for ws in book.worksheets}
26     dt1.to_excel(write, header=True, index=False, encoding="utf_8_sig")
27     write.save()
28     write.close()
29 
30 compare("E:/test/mod1a.xlsx","Sheet1","E:/test/mod2a.xlsx","Sheet1")

 

posted on 2022-01-10 00:00  Moooocha  阅读(2874)  评论(0)    收藏  举报