Move from Excel to Python with Pandas Transcripts
Chapter: Case study walk through: Sales commissions
Lecture: Final Excel report
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
There are few things who want to capture in our Excel report.
0:03
So the first one is the commission amounts by channel,
0:07
So let's go up and find that variable.
0:10
The other thing we may want to do is what are the actual commission rates,
0:14
and I used mean toe aggregate.
0:16
The commission rates are all the same,
0:18
but that's one trick you can do when you have similar values that you want to
0:22
summarize. And then the next thing we want to capture,
0:25
what are those new alignment? So how do we know what states everyone's covering now
0:37
We have our new alignments,
0:38
and we can see for each sales person what states they cover how Maney accounts they
0:44
have using the size function and how much in sales they had in the previous period
0:50
that we've been modeling. And what would be nice now is to summarize all those
0:53
in an excel file. So let's create our output.
1:10
So we've created our New Excel writer,
1:12
and for each of the data frames,
1:14
we are saving those in the report file,
1:18
and we're gonna keep this all in a single sheet.
1:21
But we have different columns where we're going to store the data so that it's easy
1:26
to read. So let's open up that file and here's our report.
1:32
So now we have each of the data frames out here so we can see how
1:36
much they've sold, what the commission rates are,
1:39
and then how we recommend aligning each individual rap to the state.
1:45
And if you wanted to. Since we've used The Excel writer,
1:48
you could also format thes, so it's a little easier to read.
1:52
But I'm not going to go through that.
1:53
You can certainly do that on your own.
1:56
But since we've used The Excel writer set this up,
2:00
we now have a really nice summary format of all the work we've done that we
2:04
can share with people do further analysis,
2:07
and then if we need to reiterate,