Move from Excel to Python with Pandas Transcripts
Chapter: Case study walk through: Sales commissions
Lecture: Commission rate calculations
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