Melt (Unpivot)
Python · Reshaping & Workflow
Theory
melt() is the reverse of a pivot: it turns columns into rows, changing a wide table into a long one. df.melt(id_vars=['city'], value_vars=['Jan', 'Feb', 'Mar'], var_name='month', value_name='amount') keeps city as an identifier and creates one row per (city, month), with the old column name in 'month' and the cell value in 'amount'. Leave out value_vars to melt every column that is not an id_var.
Wide data is common in spreadsheets and exports (one column per month, per year or per question in a survey), but most pandas operations - groupby, filtering, plotting with a hue, joining to a calendar - work best on long, tidy data where each row is one observation. Rows come out grouped by original column (all Jan rows, then all Feb rows); sort afterwards if you need city order. pd.wide_to_long() handles column names with a common prefix like sales_2023, sales_2024.
Why we use it
Spreadsheets store periods as columns, which makes 'total per month' or 'trend over time' awkward. Melting puts the period into a column so the usual groupby, filter and plot tools work.
When to use it
Melt right after loading wide spreadsheet-style data, or before plotting several columns as one series. Pivot back with pivot_table only for the final report. In SQL this is UNPIVOT (or a UNION ALL per column).
How it works
- Wide table: one column per month
- Choose id_vars to keep (city)
- For each other column, emit one row per id value
- Store the column name in var_name, the cell in value_name
- Long table: city, month, amount
Syntax
df.melt(id_vars=['id'], value_vars=['c1', 'c2'], var_name='variable', value_name='value')Example
long = wide.melt(id_vars='city', var_name='month', value_name='amount')
print(long.groupby('month')['amount'].sum())Key points
- id_vars stay as identifiers; the other columns become rows
- var_name and value_name name the two new columns
- Rows come out grouped by original column - sort if needed
- Long (tidy) data is what groupby, merge and plotting expect
- SQL equivalent: UNPIVOT (amount FOR month IN (Jan, Feb, Mar))