pivot_table() for summarization in Pandas - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the pivot_table() function in pandas?

The pivot_table() function is used to summarize and aggregate data in a DataFrame by creating a new table that groups data by one or more keys and applies aggregation functions like sum, mean, or count.

beginner

Which parameters are commonly used in pivot_table()?

• data: The DataFrame to summarize.
• index: Column(s) to group by on rows.
• columns: Column(s) to group by on columns.
• values: Column(s) to aggregate.
• aggfunc: Aggregation function like sum, mean, or count.

intermediate

How does pivot_table() handle missing values by default?

By default, pivot_table() fills missing values with NaN. You can change this behavior using the fill_value parameter to replace missing values with a specific value like 0.

intermediate

What is the difference between pivot() and pivot_table()?

pivot() reshapes data without aggregation and requires unique index/column pairs. pivot_table() allows aggregation and can handle duplicate entries by applying aggregation functions.

beginner

Write a simple example of using pivot_table() to find the average sales per product category.

Example:<br><pre>import pandas as pd

data = {'Category': ['A', 'A', 'B', 'B'], 'Sales': [100, 150, 200, 250]}
df = pd.DataFrame(data)

pivot = df.pivot_table(index='Category', values='Sales', aggfunc='mean')
print(pivot)</pre>

What does the aggfunc parameter in pivot_table() specify?

A The columns to use as column labels

B The columns to use as row labels

C The aggregation function to apply to grouped data

D The DataFrame to summarize

The aggfunc parameter tells pivot_table() how to combine data, like using sum, mean, or count.

Which parameter in pivot_table() controls the rows of the new table?

A index

B values

C columns

D aggfunc

The index parameter sets which column(s) become the rows in the pivot table.

If your data has duplicate entries for the same index and column, which function should you use to summarize it?

A pivot()

B groupby()

C merge()

D pivot_table()

pivot_table() can handle duplicates by aggregating them, unlike pivot().

What will pivot_table() fill missing values with by default?

A NaN

B Empty string

C 0

D Mean of the column

By default, missing values are shown as NaN unless fill_value is set.

Which of these is NOT a valid aggregation function for aggfunc?

A mean

B sort

C count

D sum

sort is not an aggregation function; it is used for ordering data.

Explain how you would use pivot_table() to summarize sales data by region and product.

Think about grouping rows by region and columns by product, then summarizing sales.

Describe the difference between pivot() and pivot_table() and when to use each.

Consider if your data has duplicates or needs aggregation.

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