Data aggregation reporting in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of groupby aggregation with multiple functions

What is the output of this code snippet that groups data by 'Category' and applies multiple aggregation functions?

Pandas

import pandas as pd

data = pd.DataFrame({
    'Category': ['A', 'A', 'B', 'B', 'C'],
    'Value': [10, 20, 10, 30, 40]
})

result = data.groupby('Category').agg({'Value': ['sum', 'mean']})
print(result)

A

          Value     
           sum  mean
Category            
A           30  15.0
B           40  20.0
C           40  40.0

B

          Value     
           mean  sum
Category            
A           15.0  30
B           20.0  40
C           40.0  40

C

          Value     
           sum  mean
Category            
A           30  20.0
B           40  10.0
C           40  40.0

D

          Value     
           sum  mean
Category            
A           20  15.0
B           40  20.0
C           40  40.0

💡 Hint

Remember that sum adds all values per group and mean calculates the average.

The groupby groups rows by 'Category'. For each group, sum adds values and mean calculates average. For 'A', sum is 10+20=30 and mean is 15.0. For 'B', sum is 10+30=40 and mean is 20.0. For 'C', only one value 40, so sum and mean are 40.

❓ data_output

intermediate

Number of groups after filtering and aggregation

Given this DataFrame, how many groups remain after grouping by 'Type' and filtering groups with sum of 'Score' > 50?

Pandas

import pandas as pd

data = pd.DataFrame({
    'Type': ['X', 'X', 'Y', 'Y', 'Z', 'Z'],
    'Score': [30, 25, 10, 15, 40, 20]
})

filtered = data.groupby('Type').filter(lambda x: x['Score'].sum() > 50)
groups = filtered.groupby('Type').ngroups
print(groups)

A 1

B 0

C 3

D 2

💡 Hint

Calculate sum of 'Score' per 'Type' and check which sums are greater than 50.

Sum per 'Type': X=55, Y=25, Z=60. Only groups X and Z have sum > 50. So after filtering, 2 groups remain.

🔧 Debug

advanced

Identify the error in aggregation code

What error does this code raise when trying to aggregate data?

Pandas

import pandas as pd

data = pd.DataFrame({
    'Group': ['A', 'A', 'B'],
    'Value': [1, 2, 3]
})

result = data.groupby('Group').agg({'Value': 'sum', 'NonExistent': 'mean'})
print(result)

A AttributeError: 'DataFrameGroupBy' object has no attribute 'agg'

B KeyError: 'NonExistent'

C ValueError: No numeric types to aggregate

D TypeError: unsupported operand type(s) for +: 'int' and 'str'

💡 Hint

Check if all columns in aggregation dictionary exist in the DataFrame.

The aggregation dictionary includes 'NonExistent' column which is not in the DataFrame, causing a KeyError.

❓ visualization

advanced

Correct plot for aggregated data

Which option shows the correct bar plot code to visualize the sum of 'Sales' per 'Region' from this DataFrame?

Pandas

import pandas as pd
import matplotlib.pyplot as plt

data = pd.DataFrame({
    'Region': ['North', 'South', 'East', 'West', 'North', 'South'],
    'Sales': [100, 150, 200, 130, 120, 170]
})

agg_data = data.groupby('Region')['Sales'].sum()

A

plt.bar(agg_data, agg_data.index)
plt.show()

B

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

C

plt.bar(agg_data.index, agg_data)
plt.show()

D

data.plot.bar(x='Region', y='Sales')
plt.show()

💡 Hint

Remember bar plot x-axis is categories, y-axis is values.

agg_data is a Series with index as 'Region' and values as sum of 'Sales'. plt.bar needs x as categories (index) and height as values.

🚀 Application

expert

Calculate weighted average after aggregation

Given this DataFrame, which code correctly calculates the weighted average 'Score' per 'Class' using 'Weight' as weights?

Pandas

import pandas as pd

data = pd.DataFrame({
    'Class': ['X', 'X', 'Y', 'Y', 'Y'],
    'Score': [80, 90, 70, 60, 75],
    'Weight': [1, 3, 2, 1, 2]
})

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

B data.groupby('Class').agg({'Score': lambda x: (x * data['Weight']).mean()})

C data.groupby('Class').agg({'Score': 'mean'})

D data.groupby('Class').apply(lambda x: x['Score'].mean() * x['Weight'].mean())

💡 Hint

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

Option A correctly computes weighted average per group by multiplying 'Score' and 'Weight' per row, summing, then dividing by sum of weights in that group.

Practice

1. What is the main purpose of using groupby() in pandas for data aggregation reporting?

easy

A. To filter rows based on a condition

B. To sort data in ascending order

C. To split data into groups based on column values

D. To merge two dataframes

Solution

1. Step 1: Understand the role of groupby()

   The groupby() function splits the dataframe into groups based on unique values in one or more columns.

2. Step 2: Identify the purpose in aggregation

   After grouping, aggregation functions like sum or mean can be applied to each group separately.

3. Final Answer:

   To split data into groups based on column values -> Option C

4. Quick Check:

   groupby() splits data for aggregation [OK]

Hint: Remember: groupby splits data, agg summarizes groups [OK]

Common Mistakes:

• Confusing groupby with sorting
• Thinking groupby filters rows
• Mixing groupby with merging dataframes

2. Which of the following is the correct syntax to calculate the mean of column 'Sales' grouped by 'Region' in pandas?

easy

A. df.groupby('Region').agg('Sales'.mean)

B. df.group_by('Region').mean('Sales')

C. df.groupby('Sales')['Region'].mean()

D. df.groupby('Region')['Sales'].mean()

Solution

1. Step 1: Identify correct groupby syntax

   The correct method is groupby('Region') to group by 'Region'.

2. Step 2: Select column and apply mean

   Use ['Sales'].mean() to calculate mean of 'Sales' per group.

3. Final Answer:

   df.groupby('Region')['Sales'].mean() -> Option D

4. Quick Check:

   Correct method chaining for groupby and mean [OK]

Hint: Use df.groupby('col')['col2'].mean() for grouped mean [OK]

Common Mistakes:

• Using group_by instead of groupby
• Swapping group and aggregation columns
• Incorrect agg syntax with string method

3. Given the dataframe:

import pandas as pd
data = {'Team': ['A', 'A', 'B', 'B', 'C'], 'Points': [10, 15, 10, 20, 30]}

What is the output of:

df = pd.DataFrame(data)
df.groupby('Team').agg({'Points': 'sum'})

medium

A. {'Team': ['A', 'B', 'C'], 'Points': [25, 30, 30]}

B. {'Team': ['A', 'B', 'C'], 'Points': [10, 10, 30]}

C. {'Team': ['A', 'B', 'C'], 'Points': [15, 20, 30]}

D. Error: agg() requires a list of functions

Solution

1. Step 1: Group data by 'Team'

   Teams are A, B, and C. Grouping splits rows accordingly.

2. Step 2: Sum 'Points' per group

   For A: 10+15=25, B: 10+20=30, C: 30.

3. Final Answer:

   {'Team': ['A', 'B', 'C'], 'Points': [25, 30, 30]} -> Option A

4. Quick Check:

   Sum points per team = [25, 30, 30] [OK]

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

Common Mistakes:

• Summing only first value per group
• Confusing sum with mean
• Expecting agg() needs list always

4. You run this code:

df.groupby('Category').agg('sum', 'Price')

But get an error. What is the problem?

medium

A. agg() expects a dictionary or list, not multiple arguments

B. groupby() requires numeric columns only

C. sum is not a valid aggregation function

D. Price column does not exist

Solution

1. Step 1: Check agg() function signature

   The agg() method expects a single function, list of functions, or dict mapping columns to functions.

2. Step 2: Identify incorrect argument usage

   Passing two separate arguments ('sum', 'Price') is invalid syntax and causes error.

3. Final Answer:

   agg() expects a dictionary or list, not multiple arguments -> Option A

4. Quick Check:

   agg() needs one argument: function or dict [OK]

Hint: Use agg({'Price': 'sum'}) or agg('sum') only [OK]

Common Mistakes:

• Passing multiple arguments to agg()
• Assuming groupby needs numeric columns only
• Using invalid function names

5. You have a sales dataframe with columns 'Store', 'Product', and 'Revenue'. You want to find the total revenue per store and the average revenue per product within each store. Which pandas code correctly achieves this?

hard

A. df.groupby('Store').agg({'Revenue': ['sum', 'mean']})

B. df.groupby(['Store', 'Product']).agg({'Revenue': 'mean'}).groupby('Store').agg({'Revenue': 'sum'})

C. df.groupby('Store').agg({'Revenue': 'sum'}).groupby('Product').agg({'Revenue': 'mean'})

D. df.groupby('Product').agg({'Revenue': ['sum', 'mean']})

Solution

1. Step 1: Calculate average revenue per product within each store

   Group by both 'Store' and 'Product', then calculate mean revenue.

2. Step 2: Calculate total revenue per store

   Group the result by 'Store' and sum the mean revenues of products.

3. Final Answer:

   df.groupby(['Store', 'Product']).agg({'Revenue': 'mean'}).groupby('Store').agg({'Revenue': 'sum'}) -> Option B

4. Quick Check:

   Nested groupby for product mean then store sum [OK]

Hint: Group by both, then aggregate stepwise for nested summaries [OK]

Common Mistakes:

• Trying to do sum and mean in one groupby without nesting
• Grouping by wrong columns order
• Mixing aggregation levels incorrectly