How to Merge Specific Excel Sheets with Python & Pandas in Minutes

Introduction

I'm a Python enthusiast sharing practical automation tips. This post answers two reader questions about merging specific Excel sheets using Python and pandas.

Problem 1: Merge sheet "a" from file A with sheet "b" from file B

Using pandas, read the two sheets and concatenate them:

# coding: utf-8
# Merge sheet a from file A with sheet b from file B
from pathlib import Path
import pandas as pd

path = r'E:\PythonCrawler\有趣的代码\Python自动化办公\将A文件中名为a的sheet和B文件中名为b的sheet合并到一个sheet中去'

data_ex1 = pd.read_excel('ex1.xlsx', sheet_name='df1')
data_ex2 = pd.read_excel('ex2.xlsx', sheet_name='df2')
result = pd.concat([data_ex1, data_ex2], ignore_index=True)
result.to_excel('merged_sheet.xlsx', index=False, encoding='utf-8')
print('Merge completed!')

The script creates a new Excel file showing the combined data.

Problem 2: Merge the second sheet of every Excel file in a folder

Iterate over all Excel files, read the sheet at index 1 (the second sheet), collect the data frames, and concatenate them:

# coding: utf-8
# Merge the second sheet of all Excel files in a folder
from pathlib import Path
import pandas as pd

path = Path(r'E:\PythonCrawler\有趣的代码\Python自动化办公\将文件夹下所有文件的第二张表合并')

data_list = []
for i in path.glob('*.xls*'):
    # data = pd.read_excel(i, sheet_name='df2')  # alternative if sheet names are consistent
    data = pd.read_excel(i, sheet_name=1)  # use index to get the second sheet
    data_list.append(data)

result = pd.concat(data_list, ignore_index=True)
result.to_excel(path.joinpath('merged_second_sheets.xlsx'), index=False, encoding='utf-8')
print('Merge completed!')

The script outputs a new Excel file containing the combined second sheets.

Conclusion

The two solutions demonstrate how pandas can quickly handle sheet‑level merging without complex logic, satisfying the original requests. The full code examples are available on GitHub for further exploration.

GitHub repository: https://github.com/cassieeric/Python-office-automation