6.4 Reshaping with melt, pivot, and stack

Chapter 1 introduced tidy data: each row represents one observation, each column one variable, and each table one kind of observational unit. Real exports often arrive wide instead, with values such as sales_2026_01, sales_2026_02, and sales_2026_03 encoded in column names. Reshaping changes this representation without automatically changing the facts.

Before reshaping, state the table's grain—what one row represents—and identify the key that should make rows unique. Shape alone cannot tell you whether repeated keys are errors, measurements, or legitimate line items.

Use melt to move column names into rows

Suppose each source row represents a store and monthly sales occupy separate columns:

value_columns = [
    "sales_2026_01",
    "sales_2026_02",
    "sales_2026_03",
]

long = wide.melt(
    id_vars=["store_id", "region"],
    value_vars=value_columns,
    var_name="month_key",
    value_name="sales",
)

The identifier columns are repeated for each selected measurement column. The measurement column names enter month_key, and their cells enter one sales column. If the input has $n$ rows and $k$ explicitly selected value columns, the result normally has $n \times k$ rows, including rows whose measured value is missing.

Parse information from the former headers explicitly:

long["month"] = pd.to_datetime(
    long["month_key"].str.removeprefix("sales_"),
    format="%Y_%m",
    errors="raise",
)

Do not rely on “all columns except the identifiers” in a changing dataset. A future note column could be melted into sales, mixing text and numbers and weakening the dtype. Explicit value_vars turns the expected source schema into visible code.

Useful invariants include:

• Expected output row count equals input rows times selected measurement columns.

• Each intended key, such as store_id–month, has the expected uniqueness.

• Non-missing numeric cell count is unchanged.

• Total sales is unchanged when the measure is additive.

Use pivot only when every destination cell is unique

The inverse-looking operation moves values from rows into columns:

wide_again = long.pivot(
    index="store_id",
    columns="month",
    values="sales",
)

pivot is a reshape, not an aggregation. It requires at most one sales value for each store_id–month combination. If two rows target the same output cell, pandas raises an error because it cannot decide which fact should survive.

Investigate the complete conflict groups first:

keys = ["store_id", "month"]
conflict = long.duplicated(keys, keep=False)
long.loc[conflict].sort_values(keys)

If duplicates are erroneous, repair them using the evidence-based approach from Chapter 5. If rows are valid line items and sales is additive, aggregate with a declared rule:

wide_total = long.pivot_table(
    index="store_id",
    columns="month",
    values="sales",
    aggfunc="sum",
)

Never use pivot_table merely to silence a pivot error. Its aggregation changes the grain. The default mean is wrong for many totals, counts, balances, and rates. State why sum, mean, first, or another function matches the domain, and compare totals or denominators before and after.

Multiple index or columns fields can create a MultiIndex, a hierarchical index with more than one label level. stack moves a column level into the row index; unstack moves a row-index level into columns:

stacked = wide_total.stack(future_stack=True).rename("sales")
round_trip = stacked.reset_index()

In current pandas, the newer stack implementation is the standard behavior; future_stack=True also makes that intent explicit on pandas 2.1–2.3. Do not pass legacy dropna or sort controls alongside that mode. Sort both results and normalize index/column names before testing a round trip, because representation metadata can differ even when observations agree.

A wide grid can contain a missing cell for a store–month pair that never existed. After stack, that cell is indistinguishable from a source row whose sales value was observed as missing. If this distinction matters, preserve the source key universe and use it to select the round-trip rows; blindly calling dropna would also discard genuine missing observations.

Missing combinations need special care. A missing cell in wide form may mean “no observation,” while a zero may mean an observed total of zero. Reshaping must not exchange those meanings.

Treat reshaping as a tested contract

A reliable reshape records:

• The source grain and expected destination grain.

• Identifier columns and value columns.

• Uniqueness expectations and any aggregation rule.

• Missing-combination behavior.

• Row counts, non-missing counts, and domain-specific totals before and after.

• A round-trip or equivalent invariant when the operation should be reversible.

Reshaping prepares data for the next chapter. Matplotlib works most naturally when each visual variable—position, color, group, or panel—can be mapped from a clear column, and the long form built here often provides exactly that structure.