Merging tables

01The problem

Kolanut's orders only hold IDs. The sales director's questions are about names:

"Revenue by region and by sales rep, please. And which product categories sell best through supermarkets?"

Region, rep and channel live in customers.csv; category lives in products.csv. To answer, Bisi has to bring those columns onto each order line by matching IDs: customer_id to customer_id, product_id to product_id. That's a merge (a join, in SQL). It's also the step where analyses most often go quietly wrong, so this lesson is as much about checking a merge as doing one.

02The concept

merge: match rows on a key

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

• on is the column both tables share. If the names differ, use left_on="cust_id", right_on="customer_id".
• how decides which rows survive:

For "add customer details to every order", use how="left": an order should never disappear just because its customer is missing from the lookup table. You want to see that problem as a blank, not lose the order silently.

Check the row count, every time

A left merge onto a lookup table should leave the number of rows unchanged. If orders has 4,266 rows and orders_full has more, the lookup table has duplicate keys, and every duplicated customer's orders are now counted twice. Your revenue total will be wrong with no error message.

validate makes pandas check for you:

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

"Many orders to one customer". If a customer_id appears twice in customers, pandas stops with a MergeError instead of doubling your numbers.

Finding records with no match

indicator=True adds a _merge column saying where each row came from: both, left_only or right_only.

check = customers.merge(orders[["customer_id"]].drop_duplicates(), on="customer_id", how="left", indicator=True)
no_orders = check[check["_merge"] == "left_only"]

That's the pandas version of SQL's LEFT JOIN … WHERE … IS NULL: customers who never ordered.

Stacking tables: concat

Merging adds columns by matching keys. Stacking adds rows: for example monthly files with the same columns.

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

03Example

Bring customer and product details onto every order, with checks:

import pandas as pd

base = "https://academy.cloudtechanalytics.com/datasets/sales/"
orders = pd.read_csv(base + "orders.csv")
customers = pd.read_csv(base + "customers.csv")
products = pd.read_csv(base + "products.csv")
orders["revenue"] = orders["quantity"] * orders["unit_price"] * (1 - orders["discount_pct"] / 100)

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

print(len(orders), len(full))
print(full[["region", "category"]].isna().sum().sum(), "missing lookups")
full.groupby("region")["revenue"].sum().sort_values(ascending=False)

4266 4266
0 missing lookups
region
Lagos            411162300.0
South West       131536985.0
North West        81581325.0
North Central     77540720.0
South South       68403230.0
South East        60316685.0
Name: revenue, dtype: float64

Same number of rows before and after, no blanks from the lookups: the merge is safe to use. Lagos brings in about half of all revenue.

04Walkthrough

1. Run the Example and check both numbers it prints before looking at the result.
2. See what goes wrong without checks. Make a customers table with one customer duplicated: dup = pd.concat([customers, customers.head(1)]).
3. Merge it: len(orders.merge(dup, on="customer_id", how="left")). It's more than 4,266: customer 1's orders are now in the table twice.
4. Add validate="many_to_one" to the same merge and run it again: pandas refuses with a MergeError. That error just saved a report.
5. Two-level summary: full.groupby(["channel", "category"])["revenue"].sum().unstack().round(-6) answers the director's third question.
6. Rank the reps: full.groupby("sales_rep")["revenue"].sum().sort_values(ascending=False).
7. Find matters with no hearings in the legal data:

legal = "https://academy.cloudtechanalytics.com/datasets/legal/"
matters = pd.read_csv(legal + "matters.csv")
hearings = pd.read_csv(legal + "hearings.csv")

check = matters.merge(hearings[["matter_id"]].drop_duplicates(), on="matter_id", how="left", indicator=True)
no_hearings = check[check["_merge"] == "left_only"]
len(no_hearings)

55

05Practice

06Challenge

07Check your understanding