Level 4 · Why did shipping revenue double?

The fan-out trap

When you merge a table where each key appears once (orders: one row per order) with a table where it appears many times (order_items: several lines per order), the result has one row per match. Every column from the “one” side is repeated on each of its matching rows.

That’s not a bug in pandas; it’s what a join is. It becomes a bug when you then sum a column that belonged to the “one” side:

merged = orders.merge(order_items, on="order_id")
len(orders)          # 12000
len(merged)          # about 30000
merged["shipping_fee"].sum()   # counts each order's fee once per line

An order with three lines and a $6.95 fee contributes $20.85 instead of $6.95. Nothing errors, and the result looks like a plausible number, just too big.

Spotting it

• Row counts. If a merge grows the table, ask which columns are now repeated.
• Grain. Before summing a column, ask “what does one row of this table represent, and is this column measured at that grain?” Shipping is per order; quantity is per line.
• Reconcile. Compare totals with the source table. Shipping fees summed on the merged table should equal orders["shipping_fee"].sum(). When they don’t, you’ve found a fan-out.

Fixing it

Sum each measure at its own grain, then combine:

shipping = orders["shipping_fee"].sum()                     # order grain
units = order_items["quantity"].sum()                       # line grain

If you need to filter orders by something on the lines (say, orders containing a Lighting product), find the qualifying order IDs first, then sum on orders:

lighting_ids = order_items.merge(products, on="product_id").loc[
    lambda d: d["category"] == "Lighting", "order_id"
].unique()
orders.loc[orders["order_id"].isin(lighting_ids), "shipping_fee"].sum()

Or aggregate the “many” side up to the “one” side’s grain before merging, so the merge is one-to-one. You’ll do exactly this in the next level.

Fan-out with duplicate keys

A second kind of fan-out happens when a table you think is “one per key” isn’t. If customers had two rows with the same customer_id, every order for that customer would be duplicated by a merge. validate="many_to_one" catches this immediately, which is why it’s worth adding to every merge where you expect unique keys on one side.

This is one of the most common analyst mistakes and one of the most common interview questions on joins. Being able to explain it clearly, with the grain of each table, is worth as much as knowing the syntax.