Pandas · Module 15 of 22 · 4 concepts · about 5 min

Reshaping Data

Long to wide and back, without losing the rows that were never there.

At work: Turning data into the shape a report, dashboard or model expects: one row per customer, one column per month.

pivot and pivot_table

What it is

pivot turns long data wide and requires index/column pairs to be unique; pivot_table aggregates duplicates instead of raising, with aggfunc and fill_value.

Why it matters

Models want one row per observation and one column per feature, while event data almost always arrives long.

In the real world: A sales table with one row per store per month becomes one row per store and one column per month: the layout finance expects in a spreadsheet.

Visual example

Reshaping

pivot turns long data (one row per observation) into wide data (one column per feature).

long

user	metric	value
a	clicks	4
a	views	9
b	clicks	2
b	views	7

pivot(index="user", columns="metric")

wide

	clicks	views
a	4	9
b	2	7

Why this matters

Events arrive long; models read wide. Pivoting is the step between a log table and a feature matrix.

Common misconception

Wrong idea: pivot combines duplicate rows for you.

Correct idea: pivot raises on duplicates. pivot_table aggregates them — with the mean, unless you say otherwise.

1index=One output row per user.
2columns=One output column per metric.
3values=Each cell takes the matching value.

df.pivot(index="user_id", columns="metric", values="value")

df.pivot_table(
    index="user_id", columns="event", values="id",
    aggfunc="count", fill_value=0,
)

Using pivot_table to dodge the duplicate error pivot raised. The error was information: the key you pivoted on was not what you thought it was.

Practise it: Pivot Event Counts, With the Zeros Included

melt

What it is

The inverse of pivot: turns columns into rows, with id_vars kept as identifiers and value_vars unpivoted into name/value pairs.

Why it matters

Wide spreadsheets — a column per month — have to be melted before they can be grouped or plotted over time.

In the real world: A marketing spreadsheet with a column per week is turned into one row per week, so it can be plotted and joined with sales.

wide.melt(
    id_vars=["store_id"],
    value_vars=["jan", "feb", "mar"],
    var_name="month",
    value_name="sales",
)

stack and unstack

What it is

unstack moves an index level into columns; stack does the reverse. They operate on the index rather than on named columns.

Why it matters

A group-by on several keys produces a MultiIndex, and unstacking one level is how that becomes a readable grid.

In the real world: Switching a report grouped by region and product between “products down the side” and “products across the top” for a presentation.

grid = df.groupby(["cohort", "month"])["user_id"].nunique().unstack("month")
grid.stack()     # back to a long Series

Practise it: Pivot Event Counts, With the Zeros Included

reindex and the missing zeros

What it is

reindex conforms a frame to a given set of labels, inserting missing ones with NaN, which you then fill.

Why it matters

A category with no events in a period should appear as zero, not be absent. Absent rows break time alignment and make averages wrong.

In the real world: Making sure days with no sales show up as zero on a daily chart instead of vanishing, which would make the trend look better than it was.

Visual example

Time series

A day with no sales should be a zero, not a missing row. reindex puts the missing days back.

Tue simply absent

	sales
Mon	4
Wed	6

reindex(calendar, fill_value=0)

Tue is a real zero

	sales
Mon	4
Tue	0
Wed	6

The 3-day average

without Tue: (4 + 6) / 2 = 5

with Tue: (4 + 0 + 6) / 3 ≈ 3.3

Why this matters

Absent rows skew every average and rolling window, and a chart draws a straight line across the gap as if nothing happened.

Common misconception

Wrong idea: no row means zero.

Correct idea: no row means unknown. Make the zero explicit with reindex(..., fill_value=0).

calendar = pd.date_range(store_open, store_close, freq="D")
daily = (
    sales.set_index("date")
         .reindex(calendar, fill_value=0)   # each store's own calendar
)

Padding against the global calendar rather than each group's own, which invents activity for a store before it opened.

Practise it: Pivot Event Counts, With the Zeros Included