美文网首页
Python3 & pandas实现多个excel数据合并

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

作者: 乘风破浪的姐姐 | 来源:发表于2022-12-09 16:58 被阅读0次

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

    相关文章

      网友评论

          本文标题:Python3 & pandas实现多个excel数据合并

          本文链接:https://www.haomeiwen.com/subject/gtsafdtx.html