Reshaping with pivot_table and melt

A manager does not want 3,991 rows. They want a grid: channels down the side, quarters across the top, revenue in the cells. The finance team sends targets the same way, one column per quarter. Your analysis, meanwhile, works best on long tables with one row per observation, because groupby, merge and filtering all expect that shape. Reshaping moves data between the two forms, and it is the last core skill before you start answering questions in earnest.

pivot_table: summarise and reshape at once

Start from the sales table you built in Lesson 4.2 (here rebuilt in a few lines) and ask for revenue by channel and year:

import pandas as pd

items = pd.read_csv("order_items.csv")
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount"])
sales = items.merge(orders, on="order_id", how="left", validate="many_to_one")
sales = sales[sales["status"] != "cancelled"]
sales = sales.assign(year=sales["order_date"].dt.year, quarter=sales["order_date"].dt.quarter)

table = sales.pivot_table(index="channel", columns="year", values="revenue",
                          aggfunc="sum", margins=True, margins_name="Total")
print(table.round(0))

Every argument maps to a spreadsheet pivot: index is the rows, columns the columns, values the field, aggfunc the summary. margins=True adds the totals row and column, and they confirm the result: 446,666 dollars overall, the revenue definition from the last lesson. Revenue grew in every channel from 2024 to 2025, and the web roughly tripled.

pivot_table is the same computation as groupby(["channel", "year"])["revenue"].sum().unstack(). Use groupby when you will keep computing, and pivot_table when the grid itself is the output. For counts of distinct orders, change the aggregation: values="order_id", aggfunc="nunique". And fill_value=0 replaces the NaN of empty cells when zero is the honest answer, as it is for “no sales in that cell”.

From amounts to shares

A grid of amounts invites the next question: how did the mix change? Divide each column by its total and the grid shows shares instead. by_year.sum() returns one total per column, and dividing a DataFrame by that Series lines each total up with its own column by label, the same alignment rule you met with two Series in Lesson 2.1. To divide each row by its row total instead, use by_year.div(by_year.sum(axis=1), axis=0).

import pandas as pd

items = pd.read_csv("order_items.csv")
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount"])
sales = items.merge(orders, on="order_id", how="left", validate="many_to_one")
sales = sales[sales["status"] != "cancelled"]

by_year = sales.pivot_table(index="channel", columns=sales["order_date"].dt.year,
                            values="revenue", aggfunc="sum")
print((by_year / by_year.sum()).round(3))

The web’s share of revenue rose from 53% in 2024 to 59% in 2025, while the app’s fell from 35% to 31%. Amounts tell you everything grew; shares tell you where the growth concentrated. Report both, because each hides what the other shows.

crosstab: counts and rates

For two categorical columns, pd.crosstab counts combinations, and normalize converts counts to shares. Here is Cartwheel’s web traffic:

import pandas as pd

sessions = pd.read_csv("web_sessions.csv")
print(pd.crosstab(sessions["source"], sessions["device"]))
print(pd.crosstab(sessions["device"], sessions["purchased"], normalize="index").round(3))

The second table answers a real question. normalize="index" makes each row sum to 1, so the 1 column is the purchase rate per device: 12.4% of desktop sessions end in a purchase, but only 6.7% of mobile sessions, although mobile brings the most traffic. That gap is worth a conversation with whoever owns the mobile checkout. Choose the normalisation by the question: "index" for rates within each row group, "columns" for composition of each column, "all" for shares of the grand total.

melt: from wide back to long

The finance team’s 2025 targets arrive as a spreadsheet, one column per quarter. To compare them with actual revenue you need them long, one row per channel and quarter, so they can be merged:

import pandas as pd

targets = pd.DataFrame({
    "channel": ["marketplace", "mobile_app", "web"],
    "Q1": [5000, 15000, 28000],
    "Q2": [5000, 16000, 30000],
    "Q3": [6000, 20000, 40000],
    "Q4": [9000, 30000, 60000],
})
long = targets.melt(id_vars="channel", var_name="quarter", value_name="target")
long["quarter"] = long["quarter"].str[1].astype(int)
print(long.head(6))
print(long.shape)

id_vars are the columns that identify a row and stay as they are; every other column becomes a pair of values in two new columns, the old column name in var_name and its value in value_name. Three rows times four quarters gives twelve. str[1] takes the second character of "Q1", so the quarter becomes a number that matches sales["quarter"], ready for a merge like the ones in Lesson 4.2.

In the exercise you build the quarterly grid for 2025 and check performance against those targets. Next comes time itself: resampling, periods and growth rates.