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.