Clean and Analyse Employee Exit Surveys

In this project, we'll clean and analyse the surveys from employees of Department of Education, Training and Employment and the Technical and Further Education (TAFE) institute in Queensland, Australia.

Our aim is to pretend our stakeholders want to find out the following questions:

  • Are employee who only worked for the institutes for a short period of time resigning due to some kind of dissatisfaction? What about emplotees who have been there longer?
  • Are younger employees resigning due to some kind of dissatisfaction? What about older employees?

Reading in the Datasets

In [65]:
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
In [14]:
# Read in data for dete
dete_survey = pd.read_csv('dete_survey.csv')

pd.options.display.max_columns = 150 # This line will avoid truncated output for the ease of visualisation
dete_survey.head()
Out[14]:
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 Professional Development Opportunities for promotion Staff morale Workplace issue Physical environment Worklife balance Stress and pressure support Performance of supervisor Peer support Initiative Skills Coach Career Aspirations Feedback Further PD Communication My say Information 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 True False False True False False True False False False False False False False False False False True A A N N N A A A A N N N A A A N A A 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 False False False False False False False False False False False False False False False False False False A A N N N N A A A N N N A A A N A A 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 False False False False False False False False False False False False False False False False False True N N N N N N N N N N N N N N N A A N 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 False True False False False False False False False False False False False False False False False False A N N N A A N N A A A A A A A A A A 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 False False False False False False False False False False False False False False False True False False A A N N D D N A A A A A A SA SA D D A N A M Female 61 or older NaN NaN NaN NaN NaN
In [11]:
dete_survey.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 822 entries, 0 to 821
Data columns (total 56 columns):
ID                                     822 non-null int64
SeparationType                         822 non-null object
Cease Date                             822 non-null object
DETE Start Date                        822 non-null object
Role Start Date                        822 non-null object
Position                               817 non-null object
Classification                         455 non-null object
Region                                 822 non-null object
Business Unit                          126 non-null object
Employment Status                      817 non-null object
Career move to public sector           822 non-null bool
Career move to private sector          822 non-null bool
Interpersonal conflicts                822 non-null bool
Job dissatisfaction                    822 non-null bool
Dissatisfaction with the department    822 non-null bool
Physical work environment              822 non-null bool
Lack of recognition                    822 non-null bool
Lack of job security                   822 non-null bool
Work location                          822 non-null bool
Employment conditions                  822 non-null bool
Maternity/family                       822 non-null bool
Relocation                             822 non-null bool
Study/Travel                           822 non-null bool
Ill Health                             822 non-null bool
Traumatic incident                     822 non-null bool
Work life balance                      822 non-null bool
Workload                               822 non-null bool
None of the above                      822 non-null bool
Professional Development               808 non-null object
Opportunities for promotion            735 non-null object
Staff morale                           816 non-null object
Workplace issue                        788 non-null object
Physical environment                   817 non-null object
Worklife balance                       815 non-null object
Stress and pressure support            810 non-null object
Performance of supervisor              813 non-null object
Peer support                           812 non-null object
Initiative                             813 non-null object
Skills                                 811 non-null object
Coach                                  767 non-null object
Career Aspirations                     746 non-null object
Feedback                               792 non-null object
Further PD                             768 non-null object
Communication                          814 non-null object
My say                                 812 non-null object
Information                            816 non-null object
Kept informed                          813 non-null object
Wellness programs                      766 non-null object
Health & Safety                        793 non-null object
Gender                                 798 non-null object
Age                                    811 non-null object
Aboriginal                             16 non-null object
Torres Strait                          3 non-null object
South Sea                              7 non-null object
Disability                             23 non-null object
NESB                                   32 non-null object
dtypes: bool(18), int64(1), object(37)
memory usage: 258.6+ KB
In [18]:
# Read in data for tafe
tafe_survey = pd.read_csv('tafe_survey.csv')

tafe_survey.head()
Out[18]:
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 Main Factor. Which of these was the main factor for leaving? InstituteViews. Topic:1. I feel the senior leadership had a clear vision and direction InstituteViews. Topic:2. I was given access to skills training to help me do my job better InstituteViews. Topic:3. I was given adequate opportunities for personal development InstituteViews. Topic:4. I was given adequate opportunities for promotion within %Institute]Q25LBL% InstituteViews. Topic:5. I felt the salary for the job was right for the responsibilities I had InstituteViews. Topic:6. The organisation recognised when staff did good work InstituteViews. Topic:7. Management was generally supportive of me InstituteViews. Topic:8. Management was generally supportive of my team InstituteViews. Topic:9. I was kept informed of the changes in the organisation which would affect me InstituteViews. Topic:10. Staff morale was positive within the Institute InstituteViews. Topic:11. If I had a workplace issue it was dealt with quickly InstituteViews. Topic:12. If I had a workplace issue it was dealt with efficiently InstituteViews. Topic:13. If I had a workplace issue it was dealt with discreetly WorkUnitViews. Topic:14. I was satisfied with the quality of the management and supervision within my work unit WorkUnitViews. Topic:15. I worked well with my colleagues WorkUnitViews. Topic:16. My job was challenging and interesting WorkUnitViews. Topic:17. I was encouraged to use my initiative in the course of my work WorkUnitViews. Topic:18. I had sufficient contact with other people in my job WorkUnitViews. Topic:19. I was given adequate support and co-operation by my peers to enable me to do my job WorkUnitViews. Topic:20. I was able to use the full range of my skills in my job 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] WorkUnitViews. Topic:22. I was able to use the full range of my knowledge in my job WorkUnitViews. Topic:23. My job provided sufficient variety WorkUnitViews. Topic:24. I was able to cope with the level of stress and pressure in my job WorkUnitViews. Topic:25. My job allowed me to balance the demands of work and family to my satisfaction WorkUnitViews. Topic:26. My supervisor gave me adequate personal recognition and feedback on my performance WorkUnitViews. Topic:27. My working environment was satisfactory e.g. sufficient space, good lighting, suitable seating and working area 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 WorkUnitViews. Topic:29. There was adequate communication between staff in my unit WorkUnitViews. Topic:30. Staff morale was positive within my work unit Induction. Did you undertake Workplace Induction? InductionInfo. Topic:Did you undertake a Corporate Induction? InductionInfo. Topic:Did you undertake a Institute Induction? InductionInfo. Topic: Did you undertake Team Induction? InductionInfo. Face to Face Topic:Did you undertake a Corporate Induction; Category:How it was conducted? InductionInfo. On-line Topic:Did you undertake a Corporate Induction; Category:How it was conducted? InductionInfo. Induction Manual Topic:Did you undertake a Corporate Induction? InductionInfo. Face to Face Topic:Did you undertake a Institute Induction? InductionInfo. On-line Topic:Did you undertake a Institute Induction? InductionInfo. Induction Manual Topic:Did you undertake a Institute Induction? InductionInfo. Face to Face Topic: Did you undertake Team Induction; Category? InductionInfo. On-line Topic: Did you undertake Team Induction?process you undertook and how it was conducted.] InductionInfo. Induction Manual Topic: Did you undertake Team Induction? Workplace. Topic:Did you and your Manager develop a Performance and Professional Development Plan (PPDP)? 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 NaN NaN NaN NaN NaN NaN NaN NaN Agree Agree Agree Neutral Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Strongly Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Neutral Agree Agree Yes Yes Yes Yes Face to Face - - Face to Face - - Face to Face - - Yes 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 - - - - - - - - - Travel - - NaN Agree Agree Agree Agree Agree Strongly Agree Strongly Agree Agree Strongly Agree Agree Agree Agree Disagree Strongly Agree Strongly Agree Strongly Agree Agree Agree Agree Strongly Agree Agree Agree Agree Strongly Agree Agree Strongly Agree Strongly Agree Agree Agree Strongly Agree No NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN Yes Yes Yes Yes Yes NaN NaN NaN NaN NaN NaN
2 6.341388e+17 Mount Isa Institute of TAFE Delivery (teaching) 2010.0 Retirement - - - - - - - - - - - NONE NaN Agree Agree Agree Agree Agree Agree Strongly Agree Agree Agree Agree Agree Neutral Neutral Strongly Agree Strongly Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree No NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN Yes 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 - - - - - - - - - Travel - - NaN Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Yes No Yes Yes - - - NaN - - - - - Yes 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 - - - - - - - - - - NaN Agree Agree Strongly Agree Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Agree Strongly Agree Strongly Agree Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Strongly Agree Yes Yes Yes Yes - - Induction Manual Face to Face - - Face to Face - - Yes Yes Yes Yes Yes Male 41 45 Permanent Full-time Teacher (including LVT) 3-4 3-4
In [13]:
tafe_survey.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 702 entries, 0 to 701
Data columns (total 72 columns):
Record ID                                                                                                                                                        702 non-null float64
Institute                                                                                                                                                        702 non-null object
WorkArea                                                                                                                                                         702 non-null object
CESSATION YEAR                                                                                                                                                   695 non-null float64
Reason for ceasing employment                                                                                                                                    701 non-null object
Contributing Factors. Career Move - Public Sector                                                                                                                437 non-null object
Contributing Factors. Career Move - Private Sector                                                                                                               437 non-null object
Contributing Factors. Career Move - Self-employment                                                                                                              437 non-null object
Contributing Factors. Ill Health                                                                                                                                 437 non-null object
Contributing Factors. Maternity/Family                                                                                                                           437 non-null object
Contributing Factors. Dissatisfaction                                                                                                                            437 non-null object
Contributing Factors. Job Dissatisfaction                                                                                                                        437 non-null object
Contributing Factors. Interpersonal Conflict                                                                                                                     437 non-null object
Contributing Factors. Study                                                                                                                                      437 non-null object
Contributing Factors. Travel                                                                                                                                     437 non-null object
Contributing Factors. Other                                                                                                                                      437 non-null object
Contributing Factors. NONE                                                                                                                                       437 non-null object
Main Factor. Which of these was the main factor for leaving?                                                                                                     113 non-null object
InstituteViews. Topic:1. I feel the senior leadership had a clear vision and direction                                                                           608 non-null object
InstituteViews. Topic:2. I was given access to skills training to help me do my job better                                                                       613 non-null object
InstituteViews. Topic:3. I was given adequate opportunities for personal development                                                                             610 non-null object
InstituteViews. Topic:4. I was given adequate opportunities for promotion within %Institute]Q25LBL%                                                              608 non-null object
InstituteViews. Topic:5. I felt the salary for the job was right for the responsibilities I had                                                                  615 non-null object
InstituteViews. Topic:6. The organisation recognised when staff did good work                                                                                    607 non-null object
InstituteViews. Topic:7. Management was generally supportive of me                                                                                               614 non-null object
InstituteViews. Topic:8. Management was generally supportive of my team                                                                                          608 non-null object
InstituteViews. Topic:9. I was kept informed of the changes in the organisation which would affect me                                                            610 non-null object
InstituteViews. Topic:10. Staff morale was positive within the Institute                                                                                         602 non-null object
InstituteViews. Topic:11. If I had a workplace issue it was dealt with quickly                                                                                   601 non-null object
InstituteViews. Topic:12. If I had a workplace issue it was dealt with efficiently                                                                               597 non-null object
InstituteViews. Topic:13. If I had a workplace issue it was dealt with discreetly                                                                                601 non-null object
WorkUnitViews. Topic:14. I was satisfied with the quality of the management and supervision within my work unit                                                  609 non-null object
WorkUnitViews. Topic:15. I worked well with my colleagues                                                                                                        605 non-null object
WorkUnitViews. Topic:16. My job was challenging and interesting                                                                                                  607 non-null object
WorkUnitViews. Topic:17. I was encouraged to use my initiative in the course of my work                                                                          610 non-null object
WorkUnitViews. Topic:18. I had sufficient contact with other people in my job                                                                                    613 non-null object
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
WorkUnitViews. Topic:20. I was able to use the full range of my skills in my job                                                                                 609 non-null object
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
WorkUnitViews. Topic:22. I was able to use the full range of my knowledge in my job                                                                              608 non-null object
WorkUnitViews. Topic:23. My job provided sufficient variety                                                                                                      611 non-null object
WorkUnitViews. Topic:24. I was able to cope with the level of stress and pressure in my job                                                                      610 non-null object
WorkUnitViews. Topic:25. My job allowed me to balance the demands of work and family to my satisfaction                                                          611 non-null object
WorkUnitViews. Topic:26. My supervisor gave me adequate personal recognition and feedback on my performance                                                      606 non-null object
WorkUnitViews. Topic:27. My working environment was satisfactory e.g. sufficient space, good lighting, suitable seating and working area                         610 non-null object
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
WorkUnitViews. Topic:29. There was adequate communication between staff in my unit                                                                               603 non-null object
WorkUnitViews. Topic:30. Staff morale was positive within my work unit                                                                                           606 non-null object
Induction. Did you undertake Workplace Induction?                                                                                                                619 non-null object
InductionInfo. Topic:Did you undertake a Corporate Induction?                                                                                                    432 non-null object
InductionInfo. Topic:Did you undertake a Institute Induction?                                                                                                    483 non-null object
InductionInfo. Topic: Did you undertake Team Induction?                                                                                                          440 non-null object
InductionInfo. Face to Face Topic:Did you undertake a Corporate Induction; Category:How it was conducted?                                                        555 non-null object
InductionInfo. On-line Topic:Did you undertake a Corporate Induction; Category:How it was conducted?                                                             555 non-null object
InductionInfo. Induction Manual Topic:Did you undertake a Corporate Induction?                                                                                   555 non-null object
InductionInfo. Face to Face Topic:Did you undertake a Institute Induction?                                                                                       530 non-null object
InductionInfo. On-line Topic:Did you undertake a Institute Induction?                                                                                            555 non-null object
InductionInfo. Induction Manual Topic:Did you undertake a Institute Induction?                                                                                   553 non-null object
InductionInfo. Face to Face Topic: Did you undertake Team Induction; Category?                                                                                   555 non-null object
InductionInfo. On-line Topic: Did you undertake Team Induction?process you undertook and how it was conducted.]                                                  555 non-null object
InductionInfo. Induction Manual Topic: Did you undertake Team Induction?                                                                                         555 non-null object
Workplace. Topic:Did you and your Manager develop a Performance and Professional Development Plan (PPDP)?                                                        608 non-null object
Workplace. Topic:Does your workplace promote a work culture free from all forms of unlawful discrimination?                                                      594 non-null object
Workplace. Topic:Does your workplace promote and practice the principles of employment equity?                                                                   587 non-null object
Workplace. Topic:Does your workplace value the diversity of its employees?                                                                                       586 non-null object
Workplace. Topic:Would you recommend the Institute as an employer to others?                                                                                     581 non-null object
Gender. What is your Gender?                                                                                                                                     596 non-null object
CurrentAge. Current Age                                                                                                                                          596 non-null object
Employment Type. Employment Type                                                                                                                                 596 non-null object
Classification. Classification                                                                                                                                   596 non-null object
LengthofServiceOverall. Overall Length of Service at Institute (in years)                                                                                        596 non-null object
LengthofServiceCurrent. Length of Service at current workplace (in years)                                                                                        596 non-null object
dtypes: float64(2), object(70)
memory usage: 395.0+ KB

From the initial observation of both datasets, we discovered the following:

  • dete_survey dataframe contains values that are set to 'N' or 'A' instead of NaN
  • Both datasets contain many columns that are not neccessary for us to accomplish our mission
  • Each dataframe contains many of the same column, but the column names are different
  • There are multiple columns/answers that indicate an employee resigned because they were dissatisfied.

Identifying Missing Values and Drop Unneccessary Columns

First, we'll correct the Not Stated values and drop some of the columns we don't need for our analysis.

In [21]:
# Read in the data again, but this time replace `Not Stated` values with `NaN`
dete_survey = pd.read_csv('dete_survey.csv', na_values='Not Stated')

dete_survey.head()
Out[21]:
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 Professional Development Opportunities for promotion Staff morale Workplace issue Physical environment Worklife balance Stress and pressure support Performance of supervisor Peer support Initiative Skills Coach Career Aspirations Feedback Further PD Communication My say Information 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 True False False True False False True False False False False False False False False False False True A A N N N A A A A N N N A A A N A A 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 False False False False False False False False False False False False False False False False False False A A N N N N A A A N N N A A A N A A 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 False False False False False False False False False False False False False False False False False True N N N N N N N N N N N N N N N A A N 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 False True False False False False False False False False False False False False False False False False A N N N A A N N A A A A A A A A A A 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 False False False False False False False False False False False False False False False True False False A A N N D D N A A A A A A SA SA D D A N A M Female 61 or older NaN NaN NaN NaN NaN

Since there are too many columns in the datasets, we will remove the columns we don't need for our analysis.

In [24]:
# Remove columns we don't need for our analysis
dete_survey_updated = dete_survey.drop(dete_survey.columns[28:49], axis=1)
tafe_survey_updated = tafe_survey.drop(tafe_survey.columns[17:66], axis=1)
In [25]:
dete_survey_updated.columns
Out[25]:
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 [26]:
tafe_survey_updated.columns
Out[26]:
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')

Each dataframe contains many of the same column, but with different names. 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

We want to concatenate both dataset, so we'll need to standardise the column names.

We want our column names to meet the following criterias:

  • lowercase
  • replace spaces with underscores
  • no whitespaces
In [28]:
dete_survey_updated.columns = dete_survey_updated.columns.str.lower().str.strip().str.replace(' ', '_')
dete_survey_updated.columns
Out[28]:
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 [102]:
# Update column names to match the names in dete_survey_updated
mapping = {'Record ID': 'id',
          'CESSATION YEAR': 'cease_date',
          'Reason for ceasing employment': 'separationtype',
          'Gender. What is your Gender?': 'gender',
          'CurrentAge. Current Age': 'age',
          'Employment Type. Employment Type': 'employment_status',
          'Classification. Classification': 'position',
          'LengthofServiceOverall. Overall Length of Service at Institute (in years)': 'institute_service',
          'LengthofServiceCurrent. Length of Service at current workplace (in years)': 'role_service',
          'seperationtype': 'separationtype'}

tafe_survey_updated = tafe_survey_updated.rename(mapping, axis=1)

tafe_survey_updated.head()
Out[102]:
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 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 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
In [52]:
dete_survey_updated.head()
Out[52]:
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
0 1 Ill Health Retirement 08/2012 1984.0 2004.0 Public Servant A01-A04 Central Office Corporate Strategy and Peformance Permanent Full-time True False False True False False True False False False False False False False False 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 False False False False False False False False False False False False 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 False False False False False False False False False False False False False False False True Male 61 or older NaN NaN NaN NaN NaN
3 4 Resignation 05/2012 2005.0 2006.0 Teacher Primary Central Queensland NaN Permanent Full-time False True False False False False False False False False False False False False False 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 False False False False False False False False False False False False False False False True False False Female 61 or older NaN NaN NaN NaN NaN
In [53]:
dete_survey_updated['separationtype'].value_counts()
Out[53]:
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 [54]:
tafe_survey_updated['separationtype'].value_counts()
Out[54]:
Resignation                 340
Contract Expired            127
Retrenchment/ Redundancy    104
Retirement                   82
Transfer                     25
Termination                  23
Name: separationtype, dtype: int64
In [103]:
tafe_survey_updated['institute_service'].value_counts().sum()
Out[103]:
596

Further Removing Unneccessary Columns

Recall that out aim is to answer the following question:

  • Are employees who have only worked for the institues for a short period of time resigning due to some kind of dissatisfaction? What about employees who have been at the job longer?

By looking at seperationtype columns of both dataframes, we can see different reasons for resignation. However, we are only interested in the values that contain the string Resignation.

There are 3 different reasons of resignation that contain the string Resignation, so we will modifiy the strings to display only Resignation.

In [55]:
dete_survey_updated['separationtype'] = dete_survey_updated['separationtype'].str.split('-').str[0]
dete_survey_updated['separationtype'].value_counts()
Out[55]:
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 [56]:
# 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()

Verify the Data

Before we start cleaning and manipulating the rest of our data, we need to verify that the data doesn't contain any major inconsistencies. It may not be possible to catch all of the errors, but by making sure the data seems reasonable to the best of our knowledge, we can stop ourselves from completing a data analysis project that winds up being useless because of bad data.

To ensure we manipulate our data to the highest quality possible, we will focus on verying the columns one by one.

First, we'll look at cease_date and dete_start_date columns. Since cease_date is the last year of the person's employment and the dete_start_date is the person's first year of emplyment, 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 unliekly that the dete_start_date was before the year 1940.

Hence, we will eliminate any data with years higher than the current date or lower than 1940.

In [57]:
dete_resignations['cease_date'].value_counts()
Out[57]:
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
In [69]:
# 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')

dete_resignations['cease_date'].value_counts()

AttributeErrorTraceback (most recent call last)
<ipython-input-69-3cfd1838aba3> in <module>()
      1 # Extract the years and convert them to a float type
----> 2 dete_resignations['cease_date'] = dete_resignations['cease_date'].str.split('/').str[-1]
      3 dete_resignations['cease_date'] = dete_resignations['cease_date'].astype('float')
      4 
      5 dete_resignations['cease_date'].value_counts()

/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/generic.py in __getattr__(self, name)
   3608         if (name in self._internal_names_set or name in self._metadata or
   3609                 name in self._accessors):
-> 3610             return object.__getattribute__(self, name)
   3611         else:
   3612             if name in self._info_axis:

/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/accessor.py in __get__(self, instance, owner)
     52             # this ensures that Series.str.<method> is well defined
     53             return self.accessor_cls
---> 54         return self.construct_accessor(instance)
     55 
     56     def __set__(self, instance, value):

/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/strings.py in _make_accessor(cls, data)
   1908             # (instead of test for object dtype), but that isn't practical for
   1909             # performance reasons until we have a str dtype (GH 9343)
-> 1910             raise AttributeError("Can only use .str accessor with string "
   1911                                  "values, which use np.object_ dtype in "
   1912                                  "pandas")

AttributeError: Can only use .str accessor with string values, which use np.object_ dtype in pandas
In [63]:
dete_resignations['dete_start_date'].value_counts().sort_values(ascending=False)
Out[63]:
2011.0    24
2008.0    22
2007.0    21
2012.0    21
2010.0    17
2005.0    15
2004.0    14
2006.0    13
2009.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
1990.0     5
1993.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
1976.0     2
1983.0     2
1974.0     2
1982.0     1
1973.0     1
1975.0     1
1987.0     1
1977.0     1
1984.0     1
1972.0     1
1971.0     1
1963.0     1
Name: dete_start_date, dtype: int64
In [70]:
tafe_resignations['cease_date'].value_counts().sort_values()
Out[70]:
2009.0      2
2013.0     55
2010.0     68
2012.0     94
2011.0    116
Name: cease_date, dtype: int64

Create a New Column for DETE Dataframe to Show Years of Service

To calculate the years of service in dete_survey, we will subtract dete_start_date from cease_date and create a new column named institute_service.

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

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

Identify Dissatisfied Employees

Now, we'll identify any employees who resigned because they were dissatisfied. Below are the columns we'll use to categorise 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, we'll mark them as dissatisfied in a new column.

In order to do this, we need to:

  1. Convert the values in the Contributing Factors. Dissatisfaction and Contributing Factors. Job Dissatisfaction columns in the tafe_resignations dataframe to True, False, or NaN values.
  2. If any of the columns listed above contain a True value, we'll add a True value to a new column named dissatisfied.
In [75]:
tafe_resignations['Contributing Factors. Dissatisfaction'].value_counts()
Out[75]:
-                                         277
Contributing Factors. Dissatisfaction      55
Name: Contributing Factors. Dissatisfaction, dtype: int64
In [76]:
tafe_resignations['Contributing Factors. Job Dissatisfaction'].value_counts()
Out[76]:
-                      270
Job Dissatisfaction     62
Name: Contributing Factors. Job Dissatisfaction, dtype: int64
In [80]:
# Define a function to replace the values in the contributing columns to either True, False or NaN
def update_vals(x):
    if x == '-':
        return False
    elif pd.isnull(x):
        return np.nan
    else:
        return True
    
tafe_resignations['dissatisfied'] = tafe_resignations[['Contributing Factors. Dissatisfaction',
                                                       'Contributing Factors. Job Dissatisfaction']].applymap(update_vals).any(axis=1, skipna=False)
tafe_resignations_up = tafe_resignations.copy()

tafe_resignations_up['dissatisfied'].value_counts(dropna=False)
Out[80]:
False    241
True      91
NaN        8
Name: dissatisfied, dtype: int64
In [82]:
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()
dete_resignations_up['dissatisfied'].value_counts(dropna=False)
Out[82]:
False    162
True     149
Name: dissatisfied, dtype: int64

Combining the Data

We'll now add an institute column so that we can differentiate the data from each survey after we combine them. Then, we'll combine the dataframes and drop any remaining columns we don't need.

In [84]:
# Add an institute column
dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'
In [85]:
combined = pd.concat([dete_resignations_up, tafe_resignations_up], ignore_index=True)
In [88]:
combined['institute'].value_counts()
Out[88]:
TAFE    340
DETE    311
Name: institute, dtype: int64
In [89]:
# Verify the number of non null values in each column
combined.notnull().sum().sort_values()
Out[89]:
torres_strait                                                                  0
south_sea                                                                      3
aboriginal                                                                     7
disability                                                                     8
nesb                                                                           9
business_unit                                                                 32
classification                                                               161
region                                                                       265
role_start_date                                                              271
institute_service                                                            273
dete_start_date                                                              283
role_service                                                                 290
LengthofServiceOverall. Overall Length of Service at Institute (in years)    290
interpersonal_conflicts                                                      311
job_dissatisfaction                                                          311
lack_of_job_security                                                         311
employment_conditions                                                        311
maternity/family                                                             311
none_of_the_above                                                            311
physical_work_environment                                                    311
relocation                                                                   311
study/travel                                                                 311
traumatic_incident                                                           311
work_life_balance                                                            311
lack_of_recognition                                                          311
ill_health                                                                   311
workload                                                                     311
dissatisfaction_with_the_department                                          311
career_move_to_public_sector                                                 311
career_move_to_private_sector                                                311
work_location                                                                311
Contributing Factors. Study                                                  332
Contributing Factors. Career Move - Public Sector                            332
Contributing Factors. Career Move - Self-employment                          332
Contributing Factors. Dissatisfaction                                        332
Contributing Factors. Ill Health                                             332
Contributing Factors. Interpersonal Conflict                                 332
Contributing Factors. Job Dissatisfaction                                    332
Contributing Factors. Maternity/Family                                       332
Contributing Factors. NONE                                                   332
Contributing Factors. Other                                                  332
Contributing Factors. Career Move - Private Sector                           332
Contributing Factors. Travel                                                 332
Institute                                                                    340
WorkArea                                                                     340
gender                                                                       592
age                                                                          596
employment_status                                                            597
position                                                                     598
cease_date                                                                   635
dissatisfied                                                                 643
separationtype                                                               651
institute                                                                    651
id                                                                           651
dtype: int64
In [92]:
combined.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 651 entries, 0 to 650
Data columns (total 54 columns):
Contributing Factors. Career Move - Private Sector                           332 non-null object
Contributing Factors. Career Move - Public Sector                            332 non-null object
Contributing Factors. Career Move - Self-employment                          332 non-null object
Contributing Factors. Dissatisfaction                                        332 non-null object
Contributing Factors. Ill Health                                             332 non-null object
Contributing Factors. Interpersonal Conflict                                 332 non-null object
Contributing Factors. Job Dissatisfaction                                    332 non-null object
Contributing Factors. Maternity/Family                                       332 non-null object
Contributing Factors. NONE                                                   332 non-null object
Contributing Factors. Other                                                  332 non-null object
Contributing Factors. Study                                                  332 non-null object
Contributing Factors. Travel                                                 332 non-null object
Institute                                                                    340 non-null object
LengthofServiceOverall. Overall Length of Service at Institute (in years)    290 non-null object
WorkArea                                                                     340 non-null object
aboriginal                                                                   7 non-null object
age                                                                          596 non-null object
business_unit                                                                32 non-null object
career_move_to_private_sector                                                311 non-null object
career_move_to_public_sector                                                 311 non-null object
cease_date                                                                   635 non-null float64
classification                                                               161 non-null object
dete_start_date                                                              283 non-null float64
disability                                                                   8 non-null object
dissatisfaction_with_the_department                                          311 non-null object
dissatisfied                                                                 643 non-null object
employment_conditions                                                        311 non-null object
employment_status                                                            597 non-null object
gender                                                                       592 non-null object
id                                                                           651 non-null float64
ill_health                                                                   311 non-null object
institute                                                                    651 non-null object
institute_service                                                            273 non-null float64
interpersonal_conflicts                                                      311 non-null object
job_dissatisfaction                                                          311 non-null object
lack_of_job_security                                                         311 non-null object
lack_of_recognition                                                          311 non-null object
maternity/family                                                             311 non-null object
nesb                                                                         9 non-null object
none_of_the_above                                                            311 non-null object
physical_work_environment                                                    311 non-null object
position                                                                     598 non-null object
region                                                                       265 non-null object
relocation                                                                   311 non-null object
role_service                                                                 290 non-null object
role_start_date                                                              271 non-null float64
separationtype                                                               651 non-null object
south_sea                                                                    3 non-null object
study/travel                                                                 311 non-null object
torres_strait                                                                0 non-null object
traumatic_incident                                                           311 non-null object
work_life_balance                                                            311 non-null object
work_location                                                                311 non-null object
workload                                                                     311 non-null object
dtypes: float64(5), object(49)
memory usage: 274.7+ KB
In [93]:
# Drop columns with less than 500 non null values
combined_updated = combined.dropna(thresh=500, axis=1).copy()
In [94]:
combined_updated.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 651 entries, 0 to 650
Data columns (total 9 columns):
age                  596 non-null object
cease_date           635 non-null float64
dissatisfied         643 non-null object
employment_status    597 non-null object
gender               592 non-null object
id                   651 non-null float64
institute            651 non-null object
position             598 non-null object
separationtype       651 non-null object
dtypes: float64(2), object(7)
memory usage: 45.9+ KB

Clean the Service Column

Next, we'll clean the institute_service column and categorise employees according to the following definitions:

  • New: Less than 3 years in the workplace
  • Experienced: 3-6 years in the workplace
  • Established: 7-10 years in the workplace
  • Veteran: 11 or more years in the workplace
In [96]:
combined_updated.head()
Out[96]:
age cease_date dissatisfied employment_status gender id institute position separationtype
0 36-40 2012.0 False Permanent Full-time Female 4.0 DETE Teacher Resignation
1 41-45 2012.0 True Permanent Full-time Female 6.0 DETE Guidance Officer Resignation
2 31-35 2012.0 False Permanent Full-time Female 9.0 DETE Teacher Resignation
3 46-50 2012.0 True Permanent Part-time Female 10.0 DETE Teacher Aide Resignation
4 31-35 2012.0 False Permanent Full-time Male 12.0 DETE Teacher Resignation
In [105]:
combined_updated['institute_service'].value_counts()

KeyErrorTraceback (most recent call last)
/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
   2524             try:
-> 2525                 return self._engine.get_loc(key)
   2526             except KeyError:

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'institute_service'

During handling of the above exception, another exception occurred:

KeyErrorTraceback (most recent call last)
<ipython-input-105-15e24cf5f58b> in <module>()
----> 1 combined_updated['institute_service'].value_counts()

/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/frame.py in __getitem__(self, key)
   2137             return self._getitem_multilevel(key)
   2138         else:
-> 2139             return self._getitem_column(key)
   2140 
   2141     def _getitem_column(self, key):

/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/frame.py in _getitem_column(self, key)
   2144         # get column
   2145         if self.columns.is_unique:
-> 2146             return self._get_item_cache(key)
   2147 
   2148         # duplicate columns & possible reduce dimensionality

/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/generic.py in _get_item_cache(self, item)
   1840         res = cache.get(item)
   1841         if res is None:
-> 1842             values = self._data.get(item)
   1843             res = self._box_item_values(item, values)
   1844             cache[item] = res

/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/internals.py in get(self, item, fastpath)
   3841 
   3842             if not isna(item):
-> 3843                 loc = self.items.get_loc(item)
   3844             else:
   3845                 indexer = np.arange(len(self.items))[isna(self.items)]

/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
   2525                 return self._engine.get_loc(key)
   2526             except KeyError:
-> 2527                 return self._engine.get_loc(self._maybe_cast_indexer(key))
   2528 
   2529         indexer = self.get_indexer([key], method=method, tolerance=tolerance)

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'institute_service'
In [ ]: