pandas merge: Left Join, Inner Join, and the One That Doubled the Revenue

1. merge in one line

Two frames, one shared column, one call.

orders.merge(products, on="product", how="left")

   order_id product   category  cost
0      1001    Desk  Furniture   140
1      1002   Chair  Furniture    52
2      1003    Desk  Furniture   140
3      1004    Lamp   Lighting    22

shape (16, 11)   revenue 9890

Sixteen rows in, sixteen rows out, and the revenue is unchanged at 9,890. Two columns arrived, category and cost, attached to every order by matching the product name.

Read the arguments as a sentence: take the orders, bring in the products, match on product, and keep every order whether or not it matches. That last part is how, and it is the argument that decides everything.

Those two numbers, the row count and a total, are the whole check. Say them out loud before you run a merge and compare afterwards. Everything on this page is a way for one of them to change when you did not expect it to.

2. The four hows, and the rows they give

Before the explanation: the product table is missing Lamp, and four of the sixteen orders are lamps. Predict the row count for each of the four hows.

how=left    rows= 16   revenue=9890   missing cost=4
how=inner   rows= 12   revenue=8290   missing cost=0
how=right   rows= 12   revenue=8290   missing cost=0
how=outer   rows= 16   revenue=9890   missing cost=4

Four different answers to the same question, and the difference between the first two is 1,600 of revenue, which is the lamp business.

left is the default choice for a reason: it cannot lose a row of your data. inner is the one that catches people, because it is the most natural-sounding thing to want and it deletes silently. Twelve rows came back where sixteen went in, no message appeared, and the four lamp orders are simply not in the result to be missed.

The rule: if the row count went down, an inner join threw something away, and you have to know what.

3. indicator: which side did this row come from

One argument turns "some rows did not match" into a count you can read.

orders.merge(products_missing_lamp, on="product", how="outer", indicator=True)

_merge
both          12
left_only      4
right_only     0

Twelve matched, four orders found no product, and no products went unused. Those three numbers are the shape of the join, and they take one keyword to get.

Use it while developing every merge and then take it out, or keep the column and filter on it, which is how you get the unmatched rows themselves rather than just their count:

m = left.merge(right, on=key, how="outer", indicator=True)
m[m["_merge"] == "left_only"]     # the rows that found no partner

right_only being non-zero is worth a look too. It means the lookup table has entries nothing uses, which is usually harmless and occasionally tells you the key is wrong: a lookup where nothing matches at all comes back as every row on one side and every row on the other.

4. The multiplication

Before the explanation: the product table gains a second row for Desk, identical to the first. It now has four rows instead of three. Predict what the merge returns.

rows    16  ->  23
revenue 9890 -> 14950
desk rows  7 ->  14

Seven extra rows and 5,060 of extra revenue, from adding one row to a lookup table. Nothing was added to the orders, and no order was changed.

The mechanism is exactly what the picture at the top shows. Seven orders are desks; each of them now matches two rows in the product table; each therefore comes out twice. Seven orders became fourteen rows, and every desk order's revenue is counted twice, which is 5,060 counted a second time.

What makes this the most dangerous thing on the page is what does not happen. There is no error and no warning. The frame looks entirely normal, every individual value in it is correct, and any total computed afterwards is wrong in a way that is invisible unless you knew the row count before.

It is also completely ordinary in real data. A product table with one row per product and one per price change. A customer table with a row per address. A lookup that was appended to twice. Each of those is a reasonable table that is not one row per key, and merging to it multiplies.

Say out loud what you would see if this happened on a merge inside a pipeline you did not write. A revenue figure a bit too high, no error anywhere, and every row you spot-check correct.

5. validate: make the merge refuse

pandas will check the cardinality for you and raise rather than multiply.

orders.merge(products, on="product", how="left", validate="m:1")
# clean table: accepted

orders.merge(products_with_duplicate_desk, on="product", how="left", validate="m:1")
# MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

That is the single most valuable argument in this whole function and almost nobody uses it. validate="m:1" says: many rows on the left may match, but each key must appear at most once on the right. It is the assumption you are already making every time you attach a lookup, written down so the computer can enforce it.

Add it to every merge you write. It costs nine characters, it turns an invisible inflation into an exception at the moment it happens, and the exception names the side that is wrong.

6. Column names that collide

If both frames have a column with the same name, and it is not the key, pandas keeps both and renames them.

['units_x', 'units_y']

_x is the left frame and _y is the right. That is a sensible default and a terrible thing to find in a script six months later, because nothing about units_x says which table it came from.

Two better options. Set your own suffixes at the merge, which takes two seconds and reads correctly afterwards:

orders.merge(products, on="product", suffixes=("_order", "_catalogue"))

Or, better still, drop or rename the columns you do not need before merging, so the collision never happens. A merge that brings in three columns you want is much easier to read than one that brings in fifteen and renames four of them.

7. Keys with different names, and different types

When the key column is called something different on each side, name both.

orders.merge(catalogue, left_on="product", right_on="item", how="left")

rows 16, and the result keeps both key columns: ['product', 'item']

Note the second half. Both key columns survive into the result, holding identical values, so the frame has a redundant column. Drop it straight away, or the next person will wonder which one is authoritative.

Types are stricter, and pandas is better behaved here than most tools. Merging a text key to an integer key does not silently match nothing; it raises:

ValueError: You are trying to merge on string and int64 columns for key 'order_id'.

That is genuinely good news, because the same mistake in a spreadsheet lookup produces #N/A on some rows and in SQL produces zero matches, both of which look like a data problem rather than a type problem. Here it stops you. Fix the types, usually by making both sides text if the key is an identifier, which is the argument in how a CSV gets loaded.

What is not caught is a difference in the values themselves: a trailing space, a different case, a code with a leading zero on one side only. Those merge cleanly and match nothing, and the tell is a suspiciously round number of unmatched rows in the indicator column.

8. merge, join and concat

Three functions people confuse, on different jobs.

merge matches on columns. This is the one you want almost always, and everything above is about it.

join is a convenience method that matches on the index by default. It is shorter when your frames are genuinely indexed by the key, and a source of confusion when they are not, because it silently uses the index you forgot you had. If in doubt, use merge and name the columns.

concat does not match anything. It stacks frames, either on top of each other, which is a union of rows, or side by side, which lines them up by index position and is almost never what you want. The check for a vertical concat is arithmetic: the result's row count must equal the sum of the inputs'.

pd.concat([jan, feb, mar], ignore_index=True)      # rows must add up
orders.merge(products, on="product", how="left")   # rows must not change

Picture the pipeline you maintain. How many merges are in it, and for how many of them do you know the expected row count?

Edge cases that break a merge

Six worth knowing.

Nulls in the key. By default pandas matches null to null, so every row with a missing key on the left pairs with every row with a missing key on the right, which is a multiplication waiting to happen. Drop or fill them before merging.

Whitespace and case in the key. Merges cleanly, matches nothing, and shows up only in the indicator counts. Clean the key columns first, exactly as for any other join.

The index after a merge. merge discards the left frame's index and gives you a fresh one, so anything relying on the old index breaks quietly. Reset or reattach on purpose.

Merging in a loop. Repeatedly merging one frame into a growing result is slow and multiplies the risk of the multiplication happening once without being noticed. Build a list and merge once, or reduce over the list checking the count each time.

Memory. A merge produces a new frame with the columns of both, so joining two wide tables can be several times the size of either. Select the columns you need on the right before merging, not after.

Many-to-many on purpose. Sometimes it really is right, for instance expanding an order into its lines. Then say so with validate="1:m" and check the row count against the expected expansion, so the intended multiplication is documented and the unintended one still fails.