<jupyter_start><jupyter_text>Analyzing the DETE/TAFE Employees ResignsIn this project we'll work with exit surveys from employees of the *Department of Education, Training and Employment* **(DETE)** and the *Technical and Further Education* **(TAFE)** institute in Queensland, Australia.Our goal is to answer what our stakeholders want to know:- 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?The data dictionary for both data sets is following above:**DETE**- `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- `Age`: The age group the person is within.**TAFE**- `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)- `CurrentAge. Current Age`: The age group the person is within.Let's take a look to these datasets and write down some observations.<jupyter_code>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.tail()
dete_survey.info()
dete_survey.isnull().sum()
dete_survey['SeparationType'].value_counts(dropna=False)
dete_survey['Cease Date'].value_counts(dropna=False)
dete_survey['DETE Start Date'].value_counts(dropna=False)
dete_survey['Age'].value_counts(dropna=False)<jupyter_output><empty_output><jupyter_text>The DETE data has information about 822 employees (nº of rows) in which there are 56 columns, although we are only interested in the five we described before on the data dictionary. Despite only the `Age` column has missing values (11) the `Cease Date` and `DETE Start Date` columns have "Not Stated" values. About these last two, `Cease Date` has month/year and year date format, while `DETE Start Date` has only 4-digit year date format. Regarding the `ID` column, it seems somewhere in the dataset the ID number mismatched the index.Now, let's investigate the TAFE dataset.<jupyter_code>tafe_survey.head()
tafe_survey.tail()
tafe_survey.info()
tafe_survey['Reason for ceasing employment'].value_counts(dropna=False)
tafe_survey['LengthofServiceOverall. Overall Length of Service at Institute (in years)'].value_counts()
tafe_survey['CurrentAge. Current Age'].value_counts()
tafe_survey.isnull().sum()<jupyter_output><empty_output><jupyter_text>The TAFE data has information about 702 employees (nº of rows), but again, there are many columns that we don't need to complete our analysis. There are missing values for all columns we are interested in, except `CurrentAge. Current Age`.  Cleaning DataWe'll start fixing the dete_survey dataframe that contains 'Not Stated' values which indicate values are missing. Then, we'll drop columns we know we don't need for our analysis.<jupyter_code># Converting 'Not Stated' values to NaN:
dete_survey = pd.read_csv('dete_survey.csv', na_values='Not Stated')
# Droping columns we won't need for both dataframes:
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)<jupyter_output><empty_output><jupyter_text>Now, let's rename columns that will be useful for both dataframes.<jupyter_code>#Replacing straights whitespaces with underscores, whitespaces 
#from the end of the strings and make all the capitalization lowercase.
dete_survey_updated.columns = dete_survey_updated.columns.str.replace('\s+', '_').str.strip().str.lower()
dete_survey_updated.columns
#Renaming columns names according to the map below:
map_name={'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.rename(mapper=map_name, axis=1, inplace=True)
tafe_survey_updated.head()<jupyter_output><empty_output><jupyter_text>Considering only ResignationsIn order to answer our stakeholders questions, we'll only analyze survey respondents who resigned, so their separation type contains the string 'Resignation'. As we saw before, *dete_survey* has three types of resignations: 'Resignation-Other reasons', 'Resignation-Other employer' and 'Resignation-Move overseas/interstate', whereas *tafe_survey* has only one type: 'Resignation'.Thus, we'll create a copy from both datasets slicing only rows with the values above.<jupyter_code>condition_a = dete_survey_updated['separationtype'] == 'Resignation-Other reasons'
condition_b = dete_survey_updated['separationtype'] == 'Resignation-Other employer' 
condition_c = dete_survey_updated['separationtype'] == 'Resignation-Move overseas/interstate'
condition_d = tafe_survey_updated['separationtype'] == 'Resignation'
dete_resignations = dete_survey_updated[condition_a | condition_b | condition_c].copy()
tafe_resignations = tafe_survey_updated[condition_d].copy()<jupyter_output><empty_output><jupyter_text>Now, we'll fix the date format issues of `cease_date` in dete_resignations.<jupyter_code>dete_resignations['cease_date'] = dete_resignations['cease_date'].str.split('/').str[-1]
dete_resignations['cease_date'] = dete_resignations['cease_date'].astype('float')
dete_resignations['cease_date'].value_counts(dropna=False)
dete_resignations['dete_start_date'].value_counts(dropna=False).sort_index()
tafe_resignations['cease_date'].value_counts()<jupyter_output><empty_output><jupyter_text>In order to also know the years of service of the DETE employees, we'll create a institute_service column similarly to the TAFE column one.<jupyter_code>dete_resignations['institute_service'] = dete_resignations['cease_date'] - dete_resignations['dete_start_date']
dete_resignations['institute_service'].value_counts(dropna=False).sort_index()<jupyter_output><empty_output><jupyter_text>Next, we'll identify any employees who resigned because they were dissatisfied. For that, we'll use the columns below to categorize employees as "dissatisfied" from each dataframe:**tafe_survey_updated:**- Contributing Factors. Dissatisfaction- Contributing Factors. Job Dissatisfaction**dete_survey_updated:**- job_dissatisfaction- dissatisfaction_with_the_department- physical_work_environment- lack_of_recognition- lack_of_job_security- work_location- employment_conditions- work_life_balance- workloadIf the employee indicated any of the factors above caused them to resign, we'll mark them as dissatisfied in a new column called `dissatisfied`.<jupyter_code>tafe_resignations['Contributing Factors. Dissatisfaction'].value_counts(dropna=False)
tafe_resignations['Contributing Factors. Job Dissatisfaction'].value_counts(dropna=False)
#Creating a function to convert the values into boolean ones
def update_vals(value):
    if pd.isnull(value):
        return np.nan
    elif value == '-':
        return False
    else:
        return True

tafe_list = ['Contributing Factors. Dissatisfaction', 
             'Contributing Factors. Job Dissatisfaction']
dete_list = ['job_dissatisfaction',
             'dissatisfaction_with_the_department',
             'physical_work_environment',
             'lack_of_recognition',
             'lack_of_job_security',
             'work_location',
             'employment_conditions',
             'work_life_balance',
             'workload']

tafe_resignations['dissatisfied'] = tafe_resignations[tafe_list].applymap(update_vals).any(axis=1, skipna=False)
dete_resignations['dissatisfied'] = dete_resignations[dete_list].any(axis=1, skipna=False)
tafe_resignations_up = tafe_resignations.copy()
dete_resignations_up = dete_resignations.copy()
tafe_resignations_up['dissatisfied'].value_counts(dropna=False)
dete_resignations_up['dissatisfied'].value_counts(dropna=False)
dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'
combined = pd.concat([dete_resignations_up, tafe_resignations_up], ignore_index=True)
combined.notnull().sum().sort_values()
combined_updated = combined.dropna(axis=1, thresh=500)
combined_updated.info()
combined_updated['institute_service_up'] = combined_updated['institute_service'].astype('str').str.extract(r'(\d+)')
combined_updated['institute_service_up'] = combined_updated['institute_service_up'].astype('float')
combined_updated['institute_service_up'].value_counts(dropna=False)
def stages(year):
    if year < 3:
        return 'New'
    elif (3 <= year < 7):
        return 'Experienced'
    elif (7 <= year < 11):
        return 'Established'
    elif pd.isnull(year):
        return np.nan
    else:
        return 'Veteran'
combined_updated['service_cat'] = combined_updated['institute_service_up'].apply(stages)
combined_updated['service_cat'].value_counts(dropna=False)
# Another topic
combined_updated['dissatisfied'].value_counts(dropna=False)
combined_updated['dissatisfied'] = combined_updated['dissatisfied'].fillna(False)
combined_updated['dissatisfied'].value_counts(dropna=False)
pct_combined = combined_updated.pivot_table(index='service_cat', values='dissatisfied', aggfunc=np.mean)
import matplotlib.pyplot as plt
%matplotlib inline
pct_combined.plot(kind='bar')
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09904570567553993, 'fraction_numerical': 0.0035158211953792064, 'mean_word_length': 6.485714285714286, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '428045', 'license': 'MIT', 'n_tokens_mistral': 3135, 'n_tokens_neox': 2973, 'n_words': 836, 'path': '06 - DETE and TAFE/DETEandTAFE.ipynb', 'repo_name': 'depes1/Dataquest-Projects', 'size': '9955'}