skip to content

Reshaping and Pivoting

pivot spreads values into columns and fails on duplicates, pivot_table aggregates them, melt goes back to long, and stack and unstack move levels. Interviewers ask which to use.

on this pageshow

questions

6

In pandas, how do you use `melt` to turn a wide survey export with one column per question back into one row per answer?

level: juniorimportance: must knowfreq 60%

answer

  1. the inverse of pivot
  2. identifier columns stay put
  3. id_vars and value_vars
  4. var_name and value_name rename

basics

~10 s

Call df.melt(id_vars="respondent", value_vars=["q1", "q2", "q3"], var_name="question", value_name="score"). Each listed column becomes rows: the old header goes into the var_name column and its cell into the value_name column.

solid answer

~40 s

`DataFrame.melt` (or `pd.melt`) unpivots: the `id_vars` columns are repeated on every output row, and each column in `value_vars` contributes one row per input row, so 2 respondents times 3 question columns gives 6 rows. The old column label lands in a column named by `var_name` (default `"variable"`) and the cell lands in one named by `value_name` (default `"value"`). If you pass `value_vars`, any column in neither list is dropped; if you omit it, every non-id column is melted. The index is reset by default (`ignore_index=True`), missing answers stay as NaN rows, and when the headers carry two pieces of information, such as `score_2025`, `pd.wide_to_long` with `stubnames`, `i`, `j` and `sep` is the better fit.

go deeper

for a junior

Recall melt's id_vars, value_vars, var_name and value_name, and that rows equal input rows times melted columns.

for a middle

Explain which columns are dropped when value_vars is given, why NaN answers stay, and how ignore_index changes the index.

for a senior

Choose wide_to_long when headers encode two facts, and know why a missing sep returns an empty frame instead of failing.

for a principal

Decide where a pipeline stores survey data long rather than wide, so new questions add rows instead of schema changes.

## What `melt` is for A survey tool exports one row per respondent and one column per question: `respondent`, `region`, `q1`, `q2`, `q3`. That **wide** layout is easy to read but awkward to analyse, because "the average score per question" needs a question column to group on. `DataFrame.melt`, also available as the function `pd.melt`, **unpivots** the frame into a **long** layout with one row per respondent and question. It is the inverse of `pivot`. ## The four arguments that matter | Argument | Default | Role | |---|---|---| | `id_vars` | none | Columns copied onto every output row (the identifiers) | | `value_vars` | every column not in `id_vars` | Columns whose headers become values | | `var_name` | `frame.columns.name`, else `"variable"` | Name of the column holding the old headers | | `value_name` | `"value"` | Name of the column holding the old cells; it may not match an existing column | | `ignore_index` | `True` | Resets the result to `0..n-1`; `False` repeats the original index | ```python import numpy as np import pandas as pd survey = pd.DataFrame({ "respondent": [101, 102], "region": ["north", "south"], "q1": [4, 5], "q2": [3, np.nan], "q3": [5, 2], }) long = survey.melt( id_vars="respondent", value_vars=["q1", "q2", "q3"], var_name="question", value_name="score", ) print(long) # 6 rows: respondent, question, score ``` ## Reading the result - **Row count** is rows times melted columns: 2 respondents times 3 questions is 6 rows. - **Row order** walks column by column: both respondents' `q1`, then both `q2`, then both `q3`. Sort with `sort_values(["respondent", "question"])` if you want each respondent together. - **Missing answers survive.** Respondent 102 has no `q2`, and that row is still there with `score` NaN. Drop it explicitly with `dropna(subset=["score"])` if the analysis wants only answered questions. - **Columns not listed are dropped** when `value_vars` is given. `region` above is in neither list, so it is gone; put it in `id_vars` to keep it. - **Mixed types.** If you omit `value_vars`, every non-id column is melted, so `region` strings and numeric scores end up together in one value column of `object` dtype. Listing `value_vars` keeps the value column numeric. ## Errors you meet 1. `value_name="region"` raises `ValueError: value_name (region) cannot match an element in the DataFrame columns.`, because the output would have two columns of that name. 2. A label in `id_vars` or `value_vars` that the frame does not have raises `KeyError`, naming the missing labels. 3. An `id_vars` label that the frame itself carries twice (two columns with the same header) raises `ValueError: id_vars cannot contain duplicate columns.` ## When the header carries two facts: `wide_to_long` A second export has `score_2025`, `score_2026`, `wait_2025` and `wait_2026`. Melting it puts `score_2025` into a single variable column, so you would still have to split the label and pivot the two measures apart. `pd.wide_to_long` does that in one call: ```python wide = pd.DataFrame({ "respondent": [101, 102], "score_2025": [4, 5], "score_2026": [3, 2], "wait_2025": [10, 12], "wait_2026": [8, 9], }) pd.wide_to_long(wide, stubnames=["score", "wait"], i="respondent", j="year", sep="_") ``` - `stubnames` are the prefixes that become value columns (`score`, `wait`). - `i` names the column or columns that identify a row; they must be **unique**, or pandas raises `ValueError: the id variables need to uniquely identify each row`. - `j` names the new index level built from the suffix (`year`). - `sep` defaults to `""` and `suffix` to `r"\d+"`, so with headers like `score_2025` you **must** pass `sep="_"`. Without it no column matches and the call returns an empty frame instead of raising. The result has a `(respondent, year)` `MultiIndex` and one column per stub. `reset_index()` turns it back into ordinary columns. ## Melting a pivoted frame back A station-by-date frame produced by `pivot` keeps the stations in its **index**, and `melt` ignores the index: `wide.melt()` returns only `variable` and `value`, and the station labels are gone. Move the index into a column first: ```python long_again = wide.reset_index().melt( id_vars="station", var_name="date", value_name="reading" ) ``` - The `reset_index()` call is what keeps the station; without it there is nothing to pass as `id_vars`. - Holes that `pivot` filled with NaN come back as NaN rows, so a `dropna(subset=["reading"])` restores the original row count. ## Choosing the tool - One measure spread across many columns: `melt`. - Several measures, each spread across the same suffixes: `wide_to_long`. - The reverse direction, long to wide: `pivot` or `pivot_table`. - Column levels already in a `MultiIndex`: `stack` moves them into the index without naming id columns.

  • How do you keep the original row labels in the melted output?
    Pass `ignore_index=False`. The default `True` gives the result a fresh `0..n-1` index; with `False` each original index label repeats once per melted column, so a respondent's three rows share the label they had in the wide frame.
  • Why did `pd.wide_to_long(wide, stubnames=["score", "wait"], i="respondent", j="year")` return an empty frame?
    Because `sep` defaults to an empty string and `suffix` to digits, so pandas looked for headers like `score2025`. With `score_2025` nothing matched and the result is empty rather than an error. Passing `sep="_"` fixes it.

  • melt needs a loop or apply to turn each column into rows
  • melt drops rows whose value is NaN automatically
  • Columns left out of both id_vars and value_vars are kept unchanged
  • value_name can reuse an existing column name to overwrite it
  • wide_to_long splits score_2025 on the underscore without being told
open as a page

In pandas, why does `pivot` raise on a long station, date and reading weather table when `pivot_table` succeeds, and what does `pivot_table` add?

level: middleimportance: must knowfreq 70%

basics

~10 s

DataFrame.pivot only reshapes, so two rows sharing an index and column pair raise ValueError: Index contains duplicate entries, cannot reshape. pivot_table aggregates those duplicates with aggfunc (default mean) and adds fill_value and margins.

open as a page

In pandas, what does `pd.crosstab(obs["station"], obs["condition"], normalize="index")` return, and how do margins change it?

level: middleimportance: should knowfreq 40%

basics

~20 s

pd.crosstab counts rows per station and condition pair; normalize="index" divides each count by its row total so every station's shares sum to 1. With margins=True it also adds an All row holding the overall shares.

open as a page

In pandas, what does `DataFrame.explode` do to the index, to empty lists and to the dtype when a list-valued sensors column is exploded?

level: middleimportance: should knowfreq 40%

basics

~20 s

DataFrame.explode gives each list element its own row, copying the other columns and repeating the original index label. An empty list becomes one row with NaN, and the exploded column keeps object dtype for numbers.

open as a page

In pandas, why can `pd.get_dummies(batch, columns=["plan"], drop_first=True)` on a scoring batch silently disagree with the columns built from the training frame?

level: seniorimportance: should knowfreq 42%

basics

~20 s

get_dummies builds one column per level present in the data it is given, and drop_first drops the first of those levels. A batch missing some plans gets different columns, so fix the levels with a CategoricalDtype before encoding.

open as a page

In pandas 3.0, why can `wide.stack()` return more rows than the long Series you unstacked into `wide`, and how do you make the round trip exact?

level: seniorimportance: should knowfreq 35%

basics

~20 s

unstack writes NaN into every station and date pair that had no row, and in pandas 3.0 stack keeps every cell, NaN included. Chain dropna() and cast back, or unstack with fill_value, to restore the original rows and dtype.

open as a page