pandas merge explained: how, on, indicator and the fan-out trap

The idea

A merge does not glue two tables side by side. It looks at a key column, and for every row on the left it finds the rows on the right with the same key value. Each match becomes one row in the result, with the columns of both sides filled in.

That sentence predicts everything. The result has one row per match, so its length depends on how many times each key repeats. A key that appears once on each side gives one row. A key that appears three times on the right gives three rows, with the left values copied three times. A key that matches nothing gives zero rows or one row of NaN, depending on how=.

Figure 1

customers.merge(orders, on='customer_id', how='left', indicator=True)

customers
customer_id	first_name	city
69	Gary	Chicago
70	Sandra	Tucson
151	Henry	Orlando

orders
order_id	customer_id	channel
1231	69	web
3071	70	web
4230	69	web
11163	70	app

Result
first_name	order_id	channel	_merge
Gary	1231	web	both
Gary	4230	web	both
Sandra	3071	web	both
Sandra	11163	app	both
Henry	NULL	NULL	left_only

Step 1 of 5 Two DataFrames that share customer_id. In customers it is unique, one row per person. In orders it repeats, once per order.

Two DataFrames that share customer_id. In customers it is unique, one row per person. In orders it repeats, once per order.
pandas takes each left row and looks for right rows with the same key. Gary (69) matches orders 1231 and 4230.
One result row per match. Gary's name is copied onto both rows, and _merge says both: the key was found on each side.
Sandra (70) has two orders as well, so she also appears twice.
Henry (151) has no order. how='left' keeps him anyway, with NaN where orders would have filled in. indicator=True labels the row left_only, so you can filter on it.

Why it exists

Harbor & Pine stores each fact once. A customer's city is in customers, an order's channel is in orders, the price of a lamp is in products. That keeps the data correct, but almost every question crosses tables: revenue by category needs order_items and products; customers who never ordered need customers and orders.

Without merge you would copy values by hand, or write a loop and look up each row. A merge does the lookup for every row at once and leaves you with one DataFrame to group, filter or chart.

It also keeps the sources honest. When Marketing says a report is wrong, you can trace the number back: which tables went in, on which key, and how many rows came out. A merge is a short, readable statement of how two pieces of data relate, and that is worth more than the lookup itself. The price is that the relationship has to be right, and the next section is about checking it.

How it works under the hood

Step by step

Pick the key. on='customer_id' when both tables use the same name, left_on= and right_on= when they do not.
Match. Every left row is paired with every right row that has the same key.
Handle misses. how='inner' (the default) drops rows without a match on either side. how='left' keeps every left row, how='right' every right row, how='outer' both.
Resolve name clashes. If both tables have a column called unit_price, pandas keeps both and adds _x and _y. Pass suffixes=('_item', '_product') to choose better names.

indicator=True adds a _merge column that says where each row came from: both, left_only or right_only. Filter on left_only after a left merge and you have the customers who never ordered. A plain NaN could also mean "matched, but the value is missing", so the indicator is the safe test.

The grain decides the row count

The grain of a table is what one row stands for. orders: one order. order_items: one order line. customers: one customer. Before any merge, say the grain of both sides out loud. Customers to orders is one-to-many. Orders to order lines is one-to-many. Order lines to products is many-to-one, because each line points at a single product: a many-to-one merge never changes the number of rows. A many-to-many merge, where the key repeats on both sides, multiplies: 3 matches on the left times 4 on the right is 12 rows.

Fan-out

When the right side has many rows per key, the left table's columns are repeated. Counting or summing right-side columns is still correct. Summing a column that belongs to the left table is not.

Figure 2

orders.merge(order_items, on='order_id'): one fee, three lines

orders
order_id	channel	shipping_fee
1049	web	4.99

order_items
order_item_id	order_id	quantity
139	1049	2
140	1049	2
141	1049	3

Merged
order_id	order_item_id	shipping_fee
1049	139	4.99
1049	140	4.99
1049	141	4.99

Step 1 of 4 Order 1049 paid shipping once: $4.99. The fee lives on the order, because shipping is charged per order.

Order 1049 paid shipping once: $4.99. The fee lives on the order, because shipping is charged per order.
The same order has three rows in order_items. A merge on order_id finds three matches.
Three matches, three rows. The fee is copied onto each of them. Every column from the left table is repeated once per match.
Run .sum() on the merged shipping_fee and this order counts for $14.97. Across all 12,000 orders, the real $32,016.06 becomes $58,782.63.

Picture a finance colleague comparing your total with the payment provider's: yours is 80% higher and nobody can say why. Nothing errors and the number looks plausible, which is why this bug survives into reports. There are two cures. Aggregate the per-order column on orders before merging, or reduce the right table to one row per key first with groupby. And let pandas check you: validate='one_to_many' fails loudly if the left key repeats, validate='many_to_one' if the right key does, and 'one_to_one' if either does.

Figure 3

The habit that keeps a merge honest

Know the grain one row per what?
len(orders) 12,000
orders.merge(order_items, on='order_id', validate='one_to_many')
len(merged) 30,338
Aggregate only columns of the finest table

Step 1 of 4 First decide what one row means in each table. orders is one row per order, order_items one row per order line.

First decide what one row means in each table. orders is one row per order, order_items one row per order line.
Count the rows before. validate='one_to_many' makes pandas raise an error if the left key is not unique, so a wrong assumption stops the script.
The count after is 30,338, the number of order lines. That matches a one-to-many merge. If it had been 12,000 or 40,000, you would stop and look.
Only now add up. Revenue comes from the order lines. A per-order column like shipping_fee must be summed on orders itself, before the merge.

Keys must be the same kind of value

A merge matches equal values, so '101' as text will not match 101 as a number, and ' Texas' will not match 'Texas'. If a left merge fills everything with NaN, check df.dtypes on the key columns first, then clean the text. The same goes for NaN keys: web_sessions.customer_id is empty for anonymous visitors, and pandas will match those empty keys to each other if the other side has them too. Drop them first.

Common misconceptions

"A left merge returns as many rows as the left table"

It returns at least as many, as long as the keys are unique on the right. A left merge of customers with orders has 12,247 rows, not 2,018. The left table decides which rows are kept, not how often they appear. Compare len() before and after every merge.

"If the merge ran without an error, the numbers are right"

pandas has no way to know that you meant one row per order. Fan-out, wrong keys and mismatched types all run without a warning. The check is yours: row count against the grain, indicator=True for the unmatched rows, and validate= to put your assumption in the code.

"NaN after a merge means the data is missing"

After a left merge, NaN in right-side columns mostly means "no matching row". Henry's missing order_id is not a gap in the database, it is a customer with no orders. Decide which it is before you fill it: a customer with no orders has zero revenue, a real answer, while a product with no cost is a data problem.

Check yourself

One question. Answering it ticks this lesson off on the map; it never locks a level.

customers has 2,018 rows and 247 of those customers have never ordered. What can you say about customers.merge(orders, on='customer_id', how='left')? It has exactly 2,018 rows, one per customer. It has at least 2,018 rows: one per order for customers who ordered, plus one row with NaN for each of the 247 who did not. It has only the customers who ordered, because a key must exist on both sides. It has 12,000 rows, one per order, and the 247 customers are dropped.