场景：有如下2个excel，重叠字段为：randomID
image.png
image.png
现需要将2个excel合并为一个
image.png
实现步骤：
1.先读取2个excel中的内容，分别存入字典、列表中
2.合并读取的数据，遍历列表中的数据并转换为字典，循环判断重叠字段randomID的值是否相同
3.定义新列表，将randomID相同的数据，为其追加no及对应值，否则设置no为空
4.将列表中的数据重新写入excel
# -*- coding: utf-8 -*-
import pandas as pd
def write_toexcel(data,filename):
ids = []
randomIDs = []
nos = []
scores = []
for i in range(len(data)):
ids.append(data[i]["id"])
randomIDs.append(data[i]["randomID"])
nos.append(data[i]["no"])
scores.append(data[i]["score"])
dfData = {'id':ids,'randomID':randomIDs,"no":nos,"score":scores}
df = pd.DataFrame(dfData)
df.to_excel(filename,index=False)
members = pd.read_excel("D:/A.xlsx",header=0)
members_li =members.to_dict("records")
print(members_li)
points = pd.read_excel("D:/B.xlsx",header=0)
points_li =points.to_dict("records")
print(points_li)
#合并数据
listnew=[]
for i in range(len(points_li)):
mdict = dict(eval(str(points_li[i])))
dictnew = {}
ouid = mdict.get("randomID")
for j in range(len(members_li)):
pdict = dict(eval(str(members_li[j])))
p_list = list(pdict.values())
if ouid == p_list[0]:
dictnew['id'] = mdict.get("id")
dictnew['randomID'] = mdict.get("randomID")
dictnew['score'] = mdict.get("score")
dictnew['no'] = p_list[1]
break
else:
pass
else:
dictnew['id'] = mdict.get("id")
dictnew['randomID'] = mdict.get("randomID")
dictnew['score'] = mdict.get("score")
dictnew['no'] = "null"
j = j + 1
i=i+1
listnew.append(dictnew)
print(listnew)
write_toexcel(listnew,'数据sc.xlsx')
