Merging Two Messy CSVs: The Key Problems Excel Formulas Hide From You
You have two spreadsheets. One is your export, one is the client's. Someone says "just merge them on the ID column." That sentence has caused more quiet data disasters than any schema migration, because it assumes three things that are never true: the IDs match, the columns mean the same thing, and the rows are unique.
I reconcile exported data files for a living — chat logs, server logs, marketplace exports — and the merge is where the bodies are buried. Here are the failure modes, in the order they will find you.
1. The Key Is Not a Key
"Same ID column" usually means: one file has id, the other has customer_id, order.customer, and a vlookup_key someone added by hand. Open both, count distinct values, and compare sets before you merge:
a_ids = set(df_a["id"])
b_ids = set(df_b["customer_id"])
print(len(a_ids), len(b_ids), len(a_ids & b_ids))
print("orphans A:", len(a_ids - b_ids), "orphans B:", len(b_ids - a_ids))
If the intersection is 91%, not 100%, your merge is a research project, not a formula. Excel's VLOOKUP returns #N/A and moves on; a pandas merge silently picks a join type for you. You must count the misses, not spot-check them.
2. Invisible Keys
The intersection is computed on strings. "A102" and "A102 " — trailing space from a CRM export — are different strings. "42" and "42.0" — one file went through a spreadsheet app that cast numbers — are different strings. The fix is boring and mandatory: trim, cast, and casefold both sides before joining, then re-count. Ninety percent of "impossible" 4% miss rates are whitespace.
3. Uniqueness Is a Claim, Not a Fact
Run df["id"].is_unique on both sides. If the right side has duplicates, your 1,000-row left table becomes 1,400 rows after the merge, and every downstream sum is inflated by 40%. In Excel you see it as weird double-counting you can never explain. In code, the assertion is one line, and it fails loudly.
4. The Merge Type Is a Business Decision
Inner drops unmatched rows. Left keeps your rows and nulls theirs. Outer keeps everything and doubles your review work. There is no default — there is only what the deliverable promises. A reconciliation report is outer-merge, annotate. A clean master table is inner-merge, log the drops. Pick before you write the line, and record the count of rows that fell out the bottom of every join you do.
5. Dates Are Four Formats in a Trench Coat
2026-1-5, 01/05/2026, 5 Jan 26, and a serial number 46027 are the same date in four costumes, and the file with the serial numbers does not tell you it is wearing one. Parse with explicit formats; never trust auto-detection to pick month-first. 03/04/2026 is unambiguous to no one.
The Workflow That Survives
Load both files raw. Normalize (trim, cast, casefold keys; explicit date parse). Assert uniqueness. Merge with an explicit how and an indicator. Count before, after, matched, dropped — all four, in the log, every run.
Total: one screen of pandas, zero #N/As, and a number you can hand the client when the totals do not match: "14 rows in their file had no match on our side; here they are." That sentence — not the merge itself — is what you are being paid for.
Top comments (1)
Official Platform Update
Security protocols have been updated for all developer accounts.