pivot_table() for summarization in Pandas - Step-by-Step Execution

Concept Flow - pivot_table() for summarization

Start with DataFrame

↓

Choose index (rows)

↓

Choose columns (optional)

↓

Choose values to summarize

↓

Choose aggregation function

↓

pivot_table() creates summary table

↓

Use or display summarized data

pivot_table() takes a DataFrame and summarizes data by grouping rows and columns, then applying an aggregation like sum or mean.

Execution Sample

Pandas

import pandas as pd

data = {'City': ['NY', 'LA', 'NY', 'LA'],
        'Year': [2020, 2020, 2021, 2021],
        'Sales': [100, 200, 150, 250]}

df = pd.DataFrame(data)

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

This code summarizes sales by city and year, showing total sales for each city-year pair.

Execution Table

Step	Action	DataFrame State	Result
1	Create DataFrame	{'City': ['NY', 'LA', 'NY', 'LA'], 'Year': [2020, 2020, 2021, 2021], 'Sales': [100, 200, 150, 250]}	DataFrame with 4 rows and 3 columns
2	Call pivot_table with index='City', columns='Year', values='Sales', aggfunc='sum'	Same DataFrame	Grouped sales by City and Year, summed values
3	Group rows by City: NY and LA	Groups: NY (rows 0,2), LA (rows 1,3)	Two groups formed
4	Group columns by Year: 2020 and 2021	Columns split into 2020 and 2021	Two columns for years
5	Sum Sales for NY in 2020	Rows with City=NY and Year=2020	100
6	Sum Sales for NY in 2021	Rows with City=NY and Year=2021	150
7	Sum Sales for LA in 2020	Rows with City=LA and Year=2020	200
8	Sum Sales for LA in 2021	Rows with City=LA and Year=2021	250
9	Create pivot table with sums	Aggregated sums	pivot_table with City as index, Year as columns, Sales sums as values
10	Display pivot table	Final summarized table	City\Year 2020 2021 LA 200 250 NY 100 150
11	End	No further action	Execution complete

💡 All groups processed and summarized, pivot_table created successfully

Variable Tracker

Variable	Start	After Step 2	After Step 9	Final
df	undefined	DataFrame with 4 rows and 3 columns	Same DataFrame	Same DataFrame
summary	undefined	undefined	pivot_table DataFrame with summarized sales	pivot_table DataFrame with summarized sales

Key Moments - 3 Insights

Why do we need to specify 'index' and 'columns' in pivot_table?

The 'index' defines the rows to group by, and 'columns' define the columns to group by. This creates a grid to summarize data. See execution_table steps 3 and 4.

What happens if we don't specify an aggregation function?

pivot_table defaults to 'mean' aggregation. In this example, we used 'sum' explicitly (step 2). Without it, the result would show average sales instead of total.

Why is the result a new DataFrame and not modifying the original?

pivot_table returns a new summarized DataFrame, leaving the original data unchanged. See variable_tracker where 'summary' is new, original 'df' stays the same.

Visual Quiz - 3 Questions

Look at the execution_table at step 5. What is the sum of Sales for NY in 2020?

A 100

B 150

C 200

D 250

💡 Hint

Check the 'Result' column at step 5 in the execution_table.

At which step does the pivot_table create the final summarized table?

A Step 3

B Step 7

C Step 9

D Step 11

💡 Hint

Look for the step where the pivot_table with sums is created in the execution_table.

If we change aggfunc from 'sum' to 'mean', how would the summary change?

A It would show the total sales instead of average

B It would show the average sales instead of total

C It would show the maximum sales

D It would cause an error

💡 Hint

Recall the key_moments explanation about default aggregation and aggfunc parameter.

Concept Snapshot

pivot_table() summarizes data by grouping rows (index) and columns,
then applies an aggregation function (aggfunc) like sum or mean.
Syntax: df.pivot_table(index='row_col', columns='col_col', values='val_col', aggfunc='sum')
Returns a new DataFrame with summarized values.
Useful for quick data summaries and cross-tabulations.

Full Transcript

We start with a DataFrame containing sales data by city and year. Using pivot_table(), we choose 'City' as rows (index), 'Year' as columns, and 'Sales' as values to summarize. The aggregation function 'sum' adds sales for each city-year pair. Step by step, the data groups by city and year, sums sales, and creates a new summarized table. The original DataFrame remains unchanged. This method helps quickly see total sales per city and year in a clear table format.

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