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