Three flavours. (1) A merge returns MORE rows than the left table had — 4,000
transactions become 9,000, and every total downstream double-counts. (2) The merge
returns the right row count, but every column that came from the right table is NaN.
(3) The merge refuses to run at all:
ValueError: You are trying to merge on int64 and str columns for key 'pin'
(older pandas says int64 and object).
Joining a transaction table to a master list — stores to cities, employees to departments, PIN codes to districts. The master was exported from Excel or typed by hand, so it carries duplicates, stray spaces, or a different idea of what a PIN code is. Flavour 1 ships to production most often because nothing errors and the report still looks plausible. Linked module: 0.5 (Pandas).
All three flavours are key problems. Flavour 1 is duplicate keys on the right: merge
pairs EVERY matching row from both sides, so one left row matching three master rows
becomes three output rows — row growth is the fingerprint. Flavour 2 is keys that
never match in VALUE: "S2" versus "s2 ", or "682001" versus "KL-682001" —
a left join then keeps all left rows but fills the right columns with NaN. Flavour 3
is keys that differ in DTYPE: 682001 as int64 in one table, "682001" as text in
the other; pandas 3 raises, older pandas quietly produced flavour 2. Diagnose with
four lines before blaming merge: left.shape and right.shape;
right["key"].is_unique; left["key"].dtype and right["key"].dtype; and a spot
check like set(left.key) & set(right.key) to see whether ANY values overlap.
Flavour 1: deduplicate the master on the key — right.drop_duplicates(subset="store")
— after checking WHICH duplicate is correct, then re-merge. Flavour 2: normalise both
key columns first — .str.strip(), matching case, one shared format — then re-merge
and count the NaN that remain. Flavour 3: cast both keys to one dtype, usually text —
astype(str) on both sides — because IDs and PIN codes are labels, not quantities.
In every flavour, add validate="many_to_one" to the merge call so pandas itself
errors the next time the master grows a duplicate, and assert the row count matches
the left table after a left join.
Print shapes before and after every merge — one line, catches flavour 1 instantly.
Pass validate= on every merge that has a "master table" side; it is a free unit
test. Keep identifier columns (PINs, account numbers, store codes) as strings from
the moment of loading, since they are names that happen to use digits. Treat a NaN
flood after a join as a key mismatch until proven otherwise — the data is rarely
actually missing.
Q: "After joining transactions to the store master, revenue doubled. Walk me through
your debugging." Strong answer: name duplicate join keys as the prime suspect, give
the row-count fingerprint (output rows exceed left rows), show the is_unique check,
and fix with dedupe plus validate="many_to_one". Finishing with the dtype trap —
int PIN in one table, text PIN in the other — signals you have joined real, messy
Indian datasets, not textbook ones.