Combining Tables with Merge

Introduction

Real data usually lives in several tables: orders in one, customers in another. To answer questions such as "how much does each city spend?", you must join them. This is the same idea as an SQL join, and pandas calls it merge. The code is shown for reading, and the outputs come from real runs with pandas 3.1.

The data

import pandas as pd

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4, 5],
    "customer_id": [10, 11, 10, 12, 99],
    "amount": [20.0, 35.5, 12.0, 50.0, 8.0],
})
customers = pd.DataFrame({
    "customer_id": [10, 11, 12, 13],
    "name": ["Ana", "Ben", "Cy", "Dee"],
    "city": ["Leeds", "York", "Leeds", "Hull"],
})

The shared column, customer_id, is the key. Order 5 belongs to customer 99, who is not in the customers table, and customer 13 (Dee) has no orders.

Inner join

Keep only rows with a match on both sides:

print(orders.merge(customers, on="customer_id", how="inner"))

   order_id  customer_id  amount name   city
0         1           10    20.0  Ana  Leeds
1         2           11    35.5  Ben   York
2         3           10    12.0  Ana  Leeds
3         4           12    50.0   Cy  Leeds

Order 5 has vanished, because customer 99 has no match. Silent loss of rows is the biggest danger of inner joins.

Left join

Keep all rows from the left table, and fill in matches where they exist:

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

   order_id  customer_id  amount name   city
0         1           10    20.0  Ana  Leeds
1         2           11    35.5  Ben   York
2         3           10    12.0  Ana  Leeds
3         4           12    50.0   Cy  Leeds
4         5           99     8.0  NaN    NaN

Order 5 is kept, with missing values (NaN) where there was no customer. This is the usual choice when one table is the "main" one.

Outer join and the indicator

An outer join keeps rows from both sides. Add indicator=True to see where each row came from:

print(orders.merge(customers, on="customer_id", how="outer", indicator=True))

   order_id  customer_id  amount name   city      _merge
0       1.0           10    20.0  Ana  Leeds        both
1       3.0           10    12.0  Ana  Leeds        both
2       2.0           11    35.5  Ben   York        both
3       4.0           12    50.0   Cy  Leeds        both
4       NaN           13     NaN  Dee   Hull  right_only
5       5.0           99     8.0  NaN    NaN   left_only

The _merge column labels each row both, left_only or right_only. This is excellent for finding unmatched keys. Note that order_id became decimal because of the missing value.

Summarising after a merge

m = orders.merge(customers, on="customer_id", how="left")
print(m.groupby("city", dropna=False).agg(
    total=("amount", "sum"),
    orders=("order_id", "count"),
    avg=("amount", "mean"),
).round(2))

       total  orders    avg
city                       
Leeds   82.0       3  27.33
York    35.5       1  35.50
NaN      8.0       1   8.00

dropna=False keeps the unmatched order visible as its own group, instead of quietly dropping it. Leeds has orders of 20, 12 and 50, which total 82.

Merge safely

• Check key uniqueness. If a key is duplicated in the right table, rows multiply. Use validate="many_to_one" to make pandas raise an error if that assumption is wrong:

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

• Compare row counts before and after. A left join should keep the left table's count, unless keys were duplicated.
• Make sure keys have the same type and format. A number in one table and text in the other will not match, and extra spaces or different capitals break matches.
• Use left_on and right_on when the key columns have different names, and suffixes when other columns share a name.
• Use pd.concat to stack tables with the same columns on top of each other.

Try it yourself

Create a products table and an order_items table with a few unmatched products. Left-join them, use indicator=True to find the unmatched rows, and compute total sales per product category.

Summary

• merge joins tables on a key, with inner, left, right and outer options that decide which rows survive.
• indicator=True and row counts reveal unmatched or lost rows, and validate= catches duplicated keys.
• Use dropna=False and clean key columns so problems stay visible instead of silently disappearing.