Pivot tables with pivot_table() in Data Analysis Python - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a basic pivot_table aggregation

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

Data Analysis Python

import pandas as pd

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

pivot = df.pivot_table(index='City', columns='Year', values='Sales', aggfunc='sum')
print(pivot)

A

Year  2020  2021
City             
LA     200.0  250.0
NY     100.0  450.0

B

Year  2020  2021
City             
LA     200.0  250.0
NY     100.0  300.0

C

Year  2020  2021
City             
LA     200.0  250.0
NY     100.0  150.0

D

Year  2020  2021
City             
LA     200.0  300.0
NY     100.0  450.0

💡 Hint

Sum sales grouped by City and Year.

The pivot_table sums Sales for each City and Year. NY in 2021 has two entries: 150 and 300, summed to 450.

❓ data_output

intermediate

Number of items in pivot table result

How many cells (non-NaN) are in the resulting pivot table from this code?

Data Analysis Python

import pandas as pd

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

pivot = df.pivot_table(index='Product', columns='Store', values='Quantity', aggfunc='sum')

A 6

B 4

C 5

D 3

💡 Hint

Count cells with actual sums, ignoring missing combinations.

The pivot table has Products A, B, C (rows) and Stores X, Y (columns). A-X, A-Y, B-X, B-Y, C-X have values (5 non-NaN cells); C-Y is NaN.

🔧 Debug

advanced

Identify the error in pivot_table usage

What error does this code raise?

Data Analysis Python

import pandas as pd

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

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

A KeyError: 'NonExistent'

B TypeError: aggfunc must be callable or string

C ValueError: index and columns must be different

D No error, outputs pivot table

💡 Hint

Check if the columns argument exists in DataFrame.

The 'columns' parameter refers to a column not in the DataFrame, causing KeyError.

🚀 Application

advanced

Using pivot_table to find average ratings

Given this data, which pivot_table call produces the average rating per user per product?

Data Analysis Python

import pandas as pd

data = {'User': ['Alice', 'Bob', 'Alice', 'Bob', 'Alice'],
        'Product': ['X', 'X', 'Y', 'Y', 'X'],
df = pd.DataFrame(data)

pivot = df.pivot_table(index='User', columns='Product', values='Rating', aggfunc='mean')

A df.pivot_table(index='User', columns='Product', values='Rating', aggfunc='count')

B df.pivot_table(index='Product', columns='User', values='Rating', aggfunc='sum')

C df.pivot_table(index='User', columns='Product', values='Rating', aggfunc='max')

D df.pivot_table(index='User', columns='Product', values='Rating', aggfunc='mean')

💡 Hint

Look for average rating per user per product.

Option D groups by User and Product and calculates mean rating, matching the requirement.

🧠 Conceptual

expert

Understanding fill_value in pivot_table

What is the effect of using fill_value=0 in pivot_table?

A It removes rows with any NaN values from the pivot table.

B It replaces all NaN values in the pivot table with 0.

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

D It causes pivot_table to ignore missing values during aggregation.

💡 Hint

Think about how missing data is handled in the output.

fill_value replaces missing values (NaN) in the pivot table with the specified value, here 0.