Hello everyone else to eat pantless exercise.
SS salary's solutions lecture this lecture were over the solutions for the S.F. salary's pandas exercise
.
Let's call him took the Jupiter note book to get started.
All right here we are at the Jupiter notebook.
This is just a quick exercise for you to sharpen your panda skills and get used to working pandas and
real data sets.
In this case we're going to be using S.F. salary's data set from Kaggle.
And again you can click the link here and it will take you to the actual data source with some more
information and some other examples of data analysis.
So let's go ahead and get started with the salaries exercise.
Let me go ahead and import pandas as Edi and then let's go ahead and read salary's not see as we as
a data frame called.
So that's going to be PD read CSP and a nice trick here as we begin typing it.
Use tab autocomplete And let's go ahead and check the head of this data frame.
You just call it as a method looks like we have ID Employee Name job title based pay overtime pay go
ahead and just make this two rows and other pay benefits little pay little pay benefits which is basically
total pay plus benefits a year.
Then he notes agency and status.
Now let's go ahead and use the input method.
Find out how many entries there are so I don't go to say sell info or this info method returns the number
of columns and the number of entry rows.
Now I want to know what's the average base pay.
Let's go ahead and color data frame will grab the base pay column Minnies tab autocomplete her ease
of use here and then average.
In this case I just mean a mean.
So we can call the mean off of this and it returns that the median or mean excuse me based pay is sixty
six thousand 325.
The reason I used the word average here I'm sort of saying mine was just to make sure I didn't give
away the answer completely.
All right.
Now what is the highest amount of overtime pay in the data set.
We're going to go out and do is grab the overtime pay column and then just call Max off of it in the
short term the maximum amount of overtime pay which in this case was quite substantial sort of forty
five thousand one hundred thirty one.
OK.
Now what does the job title of Joseph Driscoll and the note use all caps here.
Otherwise you may get a different answer.
Going to say sell.
I'm going to look for job title.
And in this case it's actually just called job title as a single word.
So I say job title.
They notice here you have a bunch of job titles so I want to find out the row where this matches up
.
Someone named Joseph Driscoll so I'm going to go in and say this.
I will say Sal employee name equals equals and in strings all caps.
Joseph Driscoll and I will put that into his cell.
And here I have the row where Joseph Driscoll in all caps is here.
So his job title is captain of fire suppression.
And if I just wanted that column back I could Furthermore just call it job title off of this as we showed
earlier and then grab captain of fire suppression.
Now I want to know how much does Joseph Driscoll make.
And again you need to use all caps here.
Get the matching answer.
We're going to go in and say Sal.
Sal employee name equals equals.
Joseph Driscoll.
Now we have his role here.
And I want to know how much he makes including benefits.
That's total pay benefits that can actually just grab that were on top of the total pay benefits and
it looks like the answer is 270000 324.
Keep in mind if you accidentally forgot to put this in all caps and said Joseph Driscoll you would actually
get another answer.
So he's actually in here twice.
So keep that in mind in order to have your answers match up to the solutions.
Just use all caps.
Now we want to know what's the name of the highest paid person including benefits.
There's quite a few ways to do this.
But let's go ahead and do the most straightforward way.
So I'm going to get a little pay benefits and if I call the max value here note that I get the maximum
total pay benefits but I want to know the name of who's actually receiving this.
So I can say this I can say sell
of total pay benefits equals equals sough not Max.
I run this.
This returns boolean values.
And no I just have one true.
It's actually the first person in the data frame
that I can pass that into to Soule In order to actually grab his name which is the thing to afford and
if I just wanted the name I could say employee name off of this and get his name THANIA afford.
OK that's one way to do it.
The more advanced way to do this and we actually haven't gone over this method but just use cell Loek
where south of Little pay benefits the id x Max.
And this is essentially almost like an arc emacs method and this return some information as well and
it will return Nathanial for this and more advanced methods you can look up the X Max but it's very
similar to pis.
Argh Max.
OK we just take a look at what this is.
This is essentially just a number.
So a hash tag this out and paste this.
This is essentially just RMX and in effect Panas actually supports RMX.
So I could say our max here and it would give me the same answer.
And that's essentially where I want to find this person.
So I can say sal Dot either IOC or Alosi since it matches up and I still get enough data afford.
OK I'm going to review the solution but if you're interested in this method but hopefully you've figured
out either this one or more advanced method.
Let's go on and continue on.
Now we want to know what's the name of the lowest paid person including benefits and do you notice something
strange about how much he or she is paid.
So we want the lowest paid person.
Go ahead and do it.
The advanced method is going to say sell of total pay benefits are Dimin or I.T. X-Men.
Notice here we have a hundred forty eight thousand six hundred fifty three.
That is the ID location so I can pass this into salbe IOC and this returns the employees name Joe Lopez
.
Now to notice something strange here is that this person is actually receiving negative pay meaning
for some reason this person actually owes San Francisco money through some sort of weird situation but
hopefully are able to get Joe Lopez as the answer.
This is the more advanced method of doing it with RMN But you could have also done the following.
You could of said cell total pay benefits equals equals cell of little pay benefits not men and then
just pass this on to.
So this would have also returned Joe Lopez counseller the AU's Sioux City San Francisco money.
All right now is talk about what was the average in this case.
I want to give you the answer.
I mean based pay of all employees per year.
OK now we want to use a Group By.
So I get to say sell not group by year and I want the average in this case I mean if I just report this
I get everything averaged by year and they just want the base pay.
So going to call base pay off of this column of this data frame's Scuse me and there we go.
The average base payable employees per year are we can see that it slowly went up.
2011 2012 started going up and in 2014 it went down a little.
The next question I wanted to answer was how many unique job titles are there and thats actually quite
easy you just say sell job title and then remember there is quite a few functions or methods you can
use for grepping unique values.
The one we want is not unique because that actually returns an array of all the unique values that we
want and unique which returns a number of unique values.
You could have also then the length of this unique output and that would a return the same thing the
easier way to do this is not with length but just with and you.
Now we want to know what are the top five most common jobs.
Well you can do this by saying sell job title not value counts and then call the Top 5 of this and the
search terms the top 5 most common job titles.
Okay the next question I wanted to answer was how many job titles are represented by only one person
in 2013.
Otherwise it's just job titles of only one occurrence in 2013.
So there's probably conditions you want to do.
We want to figure out the job titles as value accounts figure out where they are only equal to one.
We also want to make sure we only do this for the 2013 year.
So it's going to break this down.
I want to grab Sal where the Year is equal to 2013.
Then I'm going to go ahead and grab the job title column off of this and perform volume counts on this
here have value counts very similar to what we just said above here but now is only for the year 2013
and what to do with this is ask where's is equal to one.
And we get some boolean values falses up here and True's down here where this was all equal to one.
And I want to figure out well what's the actual count of this occurrence.
I can just actually take some of this
in return.
Two hundred and two.
And that's the fastest and easiest way to know how many job titles are represented by only one person
in 2013.
OK.
Let's go ahead and move on to the next one which is also quite tricky which is how many people have
the word chief in their job title in order to do this we have to make a custom function.
Let's go in and check out what job title looks like as a column.
So you have job title notice everything is an upper case for the most part.
Let's go ahead and scroll down.
And now we see that there's lowercase.
So we're going to have to do is make sure you take this into account and we're going to do is create
a function in order to do this.
We're going to say Chief string takes in the title
and if Chief is in title The lower it splits
return TRUE else return false.
Let's go to break down what this function is actually doing.
I'm going to grab
just a random title here.
General Manager Metropolitan Transit Authority let me grab this thing and pass it into Chief string
.
What does it mean.
Copy and paste this one more time.
There we go.
And notice I get false which makes sense because chief is in that title.
Now let's go and change this to something like chief manager.
I run this now I get true.
And what's actually happening is it's taking this title just that string.
It's applying got lower on it which makes everything here in the string lowercase and then it goes ahead
and splits it into a list and then it checks if the string cheese is in that list it returns true or
calls.
And that means I can just go ahead and apply this
using lambda X and then I'm going to just say Chief string x and then they get these boolean values
false false false true true true.
And I have a couple of methods here of actually counting these up.
What we've seen before the best way to do this is just to take some of this in here we have 477 people
have the word chief in their job title.
OK.
Finally the last one which is actually a bonus question because it has a method we haven't gone over
yet which is correllation and we want to know is there a correlation between length of the job title
string and salary.
We're going to make a new column called tidal underscore Eliane and it's going to be equal to the job
title column and I'm going to apply the link built in method for this.
So now if I take a look at these two columns job title
and title link
here I have the length the title and the job title itself.
Now what I want to do is see if there's a correlation between these two and the method you discover
on your own which is why it a bonus question is C O R R and that returns the correlation between multiple
columns.
But here what we actually wanted to know is the correlation between length of job title string and salary
.
So this makes sense that is perfectly correlated.
But we want to do is idling and actually total pay benefits column.
And here we can see that there's essentially no correlation between how long your title is how much
you get paid.
OK.
Don't worry if you didn't get this last one it was quite tricky and hard but it had some methods that
we didn't go over which is why it was a bonus question.
All right.
Hopefully you know OK as you went through this remember the harder and harder the appeal to bad if you
were able to get the last couple ones.
Those are actually quite tricky but you can always review the solutions.
No but you want more practice with this or just go ahead and post Q&A forums if you have any questions
.
Thanks everyone and I'll see you at the next lecture