Combining datasets is essential, and one of the easiest places to introduce errors.
Types of Joins
- Inner join: keep only rows with matching keys in both tables.
- Left join: keep all rows from the left table, adding matches from the right where they exist.
- Right join: the reverse.
- Full outer join: keep all rows from both, matched where possible.
Choose Keys Carefully
Join on stable, unique identifiers. Names, descriptions and free-text fields make poor keys because of spelling and formatting differences.
Check Key Uniqueness
If a key appears multiple times in both tables, a join multiplies rows. One customer with three addresses joined to three orders produces nine rows. Check uniqueness before joining, and state the expected relationship (one-to-one, one-to-many).
merged = orders.merge(customers, on="customer_id", how="left", validate="many_to_one")
Check for Lost Rows
After an inner join, count rows before and after. Unmatched keys disappear silently. Use an indicator of match status to see what didn't join and why.
Standardise Keys First
Trim whitespace, align case, fix leading zeros and match types (a numeric ID in one table and text in the other won't match).
Time-Aware Joins
When joining events to reference data that changes over time — prices, customer segments — join to the version valid at the event's time, not the current version.
Verify Results
Check row counts, totals and a few records by hand after every important join.