Setting columns as MultiIndex in Pandas - Mini Project: Build & Apply

Setting columns as MultiIndex

📖 Scenario: You work in a small company that tracks sales data. The data is organized by product categories and months. You want to organize the columns in a table to show this hierarchy clearly.

🎯 Goal: Create a pandas DataFrame with sales data, then set the columns as a MultiIndex to group by product category and month.

📋 What You'll Learn

Create a pandas DataFrame with sales data for two product categories and two months.

Create a MultiIndex for the columns using product categories and months.

Set the DataFrame columns to this MultiIndex.

Print the DataFrame to see the MultiIndex columns.

💡 Why This Matters

🌍 Real World

Organizing sales or financial data with multiple categories helps businesses analyze performance clearly.

💼 Career

Data analysts and scientists often use MultiIndex in pandas to handle complex datasets with hierarchical relationships.

1

Create the sales data DataFrame

Import pandas as pd. Create a DataFrame called sales with these columns and values: 'Product A_Jan': [100, 150], 'Product A_Feb': [120, 130], 'Product B_Jan': [90, 110], 'Product B_Feb': [95, 105]. Use index labels ['Store 1', 'Store 2'].

Pandas

# Import pandas and create the sales DataFrame with the specified columns and values
# Your code here

Hint

Use pd.DataFrame with a dictionary for columns and a list for the index.

2

Create the MultiIndex for columns

Create a MultiIndex called multi_cols from the list of tuples: [('Product A', 'Jan'), ('Product A', 'Feb'), ('Product B', 'Jan'), ('Product B', 'Feb')]. Use pd.MultiIndex.from_tuples().

Pandas

import pandas as pd

sales = pd.DataFrame({
    'Product A_Jan': [100, 150],
    'Product A_Feb': [120, 130],
    'Product B_Jan': [90, 110],
    'Product B_Feb': [95, 105]
}, index=['Store 1', 'Store 2'])

# Create the MultiIndex for columns
# Your code here

Hint

Use pd.MultiIndex.from_tuples() with the list of tuples for product and month.

3

Set the DataFrame columns to the MultiIndex

Set the sales.columns to the MultiIndex variable multi_cols.

Pandas

import pandas as pd

sales = pd.DataFrame({
    'Product A_Jan': [100, 150],
    'Product A_Feb': [120, 130],
    'Product B_Jan': [90, 110],
    'Product B_Feb': [95, 105]
}, index=['Store 1', 'Store 2'])

multi_cols = pd.MultiIndex.from_tuples([
    ('Product A', 'Jan'),
    ('Product A', 'Feb'),
    ('Product B', 'Jan'),
    ('Product B', 'Feb')
])

# Set the sales DataFrame columns to the MultiIndex
# Your code here

Hint

Assign multi_cols to sales.columns.

4

Print the DataFrame with MultiIndex columns

Print the sales DataFrame to display the columns as a MultiIndex.

Pandas

import pandas as pd

sales = pd.DataFrame({
    'Product A_Jan': [100, 150],
    'Product A_Feb': [120, 130],
    'Product B_Jan': [90, 110],
    'Product B_Feb': [95, 105]
}, index=['Store 1', 'Store 2'])

multi_cols = pd.MultiIndex.from_tuples([
    ('Product A', 'Jan'),
    ('Product A', 'Feb'),
    ('Product B', 'Jan'),
    ('Product B', 'Feb')
])

sales.columns = multi_cols

# Print the sales DataFrame
# Your code here

Hint

Use print(sales) to show the DataFrame with MultiIndex columns.

Practice

1. What is the main purpose of setting columns as a MultiIndex in a pandas DataFrame?

easy

A. To sort columns alphabetically

B. To convert columns into rows

C. To remove duplicate columns

D. To organize columns with multiple header levels for better grouping

Solution

1. Step 1: Understand MultiIndex columns

   MultiIndex columns allow multiple levels of headers, grouping related columns together.

2. Step 2: Identify the purpose

   This organization helps in better data analysis and clarity by grouping related columns under common headers.

3. Final Answer:

   To organize columns with multiple header levels for better grouping -> Option D

4. Quick Check:

   MultiIndex = multiple header levels [OK]

Hint: MultiIndex groups columns under multiple headers [OK]

Common Mistakes:

• Confusing MultiIndex with row indexing
• Thinking MultiIndex removes duplicates
• Assuming MultiIndex sorts columns automatically

2. Which of the following is the correct syntax to set MultiIndex columns from a list of tuples cols in pandas?

easy

A. df.columns = pd.MultiIndex.from_tuples(cols)

B. df.columns = pd.MultiIndex(cols)

C. df.columns = MultiIndex.from_tuples(cols)

D. df.columns = pd.MultiIndex.tuples(cols)

Solution

1. Step 1: Recall the correct method

   The pandas method to create MultiIndex from tuples is pd.MultiIndex.from_tuples().

2. Step 2: Match syntax

   Assigning df.columns = pd.MultiIndex.from_tuples(cols) correctly sets MultiIndex columns.

3. Final Answer:

   df.columns = pd.MultiIndex.from_tuples(cols) -> Option A

4. Quick Check:

   Use from_tuples() to create MultiIndex [OK]

Hint: Use pd.MultiIndex.from_tuples() to set MultiIndex columns [OK]

Common Mistakes:

• Using pd.MultiIndex() directly without from_tuples
• Missing pd prefix for MultiIndex
• Using a non-existent method like tuples()

3. Given the code:

import pandas as pd
cols = [('A', 'one'), ('A', 'two'), ('B', 'one'), ('B', 'two')]
data = [[1, 2, 3, 4], [5, 6, 7, 8]]
df = pd.DataFrame(data)
df.columns = pd.MultiIndex.from_tuples(cols)
print(df)

What will be the output?

medium

A. A DataFrame with MultiIndex columns showing levels 'A' and 'B' with sub-levels 'one' and 'two'

B. A DataFrame with single-level columns named 0,1,2,3

C. A DataFrame with columns named ('A', 'one'), ('A', 'two'), ('B', 'one'), ('B', 'two') as strings

D. SyntaxError due to incorrect MultiIndex assignment

Solution

1. Step 1: Create MultiIndex columns

   The list cols contains tuples representing two levels: first level 'A' or 'B', second level 'one' or 'two'.

2. Step 2: Assign MultiIndex to df.columns

   Using pd.MultiIndex.from_tuples(cols) sets these tuples as hierarchical column headers.

3. Final Answer:

   A DataFrame with MultiIndex columns showing levels 'A' and 'B' with sub-levels 'one' and 'two' -> Option A

4. Quick Check:

   MultiIndex columns display hierarchical headers [OK]

Hint: MultiIndex columns show grouped headers from tuples [OK]

Common Mistakes:

• Expecting flat column names instead of MultiIndex
• Confusing tuple strings with MultiIndex tuples
• Assuming syntax error without checking code

4. What is wrong with this code snippet for setting MultiIndex columns?

import pandas as pd
data = [[1, 2], [3, 4]]
df = pd.DataFrame(data)
cols = [('X', 'a'), ('Y', 'b')]
df.columns = pd.MultiIndex(cols)

medium

A. The list cols should be a list of strings, not tuples

B. DataFrame data shape does not match columns length

C. pd.MultiIndex() cannot take a list of tuples directly; use from_tuples()

D. MultiIndex columns must be set during DataFrame creation

Solution

1. Step 1: Check MultiIndex creation method

   The code uses pd.MultiIndex(cols), but this constructor does not accept a list of tuples directly.

2. Step 2: Correct method usage

   The correct way is to use pd.MultiIndex.from_tuples(cols) to create MultiIndex from tuples.

3. Final Answer:

   pd.MultiIndex() cannot take a list of tuples directly; use from_tuples() -> Option C

4. Quick Check:

   Use from_tuples() to create MultiIndex from tuples [OK]

Hint: Use from_tuples() to create MultiIndex from tuple list [OK]

Common Mistakes:

• Using pd.MultiIndex() directly with tuples
• Ignoring error messages about constructor arguments
• Trying to set MultiIndex columns without matching data shape

5. You have sales data for two regions and two products:

data = [[100, 200, 150, 250], [110, 210, 160, 260]]
cols = [('Region1', 'ProductA'), ('Region1', 'ProductB'), ('Region2', 'ProductA'), ('Region2', 'ProductB')]
df = pd.DataFrame(data)

How do you set the columns as a MultiIndex to group products under regions, and then select all sales for Region1?

hard

A. Set columns with pd.MultiIndex.from_product(cols) and select with df.loc[:, 'Region1']

B. Set columns with pd.MultiIndex.from_tuples(cols) and select with df.loc[:, 'Region1']

C. Set columns with pd.MultiIndex.from_tuples(cols) and select with df['Region1']

D. Set columns with pd.MultiIndex.from_tuples(cols) and select with df['ProductA']

Solution

1. Step 1: Set MultiIndex columns correctly

   Use pd.MultiIndex.from_tuples(cols) to create MultiIndex with regions as first level and products as second.

2. Step 2: Select all sales for Region1

   Use df.loc[:, 'Region1'] to select all columns under the 'Region1' level.

3. Final Answer:

   Set columns with pd.MultiIndex.from_tuples(cols) and select with df.loc[:, 'Region1'] -> Option B

4. Quick Check:

   Use from_tuples() and df.loc[:, region] to select [OK]

Hint: Use from_tuples() and df.loc[:, region] to select grouped columns [OK]

Common Mistakes:

• Using from_product() instead of from_tuples()
• Selecting columns with df['Region1'] which may fail for MultiIndex
• Selecting wrong level like 'ProductA' instead of region