Merging DataFrames

Why it matters

Useful data is rarely in one table. Orders are in one export and customer details in another; sample results are in the instrument file and sample metadata in the LIMS. To answer "revenue by customer segment" or "mean concentration by site" you must join the tables on a shared key. A join is also one of the easiest places to silently lose rows or, worse, duplicate them, so this lesson focuses on doing it safely.

merge in one line

orders = pd.DataFrame({
    "order_id":    [1, 2, 3, 4],
    "customer_id": [10, 11, 10, 99],
    "amount":      [50.0, 20.0, 35.0, 80.0],
})
customers = pd.DataFrame({
    "customer_id": [10, 11, 12],
    "name":        ["Ada", "Grace", "Alan"],
    "segment":     ["retail", "business", "retail"],
})

merged = orders.merge(customers, on="customer_id", how="left")
#    order_id  customer_id  amount   name   segment
# 0         1           10    50.0    Ada    retail
# 1         2           11    20.0  Grace  business
# 2         3           10    35.0    Ada    retail
# 3         4           99    80.0    NaN       NaN

Every order is kept. Order 4 refers to customer 99, who is not in customers, so its customer columns are NaN.

The four join types

With how="inner" order 4 would simply disappear, and your revenue total would drop by 80 without any warning. That is why analysts usually start with a left join from the table they are reporting on, then look at what did not match.

Finding what did not match

indicator=True adds a _merge column saying where each row came from:

check = orders.merge(customers, on="customer_id", how="left", indicator=True)
check["_merge"].value_counts()
# both         3
# left_only    1
check.loc[check["_merge"] == "left_only", "order_id"].tolist()   # [4]

The other direction, customers with no orders, is a membership test:

customers[~customers["customer_id"].isin(orders["customer_id"])]["name"].tolist()   # ['Alan']

Guarding against duplicated rows

If the right table has two rows for the same key, every matching left row is repeated, and sums double. This happens more than you would think (a customer listed twice with different spellings, a sample re-run).

Protect yourself with validate:

orders.merge(customers, on="customer_id", how="left", validate="many_to_one")

pandas raises a MergeError if customer_id is not unique in customers. Other options are "one_to_one" and "one_to_many". Also compare row counts: after a many-to-one left join, len(merged) must equal len(orders).

Keys with different names, and stacking

orders.merge(clients, left_on="customer_id", right_on="id", how="left")

When both tables have a non-key column with the same name, pandas adds suffixes _x and _y; set your own with suffixes=("_order", "_customer").

To stack tables with the same columns (January's file and February's file) use concat, not merge:

all_months = pd.concat([jan, feb], ignore_index=True)

A worked example

Revenue by segment, keeping a check on unmatched orders:

merged = orders.merge(customers, on="customer_id", how="left", validate="many_to_one")
assert len(merged) == len(orders)

unmatched = merged["segment"].isna().sum()
by_segment = merged.groupby("segment")["amount"].sum()
# business 20.0, retail 85.0   (groupby skips the NaN segment)
print(f"{unmatched} order(s) worth {merged.loc[merged['segment'].isna(), 'amount'].sum()} had no customer record")

Reporting the unmatched amount is the honest part: the segment totals add up to 105, not the 185 in orders.

Common mistakes

• Using the default inner join without thinking. Unmatched rows vanish silently.
• Duplicate keys on the "one" side. Rows multiply; use validate and check len.
• Key type mismatch. 10 (int) does not match "10" (text); convert with astype first.
• Untrimmed keys. "C-10 " does not match "C-10"; clean keys like any text column.
• Using merge to stack files. Use pd.concat for rows with the same columns.

Quick recap

• left.merge(right, on="key", how="left") keeps every row you are reporting on.
• indicator=True and isin show what did not match; report it.
• validate="many_to_one" and a row-count check stop accidental duplication.
• concat stacks tables; merge joins them side by side.

Try it

In the Do tab you will join orders to customers, find unmatched orders and customers without orders, and total revenue by segment.