GroupBy Aggregations

You've cleaned your data, filtered rows, and computed column stats — but now you're staring at a familiar wall: you need totals per category, averages per region, or counts per product, and doing it with loops feels clunky, slow, and error-prone. If you've ever written a for loop just to split a DataFrame into groups and calculate a mean for each, you know the pain. This lesson shows you how to create database-style aggregations with groupby — the pandas equivalent of SQL's GROUP BY — so you can transform raw tables into insightful summaries in one expressive line of code.

The problem this lesson solves

In real data analysis, you rarely want global summary statistics. You want to know: How does revenue differ by region? What's the average order value per customer? How many support tickets per product? These questions all share the same shape: split your data into meaningful groups, apply a calculation to each group, and combine the results.

Without a dedicated tool, this turns into manual iteration:

import pandas as pd

# Slow, verbose, and bug-prone approach
regions = df['region'].unique()
results = {}
for region in regions:
    subset = df[df['region'] == region]
    results[region] = subset['sales'].sum()

This manual approach is slow to write, slow to execute, and breaks easily — what if a region has fewer than 10 rows? What if you need two aggregations at once? You'd need nested loops and dicts of dicts. That's exactly the problem groupby solves: it gives you a scalable, expressive, and database-like way to aggregate data, handling all the splitting and combining for you.

Core concept / mental model

Think of groupby as pandas' built-in GROUP BY clause. In SQL, you write SELECT region, SUM(sales) FROM orders GROUP BY region. In pandas, you write df.groupby('region')['sales'].sum(). The mental model is three steps:

1. Split: The DataFrame is divided into groups based on one or more keys.
2. Apply: A function (like sum, mean, count) is applied to each group independently.
3. Combine: The results are assembled into a new DataFrame or Series.

This is often called the split-apply-combine pattern — a concept so fundamental it has its own name in data science. The key insight is that groupby doesn't compute anything until you call an aggregation method — it's lazy, like a query plan in a database.

The official pandas documentation calls this a groupby operation, and mastering it unlocks the ability to answer complex business questions with concise, readable code.

How it works step by step

The simplest groupby aggregation looks like this:

# Group by a single column and aggregate another
df.groupby('region')['sales'].sum()

Let's break down what happens at each step:

1. df.groupby('region'): pandas looks at the region column and creates a groupby object that knows how to split the DataFrame. At this point, nothing is computed.
2. ['sales']: you select the column you want to aggregate — this narrows the scope and improves performance.
3. .sum(): the aggregation method is applied to each group independently, producing a Series (if one column) or DataFrame (if multiple columns).

You can group by multiple columns to create hierarchical groupings, like region and product:

df.groupby(['region', 'product'])['sales'].sum()

This produces a DataFrame with a MultiIndex — think of it as a nested grouping. You can also use multiple aggregations at once with agg():

df.groupby('region')['sales'].agg(['sum', 'mean', 'count'])

Or even apply different functions to different columns:

df.groupby('region').agg({'sales': 'sum', 'profit': 'mean', 'orders': 'count'})

The result is a tidy DataFrame with one row per group and one column per aggregation — exactly what you'd get from a SQL query.

Hands-on walkthrough

Let's put this into practice with a realistic sales dataset. We'll create a DataFrame of orders, then answer several business questions using groupby aggregates.

Setup: create sample data

import pandas as pd

# Sample sales data
df = pd.DataFrame({
    'order_id': [1, 2, 3, 4, 5, 6],
    'region': ['North', 'South', 'North', 'East', 'South', 'East'],
    'product': ['Widget', 'Gadget', 'Widget', 'Gadget', 'Gadget', 'Widget'],
    'sales': [100, 200, 150, 300, 250, 400],
    'units': [2, 4, 3, 5, 4, 8]
})

print(df)

Output:

   order_id region product  sales  units
0         1  North  Widget    100      2
1         2  South  Gadget    200      4
2         3  North  Widget    150      3
3         4   East  Gadget    300      5
4         5  South  Gadget    250      4
5         6   East  Widget    400      8

Single-column aggregation

Now, compute total sales per region:

# Total sales per region
sales_by_region = df.groupby('region')['sales'].sum()
print(sales_by_region)

Output:

region
East     700
North    250
South    450
Name: sales, dtype: int64

Multiple aggregations

Get sum, mean, and count of sales per region in one go:

# Multiple aggregates in one call
summary = df.groupby('region')['sales'].agg(['sum', 'mean', 'count'])
print(summary)

Output:

        sum  mean  count
region                  
East    700  350.0      2
North   250  125.0      2
South   450  225.0      2

Group by multiple columns

Break sales down by region and product:

# Multi-column grouping
multi = df.groupby(['region', 'product'])['sales'].sum()
print(multi)

Output:

region  product
East    Gadget     300
        Widget     400
North   Widget     250
South   Gadget     450
Name: sales, dtype: int64

To use the result as a regular DataFrame, call .reset_index():

multi_df = df.groupby(['region', 'product'])['sales'].sum().reset_index()
print(multi_df)

Output:

  region product  sales
0   East  Gadget    300
1   East  Widget    400
2  North  Widget    250
3  South  Gadget    450

Custom aggregation functions

You can pass any NumPy function or a lambda to agg():

import numpy as np

# Custom + built-in mix
custom = df.groupby('region')['sales'].agg(['sum', lambda x: x.max() - x.min()])
print(custom)

Output:

        sum  <lambda>
region                
East    700       100
North   250         0
South   450        50

For better readability, name your lambda using a tuple:

custom_named = df.groupby('region')['sales'].agg(
    total='sum',
    range=lambda x: x.max() - x.min()
)
print(custom_named)

Output:

        total  range
region              
East      700    100
North     250      0
South     450     50

Compare options / when to choose what

Here's a quick comparison of the most common aggregation approaches in pandas:

When to choose which:

• Use groupby + agg when you need database-style summaries with multiple metrics — it's the most direct translation of SQL.
• Use pivot_table when you want rows as one key and columns as another (e.g., regions as rows, products as columns).
• Use .apply() sparingly — only when no built-in aggregation fits, as it can be slow for large datasets.

Troubleshooting & edge cases

Even experienced pandas users hit these confusing results. Here's how to fix them.

1. The result looks like a Series, but I need a DataFrame

groupby(...)[...].sum() returns a Series if you aggregate one column, or a DataFrame if you aggregate multiple columns at once. To force a DataFrame, use double brackets:

# Returns Series
series_result = df.groupby('region')['sales'].sum()

# Returns DataFrame
frame_result = df.groupby('region')[['sales']].sum()

2. Grouping column disappears from output

When you group by a column and aggregate, the grouping column becomes the index. If you want it as a regular column, call .reset_index():

result = df.groupby('region')['sales'].sum().reset_index()

3. agg with a single function still returns a DataFrame with a weird column name

If you pass a string like 'sum', pandas names the column 'sum', which can clash with your data. Use a dictionary or named aggregation to control names:

result = df.groupby('region')['sales'].agg(my_total='sum')

4. Grouping on a column with NaN values

By default, rows with NaN in the grouping column are dropped. Use dropna=False to keep them:

result = df.groupby('region', dropna=False)['sales'].sum()

5. Performance on large DataFrames

Grouping on many columns or using .apply() can be slow. Prefer built-in aggregations and select only the columns you need to speed things up. Also, consider converting grouping columns to categorical dtype if they have few unique values — it often improves performance.

What you learned & what's next

You now know how to create database-style aggregations with groupby — from simple sum() to complex multi-column agg() calls. You can explain the split-apply-combine mental model, apply groupby in a hands-on exercise, and troubleshoot common pitfalls like NaN handling and index vs. column confusion.

This is a foundational skill for data analysis. In the next lesson, you'll build on this to reshape data with pivot tables and merge multiple DataFrames — skills that turn isolated groupby summaries into full reporting workflows. Keep practicing: the more you use groupby, the more natural database-style thinking becomes.

Pro tip: Real-world analyses are rarely a single groupby. Practice chaining — start with groupby, then agg, then reset_index() — and soon you'll be writing elegant pipelines that would make a SQL developer proud.