Pandas Aggregation and GroupBy

Real-World Scenario

A monthly sales report needs revenue per region, per region and product, each rep’s share of their region, and a rolling trend line. Every one of those is a groupby or a close relative.

Basic GroupBy

Grouping by a column and picking a column to aggregate gives one result per group.

import pandas as pd

sales = pd.DataFrame({
    "region":  ["North", "South", "North", "East", "South", "North"],
    "product": ["Widget", "Widget", "Gadget", "Widget", "Gadget", "Widget"],
    "revenue": [1200, 800, 450, 950, None, 700],
})

print(sales.groupby("region")["revenue"].sum())
print(sales.groupby("region").size())
print(sales.groupby("region")["revenue"].count())
print(sales.groupby(["region", "product"])["revenue"].sum().unstack())

$ python basic.py
region
East      950.0
North    2350.0
South     800.0
Name: revenue, dtype: float64
region
East     1
North    3
South    2
dtype: int64
region
East     1
North    3
South    1
Name: revenue, dtype: int64
product  Gadget  Widget
region                 
East        NaN   950.0
North     450.0  1900.0
South       0.0   800.0

Groups come back sorted by key. South’s missing revenue is skipped by sum, and it separates size() (2 rows) from count() (1 filled value). Grouping by two columns produces a two-level index; unstack() turns the inner level into columns. Look closely at the two gaps: East/Gadget never occurred, so it is NaN, but South/Gadget shows 0.0 — its only revenue was missing, and the sum of nothing is zero. The two mean very different things and look almost alike.

Rows with a Missing Key Disappear

A NaN in the grouping column is dropped by default.

import pandas as pd

sales = pd.DataFrame({
    "region":  ["North", None, "North", "South", None],
    "revenue": [100, 200, 300, 400, 500],
})

print("total:", sales["revenue"].sum())
print(sales.groupby("region")["revenue"].sum())
print(sales.groupby("region", dropna=False)["revenue"].sum())

$ python nan_keys.py
total: 1500
region
North    400
South    400
Name: revenue, dtype: int64
region
North    400
South    400
NaN      700
Name: revenue, dtype: int64

The grand total is 1,500, but the default grouped sums add up to only 800 — the 700 from rows with no region vanished without a warning. dropna=False keeps them as a NaN group, so the report still reconciles with the total.

Named Aggregations

agg with name=(column, function) pairs builds a tidy summary with the column names you choose.

import pandas as pd

staff = pd.DataFrame({
    "dept":   ["Eng", "Eng", "Eng", "Sales", "Sales", "Ops"],
    "salary": [95000, 88000, 120000, 60000, 72000, 58000],
    "years":  [5, 3, 9, 2, 6, 4],
})

summary = staff.groupby("dept").agg(
    headcount=("salary", "size"),
    avg_salary=("salary", "mean"),
    spread=("salary", lambda s: s.max() - s.min()),
    max_years=("years", "max"),
)
print(summary)

$ python named_agg.py
       headcount  avg_salary  spread  max_years
dept                                           
Eng            3    101000.0   32000          9
Ops            1     58000.0       0          4
Sales          2     66000.0   12000          6

Each keyword becomes an output column, so there is no multi-level column header to flatten afterwards. A lambda works like any built-in name; Ops has a single employee, so its spread is 0.

transform: Group Statistics on Every Row

transform returns one value per input row, which is what you need to compare a row with its own group.

import pandas as pd

staff = pd.DataFrame({
    "name":   ["Ann", "Ben", "Cho", "Dev", "Eli"],
    "dept":   ["Eng", "Eng", "Eng", "Sales", "Sales"],
    "salary": [95000, 88000, 120000, 60000, 72000],
})

grp = staff.groupby("dept")["salary"]
staff["dept_avg"] = grp.transform("mean").round()
staff["vs_avg"] = staff["salary"] - staff["dept_avg"]
staff["rank"] = grp.rank(ascending=False).astype(int)
staff["share"] = (staff["salary"] / grp.transform("sum")).round(2)
print(staff)

$ python transform.py
  name   dept  salary  dept_avg   vs_avg  rank  share
0  Ann    Eng   95000  101000.0  -6000.0     2   0.31
1  Ben    Eng   88000  101000.0 -13000.0     3   0.29
2  Cho    Eng  120000  101000.0  19000.0     1   0.40
3  Dev  Sales   60000   66000.0  -6000.0     2   0.45
4  Eli  Sales   72000   66000.0   6000.0     1   0.55

The output has the same five rows as the input: every Eng row carries the Eng average, every Sales row the Sales average. That alignment is what makes vs_avg and share one-line column arithmetic. With agg you would get two summary rows and need a merge to get back here.

Top N per Group

Sort first, then take the head of each group.

import pandas as pd

orders = pd.DataFrame({
    "region":   ["North", "North", "North", "South", "South", "East"],
    "customer": ["Ann", "Ben", "Cho", "Dev", "Eli", "Fay"],
    "revenue":  [500, 1200, 900, 300, 700, 400],
})

top2 = (orders.sort_values("revenue", ascending=False)
              .groupby("region")
              .head(2))
print(top2.sort_values(["region", "revenue"], ascending=[True, False]))

$ python top_n.py
  region customer  revenue
5   East      Fay      400
1  North      Ben     1200
2  North      Cho      900
4  South      Eli      700
3  South      Dev      300

groupby(...).head(2) keeps up to two rows per group in their current order — here, highest revenue first. North loses Ann, its smallest order; East has only one row and keeps it. Unlike agg, head returns original rows, so every column survives.

Pivot Tables

pivot_table is a groupby on two keys laid out as a grid, with optional totals.

import pandas as pd

sales = pd.DataFrame({
    "region":  ["North", "North", "South", "South", "North", "East"],
    "quarter": ["Q1", "Q2", "Q1", "Q1", "Q1", "Q2"],
    "revenue": [100, 200, 150, 50, 300, 400],
})

pivot = sales.pivot_table(values="revenue", index="region", columns="quarter",
                          aggfunc="sum", fill_value=0,
                          margins=True, margins_name="Total")
print(pivot)

$ python pivot.py
quarter   Q1   Q2  Total
region                  
East       0  400    400
North    400  200    600
South    200    0    200
Total    600  600   1200

North has two Q1 sales (100 and 300), and aggfunc="sum" combines them into 400. fill_value=0 replaces the empty cells — East had no Q1 sales — and margins=True adds a Total row and column.

Rolling and Expanding Windows

Rolling windows compute a statistic over the last n rows; expanding windows use every row so far.

import pandas as pd

daily = pd.DataFrame(
    {"revenue": [100, 120, 90, 150, 130, 170, 160]},
    index=pd.date_range("2024-01-01", periods=7, freq="D"),
)

daily["avg_3d"] = daily["revenue"].rolling(3).mean().round(1)
daily["avg_3d_min1"] = daily["revenue"].rolling(3, min_periods=1).mean().round(1)
daily["running_avg"] = daily["revenue"].expanding().mean().round(1)
daily["pct_change"] = (daily["revenue"].pct_change() * 100).round(1)
print(daily)

$ python rolling.py
            revenue  avg_3d  avg_3d_min1  running_avg  pct_change
2024-01-01      100     NaN        100.0        100.0         NaN
2024-01-02      120     NaN        110.0        110.0        20.0
2024-01-03       90   103.3        103.3        103.3       -25.0
2024-01-04      150   120.0        120.0        115.0        66.7
2024-01-05      130   123.3        123.3        118.0       -13.3
2024-01-06      170   150.0        150.0        126.7        30.8
2024-01-07      160   153.3        153.3        131.4        -5.9

A 3-day window needs three rows, so the first two avg_3d values are NaN; min_periods=1 lets the window start with whatever is available. pct_change has nothing to compare the first day with, so it starts with NaN too.

Practice

1. In basic.py, compute the average revenue per product, ignoring region.

import pandas as pd

sales = pd.DataFrame({
    "region":  ["North", "South", "North", "East", "South", "North"],
    "product": ["Widget", "Widget", "Gadget", "Widget", "Gadget", "Widget"],
    "revenue": [1200, 800, 450, 950, None, 700],
})
print(sales.groupby("product")["revenue"].mean())

$ python practice_mean.py
product
Gadget    450.0
Widget    912.5
Name: revenue, dtype: float64

2. In transform.py, flag each employee who earns more than their department average.

import pandas as pd

staff = pd.DataFrame({
    "name":   ["Ann", "Ben", "Cho", "Dev", "Eli"],
    "dept":   ["Eng", "Eng", "Eng", "Sales", "Sales"],
    "salary": [95000, 88000, 120000, 60000, 72000],
})
avg = staff.groupby("dept")["salary"].transform("mean")
staff["above_avg"] = staff["salary"] > avg
print(staff)

$ python practice_flag.py
  name   dept  salary  above_avg
0  Ann    Eng   95000      False
1  Ben    Eng   88000      False
2  Cho    Eng  120000       True
3  Dev  Sales   60000      False
4  Eli  Sales   72000       True

3. In top_n.py, return only the single biggest order per region, using idxmax.

import pandas as pd

orders = pd.DataFrame({
    "region":   ["North", "North", "North", "South", "South", "East"],
    "customer": ["Ann", "Ben", "Cho", "Dev", "Eli", "Fay"],
    "revenue":  [500, 1200, 900, 300, 700, 400],
})
print(orders.loc[orders.groupby("region")["revenue"].idxmax()])

$ python practice_idxmax.py
  region customer  revenue
5   East      Fay      400
1  North      Ben     1200
4  South      Eli      700

4. In pivot.py, show the number of sales per region and quarter instead of their sum.

import pandas as pd

sales = pd.DataFrame({
    "region":  ["North", "North", "South", "South", "North", "East"],
    "quarter": ["Q1", "Q2", "Q1", "Q1", "Q1", "Q2"],
    "revenue": [100, 200, 150, 50, 300, 400],
})
print(sales.pivot_table(values="revenue", index="region", columns="quarter",
                        aggfunc="count", fill_value=0))

$ python practice_pivot.py
quarter  Q1  Q2
region         
East      0   1
North     2   1
South     2   0