Skip to content
Bamboo Weekly

pandas stack

Push the columns down into the row index — and, since Pandas 3, keep the holes.

Which direction are you moving? unstack lifts an index level up into the columns. stack does the opposite: it takes a column level and folds it down into the index, turning a rectangular table into a long series with one row per cell. That page covers the wide-versus-long idea and the pair as inverses. This one is about the thing that makes stack different in Pandas 3, which is that it no longer throws anything away.

Official documentation: DataFrame.stack.

The arguments that earn their keep

df.stack()          # fold the innermost column level down (the default, -1)
df.stack(0)         # fold the outermost level instead
df.stack('metric')  # same thing, by name

There is one argument now: level. The other three are gone in all but name. dropna= and sort= raise ValueError if you pass them, and future_stack= still exists but defaults to True and does nothing — which matters, because older Bamboo Weekly solutions pass future_stack=True to silence a Pandas 2 warning and still run fine today.

A worked example, on real data

Bamboo Weekly #125 uses a FRED download of daily exchange rates: one row per date, one column per currency, going back to 1971. Series start at different times, so the table is full of gaps.

import pandas as pd

url = 'https://www.bambooweekly.com/content/files/2025/07/bw-125.csv'

df = pd.read_csv(url, index_col='observation_date',
                 parse_dates=['observation_date'])

df.shape

(14215, 12)

Stack it, and every cell becomes a row keyed by date and currency:

df.stack().tail(4)

observation_date            
2025-06-27        DEXVZUS       106.5948
                  DTWEXAFEGS    110.3921
                  DEXINUS        85.4500
                  DEXBZUS         5.4801
dtype: float64

Here is the change, in two numbers:

df.stack().shape, df.stack().dropna().shape

((170580,), (112299,))

14,215 × 12 is 170,580 cells, and that is exactly what Pandas 3 gives back. Pandas 2 gave you 112,299 — the non-empty ones. Fifty-eight thousand rows appeared in an upgrade, and nothing warned anybody.

Why fold a table down at all? Because a series has methods a data frame does not. There is no two-dimensional idxmax, so the question "when did any currency move most in a single day, and which one?" has no answer until the table is one-dimensional:

df.pct_change().stack().abs().idxmax()

(Timestamp('2018-02-05 00:00:00'), 'DEXVZUS')

A Venezuelan redenomination, which is not really a market move. Drop that column and the list is history:

df.drop(columns='DEXVZUS').pct_change().stack().abs().nlargest(3).round(3)

observation_date         
1994-01-03        DEXCHUS    0.500
1989-12-18        DEXCHUS    0.269
1994-12-22        DEXMXUS    0.213
dtype: float64

China's 1994 devaluation of the yuan, and Mexico's peso crisis eleven months later.

level matters once the columns have more than one level:

q = df.loc['2024', ['DEXUSEU', 'DEXJPUS']].resample('QE').agg(['min', 'max'])

q.stack(0).head(4)

                               min       max
observation_date                            
2024-03-31       DEXUSEU    1.0720    1.0976
                 DEXJPUS  141.8900  151.6600
2024-06-30       DEXUSEU    1.0628    1.0890
                 DEXJPUS  151.5500  160.8800

Bare q.stack() folds min/max down instead and leaves the currencies as columns. Same data, two different questions — pick the level deliberately.

Three mistakes people make

Expecting a round trip to be symmetric. unstack builds a full rectangle, inventing NaN for combinations that never occurred; stack now hands every one of them back. So s.unstack().stack() returns more rows than s had. That is honest arithmetic, not a bug. Add .dropna() when you want the old shape.

Passing dropna=False to keep the missing values. You already have them, and the argument raises: ValueError: dropna must be unspecified as the new implementation does not introduce rows of NA values. The same goes for sort=, whose message tells you to chain sort_index instead.

Trusting a tutorial written before Pandas 3. Nearly everything published about stack says it drops missing values, because for a decade it did. If a row count you have relied on suddenly grew, this is why.

Where it shows up in Bamboo Weekly

Bamboo Weekly #72: City travel is the clearest use on the site, and free to read: three transit-mode columns get folded into the index so a scatter plot can color by mode.

Bamboo Weekly #75: Refugees, also free, stacks the inner level of a column MultiIndex so that destination and population sit side by side and can be divided.

Bamboo Weekly #175: Inflation stacks a correlation matrix into a series, then calls idxmax on it — the one-dimensional trick above, applied to a square table.

Bamboo Weekly #146: Thanksgiving travel uses stack(1) to pull the inner level of an agg result down out of the columns.

Practice it

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

Go deeper

unstack is the other half of this idea and the page to read first. melt does a similar wide-to-long move but returns flat columns rather than an index level, which is usually what a plotting library wants. The Pandas user guide on reshaping and pivot tables is the canonical treatment.

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.

See it on real data

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

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