# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Guided Project: Clean and Analyze Employee Exit Surveys
# In this project, we will clean and analyze two datasets that consist of information from employee exit surveys from the Department of Education, Training and Employment (DETE) and the Technical and Further Education (TAFE) institute.
#
# The goal of this project is to answer two questions raised by stakeholders:
# - Are employees who only worked for the institutes for a short period of time resigning due to some dissatisfaction? What about employees who have been there longer?
# - Are younger employees resigning due to some kind of dissatisfaction? What about older employees?

# +
import pandas as pd
import numpy as np

dete_survey = pd.read_csv('dete_survey.csv')
tafe_survey = pd.read_csv('tafe_survey.csv')
# -

dete_survey.head()

dete_survey.info()

dete_survey['Career Aspirations'].value_counts()

dete_survey.isnull().sum().sort_values(ascending=False)

dete_survey['Torres Strait'].value_counts()

tafe_survey.head()

tafe_survey.info()

tafe_survey.isnull().sum().sort_values(ascending=False)

tafe_survey['Contributing Factors. Ill Health'].value_counts()

tafe_survey['Main Factor. Which of these was the main factor for leaving?'].value_counts()

# ### Observations
# The datasets seem to have made columns out of several Y/N questions (the unique values generally consist of Y, N, null, and -) which is inefficient for data analysis and makes it difficult to observe multiple columns at once.
#
# In the tafe_survey dataset:
# - Ironically, the column 'Main Factor. Which of these was the main factor for leaving?' has the most missing values (589) which makes us wonder about the structure and the questions asked in the survey since this column describing main reasons for leaving has few values.
#
# In the dete_survey dataset:
# - Several columns with large amounts of missing values (practically empty) were likely made out of Y/N questions with their unique values being either Y or null, causing many null values. Null values should probable be replaced with N to indicate they were answered and are not missing.

dete_survey = pd.read_csv('dete_survey.csv', na_values='Not Stated')
dete_survey['DETE Start Date'].value_counts().sum()

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

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

# In the last 3 cells, we:
# - Read in 'Not Stated' values in the DETE dataset as null values to better perform analysis and counts in the DETE Start Year column
# - Omitted Y/N question columns in the DETE and TAFE datasets and other columns that we won't be using in our analysis

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

tafe_survey_updated = tafe_survey_updated.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_survey_updated.head()

# In the previous two cells we renamed most columns in snake_case to make them easier to type and work with. We also standardized similar columns across both datasets to setup combining of rows/cols.

dete_survey_updated['separationtype'].value_counts()

tafe_survey_updated['separationtype'].value_counts()

mask = dete_survey_updated['separationtype'].isin(['Resignation-Other reasons', 'Resignation-Other employer', 'Resignation-Move overseas/interstate'])
dete_resignations = dete_survey_updated[mask]
dete_resignations

mask = tafe_survey_updated['separationtype'] == 'Resignation'
tafe_resignations = tafe_survey_updated[mask]
tafe_resignations

# In the previous four cells, we created new dataframes for both the DETE and TAFE datasets to hold just the surveys who resigned from their respective organizations in order to limit our scope and perform analysis on relevent items related to our goal:
# - 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?

dete_resignations['cease_date'] = (dete_resignations['cease_date']
                                   .copy()
                                   .str.split('/')
                                   .str[-1]
                                   .astype(float)
                                  )
dete_resignations['cease_date'].value_counts()

dete_resignations['dete_start_date'].value_counts()

tafe_resignations['cease_date'].value_counts()

# Given the DETE start and cease dates and the TAFE end dates, we don't see any major logistical inconsistencies in the year data. 
# - The earliest DETE start year is in 1963 which is within the 1940-current threshold 
# - The latest DETE cease date is later than the latest DETE start date

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

# Above we created a new institute_service column in the DETE dataset to show the years of service per employee by subtracting the given start year from the cease year. We were alredy given an institute_service column in the TAFE dataset. The reason for this column is to identify employees who resigned after a short period and those after a long period, to answer the question in our project goal:
# - 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?

tafe_resignations['Contributing Factors. Dissatisfaction'].value_counts()

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


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


tafe_tf = tafe_resignations[['Contributing Factors. Dissatisfaction', 'Contributing Factors. Job Dissatisfaction']].applymap(update_vals)
tafe_tf

tafe_resignations['dissatisfied'] = tafe_tf.any(axis=1, skipna=False)
tafe_resignations

tafe_resignations[tafe_resignations['Contributing Factors. Job Dissatisfaction'] == 'Job Dissatisfaction']

dete_resignations.columns

dete_tf = 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']]
dete_tf

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

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

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

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

# Above we created a new dissatisfied column in each dataset to indicate if employees (who've resigned) marked True for any dissatisfaction-related questions in the survey by creating truth tables for Y/N columns related to dissatisfaction.
#
# In the DETE dataset, columns indicating dissatisfaction are:
# - 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 the TAFE dataset, columns indicating dissatisfaction are:
# - Contributing Factors. Dissatisfaction
# - Contributing Factors. Job Dissatisfaction

dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'
combined = pd.concat([dete_resignations_up, tafe_resignations_up])
combined

combined.isnull().sum().sort_values(ascending=False)

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

# Above, we added an extra column to both datasets indicating the institution the survey was from to identify which dataset row items came from once the datasets are concatenated. We further dropped columns irrelevant to our analysis and columns missing many values.
# - Typically, after most cleaning/transforming/operating on particular columns, most if not all values in those columns are non-null and would be kept when removing columns on a null value basis.

combined_updated['institute_service'].value_counts()

combined_updated['institute_service'] = (combined_updated['institute_service']
                                         .astype(str)
                                         .str.extract(r'(\d+)')
                                         .astype(float)
                                        )
combined_updated['institute_service'].value_counts()


def to_def(x):
    if pd.isnull(x):
        return np.nan
    elif x < 3:
        return 'New'
    elif x < 6:
        return 'Experienced'
    elif x < 10:
        return 'Established'
    else:
        return 'Veteran'


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

# Above, we standardized the institute_service values after combining the two datasets into floats then categorized the values into range bins to categorize each employee. We stored the categories they fall under in the new column service_cat.

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

combined_updated['dissatisfied'].fillna(False, inplace=True)
combined_updated['dissatisfied'].value_counts(dropna=False)

combined_pt = combined_updated.pivot_table(index='service_cat', values='dissatisfied')
combined_pt

# +
import matplotlib.pyplot as plt
# %matplotlib inline

combined_pt.plot(kind='bar')
# -

# ### Observations
# Established, followed by Veteran, employees who've resigned from their companies have the highest percentages of dissatisfaction in their company.
#
# Percentage of those resigned whom felt a sort of dissatisfaction for each category of service years:
# - New: 30% 
# - Experienced: 32%
# - Established: 56%
# - Veteran: 47%
#
# There seems to be a slight positive correlation between years worked and dissatisfaction of those who resigned from their institutes as the percentage is generally lower the newer the employee is and higher the longer they have been working there. 
#
#
#
#
# This may support the narrative that generally, employees who have been at their institution longer will feel less appreciated and eventually become dissatisfied in at least one way as they get more situated into their occupation over time compared to a newer employee who've yet to unravel the experience of their institue in its entirety.
#
# Though not a majority, the percentage of those who've resigned from their company that have felt some form of dissatisfaction is fairly high and ranges from 30% to 56% across the four service-year categories.
#
# In the context of the question of our stakeholders-- 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?-- it seems that employees who have been with their company longer have a higher chance of feeling some sort of dissatisfaction than those who have been there shorter (in the context of those that have resigned from their compa

StarcoderdataJupyterScriptsDedupFiltered