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')