Pandas Aggregation and GroupBy

Real-World Scenario

A business analyst needs to produce a monthly sales report: total and average revenue by region and product category, month-over-month growth rates, and ranking of top 10 customers per country. Every one of these is a groupby + aggregation problem. Mastering this pattern is the single highest-leverage Pandas skill for analytics work.

Basic GroupBy

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
n = 1000

df = pd.DataFrame({
    "date":     pd.date_range("2024-01-01", periods=n, freq="h"),
    "region":   rng.choice(["North", "South", "East", "West"], n),
    "product":  rng.choice(["Widget", "Gadget", "Gizmo"], n),
    "revenue":  rng.uniform(100, 5000, n).round(2),
    "quantity": rng.integers(1, 50, n),
    "rep_id":   rng.integers(1, 20, n),
})

# GroupBy a single column and compute one aggregation
region_revenue = df.groupby("region")["revenue"].sum()
print(region_revenue)
# East     126403.21
# North    124811.44
# South    127200.33
# West     125892.55

# Sort results
print(region_revenue.sort_values(ascending=False))

# Count rows per group
print(df.groupby("region").size())

# GroupBy multiple columns
region_product = df.groupby(["region", "product"])["revenue"].sum()
print(region_product)
print(region_product.unstack())   # pivot to wide format: regions × products

Multiple Aggregations with agg

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
df = pd.DataFrame({
    "dept":   rng.choice(["Eng", "Sales", "Marketing"], 500),
    "salary": rng.normal(80000, 20000, 500).clip(30000, 200000),
    "years":  rng.integers(1, 20, 500),
    "rating": rng.uniform(1, 5, 500).round(1),
})

# Multiple aggregations per column
summary = df.groupby("dept").agg(
    headcount   = ("salary", "count"),
    total_salary= ("salary", "sum"),
    avg_salary  = ("salary", "mean"),
    median_salary=("salary", "median"),
    max_salary  = ("salary", "max"),
    avg_years   = ("years", "mean"),
    avg_rating  = ("rating", "mean"),
)

print(summary.round(0))

# Custom aggregation function
def salary_range(x):
    return x.max() - x.min()

custom_agg = df.groupby("dept")["salary"].agg([
    "mean",
    "std",
    ("range", salary_range),
    ("p90", lambda x: x.quantile(0.9)),
])
print(custom_agg.round(0))

transform — Group-Wise Values in Original Shape

transform is essential for feature engineering: add a column to the original DataFrame that contains group-level statistics.

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
df = pd.DataFrame({
    "dept":   rng.choice(["Eng", "Sales", "Marketing"], 100),
    "salary": rng.normal(80000, 20000, 100).clip(30000, 200000),
})

# Add department mean and std to original DataFrame (same row count)
df["dept_avg_salary"]   = df.groupby("dept")["salary"].transform("mean")
df["dept_std_salary"]   = df.groupby("dept")["salary"].transform("std")

# Z-score within department — normalized relative to peers
df["salary_zscore"] = (
    (df["salary"] - df["dept_avg_salary"]) / df["dept_std_salary"]
)

# Rank within group — 1 = highest earner in their dept
df["rank_in_dept"] = df.groupby("dept")["salary"].rank(
    method="dense", ascending=False
)

print(df.sort_values(["dept", "rank_in_dept"]).head(10))

Pivot Tables

Pivot tables reshape data from long to wide format with aggregation — the same as Excel’s pivot table.

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
sales = pd.DataFrame({
    "quarter": rng.choice(["Q1", "Q2", "Q3", "Q4"], 400),
    "region":  rng.choice(["North", "South", "East", "West"], 400),
    "product": rng.choice(["Widget", "Gadget", "Gizmo"], 400),
    "revenue": rng.uniform(1000, 10000, 400).round(0),
})

# Pivot: rows=region, columns=quarter, values=revenue (sum)
pivot = sales.pivot_table(
    values="revenue",
    index="region",
    columns="quarter",
    aggfunc="sum",
    fill_value=0,
    margins=True,      # add row/column totals
    margins_name="Total",
)
print(pivot.astype(int))

# Multi-level pivot: multiple value columns
pivot2 = sales.pivot_table(
    values="revenue",
    index=["region", "product"],
    columns="quarter",
    aggfunc=["sum", "mean"],
    fill_value=0,
)
print(pivot2.head())

Rolling and Expanding Aggregations

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
dates = pd.date_range("2024-01-01", periods=90, freq="D")
df = pd.DataFrame({
    "date":    dates,
    "revenue": rng.uniform(5000, 20000, 90).round(0),
}).set_index("date")

# 7-day rolling mean (smoothed trend)
df["revenue_7d_avg"] = df["revenue"].rolling(window=7).mean()

# 30-day rolling std (volatility)
df["revenue_30d_std"] = df["revenue"].rolling(window=30).std()

# Expanding mean — mean from start of series to current row
df["revenue_cumulative_avg"] = df["revenue"].expanding().mean()

# Percentage change (day-over-day growth)
df["revenue_pct_change"] = df["revenue"].pct_change() * 100

print(df.head(10).round(1))

Real-World: Customer RFM Analysis

RFM (Recency, Frequency, Monetary) is a standard customer segmentation technique built entirely on groupby.

import pandas as pd
import numpy as np
from datetime import datetime

rng = np.random.default_rng(42)
n = 10000

df = pd.DataFrame({
    "customer_id": rng.integers(1, 500, n),
    "order_date":  pd.to_datetime(
        rng.integers(
            int(pd.Timestamp("2023-01-01").timestamp()),
            int(pd.Timestamp("2024-12-31").timestamp()),
            n
        ), unit="s"
    ),
    "order_value": rng.exponential(150, n).round(2) + 10,
})

snapshot_date = pd.Timestamp("2025-01-01")

rfm = df.groupby("customer_id").agg(
    recency   = ("order_date",  lambda x: (snapshot_date - x.max()).days),
    frequency = ("order_date",  "count"),
    monetary  = ("order_value", "sum"),
).reset_index()

# Score 1–5 on each dimension (5 = best)
rfm["R_score"] = pd.qcut(rfm["recency"],   5, labels=[5, 4, 3, 2, 1])
rfm["F_score"] = pd.qcut(rfm["frequency"].rank(method="first"), 5, labels=[1, 2, 3, 4, 5])
rfm["M_score"] = pd.qcut(rfm["monetary"],  5, labels=[1, 2, 3, 4, 5])

rfm["RFM_score"] = (
    rfm["R_score"].astype(int) +
    rfm["F_score"].astype(int) +
    rfm["M_score"].astype(int)
)

# Segment customers
def segment(score):
    if score >= 13: return "Champions"
    if score >= 10: return "Loyal"
    if score >= 7:  return "At Risk"
    return "Inactive"

rfm["segment"] = rfm["RFM_score"].apply(segment)
print(rfm["segment"].value_counts())
print(rfm.groupby("segment")[["recency", "frequency", "monetary"]].mean().round(1))