Level 3 · A category summary table

Several numbers per group

A summary table usually needs more than one statistic. agg computes several at once. The clearest form is named aggregation: each keyword is the name of an output column, and its value is a (column, function) pair.

products.groupby("category").agg(
    n_products=("product_id", "count"),
    avg_price=("unit_price", "mean"),
    max_price=("unit_price", "max"),
)

You decide the column names up front, so there’s no renaming afterwards and no confusing multi-level headers. This is the style to use in shared code.

The function can be a string ("sum", "mean", "median", "min", "max", "count", "size", "nunique", "first") or any function that takes a Series and returns one value:

products.groupby("category").agg(
    price_range=("unit_price", lambda s: s.max() - s.min()),
)

Keeping the key as a column

By default the group keys become the index. With as_index=False, they stay a regular column, which is what you want for a table you’ll sort, merge or export:

products.groupby("category", as_index=False).agg(...)

Calling .reset_index() after a normal groupby gives the same shape.

Rounding selected columns

DataFrame.round accepts a dictionary, so you can round only what needs it:

summary.round({"avg_price": 2})

Rounding the whole table with .round(2) is fine too, since counts are whole numbers anyway. Just avoid rounding the raw price column before averaging, which would change the result.

Other aggregation styles you’ll see

Older code and tutorials use other forms. They work, but produce less predictable column names:

products.groupby("category")["unit_price"].agg(["mean", "max"])   # columns: mean, max
products.groupby("category").agg({"unit_price": ["mean", "max"]}) # two-level column headers

The dictionary form gives a MultiIndex for the columns (("unit_price", "mean")), which is awkward to work with. Named aggregation avoids it.

Counting products

("product_id", "count") counts non-missing IDs. Since IDs are never missing, that equals the number of rows. For a count of distinct products in a table where they repeat, such as order lines, use ("product_id", "nunique") instead. Choosing between count, size and nunique is a correctness decision, not a style one.

Reading the result

Every category has 20 products, so the interesting columns are the prices. Furniture’s average is far higher than Decor’s; compare the average with the maximum to spot categories where one or two premium items pull the average up.

Common mistakes

• Writing n_products=("product_id", count) with the bare name count, which isn’t defined. Use the string "count" (or the built-in len).
• Forgetting as_index=False, so category is the index and not a column.
• Mistyping the column inside the tuple: ("price", "mean") raises KeyError.

If you know SQL

Named aggregation is the pandas version of SELECT category, COUNT(product_id) AS n_products, AVG(unit_price) AS avg_price ....