Cleaning and Analyzing Employee Exit Surveys

In this project, we'll be cleaning and analyzing exit surveys to learn how different factors affect employee resignations.

Approach

Specifically, we'll explore how the following affect the proportion of resignations due to dissatisfaction:

Length of service at the workplace.
Employment status at time of resignation.
Employee gender.

Results

The following groups are more likely to resign due to dissatisfaction:

Employees who spend longer at a company.
Permanent employees, in contrast to Temporary or Contract employees.
Male employees, though there is a marginal difference compared to Female employees.

For more details, refer to the full analysis below.

Data Overview

We'll be working 2014 exit surveys from the Department of Education, Training and Employment (DETE), and 2013 exit surveys from the Technical and Further Education (TAFE) Institute. Because a data dictionary wasn't provided, we'll use our general knowledge to define the columns.

dete_survey.csv has a total of 56 columns and 822 rows. Below is a preview of some of the more important columns:

ID: An id used to identify the participant of the survey
SeparationType: The reason why the person's employment ended
Cease Date: The year or month the person's employment ended
DETE Start Date: The year the person began employment with the DETE

tafe_survey.csv has a total of 72 columns and 702 rows. Below is a preview of some of the more important columns:

Record ID: An id used to identify the participant of the survey
Reason for ceasing employment: The reason why the person's employment ended
LengthofServiceOverall. Overall Length of Service at Institute (in years): The length of the person's employment in years

# import libraries import datetime as dt import pandas as pd import matplotlib.pyplot as plt import numpy as np import seaborn as sns

DETE overview

# read in the dete survey dete_df = pd.read_csv("dete_survey.csv") # print tabular information dete_df.info()

From the table above, we can make note of the following:

Business Unit, Aboriginal, Torres Strait, South Sea, Disability and NESB are missing at least ~85% of data.
Classification is missing roughly ~40% of data.
Other columns with missing data are missing between ~1%-10%.
Roughly ~40% of the columns are bool types, while most of the remainder are object types. ID is the only int.
Cease Date, DETE Start Date and Role Start Date are represented as object instead of date types.
There's many columns here that we won't need for our analysis.

# print summary statistics dete_df.describe(include="all")

While most of the summary statistic rows don't apply due to the absence of int types, we can make note of the following:

The most frequent DETE Start Date and Role Start Date values are Not Stated. This seems to be an alias for null.
Professional Development up to Health & Safety have 6 unique values: A, SA, N, D, SD, M.
- These seem to be aliases for Agree, Strongly Agree, Neutral, Disagree, Strongly Disagree. "M" might be some sort of alias for Not Applicable.
- The most frequent values in all these columns are A or Agree.
Age column seems to be broken into brackets, with 61 or older the most frequent value.
Aboriginal, Torres Strait, South Sea, Disability and NESB columns only have 1 unique value and are otherwise mostly null.
- The unique value is Yes.
- This suggests that this field was mostly optional for those doing the survey. Where the value is null, we can probably translate that to "No"

# print the first 5 rows dete_df.head()

TAFE overview

# read in the tafe survey tafe_df = pd.read_csv("tafe_survey.csv", encoding="cp1252") # print tabular information tafe_df.info()

From the table above, we can make note of the following:

Contributing Factors columns are missing 40% of data.
Many other columns are missing between ~10%-25% of data.
All of the columns, except for Record ID and CESSATION YEAR, are object types.
There's a number of columns like Gender, CurrentAge and EmploymentType which are shared with the DETE dataset.
There's many columns here that we won't need for our analysis.

# print summary statistics tafe_df.describe(include="all")

We can observe the following:

The most frequent Contributing Factors values are "-".
Institute Views and WorkUnitViews have 6 unique values:
- Agree, Neutral, Strongly Agree, Disagree, Strongly Disagree, Not Applicable.
- The most common value in these columns tend to be Agree
CurrentAge is also split into age brackets, with the most frequency bracket being 56 or older

# print the first 5 rows from this dataset tafe_df.head()

Data Cleaning

From the data overview, it's clear that this project will involve a lot of data cleaning. Let's read in the data again to properly represent the null values:

# read in the dete dataset, converting the "Not Started" values to null dete_df = pd.read_csv("dete_survey.csv", na_values="Not Stated")

Dropping columns

We'll need to be relatively merciless about dropping columns. We'll drop the following:

Columns that are inconsistent between the two datasets.
Columns that don't indicate an employee resigned because they were dissatisfied.

For DETE, we'll drop the object columns from Professional Development [28] to Health & Safety [48].

As a reminder, these were the columns that captured Strongly Agree-Strongly Disagree information.

dete_df = dete_df.drop(dete_df.columns[28:49], axis = 1)

For TAFE, we'll drop the object columns from Main Factor [17] to Workplace Topic [65].

As a reminder, these were the columns that captured Strongly Agree-Strongly Disagree and some Yes-No information.

tafe_df = tafe_df.drop(tafe_df.columns[17:66], axis = 1)

Cleaning column names

As you probably noticed, each dataset contains many of the same column information though the column names our different. Here are some of the columns we'd like to keep for our final analysis:

DETE Column Name	TAFE Column Name	Description
`ID`	`Record ID`	An id used to identify the participant of the survey
`SeperationType`	`Reason for ceasing employment`	The reason why the participant's employment ended
`Cease Date`	`CESSATION YEAR`	The year or month the participant's employment ended
`DETE Start Date`		The year the participant began employment with the DETE
`Employment Status`	`Employment Type. Employment Type`	The employment status of the employee
`Position`	`Classification. Classification`	The position of the employee
	`LengthofServiceOverall. Overall Length of Service at Institute (in years)`	The length of the person's employment (in years)
	`LengthofServiceCurrent. Length of Service at current workplace (in years)`	The length of the person's employment in the role (in years)
`Age`	`CurrentAge. Current Age`	The age of the participant
`Gender`	`Gender. What is your Gender?`	The gender of the participant

Standardize column names

# Update column names for DETE dete_df.columns = dete_df.columns.str.lower().str.strip().str.replace(" ", "_") dete_df.columns

# update column names for TAFE to match those of DETE tafe_df.columns = tafe_df.columns.str.replace('\s+', ' ', regex=True) tafe_df = tafe_df.rename({ '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' }, axis=1) tafe_df.columns

Filter rows

Recall, we're only interested in analysing employees that have resigned.

We'll use the separationtype column to filter out all rows without a seperation type of Resignation or similar.

# list the unique values in DETE separationtype column dete_df["separationtype"].value_counts()

# Retain rows with resignation listed as the separation type dete_df = dete_df[dete_df["separationtype"].str.contains(r"Resignation.+")]

# list the unique values in TAFE separationtype column tafe_df["separationtype"].value_counts()

# Retain rows with resignation listed as the separation type tafe_df = tafe_df[tafe_df["separationtype"].str.contains(r"Resignation", na=False)]

Converting date types

In our DETE data overview, we observed that cease_date, dete_start_date and role_start_date were represented as object instead of date types. In the TAFE data overview, cease_date was represented as a float.

Let's extract the year from these columns and convert them to date types.

# regex pattern to extract years from the DETE dataset pattern = r"([0-9]{4})" # first, extract the year values from these columns dete_df["cease_date"] = dete_df["cease_date"].str.extract(pattern) dete_df["dete_start_date"] = dete_df["dete_start_date"].astype(str).str.extract(pattern) dete_df["role_start_date"] = dete_df["role_start_date"].astype(str).str.extract(pattern) # convert these values to date types dete_df["cease_date"] = pd.to_datetime(dete_df["cease_date"]) dete_df["dete_start_date"] = pd.to_datetime(dete_df["dete_start_date"]) dete_df["role_start_date"] = pd.to_datetime(dete_df["role_start_date"]) # show the info about these columns dete_df[["cease_date", "dete_start_date", "role_start_date"]].info()

# regex pattern to extract years from the TAFE dataset pattern = r"([0-9]{4})" # extract the year values tafe_df["cease_date"] = tafe_df["cease_date"].astype(str).str.extract(pattern) # convert these values to dates tafe_df["cease_date"] = pd.to_datetime(tafe_df["cease_date"]) # show the info about these new column tafe_df[["cease_date"]].info()

Identifying dissatisfied employees

Now, we'll identify any employees who resigned because they were dissatisfied. Below are the columns we'll use to make this assessment:

TAFE

Contributing Factors. Dissatisfaction
Contributing Factors. Job Dissatisfaction

DETE

job_dissatisfaction
dissatisfaction_with_the_department
employment_conditions
work_life_balance
workload

If an employee indicated that any of the factors above caused them to resign, we'll mark them as dissatisfied in a new column.

First, let's update Contributing Factors. Dissatisfaction and Contributing Factors. Job Dissatisfaction in the TAFE dataset to True, False or NaN values.

We won't need to do this for the DETE dataset as they are already in this format.

# function for converting values to True, False or NaN def update_values(data): if pd.isnull(data): return np.nan elif data == "-": # recall that we're interpreting this as false return False return True # update the following columns to either be True, False or NaN tafe_df[['Contributing Factors. Dissatisfaction', 'Contributing Factors. Job Dissatisfaction']] = tafe_df[['Contributing Factors. Dissatisfaction', 'Contributing Factors. Job Dissatisfaction']].applymap(update_values)

# add dissatsified column for DETE dete_df["dissatisfied"] = dete_df[["job_dissatisfaction", "dissatisfaction_with_the_department", "employment_conditions", "work_life_balance", "workload"]].any(axis=1) # add dissatsified column for TAFE tafe_df["dissatisfied"] = tafe_df[['Contributing Factors. Dissatisfaction', 'Contributing Factors. Job Dissatisfaction']].any(axis=1)

Create an institute service column

The TAFE dataset already has an institute_service column which tells us the length of time an employee spent in a workplace.

We can compute a similar column for the DETE dataset by subtracting dete_start_date from cease_date as follows:

# create a new institute_service column dete_df["institute_service"] = pd.DatetimeIndex(dete_df["cease_date"]).year - pd.DatetimeIndex(dete_df["dete_start_date"]).year

Combining the data

With all of that cleaning done, we can finally combine the two seperate datasets into one

# create identifier columns for the datasets dete_df["institute"] = "DETE" tafe_df["institute"] = "TAFE"

combined_df = pd.concat([dete_df, tafe_df]) combined_df.info()

Combining these datasets also meant combining columns which had no overlap. This would lead to a lot of null values.

So we'll drop any columns with less than 500 non-null values. The table above highlights each columns non-null value.

combined_df = combined_df.dropna(thresh=500, axis=1) combined_df

Categorize institute service

The final step in our data cleaning is to normalize the institute_service column. Currently, it holds values that are either of the format 1.0 or 1-2.

We'll instead convert these years into the following categories:

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

# show what the different year values look like combined_df["institute_service"].value_counts(dropna=False).head(10)

# match numbers after the "-", of before the ".". This will grab "5" of "5.0", but "6" of "5-6" combined_df["institute_service"] = combined_df["institute_service"].astype(str).str.extract(r"(?<!\.)\b(\d+)\b(?!-)").astype(float)

# function that maps the number of years to a service category def map_institute_service(data): if pd.isnull(data): return np.nan elif data < 3: return "New" elif data < 7: return "Experienced" elif data < 11: return "Established" return "Veteran" # apply the mapped function to the institute_service column, putting the results in a new column service_category combined_df["service_category"] = combined_df["institute_service"].apply(map_institute_service)

Data Analysis

How does length of service affect resignation?

# create a bar chart showing proportion of dissatisfied employees in each service category ax = combined_df.pivot_table(index="service_category", values="dissatisfied").loc[["New", "Experienced", "Established", "Veteran"]].plot(kind="bar", figsize=(12,6), rot=0) # create x label, y label and title ax.set_xlabel("Service Category") ax.set_ylabel("Proportion of Employees") plt.title("Proportion of Dissatisfied Employees in Each Service Category") # show plot plt.show()

~43% of resigned Established employees were dissatisfied.
~30% of resigned Experienced employees were dissatisfied.
~28% of resigned New employees were dissatisfied.
~43% of resigned Veteran employees were dissatisfied.

The trend is that employees who spend longer at a company are more likely to resign due to dissatisfaction.

How does employment status affect resignation?

# create a bar chart showing proportion of dissatisfied employees by employment status ax = combined_df.pivot_table(index="employment_status", values="dissatisfied").loc[["Casual", "Contract/casual", "Temporary Part-time", "Permanent Part-time", "Temporary Full-time", "Permanent Full-time"]].plot(kind="bar", figsize=(12,6)) # create x label, y label and title ax.set_xlabel("Employment Status") ax.set_ylabel("Proportion of Employees") plt.title("Proportion of Dissatisfied Employees for Each Employment Status") # show plot plt.show()

From this, we can observe that:

A slightly higher proportion of Casual Employees resign due to dissatisfaction that Contract/Casual employees.
A significantly higher proportion of Permanent Part-Time Employees resign due to dissatisfaction that Temporary Part-Time employees.
A higher proportion of Permanent Full-Time Employees resign due to dissatisfaction that Temporary Full-Time employees.

This highlights that permanent employees are more likely to resign due to dissatisfaction.

How does gender affect resignation?

# create a bar chart showing proportion of dissatisfied employees by employment status ax = combined_df.pivot_table(index="gender", values="dissatisfied").loc[["Male", "Female"]].plot(kind="bar", figsize=(12,6)) # create x label, y label and title ax.set_xlabel("Gender") ax.set_ylabel("Proportion of Employees") plt.title("Proportion of Dissatisfied Employees for Each Gender") # show plot plt.show()

This highlights that within the Male category, a slightly higher proportion resign due to satisfaction.

Results

From our analysis, we've learned that the following groups are more likely to resign due to dissatisfaction:

Employees who spend longer at a company.
Permanent employees, in contrast to Temporary or Contract employees.
Male employees, though there is a marginal difference compared to Female employees.

Cleaning and Analyzing Employee Exit Surveys

Approach

Results

Data Overview

DETE overview

TAFE overview

Data Cleaning

Dropping columns

Cleaning column names

Standardize column names

Filter rows

Converting date types

Identifying dissatisfied employees

Create an institute service column

Combining the data

Categorize institute service

Data Analysis

How does length of service affect resignation?

How does employment status affect resignation?

How does gender affect resignation?

Results

Recommended on Deepnote

Stock Market Analysis

The 10 Best Ways to Create NumPy Arrays

Wide Residual Networks