Move from Excel to Python with Pandas Transcripts
Chapter: Aggregating, grouping, and merging
Lecture: Pivot table and crosstab

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