Clean and analyze employee exit surveys

The goal of this project is to answer the following questions:

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

For this project we work with exit surveys from employees of the Department of Education, Training and Employment (DETE) and the Technical Further Education (TAFE) institute in Queensland, Australia. The idea of this project is to combine both surveys and apply data cleaning and analysis techniques

1. Data Exploration

To start, the libraries are imporated and the data is imported.

In [67]:
# import libraries
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
sns.set_style("whitegrid", {'axes.grid' : False})

%matplotlib inline  

# import csv files of surveys
dete_survey = pd.read_csv("dete_survey.csv")
tafe_survey = pd.read_csv("tafe_survey.csv")

# mute SettingWithCopyWarning
pd.options.mode.chained_assignment = None  # default='warn'

1.1 DETE survey

Next, we are getting acquainted with the data and investigage the first rows, different types of values and proportion of missing data. Let us investigate the DETE data first, and subsequently the TAFE data.

In [68]:
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 [69]:
dete_survey.dtypes.value_counts()
Out[69]:
object    37
bool      18
int64      1
dtype: int64
In [70]:
dete_survey.head(50)
Out[70]:
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 11 Age Retirement 2012 1999 1999 Teacher Primary Central Office Education Queensland Permanent Full-time ... A NaN A Male 61 or older NaN NaN NaN NaN NaN
11 12 Resignation-Move overseas/interstate 2012 2009 2009 Teacher Secondary Far North Queensland NaN Permanent Full-time ... N N N Male 31-35 NaN NaN NaN NaN NaN
12 13 Resignation-Other reasons 2012 1998 1998 Teacher Primary Far North Queensland NaN Permanent Full-time ... SA A A Female 36-40 NaN NaN NaN NaN NaN
13 14 Age Retirement 2012 1967 2000 Teacher Primary Metropolitan NaN Permanent Part-time ... A D A Female 61 or older NaN NaN NaN NaN NaN
14 15 Resignation-Other employer 2012 2007 2010 Teacher Secondary Central Queensland NaN Permanent Full-time ... SA N SA Male 31-35 NaN NaN NaN NaN NaN
15 16 Voluntary Early Retirement (VER) 2012 1995 2004 Teacher Secondary Central Queensland NaN Permanent Full-time ... A N A Male 61 or older NaN NaN NaN NaN NaN
16 17 Resignation-Other reasons 2012 Not Stated Not Stated Teacher Aide NaN South East NaN Permanent Part-time ... M M M Male 61 or older NaN NaN NaN NaN NaN
17 18 Age Retirement 2012 1996 1996 Teacher Primary Central Queensland NaN Permanent Full-time ... A A A Female 56-60 NaN NaN NaN NaN NaN
18 19 Age Retirement 2012 2006 2006 Cleaner NaN Central Office Education Queensland Permanent Full-time ... A A A Female 61 or older NaN NaN NaN NaN NaN
19 20 Age Retirement 2012 1989 1989 Cleaner NaN Central Office Education Queensland Permanent Full-time ... A A A Male 61 or older NaN NaN NaN NaN NaN
20 21 Resignation-Other employer 2012 1982 1982 Teacher Secondary Central Queensland NaN Permanent Full-time ... A SD A Male 56-60 NaN NaN NaN NaN NaN
21 22 Resignation-Other reasons 2012 1980 2009 Cleaner NaN Darling Downs South West NaN Permanent Part-time ... SA NaN SA Female 51-55 NaN NaN NaN NaN NaN
22 23 Resignation-Other reasons 2012 1997 1998 School Administrative Staff NaN Metropolitan NaN Permanent Part-time ... N D D Female 46-50 NaN NaN NaN NaN NaN
23 24 Resignation-Other reasons 2012 1973 2012 Teacher Primary North Queensland NaN Permanent Full-time ... D SD SD Female 61 or older NaN NaN NaN NaN NaN
24 25 Age Retirement 2012 1981 1981 Teacher Aide NaN North Coast NaN Permanent Part-time ... A N A Female 61 or older NaN NaN NaN NaN NaN
25 26 Resignation-Other reasons 2012 1995 2002 Teacher Primary South East NaN Permanent Part-time ... A SD A Female 41-45 NaN NaN NaN NaN NaN
26 27 Age Retirement 2012 1974 1977 Teacher Primary Central Office Education Queensland Permanent Full-time ... A D A Male 56-60 NaN NaN NaN NaN NaN
27 28 Resignation-Other employer 2012 2005 2011 Public Servant AO5-AO7 Central Office Information and Technologies Permanent Full-time ... A A A Female 21-25 Yes NaN NaN NaN NaN
28 29 Age Retirement 2012 1989 1989 Teacher Aide NaN Darling Downs South West NaN Permanent Part-time ... SA SA SA Female 56-60 NaN NaN NaN NaN NaN
29 30 Age Retirement 2012 1975 2003 Teacher Special Education South East NaN Permanent Full-time ... SA A A Female 56-60 NaN NaN NaN NaN NaN
30 31 Age Retirement 2012 1989 1989 Teacher Primary North Coast NaN Permanent Full-time ... A SD SA Female 56-60 NaN NaN NaN NaN NaN
31 32 Age Retirement 2012 1978 1978 Teacher Secondary Metropolitan NaN Permanent Part-time ... A D A Female 51-55 NaN NaN NaN NaN NaN
32 33 Age Retirement 2012 1975 1992 Head of Curriculum/Head of Special Education NaN Not Stated NaN Permanent Full-time ... A D A Male 56-60 NaN NaN NaN NaN NaN
33 34 Resignation-Other reasons 2012 2003 2003 Teacher Secondary Not Stated NaN Permanent Full-time ... N D N Male 36-40 NaN NaN NaN Yes NaN
34 35 Resignation-Other reasons 2012 2006 2009 Cleaner NaN Central Office Education Queensland Permanent Part-time ... A A A Male 61 or older NaN NaN NaN NaN NaN
35 36 Ill Health Retirement 2012 2000 2000 Teacher Special Education Not Stated NaN Permanent Full-time ... SD SD D Female 51-55 NaN NaN NaN NaN NaN
36 37 Age Retirement 2012 Not Stated 1997 Schools Officer NaN Metropolitan NaN Permanent Full-time ... SA N SA Male 61 or older NaN NaN NaN NaN NaN
37 38 Resignation-Other reasons 2012 2011 2011 Teacher Aide NaN Central Queensland NaN Temporary Part-time ... SA N N Female 21-25 NaN NaN NaN NaN NaN
38 39 Other 2012 1998 1998 Teacher Aide NaN Metropolitan NaN Permanent Part-time ... N SD A Female 51-55 NaN NaN NaN NaN NaN
39 40 Resignation-Move overseas/interstate 2012 Not Stated Not Stated Teacher NaN Central Queensland NaN Permanent Full-time ... N SD N Female 21-25 NaN NaN NaN NaN NaN
40 41 Resignation-Other employer 2012 1977 1980 Teacher Primary South East NaN Permanent Full-time ... SA A SA Male 56-60 NaN NaN NaN NaN NaN
41 42 Resignation-Other reasons 2012 1974 1994 Head of Curriculum/Head of Special Education NaN Metropolitan NaN Permanent Full-time ... N N SA Female 51-55 NaN NaN NaN NaN NaN
42 43 Resignation-Move overseas/interstate 2012 2011 2011 Cleaner NaN North Coast NaN Permanent Part-time ... SA NaN NaN Female 41-45 NaN NaN NaN NaN NaN
43 44 Resignation-Other reasons 2012 1976 1976 Teacher Primary North Coast NaN Permanent Full-time ... SA N A Male 51-55 NaN NaN NaN NaN NaN
44 45 Age Retirement 2012 1985 1991 Teacher Primary Metropolitan NaN Permanent Part-time ... A N N Female 51-55 NaN NaN NaN NaN NaN
45 46 Voluntary Early Retirement (VER) 2012 1999 2001 Teacher Primary Central Queensland NaN Permanent Full-time ... A D N Female 46-50 NaN NaN NaN NaN NaN
46 47 Voluntary Early Retirement (VER) 2012 2008 2008 Cleaner NaN South East NaN Permanent Full-time ... N N N Male 56-60 NaN NaN NaN NaN NaN
47 48 Age Retirement 2012 1980 1993 Teacher Secondary Not Stated NaN Permanent Full-time ... A D A Female 56-60 NaN NaN NaN NaN NaN
48 49 Resignation-Move overseas/interstate 2012 2009 2010 Cleaner NaN South East NaN Permanent Full-time ... A A A Male 21-25 NaN NaN NaN NaN NaN
49 50 Age Retirement 2012 1963 2007 Teacher Primary Darling Downs South West NaN Permanent Full-time ... A D A Female 61 or older NaN NaN NaN NaN NaN

50 rows × 56 columns

At first sight, we can make the following observations:

  • The data set has 822 enrties and 56 columns
  • 1 column has numeric data
  • 18 columns have True or False values
  • 37 columns have textual data
  • We observe that Not Stated is answered referring to missing information (see Region column)

Now let's check what kind of values we have in these columns and explore whether we have a lot of missing or unworkable data.

In [71]:
dete_survey.isnull().sum() # Summing all missing data per column
Out[71]:
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 [72]:
dete_survey.apply(pd.Series.value_counts)
/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/indexes/api.py:87: RuntimeWarning:

unorderable types: int() < str(), sort order is undefined for incomparable objects

/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/frame.py:6206: RuntimeWarning:

unorderable types: int() < str(), sort order is undefined for incomparable objects

/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/frame.py:6206: RuntimeWarning:

unorderable types: str() < int(), sort order is undefined for incomparable objects

Out[72]:
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
823 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
270 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
280 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
279 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
278 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
277 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
276 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
275 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
274 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
273 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
272 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
271 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
269 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
282 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
268 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
267 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
266 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
265 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
264 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
263 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
262 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
261 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
260 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
259 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
281 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
283 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
308 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
296 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
306 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
305 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
Pacific Pines SHS NaN NaN NaN NaN NaN NaN NaN NaN 1.0 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Finance NaN NaN NaN NaN NaN NaN NaN NaN 1.0 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Calliope State School NaN NaN NaN NaN NaN NaN NaN NaN 1.0 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Corporate Procurement NaN NaN NaN NaN NaN NaN NaN NaN 1.0 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Indigenous Education and Training Futures NaN NaN NaN NaN NaN NaN NaN NaN 1.0 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Permanent Full-time NaN NaN NaN NaN NaN NaN NaN NaN NaN 434.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Permanent Part-time NaN NaN NaN NaN NaN NaN NaN NaN NaN 308.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Temporary Full-time NaN NaN NaN NaN NaN NaN NaN NaN NaN 41.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Temporary Part-time NaN NaN NaN NaN NaN NaN NaN NaN NaN 24.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Casual NaN NaN NaN NaN NaN NaN NaN NaN NaN 10.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
False NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
A NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 401.0 253.0 386.0 NaN NaN NaN NaN NaN NaN NaN
SA NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 162.0 78.0 141.0 NaN NaN NaN NaN NaN NaN NaN
N NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 130.0 225.0 153.0 NaN NaN NaN NaN NaN NaN NaN
D NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 60.0 105.0 50.0 NaN NaN NaN NaN NaN NaN NaN
SD NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 50.0 72.0 35.0 NaN NaN NaN NaN NaN NaN NaN
M NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... 10.0 33.0 28.0 NaN NaN NaN NaN NaN NaN NaN
Female NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN 573.0 NaN NaN NaN NaN NaN NaN
Male NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN 225.0 NaN NaN NaN NaN NaN NaN
61 or older NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN 222.0 NaN NaN NaN NaN NaN
56-60 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN 174.0 NaN NaN NaN NaN NaN
51-55 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN 103.0 NaN NaN NaN NaN NaN
46-50 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN 63.0 NaN NaN NaN NaN NaN
41-45 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN 61.0 NaN NaN NaN NaN NaN
26-30 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN 57.0 NaN NaN NaN NaN NaN
36-40 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN 51.0 NaN NaN NaN NaN NaN
21-25 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN 40.0 NaN NaN NaN NaN NaN
31-35 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN 39.0 NaN NaN NaN NaN NaN
20 or younger NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN 1.0 NaN NaN NaN NaN NaN
Yes NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN 16.0 3.0 7.0 23.0 32.0

972 rows × 56 columns

Observations that can be made from exploring the DETE data:

  • Majority of missing values is found in Classification, Region, Aboriginal, Torres Strati, South Sea, Disability and NESB columns
  • There have been 434 full-time workers and 308 part-time workers
  • 573 women took the survey and 225 men
  • Elder participants, categories 51-55, 56-60 and 61+ have larger shares
  • 23 people have a disability

1.2 TAFE survey

Now let's consider the TAFE data and follow similar exploration techniques.

In [73]:
tafe_survey.info()
tafe_survey.head(5)
tafe_survey.dtypes.value_counts() 
<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
Out[73]:
object     70
float64     2
dtype: int64

Findings:

  • 702 rows and 72 columns
  • 2 columns have numeric data
  • 70 columns have textual data
In [74]:
tafe_survey.isnull().sum() # Summing all missing data per column
Out[74]:
Record ID                                                                                                                                                          0
Institute                                                                                                                                                          0
WorkArea                                                                                                                                                           0
CESSATION YEAR                                                                                                                                                     7
Reason for ceasing employment                                                                                                                                      1
Contributing Factors. Career Move - Public Sector                                                                                                                265
Contributing Factors. Career Move - Private Sector                                                                                                               265
Contributing Factors. Career Move - Self-employment                                                                                                              265
Contributing Factors. Ill Health                                                                                                                                 265
Contributing Factors. Maternity/Family                                                                                                                           265
Contributing Factors. Dissatisfaction                                                                                                                            265
Contributing Factors. Job Dissatisfaction                                                                                                                        265
Contributing Factors. Interpersonal Conflict                                                                                                                     265
Contributing Factors. Study                                                                                                                                      265
Contributing Factors. Travel                                                                                                                                     265
Contributing Factors. Other                                                                                                                                      265
Contributing Factors. NONE                                                                                                                                       265
Main Factor. Which of these was the main factor for leaving?                                                                                                     589
InstituteViews. Topic:1. I feel the senior leadership had a clear vision and direction                                                                            94
InstituteViews. Topic:2. I was given access to skills training to help me do my job better                                                                        89
InstituteViews. Topic:3. I was given adequate opportunities for personal development                                                                              92
InstituteViews. Topic:4. I was given adequate opportunities for promotion within %Institute]Q25LBL%                                                               94
InstituteViews. Topic:5. I felt the salary for the job was right for the responsibilities I had                                                                   87
InstituteViews. Topic:6. The organisation recognised when staff did good work                                                                                     95
InstituteViews. Topic:7. Management was generally supportive of me                                                                                                88
InstituteViews. Topic:8. Management was generally supportive of my team                                                                                           94
InstituteViews. Topic:9. I was kept informed of the changes in the organisation which would affect me                                                             92
InstituteViews. Topic:10. Staff morale was positive within the Institute                                                                                         100
InstituteViews. Topic:11. If I had a workplace issue it was dealt with quickly                                                                                   101
InstituteViews. Topic:12. If I had a workplace issue it was dealt with efficiently                                                                               105
                                                                                                                                                                ... 
WorkUnitViews. Topic:25. My job allowed me to balance the demands of work and family to my satisfaction                                                           91
WorkUnitViews. Topic:26. My supervisor gave me adequate personal recognition and feedback on my performance                                                       96
WorkUnitViews. Topic:27. My working environment was satisfactory e.g. sufficient space, good lighting, suitable seating and working area                          92
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     93
WorkUnitViews. Topic:29. There was adequate communication between staff in my unit                                                                                99
WorkUnitViews. Topic:30. Staff morale was positive within my work unit                                                                                            96
Induction. Did you undertake Workplace Induction?                                                                                                                 83
InductionInfo. Topic:Did you undertake a Corporate Induction?                                                                                                    270
InductionInfo. Topic:Did you undertake a Institute Induction?                                                                                                    219
InductionInfo. Topic: Did you undertake Team Induction?                                                                                                          262
InductionInfo. Face to Face Topic:Did you undertake a Corporate Induction; Category:How it was conducted?                                                        147
InductionInfo. On-line Topic:Did you undertake a Corporate Induction; Category:How it was conducted?                                                             147
InductionInfo. Induction Manual Topic:Did you undertake a Corporate Induction?                                                                                   147
InductionInfo. Face to Face Topic:Did you undertake a Institute Induction?                                                                                       172
InductionInfo. On-line Topic:Did you undertake a Institute Induction?                                                                                            147
InductionInfo. Induction Manual Topic:Did you undertake a Institute Induction?                                                                                   149
InductionInfo. Face to Face Topic: Did you undertake Team Induction; Category?                                                                                   147
InductionInfo. On-line Topic: Did you undertake Team Induction?process you undertook and how it was conducted.]                                                  147
InductionInfo. Induction Manual Topic: Did you undertake Team Induction?                                                                                         147
Workplace. Topic:Did you and your Manager develop a Performance and Professional Development Plan (PPDP)?                                                         94
Workplace. Topic:Does your workplace promote a work culture free from all forms of unlawful discrimination?                                                      108
Workplace. Topic:Does your workplace promote and practice the principles of employment equity?                                                                   115
Workplace. Topic:Does your workplace value the diversity of its employees?                                                                                       116
Workplace. Topic:Would you recommend the Institute as an employer to others?                                                                                     121
Gender. What is your Gender?                                                                                                                                     106
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
In [75]:
tafe_survey.apply(pd.Series.value_counts)
/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/indexes/api.py:87: RuntimeWarning:

unorderable types: float() < str(), sort order is undefined for incomparable objects

/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/frame.py:6206: RuntimeWarning:

unorderable types: float() < str(), sort order is undefined for incomparable objects

/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/frame.py:6206: RuntimeWarning:

unorderable types: str() < float(), sort order is undefined for incomparable objects

Out[75]:
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)
6.34219394328258e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34992898379375e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34208063783969e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34595096448594e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34171929735346e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34329804601719e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34260719918915e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34568388536875e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34638198687656e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34577056474375e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34568317773125e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34982246838125e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34867257481719e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34849850334844e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34794857718438e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34480996232969e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34733451073235e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.3432311502146e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34552909616094e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34541462557656e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34587326853281e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34684858305469e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34753321028079e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34735049954641e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34743600339485e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34852503880156e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34761898333619e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34570821134531e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.34686807219219e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
6.3458231357125e+17 1.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
56 or older NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN 162.0 NaN NaN NaN NaN
51-55 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN 82.0 NaN NaN NaN NaN
41 45 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN 80.0 NaN NaN NaN NaN
46 50 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN 59.0 NaN NaN NaN NaN
31 35 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN 52.0 NaN NaN NaN NaN
36 40 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN 51.0 NaN NaN NaN NaN
26 30 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN 50.0 NaN NaN NaN NaN
21 25 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN 44.0 NaN NaN NaN NaN
20 or younger NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN 16.0 NaN NaN NaN NaN
Permanent Full-time NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN 237.0 NaN NaN NaN
Temporary Full-time NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN 177.0 NaN NaN NaN
Contract/casual NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN 71.0 NaN NaN NaN
Permanent Part-time NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN 59.0 NaN NaN NaN
Temporary Part-time NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN 52.0 NaN NaN NaN
Administration (AO) NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN 293.0 NaN NaN
Teacher (including LVT) NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN 201.0 NaN NaN
Professional Officer (PO) NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN 33.0 NaN NaN
Operational (OO) NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN 24.0 NaN NaN
Tutor NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN 16.0 NaN NaN
Workplace Training Officer NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN 11.0 NaN NaN
Technical Officer (TO) NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN 10.0 NaN NaN
Executive (SES/SO) NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN 6.0 NaN NaN
Apprentice NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN 2.0 NaN NaN
Less than 1 year NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN 147.0 177.0
1-2 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN 102.0 113.0
3-4 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN 96.0 86.0
11-20 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN 89.0 82.0
More than 20 years NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN 71.0 54.0
5-6 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN 48.0 40.0
7-10 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN 43.0 44.0

784 rows × 72 columns

Observations:

  • 589 missing values for the main factor of leaving
  • 265 missing values for each of the contributing factors of leaving
  • varying number of missing values for other questions, but roughly 100 on average
  • Since it is a survey there is a large distribution of unique values
  • Only for the ranges of years for length of service similar values exist
  • Participants that have worked for a short period are more represented in the study

2. Data Cleaning

Next, we determine which data we need to leave out and which data needs to be modified in order to be useful. We have observed that Not Stated needs to be transformed to NaN, so we will first re-read the .csv file. In addition, we can reduce the dataset to data that we explicitly need for our analysis by eliminating columns that we do not use.

In [76]:
dete_survey = pd.read_csv("dete_survey.csv", na_values="Not Stated")

# Drop irrelevant columns
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)

# Explore our new datasets
dete_survey_updated.head() # Change to 50 to see that Region has NaN values now
Out[76]:
ID SeparationType Cease Date DETE Start Date Role Start Date Position Classification Region Business Unit Employment Status ... Work life balance Workload None of the above Gender Age Aboriginal Torres Strait South Sea Disability NESB
0 1 Ill Health Retirement 08/2012 1984.0 2004.0 Public Servant A01-A04 Central Office Corporate Strategy and Peformance Permanent Full-time ... False False True Male 56-60 NaN NaN NaN NaN Yes
1 2 Voluntary Early Retirement (VER) 08/2012 NaN NaN Public Servant AO5-AO7 Central Office Corporate Strategy and Peformance Permanent Full-time ... False False False Male 56-60 NaN NaN NaN NaN NaN
2 3 Voluntary Early Retirement (VER) 05/2012 2011.0 2011.0 Schools Officer NaN Central Office Education Queensland Permanent Full-time ... False False True Male 61 or older NaN NaN NaN NaN NaN
3 4 Resignation-Other reasons 05/2012 2005.0 2006.0 Teacher Primary Central Queensland NaN Permanent Full-time ... False False False Female 36-40 NaN NaN NaN NaN NaN
4 5 Age Retirement 05/2012 1970.0 1989.0 Head of Curriculum/Head of Special Education NaN South East NaN Permanent Full-time ... True False False Female 61 or older NaN NaN NaN NaN NaN

5 rows × 35 columns

In [77]:
tafe_survey_updated.head()
Out[77]:
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. 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)
0 6.341330e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2010.0 Contract Expired NaN NaN NaN NaN NaN ... NaN NaN NaN NaN Female 26 30 Temporary Full-time Administration (AO) 1-2 1-2
1 6.341337e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2010.0 Retirement - - - - - ... - Travel - - NaN NaN NaN NaN NaN NaN
2 6.341388e+17 Mount Isa Institute of TAFE Delivery (teaching) 2010.0 Retirement - - - - - ... - - - NONE NaN NaN NaN NaN NaN NaN
3 6.341399e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - Travel - - NaN NaN NaN NaN NaN NaN
4 6.341466e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - - Male 41 45 Permanent Full-time Teacher (including LVT) 3-4 3-4

5 rows × 23 columns

2.1 Column consistency

Subsequently, let's focus on the differences between columns names of the above presented data structures. It appears that both data sets use different names for similar subjects. For example:

  • ID versus Record ID
  • Age versus CurrentAge. Current Age
  • Gender versus Gender. What is your Gender?

Let's consider both data sets separately and update the column names to a single format.

In [78]:
dete_survey_updated.columns = dete_survey_updated.columns.str.lower().str.strip().str.replace(' ', '_')
In [79]:
tafe_survey_updated =  tafe_survey_updated.rename(
                           {'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'
                            },
                             axis=1
                           )
In [80]:
dete_survey_updated.columns
Out[80]:
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 [81]:
tafe_survey_updated.columns
Out[81]:
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',
       'age', 'employment_status', 'position', 'institute_service',
       'role_service'],
      dtype='object')

In the above cells it can be seen that the columns that we are willing to evaluate for our research question have similar notations (e.g. id, cease_date). This is important to compare both data sets on column values and derive meaningful conclusions.

2.2 Resignation types

Now, let's count the reasons for leaving an employer for both data sets. We are only focusing on employees who have resignated and therefore filter these data from the data sets.

In [82]:
dete_survey_updated['separationtype'].value_counts()
dete_resignations = dete_survey_updated[dete_survey_updated['separationtype'].str.contains('Resignation')] 
In [83]:
tafe_survey_updated['separationtype'].value_counts()
tafe_resignations = tafe_survey_updated[tafe_survey_updated['separationtype'] == 'Resignation']

The above operations for both data sets derive each row with a clear resignation reason as separation. These are the rows that we will continue work with.

2.3 Date verification

Next, the starting and ending dates that are filled in the survey are evaluated. It would not make sense if the starting date is in the future or the beginning date is prior to 1940. The latter is not realiastic when we assume that a person is approximately 20 years when he/she begins and would now be over 100 years. Therefore, for those two exceptions we assume that the data is inaccurate and must be removed from the data sets.

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

It appears that besides the year as input, some respondents have entered the month in which they have started. Let's delete the months and solemny focus on the years that the respondents started to work.

In [85]:
# Converting years to strings to strip unwanted characters and store years
dete_resignations['cease_date'] = dete_resignations['cease_date'].astype('str')
dete_resignations['cease_date'] = dete_resignations['cease_date'].str.strip().replace("/","")
dete_resignations['cease_date'] = dete_resignations['cease_date'].str[-4:]

# Restore as integers and check whether there are remaining missing values
dete_resignations['cease_date'] = dete_resignations['cease_date'].astype('float')
dete_resignations['cease_date'].value_counts(dropna=False).sort_index(ascending=True)
Out[85]:
 2006.0      1
 2010.0      2
 2012.0    129
 2013.0    146
 2014.0     22
NaN         11
Name: cease_date, dtype: int64
In [86]:
dete_resignations['dete_start_date'].value_counts(dropna=False).sort_index(ascending=True)
Out[86]:
 1963.0     1
 1971.0     1
 1972.0     1
 1973.0     1
 1974.0     2
 1975.0     1
 1976.0     2
 1977.0     1
 1980.0     5
 1982.0     1
 1983.0     2
 1984.0     1
 1985.0     3
 1986.0     3
 1987.0     1
 1988.0     4
 1989.0     4
 1990.0     5
 1991.0     4
 1992.0     6
 1993.0     5
 1994.0     6
 1995.0     4
 1996.0     6
 1997.0     5
 1998.0     6
 1999.0     8
 2000.0     9
 2001.0     3
 2002.0     6
 2003.0     6
 2004.0    14
 2005.0    15
 2006.0    13
 2007.0    21
 2008.0    22
 2009.0    13
 2010.0    17
 2011.0    24
 2012.0    21
 2013.0    10
NaN        28
Name: dete_start_date, dtype: int64
In [87]:
tafe_resignations['cease_date'].value_counts(dropna=False)
Out[87]:
 2011.0    116
 2012.0     94
 2010.0     68
 2013.0     55
NaN          5
 2009.0      2
Name: cease_date, dtype: int64
In [88]:
dete_start = dete_resignations['dete_start_date']
dete_cease = dete_resignations['cease_date']
tafe_cease = tafe_resignations['cease_date']

df_resignations = pd.concat([dete_start, dete_cease, tafe_cease], axis=1)
df_resignations = df_resignations.dropna(subset=['dete_start_date',
                                                'cease_date']
                                        )
In [89]:
# PLEASE RUN THE LINE BELOW TO SEE MY QUESTION
ax = sns.boxplot(data = df_resignations, palette="Blues_d")

# ax = sns.boxplot(df_resignations.dete_start,
#                  df_resignations.dete_cease,
#                  df_resignations.tafe_cease,
#                  palette="Blues_d")

ax.set_xticklabels(ax.get_xticklabels(),rotation=30)
/dataquest/system/env/python3/lib/python3.4/site-packages/seaborn/categorical.py:454: FutureWarning:

remove_na is deprecated and is a private function. Do not use.

Out[89]:
[<matplotlib.text.Text at 0x7f297872eac8>,
 <matplotlib.text.Text at 0x7f29787313c8>,
 <matplotlib.text.Text at 0x7f2978715ba8>,
 <matplotlib.text.Text at 0x7f29787186a0>,
 <matplotlib.text.Text at 0x7f297871c198>]

In the above lines we have stored the starting and ending dates in series and combined them in a DataFrame. It is interesting to see that most ceasing dates are in 2012 or 2013, while, obviously, the starting dates are spear more evenly from now until 1980.

2.4 Combing job dissatisfaction

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

To answer this question, let's see what perion our respondents have been working at their institutions.

In [90]:
dete_resignations = dete_resignations.dropna(subset=['dete_start_date',
                                                'cease_date',]
                                        )

dete_resignations['institute_service'] = dete_cease.sub(dete_start, fill_value=0)
dete_resignations['institute_service'].value_counts()
Out[90]:
5.0     23
1.0     22
3.0     20
0.0     20
6.0     17
4.0     16
9.0     14
2.0     14
7.0     13
13.0     8
8.0      8
20.0     7
15.0     7
10.0     6
22.0     6
14.0     6
17.0     6
12.0     6
16.0     5
18.0     5
23.0     4
11.0     4
24.0     4
39.0     3
19.0     3
21.0     3
32.0     3
28.0     2
26.0     2
25.0     2
30.0     2
36.0     2
29.0     1
33.0     1
42.0     1
27.0     1
41.0     1
35.0     1
38.0     1
34.0     1
49.0     1
31.0     1
Name: institute_service, dtype: int64

Above the results are shown which indictes two things:

  • There are relatively many respondents who have worked for a short period at their employer (< 5 years)
  • There are negative values which indicates that these respondents did not enter a starting date

3.1 Job Dissatisfaction

Due to the large number of cessations observed in the first years of working, it is good to check the underlying reasons for this. Below we create a function to update values and check the dissatisfaction reasons of the cessations.

In [91]:
def update_vals(val):
    if pd.isnull(val):
        return np.nan
    elif val == '-':
        return False
    else:
        return True
    
tafe_dissatisfied =  tafe_resignations[['Contributing Factors. Dissatisfaction', 
                   'Contributing Factors. Job Dissatisfaction']].applymap(update_vals)  
    

In the above cells it can be seen that our function distinguishes the reasons for which employees tend to be dissatisfied and might leave their employer. Now, let's seperate the data from the

In [92]:
tafe_resignations['dissatisfied'] = tafe_dissatisfied.any(axis=1, skipna=False)
tafe_resignations_up = tafe_resignations.copy()
tafe_resignations_up['dissatisfied'].value_counts(dropna=False)
Out[92]:
False    241
True      91
NaN        8
Name: dissatisfied, dtype: int64

From the outcomes observed above we see that 91 respondents resigned since they qualify as being dissatisfied with their job in the tafe survey. For 241 respondents the resignation reason is something else and therefore we cannot assume with certainty that these respondents have been dissatisfied with their job.

Now let's perform the same steps for the tafe survey.

In [93]:
cols_dissatisfied = ['job_dissatisfaction',
                    'dissatisfaction_with_the_department',
                    'physical_work_environment',
                    'lack_of_recognition',
                    'lack_of_job_security',
                    'work_location',
                    'employment_conditions',
                    'work_life_balance',
                    'workload']

dete_resignations[cols_dissatisfied].head(5)
Out[93]:
job_dissatisfaction dissatisfaction_with_the_department physical_work_environment lack_of_recognition lack_of_job_security work_location employment_conditions work_life_balance workload
3 False False False False False False False False False
5 False False False False False False True False False
8 False False False False False False False False False
9 True True False False False False False False False
11 False False False False False False False False False
In [94]:
dete_resignations['dissatisfied'] = dete_resignations[cols_dissatisfied].any(axis=1, skipna=False)
dete_resignations_up = dete_resignations.copy()
dete_resignations_up['dissatisfied'].value_counts(dropna=False)
Out[94]:
True     137
False    136
Name: dissatisfied, dtype: int64

From the dete resignations, it appears that there are 149 respondents that left their employers due to dissatisfaction with their job. In contrast to the tafe survey, we observe that almost 50% of these respondents have been dissatisfied. To avoid drawing conclusions to quickly, we must understand that there are more columns in which respondents can provide signs of dissatisfaction, hence resulting in a higher dissatisfaction rate than the tafe survey.

3 Data Aggregation

Now that our data is workable and we have erased most of the common flaws such as missing values and unrealistic dates, let's combine the datasets together to work from the same dataset.

3.1 Combining the surveys

Our end goal is to aggregate the data according to the institute_service column, which is performed in the next steps.

In [95]:
dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'

combined = pd.concat([dete_resignations_up, tafe_resignations_up], axis=0)
print(combined.shape)
combined_updated = combined.dropna(axis=1, thresh=500)
print(combined_updated.shape)
(613, 53)
(613, 10)

We finally have combined the tafe and dete survey into one dataframe. Since values were still missing in a a number of columns, we dropped the ones that provide limited information in that sense. It is noticable that we have removed 43 columns that qualified as having fewer than 500 non-null cells that we consider as the threshold for being meaningful.

In [96]:
combined_updated
Out[96]:
age cease_date dissatisfied employment_status gender id institute institute_service position separationtype
3 36-40 2012.0 False Permanent Full-time Female 4.000000e+00 DETE 7 Teacher Resignation-Other reasons
5 41-45 2012.0 True Permanent Full-time Female 6.000000e+00 DETE 18 Guidance Officer Resignation-Other reasons
8 31-35 2012.0 False Permanent Full-time Female 9.000000e+00 DETE 3 Teacher Resignation-Other reasons
9 46-50 2012.0 True Permanent Part-time Female 1.000000e+01 DETE 15 Teacher Aide Resignation-Other employer
11 31-35 2012.0 False Permanent Full-time Male 1.200000e+01 DETE 3 Teacher Resignation-Move overseas/interstate
12 36-40 2012.0 False Permanent Full-time Female 1.300000e+01 DETE 14 Teacher Resignation-Other reasons
14 31-35 2012.0 True Permanent Full-time Male 1.500000e+01 DETE 5 Teacher Resignation-Other employer
20 56-60 2012.0 False Permanent Full-time Male 2.100000e+01 DETE 30 Teacher Resignation-Other employer
21 51-55 2012.0 False Permanent Part-time Female 2.200000e+01 DETE 32 Cleaner Resignation-Other reasons
22 46-50 2012.0 True Permanent Part-time Female 2.300000e+01 DETE 15 School Administrative Staff Resignation-Other reasons
23 61 or older 2012.0 True Permanent Full-time Female 2.400000e+01 DETE 39 Teacher Resignation-Other reasons
25 41-45 2012.0 True Permanent Part-time Female 2.600000e+01 DETE 17 Teacher Resignation-Other reasons
27 21-25 2012.0 False Permanent Full-time Female 2.800000e+01 DETE 7 Public Servant Resignation-Other employer
33 36-40 2012.0 True Permanent Full-time Male 3.400000e+01 DETE 9 Teacher Resignation-Other reasons
34 61 or older 2012.0 True Permanent Part-time Male 3.500000e+01 DETE 6 Cleaner Resignation-Other reasons
37 21-25 2012.0 False Temporary Part-time Female 3.800000e+01 DETE 1 Teacher Aide Resignation-Other reasons
40 56-60 2012.0 False Permanent Full-time Male 4.100000e+01 DETE 35 Teacher Resignation-Other employer
41 51-55 2012.0 True Permanent Full-time Female 4.200000e+01 DETE 38 Head of Curriculum/Head of Special Education Resignation-Other reasons
42 41-45 2012.0 False Permanent Part-time Female 4.300000e+01 DETE 1 Cleaner Resignation-Move overseas/interstate
43 51-55 2012.0 True Permanent Full-time Male 4.400000e+01 DETE 36 Teacher Resignation-Other reasons
48 21-25 2012.0 False Permanent Full-time Male 4.900000e+01 DETE 3 Cleaner Resignation-Move overseas/interstate
50 21-25 2012.0 False Permanent Full-time Male 5.100000e+01 DETE 3 Cleaner Resignation-Move overseas/interstate
51 61 or older 2012.0 False Permanent Full-time Female 5.200000e+01 DETE 19 Cleaner Resignation-Other reasons
55 26-30 2012.0 False Permanent Part-time Female 5.600000e+01 DETE 4 Teacher Aide Resignation-Other employer
57 46-50 2012.0 False Permanent Full-time Male 5.800000e+01 DETE 9 Teacher Resignation-Other employer
61 31-35 2012.0 False Temporary Part-time Female 6.200000e+01 DETE 1 Schools Officer Resignation-Other reasons
69 36-40 2012.0 True Permanent Full-time Female 7.000000e+01 DETE 6 Public Servant Resignation-Other reasons
71 36-40 2012.0 False Permanent Part-time Female 7.200000e+01 DETE 1 Teacher Aide Resignation-Other reasons
87 26-30 2012.0 False Permanent Full-time Female 8.800000e+01 DETE 5 Teacher Resignation-Move overseas/interstate
90 41-45 2012.0 False Permanent Part-time Female 9.100000e+01 DETE 26 Teacher Aide Resignation-Other employer
... ... ... ... ... ... ... ... ... ... ...
659 46 50 2013.0 False Temporary Part-time Female 6.349985e+17 TAFE 1-2 Administration (AO) Resignation
660 41 45 2013.0 False Permanent Part-time Female 6.349994e+17 TAFE 3-4 Administration (AO) Resignation
661 46 50 2013.0 True Permanent Full-time Female 6.350003e+17 TAFE 5-6 Administration (AO) Resignation
665 NaN 2013.0 False NaN NaN 6.350055e+17 TAFE NaN NaN Resignation
666 NaN 2013.0 False NaN NaN 6.350055e+17 TAFE NaN NaN Resignation
669 26 30 2013.0 False Temporary Full-time Female 6.350108e+17 TAFE 3-4 Administration (AO) Resignation
670 NaN 2013.0 NaN NaN NaN 6.350124e+17 TAFE NaN NaN Resignation
671 46 50 2013.0 True Temporary Full-time Female 6.350127e+17 TAFE Less than 1 year Teacher (including LVT) Resignation
675 51-55 2013.0 True Temporary Full-time Male 6.350175e+17 TAFE Less than 1 year Teacher (including LVT) Resignation
676 41 45 2013.0 False Contract/casual Female 6.350194e+17 TAFE 1-2 Administration (AO) Resignation
677 36 40 2013.0 False Temporary Full-time Female 6.350219e+17 TAFE Less than 1 year Administration (AO) Resignation
678 51-55 2013.0 False Permanent Full-time Male 6.350253e+17 TAFE 3-4 Administration (AO) Resignation
679 56 or older 2013.0 False Temporary Part-time Female 6.350279e+17 TAFE 1-2 Operational (OO) Resignation
681 26 30 2013.0 False Temporary Full-time Female 6.350314e+17 TAFE Less than 1 year Administration (AO) Resignation
682 26 30 2013.0 False Permanent Part-time Female 6.350357e+17 TAFE Less than 1 year Administration (AO) Resignation
683 41 45 2013.0 False Temporary Full-time Female 6.350374e+17 TAFE Less than 1 year Administration (AO) Resignation
684 41 45 2013.0 False Contract/casual Male 6.350375e+17 TAFE 3-4 Administration (AO) Resignation
685 26 30 2013.0 True Temporary Full-time Female 6.350402e+17 TAFE 1-2 Technical Officer (TO) Resignation
686 41 45 2013.0 False Temporary Full-time Female 6.350426e+17 TAFE 5-6 Administration (AO) Resignation
688 46 50 2013.0 False Permanent Part-time Female 6.350479e+17 TAFE 5-6 Professional Officer (PO) Resignation
689 41 45 2013.0 True Permanent Full-time Male 6.350480e+17 TAFE Less than 1 year Teacher (including LVT) Resignation
690 NaN 2013.0 False NaN NaN 6.350496e+17 TAFE NaN NaN Resignation
691 56 or older 2013.0 False Permanent Part-time Female 6.350496e+17 TAFE 3-4 Operational (OO) Resignation
693 26 30 2013.0 False Temporary Full-time Female 6.350599e+17 TAFE 1-2 Administration (AO) Resignation
694 NaN 2013.0 False NaN NaN 6.350652e+17 TAFE NaN NaN Resignation
696 21 25 2013.0 False Temporary Full-time Male 6.350660e+17 TAFE 5-6 Operational (OO) Resignation
697 51-55 2013.0 False Temporary Full-time Male 6.350668e+17 TAFE 1-2 Teacher (including LVT) Resignation
698 NaN 2013.0 False NaN NaN 6.350677e+17 TAFE NaN NaN Resignation
699 51-55 2013.0 False Permanent Full-time Female 6.350704e+17 TAFE 5-6 Teacher (including LVT) Resignation
701 26 30 2013.0 False Contract/casual Female 6.350730e+17 TAFE 3-4 Administration (AO) Resignation

613 rows × 10 columns

3.2 Categorizing company years

We observe that values in the institute_service column still have values that are difficult to compare. For example, it can be seen that there are cells that include words such as less than or more than in the cells. In addition, we want to categorize values based on years at a company and do this as follows:

  • New: Less than 3 years at a company
  • Experienced: 3-6 years at a company
  • Established: 7-10 years at a company
  • Veteran: 11 or more years at a company
In [97]:
combined_updated = combined_updated.dropna(subset=['institute_service'])

combined_updated['institute_service'].value_counts()
Out[97]:
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
20.0                   7
15.0                   7
10.0                   6
12.0                   6
14.0                   6
17.0                   6
22.0                   6
16.0                   5
18.0                   5
11.0                   4
24.0                   4
23.0                   4
19.0                   3
21.0                   3
39.0                   3
32.0                   3
25.0                   2
26.0                   2
28.0                   2
30.0                   2
36.0                   2
27.0                   1
29.0                   1
33.0                   1
35.0                   1
38.0                   1
31.0                   1
41.0                   1
42.0                   1
49.0                   1
34.0                   1
Name: institute_service, dtype: int64

We observe that there are two values that are recurring in which string characters are found: Less than 1 year and More than 20 years. If we follow the previous mentioned categorization, these values can be modified to end up in new and veteran categrories, respectively. Lets filter out all the string characters als leave the digits.

In [98]:
# Set data type to string to perform string operations
combined_updated['institute_service'] = combined_updated['institute_service'].astype('str')
combined_updated['institute_service'] = combined_updated['institute_service'].str.split(pat='-').str[0]
combined_updated['institute_service'] = combined_updated['institute_service'].str.split(pat='.').str[0]

# Function that only stores digits of a string
def digit_only(s):   
    s = ''.join(filter(str.isdigit, s))
    return s

# Apply function and set data type back to float
combined_updated['institute_service'] = combined_updated['institute_service'].apply(digit_only)
combined_updated['institute_service'] = combined_updated['institute_service'].astype('float')

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

Now the institute_service data is removed from unwanted characters and can be grouped according to the previously announced categorizations.

In [99]:
def career_cat(year):
    if year < 3:
        return 'New'
    elif (year > 2) and (year < 7):
        return 'Experienced'
    elif (year > 6) and (year < 10):
        return 'Established'
    else:
        return 'Veteran'

combined_updated['service_cat'] = combined_updated['institute_service'].map(career_cat)

counts = combined_updated['service_cat'].value_counts()

ax = sns.countplot(data=combined_updated, x='service_cat', hue='gender',  
                 palette="Blues_d")        
/dataquest/system/env/python3/lib/python3.4/site-packages/seaborn/categorical.py:1468: FutureWarning:

remove_na is deprecated and is a private function. Do not use.

We observe that there are much more women participated in the studies.

4. Data Analysis

Finally we are able to use the combined data set to determine the dissatisfaction among participants of the surveys.

In [100]:
combined_updated['dissatisfied'].value_counts(dropna=False)
Out[100]:
False    349
True     214
Name: dissatisfied, dtype: int64
In [101]:
pd.pivot_table(combined_updated, index=['service_cat'], 
               values=['dissatisfied'], aggfunc='sum')
Out[101]:
dissatisfied
service_cat
Established 31
Experienced 59
New 57
Veteran 67
In [102]:
ax = sns.countplot(data=combined_updated, 
                   x='dissatisfied', 
                 hue='service_cat',  
                 palette="Blues_d")
/dataquest/system/env/python3/lib/python3.4/site-packages/seaborn/categorical.py:1468: FutureWarning:

remove_na is deprecated and is a private function. Do not use.

In [103]:
combined_updated.columns
Out[103]:
Index(['age', 'cease_date', 'dissatisfied', 'employment_status', 'gender',
       'id', 'institute', 'institute_service', 'position', 'separationtype',
       'service_cat'],
      dtype='object')
In [104]:
ax = sns.countplot(data=combined_updated, x='position', 
                    hue='dissatisfied',  
                 palette="Blues_d")

ax.set_xticklabels(ax.get_xticklabels(),rotation=90)
/dataquest/system/env/python3/lib/python3.4/site-packages/seaborn/categorical.py:1468: FutureWarning:

remove_na is deprecated and is a private function. Do not use.

Out[104]:
[<matplotlib.text.Text at 0x7f2978a95cc0>,
 <matplotlib.text.Text at 0x7f29789a8160>,
 <matplotlib.text.Text at 0x7f2978b25208>,
 <matplotlib.text.Text at 0x7f2978b0e940>,
 <matplotlib.text.Text at 0x7f2978b0eef0>,
 <matplotlib.text.Text at 0x7f2978c470f0>,
 <matplotlib.text.Text at 0x7f2978c3feb8>,
 <matplotlib.text.Text at 0x7f2978c3fb00>,
 <matplotlib.text.Text at 0x7f2979e15518>,
 <matplotlib.text.Text at 0x7f2978bf0160>,
 <matplotlib.text.Text at 0x7f2979df5748>,
 <matplotlib.text.Text at 0x7f2978b7f9b0>,
 <matplotlib.text.Text at 0x7f2978b86278>,
 <matplotlib.text.Text at 0x7f2978b861d0>,
 <matplotlib.text.Text at 0x7f2978c1eb38>,
 <matplotlib.text.Text at 0x7f2979cb82e8>,
 <matplotlib.text.Text at 0x7f2979cb8390>,
 <matplotlib.text.Text at 0x7f2978bfe4a8>,
 <matplotlib.text.Text at 0x7f2978b97208>,
 <matplotlib.text.Text at 0x7f2978b97ac8>,
 <matplotlib.text.Text at 0x7f2978ba3a58>]
In [105]:
ax = sns.countplot(data=combined_updated, x='institute', 
                    hue='dissatisfied',  
                 palette="Blues_d")

ax.set_xticklabels(ax.get_xticklabels(),rotation=90)
/dataquest/system/env/python3/lib/python3.4/site-packages/seaborn/categorical.py:1468: FutureWarning:

remove_na is deprecated and is a private function. Do not use.

Out[105]:
[<matplotlib.text.Text at 0x7f2978ae6f98>,
 <matplotlib.text.Text at 0x7f297a846908>]

Conclusion

This project has cleaned, aggregated and analysed two institute data sets to determine workplace dissatisfaction. Main observations are:

  • There are women participating in the surveys, thus larger absolute number of dissatisfied women
  • There is a correlation in time of service and dissatisfaction
  • Teachers, cleaners and public servants are relatively dissatisfied compared to other positions
  • DETE survey participants are 50% of the times dissatisfied, while for TAFE survey participants this is approximately 25%

In conclusion, there are multiple indicators that show correlations with workplace dissatisfaction. If dissatisfied respondents are more willing to participate in surveys that may bias the results is an interesting future study.