Stack and unstack in Data Analysis Python - Mini Project: Build & Apply

Stack and Unstack in Pandas

📖 Scenario: You work in a small company that tracks sales data for different products across regions. The data is stored in a table format with multi-level columns representing product categories and subcategories.You want to learn how to reshape this data to analyze it better by stacking and unstacking the table.

🎯 Goal: Learn how to use stack() and unstack() methods in pandas to reshape multi-index DataFrames for easier analysis.

📋 What You'll Learn

Create a pandas DataFrame with multi-level columns representing product categories and subcategories.

Create a variable to select a specific level for stacking.

Use the stack() method to reshape the DataFrame by stacking the specified level.

Print the reshaped DataFrame to see the stacked result.

💡 Why This Matters

🌍 Real World

Stacking and unstacking data is useful when you want to convert data between wide and long formats. This helps in preparing data for analysis or visualization.

💼 Career

Data analysts and scientists often reshape data to fit the needs of different tools or to simplify complex datasets for reporting and modeling.

1

Create a DataFrame with Multi-Level Columns

Create a pandas DataFrame called sales with the following data and multi-level columns. The columns should have two levels: the first level is 'Fruit' and 'Vegetable', and the second level is 'Apples', 'Oranges' under 'Fruit', and 'Carrots', 'Potatoes' under 'Vegetable'. The rows are indexed by 'Region' with values 'North' and 'South'. The data values are: North - Apples: 10, Oranges: 15, Carrots: 7, Potatoes: 12; South - Apples: 20, Oranges: 25, Carrots: 14, Potatoes: 18.

Data Analysis Python

# Create the DataFrame sales with multi-level columns and index
# Your code here

Hint

Use pd.MultiIndex.from_tuples to create multi-level columns and pass it to the columns parameter of pd.DataFrame.

2

Set the Level to Stack

Create a variable called level_to_stack and set it to 1 to indicate that you want to stack the second level of the columns (the product names).

Data Analysis Python

import pandas as pd

columns = pd.MultiIndex.from_tuples([
    ('Fruit', 'Apples'),
    ('Fruit', 'Oranges'),
    ('Vegetable', 'Carrots'),
    ('Vegetable', 'Potatoes')
])

data = [
    [10, 15, 7, 12],
    [20, 25, 14, 18]
]

sales = pd.DataFrame(data, index=['North', 'South'], columns=columns)

# Create a variable level_to_stack and set it to 1
# Your code here

Hint

Set level_to_stack to the integer 1 to select the second level of columns.

3

Stack the DataFrame

Use the stack() method on the sales DataFrame with the parameter level=level_to_stack and assign the result to a new variable called stacked_sales.

Data Analysis Python

import pandas as pd

columns = pd.MultiIndex.from_tuples([
    ('Fruit', 'Apples'),
    ('Fruit', 'Oranges'),
    ('Vegetable', 'Carrots'),
    ('Vegetable', 'Potatoes')
])

data = [
    [10, 15, 7, 12],
    [20, 25, 14, 18]
]

sales = pd.DataFrame(data, index=['North', 'South'], columns=columns)

level_to_stack = 1

# Use stack() on sales with level=level_to_stack and assign to stacked_sales
# Your code here

Hint

Call sales.stack(level=level_to_stack) and assign it to stacked_sales.

4

Print the Stacked DataFrame

Print the variable stacked_sales to display the reshaped DataFrame after stacking.

Data Analysis Python

import pandas as pd

columns = pd.MultiIndex.from_tuples([
    ('Fruit', 'Apples'),
    ('Fruit', 'Oranges'),
    ('Vegetable', 'Carrots'),
    ('Vegetable', 'Potatoes')
])

data = [
    [10, 15, 7, 12],
    [20, 25, 14, 18]
]

sales = pd.DataFrame(data, index=['North', 'South'], columns=columns)

level_to_stack = 1

stacked_sales = sales.stack(level=level_to_stack)

# Print stacked_sales
# Your code here

Hint

Use print(stacked_sales) to show the stacked DataFrame.

Practice

1. What does the stack() method do to a DataFrame in pandas?

easy

A. It moves columns into rows, making the data longer.

B. It moves rows into columns, making the data wider.

C. It sorts the DataFrame by index.

D. It removes duplicate rows from the DataFrame.

Solution

1. Step 1: Understand the stack operation

   The stack() method compresses columns into a single column, turning columns into rows.

2. Step 2: Compare with unstack

   Unlike unstack(), which moves rows to columns, stack() makes data longer by stacking columns.

3. Final Answer:

   It moves columns into rows, making the data longer. -> Option A

4. Quick Check:

   Stack = columns to rows [OK]

Hint: Stack compresses columns into rows, making data longer [OK]

Common Mistakes:

• Confusing stack with unstack direction
• Thinking stack sorts data
• Assuming stack removes duplicates

2. Which of the following is the correct syntax to unstack the last level of a MultiIndex DataFrame df?

easy

A. df.stack(axis=0)

B. df.stack(level=-1)

C. df.unstack(level=-1)

D. df.unstack(axis=1)

Solution

1. Step 1: Recall unstack syntax

   The unstack() method by default unstacks the last level of the index, and level=-1 explicitly specifies this.

2. Step 2: Differentiate from stack

   stack() is the opposite operation, so options A and B are incorrect. unstack() does not take an axis parameter, so df.unstack(axis=1) is invalid.

3. Final Answer:

   df.unstack(level=-1) -> Option C

4. Quick Check:

   Unstack last level = df.unstack(level=-1) [OK]

Hint: Use unstack(level=-1) to unstack last index level [OK]

Common Mistakes:

• Using stack instead of unstack
• Passing axis parameter to unstack
• Not specifying level when needed

3. Given the DataFrame df below:

import pandas as pd
d = {('A', 'x'): 1, ('A', 'y'): 2, ('B', 'x'): 3, ('B', 'y'): 4}
df = pd.Series(d).unstack()

What is the output of df.stack()?

medium

A. A DataFrame with columns 'A' and 'B' stacked

B. A Series with MultiIndex where second level is stacked into rows

C. A DataFrame with columns 'x' and 'y' unchanged

D. An error because stack cannot be applied here

Solution

1. Step 1: Understand the initial DataFrame

   The unstack() converts the Series with MultiIndex into a DataFrame with columns 'x' and 'y' and index 'A' and 'B'.

2. Step 2: Apply stack() on the DataFrame

   Calling stack() moves the columns 'x' and 'y' back into the inner level of the row index, producing a Series with MultiIndex.

3. Final Answer:

   A Series with MultiIndex where second level is stacked into rows -> Option B

4. Quick Check:

   Stack reverses unstack, making columns into row index [OK]

Hint: Stack reverses unstack, turning columns back into row index [OK]

Common Mistakes:

• Expecting a DataFrame output instead of Series
• Confusing which index level is stacked
• Thinking stack causes error here

4. You run the code below and get an error:

df = pd.DataFrame({
  'A': [1, 2],
  'B': [3, 4]
})
stacked = df.stack(level=1)

What is the cause of the error?

medium

A. The DataFrame index is missing.

B. Stack requires numeric data only.

C. Stack cannot be used on DataFrames, only Series.

D. DataFrame columns have no MultiIndex, so level=1 is invalid.

Solution

1. Step 1: Check DataFrame columns

   The DataFrame has simple columns 'A' and 'B', which are not a MultiIndex, so specifying level=1 is invalid.

2. Step 2: Understand stack level parameter

   The level parameter in stack() refers to the level of the columns MultiIndex to stack. Without MultiIndex columns, level=1 causes an error.

3. Final Answer:

   DataFrame columns have no MultiIndex, so level=1 is invalid. -> Option D

4. Quick Check:

   Stack level requires MultiIndex columns [OK]

Hint: Stack level needs MultiIndex columns; simple columns cause error [OK]

Common Mistakes:

• Assuming stack only works on Series
• Thinking stack requires numeric data
• Ignoring the column index type

5. You have a DataFrame df with a MultiIndex on rows (levels: 'City', 'Year') and columns as years (e.g., 2020, 2021). You want to reshape it so that the 'Year' level from rows becomes columns, and the columns years become a new row level. Which sequence of operations correctly achieves this?

hard

A. First unstack the 'Year' level from rows, then stack the columns.

B. First stack the columns, then unstack the 'City' level from rows.

C. Use only stack on the columns without unstacking rows.

D. Use only unstack on the 'City' level.

Solution

1. Step 1: Unstack the 'Year' level from rows

   Unstacking the 'Year' level moves it from the row index to columns, widening the DataFrame.

2. Step 2: Stack the columns

   Stacking the columns moves the columns years into a new row index level, making the data longer again but reshaped as desired.

3. Final Answer:

   First unstack the 'Year' level from rows, then stack the columns. -> Option A

4. Quick Check:

   Unstack rows then stack columns reshapes MultiIndex correctly [OK]

Hint: Unstack row level first, then stack columns to reshape MultiIndex [OK]

Common Mistakes:

• Stacking before unstacking causes wrong shape
• Unstacking wrong index level
• Ignoring the need to reshape both rows and columns