場(chǎng)景：有如下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及對(duì)應(yīng)值，否則設(shè)置no為空
4.將列表中的數(shù)據(jù)重新寫(xiě)入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')