Guided Project: Clean and Analyze Employee Exit Surveys

By: Naftali N Indongo

1. Introduction

In this guided project, I will 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. You can find the DETE exit survey data here. The original TAFE exit survey data is no longer available. Some slight modifications to the original datasets has been made to make them easier to work with, including changing the encoding to UTF-8 (the original ones are encoded using cp1252.)

In this project, I will play the role of a data analyst and pretend the stakeholders want to know the following:

  • 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?

They want me to combine 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. The guided steps, aim to do most of the data cleaning and get me started analyzing the first question.

Since a data dictionary wasn't provided with the dataset. In a job setting, I will make sure to meet with a manager to confirm the definitions of the data. For this project, I will use my general knowledge to define the columns.

Below is a preview of a couple columns I will work with from the dete_survey.csv:

  • 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

Below is a preview of a couple columns we'll work with from the tafe_survey.csv:

  • 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)

I will start by reading the datasets into pandas and exploring them.

In [917]:
# importing the required libraries
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
%matplotlib inline
In [918]:
# Using pandas to read in the datasets
"""
I will use the pd.read_csv() function to specify values that 
should be represented as NaN and fix the missing values first.
"""
dete_survey = pd.read_csv("dete_survey.csv", na_values='Not Stated')
tafe_survey = pd.read_csv("tafe_survey.csv")

I will use the DataFrame.info() and DataFrame.head() methods to print information about both dataframes, as well as the first few rows. Furthermore, I will use the Series.value_counts() and DataFrame.isnull() methods to explore the data and figure out some next steps.

In [919]:
dete_survey.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 822 entries, 0 to 821
Data columns (total 56 columns):
 #   Column                               Non-Null Count  Dtype  
---  ------                               --------------  -----  
 0   ID                                   822 non-null    int64  
 1   SeparationType                       822 non-null    object 
 2   Cease Date                           788 non-null    object 
 3   DETE Start Date                      749 non-null    float64
 4   Role Start Date                      724 non-null    float64
 5   Position                             817 non-null    object 
 6   Classification                       455 non-null    object 
 7   Region                               717 non-null    object 
 8   Business Unit                        126 non-null    object 
 9   Employment Status                    817 non-null    object 
 10  Career move to public sector         822 non-null    bool   
 11  Career move to private sector        822 non-null    bool   
 12  Interpersonal conflicts              822 non-null    bool   
 13  Job dissatisfaction                  822 non-null    bool   
 14  Dissatisfaction with the department  822 non-null    bool   
 15  Physical work environment            822 non-null    bool   
 16  Lack of recognition                  822 non-null    bool   
 17  Lack of job security                 822 non-null    bool   
 18  Work location                        822 non-null    bool   
 19  Employment conditions                822 non-null    bool   
 20  Maternity/family                     822 non-null    bool   
 21  Relocation                           822 non-null    bool   
 22  Study/Travel                         822 non-null    bool   
 23  Ill Health                           822 non-null    bool   
 24  Traumatic incident                   822 non-null    bool   
 25  Work life balance                    822 non-null    bool   
 26  Workload                             822 non-null    bool   
 27  None of the above                    822 non-null    bool   
 28  Professional Development             808 non-null    object 
 29  Opportunities for promotion          735 non-null    object 
 30  Staff morale                         816 non-null    object 
 31  Workplace issue                      788 non-null    object 
 32  Physical environment                 817 non-null    object 
 33  Worklife balance                     815 non-null    object 
 34  Stress and pressure support          810 non-null    object 
 35  Performance of supervisor            813 non-null    object 
 36  Peer support                         812 non-null    object 
 37  Initiative                           813 non-null    object 
 38  Skills                               811 non-null    object 
 39  Coach                                767 non-null    object 
 40  Career Aspirations                   746 non-null    object 
 41  Feedback                             792 non-null    object 
 42  Further PD                           768 non-null    object 
 43  Communication                        814 non-null    object 
 44  My say                               812 non-null    object 
 45  Information                          816 non-null    object 
 46  Kept informed                        813 non-null    object 
 47  Wellness programs                    766 non-null    object 
 48  Health & Safety                      793 non-null    object 
 49  Gender                               798 non-null    object 
 50  Age                                  811 non-null    object 
 51  Aboriginal                           16 non-null     object 
 52  Torres Strait                        3 non-null      object 
 53  South Sea                            7 non-null      object 
 54  Disability                           23 non-null     object 
 55  NESB                                 32 non-null     object 
dtypes: bool(18), float64(2), int64(1), object(35)
memory usage: 258.6+ KB
In [920]:
tafe_survey.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 702 entries, 0 to 701
Data columns (total 72 columns):
 #   Column                                                                                                                                                         Non-Null Count  Dtype  
---  ------                                                                                                                                                         --------------  -----  
 0   Record ID                                                                                                                                                      702 non-null    float64
 1   Institute                                                                                                                                                      702 non-null    object 
 2   WorkArea                                                                                                                                                       702 non-null    object 
 3   CESSATION YEAR                                                                                                                                                 695 non-null    float64
 4   Reason for ceasing employment                                                                                                                                  701 non-null    object 
 5   Contributing Factors. Career Move - Public Sector                                                                                                              437 non-null    object 
 6   Contributing Factors. Career Move - Private Sector                                                                                                             437 non-null    object 
 7   Contributing Factors. Career Move - Self-employment                                                                                                            437 non-null    object 
 8   Contributing Factors. Ill Health                                                                                                                               437 non-null    object 
 9   Contributing Factors. Maternity/Family                                                                                                                         437 non-null    object 
 10  Contributing Factors. Dissatisfaction                                                                                                                          437 non-null    object 
 11  Contributing Factors. Job Dissatisfaction                                                                                                                      437 non-null    object 
 12  Contributing Factors. Interpersonal Conflict                                                                                                                   437 non-null    object 
 13  Contributing Factors. Study                                                                                                                                    437 non-null    object 
 14  Contributing Factors. Travel                                                                                                                                   437 non-null    object 
 15  Contributing Factors. Other                                                                                                                                    437 non-null    object 
 16  Contributing Factors. NONE                                                                                                                                     437 non-null    object 
 17  Main Factor. Which of these was the main factor for leaving?                                                                                                   113 non-null    object 
 18  InstituteViews. Topic:1. I feel the senior leadership had a clear vision and direction                                                                         608 non-null    object 
 19  InstituteViews. Topic:2. I was given access to skills training to help me do my job better                                                                     613 non-null    object 
 20  InstituteViews. Topic:3. I was given adequate opportunities for personal development                                                                           610 non-null    object 
 21  InstituteViews. Topic:4. I was given adequate opportunities for promotion within %Institute]Q25LBL%                                                            608 non-null    object 
 22  InstituteViews. Topic:5. I felt the salary for the job was right for the responsibilities I had                                                                615 non-null    object 
 23  InstituteViews. Topic:6. The organisation recognised when staff did good work                                                                                  607 non-null    object 
 24  InstituteViews. Topic:7. Management was generally supportive of me                                                                                             614 non-null    object 
 25  InstituteViews. Topic:8. Management was generally supportive of my team                                                                                        608 non-null    object 
 26  InstituteViews. Topic:9. I was kept informed of the changes in the organisation which would affect me                                                          610 non-null    object 
 27  InstituteViews. Topic:10. Staff morale was positive within the Institute                                                                                       602 non-null    object 
 28  InstituteViews. Topic:11. If I had a workplace issue it was dealt with quickly                                                                                 601 non-null    object 
 29  InstituteViews. Topic:12. If I had a workplace issue it was dealt with efficiently                                                                             597 non-null    object 
 30  InstituteViews. Topic:13. If I had a workplace issue it was dealt with discreetly                                                                              601 non-null    object 
 31  WorkUnitViews. Topic:14. I was satisfied with the quality of the management and supervision within my work unit                                                609 non-null    object 
 32  WorkUnitViews. Topic:15. I worked well with my colleagues                                                                                                      605 non-null    object 
 33  WorkUnitViews. Topic:16. My job was challenging and interesting                                                                                                607 non-null    object 
 34  WorkUnitViews. Topic:17. I was encouraged to use my initiative in the course of my work                                                                        610 non-null    object 
 35  WorkUnitViews. Topic:18. I had sufficient contact with other people in my job                                                                                  613 non-null    object 
 36  WorkUnitViews. Topic:19. I was given adequate support and co-operation by my peers to enable me to do my job                                                   609 non-null    object 
 37  WorkUnitViews. Topic:20. I was able to use the full range of my skills in my job                                                                               609 non-null    object 
 38  WorkUnitViews. Topic:21. I was able to use the full range of my abilities in my job. ; Category:Level of Agreement; Question:YOUR VIEWS ABOUT YOUR WORK UNIT]  608 non-null    object 
 39  WorkUnitViews. Topic:22. I was able to use the full range of my knowledge in my job                                                                            608 non-null    object 
 40  WorkUnitViews. Topic:23. My job provided sufficient variety                                                                                                    611 non-null    object 
 41  WorkUnitViews. Topic:24. I was able to cope with the level of stress and pressure in my job                                                                    610 non-null    object 
 42  WorkUnitViews. Topic:25. My job allowed me to balance the demands of work and family to my satisfaction                                                        611 non-null    object 
 43  WorkUnitViews. Topic:26. My supervisor gave me adequate personal recognition and feedback on my performance                                                    606 non-null    object 
 44  WorkUnitViews. Topic:27. My working environment was satisfactory e.g. sufficient space, good lighting, suitable seating and working area                       610 non-null    object 
 45  WorkUnitViews. Topic:28. I was given the opportunity to mentor and coach others in order for me to pass on my skills and knowledge prior to my cessation date  609 non-null    object 
 46  WorkUnitViews. Topic:29. There was adequate communication between staff in my unit                                                                             603 non-null    object 
 47  WorkUnitViews. Topic:30. Staff morale was positive within my work unit                                                                                         606 non-null    object 
 48  Induction. Did you undertake Workplace Induction?                                                                                                              619 non-null    object 
 49  InductionInfo. Topic:Did you undertake a Corporate Induction?                                                                                                  432 non-null    object 
 50  InductionInfo. Topic:Did you undertake a Institute Induction?                                                                                                  483 non-null    object 
 51  InductionInfo. Topic: Did you undertake Team Induction?                                                                                                        440 non-null    object 
 52  InductionInfo. Face to Face Topic:Did you undertake a Corporate Induction; Category:How it was conducted?                                                      555 non-null    object 
 53  InductionInfo. On-line Topic:Did you undertake a Corporate Induction; Category:How it was conducted?                                                           555 non-null    object 
 54  InductionInfo. Induction Manual Topic:Did you undertake a Corporate Induction?                                                                                 555 non-null    object 
 55  InductionInfo. Face to Face Topic:Did you undertake a Institute Induction?                                                                                     530 non-null    object 
 56  InductionInfo. On-line Topic:Did you undertake a Institute Induction?                                                                                          555 non-null    object 
 57  InductionInfo. Induction Manual Topic:Did you undertake a Institute Induction?                                                                                 553 non-null    object 
 58  InductionInfo. Face to Face Topic: Did you undertake Team Induction; Category?                                                                                 555 non-null    object 
 59  InductionInfo. On-line Topic: Did you undertake Team Induction?process you undertook and how it was conducted.]                                                555 non-null    object 
 60  InductionInfo. Induction Manual Topic: Did you undertake Team Induction?                                                                                       555 non-null    object 
 61  Workplace. Topic:Did you and your Manager develop a Performance and Professional Development Plan (PPDP)?                                                      608 non-null    object 
 62  Workplace. Topic:Does your workplace promote a work culture free from all forms of unlawful discrimination?                                                    594 non-null    object 
 63  Workplace. Topic:Does your workplace promote and practice the principles of employment equity?                                                                 587 non-null    object 
 64  Workplace. Topic:Does your workplace value the diversity of its employees?                                                                                     586 non-null    object 
 65  Workplace. Topic:Would you recommend the Institute as an employer to others?                                                                                   581 non-null    object 
 66  Gender. What is your Gender?                                                                                                                                   596 non-null    object 
 67  CurrentAge. Current Age                                                                                                                                        596 non-null    object 
 68  Employment Type. Employment Type                                                                                                                               596 non-null    object 
 69  Classification. Classification                                                                                                                                 596 non-null    object 
 70  LengthofServiceOverall. Overall Length of Service at Institute (in years)                                                                                      596 non-null    object 
 71  LengthofServiceCurrent. Length of Service at current workplace (in years)                                                                                      596 non-null    object 
dtypes: float64(2), object(70)
memory usage: 395.0+ KB
In [921]:
dete_survey.head()
Out[921]:
ID SeparationType Cease Date DETE Start Date Role Start Date Position Classification Region Business Unit Employment Status ... Kept informed Wellness programs Health & Safety Gender Age Aboriginal Torres Strait South Sea Disability NESB
0 1 Ill Health Retirement 08/2012 1984.0 2004.0 Public Servant A01-A04 Central Office Corporate Strategy and Peformance Permanent Full-time ... N N N Male 56-60 NaN NaN NaN NaN Yes
1 2 Voluntary Early Retirement (VER) 08/2012 NaN NaN Public Servant AO5-AO7 Central Office Corporate Strategy and Peformance Permanent Full-time ... N N N Male 56-60 NaN NaN NaN NaN NaN
2 3 Voluntary Early Retirement (VER) 05/2012 2011.0 2011.0 Schools Officer NaN Central Office Education Queensland Permanent Full-time ... N N N Male 61 or older NaN NaN NaN NaN NaN
3 4 Resignation-Other reasons 05/2012 2005.0 2006.0 Teacher Primary Central Queensland NaN Permanent Full-time ... A N A Female 36-40 NaN NaN NaN NaN NaN
4 5 Age Retirement 05/2012 1970.0 1989.0 Head of Curriculum/Head of Special Education NaN South East NaN Permanent Full-time ... N A M Female 61 or older NaN NaN NaN NaN NaN

5 rows × 56 columns

In [922]:
tafe_survey.head()
Out[922]:
Record ID Institute WorkArea CESSATION YEAR Reason for ceasing employment Contributing Factors. Career Move - Public Sector Contributing Factors. Career Move - Private Sector Contributing Factors. Career Move - Self-employment Contributing Factors. Ill Health Contributing Factors. Maternity/Family ... Workplace. Topic:Does your workplace promote a work culture free from all forms of unlawful discrimination? Workplace. Topic:Does your workplace promote and practice the principles of employment equity? Workplace. Topic:Does your workplace value the diversity of its employees? Workplace. Topic:Would you recommend the Institute as an employer to others? Gender. What is your Gender? CurrentAge. Current Age Employment Type. Employment Type Classification. Classification LengthofServiceOverall. Overall Length of Service at Institute (in years) LengthofServiceCurrent. Length of Service at current workplace (in years)
0 6.341330e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2010.0 Contract Expired NaN NaN NaN NaN NaN ... Yes Yes Yes Yes Female 26 30 Temporary Full-time Administration (AO) 1-2 1-2
1 6.341337e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2010.0 Retirement - - - - - ... Yes Yes Yes Yes NaN NaN NaN NaN NaN NaN
2 6.341388e+17 Mount Isa Institute of TAFE Delivery (teaching) 2010.0 Retirement - - - - - ... Yes Yes Yes Yes NaN NaN NaN NaN NaN NaN
3 6.341399e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... Yes Yes Yes Yes NaN NaN NaN NaN NaN NaN
4 6.341466e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - Career Move - Private Sector - - - ... Yes Yes Yes Yes Male 41 45 Permanent Full-time Teacher (including LVT) 3-4 3-4

5 rows × 72 columns

In [923]:
dete_survey.isnull()
Out[923]:
ID SeparationType Cease Date DETE Start Date Role Start Date Position Classification Region Business Unit Employment Status ... Kept informed Wellness programs Health & Safety Gender Age Aboriginal Torres Strait South Sea Disability NESB
0 False False False False False False False False False False ... False False False False False True True True True False
1 False False False True True False False False False False ... False False False False False True True True True True
2 False False False False False False True False False False ... False False False False False True True True True True
3 False False False False False False False False True False ... False False False False False True True True True True
4 False False False False False False True False True False ... False False False False False True True True True True
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
817 False False False False False False False False True False ... False False False False False True True True True True
818 False False False False False False False False True False ... False False False False False True True True True True
819 False False False False False False False False False False ... False False False False False True True True True True
820 False False False False False False False False True False ... False False False False False True True True True True
821 False False False True True False True False True True ... True True True True True True True True True True

822 rows × 56 columns

In [924]:
tafe_survey.isnull()
Out[924]:
Record ID Institute WorkArea CESSATION YEAR Reason for ceasing employment Contributing Factors. Career Move - Public Sector Contributing Factors. Career Move - Private Sector Contributing Factors. Career Move - Self-employment Contributing Factors. Ill Health Contributing Factors. Maternity/Family ... Workplace. Topic:Does your workplace promote a work culture free from all forms of unlawful discrimination? Workplace. Topic:Does your workplace promote and practice the principles of employment equity? Workplace. Topic:Does your workplace value the diversity of its employees? Workplace. Topic:Would you recommend the Institute as an employer to others? Gender. What is your Gender? CurrentAge. Current Age Employment Type. Employment Type Classification. Classification LengthofServiceOverall. Overall Length of Service at Institute (in years) LengthofServiceCurrent. Length of Service at current workplace (in years)
0 False False False False False True True True True True ... False False False False False False False False False False
1 False False False False False False False False False False ... False False False False True True True True True True
2 False False False False False False False False False False ... False False False False True True True True True True
3 False False False False False False False False False False ... False False False False True True True True True True
4 False False False False False False False False False False ... False False False False False False False False False False
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
697 False False False False False False False False False False ... False False False False False False False False False False
698 False False False False False False False False False False ... True True True True True True True True True True
699 False False False False False False False False False False ... False False False False False False False False False False
700 False False False False False True True True True True ... False False False False False False False False False False
701 False False False False False False False False False False ... False False False False False False False False False False

702 rows × 72 columns

From the DataFrame.info(), DataFrame.head() and DataFrame.isnull() methods the we will dediduce the following:

  • The dete_survey dataset has 822 rows and 56 columns with datatypes : Int64, float64, Object/Strings and Boolians, while the tafe_survey has 702 rows and 72 columns with float64 and Object/Strings datatypes.
  • Both dataframes contain many columns that we don't need to complete our analysis.
  • The dete_survey dataframe contains 'Not Stated' values that indicates values are missing, but they are not represented as NaN
  • Each dataframe contains many of the same columns, but the column names are different.
  • There are multiple columns/answers that indicate an employee resigned because they were dissatisfied.

2. Identifying Missing Values and Drop Unnecessary Columns

From the work in the previous screen, I will start by handling the firts two issues. The pd.read_csv funtion can be used to specify values that should be represented as NaN. I will use this function to fix the missing values first. Then, drop columns we do not need for the analysis.

In [925]:
# Dropping columns from the dete_survey dataframe that are not needed for the analysis
dete_survey_updated = dete_survey.drop(dete_survey.columns[28:49]
                                       , axis=1)
dete_survey_updated.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 822 entries, 0 to 821
Data columns (total 35 columns):
 #   Column                               Non-Null Count  Dtype  
---  ------                               --------------  -----  
 0   ID                                   822 non-null    int64  
 1   SeparationType                       822 non-null    object 
 2   Cease Date                           788 non-null    object 
 3   DETE Start Date                      749 non-null    float64
 4   Role Start Date                      724 non-null    float64
 5   Position                             817 non-null    object 
 6   Classification                       455 non-null    object 
 7   Region                               717 non-null    object 
 8   Business Unit                        126 non-null    object 
 9   Employment Status                    817 non-null    object 
 10  Career move to public sector         822 non-null    bool   
 11  Career move to private sector        822 non-null    bool   
 12  Interpersonal conflicts              822 non-null    bool   
 13  Job dissatisfaction                  822 non-null    bool   
 14  Dissatisfaction with the department  822 non-null    bool   
 15  Physical work environment            822 non-null    bool   
 16  Lack of recognition                  822 non-null    bool   
 17  Lack of job security                 822 non-null    bool   
 18  Work location                        822 non-null    bool   
 19  Employment conditions                822 non-null    bool   
 20  Maternity/family                     822 non-null    bool   
 21  Relocation                           822 non-null    bool   
 22  Study/Travel                         822 non-null    bool   
 23  Ill Health                           822 non-null    bool   
 24  Traumatic incident                   822 non-null    bool   
 25  Work life balance                    822 non-null    bool   
 26  Workload                             822 non-null    bool   
 27  None of the above                    822 non-null    bool   
 28  Gender                               798 non-null    object 
 29  Age                                  811 non-null    object 
 30  Aboriginal                           16 non-null     object 
 31  Torres Strait                        3 non-null      object 
 32  South Sea                            7 non-null      object 
 33  Disability                           23 non-null     object 
 34  NESB                                 32 non-null     object 
dtypes: bool(18), float64(2), int64(1), object(14)
memory usage: 123.7+ KB
In [926]:
# Dropping columns from the tafe_survey dataframe that are not needed for the analysis
tafe_survey_updated = tafe_survey.drop(tafe_survey.columns[17:66]
                                       , axis=1)
tafe_survey_updated.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 702 entries, 0 to 701
Data columns (total 23 columns):
 #   Column                                                                     Non-Null Count  Dtype  
---  ------                                                                     --------------  -----  
 0   Record ID                                                                  702 non-null    float64
 1   Institute                                                                  702 non-null    object 
 2   WorkArea                                                                   702 non-null    object 
 3   CESSATION YEAR                                                             695 non-null    float64
 4   Reason for ceasing employment                                              701 non-null    object 
 5   Contributing Factors. Career Move - Public Sector                          437 non-null    object 
 6   Contributing Factors. Career Move - Private Sector                         437 non-null    object 
 7   Contributing Factors. Career Move - Self-employment                        437 non-null    object 
 8   Contributing Factors. Ill Health                                           437 non-null    object 
 9   Contributing Factors. Maternity/Family                                     437 non-null    object 
 10  Contributing Factors. Dissatisfaction                                      437 non-null    object 
 11  Contributing Factors. Job Dissatisfaction                                  437 non-null    object 
 12  Contributing Factors. Interpersonal Conflict                               437 non-null    object 
 13  Contributing Factors. Study                                                437 non-null    object 
 14  Contributing Factors. Travel                                               437 non-null    object 
 15  Contributing Factors. Other                                                437 non-null    object 
 16  Contributing Factors. NONE                                                 437 non-null    object 
 17  Gender. What is your Gender?                                               596 non-null    object 
 18  CurrentAge. Current Age                                                    596 non-null    object 
 19  Employment Type. Employment Type                                           596 non-null    object 
 20  Classification. Classification                                             596 non-null    object 
 21  LengthofServiceOverall. Overall Length of Service at Institute (in years)  596 non-null    object 
 22  LengthofServiceCurrent. Length of Service at current workplace (in years)  596 non-null    object 
dtypes: float64(2), object(21)
memory usage: 126.3+ KB

3. Clean Column Names

Now, I will turn my attention to the column names. Each dataframe contains many of the same columns, but the column names are different. Below are some of the columns we'd like to use for our final analysis:

dete_survey tafe_survey Definition
ID Record ID An id used to identify the participant of the survey
SeparationType Reason for ceasing employment The reason why the participant's employment ended
Cease Date CESSATION YEAR The year or month the participant's employment ended
DETE Start Date The year the participant began employment with the DETE
LengthofServiceOverall. Overall Length of Service at Institute (in years) The length of the person's employment (in years)
Age CurrentAge. Current Age The age of the participant
Gender Gender. What is your Gender? The gender of the participant

Since I want to combine the dataset, then I have to standardize the column names. The DataFrame.columns attribute will be used along with vectorized string methods to update all of the columns at once.

In [927]:
# Renaming remaining columns in the dete_survey_updated dataframe.
dete_survey_updated.columns= dete_survey_updated.columns.str.lower().str.strip().str.replace(' ','_')
dete_survey_updated.columns
Out[927]:
Index(['id', 'separationtype', 'cease_date', 'dete_start_date',
       'role_start_date', 'position', 'classification', 'region',
       'business_unit', 'employment_status', 'career_move_to_public_sector',
       'career_move_to_private_sector', 'interpersonal_conflicts',
       'job_dissatisfaction', 'dissatisfaction_with_the_department',
       'physical_work_environment', 'lack_of_recognition',
       'lack_of_job_security', 'work_location', 'employment_conditions',
       'maternity/family', 'relocation', 'study/travel', 'ill_health',
       'traumatic_incident', 'work_life_balance', 'workload',
       'none_of_the_above', 'gender', 'age', 'aboriginal', 'torres_strait',
       'south_sea', 'disability', 'nesb'],
      dtype='object')
In [928]:
#Creatig a mapping dictionary
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'}
#Using the mapping dictionary to update the column names in the tafe_survey_updated dataframe
tafe_survey_updated = tafe_survey_updated.rename(mapping, axis=1)
In [929]:
dete_survey_updated.head()
Out[929]:
id separationtype cease_date dete_start_date role_start_date position classification region business_unit employment_status ... work_life_balance workload none_of_the_above gender age aboriginal torres_strait south_sea disability nesb
0 1 Ill Health Retirement 08/2012 1984.0 2004.0 Public Servant A01-A04 Central Office Corporate Strategy and Peformance Permanent Full-time ... False False True Male 56-60 NaN NaN NaN NaN Yes
1 2 Voluntary Early Retirement (VER) 08/2012 NaN NaN Public Servant AO5-AO7 Central Office Corporate Strategy and Peformance Permanent Full-time ... False False False Male 56-60 NaN NaN NaN NaN NaN
2 3 Voluntary Early Retirement (VER) 05/2012 2011.0 2011.0 Schools Officer NaN Central Office Education Queensland Permanent Full-time ... False False True Male 61 or older NaN NaN NaN NaN NaN
3 4 Resignation-Other reasons 05/2012 2005.0 2006.0 Teacher Primary Central Queensland NaN Permanent Full-time ... False False False Female 36-40 NaN NaN NaN NaN NaN
4 5 Age Retirement 05/2012 1970.0 1989.0 Head of Curriculum/Head of Special Education NaN South East NaN Permanent Full-time ... True False False Female 61 or older NaN NaN NaN NaN NaN

5 rows × 35 columns

In [930]:
tafe_survey_updated.head()
Out[930]:
id Institute WorkArea cease_date separationtype Contributing Factors. Career Move - Public Sector Contributing Factors. Career Move - Private Sector Contributing Factors. Career Move - Self-employment Contributing Factors. Ill Health Contributing Factors. Maternity/Family ... Contributing Factors. Study Contributing Factors. Travel Contributing Factors. Other Contributing Factors. NONE gender age employment_status position institute_service role_service
0 6.341330e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2010.0 Contract Expired NaN NaN NaN NaN NaN ... NaN NaN NaN NaN Female 26 30 Temporary Full-time Administration (AO) 1-2 1-2
1 6.341337e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2010.0 Retirement - - - - - ... - Travel - - NaN NaN NaN NaN NaN NaN
2 6.341388e+17 Mount Isa Institute of TAFE Delivery (teaching) 2010.0 Retirement - - - - - ... - - - NONE NaN NaN NaN NaN NaN NaN
3 6.341399e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - Travel - - NaN NaN NaN NaN NaN NaN
4 6.341466e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - - Male 41 45 Permanent Full-time Teacher (including LVT) 3-4 3-4

5 rows × 23 columns

  1. The columns in the dete_survey_updated dataframe were renamed as follow:
  • Using the following criteria to update the column names:
    • Make all the capitalization lowercase.
    • Remove any trailing whitespace from the end of the strings.
    • Replace spaces with underscores ('_').
  • As an example, Cease Date should be updated to cease_date.
  1. The Dataframe.rename() method was used to update the columns in the tafe_survey_updated by using a mapping dictionary with the following keys:
  • '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'

The columns to be used in our analysis were renamed for better understanding.

4. Filter the Data

In the last screen, I renamed the columns that will be used in the analysis. Next, I will remove more of the data we don't need.

Recall that the 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?

Looking at the unique values in the separationtype columns in each dataframe, you will see that each contains a couple of different separation types. For this project, I will only analyze survey respondents who resigned, so their separation type contains the string 'Resignation'.

The dete_survey_updated dataframe contains multiple separation types with the string 'Resignation':

  • Resignation-Other reasons
  • Resignation-Other employer
  • Resignation-Move overseas/interstate

All these variations have to be accounted for do that no data will be dropped unintetionally!

We will use the Series.value_counts() method to review the unique values in the separationtype column in both dete_survey_updated and tafe_survey_updated.

In [931]:
dete_survey_updated['separationtype'].value_counts()
Out[931]:
Age Retirement                          285
Resignation-Other reasons               150
Resignation-Other employer               91
Resignation-Move overseas/interstate     70
Voluntary Early Retirement (VER)         67
Ill Health Retirement                    61
Other                                    49
Contract Expired                         34
Termination                              15
Name: separationtype, dtype: int64

Resignation is the major reason people exit the company at the Department of Education, Training and Employment with a total of 311 employees. 150 employees resigned because of other reasons, 91 resigned because they got other employer and 70 resigned because they moved overseas or interstate.

In [932]:
tafe_survey_updated['separationtype'].value_counts()
Out[932]:
Resignation                 340
Contract Expired            127
Retrenchment/ Redundancy    104
Retirement                   82
Transfer                     25
Termination                  23
Name: separationtype, dtype: int64

Resignation is also the major reason people exit the company at the Technical and Further Education (TAFE) institute in Queensland, Australia with a total of 340 employees.

Before selecting only the data for survey respondents who have a Resignation separation types, all separation types containing the word 'Resignation' in the dete_survey_updated have to be updated to only the string 'Resignation'. e.g 'Resignation-Other reasons' becomes 'Resignation'.

In [933]:
dete_survey_updated['separationtype']= dete_survey_updated['separationtype'].str.split('-').str[0]

#check if the resignationtype column is updated correctly
dete_survey_updated['separationtype'].value_counts()
Out[933]:
Resignation                         311
Age Retirement                      285
Voluntary Early Retirement (VER)     67
Ill Health Retirement                61
Other                                49
Contract Expired                     34
Termination                          15
Name: separationtype, dtype: int64

Now, I will select only the data for survey who have Resignation separationtype in each dataframe.

In [934]:
dete_resignations=dete_survey_updated[dete_survey_updated['separationtype']=='Resignation'].copy()
tafe_resignations=tafe_survey_updated[tafe_survey_updated['separationtype']=='Resignation'].copy()

We have only selected the rows where employees have resigned because that is the only data relevant to answer the given questions.

5. Verify the Data

Now, before I start cleaning and manipulating the rest of the data, I will verify that the data doesn't contain any major inconsistencies (to the best of my knowledge).

In this step, I will focus on verifying that the years in the cease_date and dete_start_date columns make sense.

  • Since the cease_date is the last year of the person's employment and the dete_start_date is the person's first year of employment, it wouldn't make sense to have years after the current date.
  • Given that most people in this field start working in their 20s, it's also unlikely that the dete_start_date was before the year 1940.

If I have many years higher than the current date or lower than 1940, I wouldn't want to continue with my analysis, because it could mean there's something very wrong with the data. If there are a small amount of values that are unrealistically high or low, we can remove them.

below, I will check the years in each dataframe for logical consistencies

In [935]:
#1) Checking cease_date in dete_resignations
dete_resignations['cease_date'].value_counts()
Out[935]:
2012       126
2013        74
01/2014     22
12/2013     17
06/2013     14
09/2013     11
07/2013      9
11/2013      9
10/2013      6
08/2013      4
05/2013      2
05/2012      2
2010         1
07/2012      1
07/2006      1
09/2010      1
Name: cease_date, dtype: int64

I will use the vectorized method to extract the years and use the Series.astype() method to convert the type to float.

In [936]:
# 1) Converting cease_date in dete_resignations
dete_resignations['cease_date'] = dete_resignations['cease_date'].str.split('/').str[-1]
dete_resignations['cease_date'] = dete_resignations['cease_date'].astype("float")

# Confirming if the code did what I expected
dete_resignations['cease_date'].value_counts()
Out[936]:
2013.0    146
2012.0    129
2014.0     22
2010.0      2
2006.0      1
Name: cease_date, dtype: int64
In [937]:
#2 Checking dete_start_date in dete_resignations
dete_resignations['dete_start_date'].value_counts()
Out[937]:
2011.0    24
2008.0    22
2007.0    21
2012.0    21
2010.0    17
2005.0    15
2004.0    14
2009.0    13
2006.0    13
2013.0    10
2000.0     9
1999.0     8
1996.0     6
2002.0     6
1992.0     6
1998.0     6
2003.0     6
1994.0     6
1993.0     5
1990.0     5
1980.0     5
1997.0     5
1991.0     4
1989.0     4
1988.0     4
1995.0     4
2001.0     3
1985.0     3
1986.0     3
1983.0     2
1976.0     2
1974.0     2
1971.0     1
1972.0     1
1984.0     1
1982.0     1
1987.0     1
1975.0     1
1973.0     1
1977.0     1
1963.0     1
Name: dete_start_date, dtype: int64
In [938]:
#3 Checking cease_date in tafe_resignations
tafe_resignations['cease_date'].value_counts()
Out[938]:
2011.0    116
2012.0     94
2010.0     68
2013.0     55
2009.0      2
Name: cease_date, dtype: int64
In [939]:
# Checking for unique values
tafe_resignations['cease_date'].value_counts().sort_index(ascending=True)
Out[939]:
2009.0      2
2010.0     68
2011.0    116
2012.0     94
2013.0     55
Name: cease_date, dtype: int64
In [940]:
dete_dates = dete_resignations[['dete_start_date', 'cease_date']]

fig = px.box(dete_dates, y=dete_dates.columns, width=500, height=500, template='plotly_white')
fig.update_layout(title='DETE Resignations: Start Date and Cease Date')
fig.update_yaxes(dtick=5, color='gray', title='Year', showline=True, mirror=True)
fig.update_xaxes(title='', color='gray', showline=True, mirror=True)

fig.show()

From box plot above we observe that the majority of the employees who resigned joined the DETE between the late 1997 and 2010. Between year 2010 and 2014, a large proportion of these employees had resigned from the institution.

Looking at the years in each dataframe, it seems there are no logical inconsistencies.

6. Create a New Column

Now that I have verified the years in the dete_resignations dataframe, I will use them to create a new column. Recall that the 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?

In the Human Resources field, the length of time an employee spent in a workplace is referred to as their years of service.

Since the tafe_resignations dataframe already contains a "service" column, which we renamed to institute_service, then I have to create a corresponding institute_service column in dete_resignations in order to analyze both surveys together.

In [941]:
dete_resignations['institute_service']=dete_resignations['cease_date']-dete_resignations['dete_start_date']

#checking for values
dete_resignations['institute_service'].shape
Out[941]:
(311,)

The institute_service in the dete_resignations dataframe was created by subtracting the dete_start_date from the cease_date column. This is the column I will use to analyze survey respondents according to their length of employment.

7. Identify Dissatisfied Employees

Next, I will identify any employees who resigned because they were dissatisfied.

Below are the columns I will use to categorize employees as "dissatisfied" from each dataframe.

  1. tafe_survey_updated:

    • Contributing Factors. Dissatisfaction
    • Contributing Factors. Job Dissatisfaction
  2. 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 * workload

If the employee indicated any of the factors above caused them to resign, I will mark them as dissatisfied in a new column.

In [942]:
# Checking for Unique values
tafe_resignations['Contributing Factors. Dissatisfaction'].value_counts()
Out[942]:
-                                         277
Contributing Factors. Dissatisfaction      55
Name: Contributing Factors. Dissatisfaction, dtype: int64

Only 55 employees resigned because they were dissatisfied.

In [943]:
# Checking for Unique values
tafe_resignations['Contributing Factors. Job Dissatisfaction'].value_counts()
Out[943]:
-                      270
Job Dissatisfaction     62
Name: Contributing Factors. Job Dissatisfaction, dtype: int64

Only 62 employees resigned because they were dissatisfied with the job.

The function below will update values in 'Contributing Factors. Dissatisfaction' and 'Contributing Factors. Job Dissatisfaction' in the tafe_resignations dataframes so that each contain only True, False or NaN values.

In [944]:
def update_vals(x):
    if x == '-':
        return False
    elif pd.isnull(x):
        return np.nan
    else:
        return True
In [945]:
# Applying the function to update values in the tafe_resignation dataframe.
tafe_resignations['dissatisfied'] = tafe_resignations[['Contributing Factors. Dissatisfaction', 'Contributing Factors. Job Dissatisfaction']].applymap(update_vals).any(axis =1, skipna=False)

tafe_resignations_up = tafe_resignations.copy()
In [946]:
# Checking for unique vales
tafe_resignations_up['dissatisfied'].value_counts(dropna=False)
Out[946]:
False    241
True      91
NaN        8
Name: dissatisfied, dtype: int64
In [947]:
# Applying the function to update values in the dete_resignation dataframe.
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(axis=1, skipna=False)
dete_resignations_up = dete_resignations.copy()
In [948]:
# Checking for unique vales
dete_resignations_up['dissatisfied'].value_counts(dropna = False)
Out[948]:
False    162
True     149
Name: dissatisfied, dtype: int64

8. Combining the Data

Now, I am finally ready to combine the datasets! The end goal is to aggregate the data according to the institute_service column.

First, I will add a column to each dataframe that will allow me to easily distinguish between the two.

In [949]:
dete_resignations_up['institution']='DETE'
tafe_resignations_up['institution']='TAFE'
In [950]:
# Combining the dataframes
combined = pd.concat([dete_resignations_up, tafe_resignations_up], ignore_index=True)

# Verifying the number of non null values in each column
combined.notnull().sum().sort_values()
Out[950]:
torres_strait                                            0
south_sea                                                3
aboriginal                                               7
disability                                               8
nesb                                                     9
business_unit                                           32
classification                                         161
region                                                 265
role_start_date                                        271
dete_start_date                                        283
role_service                                           290
none_of_the_above                                      311
work_life_balance                                      311
traumatic_incident                                     311
ill_health                                             311
study/travel                                           311
relocation                                             311
maternity/family                                       311
employment_conditions                                  311
workload                                               311
lack_of_job_security                                   311
career_move_to_public_sector                           311
career_move_to_private_sector                          311
interpersonal_conflicts                                311
work_location                                          311
dissatisfaction_with_the_department                    311
physical_work_environment                              311
lack_of_recognition                                    311
job_dissatisfaction                                    311
Contributing Factors. Job Dissatisfaction              332
Contributing Factors. Travel                           332
Contributing Factors. Maternity/Family                 332
Contributing Factors. Ill Health                       332
Contributing Factors. Career Move - Self-employment    332
Contributing Factors. Career Move - Private Sector     332
Contributing Factors. Career Move - Public Sector      332
Contributing Factors. Dissatisfaction                  332
Contributing Factors. Other                            332
Contributing Factors. Interpersonal Conflict           332
Contributing Factors. NONE                             332
Contributing Factors. Study                            332
Institute                                              340
WorkArea                                               340
institute_service                                      563
gender                                                 592
age                                                    596
employment_status                                      597
position                                               598
cease_date                                             635
dissatisfied                                           643
separationtype                                         651
institution                                            651
id                                                     651
dtype: int64

Below I will drop all columns with less than 500 non null values.

In [951]:
combined_updated = combined.dropna(thresh = 500, axis = 1).copy()
combined_updated.notnull().sum().sort_values()
Out[951]:
institute_service    563
gender               592
age                  596
employment_status    597
position             598
cease_date           635
dissatisfied         643
id                   651
separationtype       651
institution          651
dtype: int64

Now I am are only left with 9 columns which are relevant for the analysis.

9. Cleaning the Services Column

Now that I have combined the dataframes, I am almost at a place where I can perform some kind of analysis! First, though, I have to clean up the institute_service column. This column is tricky to clean because it currently contains values in a couple different forms.

To analyze the data, I will convert these numbers into categories. I will base my analysis on this article, which makes the argument that understanding employee's needs according to career stage instead of age is more effective.

I will use the slightly modified definitions below:

  • New: Less than 3 years at a company
  • Experienced: 3-6 years at a company
  • Established: 7-10 years at a company
  • Veteran: 11 or more years at a company

I will categorize the values in the institute_service column using the definitions above.

In [952]:
combined_updated['institute_service'].value_counts()
Out[952]:
Less than 1 year      73
1-2                   64
3-4                   63
5-6                   33
11-20                 26
5.0                   23
1.0                   22
7-10                  21
3.0                   20
0.0                   20
6.0                   17
4.0                   16
2.0                   14
9.0                   14
7.0                   13
More than 20 years    10
8.0                    8
13.0                   8
15.0                   7
20.0                   7
17.0                   6
22.0                   6
12.0                   6
14.0                   6
10.0                   6
16.0                   5
18.0                   5
23.0                   4
11.0                   4
24.0                   4
19.0                   3
21.0                   3
39.0                   3
32.0                   3
26.0                   2
28.0                   2
30.0                   2
25.0                   2
36.0                   2
27.0                   1
29.0                   1
31.0                   1
33.0                   1
34.0                   1
41.0                   1
35.0                   1
42.0                   1
49.0                   1
38.0                   1
Name: institute_service, dtype: int64

Below I will extract the years of service for each value in the institution_service column and convert the type to float.

In [953]:
combined_updated['institute_service'] = combined_updated['institute_service'].astype('str').str.extract(r'(\d+)')
combined_updated['institute_service'] = combined_updated['institute_service'].astype('float')
In [954]:
#Checking for unique values
combined_updated['institute_service'].value_counts()
Out[954]:
1.0     159
3.0      83
5.0      56
7.0      34
11.0     30
0.0      20
20.0     17
6.0      17
4.0      16
9.0      14
2.0      14
13.0      8
8.0       8
15.0      7
17.0      6
10.0      6
12.0      6
14.0      6
22.0      6
16.0      5
18.0      5
24.0      4
23.0      4
39.0      3
19.0      3
21.0      3
32.0      3
28.0      2
36.0      2
25.0      2
30.0      2
26.0      2
29.0      1
38.0      1
42.0      1
27.0      1
41.0      1
35.0      1
49.0      1
34.0      1
33.0      1
31.0      1
Name: institute_service, dtype: int64

Now, I will create a function that maps each year value to one of the career stages above.

In [955]:
def categorize(val):
    if pd.isnull(val):
        return np.nan
    elif val<3:
        return 'New'
    elif 3<=val<=6:
        return 'Experienced'
    elif 7<=val<=10:
        return 'Established'
    else:
        return 'Veteran'
In [956]:
# Applying the function to map each year value to one of the career stages above
combined_updated['service_cat'] = combined_updated['institute_service'].apply(categorize)

# Confirming the function did what I expected.
combined_updated['service_cat'].value_counts(dropna=False)
Out[956]:
New            193
Experienced    172
Veteran        136
NaN             88
Established     62
Name: service_cat, dtype: int64

From the code above I created a service_cat column, that categorizes employees according to the number of years they spend at the componey: One can deduce that there are 193 New employees, 172 experienced employees, 62 established employees and 136 veteran employees. There are 88 missing values.

10. Performing the Initial Analysis

Now, I will finally do the first piece of analysis! I will fill in missing values in the dissatisfied column and then aggregate the data to get started, but there are still additional missing values left to deal with. This is meant to be an initial introduction to the analysis, not the final analysis.

I will use the Series.value_counts() method to confirm if the number of True and False in the dissatisfied column. I will set the dropna parameter to False to also confirm the number of missing values.

In [957]:
combined_updated['dissatisfied'].value_counts(dropna=False)
Out[957]:
False    403
True     240
NaN        8
Name: dissatisfied, dtype: int64
In [958]:
# Replacing the missing values with the most frequentely occuring value in the column. 
# in this case, the most frequent value is False
combined_updated['dissatisfied'] = combined_updated['dissatisfied'].fillna(False)

I use the DataFrame.pivot_table() to calculate the percentage of dissatisfied employees in each service_cat group.

In [959]:
dissatisfied_resignations = combined_updated.pivot_table(values='dissatisfied', index = 'service_cat')
dissatisfied_resignations
Out[959]:
dissatisfied
service_cat
Established 0.516129
Experienced 0.343023
New 0.295337
Veteran 0.485294

Doing a the analysis between service category and dissatisfied employees, I observe that 51.61% of the employees who resigned from the DETE and TAFE were established (worked for 7-10 years), 34.3% were experienced (worked for 3-7 years), 29.53% were newbies (worked for less than 3 years) and 48.53% were veterans (worked for 11 years or more). One can infer that people that work in the DETE and TAFE institute become dissatisfied with their jobs due to some challenges they are faced.

In [960]:
# Calculating the percentage of employees who resigned due to dissatisfaction in each category
dissatisfied_resignations['dissatisfied']=dissatisfied_resignations['dissatisfied']*100
In [961]:
# Plotting the results
dissatisfied_resignations.plot(kind='bar', rot=45)
plt.ylabel('percentage (%)')
plt.title('Percentage of dissatisfied employees')
plt.show()

From the bar graph above, I can dedude that established employees are more likely to resign due to some dissatisfications. New employees are least likely to resign.

10. Cleaning the Age Column

To turn back to our initial question: How many people in each age group resgined due to some kind of dissatisfaction? Instead of analyzing the survey results together, analyze each survey separately.

Did more employees in the DETE survey or TAFE survey end their employment because they were dissatisfied in some way?

In [962]:
# Checking for unique values in the age column
combined_updated['age'].value_counts().sort_index()
Out[962]:
20 or younger    10
21  25           33
21-25            29
26  30           32
26-30            35
31  35           32
31-35            29
36  40           32
36-40            41
41  45           45
41-45            48
46  50           39
46-50            42
51-55            71
56 or older      29
56-60            26
61 or older      23
Name: age, dtype: int64
In [963]:
# Extracting the dataset for employees who only indicated True in the dissatisfied column
combined_dissatisfied = combined_updated.loc[combined_updated['dissatisfied']==True,]
In [964]:
# cleaning the age column
combined_updated['age'] = combined_updated['age'].str.replace("  ","-")
combined_updated['age'].value_counts().sort_index()
Out[964]:
20 or younger    10
21-25            62
26-30            67
31-35            61
36-40            73
41-45            93
46-50            81
51-55            71
56 or older      29
56-60            26
61 or older      23
Name: age, dtype: int64

Looking at the age column, I notice some incinsisitencies in some age categories. 56 or older, 56-60 and 61 or older seems odd, therefore need cleaning. We will use the function below to haddle that issue.

In [965]:
def clean_age(element):
    if element == "56-60" or element == "61 or older":
        return "56 or older" 
    else:
        return element

# Applying the function to clean the age column.
combined_updated['age'] = combined_updated['age'].map(clean_age)
In [966]:
# Checking for unique values
combined_updated['age'].value_counts(dropna=False).sort_index()
Out[966]:
20 or younger    10
21-25            62
26-30            67
31-35            61
36-40            73
41-45            93
46-50            81
51-55            71
56 or older      78
NaN              55
Name: age, dtype: int64

Since we have cleaned up the age, now we can see if there is a relationship between age and employees resigning due to some kind of dissatisfication. I will first filter the dataset to only employees who indicated True in the dissatisfied column

In [967]:
combined_dissatisfied = combined_updated[combined_updated['dissatisfied']==True]

I will create a dataframe with age groups counts, number of dissatisfied people per age group and their respective percentages.

In [968]:
Age_DF = combined_updated['age'].value_counts().sort_index().to_frame(name='Total')
Age_DF['Dissatisfied'] = combined_dissatisfied ['age'].value_counts().sort_index()
Age_DF['Dissatisfied (%)'] = round(Age_DF['Dissatisfied'] / Age_DF['Total'],4)*100
Age_DF.index.name = 'Age'                                                           
Age_DF
Out[968]:
Total Dissatisfied Dissatisfied (%)
Age
20 or younger 10 2 20.00
21-25 62 19 30.65
26-30 67 28 41.79
31-35 61 23 37.70
36-40 73 25 34.25
41-45 93 35 37.63
46-50 81 31 38.27
51-55 71 30 42.25
56 or older 78 33 42.31
In [969]:
labels = ['20 or younger','21-25','26-30','31-35', '36-40','41-45','46-50','51-55','56 or older' ]


sizes = [15, 30, 45, 10]
explode = (0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1)  # only "explode" the 2nd slice (i.e. 'Hogs')

fig1, ax1 = plt.subplots()
ax1.pie(Age_DF['Dissatisfied (%)'], explode=explode, labels=labels, autopct='%1.1f%%',
        shadow=True, startangle=90)
ax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.
ax1.legend(labels, title="Age Group",
          loc="center left",
          bbox_to_anchor=(1, 0, 0.5, 1))
ax1.set_title("Dissatisfaction Distribution by Age\n", weight ='bold')
plt.show()

From the dissatisfaction distribution by age pie chart, one can deduce that older employees (51-55 years and 56 years or older) are the most likely to resign due to dissatisfications (42.25% and 42.31% respectively) than younger employees, with the exception of 26-30 year olds.

In [ ]: