Interactive Session

A panda in a room where everything has been sorted and framed. MidJourney 5

Yesterday afternoon, at the very end of the colab, you were asked to compare the mean dissolved oxygen at two of the six sites. It took two filters and two .mean() calls, and the instructions ended with a promise: tomorrow you will learn to do all six at once.

This is that. One line, six answers, and the line is the same length as the one that gave you two.

Almost every real question about data has the same shape. Not “what is the average”, but “what is the average for each”: for each site, for each species, for each year, for each ocean. Today you learn the sentence that answers all of those, and you learn it in the two forms you will see it written.

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

describe what .groupby() does in terms of split, apply, combine
write the split-apply-combine sentence in its two-step form and its one-line form, and say why they are the same sentence
choose the aggregation that answers the question you actually asked
read the result of a grouped calculation and say what its index means
explain why a grouped answer is more honest than a whole-column answer

Getting Started

Create a new notebook from the Command Palette (Create: New Jupyter Notebook), and confirm its kernel reads eds217_2026.
Save your notebook (Ctrl + S, or Cmd + S on macOS) as: Session_5A_Grouping_Data.ipynb
Add a title cell (Markdown), updating the date to today:

# Day 5: Session 5A - The Split-Apply-Combine Sentence

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

Date: 09/04/2026

Rebuild yesterday’s clean stream-chemistry table. Every line below is one you wrote in the colab, collected here so you can start from a table you trust:

Code

import pandas as pd

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

survey = survey.drop_duplicates()

survey['site'] = survey['site'].str.strip()
survey['site'] = survey['site'].str.lower()
survey['site'] = survey['site'].str.replace('-', '_')

survey['pH'] = survey['pH'].str.replace(',', '.')
survey['pH'] = survey['pH'].astype(float)

survey = survey.dropna(subset=['temperature_c', 'dissolved_oxygen_mg_L', 'conductivity_uS_cm'])
survey['n_replicates'] = survey['n_replicates'].fillna(1)
survey['n_replicates'] = survey['n_replicates'].astype(int)

survey = survey[survey['temperature_c'] > -100].copy()
survey = survey.rename(columns={'collection date': 'collection_date'})

survey.shape

(255, 7)

255 rows out of the 320 you started with, which is the number you finished on yesterday. If you get something else, check the order of your steps against the block above.

The question you could not answer yesterday

Here is the whole-table average dissolved oxygen:

Code

survey['dissolved_oxygen_mg_L'].mean()

np.float64(8.062039215686275)

That number is true and almost useless. It describes no site. It is the average of six streams that are not the same stream, and the moment anybody asks “is that good?” you have to admit you cannot tell them.

Yesterday you got at two of the six the only way you could:

Code

site_d = survey[survey['site'] == 'site_d']
site_f = survey[survey['site'] == 'site_f']

print(site_d['dissolved_oxygen_mg_L'].mean())
print(site_f['dissolved_oxygen_mg_L'].mean())

10.053333333333333
6.180232558139535

Two filters, two means, two answers. Six sites would be six filters and six means, twelve lines to get one small table. And if a seventh site were added to the file next season, your twelve lines would silently keep reporting six.

Split, apply, combine

The pattern in those twelve lines has a name, and pandas has one verb for the whole of it.

Split the table into one group per site. Apply the same calculation to each group. Combine the answers into a single result, labelled by group.

That is .groupby(). In its first form you do it in two steps, because doing it in two steps makes both halves visible:

Code

grouped = survey.groupby('site')
grouped

<pandas.core.groupby.generic.DataFrameGroupBy object at 0x1afc61f50>

Look carefully at what that printed. It is not a table. .groupby() on its own does the split and then stops, holding six piles of rows and waiting to be told what to do with them. Nothing has been calculated yet.

The second step picks a column and names the calculation:

Code

grouped['dissolved_oxygen_mg_L'].mean()

site
site_a     9.485909
site_b     8.601220
site_c     6.873111
site_d    10.053333
site_e     7.410930
site_f     6.180233
Name: dissolved_oxygen_mg_L, dtype: float64

Six sites, six means, one line of arithmetic. site_d and site_f are the two numbers you computed yesterday, and the other four came along for free.

Note

🐍 The object .groupby() returns is a real thing you can keep in a variable and use more than once. Ask it for a different column and it splits nothing again, because the split has already happened:

Code

grouped['temperature_c'].mean()

site
site_a    16.600000
site_b    18.102439
site_c    21.911111
site_d    15.179487
site_e    19.730233
site_f    23.297674
Name: temperature_c, dtype: float64

The same sentence, on one line

You will rarely need the grouped object for its own sake, so almost all of the pandas code you read will run the two steps together:

Code

survey.groupby('site')['dissolved_oxygen_mg_L'].mean()

site
site_a     9.485909
site_b     8.601220
site_c     6.873111
site_d    10.053333
site_e     7.410930
site_f     6.180233
Name: dissolved_oxygen_mg_L, dtype: float64

These are the same sentence. The two-step version stores the middle of it in a variable; the one-line version does not. Same split, same apply, same combine, same answer.

Read the one-line form left to right as three instructions:

df.groupby('key')['column'].aggregation()
#          split      pick        apply

groupby('key') says which piles to make. The key is the column whose values name the groups.
['column'] says which column to do arithmetic on.
.mean() says what arithmetic.

Learn it as one unit, the way you learned the top-N sentence on Wednesday. You will type it more often this quarter than you will type your own name.

✏️ Test your knowledge

Write the one-line form to find the mean pH at each site. Then write the same question in the two-step form, using the grouped object that already exists. Confirm you get identical output.

Reading the result

The result is a Series, which is the one-column object you met on Day 2. It has an index, and the index is made of the group labels:

Code

mean_do = survey.groupby('site')['dissolved_oxygen_mg_L'].mean()

print(type(mean_do))
print(mean_do.index)

<class 'pandas.core.series.Series'>
Index(['site_a', 'site_b', 'site_c', 'site_d', 'site_e', 'site_f'], dtype='object', name='site')

That matters because the index is now something you can look things up by. The site labels have stopped being data inside a column and have become the labels of rows:

Code

mean_do['site_c']

np.float64(6.873111111111111)

It also means everything you already know about a Series still works here. .idxmax(), from Wednesday afternoon, gives you the label of the largest value, and the label is now a site name:

Code

mean_do.idxmax()

'site_d'

Code

print('Best site:', mean_do.idxmax())
print('Worst site:', mean_do.idxmin())

Best site: site_d
Worst site: site_f

✏️ Test your knowledge

Group survey by site and take the mean of temperature_c, then use .idxmax() on the result to name the warmest site. Compare it with the worst site for dissolved oxygen above. Are they the same site? Write one sentence saying whether you would expect them to be.

Choosing the aggregation

.mean() is not special. Any of the summaries you have been running on whole columns since Tuesday will run on each group instead:

Code

survey.groupby('site')['dissolved_oxygen_mg_L'].max()

site
site_a    11.12
site_b     9.86
site_c     7.99
site_d    11.43
site_e     8.79
site_f     7.77
Name: dissolved_oxygen_mg_L, dtype: float64

Code

survey.groupby('site')['dissolved_oxygen_mg_L'].min()

site
site_a    8.21
site_b    7.33
site_c    5.65
site_d    8.60
site_e    5.16
site_f    4.14
Name: dissolved_oxygen_mg_L, dtype: float64

Code

survey.groupby('site')['n_replicates'].sum()

site
site_a    130
site_b    115
site_c    131
site_d    116
site_e    127
site_f    127
Name: n_replicates, dtype: int64

.count() is the one worth pausing on, because it answers a different kind of question. It does not summarise the measurement, it tells you how many rows are in each pile:

Code

survey.groupby('site')['dissolved_oxygen_mg_L'].count()

site
site_a    44
site_b    41
site_c    45
site_d    39
site_e    43
site_f    43
Name: dissolved_oxygen_mg_L, dtype: int64

Forty-five samples at site_c and thirty-nine at site_d, which is close enough to even that you can trust the comparison of their means. When it is not even, .count() is the line that tells you before you embarrass yourself. Get in the habit of asking for it.

Aggregation	Answers
`.mean()`	what is typical in each group
`.median()`	what is typical, when a few extreme values would drag the mean
`.sum()`	how much in total, per group
`.count()`	how many rows went into each answer
`.min()`, `.max()`	the extremes within each group
`.std()`	how spread out each group is

.count() is not an afterthought

A group mean computed from four rows and a group mean computed from four hundred are printed in exactly the same font. Nothing in the output warns you. Whenever you report a grouped mean, run .count() on the same grouping and look at it, even if it never reaches your report.

✏️ Test your knowledge

Answer both of these with one sentence each:

What is the highest pH recorded at each site?
How many bottles were filled in total at each site? (n_replicates counts bottles.)

Then say, in a markdown cell, why question 2 needs .sum() and not .count().

What can be a key

Any column whose values repeat can be a grouping key. The values do not have to be text, and they do not have to be tidy, but they do have to mean something.

site works because six labels describe two hundred and fifty-five rows. pH would not work, because almost every value is unique and you would get two hundred groups of one.

Yesterday you built a column of your own that groups beautifully:

Code

def classify_ph(value):
    """Label a pH value as acidic, neutral, or alkaline."""
    if value < 6.5:
        return 'acidic'
    elif value > 7.5:
        return 'alkaline'
    else:
        return 'neutral'


survey['ph_class'] = survey['pH'].apply(classify_ph)
survey['ph_class'].value_counts()

ph_class
neutral     171
acidic       42
alkaline     42
Name: count, dtype: int64

Code

survey.groupby('ph_class')['dissolved_oxygen_mg_L'].mean()

ph_class
acidic      6.350000
alkaline    9.948571
neutral     8.019181
Name: dissolved_oxygen_mg_L, dtype: float64

That is the derived-column sentence and the split-apply-combine sentence working together, and it is worth naming what just happened. ph_class was not in the file. You invented the categories yesterday, and today they are a grouping key. Most of the interesting groupings in your career will be ones you made rather than ones you were handed.

Note

🐍 .value_counts(), which you have used since Day 2, is a grouped count in disguise. These two lines produce the same numbers:

Code

print(survey['site'].value_counts())
print(survey.groupby('site')['site'].count())

site
site_c    45
site_a    44
site_f    43
site_e    43
site_b    41
site_d    39
Name: count, dtype: int64
site
site_a    44
site_b    41
site_c    45
site_d    39
site_e    43
site_f    43
Name: site, dtype: int64

.value_counts() is the convenient special case. .groupby() is the general tool that also lets you ask for a mean.

✏️ Test your knowledge

Group by ph_class and take the mean of temperature_c. Then look back at the mean temperature per site from earlier in this session. In two or three sentences, say what you think is going on: are the acidic samples acidic because of something about temperature, or are both of these telling you the same thing about which sites they came from? You cannot settle it with what you have; say what you would need.

Putting it together

The honest version of “what is the dissolved oxygen in this stream system”, in three lines:

Code

print(survey.groupby('site')['dissolved_oxygen_mg_L'].count())
print(survey.groupby('site')['dissolved_oxygen_mg_L'].mean())
print(survey.groupby('site')['temperature_c'].mean())

site
site_a    44
site_b    41
site_c    45
site_d    39
site_e    43
site_f    43
Name: dissolved_oxygen_mg_L, dtype: int64
site
site_a     9.485909
site_b     8.601220
site_c     6.873111
site_d    10.053333
site_e     7.410930
site_f     6.180233
Name: dissolved_oxygen_mg_L, dtype: float64
site
site_a    16.600000
site_b    18.102439
site_c    21.911111
site_d    15.179487
site_e    19.730233
site_f    23.297674
Name: temperature_c, dtype: float64

Read those three outputs together and there is a story in them. The sites with the warmest water have the least oxygen in it, in order, without an exception. Warm water holds less dissolved oxygen than cold water, which is a fact about gases, and here it is, falling out of a file four undergraduates transcribed off paper.

You could not see that yesterday. Not because you lacked the data, but because you lacked the sentence.

Key points

Split, apply, combine: .groupby() splits the table into piles, an aggregation runs on each pile, and the answers come back as one labelled result.
The split-apply-combine sentence is df.groupby('key')['column'].aggregation().
The two-step form and the one-line form are the same sentence. The two-step version keeps the split in a variable so you can reuse it.
.groupby() on its own calculates nothing. It waits.
The result is a Series indexed by the group labels, so .idxmax(), .idxmin() and label lookup all still work.
Any aggregation you can run on a column can run on a group: .mean(), .sum(), .count(), .min(), .max(), .median(), .std().
Always look at .count() alongside a grouped mean. Unequal groups are invisible otherwise.
Grouping keys are often columns you derived, not columns you were given.

Getting Started

The question you could not answer yesterday

Split, apply, combine

The same sentence, on one line

Reading the result

Choosing the aggregation

What can be a key

Putting it together

Key points

Resources