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.

Practice

1.

What does the pivot_table() function in pandas primarily do?

easy

A. It merges two dataframes based on a key column.

B. It sorts data in ascending or descending order.

C. It summarizes data by grouping rows and columns and calculating statistics.

D. It filters rows based on conditions.

Solution

1. Step 1: Understand the purpose of pivot_table()

   The pivot_table() function groups data by specified rows and columns and calculates summary statistics like mean, sum, or count.

2. Step 2: Compare with other options

   Sorting, filtering, and merging are different pandas operations, not the main purpose of pivot_table().

3. Final Answer:

   It summarizes data by grouping rows and columns and calculating statistics. -> Option C

4. Quick Check:

   pivot_table() = summary by groups [OK]

Hint: pivot_table groups and summarizes data quickly [OK]

Common Mistakes:

• Confusing pivot_table with sorting or filtering
• Thinking pivot_table merges dataframes
• Assuming pivot_table only reshapes data without aggregation

2.

Which of the following is the correct syntax to create a pivot table that calculates the mean of column 'Sales' grouped by 'Region'?

import pandas as pd
df = pd.DataFrame({
    'Region': ['East', 'West', 'East', 'West'],
    'Sales': [100, 200, 150, 300]
})

# Which line is correct?

easy

A. pd.pivot_table(df, index='Region', values='Sales', aggfunc='mean')

B. pd.pivot_table(df, columns='Region', values='Sales', aggfunc='sum')

C. pd.pivot_table(df, index='Sales', values='Region', aggfunc='mean')

D. pd.pivot_table(df, values='Region', index='Sales', aggfunc='count')

Solution

1. Step 1: Identify grouping and aggregation

   We want to group by 'Region' (index) and calculate mean of 'Sales' (values) using aggfunc='mean'.

2. Step 2: Check each option

   pd.pivot_table(df, index='Region', values='Sales', aggfunc='mean') matches the requirement exactly. Options B, C, and D either swap index and values or use wrong aggfunc.

3. Final Answer:

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

4. Quick Check:

   Group by Region, mean Sales = pd.pivot_table(df, index='Region', values='Sales', aggfunc='mean') [OK]

Hint: index=group, values=summary column, aggfunc=stat [OK]

Common Mistakes:

• Swapping index and values parameters
• Using wrong aggregation function
• Confusing columns parameter with index

3.

Given the following code, what is the output?

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

medium

A. Category X Y
A 10 20
B 30 40

B. Type X Y
Category
A 10.0 20.0
B 30.0 40.0

C. Type X Y
Category
A 20.0 10.0
B 40.0 30.0

D. Type X Y
Category
A 30.0 40.0
B 10.0 20.0

Solution

1. Step 1: Understand pivot_table parameters

   Grouping by 'Category' as rows, 'Type' as columns, summing 'Value'.

2. Step 2: Calculate sums for each group

   For Category A and Type X: 10, Type Y: 20; For B and X: 30, Y: 40.

3. Final Answer:

   Type X Y Category A 10.0 20.0 B 30.0 40.0 -> Option B

4. Quick Check:

   Sum by Category and Type = Type X Y Category A 10.0 20.0 B 30.0 40.0 [OK]

Hint: Sum values by row index and column labels [OK]

Common Mistakes:

• Mixing up rows and columns in output
• Confusing sum with mean aggregation
• Ignoring float formatting in output

4.

What is wrong with this pivot_table code?

import pandas as pd
df = pd.DataFrame({
    'Group': ['G1', 'G1', 'G2'],
    'Score': [5, 10, 15]
})
pivot = pd.pivot_table(df, index='Group', values='Score', aggfunc='average')
print(pivot)

medium

A. The aggfunc 'average' is invalid; should be 'mean'.

B. The 'values' parameter should be 'Group' instead of 'Score'.

C. The 'index' parameter should be 'Score' instead of 'Group'.

D. The DataFrame is missing a column for aggregation.

Solution

1. Step 1: Check aggfunc parameter

   The aggfunc parameter must be a valid aggregation function like 'mean', 'sum', or 'count'. 'average' is not recognized by pandas.

2. Step 2: Verify other parameters

   Index='Group' and values='Score' are correct for grouping and aggregation.

3. Final Answer:

   The aggfunc 'average' is invalid; should be 'mean'. -> Option A

4. Quick Check:

   aggfunc='mean' not 'average' [OK]

Hint: Use 'mean' not 'average' for aggregation [OK]

Common Mistakes:

• Using 'average' instead of 'mean' in aggfunc
• Swapping index and values parameters
• Assuming aggfunc accepts any descriptive name

5.

You have a sales dataset with columns 'Store', 'Product', and 'Revenue'. You want to create a pivot table that shows the total revenue for each store and product combination, but if a combination is missing, it should show 0 instead of NaN. Which code correctly achieves this?

hard

A. pd.pivot_table(df, index='Store', columns='Product', values='Revenue', aggfunc='mean', fill_value=0)

B. pd.pivot_table(df, index='Store', columns='Product', values='Revenue', aggfunc='sum').fillna('0')

C. pd.pivot_table(df, index='Product', columns='Store', values='Revenue', aggfunc='sum', fill_value=null)

D. pd.pivot_table(df, index='Store', columns='Product', values='Revenue', aggfunc='sum', fill_value=0)

Solution

1. Step 1: Identify grouping and aggregation

   We want total revenue (sum) grouped by Store (rows) and Product (columns).

2. Step 2: Handle missing combinations

   Use fill_value=0 in pivot_table to replace NaN with 0 for missing pairs.

3. Step 3: Check each option

   pd.pivot_table(df, index='Store', columns='Product', values='Revenue', aggfunc='sum', fill_value=0) uses correct index, columns, aggfunc, and fill_value=0. pd.pivot_table(df, index='Store', columns='Product', values='Revenue', aggfunc='sum').fillna('0') fills NaN after pivot but with string '0' (wrong type). pd.pivot_table(df, index='Store', columns='Product', values='Revenue', aggfunc='mean', fill_value=0) uses mean instead of sum. pd.pivot_table(df, index='Product', columns='Store', values='Revenue', aggfunc='sum', fill_value=null) swaps index and columns and fill_value=null.

4. Final Answer:

   pd.pivot_table(df, index='Store', columns='Product', values='Revenue', aggfunc='sum', fill_value=0) -> Option D

5. Quick Check:

   sum + fill_value=0 for missing = pd.pivot_table(df, index='Store', columns='Product', values='Revenue', aggfunc='sum', fill_value=0) [OK]

Hint: Use fill_value=0 to replace NaN in pivot_table [OK]

Common Mistakes:

• Using fillna('0') which creates string values
• Using mean instead of sum for total revenue
• Swapping index and columns parameters