Pivot tables with Pandas

What are pivot tables? How do they work? And how can you create them using Pandas? In this video, I explain why they're such useful tools in data analytics, and how we can easily create them with Pandas.
As usual, the Jupyter notebooks for my videos are on GitHub: github.com/reu...
Don't forget to subscribe to my free, weekly "Better developers" newsletter, at BetterDevelope...!

[@Maleficent_Method973]

Just started learning python a few days ago, it's my first programming language as well.....very confusing so far...You have helped a lot even with a pretty short video. Thanks so much!

[@ReuvenLerner]

I'm glad that the video helped -- keep at it, I promise that it'll get easier over time.

[@reygaji4001]

Omg, you are a godsend. Where have you been all this time when I was just starting to learn this stuff T_T Your videos are better than many python tutorials out there with millions of views damn

[@ReuvenLerner]

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

[@fdu6]

This was exactly what I needed! Just wanted to learn what pivot tables would look like in Pandas because I'm still kinda struggling to grasp it, thanks!

[@ReuvenLerner]

Fantastic, so glad that it helped!

[@YossiSoberano]

Thanks Reuven, for another informative and educational video 👍

[@Kaelthas_Sunstrider]

You’re an amazing teacher! I’m enjoying your book Python Workout :)

[@ReuvenLerner]

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!

[@Kaelthas_Sunstrider]

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

[@thomasgyting3251]

I was having a difficult time understanding pivot tables and what exactly they are vs regular tables, and your example at the end that included multiple agg functions made the lightbulb go off! Love your content!

[@ReuvenLerner]

So happy to hear it; thanks so much!

[@33samogo]

Great explained! .pivot() is amazing, it's exactly what I was looking for, it's like y axes in "xy coordinate table", fantastic!

[@ReuvenLerner]

Delighted it helped!

[@33samogo]

@@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?

[@ReuvenLerner]

@@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.

[@33samogo]

@@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?!

[@33samogo]

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.

[@mosama22]

Thank you so much for the beautiful videos, I just wish you had a direct link to the data please, so we can follow up with you. Thanks again though for the beautiful videos 🙂

[@ReuvenLerner]

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

[@AndrewT]

You can also group by two or more features right? So I guess pivot table does something similar but puts one of the features on the cols and one on the rows so you can read it like a two-way table

[@ReuvenLerner]

Yes! Doing a groupby on two columns performs precisely the same operation as a pivot table. It's just the formatting/display of the information that's different. This is on my list of videos to do; you might just have made it a bit earlier. :-)

[@joseluisbeltramone599]

Very cool explanation. Thanks a lot!

[@ReuvenLerner]

Glad you enjoyed it!

[@angelhanna2917]

Easy to understand, thanks!

[@ReuvenLerner]

Glad it helped!

[@KonradTamas]

One question, do I always need Categorical data to use pivot tables ? Thanks!

[@ReuvenLerner]

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.

[@mcllen]

There was a great class, Thanks!

[@ReuvenLerner]

Glad it helped!

[@mario1ua]

Hey Reuven, great tutorial, thanks! I've also watched your pivot and **kwargs videos and they were awesome too

[@ReuvenLerner]

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

[@Amber-rh8dh]

Please share the datasource too, if possible...

[@ReuvenLerner]

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

[@Amber-rh8dh]

@@ReuvenLerner Thanks!!! Btw I needed some practice questions... Could you recommend any source for that ... :)

[@ReuvenLerner]

@@Amber-rh8dh Of course! Check out Bamboo Weekly (BambooWeekly.com) for new Pandas problems/questions every week, based on public data sets and current events. And Pandas Workout (PandasWorkout.com/) for 200+ exercises in Pandas. If you liked this video, you'll love both of those!

[@Amber-rh8dh]

​@@ReuvenLerner TYSM 😭

[@Darkev77]

Great, thanks for explaining it so concisely. However, why would I use a pivot table when I can also group by multiple columns (group by year and team: `groupby(["Year", "Team"], observed=True)`)?

[@ReuvenLerner]

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.

[@Darkev77]

@@ReuvenLerner brilliant, thanks a lot!

[@jingchun21]

Thank you so much! I am curious that can we just show a few columns we want? For example, for the sports, I just want to show baseball, badminton. Thanks.

[@ReuvenLerner]

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.

[@joshux3210]

In the pivot_table method, you don't specify you want to calculate the mean. It auto assumes that? Can I use other calculation such as median or max?

[@joshux3210]

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

[@ReuvenLerner]

​@@joshux3210 Yup! Pretty amazing, right?

[@faoaccount]

thanks Reuven

[@ReuvenLerner]

My pleasure!

[@real.samad_]

Hello Reuven, can I use smally portion of this video for an instagram reel I am working on?

[@ReuvenLerner]

Maybe -- it depends on the context and what you're using it for. Feel free to e-mail me ([e-mail]) to discuss this further.

[@AnjanBasumatary]

How to display the all the rows and column cobination even values are missing in those combination?

[@ReuvenLerner]

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

[@AnjanBasumatary]

@@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

[@lenkapang-ek4fe]

hi," from pandas import Series, DataFrame" is it essential?

[@ReuvenLerner]

No, not essential -- I just do it all of the time, because I find it's convenient.

[@rafaelsantana5808]

Hello, I would like to know how I can make it count in a column at the end all columns that have values. example: count = ID1 + ID2+ ID3

[@aakashniture]

Hi Reuven, amazing video. After pivoting a column when you get that column's distinct values as new columns. I want to select those columns or save them in a new df but i can't. I am simply just using names of new columns e.g., df['Afghanistan'] but it gives error even though in df.shape() it says Afghanistan as a column. Kindly help.

[@ReuvenLerner]

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.

[@aakashniture]

@@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

[@ReuvenLerner]

@@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!

[@kaiserkonok]

Thank you so much🖤. You are doing amazing for us. We would be more happy if you post videos on machine learning with python. 🙏

[@ReuvenLerner]

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

[@kaiserkonok]

@@ReuvenLerner Thank you so much

[@proud_indian0161]

From where can i get this athlete dataset?

[@ReuvenLerner]

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

[@naimahrehman5997]

I believe that we can also achieve the outcome of the pivot table through groupby function

[@ReuvenLerner]

You will indeed get a similar result by using groupby and specifying that you want to use two columns. However, the result will be a series with a multi-index, rather than a data frame with rows vs. columns. You can switch between such a series and a pivot table with stack and unstack.

[@pramishprakash]

very helpful sir

[@ReuvenLerner]

Glad it helped!

[@inderjeetchandnani302]

At time 3:59 if we have a date data, will it work?

[@twocentswithdeb]

How can I rename the aggregated columns?

[@ReuvenLerner]

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

[@helloworld2740]

great video sir

[@chandancs3215]

why is thresh used for in dropna?

[@ReuvenLerner]

Without any arguments, dropna removes any row with any NaN value. If your data frame has 100 columns, then even a single NaN value will remove a row. Which can sometimes be good, but it often leads to overzealous cleaning. After all, not *all* of your columns are normally equally important. By specifying "thresh", you can say how many good values need to be in a row to keep it. That lets you set the threshold to be lower.

[@marvinlomo5845]

Thanks Reuven. Is this covered in the Book?

[@ReuvenLerner]

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

[@mohemmedkamal]

thank you

[@ravindrareddy5478]

is there any way to merge empty cells using python in a pivot table

[@ReuvenLerner]

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

[@ravindrareddy5478]

@@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

[@hieuthuan1999]

Thanks @Reuven. I wonder if it is possible to create pivot table with more than one index (row field), and more than 1 value, such as height and weight in average. Excel can do it easily :)

[@ReuvenLerner]

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!

[@hakangrss]

someone have might asked, we can do the same thing by creating ''' df.groupby( [ 'year' , 'team' ] ).agg({ 'height' : [ 'mean' , 'count' ]} ) ''' can't we. if so what makes the distinguish between them? Thank you so much ☺ ...

[@CT-7125]

Thanks

[@ReuvenLerner]

Glad it helped!

[@bhargavkongara2003]

Thank u Sir

[@ReuvenLerner]

Glad it helped!

[@rajeshn5006]

excellent

[@ReuvenLerner]

Glad you enjoyed!

[@guillegonzalez6146]

blud i love ya

[@ReuvenLerner]

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

[@AlexKashie]

Thank you

[@ReuvenLerner]

My pleasure!