groupby in pandas — practice examples (Bamboo Weekly)

Split rows into groups, apply a function to each group, and combine the results back together.

groupby is the method you reach for whenever a question contains the words "for each" — total sales for each region, average temperature for each month, number of plants for each country. If you find yourself writing a loop over the unique values of a column, groupby is almost certainly the answer you actually want.

Official documentation: DataFrame.groupby

The shape of a groupby

Every groupby has three parts: the column you group by, the column(s) you want to summarize, and the aggregation method that does the summarizing.

(
    df
    .groupby('Country')          # split: one group per country
    ['Capacity (MW)']            # the column we want to summarize
    .sum()                       # apply + combine
)

That returns a series, indexed by country. The four forms worth knowing:

# One value column, one aggregation
df.groupby('Country')['Capacity (MW)'].sum()

# Several value columns at once
df.groupby('Country')[['Capacity (MW)', 'Annual CO2 (million tonnes / annum)']].sum()

# Several grouping keys -- gives a MultiIndex
df.groupby(['Region', 'Status'])['Capacity (MW)'].mean()

# Different aggregations per column, with names you choose
df.groupby('Country').agg(
    total_capacity=('Capacity (MW)', 'sum'),
    plant_count=('Capacity (MW)', 'size'),
    biggest=('Capacity (MW)', 'max'),
)

A worked example, on real data

Here is a question from Bamboo Weekly #64, using the Global Coal Plant Tracker — a real spreadsheet of every coal-fired generating unit on earth.

We load it, keeping only the columns we need:

import pandas as pd

url = ('https://www.bambooweekly.com/content/files/wp-content/uploads/2024/02/'
       'global-coal-plant-tracker-january-2024.xlsx')

df = pd.read_excel(url, sheet_name='Units',
                   usecols=['Country', 'Capacity (MW)', 'Status',
                            'Start year', 'Combustion technology',
                            'Coal type', 'Region',
                            'Annual CO2 (million tonnes / annum)'])

The question: which countries emit the most CO2 from coal?

The data has one row per generating unit, and many units per country. So we group by country and sum the CO2 column:

(
    df
    .groupby('Country')
    ['Annual CO2 (million tonnes / annum)']
    .sum()
)

That works, but the result comes back sorted alphabetically by country, which tells us nothing. We want the worst offenders first, so we chain on sort_values:

(
    df
    .groupby('Country')
    ['Annual CO2 (million tonnes / annum)']
    .sum()
    .sort_values(ascending=False)
)

And since we only care about the top of that list, head:

(
    df
    .groupby('Country')
    ['Annual CO2 (million tonnes / annum)']
    .sum()
    .sort_values(ascending=False)
    .head(15)
)

Which gives:

Country
China             10091.0
India              3941.4
United States      1999.9

Notice that nothing was assigned to a variable along the way. Each step returns a new object, and the next method acts on it — that is the whole idea of method chaining, and it keeps the pipeline readable top to bottom.

Four mistakes people make

I cover these and more on video in Five Pandas "groupby" mistakes to avoid.

The group key becomes the index, not a column. After .groupby('Country')['x'].sum() you have a series indexed by country, so result['Country'] raises a KeyError. If you want a regular data frame with Country as an ordinary column, either pass as_index=False or chain .reset_index():

(
    df
    .groupby('Country', as_index=False)
    ['Annual CO2 (million tonnes / annum)']
    .sum()
)

Rows with a missing group key vanish silently. groupby defaults to dropna=True, so any row whose grouping column is NaN is excluded from every group — and from your totals. If missing keys are meaningful, say dropna=False and they will be grouped under NaN.

Grouping by a categorical column can produce empty groups. In Pandas 3.0 observed defaults to True, so only categories actually present in the data appear. On older versions the default was False, which returns a row for every defined category — including ones with no rows, giving you zeros or NaN you did not expect. Older Bamboo Weekly issues pass observed=True explicitly for exactly this reason. I walk through that change in Pandas 3's groupby defaults to observed=True? What does that mean?.

Reaching for apply when agg will do. .apply hands your function an entire sub-frame and is markedly slower. If you are computing a standard reduction — sum, mean, count, max — use .agg, which Pandas can run in optimized code:

(
    df
    .groupby('Region')
    .agg(total_mw=('Capacity (MW)', 'sum'),
         units=('Capacity (MW)', 'size'))
)

Practice it

Work through a groupby exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/groupby/

Go deeper

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

See it on real data

Below are the 103 Bamboo Weekly exercises that use groupby on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.

pandas groupby