Pivot tables with Pandas 

I'm delighted to hear that you enjoyed it! Thanks so much for your kind words.

Glad you enjoyed it!

Glad it helped!

Glad it helped!

Thanks so much for your kind words, and for your support! (And hey, if you have a few moments to review the book on Amazon, that's always highly appreciated...) More is coming soon, BTW, with Pandas Workout!

@@ReuvenLerner I’m glad you mentioned. Just got it via early access!

So happy to hear it; thanks so much!

I'm so glad to know it helped!

My pleasure!

I'll try to do some machine learning stuff when I can, but I've got a backlog of other ideas already...

@@ReuvenLerner Thank you so much

Delighted it helped!

@@ReuvenLerner Thanks Reuven, I have one more question, how to create a list of lists from pivot table? Is there a function like DataFrame.iterrows() for pivot table?

@@33samogo A pivot table is a data frame, so all of the methods you know and love from data frames will also work there. So yes, you can use iterrows, but remember that when you turn a data frame into a list of lists, you're moving data from NumPy's compact and efficient storage to regular Python objects, which are neither compact nor efficient.

@@ReuvenLerner Thank you! I struggle with data-frames for whole day, especially with multiindexing, I am trying to interpolate pivot table rows, that's why I need a list of row values to create a new interpolated one, but for sure there is a way to interpolate a whole pivot data-frame in to a tubular data data-frame also?!

Pandas documentation : "Please note that only method='linear' is supported for DataFrame/Series with a MultiIndex." I need to apply quadratic interpolation, so pivot table is useful for me just to reshape the data-frame values, also I am not sure if data-frame length can be automatically adjusted during interpolation operation according to number of output values (from 20-->~1000). So, I know how to retrieve a row values from pivot table, change a type of values, apply interpolation and save values in list variable, now I need to automate the process for all rows in the pivot/data-frame table and save all list variables as data frame using for loop or comprehension. What are the steps to perform this operation in a "clean" way or what functions should I consider? I started with Pandas a week ago just for fun but now I'm fell in love with it, thank you.

Glad it helped!

Glad you are enjoying them! most of the data is (should be!) available from files.lerner.co.il/ .

I'm delighted to hear you enjoyed them! More are (hopefully) coming soon.

My pleasure!

Glad you enjoyed!

Glad it helped!

Good question! When you group by multiple columns, you get a multi-index as the result. A pivot table takes one dimension of the multi-index and turns it into columns. So instead of a single column of results with a multi-index on the rows, you get a single column of results with a simple index on the rows *and* a simple index on the columns. Same information, presented differently, which can be easier to understand and process.

@@ReuvenLerner brilliant, thanks a lot!

Glad it helped!

The answer, of course, is "yes and no." You don't *need* categorical data to do a pivot table, just as you don't *need* categorical table to do a groupby. But think about what's happening in such cases: Every unique value in the column will be a separate row (or column) in the pivot table. If you have only a handful of integers or floats, then that's fine. But if you have several hundred, or several thousand, then you'll end up with a pivot table that doesn't serve anyone well. So as a general rule, we say that you should only create pivot tables with categorical data, even though you could well have numeric data that's so limited in the distinct values that it'll work just fine.

If you only want a few columns, then you have at least two choices: (1) filter the rows before you create the pivot table, or (2) filter the column after the pivot table is created with [[ ]] . I would recommend doing the first, since it reduces the amount of time and memory needed to create the pivot table in the first place.

Ok I watched to the end: it's by using the 'aggfunc' parameter

​@@joshux3210 Yup! Pretty amazing, right?

Maybe -- it depends on the context and what you're using it for. Feel free to e-mail me (reuven@lerner.co.il) to discuss this further.

If values are missing from that row-column combination, you'll get a NaN value.

@@ReuvenLerner Actually, in my case both rows and columns doesnot appear if those rows and columns combination doesnot have any value... tried usi g dropna parameter but no use

Data for most of my demos are from files for Pandas Workout, at files.lerner.co.il/pandas-workout-data.zip .

It's a data frame, so you can use the "rename" method to change the column names.

Yup, you can definitely do that! Instead of passing a string for the index or column, pass a list of strings. That'll create a pivot table with a multi-index on one or both axes. It's pretty straightforward - give it a shot!

It's in the data set for my book, Pandas Workout, at files.lerner.co.il/pandas-workout-data.zip

Yes, one exercise looks at pivot tables specifically, and a few ask you to create them based on different data sets.

Hmm, I'm not sure. The result of pivot_table is a new data frame, so you should be able to say pivot_df = df.pivot_table(...) and then retrieve whichever columns you want. I'm guessing there's a syntax bug somewhere.

@@ReuvenLerner my column names were numeric.. i pivoted Years column so new column names were 2019,2020,2021. I was selecting them like table['2022'] and was getting a Key error for 2022. I removed apostrophe and tried df22 = table[2022]. And that worked. I did not know that if the column name is in numeric ' ' is not used. Thanks for the reply Reuven

@@aakashniture Right, if your column names are integers, then you can't use quotes around their names. I'm glad that you were able to figure it out!

I'm not quite sure what you mean by this. Can you give me a short example?

@@ReuvenLerner like I'm having a pivot table in which some cells I'm between the rows are empty, so I want merge those cells with nearest value

Thanks so much! Delighted to have you here with me.