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.

Practice

1. What does the index parameter do in the pivot_table() function?

easy

A. It sets the rows of the pivot table.

B. It sets the columns of the pivot table.

C. It defines the aggregation function.

D. It filters the data before pivoting.

Solution

1. Step 1: Understand the role of index in pivot_table()

   The index parameter specifies which column(s) to use as the rows in the resulting pivot table.

2. Step 2: Differentiate from other parameters

   The columns parameter sets the columns, aggfunc sets the aggregation, and filtering is done separately.

3. Final Answer:

   It sets the rows of the pivot table. -> Option A

4. Quick Check:

   index = rows [OK]

Hint: Remember: index = rows, columns = columns [OK]

Common Mistakes:

• Confusing index with columns
• Thinking aggfunc sets rows
• Assuming index filters data

2. Which of the following is the correct syntax to create a pivot table with pandas using pivot_table()?

easy

A. pd.pivot_table(data, index='Category', columns='Year', aggfunc='sum')

B. pd.pivot(data, index='Category', columns='Year', aggfunc='sum')

C. pd.pivot_table(data, rows='Category', cols='Year', aggfunc='sum')

D. pd.pivot_table(data, groupby='Category', by='Year', aggfunc='sum')

Solution

1. Step 1: Recall the correct function and parameters

   The correct function is pd.pivot_table() with parameters index for rows and columns for columns.

2. Step 2: Check each option

   pd.pivot_table(data, index='Category', columns='Year', aggfunc='sum') uses correct function and parameters. Options B, C, and D use incorrect function names or parameter names.

3. Final Answer:

   pd.pivot_table(data, index='Category', columns='Year', aggfunc='sum') -> Option A

4. Quick Check:

   Correct function and parameters = pd.pivot_table(data, index='Category', columns='Year', aggfunc='sum') [OK]

Hint: Use index= for rows, columns= for columns in pivot_table [OK]

Common Mistakes:

• Using pd.pivot instead of pd.pivot_table
• Wrong parameter names like rows or cols
• Mixing groupby parameters with pivot_table

3. Given the DataFrame df below:

   Product  Region  Sales
0  A        East    100
1  B        East    150
2  A        West    200
3  B        West    250
4  A        East    50
5  B        West    100

What is the output of:

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

medium

A. Region East West
Product
A 150 300
B 250 350

B. Region East West
Product
A 150 200
B 150 350

C. Region East West
Product
A 150 200
B 250 350

D. Region East West
Product
A 150 200
B 150 250

Solution

1. Step 1: Group data by Product and Region, summing Sales

   For Product A and East: 100 + 50 = 150; Product A and West: 200; Product B and East: 150; Product B and West: 250 + 100 = 350.

2. Step 2: Arrange results in pivot table format

   Rows are Products, columns are Regions, values are summed Sales as calculated.

3. Final Answer:

   Region East West Product A 150 200 B 250 350 -> Option C

4. Quick Check:

   Sum Sales by Product and Region = Region East West Product A 150 200 B 250 350 [OK]

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

Common Mistakes:

• Adding wrong sales values
• Mixing up East and West sums
• Using count instead of sum

4. What is wrong with this code snippet?

pd.pivot_table(df, index='Product', columns='Region', values='Sales', aggfunc='average')

medium

A. The index parameter must be a list, not a string.

B. The aggfunc 'average' is not a valid aggregation function.

C. The values parameter cannot be a column name.

D. The columns parameter should be 'cols' instead of 'columns'.

Solution

1. Step 1: Check the aggfunc parameter value

   The valid aggregation functions include 'mean', 'sum', 'count', etc. 'average' is not recognized by pandas.

2. Step 2: Verify other parameters

   columns is correct, values can be a column name string, and index can be a string or list.

3. Final Answer:

   The aggfunc 'average' is not a valid aggregation function. -> Option B

4. Quick Check:

   aggfunc must be valid like 'mean' [OK]

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

Common Mistakes:

• Using 'average' instead of 'mean'
• Changing columns to cols
• Passing values as non-column

5. You have a sales DataFrame with columns: Store, Product, Month, and Revenue. You want a pivot table showing total revenue per store for each product, but only for months January and February. Which code correctly creates this pivot table?

hard

A. pd.pivot_table(df, index='Store', columns='Product', values='Revenue', aggfunc='sum').loc[['January', 'February']]

B. pd.pivot_table(df, index='Store', columns='Product', values='Revenue', aggfunc='sum', filter=['January', 'February'])

C. pd.pivot_table(df, index='Month', columns='Product', values='Revenue', aggfunc='sum').query('Month in ["January", "February"]')

D. pd.pivot_table(df[df['Month'].isin(['January', 'February'])], index='Store', columns='Product', values='Revenue', aggfunc='sum')

Solution

1. Step 1: Filter data for January and February

   Use df[df['Month'].isin(['January', 'February'])] to select only those months before pivoting.

2. Step 2: Create pivot table with Store as rows and Product as columns

   Use index='Store', columns='Product', and sum revenue with aggfunc='sum'.

3. Final Answer:

   pd.pivot_table(df[df['Month'].isin(['January', 'February'])], index='Store', columns='Product', values='Revenue', aggfunc='sum') -> Option D

4. Quick Check:

   Filter first, then pivot with sum [OK]

Hint: Filter data before pivoting to limit rows [OK]

Common Mistakes:

• Trying to filter inside pivot_table with invalid parameter
• Filtering after pivoting causing wrong results
• Using wrong index or columns