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.
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.
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
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
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
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
- Is the key unique where it should be?
products['product_id'].is_unique - Same type and cleaned text on both sides?
validate=on the merge.- Row count before and after (
len). - Unmatched rows with
indicator=True, and why they didn’t match. - 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:
| order | product | revenue |
|---|---|---|
| 1 | P1 | 60 |
| 2 | P2 | 40 |
| product | category |
|---|---|
| P1 | Tea |
| P1 | Tea |
| P2 | Cups |
| order | product | revenue | category |
|---|---|---|---|
| 1 | P1 | 60 | Tea |
| 1 | P1 | 60 | Tea |
| 2 | P2 | 40 | Cups |
validate='many_to_one' raises MergeError: right keys are not unique.products.drop_duplicates('product', keep='last'), join again: 2 rows, revenue 100.5. 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
- After a left join, the row count rose from 9,717 to 9,940. What’s the likely cause?
- Which
validatevalue fits joining order lines to customers? - Product codes are
'00123'in one table and123in the other. How do you fix it?
Answers
- Duplicate keys in the right-hand table; find them with
right[right.duplicated('key', keep=False)]. 'many_to_one': many lines per customer, one row per customer in the lookup.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
Sources
- pandas documentation, User Guide: Merge, join, concatenate and compare https://pandas.pydata.org/docs/user_guide/merging.html
- McKinney, W. (2022), Python for Data Analysis, 3rd edition, O'Reilly (open access) https://wesmckinney.com/book/
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.