melt in pandas — practice examples (Bamboo Weekly)

One column per year is a spreadsheet. One row per country and year is a data frame.

Has a file ever arrived with 2014, 2015, 2016 across the top? It reads beautifully and computes terribly. You cannot group by year, because year is not a column. You cannot color a chart by year, because year is not a column. You cannot filter to the last five years without naming five columns.

melt fixes the shape in one call. It takes a set of columns, and turns their names into the values of one new column and their contents into the values of another. Long, narrow, and boring to look at — which is the shape the rest of Pandas is built for.

It is the inverse of pivot, and a close relative of stack. The difference from stack is worth holding on to: stack folds columns down into the index, while melt folds them into ordinary columns and throws the old index away. Plotly and groupby both want columns, so melt is usually the one you want.

Official documentation: DataFrame.melt.

The arguments that earn their keep

df.melt(id_vars='Country',      # columns to keep as they are, repeated per row
        value_vars=[2014, 2015],# columns to unpivot (default: everything else)
        var_name='year',        # name for the column of old column names
        value_name='pct')       # name for the column of values

id_vars is the anchor, and the one to get right. Anything you do not list there and do not list in value_vars is simply dropped. var_name and value_name default to variable and value, which are fine for one step of a chain and terrible in a saved file.

A worked example, on real data

NATO publishes defense spending as a share of GDP, one row per ally and one column per year — the table behind Bamboo Weekly #124. The sheet has a title block above the data and a second table below it, so the read takes a few arguments:

import pandas as pd

url = ('https://www.bambooweekly.com/content/files/2025/06/'
       '240617-def-exp-2024-TABLES-en.xlsx')

nato = (
    pd.read_excel(url, sheet_name='TABLE3', header=7, usecols='C:N', nrows=33)
    .dropna(how='all')
    .reset_index(drop=True)
    .rename(columns={'Unnamed: 2': 'Country'})
)

nato.iloc[:4, :6].round(2)

    Country  2014  2015  2016  2017  2018
0   Albania  1.35  1.16  1.10  1.11  1.16
1   Belgium  0.97  0.91  0.89  0.88  0.89
2  Bulgaria  1.31  1.25  1.24  1.22  1.45
3    Canada  1.01  1.20  1.16  1.44  1.30

31 allies, 11 years. Melt it and the years come down off the header:

long = nato.melt(id_vars='Country', var_name='year', value_name='pct_of_gdp')

long.head(4)

    Country  year  pct_of_gdp
0   Albania  2014    1.346517
1   Belgium  2014    0.971016
2  Bulgaria  2014    1.309083
3    Canada  2014    1.006365

341 rows, three columns, and now the interesting question is one groupby away — how many allies met the 2 percent target in each year?

(
    long
    .assign(year=lambda df_: df_['year'].astype(str).str.rstrip('ef').astype(int))
    .groupby('year')['pct_of_gdp']
    .agg(mean='mean', at_target=lambda s: (s >= 2).sum())
    .round(2)
)

      mean  at_target
year                 
2014  1.37          3
2015  1.38          5
2016  1.40          5
2017  1.43          4
2018  1.49          6
2019  1.61          7
2020  1.71          9
2021  1.68          6
2022  1.72          7
2023  1.92         10
2024  2.20         23

Three allies at 2 percent in 2014, twenty-three a decade later. None of that is reachable while the years are column headers.

Notice the str.rstrip('ef') in there. That is the price of melting a header row that NATO wrote by hand: three of the eleven year labels are the strings '2019', '2023e' and '2024e' while the rest are integers, so the new year column comes out as object. Column names become data, and any mess in the names becomes mess in the data.

Three mistakes people make

Forgetting id_vars. nato.melt() runs happily and returns 372 rows in which country names and GDP percentages share a single value column, because Country was unpivoted along with everything else. No warning, no error, just a frame that is quietly nonsense. If a column identifies the row, it belongs in id_vars.

Expecting the index to survive. melt resets it. If the labels you care about are in the index rather than in a column, either call reset_index first so they become an id_vars candidate, or pass ignore_index=False to carry the old index through and let it repeat down the result.

Melting when you meant to aggregate. melt never combines anything; it only rearranges. If the wide columns should be summed or averaged rather than stacked up, you want pivot_table or groupby after the melt.

Where it shows up in Bamboo Weekly

Bamboo Weekly #19: Working women is the textbook case, and free to read: a BLS spreadsheet with a Year column and twelve month columns, melted with id_vars='Year' and var_name='month' so the whole series can be put on a time axis.

Bamboo Weekly #172: World Cup uses melt to undo one-hot encoding, twice. Three 0/1 columns for home win, away win and draw collapse into one variable column that value_counts can count.

Bamboo Weekly #166: Income tax melts five tax-type columns into Tax Type and % of GDP, which is precisely the long form px.bar needs to stack bars by color.

Bamboo Weekly #171: Hantavirus melts every datetime column at once via select_dtypes, then pivots the result back the other way to count events per day.

Practice it

Work through a melt exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/melt/

Go deeper

pivot is melt run backwards, and stack is the version that folds columns into the index instead. The Pandas user guide on reshaping and pivot tables puts all of them side by side.

More Pandas videos on Python and Pandas with Reuven Lerner.

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

.stack() — when the result should stay indexed rather than become long-form columns
.pivot() — for the opposite direction, long back to wide
.unstack() — when the wide columns are an index level rather than ordinary columns

See it on real data

Below are the 4 Bamboo Weekly exercises that use melt on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.

pandas melt