# import libraries
import datetime as dt
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import seaborn as sns
# read in the dete survey
dete_df = pd.read_csv("dete_survey.csv")
# print tabular information
dete_df.info()
# print summary statistics
dete_df.describe(include="all")
# print the first 5 rows
dete_df.head()
# read in the tafe survey
tafe_df = pd.read_csv("tafe_survey.csv", encoding="cp1252")
# print tabular information
tafe_df.info()
# print summary statistics
tafe_df.describe(include="all")
# print the first 5 rows from this dataset
tafe_df.head()
# read in the dete dataset, converting the "Not Started" values to null
dete_df = pd.read_csv("dete_survey.csv", na_values="Not Stated")
dete_df = dete_df.drop(dete_df.columns[28:49], axis = 1)
tafe_df = tafe_df.drop(tafe_df.columns[17:66], axis = 1)
# 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
# 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)]
# 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()
# 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 a new institute_service column
dete_df["institute_service"] = pd.DatetimeIndex(dete_df["cease_date"]).year - pd.DatetimeIndex(dete_df["dete_start_date"]).year
# 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()
combined_df = combined_df.dropna(thresh=500, axis=1)
combined_df
# 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)
# 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()
# 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()
# 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()
