Pivot with aggregation functions in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of pivot with sum aggregation

What is the output of this code snippet using pandas pivot_table with sum aggregation?

Pandas

import pandas as pd

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

pivot = df.pivot_table(index='Category', columns='Type', values='Value', aggfunc='sum', fill_value=0)
print(pivot)

A

Type       X   Y
Category         
A         10  20
B         30  40
C          0  50

B

Type       X   Y
Category         
A         10  20
B         30  40
C         50   0

C

Type       X   Y
Category         
A         30  60
B         30  40
C         50   0

D

Type       X   Y
Category         
A         10  20
B         40  30
C         50   0

💡 Hint

Remember that pivot_table with aggfunc='sum' adds values grouped by index and columns.

The pivot_table groups by 'Category' as rows and 'Type' as columns, summing 'Value'. Missing combinations fill with 0.

❓ data_output

intermediate

Number of items in pivot result with mean aggregation

How many cells (data points) are in the resulting DataFrame after this pivot_table operation?

Pandas

import pandas as pd

data = {'Group': ['G1', 'G1', 'G2', 'G2', 'G3', 'G3'],
        'Category': ['A', 'B', 'A', 'B', 'A', 'B'],
        'Score': [5, 10, 15, 20, 25, 30]}
df = pd.DataFrame(data)

pivot = df.pivot_table(index='Group', columns='Category', values='Score', aggfunc='mean')

A 6

B 9

C 4

D 3

💡 Hint

Count unique index values times unique columns values.

There are 3 unique Groups and 2 unique Categories, so 3*2=6 cells in the pivot table.

🔧 Debug

advanced

Identify the error in pivot_table with multiple aggfuncs

What error does this code raise when trying to pivot with multiple aggregation functions?

Pandas

import pandas as pd

data = {'Category': ['A', 'A', 'B', 'B'],
        'Type': ['X', 'Y', 'X', 'Y'],
        'Value': [1, 2, 3, 4]}
df = pd.DataFrame(data)

pivot = df.pivot_table(index='Category', columns='Type', values='Value', aggfunc=['sum', 'mean'])
print(pivot)

A No error, prints pivot table with multi-level columns

B TypeError: unhashable type: 'list'

C ValueError: aggfunc must be a function or list of functions

D KeyError: 'Value'

💡 Hint

Check if pandas supports list of functions in aggfunc parameter.

pandas pivot_table supports multiple aggregation functions as a list, producing multi-level columns without error.

🚀 Application

advanced

Choose correct pivot_table to find max sales per region and product

Given a DataFrame with columns 'Region', 'Product', and 'Sales', which pivot_table code correctly finds the maximum sales for each region and product?

Pandas

import pandas as pd

# Assume df is given with columns 'Region', 'Product', 'Sales'

A df.pivot_table(index='Region', columns='Product', values='Sales', aggfunc='min')

B df.pivot_table(index='Product', columns='Region', values='Sales', aggfunc='max')

C df.pivot_table(index='Region', columns='Product', values='Sales', aggfunc='sum')

D df.pivot_table(index='Region', columns='Product', values='Sales', aggfunc='max')

💡 Hint

Index should be 'Region' to group rows by region.

To find max sales per region and product, index must be 'Region', columns 'Product', aggfunc 'max'.

🧠 Conceptual

expert

Understanding fill_value effect in pivot_table

What is the effect of setting fill_value=0 in a pandas pivot_table when some combinations of index and columns have no data?

A It causes a runtime error if missing data exists.

B It fills missing values with the mean of the column.

C It replaces missing values in the pivot table with 0 instead of NaN.

D It removes rows and columns with missing data from the pivot table.

💡 Hint

Think about how missing data is shown in pivot tables by default.

By default, missing combinations show as NaN. fill_value=0 replaces these NaNs with zero.

Practice

1. What does the pivot_table function in pandas primarily do?

easy

A. It rearranges data to summarize and compare values using aggregation.

B. It sorts data alphabetically by column names.

C. It deletes duplicate rows from the DataFrame.

D. It converts a DataFrame into a list of dictionaries.

Solution

1. Step 1: Understand the purpose of pivot_table

   The pivot_table function rearranges data by creating a new table that summarizes data values grouped by specified keys.

2. Step 2: Recognize aggregation role

   It uses aggregation functions like sum or mean to combine data values in the new table.

3. Final Answer:

   It rearranges data to summarize and compare values using aggregation. -> Option A

4. Quick Check:

   pivot_table = rearrange + aggregate [OK]

Hint: Pivot tables summarize data by grouping and aggregating [OK]

Common Mistakes:

• Confusing pivot_table with sorting or filtering
• Thinking it deletes duplicates
• Believing it converts DataFrame to list

2. Which of the following is the correct syntax to create a pivot table with pandas to calculate the mean of 'Sales' grouped by 'Region'?

easy

A. pd.pivot_table(data, columns='Region', values='Sales', aggfunc='sum')

B. pd.pivot_table(data, index='Region', values='Sales', aggfunc='mean')

C. data.pivot_table(index='Region', 'Sales', aggfunc='mean')

D. pd.pivot(data, index='Region', values='Sales', aggfunc='mean')

Solution

1. Step 1: Identify correct function and parameters

   The correct function is pd.pivot_table with parameters: data, index='Region', values='Sales', and aggfunc='mean'.

2. Step 2: Check each option

   pd.pivot_table(data, index='Region', values='Sales', aggfunc='mean') matches the correct syntax. pd.pivot(data, index='Region', values='Sales', aggfunc='mean') uses pd.pivot which does not support aggfunc. data.pivot_table('Sales', 'Region', aggfunc='mean') has wrong method call syntax. pd.pivot_table(data, columns='Region', values='Sales', aggfunc='sum') uses columns instead of index and wrong aggregation.

3. Final Answer:

   pd.pivot_table(data, index='Region', values='Sales', aggfunc='mean') -> Option B

4. Quick Check:

   pivot_table + index + values + aggfunc = correct syntax [OK]

Hint: Use pd.pivot_table with index, values, and aggfunc parameters [OK]

Common Mistakes:

• Using pd.pivot instead of pd.pivot_table
• Mixing columns and index parameters
• Incorrect method call on DataFrame

3. Given the DataFrame:

import pandas as pd
data = pd.DataFrame({
  'City': ['A', 'A', 'B', 'B'],
  'Category': ['X', 'Y', 'X', 'Y'],
  'Value': [10, 20, 30, 40]
})
pivot = pd.pivot_table(data, index='City', columns='Category', values='Value', aggfunc='sum')

What is the output of pivot?

medium

A. Category X Y
City
A 15 15
B 35 35

B. Category X Y
City
A 20 10
B 40 30

C. Category X Y
City
A 10 20
B 30 40

D. Category X Y
City
A 10 40
B 30 20

Solution

1. Step 1: Group data by 'City' and 'Category'

   For City 'A' and Category 'X', value is 10; for 'A' and 'Y', value is 20. For City 'B' and Category 'X', value is 30; for 'B' and 'Y', value is 40.

2. Step 2: Apply sum aggregation

   Since each group has one value, sum is the same as the value itself.

3. Final Answer:

   Category X Y City A 10 20 B 30 40 -> Option C

4. Quick Check:

   Sum per group matches values [OK]

Hint: Sum values grouped by index and columns for pivot output [OK]

Common Mistakes:

• Swapping values for categories
• Mixing up rows and columns
• Using mean instead of sum

4. Consider this code snippet:

import pandas as pd
data = pd.DataFrame({
  'Team': ['A', 'A', 'B', 'B'],
  'Points': [5, 10, 15, 20]
})
pivot = pd.pivot_table(data, index='Team', values='Points', aggfunc='count')
print(pivot)

What is the error or issue in this code?

medium

A. No error; output shows count of points per team.

B. aggfunc='count' is invalid; should use 'sum' instead.

C. Missing columns parameter causes runtime error.

D. DataFrame missing 'columns' argument for pivot_table.

Solution

1. Step 1: Understand aggfunc='count'

   The 'count' aggregation counts non-null entries in 'Points' for each 'Team'.

2. Step 2: Check code correctness

   The code is valid and will output the count of 'Points' per team without error.

3. Final Answer:

   No error; output shows count of points per team. -> Option A

4. Quick Check:

   aggfunc='count' counts entries correctly [OK]

Hint: aggfunc='count' counts non-null values, no columns needed [OK]

Common Mistakes:

• Thinking 'count' is invalid aggfunc
• Expecting columns parameter is mandatory
• Confusing count with sum

5. You have sales data with columns: 'Store', 'Product', 'Month', and 'Revenue'. You want to create a pivot table that shows the total revenue per 'Store' for each 'Month', but only for months where total revenue exceeds 1000. Which approach correctly achieves this?

hard

A. Filter original data for revenue > 1000, then pivot with index='Store', columns='Month', values='Revenue', aggfunc='sum'.

B. Use pivot_table with index='Month', columns='Store', values='Revenue', aggfunc='mean', then filter values > 1000.

C. Pivot with index='Store', columns='Product', values='Revenue', aggfunc='sum', then filter rows with sum > 1000.

D. Create pivot with index='Store', columns='Month', values='Revenue', aggfunc='sum', then filter columns with sum > 1000.

Solution

1. Step 1: Create pivot table by Store and Month

   Use pivot_table with index='Store', columns='Month', values='Revenue', and aggfunc='sum' to get total revenue per store per month.

2. Step 2: Filter months by total revenue

   Since filtering by months with revenue > 1000 is needed, filter columns after pivot since columns represent months.

3. Step 3: Choose correct approach

   Create pivot with index='Store', columns='Month', values='Revenue', aggfunc='sum', then filter columns with sum > 1000, which creates the pivot and then filters columns (months) where total revenue exceeds 1000, matching the requirement.

4. Final Answer:

   Create pivot with index='Store', columns='Month', values='Revenue', aggfunc='sum', then filter columns with sum > 1000. -> Option D

5. Quick Check:

   Pivot then filter sums > 1000 [OK]

Hint: Pivot first, then filter aggregated sums to meet condition [OK]

Common Mistakes:

• Filtering raw data before pivot loses aggregation
• Using mean instead of sum for totals
• Mixing up index and columns parameters