Move from Excel to Python with Pandas Transcripts
Chapter: Case study walk through: Sales commissions
Lecture: Commission rate calculations

Learn more about this course
Login or purchase this course to watch this video and the rest of the course contents.
0:00 Before I continue with the rest of the analysis,
0:02 I'm going to delete some of the boilerplate cells that were put in here just to
0:08 keep this clean. All right,
0:09 you can see that our data has been read in and let's do the quick check
0:14 to make sure everything looks good.
0:16 So the one thing that were seeing here from a data type perspective is that zip
0:21 code is coming in as an n 64.
0:23 Total sales is and then 64 as well,
0:26 which is OK, but zip code is not so We need to go back and
0:30 fix it when we read in our file.
0:32 So let's go ahead and specify the data type,
0:36 and now we look at it and zip code is an object.
0:39 So I specified string here. But behind the scenes,
0:42 pandas turns that into an object,
0:45 and then we can take a look at her data frame.
0:49 Just double check again, and zip codes look good.
0:53 And all of our data seems to be okay.
0:55 Let's do some additional quick checks of the data toe,
0:59 understand what we have here, so we can see how many entries we have in
1:04 total sales, we can see the average,
1:07 the standard deviation and what the max and men are.
1:11 One of the things I like to use with describe and highlight how you need to
1:16 understand what the various options are.
1:19 We can just run this on objects as well,
1:22 and this is a little bit easier to see that we have 50 different companies.
1:26 We have three unique channels. We have 50 unique zip codes,
1:32 states and account numbers. It was just ah,
1:36 quick way to start to get a feel for what the data looks like.
1:44 And here we've done the math to figure out OK,
1:47 how much did we sell last year?
1:49 And this is a little bit hard to read,
1:52 So maybe we want to use our F strings to clean this up using F strings
1:58 I told it, format it as a float with commas and no decimal points
2:02 so this makes it easier to see that we have $126 million in sales.
2:07 Now what if we want to figure out what the effective commission rate would be?
2:12 So we know our commission target $1 million.
2:16 It knows how used the underscores here in more recent versions of Python,
2:21 This is inappropriate syntax. And instead of using commas,
2:24 it's a lot easier to view your data and understand that that's a million dollars.
2:34 And I did a little formula here to see what is our effective rate.
2:37 So if we know that we have a million dollars and commissions and are yet last
2:42 year sales were $126 million. Therefore,
2:45 the effective rate would be 1260.79% times last year sales to get us to a million
2:51 dollars in commissions. Let's build upper model to see what the actual commission would be
2:57 on each of these customers. So at a commission column now,
3:05 what this tells us is this is the individual commission that would be calculated for the
3:10 sales for this company. So if we wanted to maybe clean this up a little
3:15 bit because we've got the commission's out the meaning decimal points,
3:19 we can round it. It's a little cleaner.
3:27 Let's just check our math here So the total commissions is a little bit over a
3:30 million dollars, and that's just a function of floating point math and rounding than the
3:36 average commission her customers $20,000 if we want to do maybe some more detailed.
3:45 This tells us what the meaning is with standard deviation is the minimum the max and
3:51 the different percentiles. Really nice summary of the commission data.
3:54 So let's see if we take this back to our CFO and see what they think