Excel Python Pivot Tables In Excel -Episode 2616
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.
Download the workbook from this episode: https://www.mrexcel.com/youtube/X_Nyo...
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
Welcome to episode 2616 of our Python series! Today, we will be diving into the world of pivot tables in Python. As I update my book, "Microsoft Excel Pivot Table Data Crunching," I realized that I needed to add a chapter on Python pivot tables. So, let's get started!
Before we jump into the technical aspects, let's learn a new word for the day - "craw." It refers to the neck or throat of a bird and is often used to describe something that is stuck or bothersome. Now, let's move on to the main topic - pivot tables in Python.
The first thing to note is that the df.pivot_table function creates an Excel-like pivot table in a data frame. However, there are a few differences in the arguments. For example, instead of using "columns" for column fields, we use "index" for row fields. Additionally, the aggregate function defaults to mean instead of sum, and empty cells show as errors unless we use the "fill_value" argument.
One of the most exciting features of pivot tables in Python is the ability to group by dates. However, we need to be careful when using the grouper function as it can cause errors if we have grand totals turned on. Another limitation is that Python cannot sort by custom lists, unlike Excel. But, on the bright side, pivot tables in Python automatically recalculate without the need for a refresh, and the date grouping options are quite impressive.
In this video, we will be using a data set with 563 rows and various columns such as region, product, date, sector, customer, quantity, revenue, cost of goods sold, and profit. Our Python code will be displayed on the screen as we go through different examples of pivot tables. We will cover basic pivot tables, multiple fields, and even mixing calculations for different fields. We will also explore the various grouping options, including some unique ones like semi-monthly and 14-day periods.
While pivot tables in Python have some limitations compared to Excel, they also offer some advantages. For instance, they automatically recalculate, and the date grouping options are more diverse. However, we do miss some features like grand totals and the ability to sort by custom lists. Overall, pivot tables in Python are a powerful tool for data analysis, and I hope this video has helped you understand them better. Thank you for watching, and don't forget to like, subscribe, and leave a comment below. See you in the next episode of our Python series!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
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
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Watch video Excel Python Pivot Tables In Excel -Episode 2616 online without registration, duration hours minute second in high quality. This video was added by user MrExcel.com 25 August 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 7,874 once and liked it 235 people.