Analistable

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")
Output
order_idcustomer_idamountname
O-9001C-101120Bakery Lune
O-9002C-10375Nordic Supply
O-9003C-10180Bakery Lune
O-9004C-10460NaN

O-9004's customer isn't in customers, so name is NaN. With how="inner" that row would be dropped.

The how argument

Join types in pandas
how=KeepsRows in this example
'inner'Keys in both3
'left'All rows of the left DataFrame4
'right'All rows of the right DataFrame4 (customers.merge(orders, how='right'))
'outer'All keys from both5
'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 & Co

Options 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 / _y for clashing column names.
  • validate="many_to_one" — raises MergeError if the right side has duplicate keys, which would silently multiply rows.
  • left.join(right, on="key") — joins on the right DataFrame's index; merge is 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
Outer join result
order_idcustomer_idamountname_merge
O-9001C-101120.0Bakery Luneboth
O-9003C-10180.0Bakery Luneboth
NaNC-102NaNHart & Coright_only
O-9002C-10375.0Nordic Supplyboth
O-9004C-10460.0NaNleft_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")
Result
storeskusoldon_hand
LondonD-125.0
LeedsD-113.0
LeedsC-24NaN

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 and fix
SymptomCauseFix
ValueError: You are trying to merge on int64 and object columnsThe key is numeric in one DataFrame and text in the otherConvert both with `.astype(str)`, or read the file with `dtype={"customer_id": str}`
Every value from the right side is NaNKeys 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 joinDuplicate keys in the lookup DataFrame`customers["customer_id"].duplicated().sum()`, then de-duplicate or use `validate=`
KeyError on the key nameThe 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'.

Related guides