In pandas, how do you use `melt` to turn a wide survey export with one column per question back into one row per answer?
answer
- the inverse of pivot
- identifier columns stay put
- id_vars and value_vars
- var_name and value_name rename
basics
~10 sCall 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
Recall melt's id_vars, value_vars, var_name and value_name, and that rows equal input rows times melted columns.
Explain which columns are dropped when value_vars is given, why NaN answers stay, and how ignore_index changes the index.
Choose wide_to_long when headers encode two facts, and know why a missing sep returns an empty frame instead of failing.
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