Pandas Advanced (GroupBy & Pivot Tables) Cheat Sheet
Advanced groupby aggregations, pivot_table reshaping, and MultiIndex manipulation techniques for summarizing and restructuring data in pandas.
2 PagesIntermediateMar 12, 2026
Advanced GroupBy Aggregations
Named aggregations, transforms, and custom group functions.
python
import pandas as pd# Multiple named aggregations per columnsummary = df.groupby("region").agg( total_sales=("amount", "sum"), avg_sales=("amount", "mean"), num_orders=("order_id", "count")).reset_index()# transform() adds group stats back to the original rowsdf["region_avg"] = df.groupby("region")["amount"].transform("mean")# Custom aggregation functiondf.groupby("region")["amount"].agg(lambda x: x.max() - x.min())# groupby multiple keysdf.groupby(["region", "category"])["amount"].sum()
Pivot Tables
Reshape long data into a summary matrix.
python
pivot = pd.pivot_table( df, values="amount", index="region", columns="category", aggfunc="sum", fill_value=0, margins=True, # adds row/column totals margins_name="Total")# Multiple aggregation functions at oncepd.pivot_table(df, values="amount", index="region", aggfunc=["sum", "mean", "count"])
MultiIndex & Reshaping
Convert between long and wide formats and flatten hierarchical columns.
python
# Reshape long -> widewide = df.pivot(index="date", columns="product", values="sales")# Reshape wide -> longlong = wide.reset_index().melt(id_vars="date", var_name="product", value_name="sales")# Flatten MultiIndex columns from groupby.agggrouped = df.groupby(["region", "category"]).agg({"amount": ["sum", "mean"]})grouped.columns = ["_".join(col) for col in grouped.columns]
Key Concepts
Terminology for reshaping and summarizing DataFrames.
- groupby().agg()- Apply one or more aggregation functions per column, optionally with named outputs
- transform()- Returns a result aligned to the original DataFrame's index, unlike agg() which collapses rows
- filter()- Keep or drop entire groups based on a group-level condition, e.g. group size > 10
- pivot_table vs pivot- pivot_table aggregates duplicate index/column combinations; pivot requires unique combinations
- crosstab- pd.crosstab() computes a frequency table between two or more categorical columns
- MultiIndex- Hierarchical row/column index created by groupby or pivot with multiple keys
- stack()/unstack()- Pivot a level of column labels to rows (stack) or rows to columns (unstack)
GroupBy.apply() with Custom DataFrame Logic
Run arbitrary per-group logic that returns a DataFrame, Series, or scalar.
python
import pandas as pddef top_n_by_amount(group, n=2): return group.nlargest(n, "amount")# apply() on groups can return a DataFrame per group -> concatenated resulttop2 = df.groupby("region", group_keys=False).apply(top_n_by_amount, n=2)# rank within each groupdf["rank_in_region"] = df.groupby("region")["amount"].rank(method="dense", ascending=False)# pct_change within groups (e.g. period-over-period growth per region)df = df.sort_values(["region", "order_date"])df["region_pct_change"] = df.groupby("region")["amount"].pct_change()# cumulative sum reset per groupdf["running_total"] = df.groupby("region")["amount"].cumsum()
Filtering Groups & Windowed Aggregations
Keep/drop whole groups by a group-level condition and combine groupby with rolling windows.
python
# Keep only groups (regions) with more than 50 ordersactive_regions = df.groupby("region").filter(lambda g: len(g) > 50)# nth() - pick the k-th row per group (e.g. first/last order per customer)first_order = df.groupby("customer_id").nth(0)last_order = df.groupby("customer_id").nth(-1)# Rolling mean computed independently within each groupdf = df.sort_values(["region", "order_date"])df["rolling_avg_7"] = ( df.groupby("region")["amount"] .transform(lambda s: s.rolling(7, min_periods=1).mean()))# groupby with a custom binning key (pd.cut) for histogram-style aggregationbins = pd.cut(df["amount"], bins=[0, 100, 500, 1000, float("inf")])df.groupby(bins, observed=True)["amount"].count()
pivot_table with Multiple Values & Custom Aggfuncs
Build multi-metric pivot tables and apply a different aggregation per value column.
python
# Different aggfunc per value columnsummary = pd.pivot_table( df, values=["amount", "order_id"], index="region", columns="category", aggfunc={"amount": "sum", "order_id": "nunique"}, fill_value=0)# Multi-level index/columns pivotdetailed = pd.pivot_table( df, values="amount", index=["region", "sales_rep"], columns=["category", "quarter"], aggfunc="sum", fill_value=0)# Convert a pivot_table result back to long/tidy formtidy = summary.stack(level=0, future_stack=True).reset_index()
Slicing MultiIndex Results with xs() and IndexSlice
Select cross-sections of hierarchical rows/columns produced by groupby or pivot.
python
grouped = df.groupby(["region", "category"])["amount"].sum()# xs() selects a cross-section on any index levelgrouped.xs("US", level="region")grouped.xs("Electronics", level="category")# pd.IndexSlice for flexible partial slicing on a MultiIndex frameidx = pd.IndexSlicewide = df.pivot_table(values="amount", index="region", columns=["category", "quarter"])wide.loc[:, idx["Electronics", :]]# swaplevel + sort_index to reorder a MultiIndexgrouped.swaplevel().sort_index()
Advanced Terminology
Concepts that separate intermediate groupby/pivot usage from advanced usage.
- group_keys=False- Prevents apply() from adding an extra group-label index level to the result, keeping the original row index
- as_index=False- Returns groupby result as a flat DataFrame instead of using the group keys as the index
- observed=True- For groupby on Categorical columns, only includes categories that actually appear in the data instead of the full category cross-product
- pd.Grouper- Wraps a column/index for groupby with extra options, e.g. pd.Grouper(key='date', freq='M') to group by month
- xs()- Extracts a cross-section from a MultiIndex Series/DataFrame by label at any level, dropping that level
- stack(future_stack=True)- Pivots column labels into row index; the future_stack flag opts into pandas 2.1+ semantics that don't drop NaN rows automatically
- pivot_table's dropna- By default drops columns that are entirely NaN; set dropna=False to keep the full category grid
- agg with list per column- df.groupby('k').agg({'a': ['sum','mean'], 'b': 'max'}) applies different aggregations per column in one call
Pro Tip
Use named aggregation - df.groupby('col').agg(new_name=('col2', 'sum')) - instead of the old dict-based agg syntax; it avoids ambiguous MultiIndex columns and is the recommended modern API.
Was this cheat sheet helpful?
Explore Topics
#PandasAdvancedGroupByPivotTables#PandasAdvancedGroupByPivotTablesCheatSheet#DataScience#Intermediate#AdvancedGroupByAggregations#PivotTables#MultiIndexReshaping#KeyConcepts#MachineLearning#CheatSheet#SkillVeris
