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.

Talk Python's Mastodon Michael Kennedy's Mastodon