lesson 212 pandas Transformations · advanced

Merging tables

merge joins two tables on a shared key column. how= decides what happens to rows without a partner, and validate= stops duplicate keys from silently multiplying rows.

You'll be able to

Join orders to customers with merge(on=..., how="inner"|"left") and predict the number of rows
Find rows without a match with indicator=True (an anti-join)
Explain how duplicate keys multiply rows, and guard against it with validate="many_to_one"

The idea

Orders hold a customer_id; the customers table holds the city. In the Collection Algorithms module you joined them through a dictionary index. orders.merge(customers, on="customer_id") does it for whole tables: every order row is paired with the customer row that has the same key. how="inner" (the default) keeps only orders that found a customer; how="left" keeps every order and leaves the customer columns missing where there was no match. indicator=True adds a _merge column saying both or left_only.

Run each example to see what it prints. Running examples doesn't count toward progress.

example-1.py · Orders with their customer's city

import pandas as pd

orders = pd.DataFrame({
    "order_id": ["O100282", "O100376", "O101723", "O103199"],
    "customer_id": ["C00077", "C00065", "C01338", "C02264"],
})
customers = pd.DataFrame({
    "customer_id": ["C00065", "C00077", "C01338"],
    "city": ["Los Angeles", "Houston", "New York"],
})
inner = orders.merge(customers, on="customer_id")
print(len(inner), inner["city"].tolist())
left = orders.merge(customers, on="customer_id", how="left", indicator=True)
print(left[["order_id", "city", "_merge"]])

Each order should match at most one customer. If the customers table accidentally has a customer twice, every one of their orders is duplicated, and every total computed afterwards is wrong without any error. validate="many_to_one" states the relationship (many orders, one customer each) and raises MergeError when the data breaks it. When repeated rows are known to be harmless copies, customers.drop_duplicates(subset=["customer_id"]) keeps one row per key before merging.

example-2.py · A duplicated customer caught by validate

import pandas as pd

orders = pd.DataFrame({"order_id": ["O100282", "O101219"], "customer_id": ["C00077", "C00077"]})
customers = pd.DataFrame({"customer_id": ["C00077", "C00077"], "city": ["Houston", "Houston "]})
print(len(orders.merge(customers, on="customer_id")))
orders.merge(customers, on="customer_id", validate="many_to_one")

example-3.py · Revenue per product category

import pandas as pd

items = pd.DataFrame({
    "order_id": ["O100282", "O100376", "O100376", "O100918", "O100918"],
    "product_id": ["P0164", "P0594", "P0708", "P0300", "P0762"],
    "line_total": [27.9, 48.0, 37.49, 3.99, 42.49],
})
products = pd.DataFrame({
    "product_id": ["P0164", "P0594", "P0708", "P0300", "P0762"],
    "category": ["sports", "sports", "fashion", "grocery", "fashion"],
})
lines = items.merge(products, on="product_id", how="left", validate="many_to_one")
print(lines.groupby("category")["line_total"].sum().round(2).to_dict())

When to use it: whenever the question needs columns from two tables. Mistakes to avoid: an inner join that quietly drops rows (compare row counts before and after); duplicate keys on the side that should be unique; joining on columns with different spellings or types ("C00077" vs "C00077 ", or 12 vs "12").

Review

left.merge(right, on=key, how=...): inner keeps matches, left keeps every left row.
indicator=True labels rows both/left_only, which finds unmatched rows.
Duplicate keys multiply rows; validate="many_to_one" turns that into an error.
Next: spread one column's values into columns with a pivot table.

Practise this