Level 5 · Shipping fees on lighting orders

The question crosses grains

The condition (“contains a Lighting product”) lives at the line level: you need order_items and products to know it. The measure (shipping fee) lives at the order level. Mixing the two in one merged table is how the fan-out from the previous level happens.

The safe recipe is:

1. Use the line-level tables to find which orders qualify.
2. Sum the order-level measure on the orders table, for those orders only.

Step 1: qualifying order IDs

lines = order_items.merge(products[["product_id", "category"]], on="product_id")
lighting_ids = lines.loc[lines["category"] == "Lighting", "order_id"].unique()

.unique() turns “every Lighting line’s order ID” into a list of distinct orders. An order with two lamps would otherwise appear twice. For isin it doesn’t strictly matter, since isin only asks whether a value is present, but de-duplicating makes the intent obvious and lets you check len(lighting_ids) as a count of orders.

Step 2: sum on the right table

mask = (orders["status"] == "completed") & orders["order_id"].isin(lighting_ids)
orders.loc[mask, "shipping_fee"].sum()

Every order appears once in orders, so every fee counts once. That’s the whole trick.

The wrong answer, for comparison

wrong = (
    orders.merge(lines, on="order_id")
    .loc[lambda d: (d["status"] == "completed") & (d["category"] == "Lighting"), "shipping_fee"]
    .sum()
)

This counts an order’s fee once for every Lighting line it has. An order with a pendant and two lamps on separate lines is counted three times. It’s a very natural way to write the query, which is why it’s such a common bug. Run both and compare; the gap is the size of the mistake.

Another tempting fix is drop_duplicates() on the merged table. It can work, but only if you de-duplicate on order_id alone after selecting order-level columns. Calling it on the full merged table does nothing, because the line columns make every row distinct.

Alternative: aggregate up first

You can also bring the line information up to the order grain, then merge one-to-one:

has_lighting = (
    lines.assign(is_lighting=lines["category"] == "Lighting")
    .groupby("order_id", as_index=False)
    .agg(has_lighting=("is_lighting", "any"))
)
orders.merge(has_lighting, on="order_id", validate="one_to_one")

Now each order has a single True/False flag and can safely be summed, filtered or grouped. This is the better approach when you want several order-level flags at once (“has lighting”, “has furniture”, “number of lines”).

Interpreting the result

To answer Daniel’s real question, compare the average shipping fee on Lighting orders with the average on other completed orders. The total alone can’t show whether Lighting pushes fees up; it mostly reflects how many Lighting orders there are.

Common mistakes

• Summing shipping_fee on the merged line-level table.
• Forgetting the status filter.
• De-duplicating the wrong table, or on the wrong columns.