kag_sal_ecom

Question 1

For DataFrame ‘sal’, find out how many entries there are

Answer 1

sal.info()

Question 2

What is the average BasePay?

Answer 2

sal[‘BasePay’].mean()

Question 3

What is the highest amount of OvertimePay in the dataset?

Answer 3

sal[‘OvertimePay’].max()

Question 4

Return the entire record for the highest amount of OvertimePay in the dataset

Answer 4

sal[sal[‘OvertimePay’]==sal[‘OvetimePay’].max()]

Question 5

Return just the name of the employee with the highest amount of OvertimPay in the dataset

Answer 5

sal[sal[‘OvertimePay’]==sal[‘OvertimePay’].max()][‘EmployeeName’]

Question 6

Return the JobTitle of employee JOSEPH DRISCOLL

Answer 6

sal[sal[‘EmployeeName’]==’JOESEPH DRISCOLL’][‘JobTitle’]

Question 7

Return the TotalPayBenefits for employee JOSEPH DRISCOLL

Answer 7

sal[sal[‘EmployeeName’]==’JOSEPH DRISCOLL’][‘TotalPayBenefits’]

Question 8

Return the record of the person with the lowest TotalPayBenefits

Answer 8

sal[sal[‘TotalPayBenefits’]==sal[‘TotalPayBenefits’].min()]

Question 9

Return just the name of the employee with the lowest TotalPayBenefits

Answer 9

sal[sal[‘TotalPayBenefits’]==sal[‘TotalPayBenefits’].min()][‘EmployeeName’]

Question 10

What is the average (mean) BasePay of all employees per year?

Answer 10

sal.groupby(‘Year’).mean()[‘BasePay’]

Question 11

How many unique job titles are there?

Answer 11

sal[‘JobTitle’].nunique()

Question 12

What are the top 5 most common job titles?

Answer 12

sal[‘JobTitle’].value_counts().head(5)

Question 13

How many job title were represented by only 1 person in 2013?

Answer 13

sal[sal[‘Year’]==2013][‘JobTitle’].value_counts() == 1).sum()

Question 14

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

Answer 14

def chief_string(title):
    if 'chief' in title.lower():
        return True
    else:
        return False

(sal[‘JobTitle’].apply(lambda x: chief_string(x))).sum()

Question 15

How do you determine if there is correlation between length of Job Title string and TotalPayBenefits?

Answer 15

sal[‘title_len’] = sal[‘JobTitle’].apply(len)

sal[[‘title_len’, ‘TotalPayBenefits’]].corr()

Question 16

What is the average Purchase Price (ecom)

Answer 16

ecom[‘Purchase Price’].mean()

Question 17

What are the highest and lowest Purchase Prices?

Answer 17

ecom[‘Purchase Price’].max()

ecom[‘Purchase Price’].min()

Question 18

How many people have ‘en’ as their Language of choice on the website?

Answer 18

ecom[ecom[‘Language’]==’en’].count()

Question 19

How many people have the Job ‘Lawyer’?

Answer 19

ecom[ecom[‘Job’]==’Lawyer’].count()

Question 20

How many people made the puchase during the AM and how many made the purchase during the PM? (‘AM or PM’)

Answer 20

ecom[ecom[‘AM or PM’].value_counts()

Question 21

What are the 5 most common Job titles?

Answer 21

ecom[‘Job’].value_counts().head(5)

Question 22

Someone made a purchase that came from Lot “90 WT”. What was the Purchase Price for this transaction?

Answer 22

ecom[ecom[‘Lot’]==’90 WT’][‘Purchase Price’]

Question 23

What is the email address of the person with the following CC Number? 4926535242672853

Answer 23

ecom[ecom[‘CC Number’]==4926535242672853][‘Email’]

Question 24

How many people have American Express as their CC Provider and made a purchase above $95?

Answer 24

ecom[(ecom[‘CC Provider’]==’American Express’) & (ecom[‘Purchase Price’]>95)].count()

Question 25

How many people have a CC Exp Date of 2025?

Answer 25

sum(ecom[‘CC Exp Date’].apply(lambda x: x[3:]) == ‘25’)

Question 26

What are the top 5 most popular email providers/hosts?

Answer 26

ecom[‘Email’].apply(lambda x: x.split(‘@’)[1]).value_counts().head(5)