Pandas : Data Processing and Visulisation with Python (Python Exercise 25)

Data Processing and Visulisation with Python

In this exercise, we will be using the San Francisco city employee salary data from Kaggle! Just follow along and complete the tasks outlined in bold below.

Don’t worry if you can not download the data from Internet right now. I have prepared it for you in file Salaries.csv. Enjoy!

Import pandas as pd.

import pandas as pd

Read Salaries.csv as a dataframe called sal.

sal = pd.read_csv('Salaries.csv')

Check the head of the DataFrame.

sal.head()

Use the .info() method to find out how many entries there are.

sal.info()

What is the average BasePay ?

sal.BasePay.mean()

What is the highest amount of OvertimePay in the dataset ?

sal.OvertimePay.max()

What is the job title of JOSEPH DRISCOLL ?

Note:

Use all caps, otherwise you may get an answer that doesn’t match up (there is also a lowercase Joseph Driscoll).

sal.JobTitle[sal['EmployeeName']=='JOSEPH DRISCOLL']

How much does JOSEPH DRISCOLL make (including benefits)?

sal.TotalPayBenefits[sal['EmployeeName']=='JOSEPH DRISCOLL']

Who is the highest paid person (including benefits)?

sal.iloc[sal['TotalPayBenefits'].idxmax()]

Who is the lowest paid person (including benefits)? Do you notice something strange about how much he or she is paid?

sal[sal['TotalPayBenefits']==min(sal.TotalPayBenefits)]

What was the average (mean) BasePay of all employees per year? (2011-2014) ?

sal.groupby(['Year']).mean()['BasePay']

How many unique job titles are there?

sal['JobTitle'].nunique()

What are the top 5 most common jobs?

sal['JobTitle'].value_counts()[:5]

How many Job Titles were represented by only one person in 2013? (e.g. Job Titles with only one occurence in 2013?)

sal2013 = sal[sal['Year']==2013]
sum(sal2013['JobTitle'].value_counts() == 1)

How many people have the word Chief in their job title?

Hint:

You can use apply.

sal['JobTitle'].apply(lambda x : 'chief' in x.lower()).sum()

Bonus: Is there a correlation between length of the Job Title string and Salary?

Hint:

First, generate a new column title_len for length of the JobTitle.
Then, find out the correlation between title_len and TotalPayBenefits.

sal['title_len'] =sal['JobTitle'].apply(lambda x : len(x))
temp=sal[['title_len','TotalPayBenefits']]
temp.corr()

sal['title_len'] =sal['JobTitle'].apply(len)
sal[['title_len','TotalPayBenefits']].corr()