Grouping by multiple columns in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of grouping by two columns and summing

What is the output of this code snippet that groups data by two columns and sums the values?

Pandas

import pandas as pd

data = pd.DataFrame({
    'City': ['NY', 'LA', 'NY', 'LA', 'NY'],
    'Category': ['A', 'A', 'B', 'B', 'A'],
    'Sales': [100, 200, 150, 300, 50]
})
result = data.groupby(['City', 'Category']).sum()
print(result)

A

              Sales
City Category       
LA   A          300
     B          300
NY   A          150
     B          150

B

              Sales
City Category       
LA   A          200
     B          300
NY   A          150
     B          150

C

              Sales
City Category       
LA   A          200
     B          300
NY   A          100
     B          150

D

              Sales
City Category       
LA   A          200
     B          450
NY   A          150
     B          150

💡 Hint

Think about how groupby sums values for each unique pair of City and Category.

The groupby sums Sales for each City and Category pair. For LA and B, sales is 300 only, not 450. For NY and A, sales is 100 + 50 = 150.

❓ data_output

intermediate

Number of groups formed by grouping on two columns

Given this DataFrame, how many groups will be formed when grouping by 'Type' and 'Color'?

Pandas

import pandas as pd

data = pd.DataFrame({
    'Type': ['Fruit', 'Fruit', 'Vegetable', 'Fruit', 'Vegetable'],
    'Color': ['Red', 'Green', 'Green', 'Red', 'Red'],
    'Quantity': [10, 15, 7, 5, 3]
})
groups = data.groupby(['Type', 'Color'])
print(len(groups))

A 3

B 6

C 5

D 4

💡 Hint

Count unique pairs of Type and Color in the data.

The unique pairs are: (Fruit, Red), (Fruit, Green), (Vegetable, Green), (Vegetable, Red). So 4 groups.

🔧 Debug

advanced

Identify the error in grouping by multiple columns

What error will this code raise when trying to group by multiple columns?

Pandas

import pandas as pd

data = pd.DataFrame({
    'A': [1, 2, 1],
    'B': [3, 4, 3],
    'C': [5, 6, 7]
})
result = data.groupby('A', 'B').sum()
print(result)

A ValueError: No axis named 'B'

B KeyError: 'B'

C AttributeError: 'DataFrame' object has no attribute 'groupby'

D No error, outputs grouped sum

💡 Hint

Check how groupby expects multiple columns as input.

groupby expects a list or array-like for multiple columns, not multiple positional arguments.

❓ visualization

advanced

Visualizing grouped data by multiple columns

Which option correctly creates a bar plot showing the sum of 'Value' grouped by 'Group' and 'Subgroup'?

Pandas

import pandas as pd
import matplotlib.pyplot as plt

data = pd.DataFrame({
    'Group': ['X', 'X', 'Y', 'Y', 'X'],
    'Subgroup': ['A', 'B', 'A', 'B', 'A'],
    'Value': [10, 20, 30, 40, 50]
})
grouped = data.groupby(['Group', 'Subgroup']).sum()

# Which code below plots the grouped data correctly?

A

grouped.plot(kind='bar')
plt.show()

B

grouped.plot(kind='bar', stacked=True)
plt.show()

C

grouped.unstack().plot(kind='bar')
plt.show()

D

data.plot(kind='bar', x='Group', y='Value')
plt.show()

💡 Hint

Think about how to reshape grouped data for a clear bar plot with multiple categories.

unstack() reshapes the grouped data so that Subgroup becomes columns, allowing a grouped bar plot.

🚀 Application

expert

Calculate weighted average after grouping by multiple columns

You have this DataFrame with 'Category', 'Subcategory', 'Score', and 'Weight'. How do you calculate the weighted average Score for each Category and Subcategory group?

Pandas

import pandas as pd

data = pd.DataFrame({
    'Category': ['A', 'A', 'B', 'B', 'A'],
    'Subcategory': ['X', 'X', 'Y', 'Y', 'Z'],
    'Score': [80, 90, 70, 60, 85],
    'Weight': [1, 2, 1, 3, 2]
})

A data.groupby(['Category', 'Subcategory']).apply(lambda x: (x['Score'] * x['Weight']).sum() / x['Weight'].sum())

B data.groupby(['Category', 'Subcategory']).mean('Score')

C data.groupby(['Category', 'Subcategory']).agg({'Score': 'mean', 'Weight': 'sum'})

D data.groupby(['Category', 'Subcategory']).sum()['Score'] / data.groupby(['Category', 'Subcategory']).sum()['Weight']

💡 Hint

Weighted average is sum of (value * weight) divided by sum of weights.

Option A correctly applies weighted average calculation per group using apply and lambda.

Practice

1. What does grouping by multiple columns in pandas allow you to do?

easy

A. Filter rows based on a single condition

B. Sort data alphabetically by one column

C. Analyze data by several categories at the same time

D. Change the data type of multiple columns

Solution

1. Step 1: Understand grouping concept

   Grouping by multiple columns means splitting data into groups based on combinations of those columns.

2. Step 2: Identify the purpose of grouping

   This helps analyze data by several categories at once, like sales by region and product.

3. Final Answer:

   Analyze data by several categories at the same time -> Option C

4. Quick Check:

   Grouping multiple columns = multi-category analysis [OK]

Hint: Grouping by list of columns splits data by combined categories [OK]

Common Mistakes:

• Thinking grouping changes data order
• Confusing grouping with filtering
• Assuming grouping changes data types

2. Which of the following is the correct syntax to group a DataFrame df by columns 'A' and 'B'?

easy

A. df.groupby(['A', 'B'])

B. df.groupby('A', 'B')

C. df.group_by(['A', 'B'])

D. df.groupby(['A' & 'B'])

Solution

1. Step 1: Recall pandas groupby syntax

   To group by multiple columns, pass a list of column names inside groupby().

2. Step 2: Check each option

   df.groupby(['A', 'B']) uses a list ['A', 'B'], which is correct syntax. Others have syntax errors or wrong method names.

3. Final Answer:

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

4. Quick Check:

   List of columns inside groupby() = correct syntax [OK]

Hint: Use a list of column names inside groupby() [OK]

Common Mistakes:

• Passing columns as separate arguments instead of a list
• Using wrong method name like group_by
• Using bitwise operators inside list

3. Given the DataFrame df below:

   City  Product  Sales
0  NY    A        10
1  NY    B        20
2  LA    A        15
3  LA    B        25
4  NY    A        5

What is the output of df.groupby(['City', 'Product']).sum()?

medium

A. A DataFrame with total sales for each City and Product combination

B. A DataFrame with average sales for each City

C. A Series with sales summed only by City

D. An error because sum() cannot be used after groupby

Solution

1. Step 1: Group by City and Product

   The data is grouped by both City and Product columns, creating groups like (NY, A), (NY, B), (LA, A), (LA, B).

2. Step 2: Apply sum() aggregation

   Sum sales within each group: (NY, A) = 10+5=15, (NY, B) = 20, (LA, A) = 15, (LA, B) = 25.

3. Final Answer:

   A DataFrame with total sales for each City and Product combination -> Option A

4. Quick Check:

   Group by multiple columns + sum() = grouped sums [OK]

Hint: Sum after groupby sums values per group combination [OK]

Common Mistakes:

• Expecting average instead of sum
• Grouping by only one column
• Thinking sum() causes error

4. What is wrong with this code snippet?

df.groupby('City', 'Product').mean()

medium

A. groupby() requires column names as integers

B. mean() cannot be used after groupby

C. The columns 'City' and 'Product' do not exist

D. groupby() expects a single argument, so multiple columns must be passed as a list

Solution

1. Step 1: Check groupby argument format

   groupby() accepts one argument for columns; multiple columns must be inside a list.

2. Step 2: Identify error cause

   Passing 'City', 'Product' as separate arguments causes a TypeError.

3. Final Answer:

   groupby() expects a single argument, so multiple columns must be passed as a list -> Option D

4. Quick Check:

   Multiple columns in groupby() = list argument [OK]

Hint: Pass multiple columns as a list inside groupby() [OK]

Common Mistakes:

• Passing multiple columns as separate arguments
• Thinking mean() is invalid after groupby
• Assuming column names must be integers

5. You have a sales DataFrame df with columns Region, Category, and Revenue. You want to find the average revenue per region and category, but only for categories where the total revenue exceeds 1000. Which code correctly does this?

hard

A. df.groupby(['Region', 'Category']).Revenue.mean()[df.groupby(['Region', 'Category']).Revenue.mean() > 1000]

B. df.groupby(['Region', 'Category']).filter(lambda x: x['Revenue'].sum() > 1000).groupby(['Region', 'Category']).Revenue.mean()

C. df.groupby(['Region', 'Category']).Revenue.mean().filter(lambda x: x > 1000)

D. df.groupby(['Region', 'Category']).Revenue.sum().mean() > 1000

Solution

1. Step 1: Filter groups by total revenue

   Use groupby with filter and a lambda to keep only groups where sum of Revenue > 1000.

2. Step 2: Calculate average revenue per filtered group

   After filtering, group again by Region and Category and calculate mean Revenue.

3. Final Answer:

   df.groupby(['Region', 'Category']).filter(lambda x: x['Revenue'].sum() > 1000).groupby(['Region', 'Category']).Revenue.mean() -> Option B

4. Quick Check:

   Filter groups by sum, then compute mean [OK]

Hint: Use filter() on groupby to keep groups before aggregation [OK]

Common Mistakes:

• Trying to filter after mean instead of before
• Using boolean indexing on Series with mismatched index
• Calculating mean of sums instead of per group