melt and Reshaping Data
Real-world spreadsheets are often 'wide': each row is an entity and each column is a separate measurement or time period, such as monthly sales columns Jan, Feb, Mar. This layout is convenient for humans to read but awkward for analysis, plotting, or grouping, because the column name itself carries information (which month) that should live in the data. pandas' pd.melt() function performs an 'unpivot': it takes a wide DataFrame and reshapes it into 'long' or 'tidy' format, where each row represents one observation and columns describe what that observation is (an id, a variable name, and a value). This tidy-data principle, borrowed from Hadley Wickham's work in R, makes downstream operations like groupby, plotting with seaborn, or merging far simpler because every variable has its own column and every row is a single record.
Cricket analogy: A wide scorecard with separate columns for each innings (Innings1, Innings2, Innings3) is convenient to glance at but awkward to analyze; pd.melt() unpivots it into tidy rows of player, innings label, and runs, making groupby averages straightforward.
The Anatomy of pd.melt()
pd.melt(df, id_vars, value_vars, var_name, value_name) takes several key arguments. id_vars are the columns to keep as identifiers unchanged (e.g. 'student', 'region') — they get repeated for every melted row. value_vars are the columns you want to unpivot into rows; if omitted, all non-id columns are melted. var_name controls the name of the new column holding the original column headers (defaults to 'variable'), and value_name controls the name of the column holding the actual data values (defaults to 'value'). The number of resulting rows equals the number of original rows multiplied by the number of value_vars columns, so melting a 100-row, 4-month-column DataFrame produces 400 rows.
Cricket analogy: In pd.melt() on a wide scorecard, id_vars like 'player' stay fixed, value_vars like the innings columns get unpivoted into rows, var_name labels the new 'innings' column, and value_name labels the 'runs' column, so melting 20 players across 4 innings columns yields 80 rows.
import pandas as pd
wide = pd.DataFrame({
'student': ['Amir', 'Priya', 'Toma'],
'math': [88, 92, 79],
'science': [91, 85, 88],
'history': [76, 90, 95]
})
long = pd.melt(
wide,
id_vars='student',
value_vars=['math', 'science', 'history'],
var_name='subject',
value_name='score'
)
print(long)
# student subject score
# 0 Amir math 88
# 1 Priya math 92
# 2 Toma math 79
# 3 Amir science 91
# ... (9 rows total: 3 students x 3 subjects)
# groupby now works naturally on the tidy frame
avg_by_subject = long.groupby('subject')['score'].mean()
print(avg_by_subject)Melting Back with pivot
The inverse operation of melt is pivot (or pivot_table when duplicates need aggregating). df.pivot(index='student', columns='subject', values='score') would take the long DataFrame above and reconstruct the original wide layout. Because pivot requires each (index, columns) combination to be unique, it raises an error on duplicate entries — pivot_table should be used instead when duplicates exist, since it aggregates them with a function like mean. Round-tripping between melt and pivot is a common workflow: melt for tidy storage and analysis, pivot for presentation or export to a wide report format.
Cricket analogy: df.pivot(index='player', columns='innings', values='runs') rebuilds the original wide scorecard from tidy rows, but if a player somehow has two entries for the same innings it errors, requiring pivot_table with mean to aggregate the duplicates instead.
Long/tidy format is also what most plotting libraries expect. seaborn's sns.lineplot(data=long, x='subject', y='score', hue='student') works directly on melted data but would need awkward column indexing on the wide version.
A frequent mistake is melting a DataFrame that still has a meaningful index (e.g. a date index). Since melt operates on columns, the index is dropped by default unless you first reset_index() to turn it into a regular column and include it in id_vars.
wide_to_long for Structured Column Names
When wide columns encode a stub name plus a suffix, such as 'score_2023' and 'score_2024', pd.wide_to_long() is more convenient than melt because it automatically splits the stub from the suffix and creates a proper long DataFrame with a MultiIndex. It requires the stubnames, an i (id column), and a j (name for the suffix column), and is especially useful for panel/longitudinal datasets where multiple measurement variables share the same suffix pattern.
Cricket analogy: Columns like 'runs_2023' and 'runs_2024' encode the stub 'runs' plus a year suffix; pd.wide_to_long() splits them automatically into a proper long DataFrame with a MultiIndex, ideal for tracking a player's career stats year over year.
- pd.melt() converts wide-format data into long/tidy format where each row is one observation.
- id_vars stay fixed per row; value_vars become the rows being unpivoted, split into var_name and value_name columns.
- Tidy format simplifies groupby, filtering, and plotting because each variable occupies its own column.
- pivot() is the inverse of melt but fails on duplicate index/column combinations; pivot_table() aggregates duplicates instead.
- wide_to_long() handles columns with stub+suffix naming patterns like score_2023/score_2024 more directly than melt.
- Always reset_index() before melting if the index carries meaningful information you want to preserve.
Practice what you learned
1. What does pd.melt() primarily do to a DataFrame?
2. In pd.melt(df, id_vars='student', value_vars=['math','science']), what determines which columns are unpivoted into rows?
3. If a wide DataFrame has 50 rows and you melt 4 value columns, how many rows will the resulting long DataFrame have?
4. Why might df.pivot() raise an error where pivot_table() would not?
5. Which function is best suited for columns named 'revenue_2022', 'revenue_2023', 'revenue_2024'?
Was this page helpful?
You May Also Like
Pivot Tables
How pandas' pivot_table reshapes long-format data into a summarized cross-tabulation, aggregating values across row and column groupings similar to a spreadsheet pivot table.
GroupBy Basics
Understand pandas' split-apply-combine model via groupby(), including grouping keys, selecting columns, and iterating over groups.
Concatenating DataFrames
Using pandas' concat function to stack DataFrames or Series along rows or columns, and how it differs from merge in purpose and alignment behavior.
Crosstabs and Pivot Analysis
Use pd.crosstab() to build frequency and cross-tabulation tables between categorical variables, and compare it to pivot_table for richer aggregation.