Matching on a Key

The idea is simple and the failures are not. Build a lookup from one dataset keyed by the shared identifier, then walk the other and match.

Always count what did not match. A join that silently drops rows is the classic invisible error in data work: the result looks entirely reasonable and is missing a third of the population.

Four reasons a join loses rows.

Types differ. The number 42 and the string "42" do not match.

Whitespace or case differ on a text key.

A leading zero was lost when an identifier passed through a spreadsheet.

You joined on a name rather than an id, and names differ by spelling, punctuation and accents.

When a join loses rows, print a handful from each side and compare them character by character. The answer is almost always visible immediately and almost never what you assumed.

✦ Duplicate keys multiply rows

If the lookup side has two rows with the same key, every matching row on the other side becomes two rows. Counts then rise for no visible reason, and the total no longer means what you think. Check both sides for duplicate keys before joining, not after the numbers look odd.