Joining Tables with merge

The head of growth’s question needs columns from four tables. Revenue lives in order_items. Whether an order was cancelled, and when it was placed, lives in orders. The product’s category lives in products, and the customer’s country in customers. In a spreadsheet you would write lookup formulas; in pandas you merge. Merges are where most silent errors in analysis happen, so this lesson spends as much time on checking a merge as on writing one.

A first merge

import pandas as pd

items = pd.read_csv("order_items.csv")
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount"])

lines = items.merge(orders, on="order_id", how="left", validate="many_to_one")
print(len(items), "->", len(lines))
print(lines[["order_item_id", "order_id", "revenue", "order_date", "status"]].head(3))

For each row of the left table, pandas looks up the row of orders with the same order_id and copies its columns across. Many item lines point to one order, so the relationship is many to one, and validate="many_to_one" tells pandas to check that: if orders had two rows for one id, the merge would raise MergeError instead of quietly doubling those items. The row count before and after is the second check. A left merge onto a unique key never changes it.

Now revenue can finally respect the business rule you postponed in Lesson 2.5: cancelled orders are not revenue.

import pandas as pd

items = pd.read_csv("order_items.csv")
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount"])
lines = items.merge(orders, on="order_id", how="left", validate="many_to_one")

kept = lines[lines["status"] != "cancelled"]
print(round(kept["revenue"].sum(), 2), "revenue excluding cancelled orders")

446,666.32 dollars. This is the revenue definition for the rest of the course: line revenue after discounts, excluding shipping fees and cancelled orders. Returned orders stay in, because their refunds are tracked separately in returns. Write definitions like this down where readers will see them; a number without its definition invites an argument.

When the key has a different name on each side, name both: left_on="customer_id", right_on="referred_by" in a merge of customers with itself matches each customer to the people they referred. And pick the key that identifies what you are matching. Returns belong to order lines, so returns joins order_items on order_item_id; joining it on order_id instead would attach each refund to every line of the order.

Join types

how decides what happens to keys that exist on only one side:

inner (the default) keeps only keys found on both sides. left keeps every left row, filling the right-hand columns with NaN where there is no partner. right is the mirror image, and outer keeps everything. For adding information to a table of facts, such as orders, items or sessions, use how="left" with the fact table on the left: you never lose a fact because a lookup table is incomplete, and the NaNs show you exactly where it is.

Finding what did not match

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

import pandas as pd

customers = pd.read_csv("customers.csv")
orders = pd.read_csv("orders.csv")
buyers = orders[["customer_id"]].drop_duplicates()

check = customers.merge(buyers, on="customer_id", how="left", indicator=True)
print(check["_merge"].value_counts())
never_ordered = check[check["_merge"] == "left_only"]
print(len(never_ordered), "customers never ordered")

85 customers signed up and never bought anything, the same number you found with nunique in Lesson 2.2, now as a list of names you could hand to the marketing team. indicator=True is also the fastest audit of any merge: right_only rows in a left join are impossible, and a surprising number of left_only rows means keys that should match do not, often because of types (int on one side, text on the other) or untrimmed spaces.

Same column name on both sides

order_items and products both have a unit_price column: the price charged and the list price. pandas adds suffixes to tell them apart, and you can choose them:

import pandas as pd

items = pd.read_csv("order_items.csv")
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
products = pd.read_csv("products.csv")

lines = (
    items.merge(orders[["order_id", "order_date"]], on="order_id", validate="many_to_one")
    .merge(products[["product_id", "name", "unit_price"]], on="product_id",
           suffixes=("_charged", "_list"), validate="many_to_one")
)
lines["price_ratio"] = (lines["unit_price_charged"] / lines["unit_price_list"]).round(2)
print(lines.groupby(lines["order_date"] >= "2025-03-01")["price_ratio"].agg(["min", "max"]))

Before March 2025, every line was charged exactly the list price; from 1 March, every line was charged 5% more. The merge has just confirmed the price rise from the data alone, and it is one of the suspects for the Q4 revenue jump.

In the exercise you build the full line-level table that the rest of the course analyses. Next you reshape tables between wide and long formats with pivot_table and melt.