Joining Tables — merge() and the Join-Key Gotchas

SQL joins, in Pandas dialect

pd.merge(left, right, on='key', how='inner|left|right|outer') implements the same four join types you know from SQL, plus one extra (cross). It's the workhorse for combining tables, and it's also where the highest-impact silent bugs live, because Pandas does not warn you when a join changes the row count in a way that's almost certainly wrong.

The four classic gotchas

Implicit one-to-many fan-out. If the right table has duplicates on the key, an inner/left join fans out left rows. A 1,000-row left table can become 8,500 rows after the join — and you might not notice until your revenue triples.
Type mismatch on the key. Joining a string '42' against an integer 42 matches nothing. customer_id as int64 on one side and string[pyarrow] on the other is a real-world classic.
Whitespace and case mismatches. 'NewYork' and 'New York' don't join. 'NEW YORK' and 'New York' don't join. Strip and normalize before merging.
Many-to-many that becomes silent Cartesian. The same key on both sides duplicated multiple times multiplies rows. Validate cardinality before you merge.

Pandas exposes a validate= argument on merge precisely for the cardinality gotchas. Always use it. validate='one_to_one', 'one_to_many', or 'many_to_one' raises an explicit error if the relationship doesn't hold — which is what you want.

Code

Defensive merge — assert cardinality, log row counts·python

import pandas as pd

orders = pd.DataFrame({
    'order_id':    ['O1', 'O2', 'O3', 'O4'],
    'customer_id': ['C100', 'C100', 'C200', 'C300'],
    'amount_usd':  [100, 200, 50, 75],
})

customers = pd.DataFrame({
    'customer_id': ['C100', 'C200', 'C300'],
    'country':     ['KR',   'US',   'JP'],
    'tier':        ['gold', 'silver', 'bronze'],
})

# Defensive merge — explicit how + validate cardinality
joined = orders.merge(
    customers,
    on='customer_id',
    how='left',
    validate='many_to_one',     # raises if customers has duplicate customer_id
)

# Always log the row-count delta — it's the cheapest sanity check that exists
assert len(joined) == len(orders), 'unexpected row count change after join'

# Defensive normalization — strip and lower the join key on both sides first
# orders['customer_id']    = orders['customer_id'].str.strip()
# customers['customer_id'] = customers['customer_id'].str.strip()

Joining Tables — merge() and the Join-Key Gotchas

SQL joins, in Pandas dialect

The four classic gotchas

Code

External links

Exercise

Progress

Comments 0