Sorting MultiIndex in Pandas - Mini Project: Build & Apply

Sorting MultiIndex

📖 Scenario: You work in a company that tracks sales data by region and product category. The data is stored in a table with two levels of row labels: the region and the product category. You want to organize this data so it is easier to analyze by sorting the rows based on these labels.

🎯 Goal: Create a pandas DataFrame with a MultiIndex for regions and product categories, then sort the DataFrame by the MultiIndex to organize the data.

📋 What You'll Learn

Create a pandas DataFrame with a MultiIndex using the exact regions and product categories given.

Add a configuration variable to specify sorting order.

Sort the DataFrame by the MultiIndex using the specified order.

Print the sorted DataFrame to show the result.

💡 Why This Matters

🌍 Real World

Companies often store data with multiple levels of categories, like region and product type. Sorting this data helps in clear reporting and analysis.

💼 Career

Data analysts and scientists frequently work with MultiIndex data in pandas to organize and analyze complex datasets efficiently.

1

Create a MultiIndex DataFrame

Create a pandas DataFrame called sales with a MultiIndex from the tuples [('East', 'Furniture'), ('East', 'Technology'), ('West', 'Furniture'), ('West', 'Technology')]. Use the sales values [1000, 1500, 1200, 1300] for a column named 'Sales'.

Pandas

# Create the MultiIndex DataFrame called sales
# Your code here

Hint

Use pd.MultiIndex.from_tuples to create the MultiIndex and then create the DataFrame with the Sales column.

2

Set sorting order configuration

Create a variable called sort_ascending and set it to True to specify that sorting should be in ascending order.

Pandas

import pandas as pd

index = pd.MultiIndex.from_tuples([
    ('East', 'Furniture'),
    ('East', 'Technology'),
    ('West', 'Furniture'),
    ('West', 'Technology')
], names=['Region', 'Category'])

sales = pd.DataFrame({'Sales': [1000, 1500, 1200, 1300]}, index=index)

# Create the variable sort_ascending and set it to True
# Your code here

Hint

Just create a variable named sort_ascending and assign it the value True.

3

Sort the MultiIndex DataFrame

Sort the DataFrame sales by its MultiIndex using the variable sort_ascending to control ascending order. Assign the sorted DataFrame back to sales.

Pandas

import pandas as pd

index = pd.MultiIndex.from_tuples([
    ('East', 'Furniture'),
    ('East', 'Technology'),
    ('West', 'Furniture'),
    ('West', 'Technology')
], names=['Region', 'Category'])

sales = pd.DataFrame({'Sales': [1000, 1500, 1200, 1300]}, index=index)

sort_ascending = True

# Sort the sales DataFrame by its MultiIndex using sort_ascending
# Your code here

Hint

Use sales.sort_index(ascending=sort_ascending) and assign it back to sales.

4

Display the sorted DataFrame

Print the sorted DataFrame sales to display the organized sales data.

Pandas

import pandas as pd

index = pd.MultiIndex.from_tuples([
    ('East', 'Furniture'),
    ('East', 'Technology'),
    ('West', 'Furniture'),
    ('West', 'Technology')
], names=['Region', 'Category'])

sales = pd.DataFrame({'Sales': [1000, 1500, 1200, 1300]}, index=index)

sort_ascending = True

sales = sales.sort_index(ascending=sort_ascending)

# Print the sales DataFrame
# Your code here

Hint

Use print(sales) to show the sorted DataFrame.

Practice

1. What does sorting a MultiIndex in pandas do?

easy

A. It removes duplicate rows from the DataFrame.

B. It merges multiple DataFrames into one.

C. It changes the data types of the index levels.

D. It arranges the data based on one or more index levels in order.

Solution

1. Step 1: Understand MultiIndex structure

   A MultiIndex has multiple levels that can be sorted independently or together.

2. Step 2: Define sorting effect

   Sorting organizes rows by the values in these index levels, making data easier to analyze.

3. Final Answer:

   It arranges the data based on one or more index levels in order. -> Option D

4. Quick Check:

   Sorting MultiIndex = Arrange by index levels [OK]

Hint: Sorting MultiIndex orders data by index levels [OK]

Common Mistakes:

• Confusing sorting with removing duplicates
• Thinking sorting changes data types
• Mixing sorting with merging DataFrames

2. Which of the following is the correct syntax to sort a pandas DataFrame df by its MultiIndex?

easy

A. df.sort(level=[0,1], ascending=[True, False])

B. df.sort_values(by=['level_0', 'level_1'])

C. df.sort_index(level=[0,1], ascending=[True, False])

D. df.sort_index(by=['level_0', 'level_1'])

Solution

1. Step 1: Recall sorting MultiIndex method

   Use sort_index() to sort by index levels, specifying levels and order.

2. Step 2: Check syntax correctness

   df.sort_index(level=[0,1], ascending=[True, False]) correctly uses sort_index(level=[0,1], ascending=[True, False]). Others use wrong methods or parameters.

3. Final Answer:

   df.sort_index(level=[0,1], ascending=[True, False]) -> Option C

4. Quick Check:

   sort_index with level and ascending = correct syntax [OK]

Hint: Use sort_index(level=..., ascending=...) for MultiIndex [OK]

Common Mistakes:

• Using sort_values instead of sort_index for MultiIndex
• Passing 'by' parameter to sort_index
• Using deprecated sort() method

3. Given the following code, what will be the order of the MultiIndex after sorting?

import pandas as pd
index = pd.MultiIndex.from_tuples([('b', 2), ('a', 3), ('b', 1), ('a', 2)])
df = pd.DataFrame({'value': [10, 20, 30, 40]}, index=index)
sorted_df = df.sort_index(level=[0,1], ascending=[True, False])
print(sorted_df.index.tolist())

medium

A. [('a', 3), ('a', 2), ('b', 2), ('b', 1)]

B. [('a', 2), ('a', 3), ('b', 1), ('b', 2)]

C. [('b', 1), ('b', 2), ('a', 2), ('a', 3)]

D. [('b', 2), ('b', 1), ('a', 3), ('a', 2)]

Solution

1. Step 1: Understand sorting parameters

   Level 0 sorted ascending (a before b), level 1 descending (higher number first).

2. Step 2: Apply sorting to tuples

   For 'a': (3,2) sorted descending gives (3), then (2). For 'b': (2,1) sorted descending gives (2), then (1).

3. Final Answer:

   [('a', 3), ('a', 2), ('b', 2), ('b', 1)] -> Option A

4. Quick Check:

   Level 0 asc, Level 1 desc = [('a',3),('a',2),('b',2),('b',1)] [OK]

Hint: Sort level 0 ascending, level 1 descending to get correct order [OK]

Common Mistakes:

• Sorting both levels ascending by default
• Confusing ascending order for level 1
• Not matching index tuples correctly

4. You run this code but get an error:

df.sort_index(level=[0,1], ascending=True, inplace=True)

What is the likely cause?

medium

A. The 'ascending' parameter must be a list when sorting multiple levels.

B. The 'inplace' parameter is not supported in sort_index.

C. The 'level' parameter cannot accept a list.

D. The DataFrame does not have a MultiIndex.

Solution

1. Step 1: Check parameter types for multiple levels

   When sorting multiple levels, 'ascending' must be a list matching levels count.

2. Step 2: Identify error cause

   Passing a single boolean True instead of a list causes a ValueError.

3. Final Answer:

   The 'ascending' parameter must be a list when sorting multiple levels. -> Option A

4. Quick Check:

   ascending list needed for multiple levels [OK]

Hint: Use list for ascending when sorting multiple levels [OK]

Common Mistakes:

• Passing single bool to ascending with multiple levels
• Assuming inplace is unsupported
• Misunderstanding level parameter type

5. You have a MultiIndex DataFrame with levels 'Country' and 'Year'. You want to sort it so that 'Country' is ascending but 'Year' is descending, and then reset the index to get a flat DataFrame sorted accordingly. Which code snippet achieves this?

hard

A. df.sort_values(by=['Country', 'Year'], ascending=[True, False]).reset_index(drop=True)

B. df.sort_index(level=['Country', 'Year'], ascending=[True, False]).reset_index()

C. df.sort_index(level=['Country', 'Year'], ascending=[False, True]).reset_index()

D. df.sort_values(by=['Year', 'Country'], ascending=[False, True]).reset_index()

Solution

1. Step 1: Choose correct sorting method for MultiIndex

   Use sort_index() with levels and ascending list to sort MultiIndex properly.

2. Step 2: Reset index to flatten DataFrame

   Calling reset_index() converts MultiIndex to columns, preserving sorted order.

3. Final Answer:

   df.sort_index(level=['Country', 'Year'], ascending=[True, False]).reset_index() -> Option B

4. Quick Check:

   sort_index with levels + reset_index = correct approach [OK]

Hint: Use sort_index with ascending list, then reset_index [OK]

Common Mistakes:

• Using sort_values instead of sort_index for MultiIndex
• Wrong ascending order for levels
• Forgetting to reset index after sorting