Grouping and aggregation — one answer per group

The problem we are solving

The shop owner sends you one line: "How many units did we sell in each branch this week?"

You have orders.csv — eight orders, one row each. With what the last four chapters taught, you can already answer. Filter one branch, sum its column, repeat:

import pandas as pd

orders = pd.read_csv("orders.csv")

for branch in ["north", "south", "east"]:
    units = orders[orders["branch"] == branch]["quantity"].sum()
    print(branch, units)

north 48
south 6
east 27

The numbers are right. The method has three problems, and each one gets worse as the data grows.

First, you had to know the branches in advance. You typed the list yourself. When next week's file has an order from a new west branch, this loop does not see it — it does not fail, it simply leaves west out, and the totals no longer add up to the whole.

Second, every new question is a new loop. "Revenue per category" means a second loop over a second hand-typed list. "Units per branch and category" means a loop inside a loop.

Third, the answer is not a table. It is lines of printed text. You cannot sort it, filter it, or put it next to another answer without rebuilding it.

What you actually want is to say, in one line: split the rows by branch, sum the quantity inside each piece, and give me the pieces back as a table. That is groupby, and it is how almost every summary in pandas is written — a report, a dashboard number, the first answer to "where is the money coming from?".

By the end of this chapter you can

• Turn a question like "units per branch" into its three parts — key, value column, aggregation — and check the key before grouping
• Write df.groupby(key)[column].sum() and read the Series it returns, with the key as its index
• Compute several summaries at once with .agg() and with named aggregation
• Tell size() from count(), and decide what happens to missing keys and missing values
• Group by two columns, flatten or reshape the result, and avoid the "average of averages" mistake

Prerequisites: Sorting rows.

The file we are using

The same orders.csv as the previous chapters — eight orders from a small shop. If you do not have it, create it in your project folder with exactly these lines:

order_id,date,branch,product,category,quantity,price
1001,2024-03-01,north,pen,stationery,12,15.0
1002,2024-03-01,south,notebook,stationery,5,60.0
1003,2024-03-02,north,bag,accessories,2,850.0
1004,2024-03-02,east,bottle,accessories,7,120.0
1005,2024-03-03,north,eraser,stationery,30,8.0
1006,2024-03-03,south,bag,accessories,1,850.0
1007,2024-03-04,east,pen,stationery,20,15.0
1008,2024-03-04,north,bottle,accessories,4,120.0

One row is one order: which branch it came from, which product, its category, how many units and the price of one unit.

Before you write the code

Every grouped question has the same skeleton. Before typing groupby, take the question apart.

1. Say the question in three parts

A grouped question always contains three things, and the words of the question tell you which is which:

• Key — the column whose values define the groups. It is the word after per, by or for each. In "units per branch" it is branch.
• Value — the column being summarised: the thing being measured. Here, quantity.
• Aggregation — how many values become one: total, average, how many, largest. Here, sum.

A few more, so the pattern sticks:

• "Revenue per category" — key category, value revenue, aggregation sum
• "Average order size in each branch" — key branch, value quantity, aggregation mean
• "How many orders each day" — key date, no value column (you count rows), aggregation size
• "Biggest single order per product" — key product, value quantity, aggregation max

If you cannot name all three parts, the question is not ready for code yet. "Which branch is best?" has no value and no aggregation — best by units? by revenue? by number of orders? Ask before you compute. The three answers can be different branches.

2. Look at the input before grouping it

Grouping hides rows. Once the eight orders become three totals, a broken row is invisible inside a sum. So the checks happen before:

import pandas as pd

orders = pd.read_csv("orders.csv")

print(orders.shape)
print(orders.dtypes)

(8, 7)
order_id      int64
date            str
branch          str
product         str
category        str
quantity      int64
price       float64
dtype: object

Two things matter here. The value column, quantity, is int64 — numeric, so sum will add rather than glue text together. The key column, branch, is str, which is fine for a key.

Then look at the key's actual values. This is the check people skip, and it is the one that matters most:

print(orders["branch"].value_counts())
print(orders[["branch", "quantity"]].isna().sum())

branch
north    4
south    2
east     2
Name: count, dtype: int64
branch      0
quantity    0
dtype: int64

Three distinct branches, nothing missing in either column. groupby makes one group per distinct value, character for character — so "north" and "north " would be two groups. value_counts() is where you would see that, before it becomes a wrong report.

Here is what happens when the check is skipped. Three rows that all mean the north branch — one typed correctly, one with a trailing space, one with a capital letter:

messy = pd.DataFrame({"branch": ["north", "north ", "North"], "quantity": [12, 2, 30]})
print(messy.groupby("branch")["quantity"].sum())

messy["branch"] = messy["branch"].str.strip().str.lower()
print(messy.groupby("branch")["quantity"].sum())

branch
North     30
north     12
north      2
Name: quantity, dtype: int64
branch
north    44
Name: quantity, dtype: int64

No error, and three "branches" where there should be one. Each group's number is correct for what pandas was given, and the report is still wrong. Cleaning the key — strip the spaces, lower the case — before grouping makes them one group with the true total of 44.

3. Sketch the output before you produce it

Decide what the answer should look like, so you can tell when it does not:

branch    units
east          ?
north         ?
south         ?

From the checks above you already know three facts about it: it will have exactly 3 rows (one per distinct branch), the branches will come out in alphabetical order (groupby sorts its keys), and the three numbers must add up to the total of the whole column. That last one is your proof afterwards.

4. Choose the tool

• One number per group, from one column: df.groupby(key)[col].sum() (or mean, max, ...)
• Several numbers from one column: df.groupby(key)[col].agg(["sum", "mean"])
• Several numbers from several columns, with your own names: df.groupby(key).agg(name=(col, "sum"), ...)
• How many rows each group has: df.groupby(key).size()
• A column that does not exist yet, such as revenue: create it first, then group

Now the code.

Split, apply, combine

groupby does three things in order, and the names are worth knowing because every grouped question is these three steps:

1. Split the rows into pieces, one per distinct key value
2. Apply a calculation to each piece separately
3. Combine the results into one new object, indexed by the key

You can see the split on its own. A groupby object can be looped over, giving you each key and its piece of the table:

for branch, part in orders.groupby("branch"):
    print(branch, part.shape, list(part["order_id"]))

east (2, 7) [1004, 1007]
north (4, 7) [1001, 1003, 1005, 1008]
south (2, 7) [1002, 1006]

Each piece is an ordinary DataFrame with all seven columns — the north piece has four rows, the other two have two. Nobody had to type the branch names; pandas found them in the data. An order from a new west branch next week would simply be a fourth piece.

You will rarely loop like this in real code. It is here so the picture is clear: everything below is "do something to each piece, then glue the answers together".

One number per group

The question "units per branch" in the three parts — key branch, value quantity, aggregation sum — is written in exactly that order:

units = orders.groupby("branch")["quantity"].sum()
print(units)

branch
east     27
north    48
south     6
Name: quantity, dtype: int64

Read it left to right: group by branch, take quantity, sum it. Same numbers as the loop, in one line, with no list of branches typed by hand.

Now check it against the sketch. Three rows — yes. Alphabetical — yes. And the proof:

print(units.sum(), orders["quantity"].sum())

81 81

The groups add up to the whole, so no row was lost and none was counted twice. This one line of checking is worth making a habit: whenever you group, compare the total of the result with the total of the input.

The result is a Series, and the key is its index

print(type(units))
print(units.index)
print(units["north"])

<class 'pandas.Series'>
Index(['east', 'north', 'south'], dtype='str', name='branch')
48

The combine step put the branches into the index, not into a column. That is why the output has branch on a line of its own above the values — it is the index's name. It also means you look a value up by branch name, units["north"], exactly as you would with .loc in chapter four.

Because it is an ordinary Series, everything from earlier chapters works on it. Sorting it answers "which branch sold most":

print(units.sort_values(ascending=False))
print(units.idxmax())

branch
north    48
east     27
south     6
Name: quantity, dtype: int64
north

idxmax() returns the label of the largest value — the branch, not the number. For "who is top" that is usually what you want.

Why you choose the column first

It is tempting to skip the ["quantity"] and let pandas sum everything:

print(orders.groupby("branch").sum())

order_id                                      date  ... quantity  price
branch                                                      ...                
east        2011                      2024-03-022024-03-04  ...       27  135.0
north       4017  2024-03-012024-03-022024-03-032024-03-04  ...       48  993.0
south       2008                      2024-03-012024-03-03  ...        6  910.0

[3 rows x 6 columns]

No error — and almost every number in it is nonsense. order_id was added up, as if order numbers were amounts. The date strings were glued end to end, because "summing" text means joining it. price was summed too: the 993.0 for north is the prices of four different products added together, which measures nothing.

orders.groupby("branch").sum() raises no error. It adds up ID numbers, glues dates together and totals unit prices, and hands the result back looking exactly like a report.

Only quantity is meaningful, and you could have asked for only that. This is the reason the rule is pick the value column before the aggregation: sum does whatever addition means for each type, and for most columns that is not a question anyone asked.

mean is stricter, and refuses rather than produce nonsense:

print(orders.groupby("branch").mean())

TypeError: dtype 'str' does not support operation 'mean'

That error is the same mistake made visible. Read it as: you asked me to average a text column. The fix is not numeric_only=True — that would still average order_id and price — but to say which column you mean.

Several numbers at once: .agg()

One summary is rarely enough. "How much did each branch sell, on average per order, and over how many orders?" is three aggregations of the same column. Pass a list of their names to .agg():

print(orders.groupby("branch")["quantity"].agg(["sum", "mean", "count"]))

sum  mean  count
branch                  
east     27  13.5      2
north    48  12.0      4
south     6   3.0      2

Now the result is a DataFrame — one column per aggregation, one row per branch. It tells a story the total alone hid: east sold just over half of north's units, but its average order is bigger. The north branch gets more orders; east gets fewer, slightly larger ones.

The names in the list are strings: "sum", "mean", "count", "min", "max", "median", "nunique" (number of distinct values), "first", "last". They are the same methods you have used on a single column, applied to each piece.

Named aggregation: your columns, your names

Real reports summarise different columns in different ways, and want readable column names. Named aggregation does both in one call. Each keyword is the name of an output column, and its value is a pair: (input column, aggregation).

Revenue is the obvious thing to report, and it is not a column yet — it is quantity times price on each row. Creating columns gets a chapter of its own, coming next; for now one line is enough, and it shows step 4 of the plan in action: if the value column does not exist, create it before grouping.

orders["revenue"] = orders["quantity"] * orders["price"]

summary = orders.groupby("branch").agg(
    orders=("order_id", "count"),
    units=("quantity", "sum"),
    revenue=("revenue", "sum"),
)
print(summary)

orders  units  revenue
branch                        
east         2     27   1140.0
north        4     48   2600.0
south        2      6   1150.0

This is a report you could send. Each line of the agg call reads like a sentence: orders is the count of order_id, units is the sum of quantity, revenue is the sum of revenue. The output column names are chosen by you, not inherited as sum and count, so there is no renaming afterwards.

And it changes the earlier answer. By units east was clearly second; by revenue south edges ahead — one bag at 850 outweighs twenty pens. This is why step 1 insisted on naming the value: "best branch" by units and "best branch" by revenue are different questions.

The result is a DataFrame, so it sorts like one:

print(summary.sort_values("revenue", ascending=False))

orders  units  revenue
branch                        
north        4     48   2600.0
south        2      6   1150.0
east         2     27   1140.0

Counting: size() against count()

"How many orders per branch" sounds like one question. Pandas has two answers, and they differ exactly when data is missing.

• size() counts rows in each group, whatever is in them.
• count() counts non-missing values of a column in each group.

On the clean file they agree. To see them disagree, here is a small table, built by hand next to orders, with one missing branch and one missing quantity:

gaps = pd.DataFrame(
    {
        "order_id": [1001, 1002, 1003, 1004, 1005],
        "branch": ["north", "south", None, "east", "north"],
        "quantity": [12, 5, 2, None, 30],
    }
)
print(gaps)

order_id branch  quantity
0      1001  north      12.0
1      1002  south       5.0
2      1003    NaN       2.0
3      1004   east       NaN
4      1005  north      30.0

(quantity turned into a float because of the missing value — chapter six explained why.)

print(gaps.groupby("branch").size())
print(gaps.groupby("branch")["quantity"].count())

branch
east     1
north    2
south    1
dtype: int64
branch
east     0
north    2
south    1
Name: quantity, dtype: int64

east has one order (size = 1) but zero known quantities (count = 0). Both are true; they answer different questions. Use size() for "how many orders", and count() for "how many orders do I actually have a number for".

Two things missing data does silently

Look again at those outputs. Order 1003, the one with no branch, is in neither of them. Five orders went in; four came out:

print(gaps.groupby("branch").size().sum(), len(gaps))

4 5

By default groupby drops rows whose key is missing. There is no group called "unknown", so the row disappears from every result. The total check from earlier catches this immediately. If those rows matter — and an order with no branch is still an order — ask for them with dropna=False:

print(gaps.groupby("branch", dropna=False)["quantity"].sum())

branch
east      0.0
north    42.0
south     5.0
NaN       2.0
Name: quantity, dtype: float64

Now all four groups are present, and the NaN group holds the 2 units nobody could place.

The second silent thing is in the same output: east shows 0.0. The east branch did not sell zero; its only quantity is unknown. sum treats "nothing to add" as zero. If "unknown" must stay unknown, say so with min_count=1 — "only produce a sum if at least one real value exists":

print(gaps.groupby("branch", dropna=False)["quantity"].sum(min_count=1))

branch
east      NaN
north    42.0
south     5.0
NaN       2.0
Name: quantity, dtype: float64

Which one is right depends on the question, and that is the point: decide it in the planning step, not by accident. The fix for both problems is usually earlier anyway — clean the missing values (chapter six) before you group.

Two keys: groups inside groups

"Revenue per branch and category" has two keys. Pass a list:

by_both = orders.groupby(["branch", "category"])["revenue"].sum()
print(by_both)

branch  category   
east    accessories     840.0
        stationery      300.0
north   accessories    2180.0
        stationery      420.0
south   accessories     850.0
        stationery      300.0
Name: revenue, dtype: float64

One row per combination that actually occurs — six here. The index now has two levels, branch and category; pandas calls this a MultiIndex, and prints the outer level only once per block to keep it readable. The blank under east means "still east".

You can select from it the way you would expect:

print(by_both.loc["north"])
print(by_both.loc[("north", "stationery")])

category
accessories    2180.0
stationery      420.0
Name: revenue, dtype: float64
420.0

One label gives you all of north; a tuple of both labels gives you one number.

Getting ordinary columns back

A MultiIndex is compact, but many next steps — saving to CSV, filtering with a mask, combining it with another table, which is coming later — are easier with plain columns. There are two ways, and they give the same table:

flat = orders.groupby(["branch", "category"], as_index=False)["revenue"].sum()
print(flat)
print(flat.equals(by_both.reset_index()))

branch     category  revenue
0   east  accessories    840.0
1   east   stationery    300.0
2  north  accessories   2180.0
3  north   stationery    420.0
4  south  accessories    850.0
5  south   stationery    300.0
True

as_index=False tells groupby not to move the keys into the index in the first place; reset_index() moves them back out afterwards. Use whichever reads better where you are.

Reshaping for people: unstack()

For a person reading the result, a grid is better than a long list — branches down the side, categories across the top:

print(by_both.unstack())

category  accessories  stationery
branch                           
east            840.0       300.0
north          2180.0       420.0
south           850.0       300.0

unstack() takes the inner index level, category, and turns its values into columns. The numbers are exactly the six from before. If some branch had never sold a category, its cell would be NaN; unstack(fill_value=0) writes 0 there instead.

The average-of-averages trap

A tempting question: what does a unit cost, on average, in each branch? The quick answer is to average the price column:

print(orders.groupby("branch")["price"].mean())

branch
east      67.50
north    248.25
south    455.00
Name: price, dtype: float64

That is the average of the prices on the order lines, and every line counts once — the south branch's single bag (1 unit at 850) weighs the same as its notebooks (5 units at 60). But customers did not buy one of each line. They bought units. The average price per unit sold is total revenue divided by total units, and it has to be computed from sums:

per_unit = orders.groupby("branch").agg(
    units=("quantity", "sum"),
    revenue=("revenue", "sum"),
)
per_unit["avg_unit_price"] = (per_unit["revenue"] / per_unit["units"]).round(2)
print(per_unit)

units  revenue  avg_unit_price
branch                                
east       27   1140.0           42.22
north      48   2600.0           54.17
south       6   1150.0          191.67

The figure for south falls from 455 to about 192. Neither number is "wrong" as arithmetic; only one answers the question. The rule: when the thing you want is a ratio, aggregate the numerator and the denominator separately, then divide. Averaging something that is already an average, or a price that is already per-unit, weights every row equally whether it was one unit or thirty.

A complete example

branch_report.py answers the owner's question properly — per branch: number of orders, units, revenue, the share of total revenue, and the best-selling product by units — and proves its own totals.

import pandas as pd

orders = pd.read_csv("orders.csv")

# 1. Check the input before grouping hides anything.
print("Rows, columns:", orders.shape)
print("Branches:", sorted(orders["branch"].unique()))
print("Missing in key/value:", int(orders[["branch", "quantity", "price"]].isna().sum().sum()))
print()

# 2. Create the value column that does not exist yet.
orders["revenue"] = orders["quantity"] * orders["price"]

# 3. One grouped call, one row per branch.
report = orders.groupby("branch").agg(
    orders=("order_id", "count"),
    units=("quantity", "sum"),
    revenue=("revenue", "sum"),
)
report["share_pct"] = (report["revenue"] / report["revenue"].sum() * 100).round(1)

# 4. Best product per branch: group by both keys, then keep each branch's top row.
by_product = orders.groupby(["branch", "product"], as_index=False)["quantity"].sum()
top = by_product.sort_values("quantity", ascending=False).drop_duplicates("branch")
report["top_product"] = top.set_index("branch")["product"]

report = report.sort_values("revenue", ascending=False)
print(report)
print()

# 5. Prove nothing was lost or double-counted.
print("Revenue check:", report["revenue"].sum(), "==", orders["revenue"].sum())
print("Orders check :", report["orders"].sum(), "==", len(orders))

Rows, columns: (8, 7)
Branches: ['east', 'north', 'south']
Missing in key/value: 0

        orders  units  revenue  share_pct top_product
branch                                               
north        4     48   2600.0       53.2      eraser
south        2      6   1150.0       23.5    notebook
east         2     27   1140.0       23.3         pen

Revenue check: 4890.0 == 4890.0
Orders check : 8 == 8

Why it is written this way:

• The checks come first and are printed. Three distinct branches and zero missing values are the conditions under which the report is trustworthy. If next week's file says four branches or two missing, you see it at the top, before you read any total.
• revenue is created before grouping. Summing quantity * price per row and then grouping gives true revenue. Multiplying a summed quantity by an averaged price afterwards would be the average-of-averages trap again.
• One agg call, named columns. Each output column is declared once, with the name the reader will see. Adding a fourth measure is one more line.
• share_pct is computed on the result, not the input. After grouping, report is an ordinary DataFrame indexed by branch, so ordinary column arithmetic works on it. Its percentages add to 100.0.
• The top product uses two-key grouping, then sorting. groupby(["branch", "product"]) gives units per branch-product pair; sorting by quantity descending and keeping the first row per branch (drop_duplicates("branch")) leaves each branch's best seller. Assigning top.set_index("branch")["product"] lines the products up by branch label, not by position — the index does the matching.
• The last two lines are the proof. Revenue and order counts of the groups equal those of the input. Had a branch been missing, or the file had a blank branch, the numbers would differ and you would know before the report went out.

When it breaks

KeyError: 'Branch' The key column is spelt differently from the file — here a capital B. Column names are case-sensitive, and groupby checks them immediately. Print list(orders.columns) and copy the name exactly; the quotes in a list also expose trailing spaces such as 'branch '.

KeyError: 'Column not found: Quantity' Same mistake, one step later: the key was fine but the value column in groupby("branch")["Quantity"] does not exist. The message says "Column not found" rather than just the name, which tells you it was the selection after groupby that failed.

KeyError: "Label(s) ['qty'] do not exist" In named aggregation, the first item of each pair must be a real column: units=("qty", "sum") fails because there is no qty. The output name on the left (units) can be anything; the input on the right cannot.

ValueError: Cannot subset columns with a tuple with more than one element. Use a list instead. You wrote orders.groupby("branch")["quantity", "price"]. Two names inside one pair of brackets make a tuple. To select several columns you need a list — double brackets, exactly as in chapter four: orders.groupby("branch")[["quantity", "price"]].sum().

TypeError: dtype 'str' does not support operation 'mean' An aggregation reached a text column — either because no column was selected (groupby("branch").mean()), or because the column you selected came in as text. In the second case, info() will show str where you expected a number; chapter six's pd.to_numeric(..., errors="coerce") is the repair.

KeyError: 'quantity' when reading the result After units = orders.groupby("branch")["quantity"].sum(), the branches are the index of units and quantity is only its name. units["quantity"] looks for a branch called quantity. Use units["north"], or units.sum() for the total.

Your turn

The owner now wants the same kind of answer by category and by day. Using orders.csv, write category_report.py that does exactly these four things:

1. Reads the file and prints shape, the sorted list of distinct categories, and the number of missing values in category, quantity and price together
2. Creates a revenue column, then builds one table with one row per category and these columns, sorted by revenue, highest first: orders (number of orders), units (total quantity), revenue (total revenue), avg_order_units (average quantity per order) and avg_unit_price (revenue divided by units, rounded to 2 places)
3. Prints revenue per day, then the single day with the highest revenue
4. Prints a proof line: the revenue of the category table, of the daily table and of the whole file

Before writing code, plan on paper: for conditions 2 and 3, name the key, the value and the aggregation for each column, and write down how many rows each result must have.

When it is right, the program prints exactly this:

Rows, columns: (8, 7)
Categories: ['accessories', 'stationery']
Missing: 0

             orders  units  revenue  avg_order_units  avg_unit_price
category                                                            
accessories       4     14   3870.0             3.50          276.43
stationery        4     67   1020.0            16.75           15.22

date
2024-03-01     480.0
2024-03-02    2540.0
2024-03-03    1090.0
2024-03-04     780.0
Name: revenue, dtype: float64
Best day: 2024-03-02 with 2540.0

Proof: 4890.0 4890.0 4890.0

Then break it on purpose, and notice what happens each time:

• Change the category of order 1001 to "Stationery" (capital S). How many rows does your table have now, and does the proof still pass?
• Replace "sum" with "mean" for units. Which number changes, and which question does it now answer?
• Compute avg_unit_price as the mean of price instead. Which category moves most, and why?

Solution

Plan first.

• Condition 2: key category. orders = count of order_id; units = sum of quantity; revenue = sum of revenue; avg_order_units = mean of quantity; avg_unit_price = not an aggregation of one column — it is a ratio, so it is computed afterwards from revenue and units. The file has two categories, so the table has 2 rows.
• Condition 3: key date, value revenue, aggregation sum. Four distinct dates, so 4 rows; the best day is the index label of the maximum, which is idxmax().
• The proof: both tables' revenue must total the file's 4890.0.

category_report.py:

import pandas as pd

orders = pd.read_csv("orders.csv")

print("Rows, columns:", orders.shape)
print("Categories:", sorted(orders["category"].unique()))
print("Missing:", int(orders[["category", "quantity", "price"]].isna().sum().sum()))
print()

orders["revenue"] = orders["quantity"] * orders["price"]

table = orders.groupby("category").agg(
    orders=("order_id", "count"),
    units=("quantity", "sum"),
    revenue=("revenue", "sum"),
    avg_order_units=("quantity", "mean"),
)
table["avg_unit_price"] = (table["revenue"] / table["units"]).round(2)
table = table.sort_values("revenue", ascending=False)
print(table)
print()

daily = orders.groupby("date")["revenue"].sum()
print(daily)
print("Best day:", daily.idxmax(), "with", daily.max())
print()

print("Proof:", table["revenue"].sum(), daily.sum(), orders["revenue"].sum())

Rows, columns: (8, 7)
Categories: ['accessories', 'stationery']
Missing: 0

             orders  units  revenue  avg_order_units  avg_unit_price
category                                                            
accessories       4     14   3870.0             3.50          276.43
stationery        4     67   1020.0            16.75           15.22

date
2024-03-01     480.0
2024-03-02    2540.0
2024-03-03    1090.0
2024-03-04     780.0
Name: revenue, dtype: float64
Best day: 2024-03-02 with 2540.0

Proof: 4890.0 4890.0 4890.0

Two rows and four rows, as planned, and all three revenue totals agree. Notice what the table says that neither column alone would: stationery moves almost five times as many units, but accessories bring in nearly four times the money.

What the three breakages show.

• With "Stationery" on one row the table has 3 rows — Stationery is its own group with one order. The proof still passes, because no row was lost; it was only misfiled. That is why the distinct-categories line is printed at the top: it would now list three categories, and you would see the problem before reading any number.
• With "mean", units becomes 3.5 and 16.75 — the same as avg_order_units. It now answers "how big is a typical order", not "how much did we sell". avg_unit_price also changes, because it divides revenue by the wrong thing.
• Averaging price gives accessories (850 + 120 + 850 + 120) / 4 = 485.0, far above the true 276.43, because the two one- and two-unit bag orders count as much as the seven- and four-unit bottle orders. Stationery rises from 15.22 to 24.5 — less dramatic only because its prices are all low. Averaging a per-unit number over order lines is the trap; dividing sums is the fix.