Skip to content
Bamboo Weekly

pandas unstack

Move a row-index level up into columns, reshaping long data into wide — and back again.

What they do

Have you ever grouped by two columns, gotten back a long series with an indented, two-level index, and thought: this is the right answer, but it is the wrong shape? That is the moment unstack exists for.

When you group by two keys, Pandas gives you one row per combination, stacked vertically in a MultiIndex. That is a fine way to store the numbers and an awkward way to read them. unstack takes one level of that row index and lifts it up into the columns, turning the long result into a rectangular table you can read down and across.

stack does the reverse: it takes the columns and pushes them down into the index, turning a wide table back into a long series. The two are inverses, which is why I teach them together — there is no separate stack page here, because learning either one alone means learning half of an idea. Once you can see which direction you are moving, you know which method to call.

Official documentation: DataFrame.unstack and DataFrame.stack.

The arguments that earn their keep

s.unstack()                   # move the innermost index level into columns
s.unstack(level=0)            # move the outermost level instead
s.unstack('Region')           # same thing, by name — clearer
s.unstack(fill_value=0)       # 0 rather than NaN for absent combinations

There are only really two things to decide: which level moves, and what fills the holes.

A worked example, on real data

The Global Coal Plant Tracker lists every coal-fired generating unit on earth, one per row, with its region and whether it is operating, retired, under construction, or merely announced. Bamboo Weekly #64 used this dataset. I am deliberately reusing it here, because it is the same data behind the pivot_table page, and seeing one dataset answered two ways is the fastest route to understanding how the two methods relate.

Group by region and status, and you get a long series:

import pandas as pd

url = ('https://www.bambooweekly.com/content/files/wp-content/uploads/2024/02/'
       'global-coal-plant-tracker-january-2024.xlsx')

df = pd.read_excel(url, sheet_name='Units',
                   usecols=['Region', 'Status', 'Capacity (MW)'])

df.groupby(['Region', 'Status'])['Capacity (MW)'].sum()

Region  Status
Africa  announced        7720.0
        cancelled       71550.0
        construction     2805.0
        mothballed       2270.0
        operating       51152.6
        permitted        2575.0
Name: Capacity (MW), dtype: float64

Forty-two rows of correct numbers that no one can compare at a glance. Add unstack and the inner level, Status, becomes the columns:

df.groupby(['Region', 'Status'])['Capacity (MW)'].sum().unstack()

Status    announced  cancelled  construction  mothballed
Region
Africa       7720.0    71550.0        2805.0      2270.0
Americas      800.0    54070.0         120.0       342.9
Asia        80754.0  1553922.5      193653.5      6133.0
Europe       3560.0   104669.4         735.0     14292.0
Oceania         NaN    13436.0           NaN       460.0

Now a row is one region's entire pipeline and a column compares regions at the same stage. Asia's cancelled capacity by itself exceeds every other region's combined.

By default unstack moves the innermost level. Pass level= when you want the other one, and prefer naming it, since unstack('Region') says what you mean in a way that unstack(level=0) does not:

df.groupby(['Region', 'Status'])['Capacity (MW)'].sum().unstack('Region')

Region         Africa  Americas       Asia    Europe  Oceania
Status
announced      7720.0     800.0    80754.0    3560.0      NaN
cancelled     71550.0   54070.0  1553922.5  104669.4  13436.0
construction   2805.0     120.0   193653.5     735.0      NaN
mothballed     2270.0     342.9     6133.0   14292.0    460.0
operating     51152.6  221444.0  1667465.4  167153.5  22903.0

Same numbers, transposed question. Those NaN values are real information: Oceania has no announced or under-construction coal capacity at all. If you would rather see zeros, ask for them with fill_value=0, which turns Oceania's row into 0.0, 13436.0, 0.0, 460.0.

Going the other way, .stack() on that table returns the MultiIndex series you started with.

Where it shows up in Bamboo Weekly

Twenty-five Bamboo Weekly exercises use unstack on real data. Four worth studying, all of them free to read:

Bamboo Weekly #52: Border encounters groups by encounter type, resamples by month, then calls unstack(level=0) to get one column per encounter type — precisely the shape plot.line needs.

Bamboo Weekly #34: House of Representatives unstacks years into columns so that diff(axis='columns') can show which states gained and lost congressional seats between 2020 and 2022.

Bamboo Weekly #78: Stock markets uses unstack('source') to give each index its own column, which makes a correlation matrix across markets a single further method call.

Bamboo Weekly #72: City travel goes the other direction, using stack to fold three transit-mode columns down into the index so the data can drive a scatter plot.

Four mistakes people make

Unstacking the wrong level. unstack defaults to the innermost index level, which is the second key you passed to groupby. If your table comes out transposed from what you pictured, you do not need to rethink the grouping — you need level=. Name the level rather than numbering it and this class of mistake mostly disappears.

Reading NaN as zero. Unstacking creates a full rectangle, so every region gets a cell for every status whether or not any plant matched. An empty cell means no rows had that combination, which is not the same as a measured zero. Reach for fill_value=0 when the distinction genuinely does not matter for your question, and leave the NaN visible when it does — often the holes are the finding.

Assuming stack still drops missing values. It used to, and a great deal of advice online still says so. As of Pandas 3, the rewritten stack preserves them, and passing dropna= now raises a ValueError telling you the argument is on its way out. This makes round trips honest but not always symmetric: unstacking those 42 grouped rows produced three NaN cells, so stacking the result gives 45 rows, not the original 42. If you want them gone, say .stack().dropna() and mean it.

Reaching for unstack when pivot_table is clearer. See below — they overlap far more than people expect.

unstack or pivot_table?

They can produce identical results. On this dataset, df.pivot_table(index='Region', columns='Status', values='Capacity (MW)', aggfunc='sum') returns a table that compares equal to the groupby plus unstack above. Same numbers, same shape.

So the choice is about intent. pivot_table does the aggregating and the reshaping in one call, and reads well when a cross-tabulation is the destination. groupby plus unstack separates the two steps, which is what you want when the aggregation is interesting on its own, when you need something more expressive than aggfunc accepts, or when reshaping is one link in a longer chain — as in resample, then unstack, then plot. I reach for pivot_table when I want a table to look at, and for groupby plus unstack when I want a shape to keep working with.

Practice it

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

Go deeper

The pivot_table and groupby pages cover the two methods that most often sit on either side of an unstack in a real chain. The Pandas user guide on reshaping and pivot tables is the canonical treatment of the long-versus-wide idea.

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.

See it on real data

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

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