How to merge DataFrames in pandas
By the Analistable team · Updated · 5 min read
Use pd.merge(left, right, on='key', how='left') (or left.merge(right, ...)) to join two DataFrames on a shared column. how can be 'inner' (the default), 'left', 'right', 'outer' or 'cross'. Use left_on/right_on when the key columns have different names, validate= to catch duplicate keys, and pd.concat instead when you want to stack rows rather than match them.
Part of our guide: How to merge data in pandas, R and SQL
The example data
import pandas as pd
customers = pd.DataFrame({
"customer_id": ["C-101", "C-102", "C-103"],
"name": ["Bakery Lune", "Hart & Co", "Nordic Supply"],
})
orders = pd.DataFrame({
"order_id": ["O-9001", "O-9002", "O-9003", "O-9004"],
"customer_id": ["C-101", "C-103", "C-101", "C-104"],
"amount": [120, 75, 80, 60],
})Left join: every order, with its customer
orders.merge(customers, on="customer_id", how="left")| order_id | customer_id | amount | name |
|---|---|---|---|
| O-9001 | C-101 | 120 | Bakery Lune |
| O-9002 | C-103 | 75 | Nordic Supply |
| O-9003 | C-101 | 80 | Bakery Lune |
| O-9004 | C-104 | 60 | NaN |
O-9004's customer isn't in customers, so name is NaN. With how="inner" that row would be dropped.
The how argument
| how= | Keeps | Rows in this example |
|---|---|---|
| 'inner' | Keys in both | 3 |
| 'left' | All rows of the left DataFrame | 4 |
| 'right' | All rows of the right DataFrame | 4 (customers.merge(orders, how='right')) |
| 'outer' | All keys from both | 5 |
| 'cross' | Every combination (no key) | 12 |
Anti join: rows with no match
pandas has no how='anti', but indicator=True adds a _merge column you can filter:
m = customers.merge(orders, on="customer_id", how="left", indicator=True)
never_ordered = m[m["_merge"] == "left_only"] # Hart & CoOptions you'll need
left_on="cust_id", right_on="customer_id"— key columns with different names.on=["store", "sku"]— join on several columns.suffixes=("_order", "_customer")— instead of_x/_yfor clashing column names.validate="many_to_one"— raisesMergeErrorif the right side has duplicate keys, which would silently multiply rows.left.join(right, on="key")— joins on the right DataFrame's index;mergeis the general form.
concat: stacking instead of joining
import glob
monthly = pd.concat([pd.read_csv(f).assign(source=f) for f in glob.glob("sales/*.csv")],
ignore_index=True)concat aligns columns by name and fills missing ones with NaN. Use it to combine files with the same columns; use merge to match rows by key.
Key types must match: recent pandas versions raise a ValueError when you merge an int key with a text key, and a column holding a mix of numbers and strings simply fails to match. Check with df.dtypes and convert both sides with .astype(str).
Without code
If the data lives in spreadsheets, Analistable runs the same joins in an in-browser SQL engine from a plain-language question, and shows the SQL it used. For the equivalent in R and SQL, see merging data in pandas, R and SQL.
Outer join with indicator: see what matched
When you reconcile two lists, an outer join with indicator=True shows every key from both sides and where it came from in one step:
m = orders.merge(customers, on="customer_id", how="outer", indicator=True)
m["_merge"].value_counts()
# both 3
# left_only 1 <- O-9004, customer C-104 unknown
# right_only 1 <- Hart & Co, no orders| order_id | customer_id | amount | name | _merge |
|---|---|---|---|---|
| O-9001 | C-101 | 120.0 | Bakery Lune | both |
| O-9003 | C-101 | 80.0 | Bakery Lune | both |
| NaN | C-102 | NaN | Hart & Co | right_only |
| O-9002 | C-103 | 75.0 | Nordic Supply | both |
| O-9004 | C-104 | 60.0 | NaN | left_only |
Two side effects to expect: an outer join sorts the result by the key, and amount turns into floats (120.0) because NaN can't live in an integer column. Use .astype("Int64") afterwards if you need whole numbers with missing values.
A second example: joining on two columns
Stock levels are recorded per store and SKU, so a SKU alone is not a unique key. Join on both columns:
stock = pd.DataFrame({"store": ["London", "London", "Leeds"],
"sku": ["D-1", "C-2", "D-1"], "on_hand": [5, 12, 3]})
sales = pd.DataFrame({"store": ["London", "Leeds", "Leeds"],
"sku": ["D-1", "D-1", "C-2"], "sold": [2, 1, 4]})
sales.merge(stock, on=["store", "sku"], how="left")| store | sku | sold | on_hand |
|---|---|---|---|
| London | D-1 | 2 | 5.0 |
| Leeds | D-1 | 1 | 3.0 |
| Leeds | C-2 | 4 | NaN |
Leeds has no stock record for C-2, so on_hand is NaN. Joining on sku alone would have matched London's C-2 row to the wrong store.
Troubleshooting merges
| Symptom | Cause | Fix |
|---|---|---|
| ValueError: You are trying to merge on int64 and object columns | The key is numeric in one DataFrame and text in the other | Convert both with `.astype(str)`, or read the file with `dtype={"customer_id": str}` |
| Every value from the right side is NaN | Keys look equal but differ: trailing spaces, case, leading zeros | `df["key"] = df["key"].str.strip().str.upper()` on both sides |
| Row count grows after a left join | Duplicate keys in the lookup DataFrame | `customers["customer_id"].duplicated().sum()`, then de-duplicate or use `validate=` |
| KeyError on the key name | The column is the index, not a column | `df.reset_index()` or use `left_index=True` / `right_index=True` |
A quick check after any left join: len(result) == len(orders). If customers had a second C-101 row, the inner join in this example would return 5 rows instead of 3, and validate="many_to_one" would raise MergeError: Merge keys are not unique in right dataset.
Repeating the merge every month
Put the steps in a small script so next month's export runs the same way: read each file with explicit dtype for keys, clean the key columns, merge with validate= and indicator=True, print the counts of left_only and right_only, and write the result with to_csv(..., index=False) or to_excel. Printing the unmatched counts each run is the cheapest way to notice a supplier renaming its ID format.
Merging more than two DataFrames
Chain the merges, or fold a list with functools.reduce when every DataFrame shares the same key:
from functools import reduce
frames = [customers, segments, credit_limits] # all keyed on customer_id
combined = reduce(lambda a, b: a.merge(b, on="customer_id", how="left"), frames)Start the list with the DataFrame whose rows you want to keep, because each step is a left join onto the running result. Add validate="one_to_one" inside the lambda if each table should have exactly one row per customer.
Frequently asked questions
- What is the difference between merge and join in pandas?
- merge joins on columns (or indexes) and is the general function. DataFrame.join is a shortcut that joins on the other DataFrame's index.
- What is the default join type in pandas merge?
- Inner. Rows whose key isn't in both DataFrames are dropped unless you set how.
- Why does my merge return more rows than expected?
- The other DataFrame has duplicate keys, so each match creates a row. Use validate='many_to_one' to detect it.
- How do I merge on multiple columns in pandas?
- Pass a list: on=['store', 'sku'].
- How do I find rows that didn't match in a pandas merge?
- Merge with how='outer' and indicator=True, then filter _merge for 'left_only' or 'right_only'.