pivot_table() for summarization in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a simple pivot_table()

What is the output of this code snippet using pandas pivot_table()?

Pandas

import pandas as pd

data = {'City': ['NY', 'LA', 'NY', 'LA', 'NY'],
        'Year': [2020, 2020, 2021, 2021, 2020],
        'Sales': [100, 200, 150, 250, 300]}
df = pd.DataFrame(data)

result = pd.pivot_table(df, values='Sales', index='City', columns='Year', aggfunc='sum', fill_value=0)
print(result)

A

Year  2020  2021
City           
LA      200   250
NY      300   150

B

Year  2020  2021
City           
LA      100   250
NY      400   150

C

Year  2020  2021
City           
LA      200   150
NY      300   250

D

Year  2020  2021
City           
LA      200   250
NY      400   150

💡 Hint

Think about how pivot_table sums Sales grouped by City and Year.

The pivot_table sums Sales for each City and Year. LA has 200 in 2020 and 250 in 2021. NY has 100 + 300 = 400 in 2020 and 150 in 2021.

❓ data_output

intermediate

Number of items in pivot_table result

Given this pivot_table code, how many total cells (values) are in the resulting DataFrame?

Pandas

import pandas as pd

data = {'Product': ['A', 'B', 'A', 'B', 'C'],
        'Region': ['East', 'East', 'West', 'West', 'East'],
        'Quantity': [10, 20, 15, 25, 30]}
df = pd.DataFrame(data)

pivot = pd.pivot_table(df, values='Quantity', index='Product', columns='Region', aggfunc='sum', fill_value=0)
print(pivot)

A 6

B 5

C 4

D 9

💡 Hint

Count unique Products and Regions after pivot.

There are 3 unique Products (A, B, C) and 2 unique Regions (East, West). The pivot table has 3 rows and 2 columns, so 3*2=6 cells.

🔧 Debug

advanced

Identify the error in pivot_table aggregation

What error will this code raise when run?

Pandas

import pandas as pd

data = {'Category': ['X', 'Y', 'X'], 'Value': [5, 10, 15]}
df = pd.DataFrame(data)

pivot = pd.pivot_table(df, values='Value', index='Category', aggfunc='mean', columns='NonExistent')
print(pivot)

A ValueError: No numeric types to aggregate

B KeyError: 'NonExistent'

C TypeError: aggfunc must be callable or string

D No error, prints pivot table with NaN values

💡 Hint

Check if the 'columns' argument refers to a valid DataFrame column.

The 'columns' parameter is set to 'NonExistent', which is not a column in df. This causes a KeyError.

🚀 Application

advanced

Using pivot_table() to find average scores by group

You have a DataFrame with student scores. Which pivot_table call correctly computes the average score per subject and gender?

Pandas

import pandas as pd

data = {'Student': ['Ann', 'Bob', 'Cathy', 'Dan'],
        'Gender': ['F', 'M', 'F', 'M'],
        'Subject': ['Math', 'Math', 'English', 'English'],
        'Score': [90, 80, 85, 75]}
df = pd.DataFrame(data)

A pd.pivot_table(df, values='Score', index='Subject', columns='Gender', aggfunc='mean')

B pd.pivot_table(df, values='Score', index='Gender', columns='Subject', aggfunc='sum')

C pd.pivot_table(df, values='Score', index='Student', columns='Subject', aggfunc='mean')

D pd.pivot_table(df, values='Score', index='Subject', columns='Gender', aggfunc='sum')

💡 Hint

Average score per subject and gender means subject as rows, gender as columns, mean aggregation.

Option A groups by Subject (rows) and Gender (columns) and calculates mean Score, which matches the requirement.

🧠 Conceptual

expert

Understanding fill_value in pivot_table()

What is the effect of the fill_value parameter in pandas pivot_table()?

A It changes the aggregation function to fill missing values with fill_value.

B It filters out rows with missing values before aggregation.

C It replaces missing values in the pivot table with the specified fill_value.

D It renames the columns with the fill_value string.

💡 Hint

Think about what happens when some groups have no data for certain columns.

fill_value replaces NaN values in the pivot table result with the given value, making the table easier to read and use.