Skip to main content

Several summaries, several keys

One number per group is rarely enough: a mean needs its count beside it. Ask a group-by for several summaries with agg and named aggregation, group by two columns at once, flatten the two-level result with reset_index, and meet value_counts, the class balance to check before training.
P02.6.2L1 Core25 min
In review

The maths and code on this page are checked by automated tests on every change, but an independent reader has not signed it off yet. Spot a mistake or a confusing step? Tell us.

Before this lesson: P02.6.1 Split, apply, combine

What you will be able to do
  • Compute several summaries per group with agg, using a list of functions or named aggregation.
  • Group by two key columns and explain why the result holds only the pairs that occur.
  • Turn index levels back into columns with reset_index or as_index=False.
  • Use value_counts to check the class balance of a column before training a classifier.

The question

By sky, the mean noon temperatures are 28 for clear, 23 for cloudy and 20 for rain. But the rain mean rests on 2 readings and the clear mean on 6. A mean shown without its count invites you to trust it more than it deserves. How do you get both numbers from one group-by? And how do you ask about station and sky together?

From the last lesson

1. What does readings.groupby('station')['temp'].mean() return?

Show answer

A Series with the index coast, hill, valley (named station) and the values 26, 20 and 29.

2. In what order does readings.groupby('day')['temp'].mean() list the days, and why?

Show answer

mon, thu, tue, wed: pandas sorts the keys, and text sorts letter by letter. sort=False keeps the order of first appearance.

Intuition

The split does not care how many summaries follow it. Once the rows sit in their piles, each pile can be asked several questions: how many rows, what mean, what maximum. Each answer becomes one column of the result, and each pile still gives one row.

Three steps. Split by sky: three blue piles, clear 27, 23, 27, 30, 29, 32; cloudy 26, 28, 18, 20; rain 21, 19, with orange sky labels. Apply two: grey arrows take each pile to two vermillion cells headed size and mean: 6 and 28, 4 and 23, 2 and 20. Combine: green arrows carry them into a result table with an orange sky index and two vermillion columns n and mean_temp, marked shape (3, 2). Below, the code readings.groupby('sky').agg(n=('temp', 'size'), mean_temp=('temp', 'mean')).
P02.6.2-F1

One split, two summaries. Every pile gives one row with two numbers, so the result has one row per sky and one column per summary.

A key can also be two columns. Then a group is every row that shares both values, such as hill readings under a cloudy sky. Think of the possible pairs as a grid of 3 stations by 3 skies: the group-by fills only the cells that have rows in them.

Build it up

Step 1. A list of summaries. agg takes a list of function names and returns one column for each:

readings.groupby('sky')['temp'].agg(['size', 'mean', 'max'])

The columns are called size, mean and max, and the rows are clear, cloudy and rain.

Step 2. Named aggregation. To choose the column names yourself, give each output column as name=(column, function):

readings.groupby('sky').agg(n=('temp', 'size'), mean_temp=('temp', 'mean'))

Each output names its own input column, so one call can summarise different columns: days=('day', 'nunique') adds how many different days each sky was seen on.

Step 3. Two keys. Pass a list of key columns. The result is indexed by pairs, a two-level index, and holds one row per pair that occurs.

Left, a 3 by 3 grid titled 9 possible pairs, 7 occur, with orange station labels coast, hill, valley down the side and sky labels clear, cloudy, rain across the top. Blue cells give the number of rows: coast clear 3 rows, coast rain 1 row, hill clear 1 row, hill cloudy 2 rows, hill rain 1 row, valley clear 2 rows, valley cloudy 2 rows. Coast cloudy and valley rain are dashed reddish-purple cells reading none, with the note no group for an empty pair. A green arrow labelled mean leads to a result with columns station, sky and temp and 7 rows: coast clear 27.67, rain 21; hill clear 23, cloudy 19, rain 19; valley clear 31, cloudy 27, with each station printed only once. Under it: index with 2 levels, 7 rows.
P02.6.2-F2

Grouping by station and sky. Of the nine possible pairs, coast in cloudy weather and valley in rain never happen, so they get no group at all, not a group with the value 0.

A two-level result is read with a pair of labels: two.loc[('hill', 'cloudy')] is 19, and two.loc['hill'] gives all of hill’s skies.

Step 4. Back to plain columns. An index with levels is compact for reading but awkward for the next step, such as a merge (P02.5.4) or a seaborn plot (P02.8.4), which want ordinary columns. reset_index() moves the levels into columns and numbers the rows from 0; as_index=False in the groupby call gives the same table directly.

Left, titled before: keys in the index, a result whose grey index cells hold station, printed once per run, and sky, beside a vermillion temp column of 7 values: 27.67, 21, 23, 19, 19, 31, 27. A green arrow labelled .reset_index() points right to a table titled after: keys are columns, with row numbers 0 to 6 and three columns: orange station with coast, coast, hill, hill, hill, valley, valley; orange sky; and the same vermillion temp values. Underneath: shape (7, 3), ready for merge or a plot, and the line groupby(['station', 'sky'], as_index=False) gives the same table in one step.
P02.6.2-F3

reset_index turns the two index levels into ordinary columns and gives the rows fresh numbers.

Step 5. The everyday special case. Counting the rows of each value of one column is so common that it has its own method. readings['sky'].value_counts() gives clear 6, cloudy 4, rain 2, the most frequent first, and normalize=True turns the counts into shares.

Two bar charts. Left, titled readings['sky'].value_counts(): vermillion bars for clear 6 (0.50), cloudy 4 (0.33) and rain 2 (0.17), longest first, with the note most frequent first, in brackets normalize=True. Right, titled iris species: three equal bars, setosa, versicolor and virginica, each 50 (0.33), with the note balanced: 50 of each.
P02.6.2-F4

value_counts is groupby(column).size(), sorted with the largest group first. On a label column it is the class balance: iris has 50 flowers of each species.

Check the class balance before you train a classifier. If 990 of 1,000 rows are one class, a model that always answers that class is 99 percent accurate and useless, the trap of lesson ML01.7.1.

The formal statement

NoteDefinition: several summaries and several keys

Several summaries. agg applies each function it is given to every group. With a list, the output columns take the functions’ names. With named aggregation, name=(column, function), each output column has the given name and summarises the given input column.

Several keys. With a list of key columns, the group of a tuple of values \({(g, h)}\) is the set of rows whose first key is \(g\) and whose second key is \(h\). The result has one row for every tuple that occurs, indexed by a MultiIndex, an index with one level per key.

Counting values. s.value_counts() equals s.groupby(s).size() sorted from the largest count to the smallest. With normalize=True each count \(n_g\) is divided by the number of rows \(n\).

How many groups can there be? With \(a\) distinct values in the first key and \(b\) in the second, there are \({a \times b}\) possible pairs, so at most \({a \times b}\) groups. The readings have \({3 \times 3 = 9}\) possible pairs and 7 groups. There can also be no more groups than rows, since every group holds at least one row: 7 is at most 12.

The shares add up to one. The groups split the rows (lesson P02.6.1), so their sizes add up to \(n\). Dividing every size by \(n\) therefore gives shares that add up to 1:

\[ \begin{aligned} \sum_{g} \frac{n_g}{n} &= \frac{1}{n} \sum_{g} n_g \\ &= \frac{n}{n} = 1 . \end{aligned} \]

By sky, \({6/12 + 4/12 + 2/12 = 1}\). The same holds for two keys: the seven pair sizes 3, 1, 1, 2, 1, 2, 2 add up to 12.

Worked example

Size and mean by sky, as a hand tally. Keep a running pair (size, sum) for each sky. Walk down the 12 readings; each row adds 1 to the size and its temperature to the sum of its own sky. At the end, divide.

A trace table with row numbers 0 to 11, an orange sky column, a blue temp column and three vermillion tally columns headed clear: n, sum; cloudy: n, sum; rain: n, sum. In each row the tally of that row's sky is highlighted yellow. Row 0, rain 21: rain 1, 21. Row 1, rain 19: rain 2, 40. Row 2, cloudy 26: cloudy 1, 26. Row 3, clear 27: clear 1, 27. Row 4, clear 23: clear 2, 50. Row 5, cloudy 28: cloudy 2, 54. Row 6, clear 27: clear 3, 77. Row 7, cloudy 18: cloudy 3, 72. Row 8, clear 30: clear 4, 107. Row 9, clear 29: clear 5, 136. Row 10, cloudy 20: cloudy 4, 92. Row 11, clear 32: clear 6, 168. The bottom line reads mean equals sum over n: 168 over 6 equals 28, 92 over 4 equals 23, 40 over 2 equals 20.
P02.6.2-F5

The hand tally. Each row changes one tally only, the one of its own sky (yellow). The last row of tallies holds every group’s size and sum.

Rows 0 and 1 are rain: the rain tally becomes (1, 21), then (2, 40).

Rows 2 to 11 alternate between clear and cloudy. Clear collects 27, 23, 27, 30, 29 and 32, so its tally grows (1, 27), (2, 50), (3, 77), (4, 107), (5, 136), (6, 168). Cloudy collects 26, 28, 18 and 20: (1, 26), (2, 54), (3, 72), (4, 92).

Divide. Clear \({168/6 = 28}\), cloudy \({92/4 = 23}\), rain \({40/2 = 20}\). The named aggregation of the anchor figure gives the same table: n is 6, 4, 2 and mean_temp is 28, 23, 20.

Check. The sizes add up to \({6 + 4 + 2 = 12}\) rows and the sums to \({168 + 92 + 40 = 300}\), the total of all 12 readings.

Code

The table of lesson P02.6.1, then the hand tally of the worked example:

import numpy as np
import pandas as pd

temps = np.array([[21, 19, 26], [27, 23, 28], [27, 18, 30], [29, 20, 32]])
days, stations = ['mon', 'tue', 'wed', 'thu'], ['coast', 'hill', 'valley']
readings = pd.DataFrame({
    'day': [d for d in days for s in stations],
    'station': stations * 4,
    'temp': temps.ravel(),
    'sky': ['rain', 'rain', 'cloudy', 'clear', 'clear', 'cloudy',
            'clear', 'cloudy', 'clear', 'clear', 'cloudy', 'clear'],
})

tally = {}
for sky, temp in zip(readings['sky'], readings['temp']):
    n, total = tally.get(sky, (0, 0))
    tally[sky] = (n + 1, total + temp)
print(tally)
print({sky: total / n for sky, (n, total) in tally.items()})
{'rain': (2, 40), 'cloudy': (4, 92), 'clear': (6, 168)}
{'rain': 20.0, 'cloudy': 23.0, 'clear': 28.0}

Then pandas, with a list of functions and with named aggregation:

listed = readings.groupby('sky')['temp'].agg(['size', 'mean', 'max'])
named = readings.groupby('sky').agg(n=('temp', 'size'), mean_temp=('temp', 'mean'),
                                    days=('day', 'nunique'))
print(listed)
print(named)
        size  mean  max
sky                    
clear      6  28.0   32
cloudy     4  23.0   28
rain       2  20.0   21
        n  mean_temp  days
sky                       
clear   6       28.0     3
cloudy  4       23.0     4
rain    2       20.0     1

Two keys, read by pairs, and flattened two ways:

two = readings.groupby(['station', 'sky'])['temp'].mean()
print(len(two), two.index.nlevels, two.loc[('hill', 'cloudy')], ('valley', 'rain') in two.index)
flat = two.reset_index()
same = readings.groupby(['station', 'sky'], as_index=False)['temp'].mean()
print(flat.shape, flat.columns.tolist(), flat.equals(same))
7 2 19.0 False
(7, 3) ['station', 'sky', 'temp'] True

Finally value_counts, on the sky column and on the species of iris:

from sklearn.datasets import load_iris

counts = readings['sky'].value_counts()
shares = readings['sky'].value_counts(normalize=True)
iris = load_iris(as_frame=True)
species = pd.Series(iris.target_names[iris.target], name='species')
print(counts.to_dict(), shares.round(2).to_dict())
print(species.value_counts().to_dict())
{'clear': 6, 'cloudy': 4, 'rain': 2} {'clear': 0.5, 'cloudy': 0.33, 'rain': 0.17}
{'setosa': 50, 'versicolor': 50, 'virginica': 50}

Explore

The split-apply-combine machine lives in lesson P02.6.1. Choose the key sky and step through with the summary count, then with mean: the counts 6, 4 and 2 are the sizes that the means rest on.

Pitfalls

Two halves. Left, marked with a reddish-purple cross and headed every column: the code readings.groupby('sky').mean(), three column boxes day, station and temp with day and station in reddish purple and the note text: no mean, then a box saying raises a TypeError and the note nothing is computed. Right, marked with a green tick and headed pick the column first: the code readings.groupby('sky')['temp'].mean() and a table of sky and temp: clear 28, cloudy 23, rain 20, with the note one mean per sky.
P02.6.2-F6

Wrong beside right. Without a column, the mean is applied to every column, including the text columns day and station, and pandas stops. Select the column to summarise first.

“groupby(‘sky’).mean() averages the temperatures.”

It tries to average every column that is not a key. day and station hold text, which has no mean, so pandas 3 raises a TypeError. Select the column first, groupby('sky')['temp'], or name each output with named aggregation.

“Grouping by station and sky gives all nine pairs.”

It gives the seven pairs that occur. Valley in rain has no row, so it has no group, and ('valley', 'rain') in two.index is False. A missing pair is not a zero; lesson P02.6.4 shows how the grid layout makes the gaps visible.

“value_counts lists the values in alphabetical order.”

It lists them by count, largest first, which happens to be alphabetical for the sky column. Use .sort_index() when you want the labels in order.

Check yourself

Recall

1. What do the columns of readings.groupby('sky')['temp'].agg(['size', 'mean']) hold?

Show answer

size holds the number of rows of each sky (6, 4, 2) and mean their mean temperature (28, 23, 20).

2. What does mean_temp=('temp', 'mean') mean inside agg?

Show answer

Named aggregation: make an output column called mean_temp holding the mean of the input column temp for each group.

3. Why does grouping the readings by station and sky give 7 rows and not 9?

Show answer

Only 7 of the 9 possible pairs occur; coast with cloudy and valley with rain have no rows, so they have no group.

4. What do reset_index() and as_index=False do to a group-by result?

Show answer

They turn the key levels of the index into ordinary columns and number the rows from 0.

5. What does readings['sky'].value_counts(normalize=True) give?

Show answer

The share of rows for each sky, largest first: clear 0.5, cloudy about 0.33, rain about 0.17. The shares add up to 1.

Exercises

1. ★ Find the lowest and the highest temperature under each sky with one agg call.

Show solution

readings.groupby('sky')['temp'].agg(['min', 'max']) gives clear 23 and 32, cloudy 18 and 28, rain 19 and 21. By hand, the clear pile is 27, 23, 27, 30, 29, 32, the cloudy pile 26, 28, 18, 20 and the rain pile 21, 19.

2. ★★ Build a table by station with named aggregation: the number of readings n, the mean mean_temp and the highest hottest. Then use two keys to list the (station, sky) pairs with at least two readings.

Show solution
readings.groupby('station').agg(n=('temp', 'size'), mean_temp=('temp', 'mean'),
                                hottest=('temp', 'max'))

gives coast 4, 26, 29; hill 4, 20, 23; valley 4, 29, 32. For the pairs, sizes = readings.groupby(['station', 'sky']).size() and sizes[sizes >= 2] keep four pairs: (coast, clear) with 3, (hill, cloudy) with 2, (valley, clear) with 2 and (valley, cloudy) with 2.

3. ★★★ Count the (station, sky) pairs by hand with a dictionary whose keys are tuples, check that the counts add up to 12, and explain why a table with \(n\) rows and two keys with \(a\) and \(b\) distinct values has at most the smaller of \({a \times b}\) and \(n\) groups.

Show solution
pairs = {}
for station, sky in zip(readings['station'], readings['sky']):
    pairs[(station, sky)] = pairs.get((station, sky), 0) + 1

pairs holds 7 keys: (coast, rain) 1, (hill, rain) 1, (valley, cloudy) 2, (coast, clear) 3, (hill, clear) 1, (hill, cloudy) 2, (valley, clear) 2, in the order they are first met. The counts add up to 12 because every row adds 1 to exactly one count. A group is one pair of values, and there are only \({a \times b}\) pairs, so there are at most \({a \times b}\) groups. Every group holds at least one row and no row is in two groups, so there are also at most \(n\) groups. Both limits hold at once, so the number of groups is at most the smaller one: here the smaller of 9 and 12.

Summary card

Ask each group several questions; group by several columns at once.

The anchor figure again: the readings split by sky into piles of 6, 4 and 2, each pile giving its size and its mean, combined into a table with columns n and mean_temp.
P02.6.2-F1

One row per group, one column per summary.

  • Several summaries: .agg(['size', 'mean']), or named: .agg(n=('temp', 'size')).
  • Several keys: groupby(['station', 'sky']) gives one row per pair that occurs, under a MultiIndex.
  • Flatten: .reset_index(), or as_index=False.
  • Count values: s.value_counts(), largest first; with normalize=True the shares add up to 1.

Sources and next steps

Sources. Each was opened and checked against this lesson on 15 November 2026.

  • pandas developers, Group by: split-apply-combine, pandas 3.1.1 User Guide. Aggregation computes a summary statistic for each group; named aggregation takes keywords that are the output column names and values that are tuples of (column, aggregation), with pd.NamedAgg as a named tuple; as_index=False puts the keys in columns instead of the index.
  • pandas developers, Series.value_counts, pandas 3.1.1 API reference: counts of unique values in descending order, the most frequent first; normalize=True gives relative frequencies.
  • pandas developers, DataFrame.groupby, pandas 3.1.1 API reference: as_index=True returns the group labels as the index, and as_index=False gives “SQL-style” grouped output.
  • scikit-learn developers, load_iris, scikit-learn 1.9.1: 150 samples, 3 classes of 50.

Next. P02.6.3 Transform: a group summary on every row sends each group’s number back to every one of its rows.

This lesson unlocks

P02.6.3 Transform: a group summary on every row

Your progress is saved in this browser only. No account, no tracking.
Back to top