Pandas: GroupBy Aggregation

Last updated: 2026-08-26

GroupBy aggregation is the heart of data analysis — viewing sales by region, computing average prices by category, counting orders by month — they all boil down to "split, compute, combine." Pandas' groupby implements the classic split-apply-combine paradigm. In this lesson, Mermaid diagrams will help you fully understand the process, and you'll master the three key operations: agg / transform / filter.

⚠️ Note: The code below requires a local Python environment to run.

1. What You Will Learn

❶ How groupby works (split-apply-combine)
❷ Aggregation functions (sum/mean/count/max/min)
❸ Multiple aggregations and custom functions with agg
❹ Within-group transformation with transform
❺ Group-level filtering with filter

2. Alice Tallies Coffee Shop Sales by Category

(1) The Pain Point: Manual Looping Is Tedious

Alice wants to tally sales by drink category. Writing loops by hand:

PYTHON

import pandas as pd

df = pd.DataFrame({
    'drink': ['Latte', 'Americano', 'Mocha', 'Latte', 'Espresso',
              'Americano', 'Latte', 'Mocha', 'Espresso', 'Latte'],
    'category': ['Hot', 'Hot', 'Hot', 'Hot', 'Hot',
                 'Iced', 'Iced', 'Iced', 'Iced', 'Iced'],
    'sales': [320, 280, 350, 310, 150, 200, 180, 160, 90, 120],
    'quantity': [64, 56, 50, 62, 30, 40, 36, 22, 18, 24]
})

# Manual loop — tedious and error-prone
for cat in df['category'].unique():
    subset = df[df['category'] == cat]
    print(f"{cat}: sales={subset['sales'].sum()}, qty={subset['quantity'].sum()}")

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) The Solution: One Line with groupby

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: groupby basics (Difficulty ⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'drink': ['Latte', 'Americano', 'Mocha', 'Latte', 'Espresso',
              'Americano', 'Latte', 'Mocha', 'Espresso', 'Latte'],
    'category': ['Hot', 'Hot', 'Hot', 'Hot', 'Hot',
                 'Iced', 'Iced', 'Iced', 'Iced', 'Iced'],
    'sales': [320, 280, 350, 310, 150, 200, 180, 160, 90, 120],
    'quantity': [64, 56, 50, 62, 30, 40, 36, 22, 18, 24]
})

# One line replaces the entire loop!
result = df.groupby('category')['sales'].sum()
print(result)
# category
# Hot     1410
# Iced     750

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

3. The split-apply-combine Principle

(1) Mermaid Diagram of the Grouping Process

graph TB
    subgraph Split["① Split — Split by category"]
        S1["Hot: Latte 320<br>Americano 280<br>Mocha 350<br>Latte 310<br>Espresso 150"]
        S2["Iced: Americano 200<br>Latte 180<br>Mocha 160<br>Espresso 90<br>Latte 120"]
    end
    subgraph Apply["② Apply — Sum each group"]
        A1["Hot → 320+280+350<br>+310+150 = 1410"]
        A2["Iced → 200+180+160<br>+90+120 = 750"]
    end
    subgraph Combine["③ Combine — Merge results"]
        C1["category | sales<br>Hot      | 1410<br>Iced     | 750"]
    end
    Split --> Apply --> Combine

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) The GroupBy Object Is Lazy

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: GroupBy lazy evaluation (Difficulty ⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'category': ['Hot', 'Hot', 'Iced', 'Iced'],
    'sales': [320, 280, 200, 180]
})

# groupby returns a GroupBy object — no computation yet
grouped = df.groupby('category')
print(type(grouped))  # <class 'pandas.core.groupby.DataFrameGroupBy'>

# Computation happens when you call an aggregation
print(grouped['sales'].sum())

# Inspect groups
print(grouped.groups)  # {'Hot': [0, 1], 'Iced': [2, 3]}
print(grouped.ngroups)  # 2

# Iterate over groups (rarely needed)
for name, group in grouped:
    print(f"Group: {name}")
    print(group)

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

4. Common Aggregation Functions

(1) Built-in Aggregations

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Built-in aggregation functions (Difficulty ⭐⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'category': ['Electronics', 'Electronics', 'Clothing', 'Clothing', 'Home', 'Home'],
    'product': ['Laptop', 'Phone', 'Shirt', 'Pants', 'Lamp', 'Chair'],
    'price': [999, 699, 45, 65, 30, 120],
    'stock': [50, 120, 200, 180, 300, 80],
    'rating': [4.7, 4.5, 4.2, 4.0, 3.8, 4.3]
})

# Single aggregation per column
print(df.groupby('category')['price'].mean())
# Electronics    849.0
# Clothing        55.0
# Home            75.0

# Multiple columns, same aggregation
print(df.groupby('category')[['price', 'stock']].sum())

# Common aggregation methods:
# .sum() .mean() .median() .min() .max()
# .count() .size() .std() .var() .first() .last()
# .nunique() .idxmax() .idxmin()

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) size vs count

Method	What It Computes	NaN Handling
size	Number of rows per group (includes NaN)	Includes NaN
count	Number of non-NaN values per group	Excludes NaN

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: size vs count (Difficulty ⭐)

PYTHON

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'category': ['A', 'A', 'B', 'B', 'A'],
    'value': [1, np.nan, 3, 4, 5]
})

print(df.groupby('category').size())
# category
# A    3  ← 3 rows (including NaN row)
# B    2

print(df.groupby('category').count())
#           value
# category
# A            2  ← 2 non-NaN values
# B            2

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

5. Multiple Aggregations with agg

(1) Multiple Aggregation Functions

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Multiple aggregations with agg (Difficulty ⭐⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'category': ['Electronics', 'Electronics', 'Clothing', 'Clothing', 'Home', 'Home'],
    'product': ['Laptop', 'Phone', 'Shirt', 'Pants', 'Lamp', 'Chair'],
    'price': [999, 699, 45, 65, 30, 120],
    'stock': [50, 120, 200, 180, 300, 80],
    'rating': [4.7, 4.5, 4.2, 4.0, 3.8, 4.3]
})

# Multiple aggregations on one column
print(df.groupby('category')['price'].agg(['mean', 'min', 'max']))
#              mean  min   max
# category
# Clothing     55.0   45    65
# Electronics 849.0  699   999
# Home         75.0   30   120

# Different aggregations per column
print(df.groupby('category').agg({
    'price': ['mean', 'max'],
    'stock': 'sum',
    'rating': 'mean'
}))

# Named aggregations (cleaner column names)
result = df.groupby('category').agg(
    avg_price=('price', 'mean'),
    max_price=('price', 'max'),
    total_stock=('stock', 'sum'),
    avg_rating=('rating', 'mean')
)
print(result)

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) Custom Aggregation Functions

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Custom aggregation (Difficulty ⭐⭐⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'category': ['A', 'A', 'A', 'B', 'B', 'B'],
    'value': [10, 20, 30, 100, 200, 300]
})

# Custom function: range (max - min)
def value_range(series):
    return series.max() - series.min()

result = df.groupby('category')['value'].agg(['mean', value_range])
print(result)
#           mean  value_range
# category
# A         20.0           20
# B        200.0          200

# Lambda in agg (less readable but concise)
result2 = df.groupby('category')['value'].agg([
    ('mean', 'mean'),
    ('range', lambda x: x.max() - x.min()),
    ('cv', lambda x: x.std() / x.mean())  # coefficient of variation
])
print(result2)

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

6. Within-Group Transformation with transform

(1) transform Preserves the Original Shape

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Within-group standardization with transform (Difficulty ⭐⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'student': ['Alice', 'Alice', 'Bob', 'Bob', 'Charlie', 'Charlie'],
    'subject': ['Math', 'English', 'Math', 'English', 'Math', 'English'],
    'score': [85, 90, 92, 88, 78, 82]
})

# Z-score normalization per student
df['z_score'] = df.groupby('student')['score'].transform(
    lambda x: (x - x.mean()) / x.std()
)
print(df)

# Fill missing with group mean (instead of global mean)
df2 = pd.DataFrame({
    'category': ['A', 'A', 'A', 'B', 'B'],
    'value': [10, 20, None, 100, None]
})
df2['value_filled'] = df2.groupby('category')['value'].transform(
    lambda x: x.fillna(x.mean())
)
print(df2)

# Rank within group
df['rank'] = df.groupby('student')['score'].transform('rank', method='min')
print(df[['student', 'subject', 'score', 'rank']])

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

(2) transform vs agg Comparison

Feature	agg	transform
Return shape	One row per group	Same length as original DataFrame
Use case	Summary reports	Back-filling the original table
Typical operations	sum/mean/count	Standardization/filling/ranking
Broadcasting	❌	✅ Automatically broadcasts to original rows

7. Group-Level Filtering with filter

(1) Keep or Drop Groups by Condition

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Filtering groups with filter (Difficulty ⭐⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'category': ['Electronics', 'Electronics', 'Electronics',
                 'Books', 'Books',
                 'Clothing', 'Clothing', 'Clothing', 'Clothing'],
    'product': ['Laptop', 'Phone', 'Tablet', 'Novel', 'Textbook',
                'Shirt', 'Pants', 'Jacket', 'Socks'],
    'sales': [5000, 3000, 2000, 200, 300, 800, 600, 400, 100]
})

# Keep only groups where total sales > 1000
big_categories = df.groupby('category').filter(lambda x: x['sales'].sum() > 1000)
print(big_categories)
# Electronics and Clothing kept; Books dropped (200+300=500 < 1000)

# Keep groups with at least 3 items
large_groups = df.groupby('category').filter(lambda x: len(x) >= 3)
print(large_groups['category'].unique())  # ['Electronics', 'Clothing']

# Keep groups where any product has sales > 4000
has_top_seller = df.groupby('category').filter(lambda x: x['sales'].max() > 4000)
print(has_top_seller['category'].unique())  # ['Electronics']

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

8. Multi-Column Grouping and as_index

(1) Grouping by Multiple Columns

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Multi-column groupby aggregation (Difficulty ⭐⭐)

PYTHON

import pandas as pd

df = pd.DataFrame({
    'region': ['North', 'North', 'North', 'South', 'South', 'South'],
    'category': ['Electronics', 'Clothing', 'Electronics',
                 'Electronics', 'Clothing', 'Clothing'],
    'product': ['Laptop', 'Shirt', 'Phone', 'Tablet', 'Pants', 'Jacket'],
    'sales': [5000, 800, 3000, 2000, 600, 400]
})

# Group by multiple columns
result = df.groupby(['region', 'category'])['sales'].sum()
print(result)
# region  category
# North  Clothing       800
#        Electronics   8000
# South  Clothing      1000
#        Electronics   2000

# as_index=False → keeps group columns as regular columns
result2 = df.groupby(['region', 'category'], as_index=False)['sales'].sum()
print(result2)
#   region    category  sales
# 0  North    Clothing    800
# 1  North  Electronics  8000
# 2  South    Clothing   1000
# 3  South  Electronics  2000

# sort=False → preserve original order of first occurrence
result3 = df.groupby(['region', 'category'], sort=False)['sales'].sum()

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

9. Full Example: E-Commerce Multi-Dimensional Group Analysis

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

: Multi-dimensional groupby analysis (Difficulty ⭐⭐⭐)

PYTHON

import pandas as pd
import numpy as np

# ============================================
# Comprehensive example: E-commerce multi-dim
# groupby analysis with agg/transform/filter
# ============================================

# 1. Create sales data
np.random.seed(42)
df = pd.DataFrame({
    'region': np.random.choice(['North', 'South', 'East', 'West'], 100),
    'category': np.random.choice(['Electronics', 'Clothing', 'Home', 'Sports'], 100),
    'product': [f'Item_{i:03d}' for i in range(100)],
    'sales': np.round(np.random.uniform(50, 2000, 100), 2),
    'quantity': np.random.randint(1, 50, 100),
    'discount': np.random.choice([0, 0.1, 0.2, 0.3], 100)
})

# 2. agg: multi-metric summary per category
summary = df.groupby('category').agg(
    total_sales=('sales', 'sum'),
    avg_sales=('sales', 'mean'),
    max_sales=('sales', 'max'),
    order_count=('sales', 'count'),
    avg_quantity=('quantity', 'mean')
).round(2)
print("=== Category Summary ===")
print(summary)

# 3. Multi-column groupby
region_cat = df.groupby(['region', 'category'], as_index=False).agg(
    total_sales=('sales', 'sum'),
    avg_discount=('discount', 'mean')
).round(3)
print("\n=== Region × Category ===")
print(region_cat.head(8))

# 4. transform: within-group ranking
df['sales_rank_in_region'] = df.groupby('region')['sales'].transform(
    'rank', method='min', ascending=False
)
df['sales_pct_in_category'] = df.groupby('category')['sales'].transform(
    lambda x: (x / x.sum() * 100).round(1)
)
print("\n=== Top 3 in North ===")
print(df[df['region'] == 'North'].nsmallest(3, 'sales_rank_in_region')
      [['product', 'sales', 'sales_rank_in_region']])

# 5. filter: keep categories with > 20 orders
big_cats = df.groupby('category').filter(lambda x: len(x) > 20)
print(f"\n=== Big Categories ===")
print(big_cats['category'].value_counts())

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on your pandas version.

❓ FAQ

Q What does groupby return?

A groupby returns a GroupBy object (lazy) — no computation happens immediately. The split-apply-combine process only executes when you call an aggregation function (sum/mean/agg, etc.). This lazy design lets you chain multiple operations together without intermediate variables.

Q What is the difference between agg and apply?

A agg applies aggregation functions to each column and returns one row per group. apply applies an arbitrary function to each group and returns more flexible results (it can return a DataFrame). Use agg for simple aggregations (faster and more explicit); use apply for complex within-group computations. agg also supports named aggregation (name=(col, func)) for cleaner column names.

Q Does transform preserve the shape?

A Yes — transform returns a result with the same length as the original DataFrame, broadcasting each group's result back to the original rows. Typical use cases: within-group standardization (z-score), filling missing values with the group mean, and within-group ranking. An error is raised if the result length does not match the group size.

Q Does filter remove rows or groups?

A filter removes entire groups — if a group's aggregated result does not meet the condition, all rows in that group are dropped. It does not filter individual rows within a group! This differs from boolean indexing: boolean indexing filters by row-level conditions, while filter operates on group-level conditions.

Q What does as_index=False do?

A By default, groupby turns the grouping columns into the Index (not regular columns). as_index=False keeps the grouping columns as regular columns, producing a clean DataFrame instead of a Series with a MultiIndex. When you need to operate on the grouping columns afterward (e.g., in a merge), as_index=False is more convenient.

Q How do I read multi-column groupby results?

A Multi-column grouping produces a MultiIndex (hierarchical index). Use result.loc[('North', 'Electronics')] to select a specific group, or use as_index=False to avoid the hierarchical index altogether. reset_index() can also flatten a hierarchical index into regular columns.

Q Does groupby + sort slow things down?

A sort=True (the default) sorts the grouping keys, adding a small overhead. For large DataFrames with high-cardinality grouping keys, sort=False can speed things up. However, sorted results are more readable. Recommendation: use sort=True for small datasets (readability first), and sort=False for very large datasets (performance first).

📖 Summary

The core of groupby is split-apply-combine: split, compute, merge
The GroupBy object uses lazy evaluation — computation only happens when an aggregation function is called
Built-in aggregations: sum/mean/count/size/min/max/std/nunique, etc.
agg supports multiple aggregations and different aggregations per column; named aggregation syntax is the cleanest
transform returns a same-length result, ideal for standardization/filling/ranking
filter selects by group-level conditions, keeping or dropping entire groups
Multi-column grouping produces a MultiIndex; use as_index=False to avoid hierarchical indexing

📝 Exercises

Basic (Difficulty ⭐): Create a sales DataFrame (region/category/sales). Use groupby + sum/mean/count for three different aggregations and compare the results.
Intermediate (Difficulty ⭐⭐): Create a student grades table. Use agg to compute mean/max/min for each subject, and use transform to calculate z-score normalized grades for each student.
Challenge (Difficulty ⭐⭐⭐): Simulate 50 rows of e-commerce orders (region/category/sales/quantity/discount). Complete the following pipeline: agg multi-metric summary → multi-column groupby (region+category) → transform within-group ranking → filter to keep large groups → produce a comprehensive report.

← Previous: Data Transformation · Next: Merge and Join →

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Pandas: GroupBy Aggregation

1. What You Will Learn

2. Alice Tallies Coffee Shop Sales by Category

(1) The Pain Point: Manual Looping Is Tedious

(2) The Solution: One Line with groupby

▶ Example

3. The split-apply-combine Principle

(1) Mermaid Diagram of the Grouping Process

(2) The GroupBy Object Is Lazy

▶ Example

4. Common Aggregation Functions

(1) Built-in Aggregations

▶ Example

(2) size vs count

▶ Example

5. Multiple Aggregations with agg

(1) Multiple Aggregation Functions

▶ Example

(2) Custom Aggregation Functions

▶ Example

6. Within-Group Transformation with transform

(1) transform Preserves the Original Shape

▶ Example

(2) transform vs agg Comparison

7. Group-Level Filtering with filter

(1) Keep or Drop Groups by Condition

▶ Example

8. Multi-Column Grouping and as_index

(1) Grouping by Multiple Columns

▶ Example

9. Full Example: E-Commerce Multi-Dimensional Group Analysis

▶ Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好