Level 7 · Debug: the bestseller units are too low

Count is not sum

Both functions take a column, and both return one number per group, which is why they are easy to mix up:

• "count" answers “how many rows have a value in this column?” On quantity that is the number of order lines for the product.
• "sum" answers “what do the values add up to?” On quantity that is the number of units.

A customer who buys 6 wool throws in one order adds 1 to the count and 6 to the sum. Business customers often buy in bulk, so on this data the two measures are far apart:

order_items.groupby("product_id").agg(
    lines=("quantity", "count"),
    units=("quantity", "sum"),
).sort_values("units", ascending=False).head(3)

product_id	lines	units
166	1262	2565
193	1339	2545
186	1121	2268

Product 193, the Delancey Brass Vase, appears on more lines, but product 166, the Harbor Wool Knit Throw, sells more units. The starter’s ranking put the vase first; Debra’s warehouse counts say otherwise, and she is right.

Debugging by reading the numbers

Debra’s message holds two clues. The number for one product is roughly half what she shipped, and the order of the top two is swapped. “Too low by about half” fits counting lines when the average line holds about two units (the mean quantity is 2.0). Before editing code, it’s worth asking “what would produce exactly this wrong number?” Here the answer points straight at the aggregation function, and the fix is one word.

About the bad lines

order_items has 24 lines with a quantity of 0 or less, data-entry errors you’ll clean in chapter 8. The ticket asks you to use the lines as they are. A few of them belong to top-ten products, so they nudge the totals down by a unit or a few, but they don’t change the ranking.

Common mistakes

• Using "count" or "size" when the question is about units.
• Summing unit_price instead of quantity.
• Dropping the product_id tie-breaker, so equal totals could come out in any order.
• Taking .head(10) before sorting, which returns the first ten products by id.