pivot_table() for summarization in Pandas - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a pivot table that summarizes the average sales by region.

Pandas

import pandas as pd

data = {'Region': ['East', 'West', 'East', 'West'], 'Sales': [250, 150, 200, 300]}
df = pd.DataFrame(data)
pivot = df.pivot_table(values='Sales', index=[1])
print(pivot)

Drag options to blanks, or click blank then click option'

A'values'

B'Sales'

C'index'

D'Region'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Sales' as the index instead of 'Region'.

Confusing 'values' and 'index' parameters.

The index parameter specifies the column to group by. Here, we group sales by 'Region'.

2fill in blank

medium

Complete the code to create a pivot table that calculates the sum of sales for each product category.

Pandas

data = {'Category': ['A', 'B', 'A', 'B'], 'Sales': [100, 200, 150, 300]}
df = pd.DataFrame(data)
pivot = df.pivot_table(values='Sales', index='Category', aggfunc=[1])
print(pivot)

Drag options to blanks, or click blank then click option'

A'mean'

B'sum'

C'count'

D'max'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'mean' instead of 'sum' for aggregation.

Leaving aggfunc empty, which defaults to mean.

The aggfunc parameter defines how to aggregate the data. To get total sales, use 'sum'.

3fill in blank

hard

Fix the error in the code to create a pivot table that shows average sales by region and product.

Pandas

data = {'Region': ['East', 'East', 'West', 'West'], 'Product': ['A', 'B', 'A', 'B'], 'Sales': [100, 150, 200, 250]}
df = pd.DataFrame(data)
pivot = df.pivot_table(values='Sales', index=['Region', [1]])
print(pivot)

Drag options to blanks, or click blank then click option'

A'Product'

B'Category'

C'Region'

D'Sales'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Sales' as a grouping column instead of 'Product'.

Using a column name not in the DataFrame.

The index parameter can take a list of columns to group by multiple keys. Here, we add 'Product' to group sales by both region and product.

4fill in blank

hard

Fill both blanks to create a pivot table that counts the number of sales entries for each region and product.

Pandas

data = {'Region': ['North', 'South', 'North', 'South'], 'Product': ['X', 'X', 'Y', 'Y'], 'Sales': [10, 20, 30, 40]}
df = pd.DataFrame(data)
pivot = df.pivot_table(index=[1], columns=[2], aggfunc='count')
print(pivot)

Drag options to blanks, or click blank then click option'

A'Region'

B'Product'

C'Sales'

D'Category'

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping index and columns values.

Using a column not in the DataFrame.

The index groups rows, and columns groups columns. To count sales entries by region and product, use 'Region' for index and 'Product' for columns.

5fill in blank

hard

Fill all three blanks to create a pivot table that shows the maximum sales for each region and product, and rename the resulting column to 'MaxSales'.

Pandas

data = {'Region': ['East', 'East', 'West', 'West'], 'Product': ['A', 'B', 'A', 'B'], 'Sales': [120, 180, 160, 200]}
df = pd.DataFrame(data)
pivot = df.pivot_table(index=[1], columns=[2], values=[3], aggfunc='max')
pivot.columns.name = 'Product'
pivot.rename(columns=lambda x: x, inplace=True)
pivot.columns = pivot.columns.rename('MaxSales')
print(pivot)

Drag options to blanks, or click blank then click option'

A'Region'

B'Product'

C'Sales'

D'Category'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names for index or columns.

Not specifying values parameter.

To summarize max sales by region and product, set index='Region', columns='Product', and values='Sales'.