Python3 & pandas实现多个excel数据合并

场景：有如下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')