How to Merge Multiple Excel Files with Python and Filter Data Efficiently

1. Introduction

Hello, I am a Python enthusiast. In a recent interaction with a follower, a simple request arose: batch‑process many Excel files, merge them, and then filter specific rows. This article continues a previous guide on batch‑selecting rows from thousands of Excel files.

2. Implementation Process

The approach differs slightly from the previous article. Because all Excel files share the same format, we first merge every file in the directory and then apply the filter.

import pandas as pd
import os

result = []
path = "./新建文件夹"
for root, dirs, files in os.walk(path, topdown=False):
    for name in files:
        if name.endswith(".xls") or name.endswith(".xlsx"):
            df = pd.read_excel(os.path.join(root, name))
            result.append(df)

df = pd.concat(result)
df.to_excel("hebing.xlsx", index=False)

The merged data looks like the following image:

Now we can filter the merged workbook using the same logic as before:

# import os
import pandas as pd

df = pd.read_excel("hebing.xlsx")
df1 = df[df['id'] == '58666']
df1.to_excel('res.xlsx')

The final result is shown below:

3. Conclusion

This article presents a practical Python automation case for file handling that can be adapted to real‑world work scenarios. Feel free to modify the code to suit your own tasks.