Analistable

How to merge DataFrames in pandas

By the Analistable team · Updated · 2 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: a customer_id stored as int in one DataFrame and as str in the other never matches. Check with df.dtypes and convert 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.

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'].

Related guides