This lesson's interactive features are locked, please to keep using them
Multi-Table Joins
What happens when you have more than two datasets to join together? Say you want sales with product names and customer names. That's a multi-table join.
sales = pd.DataFrame(
{
"sale_id": ["K8mQ2xV7", "N4pL9sR2", "B7vD3kT6"],
"product_id": ["CHL-482", "TST-731", "CHL-482"],
"customer_id": ["CUS-4821", "CUS-7316", "CUS-4821"],
"quantity": [5, 3, 2],
}
)
products = pd.DataFrame(
{
"product_id": ["CHL-482", "TST-731"],
"product_name": ["ChillVault Mini", "ToastForge Pro"],
"category": ["Refrigeration", "Toasters"],
}
)
customers = pd.DataFrame(
{
"customer_id": ["CUS-4821", "CUS-7316"],
"customer_name": ["Alice Chen", "Bob Martinez"],
"city": ["Seattle", "Portland"],
}
)
The simplest way to handle this is by joining one table at a time, i.e., performing two successive merges:
# Step 1: Add products to sales
step1 = pd.merge(sales, products, on="product_id", how="left")
# Step 2: Add customers to sales + products
final = pd.merge(step1, customers, on="customer_id", how="left")
This works, but the intermediate DataFrame wastes memory and is often unnecessary.
Method Chaining
Method chaining lets you skip the extra variable:
result = sales.merge(
products,
on="product_id",
how="left",
).merge(
customers,
on="customer_id",
how="left",
)
Four-Dataset Example
The same pattern scales to four datasets (or to an arbitrarily large number):
sales = ... # sale_id, product_id, customer_id, store_id
products = ... # product_id, product_name, category
customers = ... # customer_id, customer_name, city
stores = ... # store_id, store_name, region
complete = (
sales.merge(products, on="product_id", how="left")
.merge(customers, on="customer_id", how="left")
.merge(stores, on="store_id", how="left")
)
Just add one table at a time, keep the keys straight, and watch out for overlapping column names.
Assignment
SnackStack's analytics team is building a unified sales report for finance. The report needs sales enriched with product and customer names, plus revenue per sale.
Complete the build_sales_report function. It accepts three DataFrames: sales, products, and customers. It should: