Lazy loaded image
Technology
Lazy loaded image Pandas pivot
Words 293Read Time 1 min
Mar 20, 2020
Apr 20, 2025
type
status
date
slug
summary
tags
category
icon
password

Pandas pivot_table() Tutorial

With Real Data Example (Your Sales Dataset)


Step 1: Setup the DataFrame

Here’s your dataset again, loaded into pandas:

What Is a Pivot Table?
A pivot table reshapes data by aggregating values. Think of it like an Excel PivotTable, but in Python.

Basic Structure


Example 1: Sales by Product & City

Output:
Product
Los Angeles
New York City
San Francisco
AA Batteries (4-pack)
0.00
11.52
0.00
Lightning Cable
44.85
0.00
0.00
USB-C Cable
0.00
0.00
35.85

Example 2: Total Quantity Ordered by Category per Weekday

Output:
Weekday
Batteries
Cables
Monday
0
2
Saturday
0
3
Sunday
3
1

Example 3: Multiple Aggregations with aggfunc

Output:
Product
Sales
Quantity Ordered
AA Batteries (4-pack)
11.52
3.0
Lightning Cable
44.85
1.5
USB-C Cable
35.85
1.5

Example 4: Pivot by Multiple Index Levels

Output:
City
Weekday
Sales
Los Angeles
Monday
29.90
Los Angeles
Saturday
14.95
New York City
Sunday
11.52
San Francisco
Saturday
23.90
San Francisco
Sunday
11.95

Example 5: Add Totals (margins=True)

Output:
Product
Los Angeles
New York City
San Francisco
All
AA Batteries (4-pack)
0.00
11.52
0.00
11.52
Lightning Cable
44.85
0.00
0.00
44.85
USB-C Cable
0.00
0.00
35.85
35.85
All
44.85
11.52
35.85
92.22

Example 6: Pivot Table + Percentage

Output:
City
Sales
% of Total
Los Angeles
44.85
0.4863
New York City
11.52
0.1249
San Francisco
35.85
0.3888

Example 7: Pivot Using Custom Functions

Output:
mean
amax
amin
Batteries
5.0
5
5
Cables
13.75
20
10

Summary Table of Pivot Tips

Feature
Code Snippet Example
Basic Pivot
pd.pivot_table(df, index='A', values='B')
Multi Aggregation
aggfunc={'col1': 'sum', 'col2': 'mean'}
Multi Index
index=['col1', 'col2']
Multi Columns
columns=['City', 'Month']
Add Totals
margins=True
Fill Missing
fill_value=0
Custom Function
aggfunc=np.median / np.std / custom_func

Bonus Tip: Use pivot_table() + .style for pretty reports

 
上一篇
Pandas Intermediate
下一篇
lambda expression