Microsoft Excel Tutorial: Using Python in Excel to create Excel-like pivot tables.
I love pivot tables in Excel. In fact, I’ve written an entire book on Pivot Tables. So when I saw that Python has a function to generate “Excel-like” Pivot Tables in a new data frame, I wanted to try it out.
The Python Pivot Table is missing a few things:
1. Row Fields are called Index
2. Defaults to Average instead of Sum
3. Empty cells show as errors. Use fill_value
4. No Grand Totals by default! Turn on with margins=True
5. When you add Grand Totals, they are called “All” Unless you change them with Margins_Name
6. When you group by dates, you can’t have grand totals
7. Does not sort by Custom Lists
8. Odd arrangement of headings when 2 row fields
But Python pivot tables have some advantages over Excel pivot tables:
Advantages over Excel
1. Automatic Recalc without Refresh
2. Date Grouping Offers some amazing options
In this episode, using Python in Excel to build data frames that look like pivot tables. You will see:
• Basic pivot table
• Adding Grand Totals
• Filling empty cells with zero
• Sorting or not sorting
• multiple row fields, column fields
• multiple value fields
• Sum of one field, mean of another
• Grouping dates by Month, Week, Quarter, Semi-Monthly, 3 days, 14 days, 2 weeks
• Crazy Excel formulas to reformat the top 2 rows of the pivot table
Table of Contents
(0:00) Podcast Words of the Day: Craw
(0:32) Syntax of Python Pivot_Table
(1:06) Contrast Python and Excel Pivot Tables
(1:58) Python better than Excel pivot tables
(2:18) Source data
(2:33) First Python Pivot Table
(2:44) #NUM! errors with Fill_Value
(3:03) Change Average to Sum
(3:30) Why blank 2nd row
(3:49) Adding Grand Totals with Margins=True
(4:35) Sorting a python pivot table in Excel
(5:07) Adding second row field
(5:50) Second column field
(6:23) Adding second Values field
(6:48) Sum Revenue and Average Profit
(7:10) Grouping Dates by Month
(8:20) Group by Weeks & More
(9:44) Blank Row 2
(10:19) Fixing the Python Pivot Table Formatting
(10:57) Can’t combine Python formula with Excel formula
(11:21) Drunk kid on Christmas
(12:15) Wrap-up