Setting columns as MultiIndex in Pandas

Introduction

We use MultiIndex columns to organize data with multiple levels of labels. It helps to group related columns together clearly.

You have data with categories and subcategories in columns, like sales by region and product.

You want to compare multiple measurements for the same items side by side.

Your data has hierarchical information that needs clear separation in columns.

You want to perform operations on grouped columns easily.

You want to display complex tables neatly with multiple column headers.

Syntax

Pandas

df.columns = pd.MultiIndex.from_tuples([('level1', 'level2'), ...])

Use a list of tuples where each tuple represents the levels for one column.

You can also create MultiIndex from arrays or product of lists.

Examples

Set two columns under the same group 'Group1' with sublabels 'A' and 'B'.

Pandas

import pandas as pd

df = pd.DataFrame({
    'A': [1, 2],
    'B': [3, 4]
})
df.columns = pd.MultiIndex.from_tuples([('Group1', 'A'), ('Group1', 'B')])
print(df)

Change columns to show subject 'Math' with two sub-columns 'Score' and 'Grade'.

Pandas

df.columns = pd.MultiIndex.from_tuples([('Math', 'Score'), ('Math', 'Grade')])

Sample Program

This example shows sales data for two products in two regions (North, South) over two quarters (Q1, Q2). We set columns as MultiIndex to group by region and quarter.

Pandas

import pandas as pd

# Create a simple DataFrame
sales = pd.DataFrame({
    'North_Q1': [100, 150],
    'North_Q2': [110, 160],
    'South_Q1': [90, 120],
    'South_Q2': [95, 130]
}, index=['Product A', 'Product B'])

# Define MultiIndex for columns
multi_cols = pd.MultiIndex.from_tuples([
    ('North', 'Q1'),
    ('North', 'Q2'),
    ('South', 'Q1'),
    ('South', 'Q2')
])

# Set the MultiIndex columns
sales.columns = multi_cols

print(sales)

Important Notes

MultiIndex columns let you access data by levels, e.g., df['North']['Q1'].

Be careful to keep the number of tuples equal to the number of columns.

You can also create MultiIndex from arrays using pd.MultiIndex.from_arrays().

Summary

MultiIndex columns organize data with multiple header levels.

Use pd.MultiIndex.from_tuples() with a list of tuples to set MultiIndex columns.

This helps group related columns and makes data easier to analyze.

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