Data Cleaning Exercises

pandas merge Pitfalls: Duplicated Rows, Lost Rows and Null Keys

Why pandas merge adds rows, drops rows and matches missing keys to each other, and how validate, indicator and a row-count check catch each problem.

By Tnas · Published · 9 min read

Dark code editor card showing orders.merge with how=left, validate=many_to_one and indicator=True, next to the title merge pitfalls: fan-out, lost rows and null keys

A pandas merge goes wrong silently in three main ways. It duplicates rows when the lookup table has repeated keys. It loses rows when you use the default inner join and some keys have no match. And it matches missing keys to each other, which a standard SQL equality join never does. None of these raise an error by default. The fix is the same each time: state what the join should look like with validate=, see where every row came from with indicator=True, and compare row counts and totals before and after.

This post works through each pitfall on a small orders and customers example. Every snippet was run on pandas 3.0.2 and the outputs are copied from those runs.

Key Takeaways

  • merge defaults to how="inner", which silently drops rows that have no match. Use how="left" when every left row must survive.
  • Fan-out is what happens when a repeated key in the lookup table multiplies rows. validate="many_to_one" turns that into an error instead of inflated totals.
  • pandas matches missing keys to each other in a merge. Split off rows with a missing key before you join.
  • indicator=True adds a _merge column showing which rows matched, so unmatched rows can be reported instead of lost.

The example tables

import pandas as pd

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4, 5],
    "customer_id": ["C1", "C2", "C3", None, None],
    "amount": [50.0, 20.0, 35.0, 10.0, 5.0],
})
customers = pd.DataFrame({
    "customer_id": ["C1", "C2", "C2", None],
    "region": ["North", "South", "West", "Unknown"],
})

Five orders worth 120.0 in total. Customer C2 appears twice in the lookup with different regions, C3 is missing from the lookup entirely, two orders have no customer ID, and the lookup has a row with a missing ID. Real lookup tables have all of these problems more often than you'd hope.

Pitfall 1: The default inner join loses rows

DataFrame.merge uses how="inner" unless you say otherwise (pandas DataFrame.merge documentation). An inner join keeps only rows whose key appears in both tables:

print(orders.merge(customers, on="customer_id"))

   order_id customer_id  amount   region
0         1          C1    50.0    North
1         2          C2    20.0    South
2         2          C2    20.0     West
3         4         NaN    10.0  Unknown
4         5         NaN     5.0  Unknown

Order 3 is gone, because C3 isn't in the lookup. Nothing warned you. If this frame feeds a revenue total, order 3's 35.0 has disappeared. (The frame actually sums to 105.0, not 85.0, because order 2 has already been duplicated, which is Pitfall 2.) When the left table is the thing you are reporting on, such as orders, payments or patients, use how="left" so every left row survives whether or not it finds a match. An order with an unknown customer is still an order. The lesson Report orphans, don't drop them explains why unmatched rows are findings, not noise.

Pitfall 2: Duplicate keys multiply rows

Switching to a left join keeps order 3, but now look at the row count:

out = orders.merge(customers, on="customer_id", how="left")
print(out)
print(len(orders), len(out), orders["amount"].sum(), out["amount"].sum())

   order_id customer_id  amount   region
0         1          C1    50.0    North
1         2          C2    20.0    South
2         2          C2    20.0     West
3         3          C3    35.0      NaN
4         4         NaN    10.0  Unknown
5         5         NaN     5.0  Unknown
5 6 120.0 140.0

Five orders went in and six rows came out. Order 2 matched both C2 rows, so it now appears twice and revenue went from 120.0 to 140.0. This is called join fan-out: every left row is repeated once for each matching right row. A left join guarantees you won't lose left rows. It doesn't guarantee you won't gain them.

Catch it with validate

Tell pandas what shape the join should have, and it will check. Many orders per customer, one row per customer in the lookup, is "many_to_one":

orders.merge(customers, on="customer_id", how="left", validate="many_to_one")

pandas.errors.MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

The other options are "one_to_one", "one_to_many" and "many_to_many" (no check). When the check fails, find the offending keys:

print(customers[customers.duplicated("customer_id", keep=False)])

  customer_id region
1          C2  South
2          C2   West

Now there's a real question for whoever owns the customer table: is C2 in South or West? Don't fix it by calling drop_duplicates: that keeps whichever row happens to come first, an arbitrary choice, and hides the conflict. Decide with a survivor rule, such as the most recent update, or send the conflict back to the source. The lesson State the cardinality before you join covers how to decide what the shape should be.

Pitfall 3: Missing keys match each other

Look again at the inner join output from Pitfall 1. Orders 4 and 5 have no customer ID, yet both matched the lookup row with no ID and picked up the region Unknown:

   order_id customer_id  amount   region
3         4         NaN    10.0  Unknown
4         5         NaN     5.0  Unknown

The pandas documentation warns about this directly: if both key columns contain null values, those rows are matched against each other, which is different from SQL joins (DataFrame.merge documentation). In SQL, NULL = NULL is not true, so a standard equality join would not match those orders.

A missing key is not evidence that two rows belong together. Join only the rows that have a key, then add the others back unchanged:

cust = customers.iloc[[0, 1]]  # C1 and C2 (South), after the owner confirms C2

has_key = orders["customer_id"].notna()
safe = pd.concat([
    orders[has_key].merge(cust, on="customer_id", how="left", validate="many_to_one"),
    orders[~has_key],
], ignore_index=True)
print(safe)

   order_id customer_id  amount region
0         1          C1    50.0  North
1         2          C2    20.0  South
2         3          C3    35.0    NaN
3         4         NaN    10.0    NaN
4         5         NaN     5.0    NaN

Five rows in, five rows out, 120.0 in total, and orders 4 and 5 keep a missing region instead of a borrowed one. Removing missing-key rows from the lookup table also works, but splitting the left side is safer because it doesn't depend on someone remembering to clean the lookup. The lesson Null keys in pandas merges goes into more detail, and the free exercise Join orders to customers without matching blanks lets you practise it with a grader.

Pitfall 4: Unmatched rows you never look at

Here cust is the corrected lookup from Pitfall 3: one row each for C1 (North) and C2 (South), with no missing keys. A left join keeps unmatched rows, but they just have missing values in the new columns, which look the same as a matched row whose region is genuinely blank. indicator=True adds a _merge column that records where each row came from:

out = orders.merge(cust, on="customer_id", how="left",
                   validate="many_to_one", indicator=True)
print(out["_merge"].value_counts())

_merge
left_only     3
both          2
right_only    0
Name: count, dtype: int64

left_only rows found no match. Here that's order 3 (C3 isn't in the lookup) and orders 4 and 5 (no key). Filter them into an exceptions report with out[out["_merge"] == "left_only"], and drop the _merge column before you deliver the clean table. This is the pandas version of a referential integrity check.

Pitfall 5: Keys that look equal but aren't

Most "my merge matched nothing" problems come from keys with different types or formats.

Different dtypes. If one table read customer_id as text and the other as numbers, pandas refuses to merge:

o2 = pd.DataFrame({"customer_id": ["00042", "00107"], "amount": [1.0, 2.0]})
c2 = pd.DataFrame({"customer_id": [42, 107], "region": ["N", "S"]})
o2.merge(c2, on="customer_id")

ValueError: You are trying to merge on str and int64 columns for key 'customer_id'. If you wish to proceed you should use pd.concat

The error is the helpful case. Converting the numbers back with astype(str) gives "42", which still doesn't match "00042", so every row comes back unmatched. The leading zeros were lost when the file was read. Restore them with str.zfill(5) only if the ID has a known fixed width, or better, read both files with dtype=str (read_csv documentation) so they're never lost. Identifiers, leading zeros and long ids covers this.

Whitespace and case. "A1 " doesn't match "A1", and "b2" doesn't match "B2". Standardize keys into a separate column before the merge, and check the _merge counts afterwards.

Pitfall 6: Clashing column names

When both tables have a non-key column with the same name, pandas keeps both and adds _x and _y:

a = pd.DataFrame({"id": [1], "status": ["paid"]})
b = pd.DataFrame({"id": [1], "status": ["active"]})
print(a.merge(b, on="id", suffixes=("_order", "_customer")))

   id status_order status_customer
0   1         paid          active

The default status_x and status_y are easy to mix up three steps later. Pass suffixes= with meaningful names, or rename or drop the clashing column before the merge. The exercise Joins with clashing column names is built around this.

The six pitfalls at a glance

Pitfall Symptom Check Fix
Default inner join Missing rows, sometimes hidden by duplicated ones Compare totals; count unmatched keys with indicator=True how="left"
Duplicate lookup keys More rows, inflated totals validate="many_to_one" Resolve duplicates with a survivor rule
Null keys match Rows with no key pick up values Count null keys on both sides Merge only rows that have a key
Unseen unmatched rows Missing values in new columns indicator=True Report left_only rows as exceptions
Mismatched key formats Nothing matches, or a dtype error Compare dtypes and sample keys Read keys as text, standardize
Clashing column names _x and _y columns Inspect the merged columns suffixes= or rename first

A merge checklist

Before you trust any merge, check these five things:

  1. How: how="left" if every row of the main table must survive.
  2. Shape: a validate= value that matches the cardinality you expect.
  3. Missing keys: rows with a null key split off before the join.
  4. Matches: indicator=True, with left_only rows reported as exceptions.
  5. Counts: row count and a key total, such as revenue, compared before and after.
import math

assert len(out) == len(orders)
assert math.isclose(out["amount"].sum(), orders["amount"].sum())

Together with the _merge counts, these two assertions catch fan-out and lost rows on every run, not just the run where you happened to look. The guide Checking joins before you trust them has more checks of this kind.

Frequently asked questions

Why does my pandas merge have more rows than the original DataFrame?

The right table has more than one row for some keys, so each matching left row is repeated once per match. Find the repeated keys with right[right.duplicated(key, keep=False)] (DataFrame.duplicated documentation), resolve them, and add validate="many_to_one" so the merge raises an error if it happens again.

Why does my pandas merge drop rows?

merge uses an inner join by default, which keeps only keys present in both tables. Use how="left" to keep every row from the left table, and indicator=True to see which rows found no match.

Does pandas merge match NaN keys?

Yes. If both tables have rows with a missing key, pandas matches them to each other, unlike a standard SQL equality join. Merge only the rows that have a key and append the rest unchanged, or remove null keys from the lookup table first.

What is the difference between merge and join in pandas?

DataFrame.join matches the caller's index, or an on= column, against the other frame's index, and defaults to how="left". merge matches columns (or indexes, with left_index and right_index) and defaults to how="inner". Both accept validate, but only merge has indicator (DataFrame.join documentation). The duplicate-key and missing-key pitfalls apply to both.

Sources