場景：有如下2個(gè)excel屈嗤，重疊字段為：randomID
image.png
image.png
現(xiàn)需要將2個(gè)excel合并為一個(gè)
image.png
實(shí)現(xiàn)步驟：
1.先讀取2個(gè)excel中的內(nèi)容，分別存入字典凡桥、列表中
2.合并讀取的數(shù)據(jù)，遍歷列表中的數(shù)據(jù)并轉(zhuǎn)換為字典，循環(huán)判斷重疊字段randomID的值是否相同
3.定義新列表疟羹，將randomID相同的數(shù)據(jù)，為其追加no及對應(yīng)值禀倔，否則設(shè)置no為空
4.將列表中的數(shù)據(jù)重新寫入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)
#合并數(shù)據(jù)
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,'數(shù)據(jù)sc.xlsx')
