TopolearnReal data lives in several tables; joining them correctly is essential and a classic source of silent errors.
`pd.merge(left, right, on="key", how="inner")` (or left.merge(right, ...)) combines rows from two DataFrames whose keys match, exactly like a SQL join.
The how argument:
"inner"(default): only keys present in both"left": all rows ofleft, with NaN whererighthas no match;"right"the mirror image"outer": all keys from both sides"cross": every combination (no key)
When key columns have different names, use left_on="cust_id", right_on="id". To join on the index, use left_index=True / right_index=True, or the shortcut `left.join(right)`, which joins on the index of right (a left join by default). Overlapping non-key column names get suffixes=("_x", "_y"); set meaningful ones.
The biggest danger is unexpected duplicate keys. If a key appears twice on each side, the result has four rows for it, and totals computed afterwards are silently inflated. Defend against it:
validate="many_to_one"(or"one_to_one","one_to_many") raises if the keys don't have the expected uniqueness.indicator=Trueadds a_mergecolumn sayingleft_only,right_onlyorboth, which is perfect for checking which rows failed to match.- Compare
len()before and after a left join: it should not grow when the right side is a lookup table.
Key dtypes must be compatible: merging an int64 id with a string id raises an error, and float keys are fragile. Clean and convert keys first.
merge always returns a new DataFrame. For "keep rows of A whose key appears in B" without adding columns, A[A["key"].isin(B["key"])] is simpler than a join.
Example
import pandas as pd
orders = pd.DataFrame({"order_id": [1, 2, 3, 4], "cust_id": [10, 20, 10, 30], "amount": [50, 20, 70, 15]})
customers = pd.DataFrame({"id": [10, 20, 40], "name": ["Ana", "Bo", "Cy"]})
inner = orders.merge(customers, left_on="cust_id", right_on="id")
print(inner[["order_id", "name"]].values.tolist()) # [[1, 'Ana'], [2, 'Bo'], [3, 'Ana']]
left = orders.merge(customers, left_on="cust_id", right_on="id",
how="left", validate="many_to_one", indicator=True)
print(len(orders), len(left)) # 4 4 no row explosion
print(left.loc[left["_merge"] == "left_only", "cust_id"].tolist()) # [30]
dup = pd.DataFrame({"id": [10, 10], "name": ["Ana", "Ana B."]})
try:
orders.merge(dup, left_on="cust_id", right_on="id", validate="many_to_one")
except pd.errors.MergeError as e:
print("caught:", type(e).__name__) # duplicate keys in right
totals = orders.groupby("cust_id")["amount"].sum().rename("total")
print(customers.join(totals, on="id").fillna({"total": 0}))Remember
merge(how=...)mirrors SQL inner/left/right/outer/cross joins- Use
left_on/right_onfor different key names andjoinfor index joins validate=andindicator=Truecatch duplicate keys and unmatched rows- Make key dtypes match before merging
Watch out for
- A left join to a lookup table that has duplicate keys, silently multiplying rows and inflating totals.