#
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