stack(), unstack() & MultiIndex

When you group by two keys or pivot a table, pandas produces labels with more than one level — (2023, "CSK"). These MultiIndexes are everywhere in grouped data. stack() and unstack() are the two low-level tools that move one level of labels between the row index and the column header — pivot and melt are built on the same idea.

Definition

A MultiIndex (hierarchical index) is an index whose labels are tuples with several levels, e.g. (season, team); each level has its own name and values.

stack() moves the innermost column level into the row index (wide → taller/long). unstack() moves the innermost row-index level into the columns (long → wider). They are inverses of each other.

• Level — one "layer" of labels. In (2023, "CSK"), 2023 is level 0 (season) and "CSK" is level 1 (team).
• Innermost — the last level (-1) by default; pass level=0 or a level name to move a different one.
• Inverse — df.stack().unstack() gives back the original table.

Diagram

  wide DataFrame                         stacked Series (MultiIndex rows)
  team    MI  CSK  RCB                   season  team
  season                    --stack-->   2022    MI      4
  2022     4    4    8                           CSK     4
  2023     8   10    7     <--unstack--          RCB     8
  2024     4    7    7                   2023    MI      8
                                                 ...
  "team" is a column level               "team" is now the inner ROW level

Syntax

df.stack(level=-1)                 # columns level -> rows
obj.unstack(level=-1, fill_value=None)   # rows level -> columns
pd.MultiIndex.from_tuples(tuples, names=[...])
pd.MultiIndex.from_product([list1, list2], names=[...])
df.set_index(["col1", "col2"])     # build a MultiIndex from columns

Example — stack a wide table

wins = pd.DataFrame(
    {"MI": [4, 8, 4], "CSK": [4, 10, 7], "RCB": [8, 7, 7]},
    index=pd.Index([2022, 2023, 2024], name="season"),
)
wins.columns.name = "team"
s = wins.stack()
print(s)
print(s.loc[2023])
print(s.loc[(2023, "CSK")])

season  team
2022    MI       4
        CSK      4
        RCB      8
2023    MI       8
        CSK     10
        RCB      7
2024    MI       4
        CSK      7
        RCB      7
dtype: int64
team
MI      8
CSK    10
RCB     7
dtype: int64
10

How it works: the only column level (team) moved into the rows, so the 3×3 DataFrame became a Series of 9 values with a two-level index. Blank spaces under 2022 mean "same as above". s.loc[2023] selects by the outer level (all teams in 2023); a tuple selects one exact value.

Example — unstack, and choosing the level

print(s.unstack())          # inner level (team) -> columns
print(s.unstack(level=0))   # outer level (season) -> columns

team    MI  CSK  RCB
season
2022     4    4    8
2023     8   10    7
2024     4    7    7
season  2022  2023  2024
team
MI         4     8     4
CSK        4    10     7
RCB        8     7     7

s.unstack(level="season") is the same as level=0 — names are clearer than numbers.

Example — groupby + unstack (the most common use)

inn = pd.DataFrame({
    "season": [2023, 2023, 2023, 2023, 2023, 2024, 2024, 2024, 2024, 2024],
    "team":   ["MI", "MI", "CSK", "CSK", "RCB", "MI", "CSK", "CSK", "RCB", "RCB"],
    "score":  [192, 165, 210, 178, 189, 201, 176, 220, 241, 187],
})
g = inn.groupby(["season", "team"])["score"].sum()
print(g.unstack())

team    CSK   MI  RCB
season
2023    388  357  189
2024    396  201  428

A two-key groupby returns a MultiIndex Series; unstack() spreads the inner key across the columns — exactly what pivot_table does internally.

Example — missing combinations and fill_value

part = pd.Series([8, 10, 4], index=pd.MultiIndex.from_tuples(
    [(2023, "MI"), (2023, "CSK"), (2024, "MI")], names=["season", "team"]))
print(part.unstack())
print(part.unstack(fill_value=0))

team     CSK   MI
season
2023    10.0  8.0
2024     NaN  4.0
team    CSK  MI
season
2023     10   8
2024      0   4

Note (pandas 3): stack() now keeps NaN values (a new implementation). In pandas 2 it silently dropped rows whose value was NaN by default, and passing dropna= now raises an error. Use .dropna() after stacking if you want them gone.

Working with a MultiIndex

With MultiIndex columns (e.g. after unstacking a frame with several value columns), stack() moves the inner column level back down:

u = inn.assign(won=[1, 0, 1, 1, 0, 1, 0, 1, 1, 0]).groupby(["season", "team"])[["score", "won"]].sum().unstack()
print(u)

       score           won
team     CSK   MI  RCB CSK MI RCB
season
2023     388  357  189   2  1   0
2024     396  201  428   1  1   1

stack/unstack vs melt/pivot

Common mistakes

• ❌ Selecting a MultiIndex cell with two separate brackets on a Series. ✅ Use a tuple: s.loc[(2023, "CSK")].
• ❌ Unstacking the wrong level. ✅ Name it: unstack(level="season").
• ❌ Unstacking an index with duplicate entries → ValueError: Index contains duplicate entries, cannot reshape. ✅ Aggregate first (groupby(...).sum()).
• ❌ Expecting stack() to drop NaN like old tutorials. ✅ In pandas 3 call .dropna() yourself.

Interview tip

Q: How do you turn df.groupby(["season", "team"])["runs"].sum() into a season × team table? A: Call .unstack() — it moves the inner index level (team) into the columns. .unstack(level=0) would put seasons in the columns instead. stack() reverses it.