#Read in the data
import pandas as pd
import numpy as np
dete_survey = pd.read_csv('D:\\dataquest\\projects\\dete_survey.csv')

#Quick exploration of the data
pd.options.display.max_columns = 150 # to avoid truncated output 
dete_survey.head()

#Read in the data
tafe_survey = pd.read_csv("D:\\dataquest\\projects\\tafe_survey.csv")

#Quick exploration of the data
tafe_survey.head()

# Read in the data again, but this time read `Not Stated` values as `NaN`
dete_survey = pd.read_csv('D:\\dataquest\\projects\\dete_survey.csv', na_values='Not Stated')

# Quick exploration of the data
dete_survey.head()

# Remove columns we don't need for our 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)

#Check that the columns were dropped
print(dete_survey_updated.columns)
print(tafe_survey_updated.columns)

# Clean the column names
dete_survey_updated.columns = dete_survey_updated.columns.str.lower().str.strip().str.replace(' ', '_')

# Check that the column names were updated correctly
dete_survey_updated.columns

# Update column names to match the names in dete_survey_updated
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)

# Check that the specified column names were updated correctly
tafe_survey_updated.columns

tafe_survey_updated['separationtype'].value_counts()

# Check the unique values for the separationtype column
dete_survey_updated['separationtype'].value_counts()

# 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()

# Select only the resignation separation types from each dataframe
dete_resignations = dete_survey_updated[dete_survey_updated['separationtype'] == 'Resignation'].copy()
tafe_resignations = tafe_survey_updated[tafe_survey_updated['separationtype'] == 'Resignation'].copy()

# Check the unique values
dete_resignations['cease_date'].value_counts()

# Extract the years and convert them to a float type
dete_resignations['cease_date'] = dete_resignations['cease_date'].str.split('/').str[-1]
dete_resignations['cease_date'] = dete_resignations['cease_date'].astype("float")

# Check the values again and look for outliers
dete_resignations['cease_date'].value_counts()

# Check the unique values and look for outliers
dete_resignations['dete_start_date'].value_counts().sort_values()

# Check the unique values
tafe_resignations['cease_date'].value_counts().sort_values()

# Calculate the length of time an employee spent in their respective workplace and create a new column
dete_resignations['institute_service'] = dete_resignations['cease_date'] - dete_resignations['dete_start_date']

# Quick check of the result
dete_resignations['institute_service'].head()

# Check the unique values
tafe_resignations['Contributing Factors. Dissatisfaction'].value_counts()

# Check the unique values
tafe_resignations['Contributing Factors. Job 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)

# Add an institute column
dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'

# Combine the dataframes
combined = pd.concat([dete_resignations_up, tafe_resignations_up], ignore_index=True,sort=False)

# Verify the number of non null values in each column
combined.notnull().sum().sort_values()

# Drop columns with less than 500 non null values
combined_updated = combined.dropna(thresh = 500, axis =1).copy()

# Check the unique values
combined_updated['institute_service'].value_counts(dropna=False)

# Extract the years of service and convert the type to float
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()

# Verify the unique values
combined_updated['dissatisfied'].value_counts(dropna=False)

# Replace missing values with the most frequent value, False
combined_updated['dissatisfied'] = combined_updated['dissatisfied'].fillna(False)

# Calculate the percentage of employees who resigned due to dissatisfaction in each category
dis_pct = combined_updated.pivot_table(index='service_cat', values='dissatisfied')

# Plot the results

dis_pct.plot(kind='bar', rot=30)

02026c1c6f3de9d2b0ce9dcd0c438dfaf8ef8a56
<mask token>


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


<mask token>


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'


<mask token>

<mask token>
dete_survey.head()
<mask token>
tafe_survey.head()
<mask token>
dete_survey.head()
<mask token>
print(dete_survey_updated.columns)
print(tafe_survey_updated.columns)
<mask token>
dete_survey_updated.columns
<mask token>
tafe_survey_updated.columns
tafe_survey_updated['separationtype'].value_counts()
dete_survey_updated['separationtype'].value_counts()
<mask token>
dete_survey_updated['separationtype'].value_counts()
<mask token>
dete_resignations['cease_date'].value_counts()
<mask token>
dete_resignations['cease_date'].value_counts()
dete_resignations['dete_start_date'].value_counts().sort_values()
tafe_resignations['cease_date'].value_counts().sort_values()
<mask token>
dete_resignations['institute_service'].head()
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


<mask token>
tafe_resignations_up['dissatisfied'].value_counts(dropna=False)
<mask token>
dete_resignations_up['dissatisfied'].value_counts(dropna=False)
<mask token>
combined.notnull().sum().sort_values()
<mask token>
combined_updated['institute_service'].value_counts(dropna=False)
<mask token>
combined_updated['institute_service_up'].value_counts()


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'


<mask token>
combined_updated['service_cat'].value_counts()
combined_updated['dissatisfied'].value_counts(dropna=False)
<mask token>
dis_pct.plot(kind='bar', rot=30)

<mask token>
dete_survey = pd.read_csv('D:\\dataquest\\projects\\dete_survey.csv')
pd.options.display.max_columns = 150
dete_survey.head()
tafe_survey = pd.read_csv('D:\\dataquest\\projects\\tafe_survey.csv')
tafe_survey.head()
dete_survey = pd.read_csv('D:\\dataquest\\projects\\dete_survey.csv',
    na_values='Not Stated')
dete_survey.head()
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)
print(dete_survey_updated.columns)
print(tafe_survey_updated.columns)
dete_survey_updated.columns = dete_survey_updated.columns.str.lower(
    ).str.strip().str.replace(' ', '_')
dete_survey_updated.columns
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)
tafe_survey_updated.columns
tafe_survey_updated['separationtype'].value_counts()
dete_survey_updated['separationtype'].value_counts()
dete_survey_updated['separationtype'] = dete_survey_updated['separationtype'
    ].str.split('-').str[0]
dete_survey_updated['separationtype'].value_counts()
dete_resignations = dete_survey_updated[dete_survey_updated[
    'separationtype'] == 'Resignation'].copy()
tafe_resignations = tafe_survey_updated[tafe_survey_updated[
    'separationtype'] == 'Resignation'].copy()
dete_resignations['cease_date'].value_counts()
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()
dete_resignations['dete_start_date'].value_counts().sort_values()
tafe_resignations['cease_date'].value_counts().sort_values()
dete_resignations['institute_service'] = dete_resignations['cease_date'
    ] - dete_resignations['dete_start_date']
dete_resignations['institute_service'].head()
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_resignations['dissatisfied'] = tafe_resignations[[
    'Contributing Factors. Dissatisfaction',
    'Contributing Factors. Job Dissatisfaction']].applymap(update_vals).any(
    1, skipna=False)
tafe_resignations_up = tafe_resignations.copy()
tafe_resignations_up['dissatisfied'].value_counts(dropna=False)
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)
dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'
combined = pd.concat([dete_resignations_up, tafe_resignations_up],
    ignore_index=True, sort=False)
combined.notnull().sum().sort_values()
combined_updated = combined.dropna(thresh=500, axis=1).copy()
combined_updated['institute_service'].value_counts(dropna=False)
combined_updated['institute_service_up'] = combined_updated['institute_service'
    ].astype('str').str.extract('(\\d+)')
combined_updated['institute_service_up'] = combined_updated[
    'institute_service_up'].astype('float')
combined_updated['institute_service_up'].value_counts()


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)
combined_updated['service_cat'].value_counts()
combined_updated['dissatisfied'].value_counts(dropna=False)
combined_updated['dissatisfied'] = combined_updated['dissatisfied'].fillna(
    False)
dis_pct = combined_updated.pivot_table(index='service_cat', values=
    'dissatisfied')
dis_pct.plot(kind='bar', rot=30)

import pandas as pd
import numpy as np
dete_survey = pd.read_csv('D:\\dataquest\\projects\\dete_survey.csv')
pd.options.display.max_columns = 150
dete_survey.head()
tafe_survey = pd.read_csv('D:\\dataquest\\projects\\tafe_survey.csv')
tafe_survey.head()
dete_survey = pd.read_csv('D:\\dataquest\\projects\\dete_survey.csv',
    na_values='Not Stated')
dete_survey.head()
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)
print(dete_survey_updated.columns)
print(tafe_survey_updated.columns)
dete_survey_updated.columns = dete_survey_updated.columns.str.lower(
    ).str.strip().str.replace(' ', '_')
dete_survey_updated.columns
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)
tafe_survey_updated.columns
tafe_survey_updated['separationtype'].value_counts()
dete_survey_updated['separationtype'].value_counts()
dete_survey_updated['separationtype'] = dete_survey_updated['separationtype'
    ].str.split('-').str[0]
dete_survey_updated['separationtype'].value_counts()
dete_resignations = dete_survey_updated[dete_survey_updated[
    'separationtype'] == 'Resignation'].copy()
tafe_resignations = tafe_survey_updated[tafe_survey_updated[
    'separationtype'] == 'Resignation'].copy()
dete_resignations['cease_date'].value_counts()
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()
dete_resignations['dete_start_date'].value_counts().sort_values()
tafe_resignations['cease_date'].value_counts().sort_values()
dete_resignations['institute_service'] = dete_resignations['cease_date'
    ] - dete_resignations['dete_start_date']
dete_resignations['institute_service'].head()
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_resignations['dissatisfied'] = tafe_resignations[[
    'Contributing Factors. Dissatisfaction',
    'Contributing Factors. Job Dissatisfaction']].applymap(update_vals).any(
    1, skipna=False)
tafe_resignations_up = tafe_resignations.copy()
tafe_resignations_up['dissatisfied'].value_counts(dropna=False)
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)
dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'
combined = pd.concat([dete_resignations_up, tafe_resignations_up],
    ignore_index=True, sort=False)
combined.notnull().sum().sort_values()
combined_updated = combined.dropna(thresh=500, axis=1).copy()
combined_updated['institute_service'].value_counts(dropna=False)
combined_updated['institute_service_up'] = combined_updated['institute_service'
    ].astype('str').str.extract('(\\d+)')
combined_updated['institute_service_up'] = combined_updated[
    'institute_service_up'].astype('float')
combined_updated['institute_service_up'].value_counts()


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)
combined_updated['service_cat'].value_counts()
combined_updated['dissatisfied'].value_counts(dropna=False)
combined_updated['dissatisfied'] = combined_updated['dissatisfied'].fillna(
    False)
dis_pct = combined_updated.pivot_table(index='service_cat', values=
    'dissatisfied')
dis_pct.plot(kind='bar', rot=30)

#Read in the data
import pandas as pd
import numpy as np
dete_survey = pd.read_csv('D:\\dataquest\\projects\\dete_survey.csv')

#Quick exploration of the data
pd.options.display.max_columns = 150 # to avoid truncated output 
dete_survey.head()

#Read in the data
tafe_survey = pd.read_csv("D:\\dataquest\\projects\\tafe_survey.csv")

#Quick exploration of the data
tafe_survey.head()

# Read in the data again, but this time read `Not Stated` values as `NaN`
dete_survey = pd.read_csv('D:\\dataquest\\projects\\dete_survey.csv', na_values='Not Stated')

# Quick exploration of the data
dete_survey.head()

# Remove columns we don't need for our 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)

#Check that the columns were dropped
print(dete_survey_updated.columns)
print(tafe_survey_updated.columns)

# Clean the column names
dete_survey_updated.columns = dete_survey_updated.columns.str.lower().str.strip().str.replace(' ', '_')

# Check that the column names were updated correctly
dete_survey_updated.columns

# Update column names to match the names in dete_survey_updated
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)

# Check that the specified column names were updated correctly
tafe_survey_updated.columns

tafe_survey_updated['separationtype'].value_counts()

# Check the unique values for the separationtype column
dete_survey_updated['separationtype'].value_counts()

# 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()

# Select only the resignation separation types from each dataframe
dete_resignations = dete_survey_updated[dete_survey_updated['separationtype'] == 'Resignation'].copy()
tafe_resignations = tafe_survey_updated[tafe_survey_updated['separationtype'] == 'Resignation'].copy()

# Check the unique values
dete_resignations['cease_date'].value_counts()

# Extract the years and convert them to a float type
dete_resignations['cease_date'] = dete_resignations['cease_date'].str.split('/').str[-1]
dete_resignations['cease_date'] = dete_resignations['cease_date'].astype("float")

# Check the values again and look for outliers
dete_resignations['cease_date'].value_counts()

# Check the unique values and look for outliers
dete_resignations['dete_start_date'].value_counts().sort_values()

# Check the unique values
tafe_resignations['cease_date'].value_counts().sort_values()

# Calculate the length of time an employee spent in their respective workplace and create a new column
dete_resignations['institute_service'] = dete_resignations['cease_date'] - dete_resignations['dete_start_date']

# Quick check of the result
dete_resignations['institute_service'].head()

# Check the unique values
tafe_resignations['Contributing Factors. Dissatisfaction'].value_counts()

# Check the unique values
tafe_resignations['Contributing Factors. Job 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)

# Add an institute column
dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'

# Combine the dataframes
combined = pd.concat([dete_resignations_up, tafe_resignations_up], ignore_index=True,sort=False)

# Verify the number of non null values in each column
combined.notnull().sum().sort_values()

# Drop columns with less than 500 non null values
combined_updated = combined.dropna(thresh = 500, axis =1).copy()

# Check the unique values
combined_updated['institute_service'].value_counts(dropna=False)

# Extract the years of service and convert the type to float
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()

# Verify the unique values
combined_updated['dissatisfied'].value_counts(dropna=False)

# Replace missing values with the most frequent value, False
combined_updated['dissatisfied'] = combined_updated['dissatisfied'].fillna(False)

# Calculate the percentage of employees who resigned due to dissatisfaction in each category
dis_pct = combined_updated.pivot_table(index='service_cat', values='dissatisfied')

# Plot the results

dis_pct.plot(kind='bar', rot=30)
