Pivot tables with pivot_table() in Data Analysis Python - Step-by-Step Execution

Concept Flow - Pivot tables with pivot_table()

Start with DataFrame

↓

Choose index (rows)

↓

Choose columns (optional)

↓

Choose values to aggregate

↓

Choose aggregation function

↓

pivot_table() creates summary table

↓

Use or display pivot table

Pivot tables summarize data by grouping rows and columns and applying aggregation functions.

Execution Sample

Data Analysis Python

import pandas as pd

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

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

This code creates a pivot table showing total sales by city and year.

Execution Table

Step	Action	DataFrame State	Pivot Table State	Explanation
1	Create DataFrame df	{'City': ['NY', 'LA', 'NY', 'LA'], 'Year': [2020, 2020, 2021, 2021], 'Sales': [100, 200, 150, 250]}	None	Initial data with city, year, and sales columns.
2	Call pivot_table()	df unchanged	Empty	Start creating pivot table with index='City', columns='Year', values='Sales', aggfunc='sum'.
3	Group data by City and Year	df unchanged	Groups: NY-2020, LA-2020, NY-2021, LA-2021	Data grouped by city and year combinations.
4	Aggregate Sales with sum	df unchanged	{NY: {2020: 100, 2021: 150}, LA: {2020: 200, 2021: 250}}	Sum sales for each city-year group.
5	Build pivot table DataFrame	df unchanged	Year 2020 2021 City LA 200 250 NY 100 150	Pivot table shows sales by city (rows) and year (columns).
6	Print pivot table	df unchanged	Same as step 5	Output the pivot table to console.
7	End	df unchanged	Pivot table ready	Pivot table creation complete.

💡 All data grouped and aggregated; pivot table created successfully.

Variable Tracker

Variable	Start	After pivot_table call	Final
df	Empty	{'City': ['NY', 'LA', 'NY', 'LA'], 'Year': [2020, 2020, 2021, 2021], 'Sales': [100, 200, 150, 250]}	Same as after call
pivot	None	Empty	Year 2020 2021 City LA 200 250 NY 100 150

Key Moments - 3 Insights

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

The 'index' defines the rows of the pivot table, and 'columns' define the columns. This organizes data into a grid for easy comparison, as shown in execution_table step 4 and 5.

What happens if multiple rows have the same index and column values?

pivot_table groups these rows and applies the aggregation function (like sum) to combine their values, as seen in step 4 where sales are summed.

Why do we use aggfunc='sum'?

Because there can be multiple sales values per group, sum adds them up to give total sales per city-year group, demonstrated in step 4.

Visual Quiz - 3 Questions

Look at the execution_table at step 4, what is the sum of sales for city 'LA' in year 2021?

A 150

B 200

C 250

D 100

💡 Hint

Check the 'Pivot Table State' column at step 4 for LA and year 2021.

At which step does the pivot table get its final shape with rows and columns?

A Step 5

B Step 3

C Step 2

D Step 6

💡 Hint

Look for when the pivot table shows the grid with City as rows and Year as columns.

If we change aggfunc from 'sum' to 'mean', how would the pivot table values change?

A Values would be the total sales per city-year group

B Values would be the average sales per city-year group

C Values would be the count of sales entries

D Pivot table would not change

💡 Hint

aggfunc controls how grouped values are combined; 'mean' calculates average.

Concept Snapshot

pivot_table() creates a summary table from data.
Use index= for rows, columns= for columns.
values= selects data to aggregate.
aggfunc= defines how to combine data (sum, mean, etc.).
Result is a DataFrame showing grouped summaries.

Full Transcript

We start with a DataFrame containing city, year, and sales data. Using pivot_table(), we choose 'City' as the index (rows), 'Year' as columns, and 'Sales' as values to summarize. The function groups data by city and year, then sums sales for each group. The result is a new table showing total sales per city for each year. This pivot table helps us quickly compare sales across cities and years. Key points include specifying index and columns to organize data, and using aggfunc to control aggregation. The process ends with a clear summary table ready for analysis.

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