Clean and Analyze Employees Exit Surveys¶
Credit to David Chung, May 7th, 2020
In this guided project, we'll work with exit surveys from employees of the Department of Education, Training and Employment)(DETE) and the Technical and Further Education(TAFE) institute in Queensland, Australia. You can find the DETE exit survey here and the survey for the TAFE here
The encoding was changed from cp1252 to UTF-8 to make them easier to work with.
In this project, we'll play the role of data analyst and pretend our stackholders want to know the following questions:
- Are employees who only worked for the institutes for a short period of time resigning due to some kind of dissatisfaction? What about employees who have been there longer?
- Are younger employees resigning due to some kind of dissatisfaction? What anout older employees?
Let's start by reading the datasets into pandas and exploring them.
# import libraries as below to begin with
% matplotlib inline
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
# read csv file into pandas
dete_survey = pd.read_csv("dete_survey.csv")
tafe_survey = pd.read_csv("tafe_survey.csv")
Check State of DataFrame¶
# review information of dete_survey
dete_survey.info()
dete_survey.head(5)
dete_survey.isnull().sum().head(5)
In dete_survey, we take a look into ID, SeperationType, Cease Date and DETE Start Date and here's my observations:
- There're 822 data in total.
- We need to rename column names to uppercase for easy reading.
- We need to review seperation type and rename data with dictionary.
- Cease date is in mm/yyyy format and in string type.
- There're missing values in DETE Start Date and we'll follow workflow of data cleaning to decide what to do.
tafe_survey.info()
tafe_survey.head(5)
tafe_survey.isnull().sum()
In tefe_survey, we take a look into Recoded ID, Reason for ceasing employment and LengthofServiceOverall. Overall Length of Service at Institute (in years) and here's my observations:
- There're 702 data in total.
- Recorded ID has too many decimal points, maybe we can slightly correct for easy reading.
- We need to rename column names for better searching.
- Value in
LengthofServiceOverallis a string of year range, maybe we can replace them as mean number for future analysis.
Clean Column Names¶
# read the dete_survey file into pandas again
# but this time read the "Not Stated" values in as "NaN"
dete_survey = pd.read_csv("dete_survey.csv", na_values="Not Stated")
# drop columns which we won't use for analysis
# dete_survey
dete_survey_updated = dete_survey.drop(dete_survey.columns[28:49], axis=1)
# tafe_survey
tafe_survey_updated = tafe_survey.drop(tafe_survey.columns[17:66], axis=1)
# rename the remaining columns in lowercase,
# remove whitespace and replace space with underline
dete_survey_updated.columns = dete_survey_updated.columns.str.lower().str.strip().str.replace(" ","_")
# use dataframe.rename() to update column names
# for tefe_survey_updated
cols_dict = {"Record ID":"id", "CESSATION YEAR":"cease_date",
"Reason for ceasing employment": "separationtype",
"Gender. What is your Gender?": "gender","CurrentAge. Current Age": "age",
"Employment Type. Employment Type": "employment_status",
"Classification. Classification": "position",
"LengthofServiceOverall. Overall Length of Service at Institute (in years)": "institute_service",
"LengthofServiceCurrent. Length of Service at current workplace (in years)": "role_service"}
tafe_survey_updated = tafe_survey_updated.rename(columns = cols_dict)
# look at the current state of both dataframe
print(dete_survey_updated.head(5))
print("-------------------------")
print(tafe_survey_updated.head(5))
The main reason to change column names in both surveys is that we can make plots and directly compare with values with each other.
The column names of Tafe_survey_updated is much more different with Dete_survey_updated. So we need to build a dictionary to change name of Tafe while we only need to use function for Dete.
Filter the Data¶
# review the unique values in both dataframe
# dete_survey_updated
print(dete_survey_updated["separationtype"].value_counts())
print()
# tafe_survey_updated
print(tafe_survey_updated["separationtype"].value_counts())
In order to analyze the dissatisfaction status of employees working short or long, we'd like to select the data with "Resignation" only. Remember there're three kinds of resignation in dete_survey_updated.
# dete_survey_updated
# select rows based on multiple values
# df[df["column_name].isin(["value_1", "value_2"])]
resignation_list = ["Resignation-Other reasons","Resignation-Other employer", "Resignation-Move overseas/interstate"]
dete_resignations = dete_survey_updated[dete_survey_updated["separationtype"].isin(resignation_list)].copy()
# tafe_survey_updated
tafe_resignations = tafe_survey_updated[tafe_survey_updated["separationtype"]=="Resignation"].copy()
We use Series.value_counts() to review unique values. There's 311 employees exit for resignation in dete_survey, and 340 employees in tafe_survey.
Since we'd like to analyze further for employees exit for resignation, we have to select the data with "resignation" in separation column.
dete_survey has 3 kinds of resignation so we need to select rows based on multiple values. After searching, we decide to use df[df["column_name"].isin(["value_1","value_2"])] method.
For tafe_survey, since there's only one kind of resignation, we can easily use boolean indexing to select rows.
Verify the Data¶
# view the unique values in cease_date
# select only year part and convert the float type
dete_resignations["cease_date"].value_counts()
dete_resignations["cease_date"] = dete_resignations["cease_date"].str[-4:].astype(float)
# Use series.value_counts().sort_values()
# to check the values
dete_resignations["cease_date"].value_counts().sort_values(ascending=False)
# Use series.value_counts().sort_index()
# to check the values by index
s1 = dete_resignations["dete_start_date"].value_counts().sort_index(ascending=False)
print(s1.head(10))
tafe_resignations["cease_date"].value_counts().sort_index(ascending=False)
The year in cease_date column of dete_resignation is string type. Some are {yyyy} format and some are {mm/yyyy}, and we'd like to unite the format to be {yyyy}. First, we use series.str() to select the last four words. Second, we chain series.astype(float) to change type of value.
Then we review values in cease_date and dete_start_date whether it's beyond our acceptance. (start_date earlier than 1940 or cease_date later than current date.) Luckily, there's no values beyond boundaries so we can move on.
Create a New Column¶
# subtract the "dete_start_date" from "cease_date"
# assign the result to a new column "institute_service"
dete_resignations["institute_service"] = dete_resignations["cease_date"] - dete_resignations["dete_start_date"]
We have to keep in mind that our final goal is to answer:
- Are employees who have only worked for the institutes for a short period of time resigning due to some kind of dissatisfaction? What about employees who have been at the job longer?
So we need to calculate the service length by subtracting dete_start_date from cease_date, and assign the result to a new column institute_service.
Identify Dissatisfied Employees¶
# Convert the values to True, False or NaN
# # "Contributing Factors. Dissatisfaction" column
# tafe_resignations["Contributing Factors. Dissatisfaction"] = tafe_resignations["Contributing Factors. Dissatisfaction"].str.replace("-","False").str.replace("Contributing Factors. Dissatisfaction","True")
# # "Contributing Factors. Job Dissatisfaction" column
# tafe_resignations["Contributing Factors. Job Dissatisfaction"] = tafe_resignations["Contributing Factors. Job Dissatisfaction"].str.replace("-","False").str.replace("Job Dissatisfaction","True")
tafe_cols = ["Contributing Factors. Dissatisfaction",
"Contributing Factors. Job Dissatisfaction"]
dete_cols = ["job_dissatisfaction", "dissatisfaction_with_the_department",
"physical_work_environment","lack_of_recognition",
"lack_of_job_security","work_location",
"employment_conditions","work_life_balance","workload"]
# write a function which transform NaN to np.nan; "-" to False
# other situation to True
def update_vals(val):
if pd.isnull(val):
return np.nan
elif val == "-":
return False
else:
return True
# tafe_resignations
# use df.applymap(function).any(axis=1,skipna=False)
tafe_resignations["dissatisfied"] = tafe_resignations[tafe_cols].applymap(update_vals).any(axis=1, skipna=False)
tafe_resignations_up = tafe_resignations.copy()
# dete_resignations
dete_resignations["dissatisfied"] = dete_resignations[dete_cols].any(axis=1, skipna=False)
dete_resignations_up = dete_resignations.copy()
# TAFE
tafe_resignations_up["dissatisfied"].value_counts(dropna=False)
# DETE
dete_resignations_up["dissatisfied"].value_counts(dropna=False)
We write a function to transform value into the format we expected and sort the resignation reasons of dissatisfied into groups.
Combine the Data¶
# Add a column "institute" to dete_resignations_up
dete_resignations_up["institute"] = "DETE"
# Do the same to tafe_resignations_up
tafe_resignations_up["institute"] = "TAFE"
# Combine both dataframes, assign to combined
combined = pd.concat([dete_resignations_up ,tafe_resignations_up])
# use df.dropna(thresh=x) to remove any columns(axis=1)
# with less than 500 non-null values, x=500
combined = combined.dropna(axis=1, thresh=500)
# df.notnull().sum() to check if result is what we expected
combined.notnull().sum()
After many cleaning steps, we're finally ready to combine both datasets. Our goal is to aggregate the data according to institute_service(how many years working here) column.
We want to remove some columns with too many null values. Since there're 651 datas and we decide to keep columns with more than 500 not-null values to make further analysis.
Clean the Service Column¶
Classify the employees into four groups:
- Newbie(Less than 3 years)
- Sophomore(3-6 years)
- Tenured(7-10 years)
- Sage(11 or more years)
# convert value into string type, extract number only
# convert the result back to float and assign to new column
years = combined["institute_service"].astype("str").str.extract(r"(\d+)", expand=True)
combined["institute_service_up"] = years.astype(float)
# write a function to classify into groups
# remember to classify null value seperately
def classify_year(val):
if pd.isnull(val):
return np.nan
elif val < 3:
return "Newbie"
elif 3 <= val <7:
return "Sophomore"
elif 7 <= val <11:
return "Tenured"
else:
return "Sage"
# apply function and assign into new column
combined["service_cat"] = combined["institute_service_up"].apply(classify_year)
# check if the result is what we expect
# combined[combined["service_cat"] == "Tenured"]
# combined[combined["service_cat"] == "Sage"]
# combined[combined["service_cat"] == "Newbie"]
# combined[combined["service_cat"] == "Sophomore"]
If we look into institute_service column, we can find the format is inconsistent and hard to analyze. So we convert values into string dtype, extract only digits and convert back to float dtype. Finally, we assign new values into new column institute_service_up
We write a function in order to classify values of institute_service_up into four groups: Newbie, Sophomore, Tenured and Sage. Use series.apply() to activate the function and assign it into new column service_cat
Remember to check if the result is what we expected.
Perform Initial Analysis¶
# confirm the number of True & False in dissatisfied column
# use dropna=False to also confirm number of missing value
combined["dissatisfied"].value_counts(dropna=False)
# use DataFrame.fillna() to replace missing value
# with the value that occurs most frequently
combined["dissatisfied"] = combined["dissatisfied"].fillna(False)
# use DataFrame.pivot_table(df, index, column, value, aggfunc)
# default aggfunc is mean
# since True is considered as 1 and False as 0
# mean value in this situation can be considered as percentage
dissatisfied_by_year = pd.pivot_table(combined, index=["service_cat"], values=["dissatisfied"])
dissatisfied_by_year
dissatisfied_by_year.plot(kind="bar", legend=None, title="Dissatisfaction Percentage by Service Year")
plt.tick_params(right="off", top="off")
plt.show()
We replace missing values of column dissatisfied with most frequent values False. Then, use pivot_table() to generate a table. True is considered as 1 while False as 0, plus the defalut aggfunc of pivot_table is mean. We don't need to set aggfunc and we can get mean values which can be considered as percentage in this situation.
We can plot the table and make some brief conclusion:
- For employees worked for lower than 7 years, they are less likely to resign due to dissatisfied reasons(about 30%).
- For employees worked for over 7 years, they are more likely to resign due to dissatisfation(about 50%).
According to this brief conclusion, if we're manager of these companies, we should take opinions from employees who worked for many years seriously or we'll easily lose their loyalty.
Next Steps¶
How many people in each career stage resigned due to some kind of dissatisfaction?¶
# 算出各個階段因為不滿意現況而離職的人數
grouped = combined.groupby(["service_cat","dissatisfied"])["service_cat"].agg("count")
print(grouped)
The number exit for dissatisfied of Newbie: 57
The number exit for dissatisfied of Sophomore: 59
The number exit for dissatisfied of Tenurated: 32
The number exit for dissatisfied of Newbie: 66
Clean the age column. How many people in each age group resgined due to some kind of dissatisfaction?¶
# review the value status and figure out how to clean data
combined["age"]
combined["age_up"] = combined["age"].str.extract(r"(\d+)", expand=True).astype(float)
combined["age_up"].value_counts()
def classify_age(val):
if pd.isnull(val):
return np.nan
elif val < 30:
return "30 or less"
elif 30< val< 40:
return "30s"
elif 40< val< 50:
return "40s"
else:
return "50 or more"
combined["age_cat"] = combined["age_up"].apply(classify_age)
combined["age_cat"].value_counts(dropna=False)
# Use groupby to see how many people by age are dissatisfied
combined.groupby(["age_cat","dissatisfied"])["age_cat"].agg("count")
The number exit for dissatisfied of 30 or less: 49
The number exit for dissatisfied of 30s: 48
The number exit for dissatisfied of 40s: 66
The number exit for dissatisfied of 50 or more: 63
Instead of analyzing the survey results together, analyze each survey separately. Did more employees in the DETE survey or TAFE survey end their employment because they were dissatisfied in some way?¶
# recall there's a "institute" column stored
# where this data comes from
# see total amounts
combined.groupby(["institute","dissatisfied"])["institute"].agg("count")
# see percentage
pd.pivot_table(combined, index=["institute"], values=["dissatisfied"])
From both total amounts(149>91) and percentage(47%>26%), we can realize more employees from DETE resigned due to dissatisfaction reasons.