How to Merge Multiple Excel Performance Sheets with Python Pandas

Hello, I'm PiPi. This article addresses a Python automation task: merging multiple employee performance Excel files into one.

1. Problem Description

A user has a folder containing several Excel files, each with two columns: date and performance score. The goal is to combine them into a single Excel workbook.

2. Implementation

The solution uses pandas to read each file, concatenate the data frames, and export the combined result.

import pandas as pd
import os

file_names = os.listdir("C:/Users/pdcfighting/Desktop/绩效")  # Get all file names in the target folder
df1 = pd.DataFrame({"日期": [], "绩效得分": []})
for file in file_names:  # Iterate over each file
    df2 = pd.read_excel("C:/Users/pdcfighting/Desktop/绩效/" + file, engine='openpyxl')
    df3 = pd.concat([df1, df2])  # Vertical concatenation
    df1 = df3
    print(f"{file}已经合并！")

print(df1)
df1.to_excel("合并表格.xlsx", engine='openpyxl')

Running the script produces the expected merged spreadsheet.

3. Common Issues

If you encounter an error about missing openpyxl, ensure the engine is installed. Also, avoid placing the Python script in the same directory as the Excel files, otherwise the script may try to read itself and cause errors.

4. Conclusion

This guide demonstrates how to automate Excel merging with Python, providing a practical solution for handling repetitive data consolidation tasks.