Groups: groupby
In short: groupby in pandas splits the rows by a key, computes in each group (sum, mean, agg) and combines the result into one table.
Problem
“How big is the average order?”
The manager asks how much a customer spends on one order on average. You have the orders of the year and their lines: every line is one product in one order, with the quantity and the price. Cancelled orders are not revenue, so you remove them with a mask, as in the lesson on filters:
There are 25,069 lines and 10,459 orders. The mean of the revenue column is €4.44, but that is the average line, not the average order. An order has several lines, and for an order you first have to add up its lines.
You need a total for every order: 10,459 numbers from 25,069 lines. A for loop with a dictionary can do it, but it is slow and long. pandas has one operation for it.
The idea
Split, apply, combine
groupby('order_id') splits the rows into groups: one group for each different value of order_id. Then you pick a column and a calculation: ['revenue'].sum() adds up the revenue in each group. At the end pandas puts the results into one Series with order_id as its index.
This is called split-apply-combine. Here it is on the first 7 lines:
Order 1 has one line (€31.60), and orders 2, 3 and 4 have two each. Order 3 is 37.5 + 8.4 = €45.90. Out of 7 lines come 4 numbers, one per group. The key order_id is the index of the result, which is why it sits under the name of the column.
Instead of sum you can use any summary from NumPy: mean, min, max, count.
See it
How groupby goes through the rows
The same 7 lines, slowly: every line goes into the group of its order_id, the group adds up to one number, and the numbers line up in the result.
import pandas as pd
items = pd.read_csv('order_items.csv').head(7)items['revenue'] = items['quantity'] * items['unit_price']print(items[['order_id', 'revenue']])items.groupby('order_id')['revenue'].sum()Step by step, in words
- 7 rows from order_items with the revenue of each line: orders 1, 2, 3 and 4.
- Split: groupby('order_id') puts every row into the group of its order_id.
- 4 different order_id values, 4 groups. Order 1 has one row, the others 2 each.
- Apply: ['revenue'].sum() in every group. Order 3: 37.5 + 8.4 = 45.9.
- Combine: the 4 sums become one Series with order_id as its index.
- 7 rows went in, 4 numbers came out: one for each group.
Try it
Which product is ordered most
In the 60 orders of order_items.csv: how many units of each product were ordered? Put them in units, and the three products with the most units in top3.
Solution
import pandas as pd
items = pd.read_csv('order_items.csv')
units = items.groupby('product_id')['quantity'].sum()
top3 = units.nlargest(3)
print(top3)units = items.groupby('product_id')['quantity'].sum() gives one number for every product, and units.nlargest(3) gives products 13, 5 and 10: cucumbers (31 units), fresh milk (27) and apples (25). The cucumbers have a stock of 0: they sold out because people want them. With count() instead of sum() you would count the lines, and one line can be 4 units.
The idea
Several calculations at once: agg
You often need several numbers for each group. agg takes a list of calculations and gives a column for each:
The columns are named after the calculations. For a report it is better to name them yourself. These are named aggregations: new_name=('column', 'calculation'), and each can use a different column:
Order 3 has 2 lines, 7 units and €45.90 of revenue. The table has a row per order and a column for every question you asked.
The idea
The count: size or count
The two counters look alike, but they count different things. size() counts the rows in each group. count() counts the filled-in values in each column: NaN is not counted. Here are the customers by the year they signed up:
16 customers signed up in 2025, but the city column has 15 values: one customer's city is missing. If you want “how many customers”, size() is the right answer. count() on a column answers “for how many do we know the city”.
The idea
Several keys
The key can be a list of columns. Then a group is every pair of values. Here are the units of the year by product and by price:
There are 25 products and 26 groups: one product was sold at two prices. That is coffee (product 17): 245 packs at €13.90 and 444 packs at €14.90, after the price went up on 1 March. The result has an index with two levels, and units.loc[17] takes the groups of coffee.
More precisely, if you are curious
Groups with NaN in the key drop out: the gift card has no category, and products.groupby('category_id') quietly leaves it out. groupby('category_id', dropna=False) keeps it as a NaN group. Check with len how many rows go into the groups and how many come out.
The idea
The group's number on every row: transform
Sometimes you do not want to shrink the group, but to put its number next to each of its rows. For example: how expensive a product is compared with the average price of its category. transform('mean') works out the mean of the group and repeats it on every row, keeping the order and the index of the table:
transform gives 25 numbers, one per product, and mean() gives 6, one per category. That is why the cat_avg column fits straight into the table. Coffee costs €14.90, and the average in drinks is €5.35: 2.79 times as much. Laundry powder is 2.19 times the average for cleaning.
More precisely, if you are curious
The result of mean() has category_id as its index, and the products table has the index 0, 1, 2… If you assign it as a column, pandas lines it up by the index, not by the category, and almost everything becomes NaN. transform keeps the index of the table. The gift card has no category, so its cat_avg is NaN.
Try it
The line's share of the order
For every line of order_items.csv: what part of its order's revenue does it bring in? Add to items a column order_total with the revenue of the whole order and a column share with the line's share.
Solution
import pandas as pd
items = pd.read_csv('order_items.csv')
items['revenue'] = items['quantity'] * items['unit_price']
items['order_total'] = items.groupby('order_id')['revenue'].transform('sum')
items['share'] = items['revenue'] / items['order_total']
print(items[items['order_id'] == 3])items.groupby('order_id')['revenue'].transform('sum') puts the order's revenue on each of its lines, and items['revenue'] / items['order_total'] gives the share. In order 3 the laundry powder brings in 37.5 of €45.90, about 82%, and the apples about 18%. The shares of the lines of one order always add up to 1. With .sum() instead of transform the column would be almost all NaN: the total is per order, and the table is per line.
The idea
The key as a column: as_index=False
The result of groupby has the key in the index. To filter it, join it with another table or put it on a chart, you need an ordinary column. There are two ways, with the same result:
as_index=False says it at grouping time, and reset_index() does it afterwards: the index becomes a column, and the rows are numbered 0, 1, 2… The columns are order_id and revenue.
Exercise
The average order
Answer the manager from the first card. Put the revenue of every order of the year without the cancelled ones in order_totals, and the average order, rounded to 2 places, in avg_order.
Solution
import pandas as pd
orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
cancelled = orders.loc[orders['status'] == 'cancelled', 'id']
items = items[~items['order_id'].isin(cancelled)]
items['revenue'] = items['quantity'] * items['unit_price']
order_totals = items.groupby('order_id')['revenue'].sum()
avg_order = round(order_totals.mean(), 2)
print(len(order_totals), avg_order)order_totals = items.groupby('order_id')['revenue'].sum() gives 10,459 numbers, one per order, and round(order_totals.mean(), 2) gives €10.63. The average line was €4.44: an order has 2.4 lines on average. With the cancelled ones there would be 11,116 orders and an average of €10.61, close but not right: a cancelled order is not revenue.
How many customers ordered at least once during the year, and how many of them only once? Put the number of orders of every customer in per_customer, the number of customers in n_customers, and how many have exactly one order in once.
Solution
import pandas as pd
orders = pd.read_csv('year_orders.csv')
per_customer = orders.groupby('customer_id').size()
n_customers = len(per_customer)
once = (per_customer == 1).sum()
print(n_customers, once, per_customer.max())per_customer = orders.groupby('customer_id').size() gives one number for every customer: 1,390 customers. (per_customer == 1).sum() counts 224 with one order, and the most loyal customer has 121 orders, 2 or 3 a week. len(orders) would give the orders (11,116), and per_customer.min() only the smallest count, 1.
Test