All lessons

Module 5: Combining tables · Lesson 5.3 · about 20 min

Join pitfalls: duplicates, keys and validate

The join mistakes that silently inflate totals: duplicate keys that multiply rows, keys with different types or spelling, and missing matches. How validate, indicator and row-count checks catch them.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 5 · about 20 minutes · uses Lesson 5.2. You’ll be able to spot and prevent the join mistakes that inflate or lose rows.

A join can quietly change your totals. Repeated keys multiply rows; mismatched keys lose them. Check the row count and tell pandas what you expect.

In everyday terms: a phone book with two entries for the same person. Look up every caller and that person’s calls get counted twice, once per entry.

1. Duplicate keys multiply rows

Python

The lookup table had P1 twice (an old and a new category name), so P1’s orders doubled and revenue went from 60 to 100. Nothing raised an error.

2. Make pandas check for you

Python

validate takes 'one_to_one', 'one_to_many', 'many_to_one' or 'many_to_many' and raises a clear error when the data doesn’t fit. Use it on every join where you expect a lookup to be unique. Then fix the lookup (keep one row per key, Lesson 3.2) and join again.

3. Keys that should match but don’t

Python

Also check types: 101 and '101' never match. Convert both sides with astype(str) (and str.zfill for leading zeros) before joining.

4. A join checklist

  1. Is the key unique where it should be? products['product_id'].is_unique
  2. Same type and cleaned text on both sides?
  3. validate= on the merge.
  4. Row count before and after (len).
  5. Unmatched rows with indicator=True, and why they didn’t match.
  6. A total (revenue) before and after; it shouldn’t change on a left join to a lookup.
Duplicate key on the right
Each left row matching it appears once per copy: rows multiply.
validate='many_to_one'
Raises an error if the right-hand key is not unique.
Type or spelling mismatch
'S01' vs 's01 ' or 101 vs '101' never match.
len before and after
The cheapest test: a left join on a unique key keeps the row count.
Physical meaning
Revenue after a join should equal revenue before it. If a product appears twice in the product table, every order of it is copied, and the sum rises by exactly that product's revenue.

“If the code runs, the join worked.” A many-to-many join runs without complaint and can double your revenue. pandas only checks when you ask with validate=.

Watch it happen, one step at a time:

Orders (revenue 100).
orderproductrevenue
1P160
2P240
Products: P1 is listed twice (an old and a new row).
productcategory
P1Tea
P1Tea
P2Cups
Left join.
orderproductrevenuecategory
1P160Tea
1P160Tea
2P240Cups
Revenue now sums to 160.
Catch it. validate='many_to_one' raises MergeError: right keys are not unique.
Fix. products.drop_duplicates('product', keep='last'), join again: 2 rows, revenue 100.

5. Your turn

Your turn
How it works

Principle: a join outputs, for each key, (left rows with that key) × (right rows with that key). With a copies on the left and b on the right, you get a × b rows. A lookup table must have b = 1, or every matched row repeats b times.

Many-to-many joins are sometimes right (students × courses through an enrolments table), but almost never when adding details to a fact table. If you see one by accident, totals are wrong.

6. Practice

  1. After a left join, the row count rose from 9,717 to 9,940. What’s the likely cause?
  2. Which validate value fits joining order lines to customers?
  3. Product codes are '00123' in one table and 123 in the other. How do you fix it?
Answers
  1. Duplicate keys in the right-hand table; find them with right[right.duplicated('key', keep=False)].
  2. 'many_to_one': many lines per customer, one row per customer in the lookup.
  3. df['code'] = df['code'].astype(str).str.zfill(5) on the numeric side.

New words: duplicate key, row explosion, validate, many-to-one, many-to-many, key hygiene.

  • Duplicate keys on the lookup side multiply rows and inflate sums.
  • Pass validate=‘many_to_one’ (or one_to_one) so pandas checks for you.
  • Clean keys first: same type, strip spaces, same case.
  • Compare row counts and a key total before and after.

Check yourself

1A product appears twice in the products table. After a left join, what happens to its order lines?
2What does validate='many_to_one' do?
3Keys are 101 (number) in one table and '101' (text) in the other. What happens?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.