Lesson 2 of 6 in this module

9/20 in track

Grouping & aggregations

pandas10 min

Overview

Reports usually ask for a value per group. Learn groupby and named aggregations, then use transform when the group result must stay beside every original row.

On this page8 sections

The table we keep using

A manager does not want to read 180 order rows. They want one line per status: how many orders and how much money.

Split the orders by status, add up each group, and put the results together. That is groupby.

What does 1 row per order look like?

We start with a 5-order sample from the ShopKart dataset. Each row is one order, and the table is a list of single purchases.

5 rows, one per order. Three are paid.

order_idorder_statusorder_total
ORD-0001paid612.07
ORD-0002refunded446.70
ORD-0003paid1104.32
ORD-0004cancelled339.90
ORD-0005paid261.73

Nobody reads 180 of these. A manager wants one line per status. That is a groupby.

What are split, apply and combine?

Grouping is three moves. Split the rows into piles that share a key, reduce each pile to 1 row, then stack those rows into a summary.

Split, apply, combine
orderspaid | pending | refundedcount + sum eachstatus summary

Rows are split by status, each group is reduced to one summary row, and the results are combined.

Five sample orders split by order_status into piles: cancelled holds one order, paid holds three and refunded holds one. Count and sum are applied to each pile and combined into three rows, with paid showing 3 orders and 1978.12
The same five orders as the table above. Rows with the same status go to the same pile, each pile becomes one row, and the rows are stacked.

groupby splits the rows, reduces each pile, and stacks the results.

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
groupbySplit rows into groups by a column
AggregateCombine many rows into one number
SplitCut the rows into groups
GrainWhat one row stands for
aggRun several aggregates at once
IndexThe label on each row

What we need from it

Which questions are really group questions?

Business questions are almost always about groups: revenue per category, orders per country, average order value per status.

Same key, three different calculations.

QuestionGroup byCalculation
How many orders in each status?order_statuscount of order_id
How much money in each status?order_statussum of order_total
What is the average order per status?order_statusmean of order_total

Without groupby you would filter for each status by hand and repeat the sums. The same idea is SQL's GROUP BY.

What happens to the grain?

Grain is what 1 row stands for. Before grouping, a row is one order. After, a row is one status.

  1. 180 rows, one per order
  2. groupby order_status
  3. 4 rows, one per status

Counting rows after a bad join, or after a hidden filter, gives numbers that look right and are wrong. So name the grain first.

Trace it step by step

Step 1: what is the output grain?

Finish this sentence before writing code: I want one output row per ___. The blank is your groupby key.

For 1 row per status, the key is order_status. For 1 row per status and size, the key is both columns.

Step 2: what does the split look like?

groupby does not compute anything yet. It sorts the rows into piles. Loop over it to see the piles.

PythonThree piles, one per status
first5 = df_orders.head(5)[["order_id", "order_status", "order_total"]]
for key, pile in first5.groupby("order_status"):
    print(key, pile["order_total"].tolist())
cancelled [339.9]
paid [612.07, 1104.32, 261.73]
refunded [446.7]

Three statuses, three piles, sorted by key name. The paid pile holds 3 orders.

Step 3: how do you reduce each pile?

.agg(...) says what to compute per pile. Each argument is name=(column, function), which is called named aggregation.

Python1 row per pile: how many orders, how much money
r5 = first5.groupby("order_status", as_index=False).agg(
    orders=("order_id", "count"),
    gmv=("order_total", "sum"),
)
print(r5)
  order_status  orders      gmv
0    cancelled       1   339.90
1         paid       3  1978.12
2     refunded       1   446.70

Paid is 612.07 plus 1104.32 plus 261.73, which is 1978.12. 5 rows became three.

Step 4: what does the same code do on all 180 orders?

Swap first5 for df_orders and nothing else changes. That is the point of groupby.

PythonThe status summary for the whole table
result = df_orders.groupby("order_status", as_index=False).agg(
    orders=("order_id", "count"),
    gmv=("order_total", "sum"),
)
print(result)
  order_status  orders       gmv
0    cancelled      30  11248.61
1         paid      93  43606.63
2      pending      29  14478.51
3     refunded      28  14082.40

4 rows, one per status. The order counts add up to 180.

Why as_index=False?

By default, groupby moves the key into the row labels. as_index=False keeps it as a normal column.

PythonWithout as_index=False
d = df_orders.groupby("order_status").agg(
    orders=("order_id", "count"),
    gmv=("order_total", "sum"),
)
print(d)
print(d.columns.tolist())
              orders       gmv
order_status                  
cancelled         30  11248.61
paid              93  43606.63
pending           29  14478.51
refunded          28  14082.40
['orders', 'gmv']

order_status is now the index, not a column, so it is missing from the column list. Use as_index=False when the next step needs it as a column, such as a merge or the grader.

Why named aggregation?

The older way is to pass a dictionary. It works, but the column names come out in 2 layers.

PythonA dictionary of lists gives a MultiIndex
m = df_orders.groupby("order_status").agg({"order_total": ["count", "sum"]})
print(m.columns.tolist())
[('order_total', 'count'), ('order_total', 'sum')]

Each column name is a pair, which is awkward to select. Named aggregation gives flat names like orders and gmv.

Use output_name=(column, function). You get flat, readable column names.

Is count the same as the number of rows?

Not always. count skips missing values, and size counts every row. Blank out one refunded total and compare.

PythonORD-0002 now has no total
tmp = first5.copy()
tmp.loc[1, "order_total"] = np.nan
check = tmp.groupby("order_status").agg(
    rows=("order_id", "size"),
    counted=("order_total", "count"),
    total=("order_total", "sum"),
)
print(check)
              rows  counted    total
order_status                        
cancelled        1        1   339.90
paid             3        3  1978.12
refunded         1        0     0.00

The refunded pile has 1 row but no counted values. Its sum is 0.00, because sum skips blanks. Count a column that is never blank, like order_id, or use size.

The common aggregations and what they do with blanks.

AggregationMeaningNull behavior
sizeRows in each groupCounts every row, including nulls
countNon-null values in the columnSkips nulls
sumTotal numeric valueSkips nulls by default
meanAverage numeric valueSkips nulls by default
nuniqueCount of distinct valuesSkips nulls by default
min / maxSmallest / largest valueSkips nulls by default
Watch outcount is not always row count

count('order_total') counts non-null values in that column. If some totals are NaN, count will be lower than the actual group size. Use 'size' or count a guaranteed non-null column like order_id.

Same table, next cut

What if the grain needs 2 keys?

Pass a list to groupby for a finer grain. Here we split every status into big and small orders, with 400 as the cut.

Python1 row per status and size, largest revenue first
size = np.where(df_orders["order_total"] >= 400, "big", "small")
by_status_size = (
    df_orders.assign(size=size)
    .groupby(["order_status", "size"], as_index=False)
    .agg(orders=("order_id", "count"), gmv=("order_total", "sum"))
    .sort_values(["gmv", "order_status"], ascending=[False, True])
)
print(by_status_size)
  order_status   size  orders       gmv
2         paid    big      49  35104.28
4      pending    big      14  11113.54
6     refunded    big      15  10760.57
3         paid  small      44   8502.35
0    cancelled    big      12   7994.76
5      pending  small      15   3364.97
7     refunded  small      13   3321.83
1    cancelled  small      18   3253.85

8 rows: four statuses times two sizes. The original index labels (2, 4, 6...) stay with their rows after the sort.

What if every row needs its group total?

agg shrinks the table. transform keeps every row and pastes the group result onto each one. Use it for a share of the group.

PythonThe group total, repeated on each row
status_gmv = first5.groupby("order_status")["order_total"].transform("sum")
print(status_gmv.tolist())
[1978.1200000000001, 446.7, 1978.1200000000001, 339.9, 1978.1200000000001]

5 values for 5 rows. The three paid rows all carry 1978.12 (plus a tiny float rounding tail). Now divide.

PythonEach order's share of its own status total
shares = first5.assign(
    status_gmv=status_gmv,
    share=lambda d: (d["order_total"] / d["status_gmv"]).round(4),
)
print(shares)
   order_id order_status  order_total  status_gmv   share
0  ORD-0001         paid       612.07     1978.12  0.3094
1  ORD-0002     refunded       446.70      446.70  1.0000
2  ORD-0003         paid      1104.32     1978.12  0.5583
3  ORD-0004    cancelled       339.90      339.90  1.0000
4  ORD-0005         paid       261.73     1978.12  0.1323
The three paid orders as a share of paid revenue
30.94ORD-000155.83ORD-000313.23ORD-0005

ORD-0001, ORD-0003 and ORD-0005 add up to 100 percent of the 1978.12 paid total.

Refunded and cancelled have only one order each, so each holds 100 percent of its group.

RememberTransform preserves row count

agg reduces rows (one per group). transform keeps all original rows and broadcasts the group result. Use transform when you need a group metric alongside each detail row.

How do you know the grouping is right?

After groupby, check that the keys are unique and the group totals add back to the source total.

PythonReconcile the summary against the source
print(round(result["gmv"].sum() - df_orders["order_total"].sum(), 6))
print(result["order_status"].is_unique)
print(result["orders"].sum() == len(df_orders))
0.0
True
True

Zero difference, 1 row per key, and every order counted once. A check that fails points to a missing category or a hidden filter.

What happens to rows with a missing key?

By default, groupby drops them without a word. A reconcile check is what catches this.

PythonDefault, then dropna=False
t2 = pd.DataFrame({"s": ["a", "a", None], "v": [1, 2, 3]})
print(t2.groupby("s")["v"].sum())
print(t2.groupby("s", dropna=False)["v"].sum())
s
a    3
Name: v, dtype: int64
s
a      3
NaN    3
Name: v, dtype: int64

The default total is 3 and the third row is gone. With dropna=False the missing key becomes a group of its own, so nothing vanishes.

How does this look in SQL?

RememberSQL connection

SELECT status, COUNT(*) AS orders, SUM(total) AS gmv FROM orders GROUP BY status is exactly df.groupby('status', as_index=False).agg(orders=('order_id', 'count'), gmv=('total', 'sum')). Same grain rules, different syntax.

If you already think in SQL, translate the clauses one at a time: GROUP BY becomes the groupby key, each aggregate in SELECT becomes one named aggregation, and the alias after AS becomes the left-hand name.

How does the whole trace replay?

5 orders to a status summary
  1. Name the grain. 1 row per status, so the key is order_status.

    1. grain
    2. order_status
  2. Split. The 5 orders fall into three piles: cancelled, paid, refunded.

    1. 5 rows
    2. 3 piles
  3. Apply and combine with named aggregation. Paid becomes 3 orders, 1978.12.

    1. agg
    2. 3 rows
  4. Use transform when every order needs its group total. ORD-0001 is 0.3094 of paid.

    1. transform
    2. share
  5. Reconcile. The summary adds back to 83416.15 and 180 orders.

    1. gmv sum
    2. matches source

Five things to know from this lesson:

  1. State the output grain before writing groupby.
  2. Use named aggregation, name=(column, function), for flat column names.
  3. count skips blanks and size does not. Count order_id when you want rows.
  4. Use transform when every source row needs its group metric, agg when you want 1 row per group.
  5. Reconcile: keys are unique, and the group totals add back to the source total.

Common beginner questions

What is the difference between agg and transform?

agg produces one summary row per group (reduces rows). transform pastes the group result back onto every original row (preserves row count). Use agg for summaries and transform for row-level enrichment.

Why use as_index=False?

By default, groupby makes the group keys the DataFrame index. as_index=False keeps them as regular columns, which is usually easier to work with and avoids confusion.

Can I group by more than 2 columns?

Yes. Pass a list of any number of columns. Each unique combination of values across those columns becomes one group.

What comes next

Now that you can summarize data by groups, the next lesson covers combining DataFrames: merging (joining) 2 tables on a shared key, stacking frames with concat, and understanding how joins can change your row count.

Practice

Group df_orders by order_status with as_index=False. Name the count of order_id as 'orders' and the sum of order_total as 'gmv'.

Assign the summary DataFrame to result. The expected grain is exactly 1 row per order status.

Going further: size or count, and a share of the total

size and count look the same until a value is missing: size counts rows, count counts non-missing values. Then transform answers 'what share of its group is this row' without losing any rows. Predict the three numbers for group A.

Pythonsize, count and share of group
import pandas as pd

df = pd.DataFrame({
    "region": ["A", "A", "A", "B"],
    "amount": [100.0, None, 50.0, 70.0],
})

print(df.groupby("region")["amount"].size().to_dict())
print(df.groupby("region")["amount"].count().to_dict())

df["share"] = df["amount"] / df.groupby("region")["amount"].transform("sum")
print(df)
{'A': 3, 'B': 1}
{'A': 2, 'B': 1}
  region  amount     share
0      A   100.0  0.666667
1      A     NaN       NaN
2      A    50.0  0.333333
3      B    70.0  1.000000

Region A has 3 rows but only 2 amounts, so size is 3 and count is 2.

Use size for 'how many rows' and count for 'how many values are present'. transform('sum') returns a column the same length as the table, so each row can be divided by its own group's total: 100 / 150 and 50 / 150. The missing amount stays missing.

Try a new scenario

For each region, find the average amount and the number of orders that have an amount, and keep the table as 1 row per region with plain column names.

Open the answer

Named aggregation gives readable column names, and reset_index turns the region back into a normal column. The result is [{'region': 'A', 'avg_amount': 75.0, 'priced_orders': 2}, {'region': 'B', 'avg_amount': 70.0, 'priced_orders': 1}]. Note that the mean of A is 75, not 50: the missing value is skipped in both the sum and the count, so the average is over the two present amounts.

What does reset_index do after groupby?

The group keys become the index of the result. reset_index turns them back into ordinary columns, which is usually what you want before merging, saving or plotting.

Close the lesson and explain it

Without scrolling up, explain the difference between size and count, and what transform adds that agg does not.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Practice this

Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.

Rate:
Was this useful?