<jupyter_start><jupyter_text>Clean and Analyze Employee Exit Surveys1. Business Understanding.2. Data Understanding.    3. Prepare Data.4. Model Data5. Results  1. Business UnderstandingHi All!Today we are going to take a look at the Department of Education, Training and Employment (DETE) and the Technical and Further Education (TAFE) institute data. The data has been downloaded from the institute's site and you can find them [here](https://data.gov.au/dataset/ds-qld-89970a3b-182b-41ea-aea2-6f9f17b5907e/details?q=exit%20survey) and [here](https://data.gov.au/dataset/ds-qld-fe96ff30-d157-4a81-851d-215f2a0fe26d/details?q=exit%20survey)Our main goal is to clean, prepare and combine the two datasets 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?  2. Data UnderstandingHere we used the Department of Education, Training and Employment (DETE) and the Technical and Further Education (TAFE) institute data to attempt to answer our questions of interest. In this case, using the data to help us arrive at our questions of interest. We combined the results for both surveys to answer these questions. However, although both used the same survey template, one of them customized some of the answers. This is a preview of a couple of columns we'll work with from the dete_survey.csv:ID: An id used to identify the participant of the surveySeparationType: The reason why the person's employment endedCease Date: The year or month the person's employment endedDETE Start Date: The year the person began employment with the DETEBelow is a preview of a couple of columns we'll work with from the tafe_survey.csv:Record ID: An id used to identify the participant of the surveyReason for ceasing employment: The reason why the person's employment endedLengthofServiceOverall. Overall Length of Service at Institute (in years): The length of the person's employment (in years)  3. Prepare Data<jupyter_code># Import libraries
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
# Read csv to Pandas
dete_survey = pd.read_csv('dete_survey.csv', encoding='latin1')
tafe_survey = pd.read_csv('tafe_survey.csv', encoding='latin1')
# Print first 5 rows
dete_survey.head()
dete_survey.info()
dete_survey.isnull()<jupyter_output><empty_output><jupyter_text>For DETE survey there are 56 columns with 822 entries, as we can see some columns contain NAN values like Opportunities for promotion, Wellness programs, Aboriginal and others. Also, empty values are referring as Not stated. There are many columns that we don't need for our project.<jupyter_code># Print first 5 rows
tafe_survey.head()
tafe_survey.info()
tafe_survey.isnull()<jupyter_output><empty_output><jupyter_text>For TAFE survey we can see here as well a lot of columns with NAN values. We don't need all the columns for our project.Both surveys have the same columns but with a different title. Also, some multiple columns/answers indicate an employee resigned because they were dissatisfied..  Identify missing values and Drop unneccessary columns<jupyter_code># Read the Not Stated values in as NaN
dete_survey = pd.read_csv('dete_survey.csv', na_values='Not Stated')
# Drop unneccessary columns
dete_survey_updated = dete_survey.drop(dete_survey.columns[28:49], axis=1)
# Drop unneccessary columns
tafe_survey_updated = tafe_survey.drop(tafe_survey.columns[17:66], axis=1)
# Print first 5 rows
dete_survey_updated.head()
# Print first 5 rows
tafe_survey_updated.head()<jupyter_output><empty_output><jupyter_text>We have replaced the Not stated and - values with Nan values, and drop all the unneccessary columns from doth data frames, to have more clean dataframes.   Standardize column names<jupyter_code># Replace space with _, remove whitespace from end, make all capitalization lowercase
dete_survey_updated.columns = dete_survey_updated.columns.str.replace(' ', '_').str.strip().str.lower()
dete_survey_updated.columns
# Replace space with _, remove whitespace from end, make all capitalization lowercase
tafe_survey_updated.columns = tafe_survey_updated.columns.str.strip().str.replace(' ', '_').str.lower()
tafe_survey_updated.columns
# Rename columns
tafe_survey_updated.rename(columns={'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'}, inplace=True)
tafe_survey_updated.head()
dete_survey_updated.head()<jupyter_output><empty_output><jupyter_text>We have changed columns names from uppercase to lower, remove whitespace from the end of the strings, replace space with _ and rename columns name from tafe_survey_updated. This will help us to work with ease on both dataframes.  Remove unneeded Data<jupyter_code># Check unique values for column separationtype
dete_survey_updated['separationtype'].value_counts().plot(kind='bar', figsize=(10,8), width=0.5)
# Check unique values for column separationtype
tafe_survey_updated['separationtype'].value_counts().plot(kind='bar', figsize=(10,8), width=0.5)
# Update all separation types containing the word Resignation to Resignation
dete_survey_updated['separationtype'] = dete_survey_updated['separationtype'].str.split('-').str[0]

# Check the values in the separationtype column were updated correctly
dete_survey_updated['separationtype'].value_counts().plot(kind='bar', figsize=(10,8), width=0.5)
# Select only the resignation separation type
dete_resignations = dete_survey_updated[dete_survey_updated['separationtype'] == 'Resignation'].copy()
dete_resignations.head()
# Select only the resignation separation type
tafe_resignations = tafe_survey_updated[tafe_survey_updated['separationtype'] == 'Resignation'].copy()
tafe_resignations.head()<jupyter_output><empty_output><jupyter_text>Work with date columns<jupyter_code># Count the unique values in the column cease_date
dete_resignations['cease_date'].value_counts()
# Transform the dates deleting slash and month and convert them to float
dete_resignations['cease_date'] = dete_resignations['cease_date'].str.split('/').str[-1]
dete_resignations['cease_date'] = dete_resignations['cease_date'].astype('float')
# Print sorted unique values
dete_resignations['cease_date'].value_counts().sort_index(ascending=False)
# Print sorted unique values
dete_resignations['dete_start_date'].value_counts().sort_index(ascending=True)
# Print sorted unique values
tafe_resignations['cease_date'].value_counts().sort_index()<jupyter_output><empty_output><jupyter_text>The years in both dataframes don't completely align. The tafe_survey_updated dataframe contains some cease dates in 2009, but the dete_survey_updated dataframe does not. The tafe_survey_updated dataframe also contains many more cease dates in 2010 than the dete_survey_updaed dataframe. Since we aren't concerned with analyzing the results by year, we'll leave them as it is.  Add New Column Recall that our end goal is to answer the following question: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?We have noticed that the tafe_resignations dataframe already contains a "service" column, which we renamed to institute_service. To analyze both surveys together, we'll have to create a corresponding institute_service column in dete_resignations.<jupyter_code># Create new column subtracting  dete_start_date from cease_date
dete_resignations['institute_service'] = dete_resignations['cease_date'] - dete_resignations['dete_start_date']
dete_resignations['institute_service'].head()<jupyter_output><empty_output><jupyter_text>Dissatisfied factor<jupyter_code># Print columns
tafe_resignations.columns
# Print unique values
tafe_resignations['contributing_factors._job_dissatisfaction'].value_counts()
# Print unique values
tafe_resignations['contributing_factors._dissatisfaction'].value_counts()
# Update the values in the contributing factors columns to be either True, False, or NaN
def update_vals(x):
    if x == '-':
        return False
    elif pd.isnull(x):
        return np.nan
    else:
        return True
tafe_resignations['dissatisfied'] = tafe_resignations[['contributing_factors._dissatisfaction', 'contributing_factors._job_dissatisfaction']].applymap(update_vals).any(1, skipna=False)
tafe_resignations_up = tafe_resignations.copy()

# Check the unique values after the updates
tafe_resignations_up['dissatisfied'].value_counts(dropna=False)
# Update the values in columns related to dissatisfaction to be either True, False, or NaN
dete_resignations['dissatisfied'] = 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']].any(1, skipna=False)
dete_resignations_up = dete_resignations.copy()
dete_resignations_up['dissatisfied'].value_counts(dropna=False)<jupyter_output><empty_output><jupyter_text>Combine Datasets<jupyter_code># Create mew column for each dataset 
dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'
# Combine datasets
combined = pd.concat([dete_resignations_up, tafe_resignations_up], ignore_index=True)
combined.head()
# Drop columns with less than 500 non null values
combined_updated = combined.dropna(thresh=500, axis=1)
combined_updated<jupyter_output><empty_output><jupyter_text>Clean up the institute_service column<jupyter_code># Convert column to string
combined_updated['institute_service'].astype('str')
# Count unique values including NaN
combined_updated['institute_service'].value_counts(dropna=False)
# Extract the years of service and convert the type to float
combined_updated = combined_updated.copy()
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')

# Check the years extracted are correct
combined_updated['institute_service_up'].value_counts()

# Convert years of service to categories
def transform_service(val):
    if val >= 11:
        return "Veteran"
    elif 7 <= val < 11:
        return "Established"
    elif 3 <= val < 7:
        return "Experienced"
    elif pd.isnull(val):
        return np.nan
    else:
        return "New"

combined_updated['service_cat'] = combined_updated['institute_service_up'].apply(transform_service)

# Quick check of the update
combined_updated['service_cat'].value_counts()<jupyter_output><empty_output><jupyter_text>4. Model Data<jupyter_code># Count unique values including NaN
combined_updated['dissatisfied'].value_counts(dropna=False)
# Fill NaN values with the higher frequency answer
combined_updated['dissatisfied'].fillna(False, inplace=True)
# Count unique values
combined_updated['dissatisfied'].value_counts(dropna=False)
# Create Pivot table for plotting 
dis_pct = combined_updated.pivot_table(index='service_cat', values='dissatisfied')
# Create bar
dis_pct.plot(kind='bar')
# Plot a line to see the year of employment ends
combined_updated['cease_date'].value_counts().plot(kind='line', style='o', figsize=(12,9), title="The year the person's employment ended")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08690536224575558, 'fraction_numerical': 0.010085728693898134, 'mean_word_length': 6.996639784946237, 'pattern_counts': {'":': 0, '<': 49, '<?xml version=': 0, '>': 46, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '141799', 'license': 'CNRI-Python', 'n_tokens_mistral': 3681, 'n_tokens_neox': 3504, 'n_words': 1083, 'path': 'CleanAndAnalyzeEmployeeExitSurveys.ipynb', 'repo_name': 'NikolaosDimitrakakis/Exis_Surveys', 'size': '11898'}