Another great video, thank you! As another commenter mentioned, you get the blank cells because the row index is not really part of the data. If you use reset_index, it will convert the row headers to columns in the data frame and should flatten the data frame. Square brackets represent a list and you can define them elsewhere in the script and reuse in multiple places. Even single-items can be in a list. So you could define the row-groups first as a list, then define several pivot_table objects with different column group lists. Curly brackets are a dictionary of key: value. These can get really complex and very powerful. As a simple example: dict = {"FirstName": "Owen", "LastName": "Price"} which can then be accessed easily by dict["FirstName"] (returns Owen).
Just got a call from Suat who showed me that I could sort the final dataframe after the fact, including by the Grand Total column. The one downside of this is that it moves the Total to the top, which is a feature that Roger Govier has been asking for for a long time.
@EciekPeciek This should have been another out take. I read about .Reset_Index(). I tried it. Excel gave me a big "Eff you" and left the index in anyways. There was some excellent swearing bleeped out of that footage.
Drunk kid on Christmas is the perfect description - I’m sure we’ll see a lot of those reactions in the next few weeks! 😂 Right now, I’m still the kid outside in the cold looking in through the window at all the amazing toys you got… crossing my fingers to be in the next stage of the roll-out!
@@Vandalfoe Yes - AutoRefresh is definitely big. And, I think that one day, I will be able to chain 7 big data processing tasks together and task #4 might be pivoting.
"Here's a way more difficult way to make a pivot table. A process that took fifteen seconds in the past now takes three minutes, and you have to know a programming language." I guess I still don't get the really compelling case for Python in Excel.
Yes. Right now, I am trying to figure out all the building blocks. I am hoping in five more weekends, it will all make sense and I can build a castle from all the little pieces.
Thanks Mr.Excel for introducing us to Python via Excel. On Pivot Tables via this route: I’m not convinced: we can always write our own Lambda.Let functions to automate those, if we want (done that quite a bit in the recent past) but classic PvtTables are much more flexible and at least understandable for the end user (and possibly they can modify those to their needs). It seems to me that those other Python functions you (&Mynda, &Leila) showed to us, will have more benefits.
I don't think I would ever do a pivot table in Python if it were all I was doing. But if I had a big set of steps..... This, then that, then this, then a pivot, then something, then a chart, I figured it would be good to know how to do it.
Great Video! I still don't have Python in my version of Excel but and I can't wait for it. One of the great features that Python and Pandas give to users is the ability to chain their code. In your example to sort the dataframe all you have to do is add the .sort_values function at the end: df.pivot_table(...).sort_values(by='Col1') Also I think by adding .reset_index() is going to "flatten" the dataframe and give you the format you want.
Chaining code is a great suggestion. I will have to do a short video with my version of .reset_index(). I am sure the problem is me, but every time I try it, Excel gives me a big "eff you" and puts the index in anyway.
As mentioned in other comments, a way more complex way to build a pivot table... what's cool about excel over programming is that excel is fast, efficient, accessible and simple; for these reasons, software engineers don't like Excel, they'd much rather overcomplicate things to make themselves look smarter. The real game changer in Excel would be either update VBE, and have bulletproof intellisense, and better code from the macro recorder or embed Python in Excel with an IDE, until that happens learning to write Python in an Excel cell it's a waste of my time
It is somewhat over my head right now too. I am hoping in 45 days, I will have enough tools that I can chain together 7 python tasks... This, then that, then this, then pivot, then something else, then a great visualization. Right now, I am trying to figure out the building blocks. If I ever get it, then there will be a new book, Beginning Python for Excel Pros".
Thanks for that feedback and roadmap! Still thinking I would be better served in learning DAX. Between Excel, and the Power Tools, there's just so much to learn.@@MrXL
