pandas groupby aggregation and pandas pivot table are three related but genuinely distinct tools for summarizing data — worth understanding as a coherent set, since knowing exactly when to reach for which one is where a lot of confusion actually lives. This article covers grouping, multi-function and named aggregation, pivot tables, and a clear decision framework for choosing between groupby() and pivot_table().
Three Tools for Summarizing Data
Three related concepts
Three important concepts in pandas for summarizing and transforming data are groupby(), aggregation, and pivot tables. They're related — all three ultimately answer "what does this data look like when broken down by some category?" — but genuinely distinct in how they organize the result.
What groupby() actually does
groupby() is used to split data into groups based on some criteria — most often one or more columns — so operations can be performed on each group separately. This is conceptually similar to SQL's GROUP BY, but with one key difference worth flagging upfront: using .groupby() in pandas, the original, ungrouped DataFrame remains fully accessible alongside the grouped result — nothing about calling .groupby() modifies or replaces your original data, unlike a SQL query, which returns only the aggregated result and discards the original row-level detail entirely.
What this article covers
Basic and multi-column grouping, applying multiple aggregation functions at once (including the modern, more readable named-aggregation syntax), pivot tables for cross-tabulated summaries, and — the genuine core of this article — clear guidance on when to reach for groupby() versus pivot_table().
GroupBy Basics: Splitting Data into Groups
Basic syntax
import pandas as pd
df = pd.DataFrame({
"region": ["East", "East", "West", "West", "East"],
"product": ["Widget", "Gadget", "Widget", "Gadget", "Widget"],
"sales": [100, 150, 200, 120, 90],
})
grouped = df.groupby("region")
print(grouped["sales"].sum())
# region
# East 340
# West 320
# Name: sales, dtype: int64df.groupby("region") creates a grouped object — not a result by itself, but a structure ready to have an aggregation applied to it. Calling .sum() (or .mean(), .count(), and similar) on a specific column of that grouped object returns the actual per-group result.
Grouping by multiple columns
multi_grouped = df.groupby(["region", "product"])["sales"].sum()
print(multi_grouped)
# region product
# East Gadget 150
# Widget 190
# West Gadget 120
# Widget 200
# Name: sales, dtype: int64Passing a list of column names groups by every unique combination of those columns simultaneously — here, sales broken down by region and product together, rather than either dimension alone. Notice the result has a multi-level (hierarchical) index — both region and product are now index levels, rather than plain columns.
Flattening back to a regular index
flat = multi_grouped.reset_index()
print(flat)
# region product sales
# 0 East Gadget 150
# 1 East Widget 190
# 2 West Gadget 120
# 3 West Widget 200Exactly as covered in the earlier data selection article, .reset_index() flattens a hierarchical result back into a plain, regular DataFrame — genuinely useful once you want to work with the grouped result using ordinary column-based operations, rather than navigating a multi-level index.
Aggregating with .agg(): Multiple Functions and Named Aggregations
Multiple functions on one column
result = df.groupby("region")["sales"].agg(["sum", "mean", "max"])
print(result)
# sum mean max
# region
# East 340 113.333333 150
# West 320 160.000000 200Passing a list of function names to .agg() applies all of them at once to the same column, producing one output column per function — genuinely more efficient, and more concise, than calling .sum(), .mean(), and .max() separately and manually combining the results yourself.
Different functions for different columns
result = df.groupby("region").agg({
"sales": "sum",
"product": "count",
})
print(result)
# sales product
# region
# East 340 3
# West 320 2Passing a dictionary instead lets you apply a genuinely different aggregation to each column — summing sales while counting product entries, all in a single .agg() call.
Named aggregation: the modern, more readable syntax
result = df.groupby("region").agg(
total_sales=("sales", "sum"),
avg_sales=("sales", "mean"),
num_transacti("product", "count"),
)
print(result)
# total_sales avg_sales num_transactions
# region
# East 340 113.333333 3
# West 320 160.000000 2This is the modern, considerably more readable syntax, available since pandas 0.25: each keyword argument becomes a column name in the output, paired with a tuple specifying which source column to aggregate and which function to apply. Compare the clean, descriptive column names here (total_sales, avg_sales) against the messier, multi-level column names pandas' older, default .agg(["sum", "mean"]) style produces when applied across several columns at once — named aggregation is generally the preferable choice for any result you plan to actually read, present, or build further logic on top of.
Custom functions with .agg()
def sales_range(x):
return x.max() - x.min()
result = df.groupby("region")["sales"].agg(sales_range)
print(result)
# region
# East 60
# West 80
# Name: sales, dtype: int64.agg() accepts any callable, not just the built-in named strings ("sum", "mean", and similar) — sales_range here computes a custom statistic (the spread between a group's maximum and minimum) that has no built-in, one-word equivalent. This genuinely extends .agg()'s reach to essentially any per-group calculation you can express as a function, including a lambda, covered in the earlier lambda functions article:
result = df.groupby("region")["sales"].agg(lambda x: x.max() - x.min())Pivot Tables: Reshaping Data into a Cross-Tabulated Summary
The core distinction, leading with the point of most confusion
This is worth stating plainly, since it's the single most common point of confusion between the two tools: pivot_table() is essentially a multidimensional version of GroupBy aggregation. Use groupby() for a single, straightforward aggregation — total sales per region. Use pivot_table() specifically when you need categories spread across both rows and columns at once — sales by region as rows, and by product as columns, simultaneously, in one combined table.
Basic syntax
pivot = pd.pivot_table(df, values="sales", index="region", columns="product", aggfunc="sum")
print(pivot)
# product Gadget Widget
# region
# East 150 190
# West 120 200This produces a genuine matrix-style summary — directly comparable to an Excel pivot table — with region values running down the rows, product values running across the columns, and each cell holding the summed sales for that specific region-product combination.
Missing combinations become NaN automatically
df2 = pd.DataFrame({
"region": ["East", "East", "West"],
"product": ["Widget", "Gadget", "Widget"],
"sales": [100, 150, 200],
})
pivot2 = pd.pivot_table(df2, values="sales", index="region", columns="product", aggfunc="sum")
print(pivot2)
# product Gadget Widget
# region
# East 150.0 100.0
# West NaN 200.0There's no "West, Gadget" combination anywhere in the source data — and pivot_table() correctly represents that gap as NaN, rather than silently omitting the row or column, or guessing at a value. This is exactly the missing-data scenario covered in the earlier missing data article, applied specifically to a pivot table's output.
The aggfunc parameter and fill_value
pivot3 = pd.pivot_table(df2, values="sales", index="region", columns="product",
aggfunc="sum", fill_value=0)
print(pivot3)
# product Gadget Widget
# region
# East 150 100
# West 0 200aggfunc accepts the exact same range of functions as .agg() — built-in strings ("sum", "mean", "count"), or a custom callable. fill_value replaces those NaN gaps with a specific value — here, 0 — genuinely appropriate whenever "no sales recorded for this combination" logically means zero, rather than a genuinely unknown or missing value that shouldn't be silently assumed to be zero.
Choosing the Right Tool and Performance Considerations
A practical decision guide
Reach for
pivot_table()when you want a genuinely cross-tabulated summary — categories spread across both rows and columns simultaneously, matrix-style.Reach for
groupby()when you want a single, simpler aggregation, without needing to spread categories across two separate axes — total sales per region, average score per student, count per category.
If you find yourself building a pivot_table() and then immediately flattening it back into a simple, single-axis summary, that's usually a sign groupby() alone would have gotten you there more directly, without the intermediate matrix-shaping step.
Honest performance guidance
groupby() is generally fastest for simple, single-axis aggregations — it doesn't need to materialize a full matrix, just one aggregated value per group. pivot_table(), by contrast, can become genuinely memory-intensive once you have many unique category combinations, since it has to materialize the entire matrix, including every combination that has no actual data (represented as NaN, as shown in Section 4) — a pivot table with 10,000 unique row categories and 10,000 unique column categories would attempt to allocate a 100-million-cell matrix, the overwhelming majority of which might be empty. For very wide or high-cardinality pivot results, it's genuinely worth checking .memory_usage() on the result directly, rather than assuming a pivot table is always a lightweight operation purely because the underlying source data isn't especially large.
Related tools worth knowing exist
pd.crosstab() is a lighter-weight cousin of pivot_table(), specifically for pure frequency cross-tabulation — counting how many rows fall into each combination of two categorical variables, without needing to specify a values column or an aggfunc at all, since counting is its entire purpose:
counts = pd.crosstab(df["region"], df["product"])
print(counts).stack()/.unstack() reshape data between "long" format (one row per observation, categories as column values) and "wide" format (categories spread across separate columns) — genuinely useful once groupby and pivot tables feel comfortable, and you need finer-grained control over exactly how a multi-level index gets reshaped into rows versus columns. Both are natural next steps once the core tools covered in this article feel solid.