Clean and Analyze Employee Exit Surveys[Wole Moses]

1. Introduction

This project aims to clean and analyze employee exit surveys from department of Education , Training(DETE) and Employment and Technical & Further Education (TAFE) Institute in Queensland , Australia. Both data sets can be found through following links: https://data.gov.au/dataset/ds-qld-fe96ff30-d157-4a81-851d-215f2a0fe26d/details?q = dete exit survey, and https://data.gov.au/dataset/ds-qld-89970a3b-182b-41ea-aea2-6f9f17b5907e/details?q= fate exit. It's project strategy is to know if employees exit job due to dissatisfaction. Thus, I will start by identifying and cleaning our datasets, drop data not needed for analysis, verify quality of data, create new columns and perform initial anaysis.

In [1]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
dete_survey = pd.read_csv('dete_survey.csv')
tafe_survey = pd.read_csv('tafe_survey.csv')
In [2]:
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                           822 non-null    object
 3   DETE Start Date                      822 non-null    object
 4   Role Start Date                      822 non-null    object
 5   Position                             817 non-null    object
 6   Classification                       455 non-null    object
 7   Region                               822 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), int64(1), object(37)
memory usage: 258.6+ KB
In [3]:
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 [4]:
 dete_survey.head(10)
Out[4]:
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 2004 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 Not Stated Not Stated 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 2011 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 2006 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 1989 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 6 Resignation-Other reasons 05/2012 1994 1997 Guidance Officer NaN Central Office Education Queensland Permanent Full-time ... D D NaN Female 41-45 NaN NaN NaN NaN NaN
6 7 Age Retirement 05/2012 1972 2007 Teacher Secondary Darling Downs South West NaN Permanent Part-time ... D D SD Female 56-60 NaN NaN NaN NaN NaN
7 8 Age Retirement 05/2012 1988 1990 Teacher Aide NaN North Coast NaN Permanent Part-time ... SA NaN SA Female 61 or older NaN NaN NaN NaN NaN
8 9 Resignation-Other reasons 07/2012 2009 2009 Teacher Secondary North Queensland NaN Permanent Full-time ... A D N Female 31-35 NaN NaN NaN NaN NaN
9 10 Resignation-Other employer 2012 1997 2008 Teacher Aide NaN Not Stated NaN Permanent Part-time ... SD SD SD Female 46-50 NaN NaN NaN NaN NaN

10 rows × 56 columns

In [5]:
tafe_survey.head(5)
Out[5]:
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 [6]:
dete_survey.isnull().sum()
Out[6]:
ID                                       0
SeparationType                           0
Cease Date                               0
DETE Start Date                          0
Role Start Date                          0
Position                                 5
Classification                         367
Region                                   0
Business Unit                          696
Employment Status                        5
Career move to public sector             0
Career move to private sector            0
Interpersonal conflicts                  0
Job dissatisfaction                      0
Dissatisfaction with the department      0
Physical work environment                0
Lack of recognition                      0
Lack of job security                     0
Work location                            0
Employment conditions                    0
Maternity/family                         0
Relocation                               0
Study/Travel                             0
Ill Health                               0
Traumatic incident                       0
Work life balance                        0
Workload                                 0
None of the above                        0
Professional Development                14
Opportunities for promotion             87
Staff morale                             6
Workplace issue                         34
Physical environment                     5
Worklife balance                         7
Stress and pressure support             12
Performance of supervisor                9
Peer support                            10
Initiative                               9
Skills                                  11
Coach                                   55
Career Aspirations                      76
Feedback                                30
Further PD                              54
Communication                            8
My say                                  10
Information                              6
Kept informed                            9
Wellness programs                       56
Health & Safety                         29
Gender                                  24
Age                                     11
Aboriginal                             806
Torres Strait                          819
South Sea                              815
Disability                             799
NESB                                   790
dtype: int64
In [7]:
tafe_survey.isnull().sum()
Out[7]:
Record ID                                                                      0
Institute                                                                      0
WorkArea                                                                       0
CESSATION YEAR                                                                 7
Reason for ceasing employment                                                  1
                                                                            ... 
CurrentAge. Current Age                                                      106
Employment Type. Employment Type                                             106
Classification. Classification                                               106
LengthofServiceOverall. Overall Length of Service at Institute (in years)    106
LengthofServiceCurrent. Length of Service at current workplace (in years)    106
Length: 72, dtype: int64

2. Identify Missing Values and Drop Unnecessary Columns

In [8]:
dete_survey = pd.read_csv('dete_survey.csv', na_values = 'Not Stated')# re-read to change Not Stated to NaN
dete_survey.head(10)
Out[8]:
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 6 Resignation-Other reasons 05/2012 1994.0 1997.0 Guidance Officer NaN Central Office Education Queensland Permanent Full-time ... D D NaN Female 41-45 NaN NaN NaN NaN NaN
6 7 Age Retirement 05/2012 1972.0 2007.0 Teacher Secondary Darling Downs South West NaN Permanent Part-time ... D D SD Female 56-60 NaN NaN NaN NaN NaN
7 8 Age Retirement 05/2012 1988.0 1990.0 Teacher Aide NaN North Coast NaN Permanent Part-time ... SA NaN SA Female 61 or older NaN NaN NaN NaN NaN
8 9 Resignation-Other reasons 07/2012 2009.0 2009.0 Teacher Secondary North Queensland NaN Permanent Full-time ... A D N Female 31-35 NaN NaN NaN NaN NaN
9 10 Resignation-Other employer 2012 1997.0 2008.0 Teacher Aide NaN NaN NaN Permanent Part-time ... SD SD SD Female 46-50 NaN NaN NaN NaN NaN

10 rows × 56 columns

In [9]:
dete_survey_updated = dete_survey.drop(dete_survey.columns[28:49], axis  = 1) # To drop any column not important for analysis
dete_survey_updated.head(5)
print(dete_survey_updated.columns) # To check columns are droped
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 [10]:
tafe_survey_updated = tafe_survey.drop(tafe_survey.columns[17:66], axis  = 1)
tafe_survey_updated.head(5)
print(tafe_survey_updated.columns) # To check columns are droped
Index(['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',
       'Contributing Factors. Dissatisfaction',
       'Contributing Factors. Job Dissatisfaction',
       'Contributing Factors. Interpersonal Conflict',
       'Contributing Factors. Study', 'Contributing Factors. Travel',
       'Contributing Factors. Other', 'Contributing Factors. NONE',
       '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)'],
      dtype='object')

2.1 Breif explanation

Before I proceed with identification of missing values, in region column, there are missing values indicated as 'Not Stated', which are not represented as NaN, because pd.read_csv() function only specifies missing values as NaN. There fore, I re-read the dete_survey data again by setting na_values parameter to 'Not Stated'. Besides that, some of the columns are not necessary for my analysis, therefore I used DataFrame.drop() method to drop some columns from both data sets

3. Clean Column Names

In order to update, Dataframe.columns attribute along with vectorized string methods was used on dete_survey data. While, DataFrame.rename() method with a dictionary as argument to the columns parameters is used on tafe_survey because it works well with any number of columns. Its important to rename columns for both data sets to look uniform because it will be easier to combined the data sets for further analysis later.

In [11]:
# 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
Out[11]:
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 [12]:
# Rename column, show column names for tafe_survey
columns = {'Record ID':'id','CESSATION YEAR':'cease_date',
                                        'Reason for ceasing employment':'separationtype',
                                         'Gender. What is your Gender':'gender', 'CurrentAge. Current Age':'age',
                                           'Employment Type.Employment Type':'employment_status','Classification. Classification':'position',
                                             'LengthofServiceOverall. Overall Length of Service at Institute (in years)':'institute_service',
                                               'LengthofServiceCurrent. Length of Service at current workplace (in years)':'role_service'
                                                 }
tafe_survey_updated = tafe_survey_updated.rename(columns, axis = 1)
tafe_survey_updated.columns
Out[12]:
Index(['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. Dissatisfaction',
       'Contributing Factors. Job Dissatisfaction',
       'Contributing Factors. Interpersonal Conflict',
       'Contributing Factors. Study', 'Contributing Factors. Travel',
       'Contributing Factors. Other', 'Contributing Factors. NONE',
       'Gender. What is your Gender?', 'age',
       'Employment Type. Employment Type', 'position', 'institute_service',
       'role_service'],
      dtype='object')

4. Filter the Data

In order to filter the data set, series.value_counts() method is used to accounts for columns of employees who were resigning . Many reasons cause employees to exit work but in this project, I would like to focus on those employees who resigned and those who quit for other reasons e.g. age versus role services(number of years in job before quiting job)

In [13]:
#Check the unique values for the separationtype column for tafe_survey_updated
tafe_survey_updated['separationtype'].value_counts()
Out[13]:
Resignation                 340
Contract Expired            127
Retrenchment/ Redundancy    104
Retirement                   82
Transfer                     25
Termination                  23
Name: separationtype, dtype: int64
In [14]:
# Check the unique values for the separationtype column for dete_survey_updated
dete_survey_updated['separationtype'].value_counts()
Out[14]:
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
In [15]:
# 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()
Out[15]:
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
In [16]:
# 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()
In [17]:
tafe_survey_updated['role_service'].value_counts() # To check the number of years of service by employees
Out[17]:
Less than 1 year      177
1-2                   113
3-4                    86
11-20                  82
More than 20 years     54
7-10                   44
5-6                    40
Name: role_service, dtype: int64

5. Verify the Data

Before cleaning and Manipulating our data sets, i will try to verify if there is any inconsistancies in the columns. In this mission, i would focus on verifying the years in cease_date and dete_start_date columns. Ideally, most employees start working at their 20's, therefore it won't make sense to have dete_start_date before 1940.

In [18]:
# Check the unique values
dete_resignations['cease_date'].value_counts()
Out[18]:
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
07/2006      1
2010         1
07/2012      1
09/2010      1
Name: cease_date, dtype: int64
In [19]:
#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()
Out[19]:
2013.0    146
2012.0    129
2014.0     22
2010.0      2
2006.0      1
Name: cease_date, dtype: int64
In [20]:
tafe_resignations['cease_date'].value_counts()
Out[20]:
2011.0    116
2012.0     94
2010.0     68
2013.0     55
2009.0      2
Name: cease_date, dtype: int64
In [21]:
# Check the unique values and look for outliers
dete_resignations['dete_start_date'].value_counts().sort_values(ascending = True)
Out[21]:
1963.0     1
1971.0     1
1972.0     1
1984.0     1
1977.0     1
1987.0     1
1975.0     1
1973.0     1
1982.0     1
1974.0     2
1983.0     2
1976.0     2
1986.0     3
1985.0     3
2001.0     3
1995.0     4
1988.0     4
1989.0     4
1991.0     4
1997.0     5
1980.0     5
1993.0     5
1990.0     5
1994.0     6
2003.0     6
1998.0     6
1992.0     6
2002.0     6
1996.0     6
1999.0     8
2000.0     9
2013.0    10
2009.0    13
2006.0    13
2004.0    14
2005.0    15
2010.0    17
2012.0    21
2007.0    21
2008.0    22
2011.0    24
Name: dete_start_date, dtype: int64

5.1 Explanation on my findings

Based on my findings, there aren't any inconsistencies in cease_date and dete_start_date columns.

6. Create a New Column

Since there is already an institute_service in tafe_resignations dataframe, I wil create a similar column in dete_resignations by subtracting dete_start_date from cease_date as seen below. I have made these changes to maneveur a way to have common columns names to join them later for further anaylsis

In [22]:
# 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']

# display results
dete_resignations['institute_service'].head()
Out[22]:
3      7.0
5     18.0
8      3.0
9     15.0
11     3.0
Name: institute_service, dtype: float64

7. Identify Dissatisfied Employees

In order to idnetify dissatisfied employees, Contributing Factors. Dissatisfaction,Contributing Factors. Job Dissatisfaction columns in tafe_survey_updated data set is used. While in 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 columns are used to identify dissatistified employees.

In [23]:
# checking for employees who resigned due dissatifaction 
tafe_resignations['Contributing Factors. Dissatisfaction'].value_counts()
Out[23]:
-                                         277
Contributing Factors. Dissatisfaction      55
Name: Contributing Factors. Dissatisfaction, dtype: int64
In [24]:
tafe_resignations['Contributing Factors. Job Dissatisfaction'].value_counts()
Out[24]:
-                      270
Job Dissatisfaction     62
Name: Contributing Factors. Job Dissatisfaction, dtype: int64
In [25]:
# function to check for NaN and '_' characters in tafe_resignations dataframe
def update_vals(value):
    if value == '-':
        return False
    elif pd.isnull(value):
        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)
Out[25]:
False    241
True      91
NaN        8
Name: dissatisfied, dtype: int64
In [26]:
tafe_resignations.applymap(update_vals)
Out[26]:
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. Travel Contributing Factors. Other Contributing Factors. NONE Gender. What is your Gender? age Employment Type. Employment Type position institute_service role_service dissatisfied
3 True True True True True False False False False False ... True False False NaN NaN NaN NaN NaN NaN True
4 True True True True True False True False False False ... False False False True True True True True True True
5 True True True True True False False False False False ... False True False True True True True True True True
6 True True True True True False True False False True ... False True False True True True True True True True
7 True True True True True False False False False False ... False True False True True True True True True True
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
696 True True True True True False True False False False ... False False False True True True True True True True
697 True True True True True True False False False False ... False False False True True True True True True True
698 True True True True True True False False False False ... False False False NaN NaN NaN NaN NaN NaN True
699 True True True True True False False False False False ... False True False True True True True True True True
701 True True True True True False False True False False ... True False False True True True True True True True

340 rows × 24 columns

In [27]:
# checking for updates in dete_resignations
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() # To create a copy of data and avoid SettingWithCopy warning

# Check the unique values after the update in dete_resignations
dete_resignations_up['dissatisfied'].value_counts(dropna=False)
Out[27]:
False    162
True     149
Name: dissatisfied, dtype: int64
In [28]:
dete_resignations.applymap(update_vals)
Out[28]:
id separationtype cease_date dete_start_date role_start_date position classification region business_unit employment_status ... none_of_the_above gender age aboriginal torres_strait south_sea disability nesb institute_service dissatisfied
3 True True True True True True True True NaN True ... True True True NaN NaN NaN NaN NaN True True
5 True True True True True True NaN True True True ... True True True NaN NaN NaN NaN NaN True True
8 True True True True True True True True NaN True ... True True True NaN NaN NaN NaN NaN True True
9 True True True True True True NaN NaN NaN True ... True True True NaN NaN NaN NaN NaN True True
11 True True True True True True True True NaN True ... True True True NaN NaN NaN NaN NaN True True
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
808 True True True True True True NaN NaN NaN True ... True True True NaN NaN NaN NaN NaN True True
815 True True True True True True True True NaN True ... True True True NaN NaN NaN NaN NaN True True
816 True True True True True True True True NaN True ... True True True NaN NaN NaN NaN NaN True True
819 True True True True True True True True True True ... True True True NaN NaN NaN NaN NaN True True
821 True True True NaN NaN True NaN True NaN NaN ... True NaN NaN NaN NaN NaN NaN NaN NaN True

311 rows × 37 columns

7.1 Explanation about changes made to Dissatisfied Employees

First, I used df.valuecounts() to check for number for employees who resigned due to dissatisfaction. Then a function is created to check for NaN, '' characters in those columns contributing to dissatisfaction of employees. Df.any() method is use to create columns in both tafe_resignations and dete_resignations dataframe. Finally, Df.copy is used to create a copy of the results and avoided the settingWithCopy Wanrning.

8. Combine the Data

In [29]:
# To add column to each dataframe
dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'
In [30]:
# To combine columns using Concatenate dataframes horizontally (axis=1)
combined = pd.concat([dete_resignations_up, tafe_resignations_up],ignore_index = True)

#Verify the number of non null values in each column
combined.notnull().sum().sort_values()
Out[30]:
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
Employment Type. Employment Type                       290
role_service                                           290
Gender. What is your Gender?                           290
gender                                                 302
employment_status                                      307
workload                                               311
work_life_balance                                      311
traumatic_incident                                     311
ill_health                                             311
study/travel                                           311
relocation                                             311
none_of_the_above                                      311
employment_conditions                                  311
work_location                                          311
lack_of_job_security                                   311
lack_of_recognition                                    311
physical_work_environment                              311
dissatisfaction_with_the_department                    311
job_dissatisfaction                                    311
interpersonal_conflicts                                311
career_move_to_private_sector                          311
career_move_to_public_sector                           311
maternity/family                                       311
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. Study                            332
Contributing Factors. Travel                           332
Contributing Factors. Ill Health                       332
Contributing Factors. NONE                             332
Contributing Factors. Job Dissatisfaction              332
Contributing Factors. Maternity/Family                 332
Institute                                              340
WorkArea                                               340
institute_service                                      563
age                                                    596
position                                               598
cease_date                                             635
dissatisfied                                           643
institute                                              651
separationtype                                         651
id                                                     651
dtype: int64
In [31]:
combined_updated = combined.dropna(thresh=500, axis = 1).copy()

8.1 Brief Explanation on Data Combination

Before combining the two columns (dete_resignations_up, tafe_resignations_up), I added column name(institute) for both dataframe. This easily allowed me to distinguish between the two dataframes. Then, I combined these columns using Concatenate dataframes horizontally (axis=1) and Verified the number of non null values in each column.Finally, Dataframe.dropna() method is used to drop any columns with less that 500 non null values as thresh parameter.

9. Clean the Service column

In [32]:
# Extracting years of service  from institute_service column
combined_updated['institute_service'].value_counts(dropna = False) # To check the institute_service groups per service role in job
Out[32]:
NaN                   88
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
0.0                   20
3.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
10.0                   6
12.0                   6
14.0                   6
22.0                   6
17.0                   6
18.0                   5
16.0                   5
11.0                   4
23.0                   4
24.0                   4
19.0                   3
32.0                   3
21.0                   3
39.0                   3
30.0                   2
25.0                   2
26.0                   2
28.0                   2
36.0                   2
38.0                   1
49.0                   1
42.0                   1
41.0                   1
29.0                   1
35.0                   1
34.0                   1
33.0                   1
27.0                   1
31.0                   1
Name: institute_service, dtype: int64
In [33]:
# Extract the years of service and convert them to float
combined_updated['institute_service'] = combined_updated['institute_service'].astype('str').str.extract(r'(\d+)')
combined_updated['institute_service'] = combined_updated['institute_service'].astype(float)

combined_updated['institute_service'].value_counts()
Out[33]:
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
In [34]:
def career_vals(x):
    if x >= 11:
        return 'Veteran'
    elif 7 <= x <= 10:
        return 'Establish'
    elif 3 <= x <= 6:
        return 'Experience'
    elif pd.isnull(x):
        return np.nan
    else:
        return ' New'
    
combined_updated['service_cat'] = combined_updated['institute_service'].apply(career_vals)


# Check the unique values after the updates
combined_updated['service_cat'].value_counts()
Out[34]:
 New          193
Experience    172
Veteran       136
Establish      62
Name: service_cat, dtype: int64

10. Perform Initial Analysis

In [35]:
# To confirm that the number of True and False in dissatisfied column
combined_updated['dissatisfied'].value_counts(dropna = False)
Out[35]:
False    403
True     240
NaN        8
Name: dissatisfied, dtype: int64
In [36]:
#To replace missing values in dissatisfied column with value that occur more frequently in the column
combined_updated['dissatisfied'] = combined_updated['dissatisfied'].fillna(False)
In [37]:
dis_cat = combined_updated.pivot_table(index='service_cat', values='dissatisfied')
In [38]:
%matplotlib inline
dis_cat.plot(kind = 'bar', rot = 30)
Out[38]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f0f19ce3a90>

10.1 Brief Explanations

Based on inital analysis of bar plot, most employees who resigned because they were dissatisfied were new to the company (193 employee) and experience (172 employees). while veterans and Establish employees were the least to resign due to job dissatisfactions. However, vetarans and establish employee are likely to exit job due retirement

Next Steps

11. Clean Age Column

In [39]:
# To check tafe_survey age group
tafe_survey_updated['age'].value_counts(dropna=False) # To check the age groups per service role in job
Out[39]:
56 or older      162
NaN              106
51-55             82
41  45            80
46  50            59
31  35            52
36  40            51
26  30            50
21  25            44
20 or younger     16
Name: age, dtype: int64
In [40]:
# Check dete_survey age group
dete_survey_updated['age'].value_counts(dropna=False)
Out[40]:
61 or older      222
56-60            174
51-55            103
46-50             63
41-45             61
26-30             57
36-40             51
21-25             40
31-35             39
NaN               11
20 or younger      1
Name: age, dtype: int64
In [41]:
# Extract the age of employee and convert them to float for dete_survey
dete_survey_updated['age'] = dete_survey_updated['age'].astype('str').str.extract(r'(\d+)')
dete_survey_updated['age'] = dete_survey_updated['age'].astype(float)

dete_survey_updated['age'].value_counts()
Out[41]:
61.0    222
56.0    174
51.0    103
46.0     63
41.0     61
26.0     57
36.0     51
21.0     40
31.0     39
20.0      1
Name: age, dtype: int64
In [42]:
# Extract the age of employee and convert them to float for tafe_survey group
tafe_survey_updated['age'] = tafe_survey_updated['age'].astype('str').str.extract(r'(\d+)')
tafe_survey_updated['age'] = tafe_survey_updated['age'].astype(float)

tafe_survey_updated['age'].value_counts()
Out[42]:
56.0    162
51.0     82
41.0     80
46.0     59
31.0     52
36.0     51
26.0     50
21.0     44
20.0     16
Name: age, dtype: int64

11.1 Combine Age columns for Dete_survey and Tafe_survey

In [43]:
# To add column to each dataframe for age colums
dete_resignations_up['age'] = 'DETE'
tafe_resignations_up['age'] = 'TAFE'
In [44]:
# To combine columns using Concatenate dataframes horizontally (axis=1)
combined_age = pd.concat([dete_survey_updated, tafe_survey_updated],ignore_index = True)

#Verify the number of non null values in each column
combined_age.notnull().sum().sort_values()
Out[44]:
torres_strait                                             3
south_sea                                                 7
aboriginal                                               16
disability                                               23
nesb                                                     32
business_unit                                           126
Contributing Factors. Career Move - Public Sector       437
Contributing Factors. Career Move - Private Sector      437
Contributing Factors. Career Move - Self-employment     437
Contributing Factors. Ill Health                        437
Contributing Factors. Maternity/Family                  437
Contributing Factors. Dissatisfaction                   437
Contributing Factors. Interpersonal Conflict            437
Contributing Factors. Job Dissatisfaction               437
Contributing Factors. Travel                            437
Contributing Factors. Other                             437
Contributing Factors. NONE                              437
Contributing Factors. Study                             437
classification                                          455
Gender. What is your Gender?                            596
institute_service                                       596
Employment Type. Employment Type                        596
role_service                                            596
WorkArea                                                702
Institute                                               702
region                                                  717
role_start_date                                         724
dete_start_date                                         749
gender                                                  798
employment_status                                       817
workload                                                822
none_of_the_above                                       822
career_move_to_public_sector                            822
career_move_to_private_sector                           822
interpersonal_conflicts                                 822
job_dissatisfaction                                     822
dissatisfaction_with_the_department                     822
physical_work_environment                               822
lack_of_job_security                                    822
lack_of_recognition                                     822
employment_conditions                                   822
maternity/family                                        822
relocation                                              822
study/travel                                            822
ill_health                                              822
traumatic_incident                                      822
work_life_balance                                       822
work_location                                           822
age                                                    1407
position                                               1413
cease_date                                             1483
separationtype                                         1523
id                                                     1524
dtype: int64
In [45]:
combined_age_updated = combined_age.dropna(thresh=500, axis = 1).copy()
In [46]:
def employee_retirement_age(age):
    age = float(age)   # Convert string to float before comparing
    if age >= 50:
        return 'Old Age'
    elif 40 <= age < 50:
        return 'Middle Age Adult'
    elif 20 >= age < 40:
        return 'Young Adult'
    elif pd.isnull(age):
        return np.nan
    else:
        return 'Adolescent'
    
combined_age_updated['Age_retirement_cat'] = combined_age_updated['age'].apply(employee_retirement_age)


# Check the unique values after the updates
combined_age_updated['Age_retirement_cat'].value_counts()
Out[46]:
Old Age             743
Adolescent          384
Middle Age Adult    263
Young Adult          17
Name: Age_retirement_cat, dtype: int64
In [47]:
fig = plt.figure()
ax = fig.add_axes([0,0,1,1])
ax.axis('equal')
Age_retirement_cat = ['Old Age', 'Adolescent', 'Middle Age Adult', 'Young Adult']
column =[60,49,39,19]
ax.pie(column, labels = Age_retirement_cat, autopct='%1.2f%%')
plt.show()

11.2 Brief Explanation Age Column

Based on pie-chart plot above, about 35.93% of employees are likely to exit job due to old age, probably due to retirement. 29.34% are adolescent age and might still be too young to work while both Middle age adult(23.35%) and young adults(11.38%) are less likely to ritire from job but these group are prone to changing job

Conclusion

From my point of view, Young and new employees are more likely to exit job due to job dissatisfaction. For Example, about 193 new employees and 29.34% young adolescent employees are likely to quit job. Veterans and establish employees at their old ages are likely to exit job due to retirement. However, Both middle and young experience employees are likely to change job due to either job dissatisfaction or find new challenges in other companies