McKinney Chapter 10 - Data Aggregation and Group Operations

FINA 6333 for Spring 2025

Author

Richard Herron 

import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import pandas_datareader as pdr
import yfinance as yf
%precision 4
pd.options.display.float_format = '{:.4f}'.format
# %config InlineBackend.figure_format = 'retina'

Introduction

Chapter 10 of McKinney (2022) discusses groupby operations, the pandas equivalent of pivot tables in Excel. Pivot tables calculate statistics (e.g., sums, means, and medians) for one set of variables by groups of other variables (e.g., weekdays and tickers). For example, we could use a pivot table to calculate mean daily stock returns by weekday.

We will focus on:

  1. The .groupby() method to group by columns and indexes
  2. The .agg() method to aggregate columns to single values
  3. The .pivot_table() method as an alternative to .groupby()

Note: Indented block quotes are from McKinney (2022) unless otherwise indicated. The section numbers here differ from McKinney (2022) because we will only discuss some topics.

GroupBy Mechanics

“Split-apply-combine” is an excellent way to describe pandas groupby operations.

Hadley Wickham, an author of many popular packages for the R programming language, coined the term split-apply-combine for describing group operations. In the first stage of the process, data contained in a pandas object, whether a Series, DataFrame, or otherwise, is split into groups based on one or more keys that you provide. The splitting is performed on a particular axis of an object. For example, a DataFrame can be grouped on its rows (axis=0) or its columns (axis=1). Once this is done, a function is applied to each group, producing a new value. Finally, the results of all those function applications are combined into a result object. The form of the resulting object will usually depend on what’s being done to the data. See Figure 10-1 for a mockup of a simple group aggregation.

Figure 10-1 visualizes a split-apply-combine operation that:

  1. Splits by the key column (i.e., “groups by key”)
  2. Applies the sum operation to the data column (i.e., “and sums data”)
  3. Combines the grouped sums (i.e., “combines the output”)

We could describe this operation as “sum the data column by groups of the key column then combines the output.”

np.random.seed(42)
df = pd.DataFrame({'key1' : ['a', 'a', 'b', 'b', 'a'],
                   'key2' : ['one', 'two', 'one', 'two', 'one'],
                   'data1' : np.random.randn(5),
                   'data2' : np.random.randn(5)})

df
key1 key2 data1 data2
0 a one 0.4967 -0.2341
1 a two -0.1383 1.5792
2 b one 0.6477 0.7674
3 b two 1.5230 -0.4695
4 a one -0.2342 0.5426

Here is the manual way to calculate the means of data1 by groups of key1.

df.loc[df['key1'] == 'a', 'data1'].mean()
0.0414
df.loc[df['key1'] == 'b', 'data1'].mean()
1.0854

We can do this calculation more easily!

  1. Use the .groupby() method to group by key1
  2. Use the .mean() method to calculate the mean of data1 within each value of key1
df['data1'].groupby(df['key1']).mean()
key1
a   0.0414
b   1.0854
Name: data1, dtype: float64

We can wrap data1 with two sets of square brackets if we prefer our result as a data frame instead of a series.

df[['data1']].groupby(df['key1']).mean()
data1
key1
a 0.0414
b 1.0854

We can group by more than one variable!

means = df['data1'].groupby([df['key1'], df['key2']]).mean()
means
key1  key2
a     one     0.1313
      two    -0.1383
b     one     0.6477
      two     1.5230
Name: data1, dtype: float64

We can use the .unstack() method if we want to use both rows and columns to organize data. Recall that the .unstack() method un-stacks the inner index level (i.e., level = -1) by default so that key2 values become the columns.

means.unstack()
key2 one two
key1
a 0.1313 -0.1383
b 0.6477 1.5230

Our grouping variables are typically columns in the data frame we want to group, so the following syntax is more compact and easier to read.

df.groupby(['key1', 'key2'])['data1'].mean().unstack()
key2 one two
key1
a 0.1313 -0.1383
b 0.6477 1.5230

We can wrap long chains in parentheses to insert line breaks and improve readability.

(
    df
    .groupby(['key1', 'key2'])
    ['data1']
    .mean()
    .unstack()
)
key2 one two
key1
a 0.1313 -0.1383
b 0.6477 1.5230

However, we must pass only numerical columns to numerical aggregation methods. Otherwise, pandas will give a type error. For example, in the following code, pandas unsuccessfully tries to calculate the mean of string key2.

# # TypeError: agg function failed [how->mean,dtype->object]
# df.groupby('key1').mean()

We avoid this error by slicing the numerical columns.

df.groupby('key1')[['data1', 'data2']].mean()
data1 data2
key1
a 0.0414 0.6292
b 1.0854 0.1490

Grouping with Functions

We can also group with functions. Below, we group with the len function, which calculates the lengths of the labels in the row index.

np.random.seed(42)
people = pd.DataFrame(
    data=np.random.randn(5, 5), 
    columns=['a', 'b', 'c', 'd', 'e'], 
    index=['Joe', 'Steve', 'Wes', 'Jim', 'Travis']
)

people
a b c d e
Joe 0.4967 -0.1383 0.6477 1.5230 -0.2342
Steve -0.2341 1.5792 0.7674 -0.4695 0.5426
Wes -0.4634 -0.4657 0.2420 -1.9133 -1.7249
Jim -0.5623 -1.0128 0.3142 -0.9080 -1.4123
Travis 1.4656 -0.2258 0.0675 -1.4247 -0.5444 
people.groupby(len).sum()
a b c d e
3 -0.5290 -1.6168 1.2039 -1.2983 -3.3714
5 -0.2341 1.5792 0.7674 -0.4695 0.5426
6 1.4656 -0.2258 0.0675 -1.4247 -0.5444

We can mix functions, lists, dictionaries, etc., as arguments to the .groupby() method.

key_list = ['one', 'one', 'one', 'two', 'two']
people.groupby([len, key_list]).min()
a b c d e
3 one -0.4634 -0.4657 0.2420 -1.9133 -1.7249
two -0.5623 -1.0128 0.3142 -0.9080 -1.4123
5 one -0.2341 1.5792 0.7674 -0.4695 0.5426
6 two 1.4656 -0.2258 0.0675 -1.4247 -0.5444 
d = {'Joe': 'a', 'Jim': 'b'}
people.groupby([len, d]).min()
a b c d e
3 a 0.4967 -0.1383 0.6477 1.5230 -0.2342
b -0.5623 -1.0128 0.3142 -0.9080 -1.4123 
d_2 = {'Joe': 'Cool', 'Jim': 'Nerd', 'Travis': 'Cool'}
people.groupby([len, d_2]).min()
a b c d e
3 Cool 0.4967 -0.1383 0.6477 1.5230 -0.2342
Nerd -0.5623 -1.0128 0.3142 -0.9080 -1.4123
6 Cool 1.4656 -0.2258 0.0675 -1.4247 -0.5444

Grouping by Index Levels

We can also group by index levels.

columns = pd.MultiIndex.from_arrays([['US', 'US', 'US', 'JP', 'JP'],
                                    [1, 3, 5, 1, 3]],
                                    names=['cty', 'tenor'])
hier_df = pd.DataFrame(np.random.randn(4, 5), columns=columns).transpose()
hier_df
0 1 2 3
cty tenor
US 1 0.1109 -0.6017 -1.2208 0.7385
3 -1.1510 1.8523 0.2089 0.1714
5 0.3757 -0.0135 -1.9597 -0.1156
JP 1 -0.6006 -1.0577 -1.3282 -0.3011
3 -0.2917 0.8225 0.1969 -1.4785 
hier_df.groupby(level='cty').count()
0 1 2 3
cty
JP 2 2 2 2
US 3 3 3 3 
hier_df.groupby(level='tenor').count()
0 1 2 3
tenor
1 2 2 2 2
3 2 2 2 2
5 1 1 1 1

Data Aggregation

Table 10-1 summarizes the optimized groupby methods:

  • count: Number of non-NA values in the group
  • sum: Sum of non-NA values
  • mean: Mean of non-NA values
  • median: Arithmetic median of non-NA values
  • std, var: Unbiased (n – 1 denominator) standard deviation and variance
  • min, max: Minimum and maximum of non-NA values
  • prod: Product of non-NA values
  • first, last: First and last non-NA values

These optimized methods are fast and efficient. Still, pandas lets us use non-optimized methods. First, any series method is available.

df
key1 key2 data1 data2
0 a one 0.4967 -0.2341
1 a two -0.1383 1.5792
2 b one 0.6477 0.7674
3 b two 1.5230 -0.4695
4 a one -0.2342 0.5426 
df.groupby('key1')['data1'].quantile(0.9)
key1
a   0.3697
b   1.4355
Name: data1, dtype: float64
0.6477 + 0.9 * (1.5230 - 0.6477)
1.4355

Second, we can write functions and pass them to the .agg() method. These functions should accept an array and return a single value.

def max_minus_min(arr):
    return arr.max() - arr.min()
df.sort_values(by=['key1', 'data1'])
key1 key2 data1 data2
4 a one -0.2342 0.5426
1 a two -0.1383 1.5792
0 a one 0.4967 -0.2341
2 b one 0.6477 0.7674
3 b two 1.5230 -0.4695 
df.groupby('key1')['data1'].agg(max_minus_min)
key1
a   0.7309
b   0.8753
Name: data1, dtype: float64
1.5230 - 0.6477
0.8753

Some other methods work, too, even if they do not aggregate an array to a scalar.

df.groupby('key1')['data1'].describe()
count mean std min 25% 50% 75% max
key1
a 3.0000 0.0414 0.3972 -0.2342 -0.1862 -0.1383 0.1792 0.4967
b 2.0000 1.0854 0.6190 0.6477 0.8665 1.0854 1.3042 1.5230

The .agg() method provides two more handy features:

  1. We can pass multiple functions to operate on all columns
  2. We can pass specific functions to operate on specific columns

First, here are examples of multiple functions that operate on all columns.

df.groupby('key1')['data1'].agg(['mean', 'median', 'min', 'max'])
mean median min max
key1
a 0.0414 -0.1383 -0.2342 0.4967
b 1.0854 1.0854 0.6477 1.5230 
df.groupby('key1')[['data1', 'data2']].agg(['mean', 'median', 'min', 'max'])
data1 data2
mean median min max mean median min max
key1
a 0.0414 -0.1383 -0.2342 0.4967 0.6292 0.5426 -0.2341 1.5792
b 1.0854 1.0854 0.6477 1.5230 0.1490 0.1490 -0.4695 0.7674

Second, here are examples of specific functions that operate on specific columns.

df.groupby('key1').agg({'data1': 'mean', 'data2': 'median'})
data1 data2
key1
a 0.0414 0.5426
b 1.0854 0.1490

We can calculate the mean and standard deviation of data1 and the median of data2 by key1.

df.groupby('key1').agg({'data1': ['mean', 'std'], 'data2': 'median'})
data1 data2
mean std median
key1
a 0.0414 0.3972 0.5426
b 1.0854 0.6190 0.1490

Apply: General split-apply-combine

The .agg() method aggregates an array to a scalar. We can use the .apply() method for more general calculations that do not return a scalar. For example, the following top() function selects the top n rows in data frame x sorted by column col. The .sort_values() method sorts from low to high by default.

def top(x, col, n=1):
    return x.sort_values(col).head(n)
df
key1 key2 data1 data2
0 a one 0.4967 -0.2341
1 a two -0.1383 1.5792
2 b one 0.6477 0.7674
3 b two 1.5230 -0.4695
4 a one -0.2342 0.5426 
top(
    x=df.loc[df['key1'] == 'a'],
    col='data1',
    n=2
)
key1 key2 data1 data2
4 a one -0.2342 0.5426
1 a two -0.1383 1.5792

The following code returns the one row with the smallest value of data1 within each group of key1. Note: we include the include_groups=False to suppress the FutureWarning and adopt the future default behavior now.

The following code returns the two rows with the smallest values of data1 within each group of key1.

df.groupby('key1').apply(top, col='data1', include_groups=False)
key2 data1 data2
key1
a 4 one -0.2342 0.5426
b 2 one 0.6477 0.7674 
df.groupby('key1').apply(top, col='data1', n=2, include_groups=False)
key2 data1 data2
key1
a 4 one -0.2342 0.5426
1 two -0.1383 1.5792
b 2 one 0.6477 0.7674
3 two 1.5230 -0.4695

We must use the .reset_index() method with the drop=True argument if we want to drop the index from df.

(
    df
    .groupby('key1')
    .apply(top, col='data1', n=2, include_groups=False)
    .reset_index(level=1, drop=True)
)
key2 data1 data2
key1
a one -0.2342 0.5426
a two -0.1383 1.5792
b one 0.6477 0.7674
b two 1.5230 -0.4695
Note

The .agg() and .apply() methods both operate on groups created by the .groupby() method. However, they serve different purposes and have distinct use cases.

The .agg() method is designed for aggregating data, meaning it applies functions that reduce a group to a single value (e.g., mean, sum, or custom functions that return a single scalar). This method is useful for summarizing data across groups.

In contrast, the .apply() method is more general and flexible. The .apply() method returns results of varying shapes. The .agg() method is limited to scalar outputs for each group, but the .apply() method is not.

Pivot Tables and Cross-Tabulation

Above, we manually made pivot tables with the .groupby(), .agg(), .apply() and .unstack() methods. pandas provides Excel-style aggregations with the .pivot_table() method and the pandas.pivot_table() function. It is worthwhile to read the .pivot_table() docstring several times.

ind = (
    yf.download(
        tickers='^GSPC ^DJI ^IXIC ^FTSE ^N225 ^HSI',
        auto_adjust=False,
        progress=False
    )
    .iloc[:-1]
    .stack(future_stack=True)
)

ind.head()
Price Adj Close Close High Low Open Volume
Date Ticker
1927-12-30 ^DJI NaN NaN NaN NaN NaN NaN
^FTSE NaN NaN NaN NaN NaN NaN
^GSPC 17.6600 17.6600 17.6600 17.6600 17.6600 0.0000
^HSI NaN NaN NaN NaN NaN NaN
^IXIC NaN NaN NaN NaN NaN NaN

The default aggregation function for .pivot_table() is .mean(). For the remaining examples, we will only consider data from 2015 and later.

(
    ind
    .loc['2015':]
    .pivot_table(
        index='Ticker'
    )
)
Price Adj Close Close High Low Open Volume
Ticker
^DJI 27935.0069 27935.0069 28079.9469 27774.3386 27931.3049 303142509.7886
^FTSE 7163.3634 7163.3634 7203.9626 7121.6113 7162.5127 814461430.5924
^GSPC 3395.6074 3395.6074 3413.1090 3375.7649 3395.1843 4015744083.7901
^HSI 23785.6004 23785.6004 23950.5568 23615.3132 23798.3853 2145570432.1457
^IXIC 9999.4784 9999.4784 10065.3920 9924.6220 9998.5261 3567504600.6265
^N225 25038.6233 25038.6233 25175.5387 24893.2712 25039.7854 98144699.7179

We can specificy a different aggregation function with the aggfunc argument. We can use values to select specific variables, pd.Grouper() to sample different date windows, and aggfunc to select specific aggregation functions.

(
    ind
    .loc['2015':]
    .reset_index()
    .pivot_table(
        values='Close',
        index=pd.Grouper(key='Date', freq='YE'),
        columns='Ticker',
        aggfunc=['min', 'max']
    )
)
min max
Ticker ^DJI ^FTSE ^GSPC ^HSI ^IXIC ^N225 ^DJI ^FTSE ^GSPC ^HSI ^IXIC ^N225
Date
2015-12-31 15666.4404 5874.1001 1867.6100 20556.5996 4506.4902 16795.9609 18312.3906 7104.0000 2130.8201 28442.7500 5218.8599 20868.0293
2016-12-31 15660.1797 5537.0000 1829.0800 18319.5801 4266.8398 14952.0195 19974.6191 7142.7998 2271.7200 24099.6992 5487.4399 19494.5293
2017-12-31 19732.4004 7099.2002 2257.8301 22134.4707 5429.0801 18335.6309 24837.5098 7687.7998 2690.1599 30003.4902 6994.7598 22939.1797
2018-12-31 21792.1992 6584.7002 2351.1001 24585.5293 6192.9199 19155.7402 26828.3906 7877.5000 2930.7500 33154.1211 8109.6899 24270.6191
2019-12-31 22686.2207 6692.7002 2447.8899 25064.3594 6463.5000 19561.9609 28645.2598 7686.6001 3240.0200 30157.4902 9022.3896 24066.1191
2020-12-31 18591.9297 4993.8999 2237.3999 21696.1309 6860.6699 16552.8301 30606.4805 7674.6001 3756.0701 29056.4199 12899.4199 27568.1504
2021-12-31 29982.6191 6407.5000 3700.6499 22744.8594 12609.1602 27013.2500 36488.6289 7420.7002 4793.0601 31084.9395 16057.4404 30670.0996
2022-12-31 28725.5098 6826.2002 3577.0300 14687.0195 10213.2900 24717.5293 36799.6484 7672.3999 4796.5601 24965.5508 15832.7998 29332.1602
2023-12-31 31819.1406 7256.8999 3808.1001 16201.4902 10305.2402 25716.8594 37710.1016 8014.2998 4783.3501 22688.9004 15099.1797 33753.3281
2024-12-31 37266.6719 7446.2998 4688.6802 14961.1797 14510.2998 31458.4199 45014.0391 8445.7998 6090.2700 23099.7793 20173.8906 42224.0195
2025-12-31 41938.4492 8201.5000 5827.0400 18874.1406 18544.4199 38142.3711 44882.1289 8807.4004 6144.1499 23787.9297 20056.2500 40083.3008

References

McKinney, Wes. 2022. Python for Data Analysis. 3rd ed. https://wesmckinney.com/book/; O’Reilly Media, Inc.