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
- Author:Entropyobserver
- URL:https://tangly1024.com/article/1dbd698f-3512-8028-a004-def190322be3
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!