Move from Excel to Python with Pandas Transcripts
Chapter: Case study walk through: Sales commissions
Lecture: Final Excel report

Learn more about this course
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,