How to Speed Up Python Data Merges: A Practical Pandas Solution

Introduction

The author, a Python enthusiast, shares a recent question from a WeChat group where a user needed to match rows from table a (70,000 rows) with rows from table b (300 rows) using a for‑loop, which took over 20 minutes.

Problem Description

The user attempted to assign values from b to a when a condition was met, but the loop was extremely slow. The discussion highlights that using pandas for‑loops is generally inefficient.

Discussion

Experts suggest that the user should first merge the two DataFrames and then apply any additional filters, rather than iterating with a for‑loop. They explain that merging once and reusing the result is more performant, and that further conditions can be applied with simple boolean indexing.

Solution

A concise pandas implementation is provided:

companies = []
nums = []
for row in jd_MergeTotal.itertuples(False):
    tmp = JD_zhekou.query(f"公司组织编码=='{row.公司编码}'")
    companies.append(tmp.公司.iat[0])
    discount_dest = None
    for _, _, num, discount in tmp.itertuples(False):
        min_v, max_v = map(int, num.split("-", maxsplit=1))
        if min_v <= row.单量 <= max_v:
            discount_dest = discount
            break
    nums.append(discount_dest)

jd_MergeTotal["公司"] = companies
jd_MergeTotal["折扣"] = nums

This code merges the tables, then filters based on the numeric range to assign the appropriate discount, dramatically reducing execution time.

Conclusion

The article demonstrates how replacing a brute‑force for‑loop with pandas merge and conditional filtering solves the performance issue, and encourages readers to share similar Python problems for community assistance.