If a pandas merge suddenly increases the row count, first count how often the join key appears on each side. The same customer ID appearing twice in both an orders table and a customers table produces four combinations. A tiny example makes the multiplication visible.
import pandas as pd
orders = pd.DataFrame({"order": ["A", "B"], "customer_id": [1, 1]})
customers = pd.DataFrame({"customer_id": [1, 1], "tier": ["X", "Y"]})
merged = orders.merge(customers, on="customer_id", how="left")
assert len(merged) == 4Order A matches customer rows X and Y; order B also matches X and Y. There are still two original orders, but the join result has four rows. Summing an order amount from that result could count each order twice.
| Order | Matched customer tiers |
|---|---|
| A | X, Y |
| B | X, Y |
If the right-hand table had only one row for that customer ID, the result would have two rows. Decide what relationship you expect before deciding whether the increased row count is correct.
Check the keys before joining
Two occurrences of key 1 on each side yield 2 × 2 = 4 row combinations. The diagram's L1, L2, R1, and R2 identify rows; they are not customer fields.
If customer_id is supposed to identify one customer row in the right-hand table, check that assumption before the join:
duplicates = customers[customers["customer_id"].duplicated(keep=False)]
assert len(duplicates) == 2Immediately calling drop_duplicates may hide a problem in the source data. You need a business rule to determine which row is current, or whether multiple rows are in fact valid.
State the expected relationship with validate
many_to_one allows repeated keys on the left but expects each right-hand key to occur only once. It should therefore raise MergeError for this example:
from pandas.errors import MergeError
try:
orders.merge(customers, on="customer_id", how="left", validate="many_to_one")
except MergeError:
pass # The duplicated customer key is the expected failure.
else:
raise AssertionError("A duplicated customer key went unnoticed")Do not erase the duplicate just to silence the error. Check the source's identity rule or update timestamp to decide whether X or Y is correct. Record row counts before and after the join, duplicate right-hand keys, and unmatched orders to narrow down where the data changed. With large data, a many-to-many join can grow memory use quickly, so check cardinality before merging.
Key takeaways
Duplicate join keys are a common reason for extra rows after merge. Define the expected cardinality, inspect duplicate keys, and encode the assumption with validate. Understanding why the duplicate exists protects data quality better than arbitrary deduplication.
