How to Simplify Pandas Merge for Excel Automation: Code Samples & Tips

1. Introduction

The author shares a Python automation question raised in a WeChat group, seeking a way to eliminate loops when processing Excel data.

2. Implementation

First solution: use pandas.merge with reset_index to preserve original row positions.

import pandas as pd

df = pd.read_excel("替换.xlsx")
ionp = df[df.编号.str.startswith("IONP")]
rule = "[一二三]工厂半成品"
chg = df[df.入库.str.fullmatch(rule) & df.出库.str.fullmatch(rule)]
t = chg.reset_index(names="idx").merge(ionp, left_on=["物料代码", "入库"], right_on=["物料代码", "出库"], suffixes=("", "_y"))
df.loc[t.idx, "入库"] = t.入库_y.values

This approach works but merge resets the index, so resetting is required to keep track of rows.

Second solution: directly locate matching records and replace values without using merge.

import pandas as pd

df = pd.read_excel('替换.xlsx')
# 1. Find rows where both 入库 and 出库 match the pattern
compile = r'[一二三]工厂半成品'
query_code = df.loc[df['入库'].str.fullmatch(compile) & df['出库'].str.fullmatch(compile), ['物料代码', '入库']]
# 2. Use the material code to find corresponding IONP rows
ionp = df.loc[(df['物料代码'] + df['出库']).isin(query_code.agg(''.join, axis=1)) & df['编号'].str.startswith('IONP')].set_index('物料代码')['入库']
# 3. Replace the 入库 values
df.loc[query_code.index, '入库'] = query_code['物料代码'].replace(ionp)

Discussion notes that merge is already a one‑line solution; further simplification would require a different approach because merge inherently resets the index.

3. Summary

The article reviews a Python automation problem, provides detailed explanations and two concrete pandas implementations, and helps readers understand how to manipulate Excel data efficiently while preserving row order.