Joining Tables
Bringing data together.
Real data lives in many tables. Sales are in one place, and store details in another. To answer, which cafes earn the most for their size, you have to join them. It's simple, and it's also where many silent mistakes happen.
- Data lives in many tables
- Joining is simple
- And full of silent mistakes
Profit per square metre
Merge joins two tables on a shared column, like store ID. Left join keeps every sales row. Then we compute profit per square metre. Run it. Now we see the weakest cafes for their size, a fairer comparison than total profit. A big cafe with a big profit might actually be underperforming. Check the last line. Rows in equals rows out. Always check that.
import pandas as pd
sales = pd.read_csv("sales.csv")
stores = pd.read_csv("stores.csv")
joined = sales.merge(stores[["store_id", "size_m2"]], on="store_id", how="left")
per_store = joined.groupby(["city", "size_m2"], as_index=False)["profit"].sum()
per_store["profit_per_m2"] = per_store["profit"] / per_store["size_m2"]
print(per_store.sort_values("profit_per_m2").round(0).head(3).to_string(index=False))
print("Rows in, rows out:", len(sales), len(joined))city size_m2 profit profit_per_m2 Giza 171 343623.0 2009.0 Milan 197 438427.0 2226.0 Bergen 183 420928.0 2300.0 Rows in, rows out: 70176 70176
Join types and traps
An inner join keeps only matching rows. A left join keeps everything on the left. The big trap is duplicates. If the right table has two rows for one key, your rows silently multiply, and your totals become wrong. So always count your rows before and after. Validate equals many to one will raise an error if that happens.
Summary
Merge with care, and always count your rows. You've got your knife skills now. But so far, our data has been clean. Real data never is. Next, the customer file that the founder was too embarrassed to show you.
Key takeaways- merge on a shared key
- Left keeps all rows
- Count rows before and after