#!/usr/bin/env python
# coding: utf-8

# # INTRODUCTION
# 
# This project will be examining data collected in 2014 from exit surveys of past employees of the Department of Education, Training and Employment (DETE) and the Technical and Further Education Institute (TAFE) in Queensland, Australia. These datasets are available [here for DETE](https://data.gov.au/dataset/ds-qld-fe96ff30-d157-4a81-851d-215f2a0fe26d/details?q=exit%20survey) and [here for TAFE](https://data.gov.au/dataset/ds-qld-89970a3b-182b-41ea-aea2-6f9f17b5907e/details?q=exit%20survey). However, for this project that there were some modifications to make it easier to work with as compared to the original. Ultimately, we will be playing the role of data analyst in order to answer the following questions:
# 
# 1) **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?**
# 
# 2) **Are YOUNGER employees resigning due to some kind of dissatisfaction? What about OLDER employees?**

# In[1]:


import numpy as np
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
get_ipython().run_line_magic('matplotlib', 'inline')


# In[2]:


dete_survey = pd.read_csv("dete_survey.csv")
tafe_survey = pd.read_csv("tafe_survey.csv") 


# In[3]:


# Info about DETE Survey
dete_survey.info()


# In[4]:


dete_survey.describe(include = 'all')


# In[5]:


dete_survey.columns


# In[6]:


dete_survey['DETE Start Date'].value_counts(dropna = False).sort_index(ascending = False).head(10)


# In[7]:


dete_survey['Cease Date'].value_counts(dropna = False).sort_index(ascending = False).head(10)


# In[8]:


dete_survey['SeparationType'].value_counts(dropna = False)


# Examining the DETE Survey data collected in **2014** of **822** former employees. This dataset consisted of several demographic variables (i.e Gender, Age) as well as other relevant background info as it pertains to their position at DETE (such as Employment Status). Additionally, there was also data pertaining to the decision to leave DETE that range across various subforms of grievances which appears to make up a large proportion of the number of variables. As it pertains to our original question, these variable may not be necessary in our analysis and will **likely need to be removed in the cleaning process**. 
# 
# Looking further into this dataset, we see that while some of the entries for certain columns have "not stated" replies, these values were not entered as "non-null values (i.e. *NaN*) as in the case for Dete Start Date, Cease Date, etc. Thus, these will need to be corrected for. 

# In[9]:


# Info about TAFE Survey
tafe_survey.info()


# In[10]:


tafe_survey.describe(include = 'all')


# In[11]:


tafe_survey.columns


# In[12]:


tafe_survey['CESSATION YEAR'].value_counts()


# In[13]:


tafe_survey['Reason for ceasing employment'].value_counts(dropna = False)


# In the case for the dataset containing the results of the TAFE survey, there were **701 entries** that appears to have been collected in **2014~ish**. As with the previous dataset, a large proportion of the data consisted of subcategories of the reasoning behind leaving the TAFE Institute as well as other demographic characteristics. However, unlike the previous dataset, there doesn't appear to be the same issue of not stating non-entries as "not stated". Lastly, this dataset appears to have a number of similar categories as the DETE survey dataset, albeit with a different namesake. We will need to somehow correct for this in our clean up process. 

# # STEP 2: IDENTIFYING MISSING VALUES AND DROPPING UNNECESSARY COLUMNS

# In[14]:


# Need to re-read the CSV file again for DETE Survey data 

dete_survey = pd.read_csv("dete_survey.csv", na_values = 'Not Stated')


# In[15]:


dete_survey.describe(include = 'all')


# In[16]:


# Need to drop unnecessary columns from dataframe for analysis

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)


# In[17]:


dete_survey_updated.columns


# In[18]:


tafe_survey_updated.columns


# From the above processes, there was a trimming of the original datasets to remove any unnecessary info for our analysis which happened to be a combination of non-greviance or personal reasoning as well as duplicate responses. Futhermore, there was also a correction to the aforementioned responses of "Not Stated" to *NaN*. 

# # STEP 3: CLEAN COLUMN NAMES

# In[19]:


# Modifying the DETE Survey Column Names
dete_survey_updated.columns


# In[20]:


dete_survey_updated.columns = dete_survey_updated.columns.str.lower().str.strip().str.replace(" ", "_")


# In[21]:


dete_survey_updated.columns


# In[22]:


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(mapping, axis = 1)


# In[23]:


tafe_survey_updated.columns


# In this process, we've prepped the columns of each dataset between the two datasets to have comparable names for concatation. 

# # STEP 4: FILTER THE DATA

# In[24]:


dete_survey_updated['separationtype'].value_counts()


# In[25]:


tafe_survey_updated['separationtype'].value_counts()


# In[26]:


dete_survey_updated['separationtype'] = dete_survey_updated['separationtype'].str.split("-").str.get(0)
dete_survey_updated['separationtype'].value_counts()


# In[27]:


dete_resignations = dete_survey_updated[dete_survey_updated["separationtype"] == "Resignation"].copy()
tafe_resignations = tafe_survey_updated[tafe_survey_updated["separationtype"] == "Resignation"].copy()


# In[28]:


print(dete_resignations['separationtype'].value_counts())
print(tafe_resignations['separationtype'].value_counts())


# From this filtering of only former employees who had actually put in a resignation (in other words, excluding those that retired or had left due to contractual or health-related issues), we've created a separate subset of the dataframe to work with to answer our initial question of whether **RESIGNATIONS** are related to age and/or duration of employment. 

# # STEP 5: VERIFYING THE DATA
# 
# In this step, we are looking to verify if the years within the "cease_date" as well as "dete_start_date" columns make sense. Considering that (a) the collection date for this data was in 2014, (b) most folks that started work were in their 20s and would have retired before reaching age 70, a start date before 1940 as being really unrealistic; we need to assess the make up of this dataset to avoid any inherent issues with its makeup. 

# In[29]:


# Checking DETE Start Dates
dete_resignations['dete_start_date'].describe()


# In[30]:


dete_resignations['dete_start_date'].value_counts().sort_index(ascending = False)


# In[31]:


ax = plt.figure(figsize = (8, 5))
sns.set_style('white')
ax2 = sns.boxplot(dete_resignations['dete_start_date'])
ax2.set_xlabel("Start Date (in Years)")
sns.despine(left = True)


# In[32]:


# Checking DETE Cease Dates
dete_resignations['cease_date'].value_counts()


# In[33]:


# Checking DETE cease date
dete_resignations['cease_date'] = dete_resignations['cease_date'].str.split("/").str.get(-1)
dete_resignations['cease_date'].value_counts()


# In[34]:


dete_resignations['cease_date']= dete_resignations['cease_date'].astype(float)


# In[35]:


dete_cease_dates = pd.DataFrame({"Years":['2006','2010','2012','2013','2014'] , "Nums":[1,2,129,129,22]})
ax3 = dete_cease_dates.plot.bar(x = "Years", y = "Nums", rot = 360, legend = False, figsize = (10,5))
ax3.set_xlabel("Year that ex-employee left")
ax3.set_ylabel("Number of ex-employees")


# In[36]:


# Checking TAFE Cease Dates
tafe_resignations['cease_date'].value_counts(dropna = False)


# In[37]:


tafe_resignations['cease_date'].describe()


# In[38]:


tafe_cease_dates = pd.DataFrame({"Years":['2009','2010','2011','2012','2013'] , "Nums":[2, 68, 116, 94, 68]})
ax4 = tafe_cease_dates.plot.bar(x = "Years", y = "Nums", rot = 360, legend = False, figsize = (10,5))
ax4.set_xlabel("Year that ex-employee left")
ax4.set_ylabel("Number of ex-employees")


# Examining the start date for DETE ex-employees, the start date ranged from 1963 to 2013 with the majority of the start date being 2010 & 2011. As for the cease date, the range is from 2006-2013 with the majority in 2012 & 2013. 
# 
# Examining the cease date for the TAFE ex-employees, the majority occured in 2011, but the range from 2009-2013. 
# 
# Although the range of the years don't quite line up, it doesn't seem to be any sort of cause for concern in regards to our data for our analysis. 

# # STEP 6: CREATE A NEW COLUMN

# In[39]:


tafe_resignations['institute_service'].value_counts()


# In[40]:


dete_resignations['institute_service'] = dete_resignations['cease_date'] - dete_resignations['dete_start_date']


# In[41]:


dete_resignations['institute_service'].head()


# In[42]:


dete_resignations['institute_service'].value_counts()


# In the TAFE dataset, the "institute service" column contains information pertaining to the length of employment which was gleamed from the cease date subtracted by start date (not available). While this variable was not made in the DETE survey dataset, we went ahead and processed this as it was necessary to delineate b/t short- and long-term ex-employees. 

# # STEP 7: IDENTIFYING DISSATISFIED EMPLOYEES

# In[43]:


tafe_resignations['Contributing Factors. Dissatisfaction'].value_counts(dropna = False)


# In[44]:


tafe_resignations['Contributing Factors. Job Dissatisfaction'].value_counts(dropna = False)


# Observing both cases, it appears that "-" is used to define "False". We need to modify the outcomes so that the values returned are "True", "False" or *NaN*. 

# In[45]:


def update_vals(value):
    if pd.isnull(value):
        return np.nan
    elif value == "-":
        return False
    else:
        return True 


# In[46]:


factors = ["Contributing Factors. Dissatisfaction", "Contributing Factors. Job Dissatisfaction"]
tafe_resignations['dissatisfied'] = tafe_resignations[factors].applymap(update_vals).any(axis = 1, skipna = False)


# In[47]:


tafe_resignations['dissatisfied'].value_counts(dropna = False)


# In[48]:


factorss = ["job_dissatisfaction", 
            "dissatisfaction_with_the_department",
            "physical_work_environment",
            "lack_of_recognition",
            "lack_of_job_security",
            "work_location",
            "employment_conditions",
            "work_life_balance", 
            "workload"]


# In[49]:


dete_resignations['dissatisfied'] = dete_resignations[factorss].any(axis = 1, skipna = False)


# In[50]:


dete_resignations['dissatisfied'].value_counts(dropna = False)


# In[51]:


dete_resignations_up = dete_resignations.copy()
tafe_resignations_up = tafe_resignations.copy()


# In the original TAFE and DETE dataset, there were various columns used to identify dissatisfaction. However, as we are only concern with dissatisfaction in general, there is a need to summarize it as a whole. Furthermore, in the case of TAFE, there isn't a uniform response to dissatisfaction as it was column specific. Therefore, we've created a new column that is attached to the dataframe that provides the following responses: True, False or *Nan*. 

# # STEP 8: COMBINE THE DATA

# In[52]:


dete_resignations_up['institute'] = "DETE"


# In[53]:


tafe_resignations_up['institute'] = "TAFE"


# In[54]:


combined = pd.concat([dete_resignations_up, tafe_resignations_up], axis = 0, ignore_index = True)


# In[55]:


combined.isnull().sum()


# In[56]:


combined_updated = combined.dropna(thresh = 500, axis = 1).copy()


# In[57]:


combined_updated.isnull().sum()


# In[58]:


combined_updated.describe(include = 'all')


# In this process, we've gone ahead and concatenate the two datasets into one through the "institute" column that delineates DETE and TAFE. Furthermore, while we still had some columns within both dataset that are not necessary for the analysis, we dropped them in cleaning the dataframe. Specifically we've dropped **columns** with less than 500 non-null values. 

# # STEP 9: CLEAN THE SERVICE COLUMN

# In[59]:


combined_updated['institute_service'].value_counts()


# In[60]:


combined_updated['institute_service'] = combined_updated['institute_service'].astype(str)


# In[61]:


pattern = r"([0-9]+)"
combined_updated["institute_service"] = combined_updated["institute_service"].str.extract(pattern, expand = True).astype(float)


# In[62]:


combined_updated["institute_service"].value_counts(dropna= False)


# In[63]:


def career_stage(val):
    if pd.isnull(val):
        return np.nan
    elif val < 3:
        return "New"
    elif val <= 6:
        return "Experienced"
    elif val <= 10:
        return "Established"
    else:
        return "Veteran"


# In[64]:


combined_updated['service_cat'] = combined_updated['institute_service'].apply(career_stage)


# In[65]:


combined_updated['service_cat'].value_counts(dropna = False)


# In this section, we've went ahead to clean up the "institute_service" column which denotes the duration of employment. However, as there were vaious degrees of entry that differed b/t the DETE and TAFE data set, we needed to be able to create a unifying model of selection which happened to be the following:
# 
# 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
# 
# To do so, we needed to first extract the entries that contain duration length adn fit it within this model and create a new column called "service_cat". 

# # STEP 10: INITIAL ANALYSIS

# In[66]:


combined_updated['service_cat'].value_counts(dropna = False)


# In[67]:


combined_updated['dissatisfied'].value_counts(dropna = False)


# In[68]:


# Fill in values with False

combined_updated['dissatisfied']= combined_updated['dissatisfied'].fillna(False)


# In[69]:


combined_updated['dissatisfied'].value_counts(dropna= False)


# In[70]:


pv_dissatisfiedxservice_cat = combined_updated.pivot_table(values = "dissatisfied", index = "service_cat")


# In[71]:


pv_dissatisfiedxservice_cat


# In[72]:


ax5 = pv_dissatisfiedxservice_cat.plot(kind = 'bar', legend = False, rot = 360)
ax5.set_xlabel("Dissatisfied Group")
ax5.set_ylabel("Percentage")


# Examining the data set, it was found that those that had formerly worked at either TAFE or DETE for a long period were generally found to have been dissatisfied as compared to more newer ex-employees. 

# # EXTRA: Looking at Age

# In[73]:


combined_updated['age'].value_counts(dropna = False)


# Based on these findings, we see that **55** ex-employees failed to include age into analysis whilst the rest are entries that are written in ranges of 5 yr intervals. 
# 
# As it is the goal to establish age groups, the best approach would be join the groups within the similar categories:
# 
#     "20 or younger" as **"< 20"**
#     "21-25" OR "21  25" as **"21-25"**
#     "26-30" OR "26  30" as **"26-30"**
#     "31-35" OR "31  35" as **"31-35"**
#     "36-40" OR "36  40" as **"36-40"**
#     "41-45" OR "41  45" as **"41-45" **
#     "46-50" OR "46  50" as **"46-50"**
#     "51-55" as **"51-55"**
#     "56-60" OR "56 or older" OR "61 or older" as **"> 56"**

# In[74]:


combined_updated['age'].astype(str)

pattern = r"([0-9]+)"

combined_updated['age_correct'] = combined_updated['age'].str.extract(pattern, expand = True).astype(float)

combined_updated['age_correct'].value_counts(dropna = False)


# In[75]:


mean_age = combined_updated['age_correct'].mean()
print(round(mean_age, 2))


# In[76]:


median_age = combined_updated['age_correct'].median()
print(round(median_age,2))


# In[77]:


mode_age = combined_updated['age_correct'].median()
print(round(mode_age,2))


# Based on these central limit findings, it appears that there is about a gaussian distribution where the average age is about 40 years old. As such, we will plan to fill out the missing value as 39.27.

# In[78]:


def age_groups(val):
    if pd.isnull(val):
        return np.nan
    elif val == 20:
        return "less than 20s"
    elif (val == 21) or (val == 26):
        return "20s"
    elif (val == 31) or (val == 36):
        return '30s'
    elif (val == 41) or (val == 46):
        return '40s'
    elif (val == 51) or (val == 56):
        return '50s'
    elif val == 61:
        return '60s or greater'


# In[79]:


combined_updated['age_cat'] = combined_updated['age_correct'].apply(age_groups)


# In[80]:


combined_updated['age_cat'].value_counts(dropna = False)


# In[81]:


combined_updated['age_cat'] = combined_updated['age_cat'].fillna("40s")


# In[82]:


pv_dissatisfied_age = combined_updated.pivot_table(values = "dissatisfied", index = "age_cat")
pv_dissatisfied_age


# In[83]:


ax6 = pv_dissatisfied_age.plot(kind = 'bar', legend = False, rot = 45)
ax6.set_xlabel("Age Groups")
ax6.set_ylabel("Percentage Dissatisfied")


# Upon examining the age groups of ex-employees from both DETE and TAFE, it was observed that there was no significant difference observed amongst "younger" ex-employees (i.e. 20s-30s) as it pertains to dissatisfaction. However this measure of dissatisfaction was noticeably increased amongst those in their 50s and 60s+. 

# # EXTRA: Looking at each individual Service Cat

# In[84]:


combined_updated.info()


# In[85]:


combined_updated['service_cat'].value_counts(dropna=False)


# In[86]:


# Create Bools 

New_only = combined_updated['service_cat'] == "New"
Experienced_only = combined_updated['service_cat'] == "Experienced"
Established_only = combined_updated['service_cat'] == 'Established'
Veteran_only = combined_updated['service_cat'] == 'Veteran'
Null_only = combined_updated['service_cat'].isnull()


# In[87]:


Combined_Only = New_only | Experienced_only | Veteran_only |  Established_only


# In[88]:


Known_Service = combined_updated[Combined_Only]
Unknown_Service = combined_updated[Null_only]
Experienced = combined_updated[Experienced_only]
Established = combined_updated[Established_only]
Veteran = combined_updated[Veteran_only]
New = combined_updated[New_only]


# In[89]:


Known_Service['dissatisfied'].value_counts(dropna = False)


# In[90]:


New['dissatisfied'].value_counts(dropna = False)


# In[91]:


Experienced['dissatisfied'].value_counts(dropna = False)


# In[92]:


Established['dissatisfied'].value_counts(dropna = False)


# In[93]:


Veteran['dissatisfied'].value_counts(dropna = False)


# In[94]:


Unknown_Service['dissatisfied'].value_counts(dropna = False)


# When examining each service category group, our findings matched our earlier analysis whereby those that had a longer tenure were related were more likely to have reported dissatisfaction as compared to ex-employees with a shorter tenure. 

# # EXTRA: TAFE vs DETE

# In[95]:


combined_updated['institute'].value_counts(dropna = False)


# In[96]:


TAFE_Only = combined_updated[combined_updated['institute'] == "TAFE"]
DETE_Only = combined_updated[combined_updated['institute'] == "DETE"]


# In[97]:


TAFE_Only['dissatisfied'].value_counts(dropna = False)


# In[98]:


DETE_Only['dissatisfied'].value_counts(dropna = False)


# In[99]:


pv_institutexdissatisfied = combined_updated.pivot_table(values = 'dissatisfied', index = 'institute')
pv_institutexdissatisfied


# In[100]:


ax7 = pv_institutexdissatisfied.plot(kind = 'bar', rot = 360, legend = False)
ax7.set_xlabel("Institute")
ax7.set_ylabel("Percentage Dissatisfied")
ax7.set_ylim(0, 1)


# Between institutes, it looks as if folks from the DETE who had left were more likely to have reported dissatisfaction as a contributing factor to leaving as compared to those that had worked at TAFE. 

# In[101]:


TAFE_agexdissatisfied = TAFE_Only.pivot_table(values = "dissatisfied", index = 'age_cat')
TAFE_agexdissatisfied 


# In[102]:


TAFE_agexdissatisfied.plot(kind = 'bar', rot = 45, legend = False) 


# In[103]:


TAFE_servicexdissatisfied = TAFE_Only.pivot_table(values = "dissatisfied", index = 'service_cat')
TAFE_servicexdissatisfied 


# In[104]:


TAFE_servicexdissatisfied.plot(kind = 'bar', rot = 45, legend = False)


# In examining TAFE data, we see that there was no difference as it pertains to age of ex-employees in terms of dissatisfication. However, amongst established ex-employees, they were more likely to have reported dissatisfaction. 

# In[105]:


DETE_agexdissatisfied = DETE_Only.pivot_table(values = "dissatisfied", index = 'age_cat')
DETE_agexdissatisfied 


# In[106]:


DETE_agexdissatisfied.plot(kind = 'bar', rot = 45, legend = False)


# In[107]:


DETE_servicexdissatisfied = DETE_Only.pivot_table(values = "dissatisfied", index = 'service_cat')
DETE_servicexdissatisfied 


# In[108]:


DETE_servicexdissatisfied.plot(kind = 'bar', rot = 360, legend = False) 


# Unlike the TAFE ex-employees, the DETE ex-employees did resemble a trend in greater reporting of dissatisfaction amongst older ex-employees as compared to younger ex-employees. Furthermore, this trend is also noted amongst Established and Veteran ex-employees as compared to New and Experienced ex-employees.