Combining and reshaping Updated November 2026

merge

pd.merge(left, right, on="key", how="inner") joins two DataFrames on matching key values, like a SQL JOIN. how="left" keeps every row of the left frame, "outer" keeps all rows from both, and left_on/right_on join on columns with different names.

Syntax

pd.merge(left, right, how="inner", on=None, left_on=None, right_on=None, suffixes=("_x", "_y"), validate=None, indicator=False)

Part	What it is
`how`	"inner" (default), "left", "right", "outer" or "cross"
`on`	Column name(s) present in both frames
`left_on / right_on`	Key column names when they differ between frames
`suffixes`	Added to non-key columns that exist in both frames
`validate`	"1:1", "1:m" or "m:1"; raises if the keys break that relationship
`indicator`	True adds a _merge column showing where each row came from

Examples

import pandas as pd

orders = pd.DataFrame({"order_id": [1, 2, 3, 4],
                       "cust_id": [10, 20, 10, 30],
                       "amount": [50, 80, 20, 60]})
customers = pd.DataFrame({"cust_id": [10, 20, 40],
                          "name": ["Ana", "Ben", "Dan"]})
pd.merge(orders, customers, on="cust_id")

Result: order_id cust_id amount name 0 1 10 50 Ana 1 2 20 80 Ben 2 3 10 20 Ana

Inner join: order 4 (customer 30) and customer 40 have no match and are dropped.

orders.merge(customers, on="cust_id", how="left")

Result: order_id cust_id amount name 0 1 10 50 Ana 1 2 20 80 Ben 2 3 10 20 Ana 3 4 30 60 NaN

Left join keeps every order; the unmatched customer name is NaN.

cust2 = customers.rename(columns={"cust_id": "id"})
orders.merge(cust2, left_on="cust_id", right_on="id", how="inner")[["order_id", "name"]]

Result: order_id name 0 1 Ana 1 2 Ben 2 3 Ana

Common mistakes

Row count explodes after a merge: the key is not unique on one side, so each match multiplies; check with validate="m:1" or drop_duplicates on the lookup table first.
Columns named amount_x and amount_y appear: both frames had a non-key column with that name; set suffixes or drop one before merging.
ValueError: You are trying to merge on int64 and object columns: cast the keys to the same dtype with astype first.

Practice merge

These drills check your answer on live data, free, no account needed.

Inner merge on a shared keyJoin `orders` to `customers` on `cust_id`, keeping only orders whose customer exists. Row order and column order do not matter here.
Left merge keeps every orderJoin `orders` to `customers` on `cust_id`, keeping every order. Orders with an unknown customer get missing name and city.
Merge on differently named keysJoin `orders` to `products`, where the key is `product_id` in orders and `id` in products, keeping only matches. Add `line_total = qty * price` and return the columns `order_id`, `product` and `line_total`. Row order does not matter.

Questions

What is the difference between merge and join?

df.join is a convenience for merging on the index; merge is the general function for joining on columns or the index. Most code uses merge.

How do I merge on multiple columns?

Pass a list: pd.merge(a, b, on=["year", "region"]). Both frames must have all listed columns.

How do I do a SQL anti-join (rows with no match)?

Merge with how="left", indicator=True, then keep rows where _merge == "left_only".

concatHow to use pd.concat in pandas to append rows from several DataFrames, add columns side by side with axis=1, reset the index, and label the source with keys.
isinHow to use isin in pandas to keep rows whose value is in a list, set or Series, exclude with ~, check several columns at once and avoid the type mismatch.
drop_duplicatesHow to remove duplicates in pandas with drop_duplicates: whole rows or a subset of columns, keep first, last or none, and finding duplicates with duplicated.
groupbyHow groupby works in pandas: group by one or more columns, apply sum, mean, count or size, get a flat table with as_index=False, and iterate over groups.