SQL-style joins - the four kinds, and the duplicate key that silently multiplies your rows.
Overview
The four kinds
Merging is how you combine two DataFrames that share a key column but hold different information. One frame has customer ids and names. Another has customer ids and orders. A merge on the id column produces a single frame with names and orders side by side. That is the same operation as a SQL join, and pandas borrows the vocabulary.
The four join types decide what happens to keys that appear on only one side. The choice between them is the single most important decision in a merge, and the default is the one that causes the most silent data loss.
An inner join keeps only keys present in both frames. Rows from either side that have no match are discarded. That means the output can have fewer rows than either input. If you expected to keep every customer and just add order data, an inner join quietly drops the customers who never ordered.
A left join keeps every row from the left frame and fills with NaN where the right frame had no match. This is usually what you want when enriching a table with a lookup. The row count of the result matches the left input, unless keys are duplicated on the right side.
A right join is the mirror image. An outer join keeps every key from both sides.
Duplicate keys are the other major hazard. If a key appears three times on the left and twice on the right, the merge produces six rows for that key. The result is larger than either input, and nothing warns you. Checking the row count after a merge catches this immediately.
This module covers merge with all four join types, single and multiple keys, indicator for diagnosing unmatched rows, and validate for catching duplicate keys before they multiply your data.
merge mixes two frames on one or more key columns. how decides what happens to keys that appear on only one side.
inner — only keys present in both. This is the default. It means unmatched rows are silently discarded.
left — each row from the left frame, with NaN where the right had no match. This is often what you want when enriching a table with a lookup: you're adding data, not filtering.
right — the mirror image, and rarely used, since swapping the operands is clearer.
outer — each key from either side.
The default being inner is worth remembering. A join that was meant to add a column can quietly drop rows. The only sign is a row count you weren't checking.
Worth knowing
how= picks the join: inner (the default, which silently drops unmatched rows), left, right, outer.
indicator=True adds a _merge column naming which side each row came from — the first thing to run when a join loses rows.
A duplicated key multiplies rows and inflates every later total, with no warning.
validate="one_to_one" (or one_to_many, many_to_one) turns that silent bug into an error.
Overlapping column names get _x/_y suffixes — set suffixes= or drop the duplicate before merging.
join() merges on the index and defaults to a left join, where merge defaults to inner.
merge and join
SQL-style joins, and the copy key that silently times your rows.
The four kinds of join
how= decides which rows survive when a key is missing on one side.
example_01.pypandas
import pandas as pd
left = pd.DataFrame({"id": [1, 2, 3], "name": ["ana", "raj", "kim"]})
right = pd.DataFrame({"id": [2, 3, 4], "score": [90, 80, 70]})
for how in ["inner", "left", "right", "outer"]:
out = left.merge(right, on="id", how=how)
print("%-6s -> %d rows: ids %s" % (how, len(out), sorted(out["id"])))
print()
print("inner: only ids in BOTH (2, 3)")
print("left : every left row, score NaN where unmatched")
print("outer: every id from either side")
print()
print("the default is 'inner', which quietly drops unmatched rows.")
Output
Seeing what matched
indicator= tells you which side each row came from.
example_02.pypandas
import pandas as pd
left = pd.DataFrame({"id": [1, 2, 3], "name": ["ana", "raj", "kim"]})
right = pd.DataFrame({"id": [2, 3, 4], "score": [90, 80, 70]})
out = left.merge(right, on="id", how="outer", indicator=True)
print(out)
print()
print(out["_merge"].value_counts())
print()
print("This is the first thing to run when a join loses rows you")
print("expected to keep - it names them instead of leaving you to guess.")
Output
A duplicated key multiplies rows
The single most damaging silent bug in pandas.
example_03.pypandas
import pandas as pd
orders = pd.DataFrame({"id": [1, 2], "amount": [100, 200]})
lookup = pd.DataFrame({"id": [1, 1, 2], "label": ["a", "b", "c"]})
print("orders:", len(orders), "rows lookup:", len(lookup), "rows")
out = orders.merge(lookup, on="id")
print()
print(out)
print()
print("3 rows out of a 2-row left frame - id 1 matched twice.")
print("total amount before:", int(orders["amount"].sum()))
print("total amount after :", int(out["amount"].sum()), "<- inflated")
print()
print("Nothing warned. Every sum downstream is now wrong.")
Output
validate catches it at the join
State the relationship you expect and let pandas check it.
example_04.pypandas
import pandas as pd
orders = pd.DataFrame({"id": [1, 2], "amount": [100, 200]})
lookup = pd.DataFrame({"id": [1, 1, 2], "label": ["a", "b", "c"]})
try:
orders.merge(lookup, on="id", validate="one_to_one")
except Exception as e:
print("validate='one_to_one' ->", type(e).__name__)
print(" ", str(e)[:64])
print()
good = pd.DataFrame({"id": [1, 2], "label": ["a", "c"]})
out = orders.merge(good, on="id", validate="one_to_one")
print("with a unique lookup it passes:", len(out), "rows")
print()
print("Options: one_to_one, one_to_many, many_to_one, many_to_many.")
print("Writing the one you expect turns a silent bug into an error.")
Output
Different column names, and overlapping ones
left_on/right_on, and the suffixes that appear when names collide.
example_05.pypandas
import pandas as pd
a = pd.DataFrame({"user_id": [1, 2], "v": [10, 20]})
b = pd.DataFrame({"id": [1, 2], "v": [99, 98]})
out = a.merge(b, left_on="user_id", right_on="id")
print(out)
print()
print("both frames had a 'v', so pandas suffixed them:")
print(" ", [c for c in out.columns if c.startswith("v")])
print()
print("name them yourself:")
print(list(a.merge(b, left_on="user_id", right_on="id",
suffixes=("_a", "_b")).columns))
print()
print("Unnoticed suffixes are how you end up analysing v_x when you")
print("meant v_y. Drop or rename the duplicate before merging.")
Output
Joining on the index
join() is merge with the index as the default key.
example_06.pypandas
import pandas as pd
a = pd.DataFrame({"v": [1, 2]}, index=["x", "y"])
b = pd.DataFrame({"w": [10, 30]}, index=["x", "z"])
print("join defaults to a LEFT join on the index:")
print(a.join(b))
print()
print("how= works the same way:")
print(a.join(b, how="outer"))
print()
print("merge can do it too, explicitly:")
print(a.merge(b, left_index=True, right_index=True, how="outer"))
print()
print("Index joins are faster on a sorted unique index, which is a")
print("reason to set_index on a key you join on repeatedly.")
Output
Check what happened
Two habits catch nearly each join problem.
Compare row counts.len(before) against len(after). If a left join changed the count, a thing is duplicated. If an inner join dropped rows, some keys didn't match.
**Use indicator=True.** It adds a _merge column with values left_only, right_only or both. value_counts() on it summarises the whole join in one line.
That is far better than guessing. It tells you not just that rows went missing but which ones. You can look at them and find out whether the key is misspelled, differently typed, or truly absent.
A frequent cause of a "failed" join is a dtype mismatch: 1 as an int on one side and "1" as a string on the other never match. Nothing warns. Checking both key columns' dtypes takes a second.
Whitespace and case do the same thing to string keys. This Is why the cleaning modules come before this one.
The duplicate-key multiplication
This is the most damaging silent bug in pandas.
If a key appears twice on the right, each matching left row is duplicated. Two orders joined to a lookup with a repeated id give three rows. The frame still looks like orders, still has an amount column. Each sum found from it's now wrong.
Nothing warns, because a many-to-one join *turning* many-to-many is a legitimate step. Pandas can't know you didn't intend it.
The results are quiet and severe: inflated revenue, double-counted users, a model trained on duplicated rows.
validate
validate= states the link you expect and raises if it doesn't hold:
"one_to_one" — keys unique on both sides.
"one_to_many" — unique on the left.
"many_to_one" — unique on the right. This is the common case for a lookup table. The one worth reaching for by default when joining link data.
"many_to_many" — no constraint. This Is the current action spelled out loud.
Adding it costs one arg and converts an invisible data-corruption bug into a clear exception at the point it happens. On any join that feeds a number someone will act on, it's worth having.
Different names, colliding names
left_on and right_on join columns with other names. left_index=True / right_index=True use the index on that side.
When both frames have a column with the same name that is *not* a key, pandas keeps both and appends _x and _y.
That is where a subtle error lives: v_x and v_y both look plausible. Picking the wrong one makes a working analysis of the wrong column. Set suffixes=("_orders", "_lookup") so the names say where they came from, or drop the redundant column before merging.
join
df.join(other) is merge with the index as the key. It defaults to a left join rather than an inner one.
Two other defaults for two similar methods is a genuine wart. When it matters, use merge with clear args. join is a convenience for the index case.
Index joins are faster than column joins on a sorted, unique index. This Is a reason to set_index on a key you join on again and again rather than passing on= each time.
An order of operations
Clean the keys — type, case, whitespace. Check uniqueness on the side that should be unique. Choose how on purpose rather than taking the default. Add validate. Compare row counts later.
That sounds like a lot for one step. It's five seconds of typing. Joins are where the costly, silent errors live.
Joining on several keys
on=["date", "store"] joins on a composite key. Both frames need all the named columns. Rows match only when each key agrees.
The usual failure is a type mismatch on one of a few keys. The dates match but one side stores the store id as text. The join then returns far fewer rows than expected. indicator=True shows a large left_only count without saying why.
Checking the dtypes of each key column on both sides takes one line:
Two common intentions are filters rather than enrichments.
Keep rows whose key exists elsewhere. A semi-join. pandas has no dedicated method. df[df["id"].isin(other["id"])] is the right setup. It can't times rows. This An inner join can.
Keep rows whose key doesn't exist elsewhere. An anti-join. df[~df["id"].isin(other["id"])], or an outer join with indicator=True filtered to left_only when you also want the other side's columns.
Using a merge for either is a common way to add accidental row product.
Ordered and nearest-match joins
pd.merge_ordered merges two sorted frames and can fill forward across the join. This Suits time series with other sampling.
pd.merge_asof joins on the nearest key rather than an exact match. This Is the standard tool for joining a reading to the most recent preceding link value. A trade to the latest quote, a reading to the last tuning.
Both need sorted inputs and both are much faster than the option of a cross join and a filter. This On real data doesn't fit in memory.
direction="backward" (the default), "forward" or "nearest" and a handling control the matching.
Performance
Merging is roughly O(n + m) with hashing, and the hands-on costs are elsewhere:
Key dtype. Joining on ints or cats is faster than on strings.
Index joins. A sorted, unique index makes join faster than a column merge. This Is a reason to set_index on a key used again and again.
Result size. A many-to-many join can give a frame far larger than either input. Checking the expected size before running it. The product of the group counts. Avoids allocating a thing that doesn't fit.
Column count. Selecting only the columns you need from the right frame before merging avoids carrying passengers through the join and into memory.
That last one is the easiest win: left.merge(right[["id", "label"]], on="id") rather than merging the whole of right.
A merge checklist
Before:
Clean both key columns — type, case, whitespace.
Check uniqueness on the side that should be unique.
Select only the columns you need from the right frame.
During:
Choose how on purpose. The default is inner.
Set validate to the link you expect.
Set suffixes if column names share.
Add indicator=True while developing.
After:
Compare row counts with the input.
Check the _merge counts.
Check isna() on the newly added columns. A left join that matched nothing gives a column that is fully null. This Is easy to miss and obvious once looked for.
What to do when a join goes wrong
A structured approach beats guessing, and it takes about a minute.
Row count changed unexpectedly? A duplicated key on one side. Check is_unique on both, and use validate.
Rows missing? An inner join with unmatched keys. Switch to how="left" and indicator=True to see which.
all unmatched? A dtype mismatch on the key, or whitespace or case in a string key. Compare dtypes and look at a few values from each side.
New columns fully null? The join matched no rows, or matched on the wrong column.
**surprise _x / _y columns?** shared non-key names. Set suffixes, or select the columns you need before merging.
The tool for the first three is indicator=True. The habit of checking len(before) to len(after) catches all of them earlier than anything else.
Joining a summary back to detail
A frequent shape: find a per-group statistic, then attach it to each row.
The merge version works and has three steps and a risk.
Reaching for a merge where a transform fits is one of the most common not needed joins. It's worth recognising because the merge route is where the row-product bugs live.
Set-like operations without a join
a few questions phrased as joins are really membership tests:
"Which of these ids exist in that table?" — isin.
"Which don't?" — ~isin.
"What is the share between two key sets?". a.index.intersection(b.index), or set steps on the columns.
All three avoid the join machinery fully and can't change the row count. This Makes them both safer and clearer when the answer is a filter rather than an enrichment.
A summary
how="inner" is the default and drops unmatched rows silently.
indicator=True while developing. It shows the result.
validate= states the link and turns silent product into an error.
Clean and type-check the key columns first. Most failed joins are dtype or whitespace.
Select only the columns you need from the right frame.
Set suffixes when names share.
join() merges on the index and defaults to left, unlike merge.
Use transform instead of a self-join for group statistics. isin instead of a join for membership.
And compare row counts before and after, each time.
A closing note on merge and join
Joins are where the costly, quiet errors live.
An inner join silently drops the rows that didn't match. A duplicated key on either side silently times the rows that did. Neither raises, both give output of the right shape. Each number found later is wrong in a way that looks fair.
Three habits reduce almost all of it to nothing. Check the key columns' dtypes before joining. Most "nothing matched" cases are an int against a string. Pass validate= to state the link you expect. And compare the row count before and after, each single time.
Those cost a few seconds and replace the option. This Is discovering months later that a total has been inflated because one link table gained a copy.
And a good share of joins aren't joins at all. A group statistic wants transform. A membership test wants isin. Both are safer. Neither can change the number of rows.
One more thing
merge accepts how="cross", making each mix of rows from both frames. It's at times what you want. Building a full grid of params, or each store crossed with each date. And it's worth knowing that the result's size is the product of the inputs. This Grows alarmingly fast.
For the common case of filling out a full grid before joining sparse data onto it, pd.MultiIndex.from_product and reindex are often the better route.
Questions people ask about merge and join
What are the types of merge? inner keeps keys found in both frames, left keeps all keys from the left, right keeps all from the right, and outer keeps all from both.
Why did my merge produce more rows than I started with? Duplicate keys on one or both sides multiply: three matches on the right produce three output rows. Check df['key'].is_unique and use validate='one_to_one' or 'many_to_one'.
What is the difference between merge and join? join is a shortcut for merging on the index and defaults to a left join. merge is more general and works on columns or the index.
How do I merge when the key columns have different names? Use left_on='a', right_on='b'. Both columns stay in the result, so drop the extra one afterwards.
What do the _x and _y suffixes mean? They are added when both frames have a non-key column with the same name. Set suffixes=('_left', '_right') to choose better names.
How can I see which rows did not match? Pass indicator=True, which adds a _merge column with the values left_only, right_only or both.
merge and join, in summary
The default is an inner join. This Drops unmatched rows without a word. A duplicated key on either side times rows without a word either.
Both are blocked by the same short routine. Clean and type-check the keys, assert uniqueness where you assume it, pass validate=. Compare the row count before and after.
And a good number of steps phrased as joins aren't. A group statistic is transform. A membership test is isin. Both are safer. Neither can change how many rows you've.
Check yourself
0 of 4
Answer without scrolling back up.
What is `merge`'s default `how`?
A join meant to add a column can quietly remove rows, and the only sign is a row count you were not checking. Note join() defaults to left instead.
A key appears twice on the right. What happens?
Nothing warns, because many-to-many is a legitimate operation. validate='many_to_one' turns it into an error.
A join matches nothing even though the keys look identical. What is the usual cause?
Whitespace and case do the same to string keys. Check both key columns' dtypes, and use indicator=True to see what matched.
What does `indicator=True` add?
value_counts() on it summarises the whole join in one line - far better than guessing why rows went missing.
Cheat sheet
merge and join
Join two DataFrames with pandas merge using inner, left, right and outer joins. Spot the duplicate key that silently multiplies your rows.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.