Level 8 · Debug: Furniture can't be our best margin

Why 0 is the wrong fill

Nine products have no unit_cost (NaN). A missing cost means “we don’t know”, not “free”. fillna(0) turns “we don’t know” into “it costs nothing”, and the margin formula then gives those products 100%. Five of the nine are Furniture (the Vale Velvet Loveseat, Tidewater Teak Loveseat, Weston Ash Bed Frame, Alder Linen Sideboard and Sable Oak Daybed), so Furniture’s average jumped to 65.8% and to the top of the list. Laura knew the business well enough to see that was impossible. That’s the best kind of check.

Fill with the group’s median: transform

Laura asked for an estimate from similar products. Costs differ hugely in dollars, so fill the cost ratio (cost as a share of price), which is comparable across products in a category:

products["cost_ratio"] = products["unit_cost"] / products["unit_price"]
products.groupby("category")["cost_ratio"].median()
# Furniture 0.476, Outdoor 0.445, Lighting 0.437, Decor 0.434, Kitchen 0.428, Textiles 0.416

agg or median() after a groupby returns one row per category. To fill a column you need the category’s median on every product row, lined up with the table. That’s what transform does: it computes per group and broadcasts the answer back to every row of the group, keeping the original index.

category_median = products.groupby("category")["cost_ratio"].transform("median")
products["cost_ratio"] = products["cost_ratio"].fillna(category_median)

fillna with a Series fills each gap with the value at the same index label, so every loveseat gets the Furniture median and every grill the Outdoor one. The median ignores the NaNs it’s replacing.

The corrected report

Kitchen now leads at 57.5% and Furniture is last at 53.9%, which matches what finance sees. All 120 products stay in the report, 20 per category. Simply dropping the nine would also give a sensible ranking, but with only 15 Furniture products, and Laura asked to keep them.

Lighting (55.8%) and Outdoor (55.7%) are close. Before rounding they’re far enough apart, but the category tie-breaker in the sort keeps the order fixed whatever the data does next month.

Common mistakes

• fillna(0) on a cost, price or salary. Zero is a real value, not “unknown”.
• Using .agg("median") or .median() instead of transform, then trying to fill with a table that has six rows.
• Filling with the median of the whole column, which ignores how categories differ.
• Filling unit_cost with the category’s median cost in dollars, which gives the $259 Alder Linen Sideboard the typical cost of a far pricier sofa or bed.