Data aggregation reporting in Pandas - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the average score from the DataFrame.

Pandas

average_score = df['score'].[1]()

Drag options to blanks, or click blank then click option'

Amean

Bsum

Ccount

Dmax

Attempts:

3 left

💡 Hint

Common Mistakes

Using sum() instead of mean() will give the total, not the average.

Using count() returns the number of entries, not the average.

The mean() function calculates the average value of the 'score' column.

2fill in blank

medium

Complete the code to group the DataFrame by 'category' and calculate the sum of 'sales'.

Pandas

grouped = df.groupby('[1]')['sales'].sum()

Drag options to blanks, or click blank then click option'

Aregion

Bscore

Cdate

Dcategory

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by 'score' or 'date' will not group sales by category.

Using 'region' groups by location, not category.

Grouping by 'category' allows us to sum sales within each category.

3fill in blank

hard

Fix the error in the code to calculate the maximum value in the 'price' column.

Pandas

max_price = df['price'].[1]()

Drag options to blanks, or click blank then click option'

Amax

Bmaximum

Cmax_value

DmaxPrice

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'maximum' or 'max_value' causes errors because they are not pandas functions.

Using camelCase like 'maxPrice' is incorrect in pandas.

The correct pandas function to get the maximum value is max().

4fill in blank

hard

Fill both blanks to create a dictionary with word lengths for words longer than 3 characters.

Pandas

lengths = {word: [1] for word in words if [2]

Drag options to blanks, or click blank then click option'

Alen(word)

Bword > 3

Clen(word) > 3

Dword

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'word > 3' compares a string to a number, causing errors.

Using 'word' alone in the condition does not filter by length.

The dictionary comprehension maps each word to its length using len(word). The condition filters words longer than 3 characters with len(word) > 3.

5fill in blank

hard

Fill all three blanks to create a dictionary of uppercase keys and values greater than zero.

Pandas

result = [1]: [2] for k, v in data.items() if v [3] 0}

Drag options to blanks, or click blank then click option'

Ak.upper()

Bv

C>

Dk.lower()

Attempts:

3 left

💡 Hint

Common Mistakes

Using k.lower() instead of uppercase keys.

Using '<' instead of '>' in the condition.

Using keys or values incorrectly in the dictionary.

The dictionary comprehension uses k.upper() for keys, keeps values as v, and filters values greater than zero with v > 0.

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