How to Merge Multiple Excel Sheets with Python Pandas – A Quick Guide

Hello everyone, I’m PiPi.

1. Introduction

Recently a member of the Python community asked about automating office tasks with Python. In this post we explore a solution that reads multiple Excel sheets, renames columns, merges the data, and outputs the combined table.

2. Implementation

The following code demonstrates the complete process:

# Read Excel sheets into DataFrames
df1 = pd.read_excel(file, sheet_name='淮海')
df2 = pd.read_excel(file, sheet_name='南京')
df3 = pd.read_excel(file, sheet_name='北京')
# Rename quantity columns
df1.rename(columns={'数量': '淮海数量'}, inplace=True)
df2.rename(columns={'数量': '南京数量'}, inplace=True)
df3.rename(columns={'数量': '北京数量'}, inplace=True)
# Merge the DataFrames on the product name
result = df1.merge(df2, on='品名', how='outer')\
            .merge(df3, on='品名', how='outer').fillna('')
print(result)

The code successfully resolves the fan’s question by automating the data aggregation.

3. Summary

This article presented a concrete Python automation example for office data processing, showing how to read Excel files, rename columns, merge them on a common field, and output the result, helping readers apply pandas for similar tasks.