Level 6 · Boss: find the biggest baskets

Changing the grain

order_items has one row per order line. Megan’s questions are about orders. Moving from one grain to a coarser one is what groupby is for: many lines in, one row per order out.

items = order_items.assign(
    line_revenue=order_items["quantity"] * order_items["unit_price"]
    * (1 - order_items["discount_pct"] / 100)
)
per_order = items.groupby("order_id", as_index=False).agg(revenue=("line_revenue", "sum"))

Check the grain afterwards: per_order["order_id"].is_unique should be True, and len(per_order) should equal order_items["order_id"].nunique(). Both are quick and they catch grouping on the wrong column.

The mean of per_order["revenue"] is the average order value (AOV), one of the most watched numbers in e-commerce. Note that it’s the mean of order totals, not the mean of line revenue: items["line_revenue"].mean() is the average line, about two and a half times smaller.

Each boss step runs on a fresh copy of the data, which is why every step computes line revenue again. In a notebook you’d compute it once and reuse it.

Ranking the groups

The ten biggest orders are a sort on the aggregated table:

per_order.sort_values(["revenue", "order_id"], ascending=[False, True]).head(10)

The biggest baskets at Harbor & Pine come from business accounts buying furniture in quantity. That’s a useful thing to say out loud: a “top orders” list is often a list of a few B2B customers, and averages across all orders hide that segment entirely. Your next question as an analyst would be who placed them, which needs a join with orders and customers (next chapter).

A share of a total

“How much revenue was sold at a discount?” is a ratio of two sums: discounted revenue over all revenue. You don’t need a groupby; a mask selects the discounted lines:

line_revenue = order_items["quantity"] * order_items["unit_price"] * (1 - order_items["discount_pct"] / 100)
share = line_revenue[order_items["discount_pct"] > 0].sum() / line_revenue.sum()

The mask comes from order_items and is applied to line_revenue. That works because both share the same index, so pandas lines the rows up by label. This alignment is a pandas superpower and an occasional trap: if you’d filtered or re-sorted one of them, the labels would still match, but reset indexes would not.

Notice this is a share of revenue, not of lines. A share of discounted lines, (order_items["discount_pct"] > 0).mean(), answers a different question. Here about 12.9% of lines carry a discount but they bring in only about 10.7% of revenue, partly because the discount itself shrinks them. Say which one you report.

About the mess

This level uses every line as it is, including a handful of lines with zero or negative quantities. Those are data-entry errors you’ll clean in chapter 8. Their effect here is tiny, but in a real report you’d clean first and say so.

Common mistakes

• Summing unit_price instead of line revenue.
• Ranking order_items rows (single lines) instead of orders.
• Taking the mean of discount_pct as “share of discounted revenue”.