In [1]:
from dsc80_utils import *

Lecture 3 – Aggregating¶

DSC 80, Summer 2026¶

Announcements 📣¶

  • Lab 1 grades are available. You can resubmit by tomorrow night for redemption credit.
  • Start working on Project 1. The checkpoint (part of the project) is due tonight.
  • Engagment interview slots are available for Friday. This is the alternative for lecture attendance.

Agenda¶

  • Data granularity and the groupby method.
  • DataFrameGroupBy objects and aggregation.
  • Other DataFrameGroupBy methods.
  • Pivot tables using the pivot_table method.

You will need to code a lot today – make sure to pull the course repository

Data granularity and the groupby method¶

Example: Palmer Penguins¶

No description has been provided for this image Artwork by @allison_horst

The dataset we'll work with for the rest of the lecture involves various measurements of three species of penguins in Antarctica.

In [2]:
import seaborn as sns
penguins = sns.load_dataset('penguins').dropna()
penguins
Out[2]:
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex
0 Adelie Torgersen 39.1 18.7 181.0 3750.0 Male
1 Adelie Torgersen 39.5 17.4 186.0 3800.0 Female
2 Adelie Torgersen 40.3 18.0 195.0 3250.0 Female
... ... ... ... ... ... ... ...
341 Gentoo Biscoe 50.4 15.7 222.0 5750.0 Male
342 Gentoo Biscoe 45.2 14.8 212.0 5200.0 Female
343 Gentoo Biscoe 49.9 16.1 213.0 5400.0 Male

333 rows × 7 columns

Here, each row corresponds to a single penguin, and each column corresponds to a different attribute (or feature) we have for each penguin. Data formatted in this way is called tidy data.

Granularity¶

  • Granularity refers to what each observation in a dataset represents.
    • Fine: small details.
    • Coarse: bigger picture.
  • If you can control how your dataset is created, you should opt for finer granularity, i.e. for more detail.
    • You can always remove details, but it's difficult to add detail that isn't already there.
    • But obtaining fine-grained data can take more time/money.
  • Today, we'll focus on how to remove details from fine-grained data, in order to help us understand bigger-picture trends in our data.

Aggregating¶

Aggregating is the act of combining many values into a single value.

  • What is the mean 'body_mass_g' for all penguins?
In [ ]:
  • This may be too coarse of a summary to be useful. Instead, what is the mean 'body_mass_g' for each species?

Naive approach: looping through unique values¶

In [4]:
species_map = pd.Series([], dtype=float)

for species in penguins['species'].unique():
    species_only = penguins.loc[penguins['species'] == species]
    species_map.loc[species] = species_only['body_mass_g'].mean()

species_map
Out[4]:
Adelie       3706.16
Chinstrap    3733.09
Gentoo       5092.44
dtype: float64
  • Inefficient: For each unique 'species', we make a pass through the entire dataset.
    • The asymptotic runtime of this procedure is $\Theta(ns)$, where $n$ is the number of rows and $s$ is the number of unique species.
  • Main problem: This is a lot of code to write, for such a common operation.

Grouping¶

A better solution, as we know from DSC 10, is to use the groupby method.

In [5]:
# Mean body_mass_g for each species
# Start by extracting relevant columns

Somehow, the groupby method computes what we're looking for in just one line. How?

"Split-apply-combine" paradigm¶

The groupby method involves three steps: split, apply, and combine. This is the same terminology that the pandas documentation uses.

No description has been provided for this image

  • Split breaks up and "groups" the rows of a DataFrame according to the specified key. There is one "group" for every unique value of the key.

  • Apply uses a function (e.g. aggregation, transformation, filtration) within the individual groups.

  • Combine stitches the results of these operations into an output DataFrame.

  • The split-apply-combine pattern can be parallelized to work on multiple computers or threads, by sending computations for each group to different processors.

More examples¶

Before we dive into the internals, let's look at a few more examples.

Question 🤔

What proportion of penguins of each 'species' live on 'Dream' island?

Your output should look like:

species
Adelie       0.38
Chinstrap    1.00
Gentoo       0.00
In [6]:
# approach 1
In [7]:
# approach 2

DataFrameGroupBy objects and aggregation¶

DataFrameGroupBy objects¶

We've just evaluated a few expressions of the following form.

In [8]:
penguins[['species', 'body_mass_g']].groupby('species').mean()
Out[8]:
body_mass_g
species
Adelie 3706.16
Chinstrap 3733.09
Gentoo 5092.44

There are two method calls in the expression above: .groupby('species') and .mean(). What happens in the .groupby() call?

In [9]:
penguins.groupby('species')
Out[9]:
<pandas.core.groupby.generic.DataFrameGroupBy object at 0x1066f92b0>

Peeking under the hood¶

If df is a DataFrame, then df.groupby(key) returns a DataFrameGroupBy object.

This object represents the "split" in "split-apply-combine".

In [10]:
# Simplified DataFrame for demonstration:
penguins_small = penguins.iloc[[0, 150, 300, 1, 251, 151, 301], [0, 5, 6]]
penguins_small
Out[10]:
species body_mass_g sex
0 Adelie 3750.0 Male
156 Chinstrap 3725.0 Male
308 Gentoo 4875.0 Female
1 Adelie 3800.0 Female
258 Gentoo 4350.0 Female
157 Chinstrap 3950.0 Female
309 Gentoo 5550.0 Male
In [11]:
# Creates one group for each unique value in the species column.
penguin_groups = penguins_small.groupby('species')
penguin_groups
Out[11]:
<pandas.core.groupby.generic.DataFrameGroupBy object at 0x1066f80e0>

DataFrameGroupBy objects have a groups attribute, which is a dictionary in which the keys are group names and the values are lists of row labels.

In [12]:
penguin_groups.groups
Out[12]:
{'Adelie': [0, 1], 'Chinstrap': [156, 157], 'Gentoo': [308, 258, 309]}

DataFrameGroupBy objects also have a get_group(key) method, which returns a DataFrame with only the values for the given key.

In [13]:
penguin_groups.get_group('Chinstrap')
Out[13]:
species body_mass_g sex
156 Chinstrap 3725.0 Male
157 Chinstrap 3950.0 Female
In [14]:
# Same as the above!
penguins_small.query('species == "Chinstrap"')
Out[14]:
species body_mass_g sex
156 Chinstrap 3725.0 Male
157 Chinstrap 3950.0 Female

We usually don't use these attributes and methods, but they're useful in understanding how groupby works under the hood.

Aggregation¶

  • Once we create a DataFrameGroupBy object, we need to apply some function to each group, and combine the results.

  • The most common operation we apply to each group is an aggregation.

    • Remember, aggregation is the act of combining many values into a single value.

  • To perform an aggregation, use an aggregation method on the DataFrameGroupBy object, e.g. .mean(), .max(), or .median().

Let's look at some examples.

In [15]:
penguins_small
Out[15]:
species body_mass_g sex
0 Adelie 3750.0 Male
156 Chinstrap 3725.0 Male
308 Gentoo 4875.0 Female
1 Adelie 3800.0 Female
258 Gentoo 4350.0 Female
157 Chinstrap 3950.0 Female
309 Gentoo 5550.0 Male
In [16]:
# Whoa, what happened in the sex column?
penguins_small.groupby('species').sum()
Out[16]:
body_mass_g sex
species
Adelie 7550.0 MaleFemale
Chinstrap 7675.0 MaleFemale
Gentoo 14775.0 FemaleFemaleMale
In [17]:
# Select out the column you want before aggregating
penguins_small.groupby('species')['body_mass_g'].sum()
Out[17]:
species
Adelie        7550.0
Chinstrap     7675.0
Gentoo       14775.0
Name: body_mass_g, dtype: float64
In [18]:
penguins_small.groupby('species').first()
Out[18]:
body_mass_g sex
species
Adelie 3750.0 Male
Chinstrap 3725.0 Male
Gentoo 4875.0 Female
In [19]:
penguins_small.groupby('species').max()
Out[19]:
body_mass_g sex
species
Adelie 3800.0 Male
Chinstrap 3950.0 Male
Gentoo 5550.0 Male

Column independence¶

Within each group, the aggregation method is applied to each column independently.

In [20]:
penguins_small.groupby('species').max()
Out[20]:
body_mass_g sex
species
Adelie 3800.0 Male
Chinstrap 3950.0 Male
Gentoo 5550.0 Male

It is not telling us that there is a 'Male' 'Adelie' penguin with a 'body_mass_g' of 3800.0!

In [21]:
# The Adelie penguin with a body mass of 3800g is Female!
penguins_small.loc[(penguins['species'] == 'Adelie') & (penguins['body_mass_g'] == 3800.0)]
Out[21]:
species body_mass_g sex
1 Adelie 3800.0 Female

Question 🤔

Find the species, island, and body_mass_g of the heaviest Male and Female penguins in penguins (not penguins_small).

In [22]:
# approach 1
In [23]:
# approach 2

Column selection and performance implications¶

  • By default, the aggregator will be applied to all columns that it can be applied to.
    • max, min, and sum are defined on strings, while median and mean are not.
  • If we only care about one column, we can select that column before aggregating to save time.
    • DataFrameGroupBy objects support [] notation, just like DataFrames.
In [24]:
# Back to the big penguins dataset!
penguins
Out[24]:
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex
0 Adelie Torgersen 39.1 18.7 181.0 3750.0 Male
1 Adelie Torgersen 39.5 17.4 186.0 3800.0 Female
2 Adelie Torgersen 40.3 18.0 195.0 3250.0 Female
... ... ... ... ... ... ... ...
341 Gentoo Biscoe 50.4 15.7 222.0 5750.0 Male
342 Gentoo Biscoe 45.2 14.8 212.0 5200.0 Female
343 Gentoo Biscoe 49.9 16.1 213.0 5400.0 Male

333 rows × 7 columns

In [25]:
# Works, but involves wasted effort since the other columns had to be aggregated for no reason.
penguins.groupby('species').sum()['bill_length_mm']
Out[25]:
species
Adelie       5668.3
Chinstrap    3320.7
Gentoo       5660.6
Name: bill_length_mm, dtype: float64
In [26]:
# This is a SeriesGroupBy object!
penguins.groupby('species')['bill_length_mm']
Out[26]:
<pandas.core.groupby.generic.SeriesGroupBy object at 0x135dde6f0>
In [27]:
# Saves time!
penguins.groupby('species')['bill_length_mm'].sum()
Out[27]:
species
Adelie       5668.3
Chinstrap    3320.7
Gentoo       5660.6
Name: bill_length_mm, dtype: float64

To demonstrate that the former is slower than the latter, we can use %%timeit. For reference, we'll also include our earlier for-loop-based solution.

In [28]:
%%timeit
penguins.groupby('species').sum()['bill_length_mm']

361 μs ± 3.4 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)
In [29]:
%%timeit
penguins.groupby('species')['bill_length_mm'].sum()

116 μs ± 2.9 μs per loop (mean ± std. dev. of 7 runs, 10,000 loops each)
In [30]:
%%timeit
species_map = pd.Series([], dtype=float)

for species in penguins['species'].unique():
    species_only = penguins.loc[penguins['species'] == species]
    species_map.loc[species] = species_only['body_mass_g'].mean()

species_map

857 μs ± 31.7 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)

Takeaways¶

  • It's important to understand what each piece of your code evaluates to – in the first two timed examples, the code is almost identical, but the performance is quite different.

    # Slower
penguins.groupby('species').sum()['bill_length_mm']

# Faster
penguins.groupby('species')['bill_length_mm'].sum()

  • The groupby method is much quicker than for-looping over the DataFrame in Python. It can often produce results using just a single, fast pass over the data, updating the sum, mean, count, min, or other aggregate for each group along the way.

  • You should almost always select the columns you want directly after groupby.

Beyond default aggregation methods¶

  • There are many built-in aggregation methods.
  • What if you want to apply different aggregation methods to different columns?
  • What if the aggregation method you want to use doesn't already exist in pandas?

The aggregate method¶

  • The DataFrameGroupBy object has a general aggregate method, which aggregates using one or more operations.
    • Remember, aggregation is the act of combining many values into a single value.
  • There are many ways of using aggregate; refer to the documentation for a comprehensive list.
  • Example arguments:
    • A single function.
    • A list of functions.
    • A dictionary mapping column names to functions.
  • Per the documentation, agg is an alias for aggregate.

Example¶

How many penguins are there of each 'species', and what is the mean 'body_mass_g' of each 'species'?

In [31]:
(penguins
 .groupby('species')
 ['body_mass_g']
 .aggregate(['count', 'mean'])
)
Out[31]:
count mean
species
Adelie 146 3706.16
Chinstrap 68 3733.09
Gentoo 119 5092.44

Example¶

What is the maximum 'bill_length_mm' of each 'species', and which 'island's is each 'species' found on?

In [32]:
(penguins
 .groupby('species')
 .aggregate({'bill_length_mm': 'max', 'island': 'unique'})
)
Out[32]:
bill_length_mm island
species
Adelie 46.0 [Torgersen, Biscoe, Dream]
Chinstrap 58.0 [Dream]
Gentoo 59.6 [Biscoe]

Example¶

What is the interquartile range of the 'body_mass_g' of each 'species'?

In [33]:
# Here, the argument to agg is a function,
# which takes in a pd.Series and returns a scalar.

def iqr(s):
    return np.percentile(s, 75) - np.percentile(s, 25)

(penguins
 .groupby('species')
 ['body_mass_g']
 .agg(iqr)
)
Out[33]:
species
Adelie       637.5
Chinstrap    462.5
Gentoo       800.0
Name: body_mass_g, dtype: float64

Other DataFrameGroupBy methods¶

Split-apply-combine, revisited¶

When we introduced the split-apply-combine pattern, the "apply" step involved aggregation – our final DataFrame had one row for each group.

No description has been provided for this image

Instead of aggregating during the apply step, we could instead perform a:

  • Transformation, in which we perform operations to every value within each group.

  • Filtration, in which we keep only the groups that satisfy some condition.

Transformations¶

Suppose we want to convert the 'body_mass_g' column to to z-scores (i.e. standard units):

$$z(x_i) = \frac{x_i - \text{mean of } x}{\text{SD of } x}$$

In [34]:
def z_score(x):
    return (x - x.mean()) / np.std(x)
In [35]:
z_score(penguins['body_mass_g'])
Out[35]:
0     -0.57
1     -0.51
2     -1.19
       ... 
341    1.92
342    1.23
343    1.48
Name: body_mass_g, Length: 333, dtype: float64

Transformations within groups¶

  • Now, what if we wanted the z-score within each group?

  • To do so, we can use the transform method on a DataFrameGroupBy object. The transform method takes in a function, which itself takes in a Series and returns a new Series.

  • A transformation produces a DataFrame or Series of the same size – it is not an aggregation!

In [36]:
z_mass = (penguins
          .groupby('species')
          ['body_mass_g']
          .transform(z_score))
z_mass
Out[36]:
0      0.10
1      0.21
2     -1.00
       ... 
341    1.32
342    0.22
343    0.62
Name: body_mass_g, Length: 333, dtype: float64
In [37]:
display_df(penguins.assign(z_mass=z_mass), rows=8)
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex z_mass
0 Adelie Torgersen 39.1 18.7 181.0 3750.0 Male 0.10
1 Adelie Torgersen 39.5 17.4 186.0 3800.0 Female 0.21
2 Adelie Torgersen 40.3 18.0 195.0 3250.0 Female -1.00
4 Adelie Torgersen 36.7 19.3 193.0 3450.0 Female -0.56
... ... ... ... ... ... ... ... ...
340 Gentoo Biscoe 46.8 14.3 215.0 4850.0 Female -0.49
341 Gentoo Biscoe 50.4 15.7 222.0 5750.0 Male 1.32
342 Gentoo Biscoe 45.2 14.8 212.0 5200.0 Female 0.22
343 Gentoo Biscoe 49.9 16.1 213.0 5400.0 Male 0.62

333 rows × 8 columns

Note that above, penguin 340 has a larger 'body_mass_g' than penguin 0, but a lower 'z_mass'.

  • Penguin 0 has an above average 'body_mass_g' among 'Adelie' penguins.
  • Penguin 340 has a below average 'body_mass_g' among 'Gentoo' penguins. Remember from earlier that the average 'body_mass_g' of 'Gentoo' penguins is much higher than for other species.
In [38]:
penguins.groupby('species')['body_mass_g'].mean()
Out[38]:
species
Adelie       3706.16
Chinstrap    3733.09
Gentoo       5092.44
Name: body_mass_g, dtype: float64

Filtering groups¶

  • To keep only the groups that satisfy a particular condition, use the filter method on a DataFrameGroupBy object.

  • The filter method takes in a function, which itself takes in a DataFrame/Series and returns a single Boolean. The result is a new DataFrame/Series with only the groups for which the filter function returned True.

For example, suppose we want only the 'species' whose average 'bill_length_mm' is above 39.

In [39]:
(penguins
 .groupby('species')
 .filter(lambda df: df['bill_length_mm'].mean() > 39)
)
Out[39]:
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex
152 Chinstrap Dream 46.5 17.9 192.0 3500.0 Female
153 Chinstrap Dream 50.0 19.5 196.0 3900.0 Male
154 Chinstrap Dream 51.3 19.2 193.0 3650.0 Male
... ... ... ... ... ... ... ...
341 Gentoo Biscoe 50.4 15.7 222.0 5750.0 Male
342 Gentoo Biscoe 45.2 14.8 212.0 5200.0 Female
343 Gentoo Biscoe 49.9 16.1 213.0 5400.0 Male

187 rows × 7 columns

No more 'Adelie's!

Or, as another example, suppose we only want 'species' with at least 100 penguins:

In [40]:
(penguins
 .groupby('species')
 .filter(lambda df: df.shape[0] > 100)
)
Out[40]:
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex
0 Adelie Torgersen 39.1 18.7 181.0 3750.0 Male
1 Adelie Torgersen 39.5 17.4 186.0 3800.0 Female
2 Adelie Torgersen 40.3 18.0 195.0 3250.0 Female
... ... ... ... ... ... ... ...
341 Gentoo Biscoe 50.4 15.7 222.0 5750.0 Male
342 Gentoo Biscoe 45.2 14.8 212.0 5200.0 Female
343 Gentoo Biscoe 49.9 16.1 213.0 5400.0 Male

265 rows × 7 columns

No more 'Chinstrap's!

Question 🤔

Answer the following questions about grouping:

  • In .agg(fn), what is the input to fn? What is the output of fn?
  • In .transform(fn), what is the input to fn? What is the output of fn?
  • In .filter(fn), what is the input to fn? What is the output of fn?

Grouping with multiple columns¶

When we group with multiple columns, one group is created for every unique combination of elements in the specified columns.

In [41]:
penguins
Out[41]:
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex
0 Adelie Torgersen 39.1 18.7 181.0 3750.0 Male
1 Adelie Torgersen 39.5 17.4 186.0 3800.0 Female
2 Adelie Torgersen 40.3 18.0 195.0 3250.0 Female
... ... ... ... ... ... ... ...
341 Gentoo Biscoe 50.4 15.7 222.0 5750.0 Male
342 Gentoo Biscoe 45.2 14.8 212.0 5200.0 Female
343 Gentoo Biscoe 49.9 16.1 213.0 5400.0 Male

333 rows × 7 columns

In [42]:
species_and_island = (
    penguins
    .groupby(['species', 'island'])
    [['bill_length_mm', 'body_mass_g']]
    .mean()
)
species_and_island
Out[42]:
bill_length_mm body_mass_g
species island
Adelie Biscoe 38.98 3709.66
Dream 38.52 3701.36
Torgersen 39.04 3708.51
Chinstrap Dream 48.83 3733.09
Gentoo Biscoe 47.57 5092.44

MultiIndex¶

  • The groupby method creates an index based on the specified columns.
  • When grouping by multiple columns, the resulting DataFrame has a MultiIndex.
  • Advice: When working with a MultiIndex, use reset_index or set as_index=False in groupby.
In [43]:
species_and_island
Out[43]:
bill_length_mm body_mass_g
species island
Adelie Biscoe 38.98 3709.66
Dream 38.52 3701.36
Torgersen 39.04 3708.51
Chinstrap Dream 48.83 3733.09
Gentoo Biscoe 47.57 5092.44
In [44]:
species_and_island['body_mass_g']
Out[44]:
species    island   
Adelie     Biscoe       3709.66
           Dream        3701.36
           Torgersen    3708.51
Chinstrap  Dream        3733.09
Gentoo     Biscoe       5092.44
Name: body_mass_g, dtype: float64
In [45]:
species_and_island.loc['Adelie']
Out[45]:
bill_length_mm body_mass_g
island
Biscoe 38.98 3709.66
Dream 38.52 3701.36
Torgersen 39.04 3708.51
In [46]:
species_and_island.loc[('Adelie', 'Torgersen')]
Out[46]:
bill_length_mm      39.04
body_mass_g       3708.51
Name: (Adelie, Torgersen), dtype: float64
In [47]:
species_and_island.reset_index()
Out[47]:
species island bill_length_mm body_mass_g
0 Adelie Biscoe 38.98 3709.66
1 Adelie Dream 38.52 3701.36
2 Adelie Torgersen 39.04 3708.51
3 Chinstrap Dream 48.83 3733.09
4 Gentoo Biscoe 47.57 5092.44
In [48]:
(penguins
 .groupby(['species', 'island'], as_index=False)
 [['bill_length_mm', 'body_mass_g']]
 .mean()
)
Out[48]:
species island bill_length_mm body_mass_g
0 Adelie Biscoe 38.98 3709.66
1 Adelie Dream 38.52 3701.36
2 Adelie Torgersen 39.04 3708.51
3 Chinstrap Dream 48.83 3733.09
4 Gentoo Biscoe 47.57 5092.44

Question 🤔

Find the most popular Male and Female baby Name for each Year in baby. Exclude Years where there were fewer than 1 million births recorded.

In [49]:
baby_path = Path('data') / 'baby.csv'
baby = pd.read_csv(baby_path)
baby
Out[49]:
Name Sex Count Year
0 Liam M 20456 2022
1 Noah M 18621 2022
2 Olivia F 16573 2022
... ... ... ... ...
2085155 Wright M 5 1880
2085156 York M 5 1880
2085157 Zachariah M 5 1880

2085158 rows × 4 columns

In [ ]:

Summary, next time¶

Summary¶

  • Grouping allows us to change the level of granularity in a DataFrame.
  • Grouping involves three steps – split, apply, and combine.
    • Usually, what is applied is an aggregation, but it could be a transformation or filtration.

Next time¶

  • Pivot tables.
  • Simpson's paradox.
  • Merging.
    • Review this diagram from DSC 10!
  • The pitfalls of the apply method.