python pandas操作excel多个sheet合并成一个sheet

一、背景

用python(2.7) pandas处理excel多个sheet,各sheet之间有关联字段,起到的效果相当于多张RDB的表,做联表查询。

二、python代码

import pandas as pd
import xlsxwriter


def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    dfCity = \
        pd.read_excel('/Users/xxxx/Documents/excels/doctor_data.xlsx',
                      sheet_name='city')
    dfArea = \
        pd.read_excel('/Users/xxxx/Documents/excels/doctor_data.xlsx',
                      sheet_name='area')
    dfHospital = \
        pd.read_excel('/Users/xxxx/Documents/excels/doctor_data.xlsx',
                      sheet_name='hospital')
    dfDepartment = \
        pd.read_excel('/Users/xxxx/Documents/excels/doctor_data.xlsx',
                      sheet_name='department')
    dfDoctor = \
        pd.read_excel('/Users/xxxx/Documents/excels/doctor_data.xlsx',
                      sheet_name='doctor')

    df1 = pd.merge(dfCity, dfArea, left_on='id_city', right_on='cityId', how='inner')
    df2 = pd.merge(df1, dfHospital, left_on='id_area', right_on='areaId', how='inner')
    df3 = pd.merge(df2, dfDepartment, left_on='id_hospital', right_on='hospitalId', how='inner')
    df4 = pd.merge(df3, dfDoctor, left_on='id_department', right_on='departmentId', how='inner')

    with pd.ExcelWriter('/Users/xxxx/Documents/excels/doctor_data_merge.xlsx',
                        engine='xlsxwriter',
                        options={'strings_to_urls': False}) as writer:
        df4.to_excel(writer, index=False)


if __name__ == '__main__':
    print_hi('PyCharm')

 

上一篇:Flutter 环境配置


下一篇:appium-doctor‘ 不是内部或外部命令,也不是可运行的程序或批处理文件的解决方法