Pandas: Pivot Columns to MultiIndex Rows with stack()

Pivot column levels into row MultiIndex levels using the Pandas stack() method. Compress wide data structures into multi-level index rows while preserving structured hierarchy.

Show Table of Contents ↓

Introduction to stack() 🔝

The stack() method pivots column labels into inner row MultiIndex levels. It turns a wide DataFrame into a compact MultiIndex Series or DataFrame, making hierarchical data representation effortless.

Create multi-header sample dataset 🔝

We start with a multi-level column DataFrame containing term-wise marks for students.

import pandas as pd
cols = pd.MultiIndex.from_tuples([("MATH", "Term1"), ("MATH", "Term2"), ("ENGLISH", "Term1"), ("ENGLISH", "Term2")])
df = pd.DataFrame([[85, 90, 78, 82], [88, 92, 80, 85]], index=["Ravi", "Raju"], columns=cols)
print(df)
assert df.shape == (2, 4)

Expected output

      MATH       ENGLISH      
     Term1 Term2   Term1 Term2
Ravi    85    90      78    82
Raju    88    92      80    85

Basic stack operation 🔝

By default, calling df.stack() stacks the innermost column level (level=-1) into the row index.

stacked = df.stack()
print(stacked)
assert stacked.index.nlevels == 2

Expected output

            ENGLISH  MATH
Ravi Term1       78    85
     Term2       82    90
Raju Term1       80    88
     Term2       85    92

Stacking specific levels 🔝

Specify level=0 to stack the outer subject level into rows instead of the term level.

stacked_l0 = df.stack(level=0)
print(stacked_l0)
assert "MATH" in stacked_l0.index.get_level_values(1)

Expected output

             Term1  Term2
Ravi ENGLISH    78     82
     MATH       85     90
Raju ENGLISH    80     85
     MATH       88     92

Handling missing values with dropna 🔝

By default, dropna=True drops resultant missing values. Set dropna=False to retain NaN entries.

df_nan = df.copy()
df_nan.iloc[0, 0] = None
stacked_with_nan = df_nan.stack(future_stack=True)
print(stacked_with_nan)
assert pd.isna(stacked_with_nan.loc[("Ravi", "Term1"), "MATH"])

Expected output

            ENGLISH  MATH
Ravi Term1       78   NaN
     Term2       82  90.0
Raju Term1       80  88.0
     Term2       85  92.0

Exercise: stack student subject scores 🔝

Create a MultiIndex column DataFrame for 2 students across 2 subjects (Math, Science) and stack the subject columns into rows. Verify that a 2-level MultiIndex row Series/DataFrame is formed.

Exercise solution 🔝

Solution constructing the multi-level DataFrame and applying stack().

ex_df = pd.DataFrame([[90, 95], [80, 85]], index=["Ravi", "Raju"], columns=["Math", "Science"])
answer = ex_df.stack()
print(answer)
assert len(answer) == 4 and answer.index.nlevels == 2

Expected output

Ravi  Math       90
      Science    95
Raju  Math       80
      Science    85
dtype: int64

Practice in Google Colab 🔝

Interactive Colab Notebook

Practice the stack() examples interactively directly in your browser:

View on GitHub

Reference: Pandas stack() documentation.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here