How to Automate Bulk Excel Find‑Replace with Python and Pandas

1. Introduction

In a Python community chat, a user asked for a practical automation solution to replace multiple values across Excel sheets. The goal was to read a mapping table, apply it to a target workbook, and save the transformed result.

2. Implementation

The following Python script uses pandas to achieve the task:

import pandas as pd

reedFile_df = pd.read_excel("1查找替换关系转换表.xlsx")
replaced_df = pd.read_excel("2待替换表格.xlsx")
reedFile = reedFile_df.drop_duplicates().set_index('查找内容').to_dict('dict')['替换内容']
replaced_df.replace(reedFile, inplace=True)
replaced_df.to_excel('结果.xlsx', index=False)

During the replacement, missing values such as NaN and NaT are written as blank cells in the output Excel file.

The script produces the expected transformed workbook, as shown in the result screenshot.

3. Conclusion

This concise solution demonstrates how Python can efficiently handle bulk find‑replace operations in Excel, eliminating the tedious manual process and reducing the risk of errors. The approach is reusable for similar data‑processing tasks.