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. So the first one is the commission amounts by channel,
0:08
So let's go up and find that variable. The other thing we may want to do is what are the actual commission rates, and I used mean toe aggregate.
0:17
The commission rates are all the same, but that's one trick you can do when you have similar values that you want to
0:23
summarize. And then the next thing we want to capture, what are those new alignment? So how do we know what states everyone's covering now
0:38
We have our new alignments, and we can see for each sales person what states they cover how Maney accounts they
0:45
have using the size function and how much in sales they had in the previous period
0:51
that we've been modeling. And what would be nice now is to summarize all those in an excel file. So let's create our output.
1:11
So we've created our New Excel writer, and for each of the data frames, we are saving those in the report file,
1:19
and we're gonna keep this all in a single sheet. But we have different columns where we're going to store the data so that it's easy
1:27
to read. So let's open up that file and here's our report. So now we have each of the data frames out here so we can see how
1:37
much they've sold, what the commission rates are, and then how we recommend aligning each individual rap to the state.
1:46
And if you wanted to. Since we've used The Excel writer, you could also format thes, so it's a little easier to read.
1:53
But I'm not going to go through that. You can certainly do that on your own. But since we've used The Excel writer set this up,
2:01
we now have a really nice summary format of all the work we've done that we can share with people do further analysis,
2:08
and then if we need to reiterate,