Selecting data with MultiIndex in Pandas - Mini Project: Build & Apply

Selecting data with MultiIndex

📖 Scenario: You work in a store that sells fruits in different cities. You have sales data organized by city and fruit type. You want to find sales numbers for specific cities and fruits easily.

🎯 Goal: Build a pandas DataFrame with a MultiIndex for city and fruit. Then select sales data for a specific city and fruit using MultiIndex selection.

📋 What You'll Learn

Create a pandas DataFrame with a MultiIndex from tuples of city and fruit

Add a sales column with integer values

Create a variable for the city to select

Use .loc with the MultiIndex to select sales data for the chosen city

Print the selected sales data

💡 Why This Matters

🌍 Real World

Stores and companies often have sales data organized by multiple categories like city and product. MultiIndex helps manage and analyze such data efficiently.

💼 Career

Data analysts and scientists use MultiIndex DataFrames to handle complex datasets with multiple levels of grouping, making data selection and aggregation easier.

1

Create a MultiIndex DataFrame

Import pandas as pd. Create a list of tuples called index_tuples with these exact pairs: ('New York', 'Apple'), ('New York', 'Banana'), ('Los Angeles', 'Apple'), ('Los Angeles', 'Banana'). Then create a MultiIndex called multi_index from index_tuples with names ['City', 'Fruit']. Finally, create a DataFrame called sales_data with this MultiIndex and a column 'Sales' with values [100, 150, 200, 250].

Pandas

# Import pandas and create the MultiIndex DataFrame
# Your code here

Hint

Use pd.MultiIndex.from_tuples() to create the MultiIndex. Then pass it as the index when creating the DataFrame.

2

Set the city to select

Create a variable called selected_city and set it to the string 'New York'.

Pandas

import pandas as pd
index_tuples = [('New York', 'Apple'), ('New York', 'Banana'), ('Los Angeles', 'Apple'), ('Los Angeles', 'Banana')]
multi_index = pd.MultiIndex.from_tuples(index_tuples, names=['City', 'Fruit'])
sales_data = pd.DataFrame({'Sales': [100, 150, 200, 250]}, index=multi_index)
# Create a variable called selected_city and set it to 'New York'
# Your code here

Hint

Just assign the string 'New York' to the variable selected_city.

3

Select sales data for the chosen city

Use sales_data.loc with selected_city to select all fruit sales for that city. Store the result in a variable called city_sales.

Pandas

import pandas as pd
index_tuples = [('New York', 'Apple'), ('New York', 'Banana'), ('Los Angeles', 'Apple'), ('Los Angeles', 'Banana')]
multi_index = pd.MultiIndex.from_tuples(index_tuples, names=['City', 'Fruit'])
sales_data = pd.DataFrame({'Sales': [100, 150, 200, 250]}, index=multi_index)
selected_city = 'New York'
# Select sales data for selected_city using .loc and save to city_sales
# Your code here

Hint

Use sales_data.loc[selected_city] to get all rows where the first level of the MultiIndex matches selected_city.

4

Print the selected sales data

Print the variable city_sales to display the sales data for the selected city.

Pandas

import pandas as pd
index_tuples = [('New York', 'Apple'), ('New York', 'Banana'), ('Los Angeles', 'Apple'), ('Los Angeles', 'Banana')]
multi_index = pd.MultiIndex.from_tuples(index_tuples, names=['City', 'Fruit'])
sales_data = pd.DataFrame({'Sales': [100, 150, 200, 250]}, index=multi_index)
selected_city = 'New York'
city_sales = sales_data.loc[selected_city]
# Print city_sales
# Your code here

Hint

Use print(city_sales) to show the sales for the selected city.

Practice

1. What does df.loc[('A', 1)] do when df has a MultiIndex with two levels?

easy

A. Selects the row where the first level is 'A' and the second level is 1.

B. Selects all rows where the first level is 'A' regardless of the second level.

C. Selects all rows where the second level is 1 regardless of the first level.

D. Raises an error because tuples cannot be used with MultiIndex.

Solution

1. Step 1: Understand MultiIndex selection with tuples

   Using df.loc with a tuple selects rows matching each level in the MultiIndex exactly.

2. Step 2: Apply tuple to select specific row

   The tuple ('A', 1) matches the first level 'A' and second level 1, selecting that exact row.

3. Final Answer:

   Selects the row where the first level is 'A' and the second level is 1. -> Option A

4. Quick Check:

   Tuple in loc = exact MultiIndex row [OK]

Hint: Use full tuple in loc to select exact MultiIndex row [OK]

Common Mistakes:

• Thinking partial tuple selects exact row
• Confusing level order in tuple
• Believing tuple usage causes error

2. Which of the following is the correct tuple syntax to select all rows where the first level of a MultiIndex is 'B' using df.loc?

easy

A. df.loc[('B')]

B. df.loc[('B',)]

C. df.loc['B']

D. df.loc[['B']]

Solution

1. Step 1: Understand tuple syntax for single element

   To specify a single-element tuple in Python, a comma is needed: ('B',).

2. Step 2: Use tuple in df.loc to select first level

   Using df.loc[('B',)] selects all rows where the first level is 'B'.

3. Final Answer:

   df.loc[('B',)] -> Option B

4. Quick Check:

   Single-element tuple needs comma [OK]

Hint: Use comma for single-element tuple in loc [OK]

Common Mistakes:

• Using parentheses without comma for tuple
• Using list instead of tuple
• Omitting tuple and causing key error

3. Given the DataFrame df with MultiIndex levels 'Letter' and 'Number':

import pandas as pd
index = pd.MultiIndex.from_tuples([('A', 1), ('A', 2), ('B', 1), ('B', 2)], names=['Letter', 'Number'])
data = [10, 20, 30, 40]
df = pd.DataFrame(data, index=index, columns=['Value'])
print(df.loc['A'])

What is the output?

medium

A. Value\nLetter Number\nA 1 10\n 2 20

B. Value\nLetter Number\nB 1 30\n 2 40

C. KeyError because 'A' is not a valid index

D. Value\nNumber\n1 10\n2 20

Solution

1. Step 1: Understand partial selection with single label

   Using df.loc['A'] selects all rows where the first level is 'A'. The result is a DataFrame indexed by the second level only.

2. Step 2: Check output structure

   The output shows 'Value' column with index 'Number' 1 and 2, values 10 and 20 respectively.

3. Final Answer:

   Value Number 1 10 2 20 -> Option D

4. Quick Check:

   Partial index selection drops first level [OK]

Hint: Partial loc drops selected level from index [OK]

Common Mistakes:

• Expecting full MultiIndex in output
• Confusing which levels remain after selection
• Thinking KeyError occurs for valid label

4. Consider this code snippet:

import pandas as pd
index = pd.MultiIndex.from_tuples([('X', 1), ('X', 2), ('Y', 1)])
data = [100, 200, 300]
df = pd.DataFrame(data, index=index, columns=['Score'])

# Attempt to select all rows where first level is 'X'
result = df.loc[('X')]
print(result)

What is the issue and how to fix it?

medium

A. SyntaxError; fix by removing parentheses: df.loc['X'].

B. Raises KeyError; fix by using df.loc[('X',)] to specify single-element tuple.

C. No issue; code runs correctly and prints rows with first level 'X'.

D. Raises TypeError; fix by converting 'X' to list: df.loc[['X']].

Solution

1. Step 1: Analyze the tuple usage in df.loc

   Here, df.loc[('X')] is equivalent to df.loc['X'] because parentheses around a string do not create a tuple.

2. Step 2: Confirm code behavior

   This selects all rows where the first level is 'X' correctly, so no error occurs.

3. Final Answer:

   No issue; code runs correctly and prints rows with first level 'X'. -> Option C

4. Quick Check:

   Parentheses alone don't make tuple [OK]

Hint: Parentheses alone don't create tuple; string stays string [OK]

Common Mistakes:

• Assuming ('X') is a tuple
• Expecting KeyError for valid label
• Confusing tuple syntax with parentheses

5. You have a MultiIndex DataFrame df with levels 'City', 'Year', and 'Month'. How do you select all data for 'New York' in 2023 regardless of month using a tuple that specifies all three levels?

hard

A. df.loc[('New York', 2023, slice(None))]

B. df.loc[('New York', 2023)]

C. df.loc[('New York', 2023,)]

D. df.loc['New York', 2023]

Solution

1. Step 1: Understand MultiIndex with 3 levels

   The MultiIndex has three levels: 'City', 'Year', and 'Month'. To select all months for 'New York' and 2023, we must specify all three levels.

2. Step 2: Use slice(None) to select all in third level

   Using slice(None) in the third position selects all months. So df.loc[('New York', 2023, slice(None))] selects all rows matching city and year, any month.

3. Final Answer:

   df.loc[('New York', 2023, slice(None))] -> Option A

4. Quick Check:

   Use slice(None) for full level selection [OK]

Hint: Use slice(None) for full selection in MultiIndex level [OK]

Common Mistakes:

• Omitting third level causing KeyError
• Using incomplete tuple without slice
• Using comma-separated loc without tuple