What this usually means
The core issue is a mismatch between your expected join cardinality and the actual data. Pandas does not enforce uniqueness on join keys. If either DataFrame has duplicate values in the key columns, every matching pair creates a row. For example, if left has key 'A' twice and right has key 'A' three times, you get 6 rows. This often happens when you assume a one-to-one relationship but the data contains duplicates from data entry errors, unrolled nested structures, or forgotten aggregations.
The first ten minutes — establish facts before touching code.
- 1df.merge(df2, on='key', how='inner').shape — compare to expected row count
- 2df['key'].duplicated().sum() — count duplicates in left key
- 3df2['key'].duplicated().sum() — count duplicates in right key
- 4df.merge(df2, on='key', how='inner').duplicated().sum() — see if output itself has duplicates
The specific files, logs, configs, and dashboards that usually own this bug.
- searchData source validation logs: check for duplicate primary keys
- searchETL scripts: missing deduplication step before merge
- searchKey column dtype mismatches: int vs string causing implicit duplicates?
- searchCross-join risk: merge with no valid keys produces Cartesian product
- searchIndex columns: accidental merge on index when you meant a column
Practical causes, not theory. These are the things you will actually find.
- warningKey column has duplicates in one or both DataFrames
- warningMultiple columns used as key but only one column actually matches
- warningMerging on index when index contains duplicates (reset_index often fixes this)
- warningData type mismatch causing rows that look equal but aren't (e.g., int vs float, string vs category)
- warningLeft and right keys have different names but you used 'on' instead of left_on/right_on
- warningData contains NaN in key columns — NaN != NaN in merge logic, so they don't match
Concrete fix directions. Pick the one that matches your root cause.
- buildDrop duplicates from keys before merge: df.drop_duplicates(subset='key')
- buildUse validate argument: pd.merge(..., validate='one_to_one') raises error if cardinality mismatched
- buildAggregate duplicate keys before merge: df.groupby('key').agg(...).reset_index()
- buildAdd a row ID column and deduplicate after merge: df['_row'] = range(len(df)); df.merge(...).drop_duplicates(subset='_row')
- buildCheck for NaN keys: df['key'].isna().sum() and decide how to handle (drop or fill)
- buildExplicitly specify left_on and right_on if column names differ, don't rely on 'on'
A fix you cannot prove is a guess. Close the loop.
- verifiedRun shape check: merged.shape[0] should be <= df.shape[0] if left is unique
- verifiedValidate no duplicates on primary key after fix: merged['key'].duplicated().sum() == 0
- verifiedCompare aggregated sums before and after: df['value'].sum() vs merged.groupby('key')['value'].sum().sum()
- verifiedRun pd.merge(..., indicator=True) and check '_merge' column for unexpected 'both' vs 'left_only' counts
- verifiedUnit test with small known duplicates to confirm deduplication logic
Things that make this bug worse or harder to find.
- warningSilently dropping duplicates without investigating root cause — you might lose legitimate data
- warningUsing how='outer' when duplicates exist — explodes rows even more
- warningAssuming merge preserves index order — it doesn't; reset_index before merge if order matters
- warningForgetting to reset index after groupby aggregation
- warningNot checking dtype consistency between key columns (e.g., object vs int)
Customer Order Merge Produced 3x Rows
Timeline
- 09:00Ran order merge: orders.merge(customers, on='customer_id')
- 09:01Result has 45k rows, expected ~15k (orders table)
- 09:05Checked orders shape: 15k rows
- 09:07Checked customers shape: 10k rows (unique customers)
- 09:10Ran customers['customer_id'].duplicated().sum() -> 0
- 09:12Ran orders['customer_id'].duplicated().sum() -> 5k duplicate customer_ids
- 09:15Found customers table had one row per customer, but orders had multiple orders per customer
- 09:20Fixed by aggregating order metrics before merge, or using merge with validate='many_to_one'
I was merging an orders DataFrame with a customers DataFrame on customer_id. I expected one customer row per order, but the result had 45k rows instead of 15k. I first checked shapes and found orders had 15k rows and customers had 10k rows — so a Cartesian product was likely.
I ran duplicated checks. Customers had zero duplicate customer_ids, but orders had 5k duplicate customer_ids because many customers had multiple orders. That was the root cause: a many-to-one merge on a key that had duplicates on the 'many' side is fine, but I had mistakenly assumed one-to-one.
I fixed it by adding validate='many_to_one' to catch future mistakes, and I aggregated order-level metrics per customer before merging. The lesson: always check key cardinality — and use the validate parameter to enforce assumptions.
Root cause
orders.customer_id had duplicates (multiple orders per customer) — merge with customers produced one row per order-customer pair, which is correct but unexpected if you assume one-to-one.
The fix
Added validate='many_to_one' in merge and aggregated order data per customer before merging.
The lesson
Always check key duplicates on both sides and use the validate parameter to enforce join cardinality.
Pandas merge does not check or enforce cardinality by default. You can pass validate='one_to_one', 'one_to_many', 'many_to_one', or 'many_to_many'. This raises an error if the actual data violates the specified relationship.
For example, if you expect one-to-one, run: pd.merge(df1, df2, on='key', validate='one_to_one'). If duplicates exist, it raises a MergeError with a clear message. This catches the problem early and documents intent.
NaN values in key columns never match each other — NaN != NaN in merge logic. This can cause rows to be dropped in inner joins or appear as left_only/right_only in indicator merges.
If you want NaN to match, fill them with a sentinel: df['key'] = df['key'].fillna('__MISSING__'). But be careful: this can create unwanted matches if the sentinel value exists in the other DataFrame.
Merging on index when the index contains duplicates creates the same explosion. Always check df.index.duplicated().sum() before merging on index.
Use df.reset_index() to convert index to a column, then merge on that column. This makes the key explicit and easier to debug.
Frequently asked questions
Why does an inner join produce more rows than the smaller DataFrame?
Because the join key has duplicates. Each duplicate in one DataFrame multiplies with each duplicate in the other. For example, if left has key 'A' twice and right has key 'A' three times, the join produces 6 rows for 'A'. Always check key duplicates on both sides.
How can I check for duplicates without modifying the DataFrames?
Use df.duplicated(subset=['key1','key2']).sum() to count duplicate rows based on key columns. For a quick visual, run df[df.duplicated(subset=['key'], keep=False)].sort_values('key') to see all rows with duplicate keys.
What does the indicator=True parameter do?
It adds a column '_merge' that indicates whether each row came from left_only, right_only, or both. This helps identify unexpected missing or extra matches, especially when combined with key cardinality checks.
Can dtype mismatches cause duplicate rows?
Yes. If one key column is int64 and the other is object (string), values like 1 and '1' are not considered equal, so they don't match. But if both are object, they might match unexpectedly. Always cast keys to the same dtype: df['key'] = df['key'].astype(str).
Is there a way to avoid duplicates without dropping data?
If duplicates are legitimate (e.g., multiple orders per customer), use a many-to-one merge. If you need one row per left key, aggregate the right side first: right_agg = right.groupby('key').agg(...).reset_index(). Then merge left with right_agg.