Pivot tables and trends over time

01The problem

The board meets next week. The managing director wants one slide on how Kolanut is doing, and the questions behind it are all about change over time:

"Are we growing? Which regions are growing and which are falling? Is the dip in May a real problem or just noise?"

Raw monthly totals don't answer that well: months have different numbers of trading days, December always spikes, and the data stops at June 2026. Answering honestly takes three tools: a pivot table to lay the numbers out, growth rates to compare like with like, and a rolling average to see the trend under the noise.

02The concept

pivot_table: rows × columns × a value

pd.pivot_table(full, index="region", columns="year", values="revenue", aggfunc="sum")

One row per region, one column per year, revenue summed in each cell. It's a groupby on two columns followed by unstack(), written in one readable call. Add margins=True for totals, and fill_value=0 where a combination has no rows.

pd.crosstab: counts of two categories

pd.crosstab(full["region"], full["channel"]) counts rows for each region and channel. normalize="index" turns each row into shares that add up to 1.

Time series: one row per period

Turn dates into periods, then group:

monthly = orders.groupby(orders["order_date"].dt.to_period("M"))["revenue"].sum()

Measuring change

• .pct_change() gives each period's change from the one before: 0.05 means 5% up.
• .pct_change(12) on monthly data compares each month with the same month a year earlier, which removes seasonal effects like December's spike. That's year-on-year (YoY).
• .diff() gives the change in naira rather than in percent.
• .rolling(3).mean() averages each month with the two before it: a 3-month rolling average. It smooths out one-off spikes so you can see the direction.

Compare like with like

The data covers January 2025 to June 2026. Comparing 2026 with 2025 as whole years compares 6 months with 12. Compare H1 2025 (January–June) with H1 2026 instead.

03Example

Set up the merged table from lesson 8, then compare the two half-years by region:

import pandas as pd

base = "https://academy.cloudtechanalytics.com/datasets/sales/"
orders = pd.read_csv(base + "orders.csv", parse_dates=["order_date"])
customers = pd.read_csv(base + "customers.csv")
orders["revenue"] = orders["quantity"] * orders["unit_price"] * (1 - orders["discount_pct"] / 100)
full = orders.merge(customers, on="customer_id", how="left", validate="many_to_one")
full["year"] = full["order_date"].dt.year

h1 = full[full["order_date"].dt.month <= 6]
growth = pd.pivot_table(h1, index="region", columns="year", values="revenue", aggfunc="sum")
growth["change"] = growth[2026] - growth[2025]
growth["growth_pct"] = (growth[2026] / growth[2025] - 1) * 100
growth.sort_values("growth_pct", ascending=False).round(1)

year                  2025         2026      change  growth_pct
region
South West      28946835.0   51692900.0  22746065.0        78.6
Lagos          118153395.0  152768595.0  34615200.0        29.3
South South     19748010.0   23088650.0   3340640.0        16.9
North Central   25315755.0   27165785.0   1850030.0         7.3
South East      20851155.0   19367705.0  -1483450.0        -7.1
North West      31147920.0   16646820.0 -14501100.0       -46.6

parse_dates=["order_date"] converts the dates while loading, which saves the separate pd.to_datetime step.

The story writes itself: Lagos added the most money (₦34.6m), South West grew fastest (79%), and North West fell by almost half, with South East slipping too. That's the line for the board slide, and the question for the next meeting is why.

04Walkthrough

1. Run the Example. Read the table row by row and say each result out loud in words.
2. Overall H1 growth: h1.groupby("year")["revenue"].sum().pct_change().iloc[-1] gives the change from H1 2025 to H1 2026 as a fraction.
3. Monthly series: monthly = full.groupby(full["order_date"].dt.to_period("M"))["revenue"].sum().
4. Month-on-month change: monthly.pct_change().round(3).tail(6). Notice how jumpy it is.
5. Year-on-year: monthly.pct_change(12).dropna().round(3). Only January to June 2026 have a month a year earlier to compare with, so the other values are blank and dropna() removes them.
6. Smooth it: monthly.rolling(3).mean() and compare it with monthly for the last six months. The monthly totals swing by several million naira from one month to the next; the rolling average moves far less, so the direction is easier to see.
7. A crosstab of channel mix by region: pd.crosstab(full["region"], full["channel"], values=full["revenue"], aggfunc="sum", normalize="index").round(2). Each row shows how a region's revenue splits across channels.

05Practice

06Challenge

07Check your understanding