Orders live in one table, customers in another, products in a third — because that's how databases stay sane. But your analysis needs all three at once: 'revenue by customer segment by product category' touches every table. merge is how separate tables become one answer — and it's also where careless analyses silently lose 20% of their rows or double-count half their revenue.
What you'll learn
What
pd.merge combines two DataFrames by matching rows on key columns, like SQL joins: inner keeps only matched rows, left keeps everything from the left table, right mirrors that, and outer keeps everything from both. The how, on/left_on/right_on, validate, and indicator parameters control and audit the match.
Why
Real data is relational — facts about the same entities scattered across tables. Almost every real analysis starts by joining them. Joins are also the top source of silent data corruption in analytics: lost rows from mismatched keys and duplicated rows from fan-out don't error, they just produce wrong numbers.
Where it's used
Enriching transactions with customer attributes, attaching product metadata to sales, combining API results with internal records, building model feature tables from many sources — every star-schema analysis ever.
Where this runs in production
Sales facts join to product, store, and date dimension tables for every report — the same left-joins analysts write in pandas, run at hundreds-of-millions-of-rows scale.
Labs, prescriptions, and visits live in separate systems keyed by patient ID; clinical analytics join them — with strict validation, because a fan-out that duplicates a dosage row is a safety issue.
Ad clicks, email opens, and deals close in different tools; attribution joins them on contact ID, and the unmatched remainder — clicks with no contact — is itself a tracked metric.
orders = pd.DataFrame({'order_id':[1,2,3], 'cust':['A','B','X'], 'amt':[100,200,50]})
custs = pd.DataFrame({'cust':['A','B','C'], 'city':['Pune','Delhi','Goa']})
pd.merge(orders, custs, on='cust', how='inner') # 2 rows: A, B
pd.merge(orders, custs, on='cust', how='left') # 3 rows: X gets NaN city
pd.merge(orders, custs, on='cust', how='outer') # 4 rows: X and C both keptOne pair of tables, three different results. Order X has no customer record; customer C has no orders. Which rows survive is entirely the how= parameter — and therefore a business decision, not a technicality.
inner: only keys present in BOTH tables — silently drops non-matches on either side. left: every left row survives; missing right-side values become NaN — the default for enrichment ('keep all my orders, attach customer info where known'). right: the mirror. outer: union of keys, NaN where either side is absent — the audit join, because nothing disappears.
If the 'lookup' table has 2 rows for customer A, every A-order appears TWICE after the merge — and revenue double-counts. No error, no warning. Defences: check key uniqueness before merging (custs['cust'].is_unique), pass validate='many_to_one' so pandas RAISES if the right side has duplicate keys, and compare row counts before vs after — a left join should never grow the left table's row count unless you expected many-to-many.
merged = pd.merge(orders, custs, on='cust', how='left',
validate='many_to_one', # raise if custs has dup keys
indicator=True) # adds _merge column
print(merged['_merge'].value_counts().to_dict())
# {'both': 2, 'left_only': 1, 'right_only': 0}The two audit tools. validate encodes your assumption about key multiplicity and turns violations into loud errors. indicator adds a _merge column telling each row's provenance — value_counts on it is the one-line merge health report.
# Different key names on each side
pd.merge(sales, users, left_on='customer_id', right_on='id')
# Same-named non-key columns get suffixes
pd.merge(a, b, on='key', suffixes=('_2025', '_2026'))
# df.join: index-based convenience wrapper
totals.join(counts) # matches on indexleft_on/right_on when the key has different names (drop the redundant duplicate column afterwards). suffixes disambiguates same-named columns — set them meaningfully instead of accepting _x/_y. df.join is merge's index-matching convenience sibling.
One cabinet holds order slips, another holds customer cards, both filed by customer number. An inner join keeps only slips with a matching card — orphan slips go in the bin. A left join keeps every slip, stapling on the card when found and a blank card when not. And if someone mis-filed two cards under the same number, every matching slip gets photocopied — one copy per card. That photocopier is fan-out, and it runs silently.
Orders {A, B, X} × Customers {A, B, C} — click each join to see who survives.
Select a type to see its full definition, operations, and data science usage.
Attach each order's customer city from a lookup table.
A fact table (orders) and a clean lookup (one row per customer).
Your task
Enrich a rides table with driver info, quantify the unmatched rides, and compute revenue per driver rating — auditing as you go.
4
1
{4.5: 250, 4.8: 300}Write your solution in the editor on the right, then hit Run.
In a LEFT join of orders (left) to customers (right), an order whose customer isn't in the customer table:
What does indicator=True add to a merge result?
A left join of a 10,000-row fact table to a 'lookup' produced 11,400 rows. What happened?
What's wrong with this picture?
Merge the two tables on their differently-named keys, then print the number of employees whose department has no budget record. emps = pd.DataFrame({'name':['ana','raj','kim','lee'], 'dept_id':[10, 20, 30, 10]}) budgets = pd.DataFrame({'department':[10, 20], 'budget':[500, 300]})
Two tables both have an int-looking customer key, but the merge matches zero rows. The most likely culprit?
Explain the four join types and how you choose among them.
All four match rows by key; they differ in who survives a non-match. Inner keeps only keys present in both tables — clean but lossy, and the loss is silent. Left keeps every left row, attaching NaN where the right side is absent; right mirrors it. Outer keeps the union with NaN on both sides' gaps. My choice follows the question's grammar: if the left table holds the facts I'm counting — orders, sessions, patients — I default to LEFT, because losing facts to an incomplete lookup corrupts totals. Inner is for when unmatched rows are genuinely out of scope, and I say so explicitly in the analysis. Outer plus indicator is my audit tool for understanding two tables' overlap before committing. The habit that matters more than the choice: after any join, account for the row count — I should be able to explain exactly why the output has the rows it has.
What is join fan-out, when does it happen, and how do you defend against it?
For each key value, a merge outputs the cross product: left rows with that key times right rows with that key. When the right table is a proper lookup — one row per key — that's ×1 and invisible. When a key repeats on the right, every matching left row is duplicated once per repeat, and downstream sums and counts inflate with no error raised. It bites when 'lookups' aren't as unique as assumed: duplicate customer records, a product table with one row per variant instead of per product, or a slowly-changing dimension holding history. Three defences, in order: check assumptions before joining (right_df[key].is_unique, or duplicated().sum()); encode the assumption in the merge itself with validate='many_to_one' so violations raise MergeError instead of corrupting silently; and reconcile after — a left join must not change the left table's row count, so assert len(merged) == len(left). In pipelines I keep all three: the assert catches what future data breaks.
A left join is losing matches you know should hit. How do you debug it?
Mismatches are almost always key hygiene, so I work through the usual suspects in order of frequency. First dtypes: print both key columns' dtypes — int64 versus object is the classic, where 1 never equals '1'; align with astype. Second, string cleanliness: whitespace and casing — ' A123' versus 'A123' — fixed by normalising BOTH sides with the same strip/lower treatment. Third, NaN keys: NaN matches nothing, so null-keyed rows are guaranteed misses; count them separately. Fourth, encoding-level gremlins if the data crossed systems: non-breaking spaces, zero-width characters — visible via repr() of sample values, not via print. To localise, I take a handful of keys that should match, pull them from both tables, and compare their repr side by side; the difference is usually visible immediately. Then I quantify with an outer join + indicator to see whether the misses are a random sprinkle (data entry noise) or a systematic block (one source file, one date range) — because the fix differs: cleaning rules for the former, an upstream conversation for the latter.
Common Mistakes to Avoid
1) Defaulting to inner join and silently losing unmatched facts — enrichment wants LEFT. 2) Never checking row counts before vs after — a left join that grew has fan-out; one that shrank wasn't a left join. 3) Skipping validate= on joins to 'lookups' you didn't verify are unique. 4) Merging on keys with mismatched dtypes (int vs string) and getting zero matches. 5) Accepting _x/_y suffixes instead of naming them meaningfully. 6) Forgetting NaN keys can never match — count them before blaming the merge. 7) Chaining several joins with no per-hop assertions, then debugging the final number instead of the hop that broke it.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: the four joins with the filing-cabinets story.' • 'Show me fan-out corrupting a revenue sum, then validate catching it.' • 'Quiz me: pick the right how= for scenarios you invent.' • 'My join matches zero rows — walk me through the debugging checklist.' • 'Interview mode: make me defend a left-vs-inner choice in a sessions report.'
Glossary
pd.merge — combine two DataFrames by matching key values. how — join type: inner (intersection), left/right (keep one side), outer (union). on / left_on / right_on — key column(s), same-named or per-side. Fan-out — row multiplication from duplicate keys on the joined side. validate — assert key multiplicity ('one_to_one', 'many_to_one'…); raises MergeError on violation. indicator — adds _merge provenance column (both/left_only/right_only). suffixes — disambiguate same-named non-key columns. df.join — index-based merge convenience. Fact/dimension table — the many-rows events table and the one-row-per-entity lookups it joins to.
Recommended Resources
• Docs: pandas user guide 'Merge, join, concatenate and compare' — merge semantics, validate, and indicator in depth. • Read: any star-schema primer (fact vs dimension tables) — it's the data model your joins will meet in every warehouse. • Practice: build two small tables with deliberate orphans and a duplicate key; run all four joins, predict each row count BEFORE running, and verify with indicator. • Next in DSM: joins combine tables at matching rows — Window Functions compute over neighbouring rows within one table: rolling averages, cumulative sums, and shifts.
Recap
✓ merge matches rows by key; how= decides who survives a non-match — a business decision, not a technicality. ✓ Default to left joins for enrichment: keep every fact, take NaN where the lookup is silent. ✓ Output rows per key = left count × right count — duplicate lookup keys mean fan-out and inflated sums. ✓ validate='many_to_one' turns your uniqueness assumption into a loud MergeError; indicator gives the match report. ✓ Zero matches? Check key dtypes, whitespace/casing, and NaN keys before anything else. ✓ Chain joins with per-hop row-count assertions, not end-of-pipeline debugging. Next up: Window Functions (rolling, expanding). Joins looked ACROSS tables — next you'll look along ordered rows within one table: moving averages, cumulative totals, and comparing each row to the one before it.
Run your code to see the output here.