Move from Excel to Python with Pandas Transcripts
Chapter: Aggregating, grouping, and merging
Lecture: Pivot table and crosstab
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Here's some examples of how to use pivot table on cross tab. We're using the same notebook we use for a group by example.
0:07
So we'll just show a couple examples of how to use a pivot table. The way the pivot table works is you defined the data frame that you want to
0:22
work on, and then the index here, which is a company and then the columns or products so you can see we've got all of our products listed across here.
0:30
The values are data that we actually want to do a mathematical function on. So here's the extended amount, and we tell it that we want to do the
0:39
some. So add them all up and then we use margins equals true toe. Add this all, Ah, as a column and a row, one of the other functions we have.
0:50
We can define a fill value here, which is useful. So instead of having the Entei ends here, it's now filled in with zero.
1:01
I'm going to copy and paste this cause I want to go through some other examples of how to use the pivot table.
1:06
So one of the things we can do is we can actually combine. So knows how each of these arguments is a list.
1:12
So if we wanted Teoh, what we could do is actually do multiple math functions here. So if we want to do this some in the mean and the max
1:26
we now get for each product we get the some of the books, the average extended amount for the book and so on for all the products.
1:36
So this just shows how you have a lot of flexibility with this function and how
1:42
you can use the different lists and the different aggregation functions that you can run to do. Ah, lot of complex analysis on your data very quickly.
1:52
So let's do another example, since it's pretty long thing to type. So one of the things we can do is we don't necessarily have to pass in
2:00
the columns, we put it here we get ah, similar sort of view. So we're just gonna do some. And now we can see for each product for each company,
2:14
the product in the some, and you may be thinking this is very similar to Group I and IT ISS. But we can use the fill value in the margins
2:23
equal true, to get a total. So the other shortcut function I want to talk about is the cross tab. So the function call is a little bit different here.
2:36
So you just defined the two different columns that you want to perform the function on So in this case, I want to look at company and product,
2:47
and what it's doing is it's counting how many occurrences there are for each of these
2:54
combinations. So how many occurrences of books for this company pins, posters, etcetera? And for this specific data set,
3:03
it's not terribly useful. One of the things we may want to do is actually some the values associated with each of these combinations,
3:13
so we could tell what values to use. And we need to tell what to do with those values using ak funk again.
3:24
So now we can tell what the total purchase amount was for each one of those and then the other useful argument is to pass normalize equals true,
3:34
and this gives you a view on what percentage of the total amount of purchases in this case or extended amount is allocated to each one of these cells.
3:46
So how many books and what percent of total is it for a bat's? We can also do columns. So then we can see that 1.7% for the books
4:00
went to company a bots and 0.3% for Abu. So that's how you could do it at the columns level. And if you want to look at the index level,
4:13
then we can see for a bots. Almost 60% of its persons were books, 36% were posters and the rest were pens.
4:23
So this is just another example of how pandas has functions that, as you start to master them and get exposure to how to use them,
4:31
you can easily iterated on your analysis and call different combinations of the functions to understand
4:37
your data better and drive insights that you can use in your business.