How to Unzip Multiple Files and Merge Excel Sheets with Python Pandas

1. Introduction

The author received a request to process several zip files (ZIP format), each containing three Excel sheets with identical names, and needed a way to unzip the files and merge the three Excel sheets into a single table.

2. Implementation

The following Python script uses pathlib to locate zip files, zipfile to read their contents, and pandas to concatenate the Excel sheets and save the result.

from pathlib import Path
from zipfile import ZipFile
import pandas as pd

# Get all zip files in the source directory
zip_path = Path('你的压缩文件目录').glob('*.zip')
# Destination directory for the merged Excel files
to_path = Path('你的目标目录')

# Process each zip file individually
for file in zip_path:
    # Merge the Excel files inside one zip archive
    with ZipFile(file) as zipf:
        df = pd.concat(pd.read_excel(zipf.open(i)) for i in zipf.namelist())
        # Save the merged table to the target directory
        df.to_excel(to_path.joinpath(f'{file.stem}.xlsx'), index=False)

After running the script, all Excel files from the three zip archives are successfully merged into a single Excel file for each archive.

3. Conclusion

The article provides a clear, practical Python automation approach for handling zip archives and Excel data, helping readers solve similar data‑processing challenges efficiently.