Grouping and Aggregating in pandas: groupby as the PivotTable Replacement

Introduction

You know that moment in Excel when you've got a flat table of transaction data and your manager asks, "What were total sales by region, broken down by product category, for each quarter?" You instinctively reach for PivotTable. A few drags and drops later, you've got a beautiful summary. It feels like magic.

In pandas, that magic is called groupby. And once you understand it deeply — not just the syntax, but the mechanics underneath — you'll find that groupby is actually more powerful than Excel's PivotTable, more composable than SQL's GROUP BY, and capable of transforming complex multi-dimensional aggregation problems into clean, readable code. The trade-off is that you have to understand what's happening under the hood, or you'll run into errors and performance cliffs that will leave you confused.

This lesson is a complete deep dive. By the end, you'll be able to replace any PivotTable you've ever built, handle multi-level grouping, write custom aggregation functions, reshape grouped results into exactly the format your reports need, and understand the performance implications of every choice you make. We're going to use a realistic retail sales dataset throughout, so everything you learn here maps directly to real analytical work.

What you'll learn:

• How groupby works internally — the split-apply-combine model and what it means for your code
• Single and multi-key grouping with multiple aggregation functions in one pass
• agg(), transform(), and apply() — what each is for and when to use which
• How to reshape grouped output: unstacking, pivoting, and renaming columns
• Performance optimization: avoiding anti-patterns that silently destroy speed at scale
• Advanced patterns: conditional aggregations, rolling group statistics, and custom functions

---

Prerequisites

You should be comfortable loading data with pandas and understand DataFrame fundamentals. If you're coming from Excel or SQL and are new to pandas, start with Your First pandas DataFrame: Loading CSV and Excel Files and Exploring Data first. You should also understand how to filter DataFrames — specifically boolean indexing — because groupby and filtering interact in important ways. If that's fuzzy, review Selecting and Filtering Data in pandas: loc, iloc, and Boolean Masks before continuing.

You'll need pandas 1.5+ and NumPy installed. The examples here work through pandas 2.x.

---

The Split-Apply-Combine Model

Before you write a single line of groupby code, you need to understand what it's actually doing. Every groupby operation follows the same three-step mental model, famously described by Hadley Wickham in the context of R, but equally applicable here.

Split: pandas divides your DataFrame into groups based on the key(s) you specify. If you group by region, every row with region == "West" ends up in one bucket, every "East" row in another, and so on. Nothing is computed yet — you're just partitioning the data.

Apply: Some function runs on each partition independently. That function could be a simple aggregation like sum(), a transformation that returns a same-size result, or a completely arbitrary function via apply().

Combine: The results from each group are reassembled into a single output object.

This model is important because it tells you why certain operations are fast and others are slow. pandas implements the split step using hash-based grouping (similar to a hash join in a database), which means it runs in O(n) time regardless of how many unique group keys you have. The bottleneck is almost always the apply step, which is why choosing the right function — NumPy-native vs. Python-level — matters so much at scale.

Let's build our working dataset. We'll simulate a retail company's transaction log:

import pandas as pd
import numpy as np

np.random.seed(42)

n = 10_000

df = pd.DataFrame({
    "transaction_id": range(1, n + 1),
    "date": pd.date_range("2023-01-01", periods=n, freq="H"),
    "region": np.random.choice(["North", "South", "East", "West"], size=n),
    "rep_id": np.random.choice([f"REP{i:03d}" for i in range(1, 21)], size=n),
    "category": np.random.choice(["Electronics", "Apparel", "Home", "Sports", "Food"], size=n),
    "product": np.random.choice([f"PROD{i:04d}" for i in range(1, 101)], size=n),
    "units": np.random.randint(1, 50, size=n),
    "unit_price": np.round(np.random.uniform(5.0, 500.0, size=n), 2),
    "discount_pct": np.random.choice([0, 5, 10, 15, 20], size=n, p=[0.5, 0.2, 0.15, 0.1, 0.05]),
    "returned": np.random.choice([True, False], size=n, p=[0.08, 0.92]),
})

df["revenue"] = np.round(df["units"] * df["unit_price"] * (1 - df["discount_pct"] / 100), 2)
df["quarter"] = df["date"].dt.to_period("Q").astype(str)
df["month"] = df["date"].dt.to_period("M").astype(str)

print(df.shape)
print(df.dtypes)
print(df.head(3))

This gives us 10,000 transactions across four regions, five categories, twenty sales reps, and a full year of data. Realistic enough that every technique we cover will feel grounded.

---

Your First groupby: Single Key, Single Aggregation

Let's start simple and build up. The most direct PivotTable equivalent: total revenue by region.

# In Excel: drag "region" to Rows, "revenue" to Values (Sum)
revenue_by_region = df.groupby("region")["revenue"].sum()
print(revenue_by_region)

Output:

region
East     6234871.45
North    6189043.22
South    6102334.78
West     6097834.11
Name: revenue, dtype: float64

What you get back is a Series with the group keys as the index. This is important: the result of a groupby aggregation is indexed by your group keys. Excel hides this — it just shows you a table. pandas makes the index explicit, which is more powerful but requires you to understand it.

If you want a DataFrame instead:

# Two ways to get a DataFrame result
revenue_by_region_df = df.groupby("region")[["revenue"]].sum()  # double brackets → DataFrame
print(type(revenue_by_region_df))  # <class 'pandas.core.frame.DataFrame'>

The double brackets [["revenue"]] select a DataFrame (single-column) rather than a Series. This is a pandas idiom you'll use constantly.

You can also reset the index to get a flat table format — closer to what Excel would show:

revenue_by_region_flat = (
    df.groupby("region")["revenue"]
    .sum()
    .reset_index()
    .sort_values("revenue", ascending=False)
)
print(revenue_by_region_flat)

  region      revenue
0   East  6234871.45
3   West  6097834.11
2  South  6102334.78
1  North  6189043.22

---

Multiple Aggregations: The agg() Method

In Excel's PivotTable you can add the same field multiple times and set each to a different summary function — Sum, Average, Count, Max. The pandas equivalent is agg(), and it's substantially more flexible.

Named Aggregations (the Right Way)

summary = df.groupby("region")["revenue"].agg(
    total_revenue="sum",
    avg_revenue="mean",
    transaction_count="count",
    max_transaction="max",
    std_revenue="std"
)
print(summary)

        total_revenue  avg_revenue  transaction_count  max_transaction  std_revenue
region
East      6234871.45  2481.34       2512              24831.20         2104.33
North     6189043.22  2488.12       2487              24917.55         2099.87
South     6102334.78  2456.22       2485              24804.11         2108.44
West      6097834.11  2471.93       2516              24795.33         2101.72

This named aggregation syntax (column_name="function") was introduced in pandas 0.25 and is the preferred approach. It gives your output columns clean, meaningful names immediately, without requiring a rename step afterward.

Aggregating Multiple Columns at Once

Real analyses rarely aggregate just one column. Here's how you aggregate several columns with different functions per column:

multi_col_summary = df.groupby("region").agg(
    total_revenue=("revenue", "sum"),
    avg_units=("units", "mean"),
    total_units=("units", "sum"),
    avg_discount=("discount_pct", "mean"),
    return_count=("returned", "sum"),   # True counts as 1
    transaction_count=("transaction_id", "count")
)

# Compute a derived metric after the fact
multi_col_summary["return_rate"] = (
    multi_col_summary["return_count"] / multi_col_summary["transaction_count"]
).round(4)

print(multi_col_summary)

This is the pattern you'll use for 80% of your real reporting work: group by dimension, aggregate multiple metrics in one call, compute derived metrics on the result.

Custom Functions in agg()

You're not limited to built-in functions. Any callable that takes a Series and returns a scalar works:

def coefficient_of_variation(series):
    """CV = std/mean — a normalized measure of variability."""
    return series.std() / series.mean()

def pct_above_median(series):
    """What fraction of values exceed the overall median?"""
    return (series > series.median()).mean()

cv_summary = df.groupby("category")["revenue"].agg(
    mean_revenue="mean",
    cv=coefficient_of_variation,
    pct_above_median=pct_above_median
)
print(cv_summary.round(4))

---

Multi-Key Grouping: The True PivotTable Equivalent

Single-key grouping is useful, but the real power comes from grouping by multiple dimensions simultaneously. This is the pandas equivalent of dropping multiple fields into the Rows and Columns areas of a PivotTable.

# Revenue by region AND category
region_category = df.groupby(["region", "category"])["revenue"].sum()
print(region_category)

region  category
East    Apparel      1234567.89
        Electronics  1456789.01
        Food          987654.32
        Home         1102345.67
        Sports        453514.56
North   Apparel      ...
...

The result is a Series with a two-level MultiIndex. This is pandas' way of representing hierarchical structure. If you want to work with it as a flat table, reset_index() again:

region_category_flat = (
    df.groupby(["region", "category"])["revenue"]
    .sum()
    .reset_index()
)

Pivoting the Result: Recreating a Classic PivotTable Layout

Most PivotTables have one dimension on rows and another on columns. The unstack() method rotates the innermost index level into columns:

pivot_layout = (
    df.groupby(["region", "category"])["revenue"]
    .sum()
    .unstack(level="category")  # or unstack(-1) for the last level
    .fillna(0)
    .round(2)
)

print(pivot_layout)

category   Apparel  Electronics     Food     Home  Sports
region
East      1234567    1456789    987654  1102345  453514
North     1198234    1412345   1003456  1087654  487354
South     1189234    1423456    987654  1067234  434756
West      1212345    1445678   1001234  1078456  360121

This is visually identical to what Excel's PivotTable produces. You have regions on the rows, categories as columns, and revenue values in the cells.

You can go further and add column totals and row totals, just like Excel's "Grand Total" feature:

pivot_with_totals = pivot_layout.copy()
pivot_with_totals["Total"] = pivot_layout.sum(axis=1)
totals_row = pivot_layout.sum(axis=0)
totals_row.name = "Total"
pivot_with_totals = pd.concat([pivot_with_totals, totals_row.to_frame().T])
print(pivot_with_totals)

---

Three Tools, Three Jobs: agg(), transform(), and apply()

This is where most pandas learners get confused. There are three methods you can call on a GroupBy object, and they do fundamentally different things. Choosing the wrong one is a common source of bugs.

agg(): Reduces to One Row Per Group

You've already seen this. agg() takes a group, runs a function, and returns a scalar. The result has one row per group. Use it for summary tables.

transform(): Returns Same Shape as Input

transform() runs a function on each group but returns a result that is the same length as the original DataFrame, with each row getting the value corresponding to its group. This is incredibly useful for computing within-group statistics that you want to add back as new columns.

Classic use case: computing each transaction's percentage of its region's total revenue.

# Without transform, you'd have to merge the group summary back in
df["region_total_revenue"] = df.groupby("region")["revenue"].transform("sum")
df["pct_of_region"] = (df["revenue"] / df["region_total_revenue"] * 100).round(2)

print(df[["transaction_id", "region", "revenue", "region_total_revenue", "pct_of_region"]].head(8))

Compare this to the clunky alternative:

# The wrong way: merge back in
region_totals = df.groupby("region")["revenue"].sum().reset_index()
region_totals.columns = ["region", "region_total_revenue"]
df_merged = df.merge(region_totals, on="region")  # works, but slower and more code

transform() does this in one line and doesn't require a merge. Under the hood, it aligns the group results back to the original index automatically.

Other powerful uses of transform():

# Z-score within each category (normalize by group)
df["revenue_zscore_in_category"] = (
    df.groupby("category")["revenue"]
    .transform(lambda x: (x - x.mean()) / x.std())
)

# Rank within region (1 = highest revenue transaction in that region)
df["rank_in_region"] = (
    df.groupby("region")["revenue"]
    .transform(lambda x: x.rank(method="dense", ascending=False))
)

# Fill missing values with group mean (extremely common in data cleaning)
df["revenue_filled"] = (
    df.groupby("category")["revenue"]
    .transform(lambda x: x.fillna(x.mean()))
)

apply(): The Swiss Army Knife (Use Sparingly)

apply() is the escape hatch. It receives an entire group as a DataFrame (or Series), and you can return anything from it: a scalar, a Series, a DataFrame, even a ragged structure. This power comes at a cost: apply() is almost always slower than agg() or transform(), sometimes by an order of magnitude, because it can't use pandas' internal C-level optimizations.

Use apply() when neither agg() nor transform() can express what you need:

def top_products_by_revenue(group_df):
    """Return the top 3 products by revenue within this group."""
    return (
        group_df.groupby("product")["revenue"]
        .sum()
        .nlargest(3)
        .reset_index()
    )

top_products = df.groupby("region").apply(top_products_by_revenue)
print(top_products.head(12))

# Another apply use case: custom percentile summaries
def revenue_percentiles(group):
    return pd.Series({
        "p25": group["revenue"].quantile(0.25),
        "p50": group["revenue"].quantile(0.50),
        "p75": group["revenue"].quantile(0.75),
        "p90": group["revenue"].quantile(0.90),
        "p99": group["revenue"].quantile(0.99),
    })

percentile_summary = df.groupby("category").apply(revenue_percentiles)
print(percentile_summary)

---

Grouping by Time: Period and Resample

Time-series grouping is a special case that comes up constantly in business analytics. You want revenue by month, by quarter, by week. pandas has two approaches: using groupby() with a time-derived column, and using resample() which is purpose-built for time-series.

Groupby with Time Columns

# Monthly revenue by category — using string period column we created
monthly_category = (
    df.groupby(["month", "category"])["revenue"]
    .sum()
    .reset_index()
    .sort_values(["month", "revenue"], ascending=[True, False])
)

print(monthly_category.head(10))

This works fine when you've already extracted the period as a string or categorical column. The advantage is it behaves exactly like any other groupby.

Resample for Time-Series DataFrames

If your DataFrame is indexed by a DatetimeIndex (which it often should be for time-series data), resample() is the cleaner tool:

# Set date as the index for resampling
df_ts = df.set_index("date").sort_index()

# Monthly totals
monthly_totals = df_ts["revenue"].resample("ME").sum()  # "ME" = Month End
print(monthly_totals.head(6))

# Quarterly revenue by region — combining resample with groupby
quarterly_region = (
    df_ts.groupby("region")["revenue"]
    .resample("QE")
    .sum()
    .reset_index()
)
print(quarterly_region.head(8))

---

Filtering Groups: groupby + filter()

Sometimes you don't want to aggregate at all — you want to filter out entire groups based on group-level criteria. This is something Excel PivotTables can't do gracefully, but pandas handles it elegantly.

# Keep only sales reps with more than 600 transactions
active_reps = df.groupby("rep_id").filter(lambda x: len(x) > 600)
print(f"Original: {len(df)} rows, Filtered: {len(active_reps)} rows")
print(active_reps["rep_id"].nunique(), "reps remain")

# Keep only categories where the average revenue exceeds a threshold
high_value_categories = df.groupby("category").filter(
    lambda x: x["revenue"].mean() > 2400
)
print(high_value_categories["category"].unique())

# Remove groups that have any returned transactions
# (example of a cross-row condition that's hard to express otherwise)
no_return_regions = df.groupby("region").filter(
    lambda x: x["returned"].sum() == 0
)

The filter() method returns the original rows (not a summary) for groups that pass the condition. It's the correct tool when your question is "which rows belong to groups that satisfy some condition?"

---

Named Groups and Categorical Grouping

groupby with Categorical Dtype

If your grouping column is a categorical dtype, pandas handles it differently: it preserves all category levels in the output even if some groups are empty. This is important when you're building reports that need consistent structure regardless of what's in the data.

# Convert category to categorical dtype
df["category"] = pd.Categorical(
    df["category"],
    categories=["Electronics", "Apparel", "Home", "Sports", "Food"],
    ordered=False
)

# With observed=False, empty groups are included
summary_with_empties = df.groupby("category", observed=False)["revenue"].sum()
print(summary_with_empties)  # All 5 categories appear, even if some have 0 revenue

In pandas 2.0, observed=True became the default for groupby with Categorical columns (previously it was False). If you relied on the old behavior of including empty categories, you need to explicitly pass observed=False. You'll see a FutureWarning if you're on 1.5.x without specifying it — always be explicit.

Using pd.Grouper for Flexible Time Grouping

pd.Grouper lets you mix time-frequency grouping with categorical grouping cleanly:

df_ts = df.set_index("date")

quarterly_by_region = (
    df_ts.groupby([pd.Grouper(freq="QE"), "region"])["revenue"]
    .sum()
    .reset_index()
)

quarterly_by_region.columns = ["quarter_end", "region", "total_revenue"]
print(quarterly_by_region.sort_values(["quarter_end", "region"]))

This is the clean way to do "revenue by quarter by region" without pre-computing a quarter column.

---

Performance Optimization: Making groupby Fast at Scale

At 10,000 rows, everything is fast. At 10 million rows, wrong choices will cost you minutes. Here's what actually matters.

Use Built-in Functions, Not Lambda Expressions

The single biggest performance lever is choosing built-in aggregation functions over Python lambdas.

import time

# Fast: built-in function name as string
start = time.time()
for _ in range(100):
    df.groupby("region")["revenue"].sum()
print(f"Built-in: {time.time() - start:.3f}s")

# Slower: lambda that does the same thing
start = time.time()
for _ in range(100):
    df.groupby("region")["revenue"].agg(lambda x: x.sum())
print(f"Lambda: {time.time() - start:.3f}s")

On a typical machine with 10,000 rows, you'll see the lambda version run 3-10x slower. At scale, this gap widens. Built-in functions like "sum", "mean", "count", "min", "max", "std", "var" are implemented in C and bypass Python's overhead.

Use Categorical Columns for High-Cardinality Grouping Keys

If you're grouping by a string column with many repeated values (like region, category, rep_id), converting to categorical dtype before grouping can significantly reduce memory usage and sometimes improve speed:

# Convert string columns to categorical before heavy groupby operations
for col in ["region", "category", "rep_id"]:
    df[col] = df[col].astype("category")

# Now groupby operations use integer codes internally, not string comparisons
summary = df.groupby(["region", "category"])["revenue"].sum()

The benefit is proportional to the ratio of rows to unique values. If you have 10 million rows and 5 categories, converting to categorical turns all the string comparisons into integer comparisons — a massive speedup.

Avoid apply() When You Can

We already mentioned this, but it deserves a benchmark:

import timeit

# Simulate a larger dataset
large_df = pd.concat([df] * 100)  # 1 million rows

# The wrong way for a simple percentile
t1 = timeit.timeit(
    lambda: large_df.groupby("region")["revenue"].apply(lambda x: x.quantile(0.9)),
    number=5
)

# The right way
t2 = timeit.timeit(
    lambda: large_df.groupby("region")["revenue"].quantile(0.9),
    number=5
)

print(f"apply: {t1:.2f}s | quantile: {t2:.2f}s | speedup: {t1/t2:.1f}x")

The built-in quantile() on a GroupBy object is often 5-20x faster than an equivalent apply(lambda x: x.quantile(...)).

sort=False for Performance When Order Doesn't Matter

By default, groupby() sorts the groups by key. If you don't need sorted output, disabling this saves time:

# If you don't need alphabetical/sorted group order
summary = df.groupby("region", sort=False)["revenue"].sum()

On datasets with many unique keys, sort=False can be 20-30% faster because it skips the final sort step.

---

Advanced Patterns

Conditional Aggregation

SQL has SUM(CASE WHEN condition THEN value END) for conditional aggregation. pandas achieves this by creating boolean mask columns or using np.where() before aggregating:

# What's the revenue from non-returned transactions, by region?
df["revenue_non_returned"] = df["revenue"].where(~df["returned"], 0)

# What's the revenue from Electronics only, within each region?
df["electronics_revenue"] = df["revenue"].where(
    df["category"] == "Electronics", 0
)

conditional_summary = df.groupby("region").agg(
    total_revenue=("revenue", "sum"),
    non_returned_revenue=("revenue_non_returned", "sum"),
    electronics_revenue=("electronics_revenue", "sum"),
)

conditional_summary["return_revenue_loss"] = (
    conditional_summary["total_revenue"] - conditional_summary["non_returned_revenue"]
)

print(conditional_summary.round(2))

This is equivalent to complex SQL like:

SELECT
    region,
    SUM(revenue) AS total_revenue,
    SUM(CASE WHEN returned = FALSE THEN revenue ELSE 0 END) AS non_returned_revenue,
    SUM(CASE WHEN category = 'Electronics' THEN revenue ELSE 0 END) AS electronics_revenue
FROM transactions
GROUP BY region

Cumulative and Rolling Statistics Within Groups

A common analytical need is computing running totals or rolling averages within groups — so the cumulative sum resets at each new group boundary. transform() combined with cumsum() handles this:

# Sort first — cumulative operations are order-dependent
df_sorted = df.sort_values(["rep_id", "date"]).copy()

# Cumulative revenue per rep (resets per rep)
df_sorted["rep_cumulative_revenue"] = (
    df_sorted.groupby("rep_id")["revenue"].transform("cumsum")
)

# Rolling 5-transaction average revenue per rep
df_sorted["rep_rolling_avg_5"] = (
    df_sorted.groupby("rep_id")["revenue"]
    .transform(lambda x: x.rolling(window=5, min_periods=1).mean())
)

print(df_sorted[["rep_id", "date", "revenue", "rep_cumulative_revenue", "rep_rolling_avg_5"]].head(15))

Groupby with Multiple Output Levels: Stacking and Unstacking

When you have a three-dimensional aggregation — say, region × category × quarter — you end up with a three-level MultiIndex. Reshaping this into a readable format requires understanding stack() and unstack():

# Three-way grouping
three_way = (
    df.groupby(["region", "category", "quarter"])["revenue"]
    .sum()
)

print(three_way.index.nlevels)  # 3

# Unstack quarter to columns → region × category with quarters as columns
pivot_quarters = three_way.unstack("quarter").fillna(0)
print(pivot_quarters.head(10))

# Unstack both category and quarter
pivot_flat = three_way.unstack(["category", "quarter"]).fillna(0)
print(pivot_flat.shape)  # rows=4 (regions), columns=5×4=20 (category×quarter combos)

The unstack() method is your friend for any time you need to rotate an index level into columns. You can unstack multiple levels at once, and the resulting MultiIndex on columns can be collapsed with columns = ['_'.join(col) for col in df.columns] if you need flat column names for export.

Detecting and Handling NaN in Group Keys

What happens when your grouping column has NaN values? By default, pandas excludes NaN keys from the result — they're simply dropped:

df_with_nulls = df.copy()
df_with_nulls.loc[df_with_nulls["region"] == "North", "region"] = np.nan

result = df_with_nulls.groupby("region")["revenue"].sum()
print(result)  # "North" rows are completely excluded

If you want NaN keys included in the result, use dropna=False:

result_with_nan = df_with_nulls.groupby("region", dropna=False)["revenue"].sum()
print(result_with_nan)  # NaN appears as a group with all the "North" revenue

This is important in data quality work: if you're auditing nulls, dropna=False lets you see how much data you'd lose by excluding them.

---

Hands-On Exercise

Work through this sequence of tasks on the dataset we built. Each task builds on the previous one.

Setup:

import pandas as pd
import numpy as np

np.random.seed(99)
n = 5000

df = pd.DataFrame({
    "date": pd.date_range("2023-01-01", periods=n, freq="4H"),
    "store": np.random.choice(["Store_A", "Store_B", "Store_C", "Store_D"], n),
    "category": np.random.choice(["Electronics", "Apparel", "Grocery"], n),
    "salesperson": np.random.choice([f"SP{i:02d}" for i in range(1, 11)], n),
    "units_sold": np.random.randint(1, 30, n),
    "unit_price": np.round(np.random.uniform(10, 300, n), 2),
    "discount_pct": np.random.choice([0, 10, 20, 30], n, p=[0.4, 0.3, 0.2, 0.1]),
    "refunded": np.random.choice([True, False], n, p=[0.07, 0.93]),
})

df["revenue"] = np.round(df["units_sold"] * df["unit_price"] * (1 - df["discount_pct"] / 100), 2)
df["quarter"] = df["date"].dt.to_period("Q").astype(str)

Task 1: Compute total revenue, average revenue per transaction, and total units sold, grouped by store. Sort by total revenue descending.

Task 2: Create a pivot-table style output with stores on rows and categories on columns, showing total revenue. Add a "Total" column. (Hint: groupby + unstack + column sum)

Task 3: Add a new column to the original DataFrame showing each transaction's percentage of its store's total revenue. Do this without merging — use transform().

Task 4: For each salesperson, compute:

• Total revenue
• Number of transactions
• Refund rate (refunded / total transactions)
• Revenue from transactions with no discount

Use a single agg() call where possible. For the no-discount revenue, pre-compute a helper column.

Task 5: Filter the DataFrame to keep only rows belonging to salespersons who generated more than $100,000 in total revenue. Use groupby().filter().

Task 6: Compute quarterly revenue by store using pd.Grouper. Reshape the result so quarters are columns and stores are rows. Export this to Excel with to_excel().

Expected insight from Task 6: You should see a roughly even distribution of revenue across quarters (since we used random data), but the structure of the export will be immediately recognizable to anyone who's seen a quarterly performance report.

---

Common Mistakes & Troubleshooting

"I got a MultiIndex on my columns and I can't work with it"

This happens when you use the old dict-of-lists syntax: df.groupby("region").agg({"revenue": ["sum", "mean"]}). The output has a two-level column index. Fix it either by using named aggregation syntax (which avoids the problem), or by flattening after the fact:

result = df.groupby("region").agg({"revenue": ["sum", "mean"], "units": "sum"})
# Flatten MultiIndex columns
result.columns = ["_".join(col).strip() for col in result.columns.values]
print(result.columns)  # ['revenue_sum', 'revenue_mean', 'units_sum']

"My groupby result has different row counts than I expect"

Check two things: First, are there NaN values in your grouping column? By default they're excluded. Second, did you accidentally call reset_index() in the wrong place and collapse a multi-level result?

"transform() raises an error about the shape"

transform() must return the same number of rows it received. If your function returns a DataFrame instead of a Series, or a differently-sized Series, it will fail. Check that your lambda or function returns a Series aligned to the input's index.

# This fails — returns a scalar, not a Series
df.groupby("region")["revenue"].transform(lambda x: x.sum())  # actually works, broadcasts scalar

# This fails — returns a DataFrame
df.groupby("region").transform(lambda x: x.describe())  # Error

"groupby is really slow on my big dataset"

Work through this checklist:

1. Are you using apply() when agg() or transform() would work? Replace it.
2. Are your grouping columns strings that should be categoricals?
3. Are you running groupby inside a loop? Vectorize — do the groupby once.
4. Do you need sort=True (the default)? Set sort=False if order doesn't matter.
5. Are you grouping an unnecessarily large DataFrame? Filter first, then group.

"The output index is messing up my downstream merge"

Call reset_index() after your groupby to turn the group keys into regular columns. Then merge works as expected. This is the most common "groupby to merge" issue.

# Don't do this
merged = big_df.merge(df.groupby("region")["revenue"].sum(), on="region")  # Fails or wrong

# Do this
region_summary = df.groupby("region")["revenue"].sum().reset_index()
region_summary.columns = ["region", "total_revenue"]
merged = big_df.merge(region_summary, on="region")

"My category order in the output is alphabetical but I need a custom order"

Use pd.Categorical with an ordered dtype before grouping:

category_order = ["Electronics", "Apparel", "Home", "Sports", "Food"]
df["category"] = pd.Categorical(df["category"], categories=category_order, ordered=True)
result = df.groupby("category", observed=True)["revenue"].sum()
print(result)  # Preserves your custom order

---

Summary & Next Steps

You've now covered the full spectrum of groupby functionality in pandas. Let's recap the mental models that will serve you longest:

• Split-apply-combine is the underlying mechanism for everything. When a groupby operation confuses you, think about what happens to each individual group.
• agg() reduces, transform() broadcasts, apply() is the escape hatch. Use them in that order of preference.
• Named aggregations (col_name=("source_col", "func")) give you clean output column names without extra renaming. Use them by default.
• unstack() is your pivot. After a multi-key groupby, unstack() rotates the inner index level into columns, recreating the PivotTable layout.
• Performance matters. Built-in functions over lambdas, categorical dtypes over strings, agg() over apply().

The patterns here — group, aggregate, reshape, export — form the backbone of data reporting in Python. If you've been building these workflows in Excel or SQL, you now have a direct translation that's faster, more reproducible, and infinitely more automatable.

For your next step in this learning path, explore how to take these grouped results and turn them into visualizations, or learn how to pipe the output directly into Excel reports with formatting using openpyxl. If you're still building confidence with Python fundamentals — particularly how loops and dictionaries work, which connect to how groupby objects behave under the hood — the lesson on Python Basics for Excel Users: Variables, Lists, Dictionaries, and Loops is a solid companion read.

The data you've been working with throughout this lesson is clean by design. In practice, you'll spend meaningful time before the groupby call handling nulls, fixing dtypes, and normalizing category names — all covered in Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types. Getting your data clean before grouping is not optional; it's what makes your aggregations trustworthy.