How to Merge Multiple Excel Sheets with Pandas: A Step-by-Step Python Guide

1. Introduction

The author received a question in a Python community about automating office tasks with Python, and this article presents a solution using pandas to consolidate data from multiple Excel sheets.

2. Implementation

The following code reads three Excel sheets, renames the quantity columns, merges the DataFrames on the product name using an outer join, fills missing values with zero, calculates a total column, and cleans up empty values.

# 读取Excel表数据到DataFrame
df1 = pd.read_excel(file, sheet_name='淮海')
df2 = pd.read_excel(file, sheet_name='南京')
df3 = pd.read_excel(file, sheet_name='北京')
# 数量列重命名为"淮海数量"、"南京数量"、"北京数量"
df1.rename(columns={'数量': '淮海数量'}, inplace=True)
df2.rename(columns={'数量': '南京数量'}, inplace=True)
df3.rename(columns={'数量': '北京数量'}, inplace=True)
# 按照品名纵相拼接df1、df2、df3
result = df1.merge(df2, on='品名', how='outer').merge(df3, on='品名', how='outer').fillna(0)
# 对"淮海数量"、"南京数量"、"北京数量"3列进行求和汇总
result['汇总'] = result['淮海数量'] + result['南京数量'] + result['北京数量']
result.replace(0, '', inplace=True)
print(result)

The code successfully merges the data and produces the desired summary.

3. Conclusion

The approach resolves the user's problem and demonstrates how pandas can be used for practical data‑processing automation in Python.