Cleaning and Analyzing Employee Exit Surveys From Government Agencies¶
Executive Summary¶
In this project, we will clean and analyze data on employee exit surveys from the Department of Education, Training and Employment (DETE) and from the Technical and Further Education (TAFE) institutes in Australia. DETE is a department of the Queensland government in Austrialia, while TAFE institutes are government-owned providers of Vocational Education and Training (VET) courses.
The datasets can be found here for the TAFE exit survey and here for the DETE survey.
Our main goal in the project is to perform the required data cleaning and analysis in order to explore 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 about older employees?
We find that, on average, employees that report dissatisfaction as a reason for resigning tend to be those with the highest amount of years working at the company, that is, established and veteran employees.
Note: This project is part of Data Quest's Data Scientist in Python track.
Reading in the datasets and initial exploration¶
Let us begin by loading the required libraries and by conducting an initial exploration on the datasets.
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
dete_survey = pd.read_csv('dete_survey.csv', na_values = 'Not Stated')
tafe_survey = pd.read_csv('tafe_survey.csv')
## DETE Survey
print('DETE Survey Dataset')
dete_survey.info()
dete_survey.head()
## TAFE Survey
print('TAFE Survey Dataset')
tafe_survey.info()
tafe_survey.head()
We note that although both institutions used the same survey template, the TAFE Survey customized some of the answers. Each row in the datasets belongs to an employee with information on his/her separation type, cease dates, reasons for ceasing employment, and other details.
No data dictionary was provided with the datasets; however, below are some main columns and definitions we will use to perform our analysis in each survey.
DETE Survey
ID: An id used to identify the participant of the surveySeparationType: The reason why the person's employment endedCease Date: The year or month the person's employment endedDETE Start Date: The year the person began employment with the DETE
TAFE Survey
Record ID: An id used to identify the participant of the surveyReason for ceasing employment: The reason why the person's employment endedLengthofServiceOverall. Overall Length of Service at Institute (in years): The length of the person's employment (in years)
Data Cleaning¶
From our initial exploration, we observe that both datasets contain various columns which are not needed in order to perform our analysis. This is an important point as we could also observe that several columns in both datasets contained missing values.
Another observation is that both datasets contain equivalent columns but with different names, which will need to be corrected.
Finally, we note that there are various columns/answers that can help us identify employees who ceased their employment due to dissatisfaction.
Dropping unnecessary columns¶
We begin by removing unnecessary columns from the datasets for the purposes of our stated analytical goals.
From the DETE survey, we remove columns that enter into a lot of detail regarding the employee's workplace and worklife such as Staff morale and Worklife balance, given that we will be able to identify employee dissatisfaction more directly via columns such as Job dissatisfaction and Lack of recognition.
From the TAFE survey, we apply this same logic to remove unnecessary columns, keeping columns such as Contributing Factors. Career Move - Public Sector and removing more detailed columns such as InstituteViews. Topic:5. I felt the salary for the job was right for the responsibilities I had.
dete_survey_updated = dete_survey.drop(dete_survey.columns[28:49],
axis = 1)
tafe_survey_updated = tafe_survey.drop(tafe_survey.columns[17:66],
axis = 1)
Standardizing column names¶
Given that we want to eventually combine the datasets in order to compare them, we will proceed to standardizing column names.
We will begin by changing column names in the DETE Survey to lowercase, stripping whitespace and replacing spaces with underscores. In the TAFE Survey, we note that column names are more explicit and detailed, so we will use the following mapping to identify equivalent columns and change their names to the same name as in the DETE Survey.
dete_survey_updated.columns = dete_survey_updated.columns.str.lower().str.replace(' ', '_').str.strip()
tafe_mapping = {'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(tafe_mapping, axis = 1)
## DETE Survey
print('DETE Survey with updated columns:')
dete_survey_updated.head()
## TAFE Survey
print('TAFE Survey with updated columns:')
tafe_survey_updated.head()
Dropping unnecessary rows¶
As a following step, we will drop unnecessary rows from the datasets. We observe that since our analysis is focused on the reasons for resignation of employees, we will remove rows from the separationtype column that do not correspond to respondents who resigned. That is, we will drop rows that do not include the word 'Resign' in this column.
We begin by taking a look at this column to understand what types of separation are included in the datasets.
## DETE Survey
unique_dete = dete_survey_updated['separationtype'].unique()
## TAFE Survey
unique_tafe = tafe_survey_updated['separationtype'].unique()
print('Unique values in DETE Survey \n:', unique_dete)
print('\n Unique values in TAFE Survey \n:',unique_tafe)
We note that there are multiple separation types including the word 'Resigned' in the DETE Survey, such as 'Resignation-Other employer', while there is only one type of 'Resignation' in the TAFE Survey. Next, we filter the datasets to select only respondents whose employment ceased due to resignation.
dete_resigned = dete_survey_updated['separationtype'].str.contains('Resignation')
dete_resignations = dete_survey_updated.copy()
dete_resignations = dete_resignations[dete_resigned]
print('DETE Survey initial rows: ', dete_survey_updated.shape[0])
print('DETE Survey resignation rows: ', dete_resignations.shape[0])
tafe_resignations = tafe_survey_updated.copy()
tafe_resignations = tafe_resignations[tafe_resignations['separationtype'] == 'Resignation']
print('TAFE Survey initial rows: ', tafe_survey_updated.shape[0])
print('TAFE Survey resignation rows: ', tafe_resignations.shape[0])
We observe that after selecting this Separation Type, our datasets have been reduced from 822 employees to 311 in the DETE survey, and from 702 to 340 employees in the TAFE survey.
Checking for input errors¶
In this next step in our data cleaning process, we will look for errors in the dataset. We will start by checking that the dates in the cease_date and dete_start_date columns seem sensible. Note that the start date is only available in the DETE dataset, so we will only verify this for dete_resignations.
In particular, we can verify that no cease date comes before the start date, and that no start date is lower than around 1940, given that most people in this field start working in their 20s and a date before 1940 would imply the person resigned at about 100 years of age.
print('DETE Cease Date Frequency Table: ')
dete_resignations['cease_date'].value_counts()
From the above exploration of value counts in the DETE Survey's cease_date column, we note that these values need to be cleaned in order to be able to work with the dates. Specifically, we will extract the year from these dates using regular expressions. To make things easier, we note that all cease dates belong to the current milennium.
dete_resignations['cease_year'] = dete_resignations['cease_date'].str.extract(r'(2[0-9]{3})', expand = False)
dete_resignations['cease_year'] = dete_resignations['cease_year'].astype(float)
print('Unique cease years in DETE Survey:')
dete_resignations['cease_year'].value_counts().sort_index()
print('DETE Start Date Frequency Table: ')
dete_resignations['dete_start_date'].value_counts().sort_index()
print('TAFE Cease Date Frequency Table: ')
tafe_resignations['cease_date'].value_counts().sort_index()
dete_resignations.boxplot(column = ['cease_year', 'dete_start_date'])
plt.title('Cease and start dates in DETE Survey')
plt.show()
As we can observe in the plot above, the date ranges in the DETE Survey sound reasonable and therefore we will not remove any rows in relation to this topic.
Working with dates¶
Given that one of our analytical goals is to identify differences in resignation reasons between employees who worked at a short time and employees who worked at a long time at the institutions, we need a column that details the length of service of the employee. We note that this column already exists for the TAFE Survey (institute_service), but not for the DETE Survey. Hence, we will use the start and cease dates in the DETE Survey to create an equivalent column.
dete_resignations['institute_service'] = dete_resignations['cease_year'] - dete_resignations['dete_start_date']
dete_resignations['institute_service'].head()
Exploratory Analysis¶
Identifying dissatisfaction¶
As a next step, we will identify the employees who resigned due to dissatisfaction with their job and categorize them as 'dissatisfied' from each dataframe. We will identify an employee as 'dissatisfied' if he or she indicated that any of the following factores caused her/him to resign.
DETE Survey
job_dissatisfactiondissatisfaction_with_the_departmentphysical_work_environmentlack_of_recognitionlack_of_job_securitywork_locationemployment_conditionswork_life_balanceworkload
TAFE Survey
Contributing Factors. DissatisfactionContributing Factors. Job Dissatisfaction
We will begin by categorizing the employees in the TAFE Survey. We observe that these columns only take two values, with '-' indicating that the variable was not a factor in the employee's decision to resign. We will change these values to True, False or NaN values by writing the following update_vals function.
tafe_resignations['Contributing Factors. Dissatisfaction'].value_counts()
tafe_resignations['Contributing Factors. Job Dissatisfaction'].value_counts()
def update_vals(element):
if pd.isnull(element):
return np.nan
elif element == '-':
return False
else:
return True
tafe_factors = tafe_resignations[['Contributing Factors. Dissatisfaction', 'Contributing Factors. Job Dissatisfaction']].applymap(update_vals)
tafe_resignations['dissatisfied'] = tafe_factors.any(axis = 1, skipna = False)
Next, we will apply the same process to the DETE Survey. Note that this Survey already has True/False values for each of the factors, so we can skip the above manipulation.
dete_resignations['dissatisfied'] = dete_resignations[['job_dissatisfaction', 'dissatisfaction_with_the_department',
'physical_work_environment', 'lack_of_recognition',
'lack_of_job_security', 'work_location',
'employment_conditions', 'work_life_balance',
'workload']].any(axis = 1, skipna = False)
dete_resignations_up = dete_resignations.copy()
tafe_resignations_up = tafe_resignations.copy()
Combining both datasets for analysis¶
Next, we will combine the TAFE and DETE Surveys. For this purpose, we will add a column named institute to identify which institue the employee worked at.
dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'
combined = pd.concat([dete_resignations_up, tafe_resignations_up], ignore_index = True, sort = False)
combined_updated = combined.dropna(thresh = 500, axis = 1)
Below, we find the first rows of the combined dataset. We have kept the columns detailing employee ID, separation type, cease date, position, employment status, gender, age, years of service and institute.
combined_updated.head()
Analysis¶
Now that we have cleaned, combined dataset, we will look into the analytical goals that were set at the beginning of this project. Since we are interested in the effect that years of service has on resignation reasons, we begin by cleaning this column. We will convert its numbers into the following categories to be able to analyze the data:
- New: Less than 3 years at a company
- Experienced: 3-6 years at a company
- Established: 7-10 years at a company
- Veteran: 11 or more years at a company
combined_updated['institute_service'].value_counts()
## The below regex expression extracts the first value in the XX-XX pattern. We note that this is sufficient for
## analysis purposes given the categories that we've described for employees, as extracting the first number still
## results in employees being categorized in the correct bin.
years_service = combined_updated['institute_service'].astype(str).str.extract(r'([0-9][0-9]?)').astype(float)
def categorize(value):
if pd.isnull(value):
return np.nan
elif value < 3:
return 'New'
elif (value >= 3) & (value < 6):
return 'Experienced'
elif (value >= 6) & (value <= 10):
return 'Established'
else:
return 'Veteran'
service_cat = years_service[0].apply(categorize)
final = combined_updated.copy()
final['service_cat'] = service_cat
final.head()
We will now proceed to perform our analysis. It is important to note that there are NaN values in the Dissatisfied column and other variables, so this is only a preliminary analysis and further cleaning and exploration can be performed.
final['dissatisfied'].value_counts(dropna = False)
final.isnull().sum()
final['dissatisfied'] = final['dissatisfied'].fillna(False)
final.isnull().sum()
pivot_final = final.pivot_table(index = 'service_cat', values = 'dissatisfied' )
pivot_final.plot(kind = 'bar')
plt.title('Dissatisfaction by Employee Category Based on Years of Service')
plt.show()
As seen above, we see that employees tend to resign due to dissatisfaction primarily when they are Established and Veterans. That is, the employees who have been at the insitute for the longest time are those who tend to resign due to dissatisfaction. This is highly reasonable, seeing as New and Experienced employees (those with less than 11 years at the institute) are in the process of developing their careers at the insitutes and tend to have higher opportunities for mobility.
Conclusions¶
In this report, we performed several data cleaning tasks in order to explore the relation between employee dissatisfaction when resigning and years of service at two institutes. We found that, on average, employees that report dissatisfaction as a reason for resigning tend to be those with the highest amount of years working at the company, that is, established and veteran employees. Further analysis could be performed to explore differences across institutes (DETE and TAFE), a more granular analysis on reasons for dissatisfaction and employee position.