<jupyter_start><jupyter_text>Employee Exit Survey The ClientTAFE and DETE are vocational colleges in Australia. They have been doing exit surveys for a while and have now gathered a dataset of about 1600 results which they would like analysed. The client is focussed on internal contributing factors. Aims of Analysis: DissatisfactionThe client has asked for a report to help them understand the results of their recent exit survey.They wish to understand the profile of employees who cite dissatisfaction as a contributing factor to their exit from the organisation.Leadership wants to understand where to target retention improvement strategies.  Conclusions: Age and DissatisfactionOlder employees make up a signficant proportion of the employee demographics at TAFE and DETE. This explained the much larger number of dissatisfied leavers amongster older populations.Analysis revealed:- TAFE: 46-50 and 21-25 are the most dissatisfied age groups, showing significant levels above the college's mean, and 10% more than other age groups.- DETE: 51-50, 31-35, 26-30 are the most dissatisfied age groups, showing significant levels above the college's mean, and also 10% above that of other age groups.  VisualisationsThe correlation analysis revealed that there was likely a pattern in age and dissatisfaction. Initially, it seemed that older people were experiencing higher levels, but really it's younger and middle-aged people. A Story of Two Bar ChartsBar charts, stacked as percentages, and arranged side-by-side make excellent tools for comparing.At the end of this notebook there is a useful infographic to communicate the above analysis.The bar charts allow the viewer to immediately grasp the comparison between TAFE and DETE and between the different age groups through use of similarity, proximity and (im)balance across a line of symmetry.  Notebooks and ReportsThe following notebooks and documents are part of this anaylsis: Jupyter Notebook Filename: Summary- [employee_exit_survey_cleaning_1.ibynb](https://github.com/jholidayscott/employee_exit_survey/blob/main/employee_exit_survey_cleaning_1.ipynb): Columns drops, missing data, renaming columns, tidying data for consistency- [employee_exit_survey_cleaning_2.ibynb](https://github.com/jholidayscott/employee_exit_survey/blob/main/employee_exit_survey_cleaning_2.ipynb): Adding calculated columns, adding category columns, further drops- [employee_exit_survey_correlation.ibynb](https://github.com/jholidayscott/employee_exit_survey/blob/main/employee_exit_survey_correlation.ipynb): Investigating correlations to guide analysis- [employee_exit_survey_gender.ibynb](https://github.com/jholidayscott/employee_exit_survey/blob/main/employee_exit_survey_gender.ipynb): Aggregation by pivot_table of gender subsets, visualisations- [employee_exit_survey_age.ibynb](https://github.com/jholidayscott/employee_exit_survey/blob/main/employee_exit_survey_age.ipynb): Aggregation by pivot_table of age subsets, visualisations- [employee_exit_survey_conflict.ibynb](https://github.com/jholidayscott/employee_exit_survey/blob/main/employee_exit_survey_conflict.ipynb): Exploration of conflict as a contributory factor<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.style as style
import numpy as np
survey = pd.read_csv('employee_exit_survey_clean_final.csv')
survey.head()<jupyter_output><empty_output><jupyter_text>Exploratory ChartsFrom the initial exploration of correlations, there is a clear tendency for older employees to be more dissatisfied than younger ones.It would be beneficial to explore:- Measure the scale of increase in dissatisfaction as age increases- Link to length of service to see if this is a factor (note initial correlation analysis did not suggest length of servce was correlated directly to dissatisfaction)- Compare TAFE to DETE because there were clear differences.  Baseline Age ChartsThese charts describe the age and dissatisfaction profiles of the two colleges.<jupyter_code>pv_age = survey.pivot_table(values='cf_dept_or_job_dissatisfaction', index='age', aggfunc=np.sum, margins=True)
pv_age.plot(kind='bar', xlim=((0,10)), title='Dept and Job Dissatisfaction as a CF by Age', legend=False)<jupyter_output><empty_output><jupyter_text>Initially, there appears to be rise in dissatisfaction in older people, and perhaps a small increase in the 20-30 buckets.To see if there is a tendency for these employees to cite dissatisfaction as their reason for leaving more than their peers, these values have to made proportionate to the age distribution.<jupyter_code>values = survey.loc[survey['college'] == 'TAFE','age']
values.value_counts().sort_values(ascending=True).plot(kind='bar', title='TAFE Age Distribution')
values = survey.loc[survey['college'] == 'DETE','age']
values.value_counts().sort_values(ascending=True).plot(kind='bar', title='DETE Age Distribution')<jupyter_output><empty_output><jupyter_text>Both TAFE and DETE have many more employees aged 56+ than any other age group.TAFE does not have a higher number of 20-30 year olds to correspond to the slight bulge in dissatisfaction we saw above.Therefore, it is worth pressing on with a fulling proportional analysis.<jupyter_code># Split the dataset into TAFE and DETE because the age profile is different.
tafe_survey = survey[survey['college'] == 'TAFE']
dete_survey = survey[survey['college'] == 'DETE']
# Find dissatisfied leavers in each age bucket.
pv_tafe = tafe_survey.pivot_table(values='cf_dept_or_job_dissatisfaction', index='age', aggfunc=np.sum, margins=True).drop('All')
pv_dete = dete_survey.pivot_table(values='cf_dept_or_job_dissatisfaction', index='age', aggfunc=np.sum, margins=True).drop('All')

# Get TAFE DataFrame ready for converting to percentages.
pv_tafe['All'] = tafe_survey['age'].value_counts()
pv_tafe['Satisfied or Other'] = pv_tafe['All'] - pv_tafe['cf_dept_or_job_dissatisfaction']
print(pv_tafe)

# Get DETE DataFrame ready for converting to percentages.
pv_dete['All'] = dete_survey['age'].value_counts()
pv_dete['Satisfied or Other'] = pv_dete['All'] - pv_dete['cf_dept_or_job_dissatisfaction']
print(pv_dete)
# Rename some columns and make it easier to work with.
new_cols = {
    'cf_dept_or_job_dissatisfaction':'Dissatisfied', 
    'All':'Total', 
    'Satisfied or Other':'Satisfied'
}

pv_tafe.rename(new_cols, axis=1, inplace=True)
pv_dete.rename(new_cols, axis=1, inplace=True)
print(pv_tafe)
print(pv_dete)

# Percentage conversion function for TAFE
def tafe_perc(item):
    div = item/pv_tafe['Total']
    return div*100

# Percentage conversion function for DETE
def dete_perc(item):
    div = item/pv_dete['Total']
    return div*100

# Apply the conversion and make a new DataFrame
columns = ['Dissatisfied','Satisfied']

pv_tafe_perc = pv_tafe[columns].apply(tafe_perc)
print(pv_tafe_perc)

pv_dete_perc = pv_dete[columns].apply(dete_perc)
print(pv_dete_perc)
# Quick exploratory Plot
pv_tafe_perc.plot(kind='bar', stacked=True, title = 'Percentage Stacked Bar Graph', mark_right = True)

# Quick exploratory Plot
pv_dete_perc.plot(kind='bar', stacked=True)<jupyter_output><empty_output><jupyter_text>There is no correlation between older employees and dissatisfaction. Older employees make a significant portion of the population which initially suggested that there may be a dissatisfaction problem in older employees. However, once adjusted to percentages in each age bucket, it is clear that there are no negative trends in that age group.Other age groups vary fairly widely. For example, twice as many 31-35 year olds are dissatisfied as 21-25 year olds.This information is worth knowing.<jupyter_code>pv_tafe_perc.index
# ax = pd.crosstab(df['Name'], df['Subject']).apply(lambda r: r/r.sum()*100, axis=1)
fig, (ax1,ax2) = plt.subplots(ncols=2, nrows=1, figsize=(15,5), sharey=True)

style.use('default')
style.use('fivethirtyeight')

width = 0.8
satisfied_color = '#6d904f'
dissatisfied_color = '#e5ae38'

ax1.bar(pv_tafe_perc.index, pv_tafe_perc['Satisfied'],width,color=satisfied_color)
ax1.bar(pv_tafe_perc.index, pv_tafe_perc['Dissatisfied'],width, bottom=pv_tafe_perc['Satisfied'],color=dissatisfied_color)

ax2.bar(pv_dete_perc.index, pv_dete_perc['Satisfied'],width,color=satisfied_color)
ax2.bar(pv_dete_perc.index, pv_dete_perc['Dissatisfied'],width, bottom=pv_dete_perc['Satisfied'],color=dissatisfied_color)

x_tick_size = 11
x_tick_weight = 'bold'
x_tick_color = '#8b8b8b'

ax1.grid(False)
ax1.set_xticks(pv_tafe_perc.index)
ax1.set_xticklabels(pv_tafe_perc.index,size=x_tick_size, color=x_tick_color,weight=x_tick_weight)
ax1.set_yticklabels([])

ax2.grid(False)
ax2.set_xticks(pv_dete_perc.index)
ax2.set_xticklabels(pv_dete_perc.index,size=x_tick_size, color=x_tick_color,weight=x_tick_weight)
ax2.set_yticklabels([])

title_x = -0.4
title_y = 103
title_size = 18
title_weight = 'bold'
title_color = '#8b8b8b'
ax1.text(x=title_x, y=title_y, s='Employee Satisfaction at TAFE by Age',size=title_size, weight=title_weight, color=title_color)
ax2.text(x=title_x, y=title_y, s='Employee Satisfaction at DETE by Age',size=title_size, weight=title_weight, color=title_color)

t_perc_x = -0.3
t_perc_y = 93
t_perc_size = 11
t_perc_weight = 'bold'
t_perc_color = 'white'

for perc in pv_tafe_perc['Dissatisfied']:
    ax1.text(x=t_perc_x,y=t_perc_y,s=str(round(perc))+'%',size=t_perc_size,color=t_perc_color,weight=t_perc_weight)
    t_perc_x += 1

d_perc_x = -0.3
d_perc_y = 93
d_perc_size = 11
d_perc_weight = 'bold'
d_perc_color = 'white'

for perc in pv_dete_perc['Dissatisfied']:
    ax2.text(x=d_perc_x,y=d_perc_y,s=str(round(perc))+'%',size=d_perc_size,color=d_perc_color,weight=d_perc_weight)
    d_perc_x += 1

    
plt.show()<jupyter_output><empty_output><jupyter_text>This visualisation is not optimal. The arrangement makes comparison hard, which is surely the point of having two charts. There's repetition in the title and in the age group axes.<jupyter_code># ax = pd.crosstab(df['Name'], df['Subject']).apply(lambda r: r/r.sum()*100, axis=1)
fig, (ax1,ax2) = plt.subplots(ncols=2, nrows=1, figsize=(12,5), sharey=True)

style.use('default')
style.use('fivethirtyeight')

# Style Plot
width = 0.8
satisfied_color = '#6d904f'
dissatisfied_color = '#e5ae38'



# Make Plot
ax1.barh(pv_tafe_perc.index, pv_tafe_perc['Satisfied'],width,color=satisfied_color)
ax1.barh(pv_tafe_perc.index, pv_tafe_perc['Dissatisfied'],width, left=pv_tafe_perc['Satisfied'],color=dissatisfied_color)

ax2.barh(pv_dete_perc.index, pv_dete_perc['Dissatisfied'],width,color=dissatisfied_color, label='Dissatisfied')
ax2.barh(pv_dete_perc.index, pv_dete_perc['Satisfied'],width, left=pv_dete_perc['Dissatisfied'],color=satisfied_color, label='Satisfied')

# Style and Display Y Axis
y_tick_size = 13
y_tick_weight = 'bold'
y_tick_color = '#8b8b8b'
x_pos = 1.15

ax1.grid(False)
ax1.set_yticks(pv_tafe_perc.index)
ax1.set_yticklabels(pv_tafe_perc.index,size=y_tick_size, color=y_tick_color,weight=y_tick_weight,x=x_pos)
ax1.set_xticklabels([])

# Style out X Axis
ax2.grid(False)
ax2.set_xticklabels([])

# Title
title_x = 0.09
title_y = 0.9
title_size = 35
title_weight = 'bold'
title_color = '#8b8b8b'
fig.text(x=title_x, y=title_y, s='Employee Satisfaction by Age Group',size=title_size, weight=title_weight, color=title_color)

# Style and Display Percentage Values for TAFE
t_perc_x = 90
t_perc_y = -0.15
t_perc_size = 11
t_perc_weight = 'bold'
t_perc_color = 'white'

for perc in pv_tafe_perc['Dissatisfied']:
    ax1.text(x=t_perc_x,y=t_perc_y,s=str(round(perc))+'%',size=t_perc_size,color=t_perc_color,weight=t_perc_weight)
    t_perc_y += 1

# Style and Display Percentage Values for DETE
d_perc_x = 1.5
d_perc_y = -0.15
d_perc_size = 11
d_perc_weight = 'bold'
d_perc_color = 'white'

for perc in pv_dete_perc['Dissatisfied']:
    ax2.text(x=d_perc_x,y=d_perc_y,s=str(round(perc))+'%',size=d_perc_size,color=d_perc_color,weight=d_perc_weight)
    d_perc_y += 1

# Labels 'DETE' and 'TAFE'
ax1.text(x=2,y=7.75,s='TAFE',color='white',weight='bold',size=20)
ax2.text(x=80,y=7.75,s='DETE',color='white',weight='bold',size=20)

# Mean Line
ax1.axvline(100-pv_tafe_perc['Dissatisfied'].mean(), linewidth=1,ymin=0.05, ymax=0.95,label='Mean Dissatisfaction')
ax2.axvline(pv_dete_perc['Dissatisfied'].mean(), linewidth=1,ymin=0.05, ymax=0.95)

# Legends
ax1.legend(ncol=3,fontsize='small', loc='right', bbox_to_anchor=(0.98, 0),markerfirst=False,frameon=False)
ax2.legend(ncol=3,fontsize='small', loc='right', bbox_to_anchor=(0.64, 0),frameon=False)

# Footer
ax1.text(x=0, y=-1.9, s='<NAME>, 2021' + ' '*169, backgroundcolor='grey', color='white', size=11)

plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10711177913139783, 'fraction_numerical': 0.019856024048730322, 'mean_word_length': 7.140373470701867, 'pattern_counts': {'":': 0, '<': 26, '<?xml version=': 0, '>': 26, 'https://': 6, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '17419', 'license': 'CC0-1.0', 'n_tokens_mistral': 4736, 'n_tokens_neox': 4412, 'n_words': 1089, 'path': '.ipynb_checkpoints/employee_exit_survey_age-checkpoint.ipynb', 'repo_name': 'jholidayscott/employee_exit_survey', 'size': '12654'}