Level 7 · Debug: 30,338 orders by segment?

Spot the fan-out from the row count

Laura’s clue is a count: 30,338 “orders” when the store has 12,000. 30,338 is exactly the number of rows in order_items. That’s the fingerprint of a fan-out: the table was merged with order lines, so each order now appears once per line, and every order-level column, including shipping_fee, is repeated.

len(orders)                                        # 12000
len(orders.merge(order_items, on="order_id"))      # 30338

The report never uses a column from order_items. The merge was probably copied from another script “just in case”, and it silently changed the grain from orders to order lines.

Fix the grain

Keep the table at one row per order. The segment comes from customers, and each order has exactly one customer, so that merge is many-to-one and doesn’t add rows:

full = orders.merge(customers[["customer_id", "segment"]], on="customer_id", validate="many_to_one")
len(full)   # 12000

validate="many_to_one" makes pandas raise an error if a customer id appeared twice in customers, which would be a different fan-out. It costs nothing and documents what you expect.

Why even the average was wrong

You might think a mean survives the fan-out, since every fee is repeated. It doesn’t. Orders with many lines are repeated more often, and big multi-line baskets usually ship free, so the fanned-out mean leans toward $0. The starter gave $2.40 for consumers instead of $3.20, and $1.06 for business instead of $1.27. A fan-out quietly turns a plain average into a weighted one.

The fixed table also tells a real story: business customers place bigger orders that mostly clear the free-shipping threshold, so they pay $1.27 per order on average against $3.20 for consumers. And the two order counts, 3,305 and 8,695, add up to exactly 12,000, the reconciliation Laura was missing.

Common mistakes

• Merging every table “in case it’s needed”. Each merge with a many-side table changes the grain.
• Switching size to nunique for the order count but keeping the inflated sums.
• Trusting an average after a fan-out: repeated rows change the weights.
• Not comparing len() before and after a merge.