Interactive Session

A book far too large to read by eye. MidJourney 5

Six sites and six means is a table you can read by eye. However, many groupings are much bigger than six, and most questions want more than one summary of each group.

This morning we take the pattern you just learned and increase the load. You will ask for several summaries in one call instead of one, ask for different summaries of different columns, and rank a grouped result the way you ranked a raw table on Wednesday. In addition, we will see what happens when you group by two things at once.

The data should look familiar: the National Park Service visitor records from Wednesday’s colab. On Wednesday you ranked individual park-years.

This morning we will rank whole parks.

By the end of this session you will be able to:

pass a list to .agg() to get several summaries of one column
pass a dictionary to .agg() to summarise several columns differently
apply the top-N pattern to a grouped result
recognize a MultiIndex produced by grouping on two keys, and flatten it with .reset_index()
explain why a grouped mean should always be reported with the count it was computed from

Getting Started

Create the file. In the Explorer, hover over the EDS217 heading and click New File…, then type the name in full, extension included: Session_5B_Aggregating_Data.ipynb
Check the kernel. The Kernel Selector in the notebook’s action bar should read Python 3.11.15 (Conda: eds217). If it reads anything else, click it, choose Change Kernel, and pick the eds217 entry.
Add a title cell. Click + Markdown in the action bar, and give it this content, with today’s date:

# Day 5: Session 5B - Several Answers at Once

[Session Webpage](https://eds-217-essential-python.github.io/course-materials/interactive-sessions/5b_aggregating_data.html)

Date: 09/04/2026

Save with Ctrl + S (Cmd + S on macOS), and keep saving as you go.
Read the visitor records and rebuild two subsets from Wednesday, this time applied one after the other: keep the rows that describe a real year, and from those keep the rows that describe a National Park. Note the order: Wednesday’s national_parks was taken from the whole file, so it still had the Total rows in it, while chaining the two filters here gives a smaller table under the same name.

Code

import pandas as pd

url = 'https://eds-217-essential-python.github.io/data/national_parks.csv'
parks = pd.read_csv(url)

# Remove the rows that have park Totals in them and make a new dataframe:
by_year = parks[~(parks['year'] == 'Total')]
national_parks = by_year[by_year['unit_type'] == 'National Park'].copy()

national_parks.shape

(4682, 12)

4,682 rows. Each one is a single park in a single year, running from 1904 to 2016, and there are 61 distinct parks in the dataset.

One column, several summaries

Start with what you already know how to write:

Code

national_parks.groupby('region')['visitors'].mean()

region
AK    1.204881e+05
IM    7.931557e+05
MW    6.108661e+05
NC    5.107866e+05
NE    1.618013e+06
PW    6.627740e+05
SE    1.572087e+06
Name: visitors, dtype: float64

Seven regions, seven means. And now the question that should be a reflex by the end of today: how many rows went into each of those?

Code

national_parks.groupby('region')['visitors'].count()

region
AK     488
IM    1590
MW     533
NC      46
NE     179
PW    1393
SE     453
Name: visitors, dtype: int64

Two calls, two Series, and you are already reading across them by eye and hoping you have lined them up correctly. .agg() removes the eye-matching by allowing you to do more than one calculation at a time.

Give it a list of aggregation names and it runs all of them on the same grouping and puts the answers side by side:

Code

national_parks.groupby('region')['visitors'].agg(['count', 'mean', 'max'])

	count	mean	max
region
AK	488	1.204881e+05	592431.0
IM	1590	7.931557e+05	5969811.0
MW	533	6.108661e+05	3527837.0
NC	46	5.107866e+05	680862.0
NE	179	1.618013e+06	5440952.0
PW	1393	6.627740e+05	5028868.0
SE	453	1.572087e+06	11312786.0

The result is a DataFrame rather than a Series: one row per group, one column per thing you asked for, and the group labels as the index.

The names in the list are strings, and they are the same names you would have called as methods:

'count', 'mean', 'sum', 'min', 'max', 'median', 'std', 'nunique'.

Note

🐍 .agg(['mean']) with one name in the list gives you a DataFrame with one column. .mean() gives you a Series. Same numbers, in a different kind of object. Ask for the one you want to work with next.

The reason `count` goes first

Look again at the table of counts and means. NE has the highest mean of any region, a little over 1.6 million visitors, though SE is within three per cent of it. If you were writing a report about which region of the park system draws the crowds, NE is your headline.

Now read the count column. NE has 179 rows out of 4,682. Ask how many parks that is:

Code

national_parks.groupby('region')['unit_name'].nunique()

region
AK     9
IM    18
MW     7
NC     1
NE     2
PW    17
SE     7
Name: unit_name, dtype: int64

Two. The entire Northeast region contains two national parks, Acadia and Shenandoah, and both of them are busy ones. The mean is not wrong, but it is a statement about two parks that happen to share a region code rather than a statement about a region.

NC is worse: one park and 46 rows, and its mean prints in exactly the same format as IM’s, whose mean is computed from 1,590 rows.

A mean is a summary of a count you did not print

Every grouped mean is really two numbers, and pandas prints only one of them. The habit that fixes it is a small one: put 'count' first in every .agg() list you write, and read it before you read anything else.

✏️ Test your knowledge

Group national_parks by state and use .agg() with a list to get the count, mean and max of visitors. Find a state whose mean looks impressive, but which is based on a limited sample number.

Several columns, summarised differently

The list form runs the same summaries on one column. Often you want something else: the mean of this column, the sum of that one, the number of distinct values in a third.

For that, .agg() takes a dictionary. The keys are column names and the values are what to do with each:

Code

national_parks.groupby('region').agg({
    'visitors': 'mean',
    'unit_name': 'nunique',
    'year': 'min'
})

	visitors	unit_name	year
region
AK	1.204881e+05	9	1922
IM	7.931557e+05	18	1904
MW	6.108661e+05	7	1904
NC	5.107866e+05	1	1971
NE	1.618013e+06	2	1919
PW	6.627740e+05	17	1904
SE	1.572087e+06	7	1931

Read the dictionary as three instructions applied to the same split: average the visitors, count the distinct park names, and find the earliest year of record.

Two things changed from the list form, and both follow from what a dictionary is:

there is no ['column'] before .agg(). The keys of the dictionary say which columns, so naming a column beforehand would be saying it twice.
the result has one column per key, named after the key.

NOTE: The dictionary here is the same kind you have been writing since Day 2. You used one to rename columns in .rename(columns={...}). Same syntax, same idea: a mapping from a name to what should happen to it.

Note

🐍 A dictionary value can also be a list, which combines both forms 🤯:

Code

national_parks.groupby('region').agg({
    'visitors': ['count', 'mean'],
    'unit_name': 'nunique'
})

	visitors		unit_name
	count	mean	nunique
region
AK	488	1.204881e+05	9
IM	1590	7.931557e+05	18
MW	533	6.108661e+05	7
NC	46	5.107866e+05	1
NE	179	1.618013e+06	2
PW	1393	6.627740e+05	17
SE	453	1.572087e+06	7

The column headings stack into two rows when you do this, which gets awkward fast. It is worth being able to read one when you meet it. Reach for it sparingly.

✏️ Test your knowledge

Write one .agg() call, grouped by region, that reports the median number of visitors, the latest year of record, and the number of distinct states in each region. Then say in one sentence why the median might be the more honest of the two averages for this data.

Ranking a grouped result

On Wednesday afternoon you learned the top-N pattern:

df.sort_values('column', ascending=False).head(n)

You wrote it against raw tables. A grouped result is a table too, so the pattern works there without modification, and the combination answers a question the raw table could not.

Wednesday’s ranking found the single busiest park-year. Grouping first finds the busiest park:

Code

national_parks.groupby('unit_name')['visitors'].mean().sort_values(ascending=False).head(10)

unit_name
Great Smoky Mountains National Park    6.069152e+06
Grand Canyon National Park             2.096805e+06
Cuyahoga Valley National Park          2.093105e+06
Olympic National Park                  1.960219e+06
Rocky Mountain National Park           1.765457e+06
Yosemite National Park                 1.715356e+06
Grand Teton National Park              1.680664e+06
Acadia National Park                   1.668492e+06
Shenandoah National Park               1.556940e+06
Yellowstone National Park              1.549028e+06
Name: visitors, dtype: float64

Read the line left to right and it is four instructions, two from this morning and two from Wednesday: group by park, average the visitors, put the averages in order, and keep the top ten.

Great Smoky Mountains averages nearly three times the Grand Canyon over its record. It is free to enter and it straddles a major road, and it has been the busiest national park in most years since its record began in 1931. The busiest unit of any kind is usually the Blue Ridge Parkway, which is a road.

The same pattern run the other way gives the quietest parks:

Code

national_parks.groupby('unit_name')['visitors'].mean().sort_values().head(5)

unit_name
Kobuk Valley National Park            4475.857143
Gates of the Arctic National Park     6451.800000
National Park of American Samoa       8745.692308
Lake Clark National Park             10396.971429
Isle Royale National Park            13917.064935
Name: visitors, dtype: float64

When the grouped result is a DataFrame rather than a Series, .sort_values() needs to be told which column to sort on, exactly as it does on a raw table:

Code

park_stats = national_parks.groupby('unit_name')['visitors'].agg(['count', 'mean', 'max'])

park_stats.sort_values('mean', ascending=False).head(10)

	count	mean	max
unit_name
Great Smoky Mountains National Park	86	6.069152e+06	11312786.0
Grand Canyon National Park	98	2.096805e+06	5969811.0
Cuyahoga Valley National Park	39	2.093105e+06	3527837.0
Olympic National Park	82	1.960219e+06	3846709.0
Rocky Mountain National Park	102	1.765457e+06	4517585.0
Yosemite National Park	111	1.715356e+06	5028868.0
Grand Teton National Park	88	1.680664e+06	3352500.0
Acadia National Park	98	1.668492e+06	5440952.0
Shenandoah National Park	81	1.556940e+06	2789100.0
Yellowstone National Park	113	1.549028e+06	4257177.0

Now the count sits next to the mean while you read the ranking, so you can see how much record each average is built on without running a second call.

Averaging across unequal records

count in that table is the number of years each park has been reporting, and it ranges from 13 to 113. Cuyahoga Valley’s average is computed from 39 years, all of them recent. Yellowstone’s is computed from 113 years, reaching back to 1904, so about two decades of its record come from before mass car ownership. Cuyahoga Valley’s record does not reach back that far at all.

Ranking parks by their all-time average is therefore partly a ranking of when each park existed. The imbalance is not a flaw in your code. It is a question you have to answer before your ranking means anything, and the usual fix is to filter to a common set of years first, and then group.

✏️ Test your knowledge

Filter national_parks to the single year '2016', then group by state and use .agg() to report the count and mean of visitors. Rank the result by mean. Compare your top three states with the all-time ranking by state you built in the earlier exercise, the one that asked for count, mean and max. Did anything move? Say in one sentence which of the two rankings you would put in front of a park superintendent, and why.

Grouping by two keys

Everything so far has split the table by one column. .groupby() will take a list of columns and split by all of them together, making one group per combination that actually occurs:

Code

national_parks.groupby(['region', 'state'])['visitors'].mean()

region  state
AK      AK       1.204881e+05
IM      AZ       1.009639e+06
        CO       6.353273e+05
        MT       9.908449e+05
        NM       4.702549e+05
        TX       1.935455e+05
        UT       5.792425e+05
        WY       1.606659e+06
MW      AR       8.443021e+05
        MI       1.391706e+04
        MN       2.161420e+05
        ND       4.142048e+05
        OH       2.093105e+06
        SD       5.786315e+05
NC      VA       5.107866e+05
NE      ME       1.668492e+06
        VA       1.556940e+06
PW      AS       8.745692e+03
        CA       6.076323e+05
        HI       8.838688e+05
        NV       4.543101e+04
        OR       3.068096e+05
        WA       1.115853e+06
SE      FL       3.961249e+05
        KY       1.046299e+06
        NC       6.069152e+06
        SC       8.217781e+04
        VI       4.330022e+05
Name: visitors, dtype: float64

Twenty-eight groups, because most states have parks in only one region. Look at the left-hand side of the output: there are two levels of labels, and the outer one is not repeated on every row. The two-level index is a MultiIndex, an index made of more than one column.

A MultiIndex is genuinely useful, and it is also a common reason that pandas code stops working, because everything you know about selecting from an index now needs a tuple:

Code

two_key = national_parks.groupby(['region', 'state'])['visitors'].mean()

two_key['IM', 'WY']

np.float64(1606659.343283582)

You are not expected to become fluent with a MultiIndex this week! You should be able to recognize one and get out of it, and the way out is a single method:

Code

flat = two_key.reset_index()
flat.head()

	region	state	visitors
0	AK	AK	1.204881e+05
1	IM	AZ	1.009639e+06
2	IM	CO	6.353273e+05
3	IM	MT	9.908449e+05
4	IM	NM	4.702549e+05

.reset_index() takes the index levels and turns them back into ordinary columns. The result is a plain DataFrame with three columns, and everything you have learned this week works on it again:

Code

flat.sort_values('visitors', ascending=False).head(5)

	region	state	visitors
25	SE	NC	6.069152e+06
12	MW	OH	2.093105e+06
15	NE	ME	1.668492e+06
7	IM	WY	1.606659e+06
16	NE	VA	1.556940e+06

Code

flat[flat['region'] == 'PW'].sort_values('visitors', ascending=False)

	region	state	visitors
22	PW	WA	1.115853e+06
19	PW	HI	8.838688e+05
18	PW	CA	6.076323e+05
21	PW	OR	3.068096e+05
20	PW	NV	4.543101e+04
17	PW	AS	8.745692e+03

🧭 When to reach for .reset_index()

Whenever a grouped result is going to be used rather than read. If you are filtering it, merging it, plotting it, or writing it to a file, flatten it first. If you are looking at it in a notebook cell and moving on, leave it alone.

.reset_index() also works on a single-key grouped result, and turns the Series into a two-column DataFrame, which is often exactly what you want before plotting.

✏️ Test your knowledge

Run national_parks.groupby(['region', 'unit_name'])['visitors'].agg(['count', 'mean']) and look at what comes back. Then call .reset_index() on it and use the top-N pattern to find the five busiest parks. Say in one sentence what .reset_index() changed.

Putting it together

Let’s do one final question using every piece of this morning so far:

Which national parks in the Pacific West region have drawn the most visitors, and how much record is each average built on?

Code

pacific_west = national_parks[national_parks['region'] == 'PW']

pw_stats = pacific_west.groupby('unit_name')['visitors'].agg(['count', 'mean', 'max'])

pw_stats.sort_values('mean', ascending=False).head(10)

	count	mean	max
unit_name
Olympic National Park	82	1.960219e+06	3846709.0
Yosemite National Park	111	1.715356e+06	5028868.0
Haleakala National Park	57	9.164551e+05	1963187.0
Hawai'i Volcanoes National Park	96	8.645207e+05	2247974.0
Mount Rainier National Park	113	8.361841e+05	1925100.0
Joshua Tree National Park	76	7.458512e+05	2505286.0
Sequoia National Park	111	5.662813e+05	1254688.0
Death Valley National Park	84	5.477119e+05	1296283.0
Kings Canyon National Park	113	4.688335e+05	1216800.0
Redwood National Park	46	4.207219e+05	677135.0

Filter, group, aggregate, rank. Four steps, and only two of them are new this morning! The answer is a table that captures all the relevant information necessary to answer the question.

Key points

.agg(['count', 'mean', 'max']) runs several summaries on one column and returns a DataFrame.
.agg({'col': 'fn', 'other': 'fn'}) runs different summaries on different columns. There is no ['column'] before it, because the dictionary keys name the columns.
Put 'count' first, every time. A grouped mean without a count beside it is half a number.
The top-N pattern works on a grouped result: .groupby(...)...sort_values(...).head(n).
On a grouped DataFrame, .sort_values() needs the name of the column to sort by.
Grouping by a list of keys produces a MultiIndex.
.reset_index() turns index levels back into columns. Use it whenever the result is going to be used rather than just read.

Getting Started

One column, several summaries

The reason count goes first

Several columns, summarised differently

Ranking a grouped result

Grouping by two keys

Putting it together

Key points

Resources

The reason `count` goes first