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