PD-01-015-P · Primer — Merging & Joining Tables
The idea in one sentence
Merging stitches two DataFrames into one by lining them up on a shared join key — a column they have in common — and the how setting decides which rows survive when a key on one side has no partner on the other.
Why it exists
Real data almost never lives in one table. Your orders sit in one place with a customer_id on each row; the customers' actual names, cities, and signup dates live somewhere else, keyed by that same customer_id. Keeping them apart is deliberate — it avoids repeating "Ada, Paris, joined-2019" on every one of Ada's thousand orders — but the moment you want to ask a question that spans both ("what's the total spend per city?"), you need them side by side. merge is the tool that does that lining-up: it walks the shared key, finds each order's matching customer, and glues the columns together into one wide table. Without it you'd be looking things up by hand, row by row; with it, you connect two tables in a single line. This is the single most important operation for turning scattered tables into answers, which is exactly why it shows up in nearly every real analysis and interview.
How it works
You call left.merge(right, on="key", how=...): pandas takes each row on the left, finds every row on the right that shares the same value in the join key column, and pastes their columns together into one combined row. The on= argument names the shared column (here customer_id); rows only pair up when their key values are equal. The how= argument is the whole game — it decides what happens to a key that exists on one side but not the other. how="inner" (the default) keeps only matching rows: a key must appear on both sides or it's dropped. how="left" keeps every left row and fills the right-hand columns with NaN when there's no match. how="outer" keeps everything from both sides, padding with NaN wherever a partner is missing. Watch one pair of tables run through all three:
import pandas as pd
orders = pd.DataFrame({
"order_id": [1, 2, 3, 4],
"customer_id": [10, 11, 10, 99], # 99 has no customer record
"amount": [50, 30, 80, 20],
})
customers = pd.DataFrame({
"customer_id": [10, 11, 12], # 12 never placed an order
"name": ["Ada", "Bo", "Cy"],
})
print(orders.merge(customers, on="customer_id", how="inner"))
# order_id customer_id amount name
# 0 1 10 50 Ada
# 1 2 11 30 Bo
# 2 3 10 80 Ada
# (order 4 dropped: customer 99 isn't in customers)
print(orders.merge(customers, on="customer_id", how="left"))
# order_id customer_id amount name
# 0 1 10 50 Ada
# 1 2 11 30 Bo
# 2 3 10 80 Ada
# 3 4 99 20 NaN <- kept; no name to fill
print(orders.merge(customers, on="customer_id", how="outer"))
# order_id customer_id amount name
# 0 1.0 10 50.0 Ada
# 1 3.0 10 80.0 Ada
# 2 2.0 11 30.0 Bo
# 3 NaN 12 NaN Cy <- customer with no order
# 4 4.0 99 20.0 NaN <- order with no customerSame two tables, same key — only how changed, and the row count went from 3 (inner) to 4 (left) to 5 (outer).
The one gotcha
A many-to-many key silently multiplies your rows — merge gives you every matching pair, not one row per left row. The mental model people carry is "merge adds columns," and with a clean one-to-one key it does. But if the key value 10 appears twice on the left and twice on the right, the merge produces 2 × 2 = 4 rows for that key, because it faithfully lines up every left-10 with every right-10. Do that on a key you thought was unique — an email that's duplicated, an id that repeats — and your table quietly inflates, so a later .sum() on amount double-counts and your totals come out too high with no error to warn you. Two habits defuse this: before merging, check right["customer_id"].is_unique (and the left key too) so you know the shape you're about to create, and after merging, glance at len(result) — if it's larger than your left table when you expected a lookup, a duplicate key multiplied you. Also mind the flip side: how="left" and how="outer" introduce NaN for missing partners, and — as the 1.0, 50.0 above show — that promotes neat integer columns to float, because integer columns have no way to store a missing value.
See it — one order row flowing through inner vs left vs outer
flowchart TD
A["order row<br/>customer_id = 99<br/>(no matching customer)"] --> B{"how = ?"}
B -->|"inner"| C["dropped<br/>(needs both sides)"]
B -->|"left"| D["kept, name = NaN<br/>(all left rows survive)"]
B -->|"outer"| E["kept, name = NaN<br/>(everything survives)"]
F["customer row<br/>id = 12 (no order)"] --> G{"how = ?"}
G -->|"inner / left"| H["dropped<br/>(not on left side)"]
G -->|"outer"| I["kept, amount = NaN<br/>(both sides survive)"]
The one-line takeaway
left.merge(right, on="key", how=...) joins tables on a shared key — inner keeps only matches, left keeps all left rows, outer keeps everything — so pick how by asking "which unmatched rows do I refuse to lose?"
Where you'll meet it
You'll meet merge the instant your analysis needs two tables at once: attaching customer names to an orders table, pulling product prices onto a line-items table, or joining this month's numbers to last month's to compute change. You'll meet the how choice every time there's a mismatch you care about — a left join to keep all orders even from customers you have no record of, or an outer join to audit both "orders with no customer" and "customers with no orders" in one shot. You'll meet the many-to-many gotcha the first time a report's revenue looks impossibly high and the fix is spotting a duplicated key that doubled the rows. And you'll meet all of it in interviews, where "join these two tables and total the spend per city" is a canonical question — the answer is one merge, the right how, and a quick check that your key was as unique as you assumed.
Named concepts: merge, join keys, how (inner/left/outer), matching rows