Analysis of Employee Exit Interviews

Introduction

In this project, we will be analyzing the exit surveys of two public institutes based out of Queensland, Australia; the Department of Education, Training, and Employement (DETE) and the Technical and Further Education Institute (TAFE).

We will be assuming the role of the in-house data analyst, and we have been tasked with presenting answers to the following questions:

  • Are employees who worked for only a short term resigning due to dissatisfaction? Long-term employees?
  • Are younger employees resigning due to dissatifation? Older employees?

In order to better understand how to answer these questions, we will be looking through datasets of exit interviews from the institutes. These can be found here for DETE and here for TAFE.

The datasets are fundamentally different in how they set themselves up. As such, we will be combining surveys to better answer the above questions. Therefore, the main objective of this project is to better understand and utilize methods for data cleaning within the python libraries, and detailed explanations will be provided throughout.

Importing Libraries

We can begin by importing all necessary libaries, in this instance only pandas and NumPy are needed for their ability to work with DataFrame strucutres.

In [1]:
import pandas as pd
import numpy as np

###Checking for successful import###

if pd and np:
    print('Import Successful')
Import Successful

Creating the DataFrames

In [2]:
dete_survey = pd.read_csv('dete_survey.csv') #pd.read_csv reads in a .csv file as a DataFrame

tafe_survey = pd.read_csv('tafe_survey.csv') 

Initial Exploration

We can take a look at the first line of each DataFrame to better understand the variables within.

In [3]:
dete_survey.head() #.head(n) returns the first n columns, 5 if unspecified
Out[3]:
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 12-Aug 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) 12-Aug 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) 12-May 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 12-May 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 12-May 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 rows × 56 columns

In [4]:
dete_survey.info() #Displays all headers, as well as # of non-null values and value type(obj, bool, etc)
<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 [5]:
tafe_survey.head()
Out[5]:
Record ID Institute WorkArea CESSATION YEAR Reason for ceasing employment Contributing Factors. Career Move - Public Sector Contributing Factors. Career Move - Private Sector Contributing Factors. Career Move - Self-employment Contributing Factors. Ill Health Contributing Factors. Maternity/Family ... Workplace. Topic:Does your workplace promote a work culture free from all forms of unlawful discrimination? Workplace. Topic:Does your workplace promote and practice the principles of employment equity? Workplace. Topic:Does your workplace value the diversity of its employees? Workplace. Topic:Would you recommend the Institute as an employer to others? Gender.     What is your Gender? CurrentAge.     Current Age Employment Type.     Employment Type Classification.     Classification LengthofServiceOverall. Overall Length of Service at Institute (in years) LengthofServiceCurrent. Length of Service at current workplace (in years)
0 6.340000e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2010.0 Contract Expired NaN NaN NaN NaN NaN ... Yes Yes Yes Yes Female 26 – 30 Temporary Full-time Administration (AO) 2-Jan 2-Jan
1 6.340000e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2010.0 Retirement - - - - - ... Yes Yes Yes Yes NaN NaN NaN NaN NaN NaN
2 6.340000e+17 Mount Isa Institute of TAFE Delivery (teaching) 2010.0 Retirement - - - - - ... Yes Yes Yes Yes NaN NaN NaN NaN NaN NaN
3 6.340000e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... Yes Yes Yes Yes NaN NaN NaN NaN NaN NaN
4 6.340000e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - Career Move - Private Sector - - - ... Yes Yes Yes Yes Male 41 – 45 Permanent Full-time Teacher (including LVT) 4-Mar 4-Mar

5 rows × 72 columns

In [6]:
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

Upon initial inspection, a few points stand out:

  • The surveys largely tackle the same questions, but they do so with different variable names, for example:

    • Cease Date/CESSATION YEAR
    • SeparationType/Reason for ceasing employment
    • Position/Work Area
  • Many columns in both sets are not neccessary for our analysis.
  • DETE's survey contains values that read 'Not Stated', which are being counted differently than NaN null values.
  • Both have questions with answers assigned based on the Likert Scale. While TAFE has the answers written out (Strongly Agree), DETE abbreviates (SA). These will likely be removed. Similarly, both datasets contain Boolean objets.
  • The datasets both contain large amounts of null values, presumed to be missing data; below, we can utilize the .isnull() and .value_counts() operators to find where the missing data lies.
In [7]:
missing_dete = dete_survey.isnull().sum() #sums all null values by column

missing_dete
Out[7]:
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

_detesurvey has several null values in the last 5 columns, which are therefore unlikely to be of use to us for our analysis. Classification and Business Unit are the only others with null values numbering over 100, therefore the rest might be able to be extrapolated to be filled.

In [8]:
missing_tafe = tafe_survey.isnull().sum()

missing_tafe
Out[8]:
Record ID                                                                      0
Institute                                                                      0
WorkArea                                                                       0
CESSATION YEAR                                                                 7
Reason for ceasing employment                                                  1
                                                                            ... 
CurrentAge.     Current Age                                                  106
Employment Type.     Employment Type                                         106
Classification.     Classification                                           106
LengthofServiceOverall. Overall Length of Service at Institute (in years)    106
LengthofServiceCurrent. Length of Service at current workplace (in years)    106
Length: 72, dtype: int64

_tafesurvey has similar amounts of null values for several columns, which opens up the possibility of simply being able to delete the specific rows from our analysis.

Identify Missing Values

Because there are 'Not Stated' values in the DETE dataset that are essentially null, we can use the pd.read_csv's na_values parameter to set those to null.

In [9]:
###Specifying 'Not Stated' as NaN values###

dete_survey = pd.read_csv('dete_survey.csv', na_values='Not Stated')

Dropping Columns

In [10]:
###Using the .drop() method to specify all unneccessary columns and remove them.###

dete_survey_updated = dete_survey.drop(dete_survey.columns[28:49], axis=1)

###Using .shape attribute to check###

dete_survey_updated.shape #Should be 35 columns now
Out[10]:
(822, 35)
In [11]:
tafe_survey_updated = tafe_survey.drop(tafe_survey.columns[17:66], axis=1)

tafe_survey_updated.shape #Should be 23 columns now
Out[11]:
(702, 23)

Above, we dropped 21 columns from the DETE dataset, and 59 from TAFE. These were unneccessary for our analysis. Many contained a large majority of null values, others were based on Likert Scale questionnaires. It will make the datasets much easier to work with going forward without these columns.

Renaming Columns

Many of the columns we want to use in our analysis have differing names across the datasets, we need to use vectorized string methods to align them with one another.

Below, we start with _dete_surveyupdated's column names

In [12]:
###Updating Column Names in dete_survey (snake_case)###
#Example: Cease Date -> cease_date

dete_survey_updated.columns = dete_survey_updated.columns.str.lower().str.strip().str.replace(' ', '_')

#str.lower() sets all columns to lowercase
#str.strip() removes all whitespace at ends of strings
#str.replace() replaces all spaces in titles with underscores

###Changing separationtype for continuity###

dete_survey_updated.rename(columns={'separationtype': 'separation_type'}, inplace=True)

###Printing column index to ensure changes###

dete_survey_updated.columns
Out[12]:
Index(['id', 'separation_type', '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')

Next, we can use the .rename() function to change some column names of _tafe_surveyupdated to match those of the DETE dataset. This will make it easier to merge the datasets in later steps.

In [13]:
###Updating Important Column Names in tafe_survey###

cols = {
    'Record ID': 'id',
    'CESSATION YEAR': 'cease_date',
    'Reason for ceasing employment': 'separation_type',
    'Gender.     What is your Gender?': 'gender',
    'CurrentAge.     Current Age': 'age',
    'Employment Type.     Employment Type': 'employment_status',
    'Classification.     Classification': 'position',
    'LengthofServiceOverall. Overall Length of Service at Institute (in years)': 'institute_service',
    'LengthofServiceCurrent. Length of Service at current workplace (in years)': 'role_service'
}

tafe_survey_updated.rename(columns=cols, inplace=True)

###Printing column index to ensure changes###

tafe_survey_updated.columns
Out[13]:
Index(['id', 'Institute', 'WorkArea', 'cease_date', 'separation_type',
       '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 [14]:
print(dete_survey_updated.head(2))
   id                   separation_type cease_date  dete_start_date  \
0   1             Ill Health Retirement     12-Aug           1984.0   
1   2  Voluntary Early Retirement (VER)     12-Aug              NaN   

   role_start_date        position classification          region  \
0           2004.0  Public Servant        A01-A04  Central Office   
1              NaN  Public Servant        AO5-AO7  Central Office   

                       business_unit    employment_status  ...  \
0  Corporate Strategy and Peformance  Permanent Full-time  ...   
1  Corporate Strategy and Peformance  Permanent Full-time  ...   

   work_life_balance  workload  none_of_the_above  gender    age  aboriginal  \
0              False     False               True    Male  56-60         NaN   
1              False     False              False    Male  56-60         NaN   

   torres_strait  south_sea  disability  nesb  
0            NaN        NaN         NaN   Yes  
1            NaN        NaN         NaN   NaN  

[2 rows x 35 columns]

Above, we reformatted the column names of the DETE dataset to properly fit in snake case, afterwards we renamed several of the most important columns in the TAFE dataset to match. It was not important that we change the ones that are less relevant to our analysis, as they can be left out when using the pd.merge() function in later steps.

Filtering the Data

Due to the questions being asked in this project, we only need data for those who resigned from their jobs. The separation_type variable is where we can find those who resigned. The TAFE dataset simply has the value of "Resignation", while the DETE dataset has "Resignation" followed by a few possible strings:

  • -Other reasons
  • -Other employer
  • -Move overseas/interstate
In [15]:
###Using .value_counts() to see unique values in separation_type###

tafe_survey_updated['separation_type'].value_counts()
Out[15]:
Resignation                 340
Contract Expired            127
Retrenchment/ Redundancy    104
Retirement                   82
Transfer                     25
Termination                  23
Name: separation_type, dtype: int64
In [16]:
###Removing all strings after Registration###

dete_survey_updated['separation_type'] = dete_survey_updated['separation_type'].str.split('-').str[0]

###Using .value_counts() to check for accuracy###

dete_survey_updated['separation_type'].value_counts()
Out[16]:
Resignation                         311
Age Retirement                      285
Voluntary Early Retirement (VER)     67
Ill Health Retirement                61
Other                                49
Contract Expired                     34
Termination                          15
Name: separation_type, dtype: int64
In [17]:
###Using Boolean Masking to create a DataFrame of only Resignations###

dete_resignations = dete_survey_updated[dete_survey_updated['separation_type'] == 'Resignation']

tafe_resignations = tafe_survey_updated[tafe_survey_updated['separation_type'] == 'Resignation']

Now we have two datasets that only contains the rows in which the employee in question resigned from their position.

Verifying The Data

With all the effort put into cleaning a dataset, it can be critical to know that the data within the set is trustworthy. If large amounts of data in a dataset are illogical, the entire set could potentially be discarded. In the DETE dataset, there are a few logical inconsistencies we plan to look for below:

  • The _ceasedate variable having dates after the current date, and before the _dete_startdate variable.
  • The _dete_startdate variable having values before the year of 1940.
In [35]:
###Checking for Logical Inconsistencies###

##Cleaning cease_date in dete_resignations

dete_resignations['cease_date'].value_counts()

##Isolating the year##

dete_resignations['cease_date'] = dete_resignations['cease_date'].str.split('-').str[0]

##Replacing split values and formatting as 20xx##

dete_resignations['cease_date'] = dete_resignations['cease_date'].replace(['12'],'2012')

dete_resignations['cease_date'] = dete_resignations['cease_date'].replace(['13'],'2013')

dete_resignations['cease_date'] = dete_resignations['cease_date'].replace(['14'],'2014')

dete_resignations['cease_date'] = dete_resignations['cease_date'].replace(['6'],'2006')

dete_resignations['cease_date'] = dete_resignations['cease_date'].replace(['10'], '2010')

##Re-utilize .value_counts() to ensure proper formatting##

dete_resignations['cease_date'].value_counts()

##Format year strings as floats##

dete_resignations['cease_date'] = dete_resignations['cease_date'].astype("float")

dete_resignations['cease_date'].value_counts()
D:\Anaconda\lib\site-packages\ipykernel_launcher.py:9: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  if __name__ == '__main__':
D:\Anaconda\lib\site-packages\ipykernel_launcher.py:13: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  del sys.path[0]
D:\Anaconda\lib\site-packages\ipykernel_launcher.py:15: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  from ipykernel import kernelapp as app
D:\Anaconda\lib\site-packages\ipykernel_launcher.py:17: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
D:\Anaconda\lib\site-packages\ipykernel_launcher.py:19: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
D:\Anaconda\lib\site-packages\ipykernel_launcher.py:21: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
D:\Anaconda\lib\site-packages\ipykernel_launcher.py:29: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
Out[35]:
2013.0    146
2012.0    129
2014.0     22
2010.0      2
2006.0      1
Name: cease_date, dtype: int64

Now that we have a .value_counts series for DETE's resignations, we can do the same with the _dete_startdate variable to check for outliers.

In [36]:
##Checking for outliers##

dete_resignations['dete_start_date'].value_counts()
Out[36]:
2011.0    24
2008.0    22
2007.0    21
2012.0    21
2010.0    17
2005.0    15
2004.0    14
2009.0    13
2006.0    13
2013.0    10
2000.0     9
1999.0     8
1996.0     6
2002.0     6
1992.0     6
1998.0     6
2003.0     6
1994.0     6
1993.0     5
1990.0     5
1980.0     5
1997.0     5
1991.0     4
1989.0     4
1988.0     4
1995.0     4
2001.0     3
1985.0     3
1986.0     3
1983.0     2
1976.0     2
1974.0     2
1971.0     1
1972.0     1
1984.0     1
1982.0     1
1987.0     1
1975.0     1
1973.0     1
1977.0     1
1963.0     1
Name: dete_start_date, dtype: int64
In [37]:
##Checking for outliers in TAFE's cease_date##

tafe_resignations['cease_date'].value_counts()
Out[37]:
2011.0    116
2012.0     94
2010.0     68
2013.0     55
2009.0      2
Name: cease_date, dtype: int64

As we can see, the dataframes aren't entirely congruent. TAFE has a cease_dates in 2009, but DETE does not. Additionally, TAFE has a larger amount of resignations since 2010 than DETE. However, we aren't attempting to analyze the resignations based on year, and the information falls in line logically with the groundlines we had set above. As such, we can leave the data as is.

Creating a New Column

It's important to remember our research questions depend on the length of time an employee worked for their company, as such we will need to create a new column.

TAFE's dataset has an _instituteservice column, referring to the length of an employee's employement. We want to create an _instituteservice column for DETE's dataset as well.

This column can be created by subtracting the _dete_startdate variable from the _ceasedate variable.

In [42]:
##Creating the new column##

dete_resignations['institute_service'] = dete_resignations['cease_date'] - dete_resignations['dete_start_date']

##Checking the DataFrame for the new column##

dete_resignations.head() #Institute Service should be formatted as a float.
D:\Anaconda\lib\site-packages\ipykernel_launcher.py:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  This is separate from the ipykernel package so we can avoid doing imports until
Out[42]:
id separation_type cease_date dete_start_date role_start_date position classification region business_unit employment_status ... workload none_of_the_above gender age aboriginal torres_strait south_sea disability nesb institute_service
3 4 Resignation 2012.0 2005.0 2006.0 Teacher Primary Central Queensland NaN Permanent Full-time ... False False Female 36-40 NaN NaN NaN NaN NaN 7.0
5 6 Resignation 2012.0 1994.0 1997.0 Guidance Officer NaN Central Office Education Queensland Permanent Full-time ... False False Female 41-45 NaN NaN NaN NaN NaN 18.0
8 9 Resignation 2012.0 2009.0 2009.0 Teacher Secondary North Queensland NaN Permanent Full-time ... False False Female 31-35 NaN NaN NaN NaN NaN 3.0
9 10 Resignation 2012.0 1997.0 2008.0 Teacher Aide NaN NaN NaN Permanent Part-time ... False False Female 46-50 NaN NaN NaN NaN NaN 15.0
11 12 Resignation 2012.0 2009.0 2009.0 Teacher Secondary Far North Queensland NaN Permanent Full-time ... False False Male 31-35 NaN NaN NaN NaN NaN 3.0

5 rows × 36 columns

Identify Dissatisfied Employees

As a reminder, we're trying to determine, in part, whether employees who have varying lengths of employement are resigning due to dissatisfaction. We just created a column to determine length of employment, so next is determining which employees are dissatisifed. In the datasets, there are a couple columns we will be using to determine dissatisifacation:

tafe_survey_updated:

  • Contributing Factors. Dissatisfaction
  • Contributing Factors. Job Dissatisfaction

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

    We will aggregate these columns into one dissatisfied column.

In [46]:
##Checking for Unique Values in TAFE's dataset for job dissatisfcation##

#Column name values are True, - values are False, all else are NaN

tafe_resignations['Contributing Factors. Dissatisfaction'].value_counts()
Out[46]:
-                                         277
Contributing Factors. Dissatisfaction      55
Name: Contributing Factors. Dissatisfaction, dtype: int64
In [47]:
tafe_resignations['Contributing Factors. Job Dissatisfaction'].value_counts()
Out[47]:
-                      270
Job Dissatisfaction     62
Name: Contributing Factors. Job Dissatisfaction, dtype: int64

Updating Values

In [51]:
#Returning job dissatifcation values to True, False, or NaN.

def update_vals(x):
    if x == '-':
        return False
    elif pd.isnull(x):
        return np.nan
    else:
        return True

##Using the .applymap() method to the relevant TAFE columns##  
    
tafe_resignations['dissatisfied'] = tafe_resignations[['Contributing Factors. Dissatisfaction', 'Contributing Factors. Job Dissatisfaction']].applymap(update_vals).any(1, skipna=False)

#.any() will allow for creation of new column

tafe_resignations_up = tafe_resignations.copy()

#Check the unique values after the updates
tafe_resignations_up['dissatisfied'].value_counts(dropna=False)
D:\Anaconda\lib\site-packages\ipykernel_launcher.py:13: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  del sys.path[0]
Out[51]:
False    241
True      91
NaN        8
Name: dissatisfied, dtype: int64
In [56]:
#Creating a 'dissatisfied' column to the DETE dataset##

dete_resignations['dissatisfied'] = dete_resignations[[
    'job_dissatisfaction',
       'dissatisfaction_with_the_department', 'physical_work_environment',
       'lack_of_recognition', 'lack_of_job_security', 'work_location',
       'employment_conditions', 'work_life_balance',
       'workload']].any(1, skipna=False)

dete_resignations_up = dete_resignations.copy()

#Check unique values after the updates

dete_resignations_up['dissatisfied'].value_counts(dropna=False)
D:\Anaconda\lib\site-packages\ipykernel_launcher.py:8: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  
Out[56]:
False    162
True     149
Name: dissatisfied, dtype: int64

Above, we applied the update_vals formula to all relevant columns in both datasets in order to create a new column entitled 'dissatisfied'. Simply, the .any() function allows for the value in an employee's dissatisfied column to be set to True if they have True values for any of the dissatisfied columns we looked through.

Combining The Data

In [58]:
##Adding Institute Columns##

dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'

#Vertifying Changes

tafe_resignations_up.head()
Out[58]:
id Institute WorkArea cease_date separation_type 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. Other Contributing Factors. NONE gender age employment_status position institute_service role_service dissatisfied institute
3 6.340000e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - - NaN NaN NaN NaN NaN NaN False TAFE
4 6.340000e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - Career Move - Private Sector - - - ... - - Male 41 – 45 Permanent Full-time Teacher (including LVT) 4-Mar 4-Mar False TAFE
5 6.340000e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - - - - - ... Other - Female 56 or older Contract/casual Teacher (including LVT) 10-Jul 10-Jul False TAFE
6 6.340000e+17 Barrier Reef Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - Career Move - Private Sector - - Maternity/Family ... Other - Male 20 or younger Temporary Full-time Administration (AO) 4-Mar 4-Mar False TAFE
7 6.340000e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - - - - - ... Other - Male 46 – 50 Permanent Full-time Teacher (including LVT) 4-Mar 4-Mar False TAFE

5 rows × 25 columns

In [65]:
##Combining the DataFrames with institute as the index##

combined = pd.concat([dete_resignations_up, tafe_resignations_up], ignore_index=True, sort=False)

#Verifying the number of non-null values in each column#

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

combined_updated = combined.dropna(thresh=500, axis=1).copy() #thresh paramter signifies max amount of non null values

#Verifying the Changes#

combined_updated.head()
Out[66]:
id separation_type cease_date position employment_status gender age institute_service dissatisfied institute
0 4.0 Resignation 2012.0 Teacher Permanent Full-time Female 36-40 7 False DETE
1 6.0 Resignation 2012.0 Guidance Officer Permanent Full-time Female 41-45 18 True DETE
2 9.0 Resignation 2012.0 Teacher Permanent Full-time Female 31-35 3 False DETE
3 10.0 Resignation 2012.0 Teacher Aide Permanent Part-time Female 46-50 15 True DETE
4 12.0 Resignation 2012.0 Teacher Permanent Full-time Male 31-35 3 False DETE

Above, we combined the two cleaned datasets together in order to analyze them together. In order to make the resulting DataFrame easier to work with, we used the .dropna() method with the thresh parameter to drop every column which fewer than 500 non-null values.

Cleaning the Service Column

To clean the differently formatted values of the _insitituteservice column, we will be using the following buckets for employees.

  • 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 [70]:
##Checking for unique values within the _institute_service_ column##

combined_updated['institute_service'].value_counts(dropna=False)
Out[70]:
nan                   88
Less than 1 year      73
2-Jan                 64
4-Mar                 63
6-May                 33
20-Nov                26
5.0                   23
1.0                   22
10-Jul                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
13.0                   8
8.0                    8
20.0                   7
15.0                   7
17.0                   6
22.0                   6
14.0                   6
12.0                   6
10.0                   6
18.0                   5
16.0                   5
11.0                   4
24.0                   4
23.0                   4
19.0                   3
32.0                   3
39.0                   3
21.0                   3
25.0                   2
30.0                   2
36.0                   2
28.0                   2
26.0                   2
27.0                   1
49.0                   1
41.0                   1
35.0                   1
38.0                   1
42.0                   1
29.0                   1
34.0                   1
31.0                   1
33.0                   1
Name: institute_service, dtype: int64
In [72]:
###Creating the buckets for each value in institute_service###

#Extracting the years of service and converting the type to float
combined_updated['institute_service_up'] = combined_updated['institute_service'].astype('str').str.extract(r'(\d+)')
combined_updated['institute_service_up'] = combined_updated['institute_service_up'].astype('float')

#Verifying the Changes#
combined_updated['institute_service_up'].value_counts()
Out[72]:
1.0     95
4.0     79
2.0     78
6.0     50
20.0    43
10.0    27
5.0     23
3.0     20
0.0     20
9.0     14
7.0     13
13.0     8
8.0      8
15.0     7
17.0     6
12.0     6
14.0     6
22.0     6
16.0     5
18.0     5
11.0     4
24.0     4
23.0     4
39.0     3
19.0     3
21.0     3
32.0     3
28.0     2
36.0     2
25.0     2
30.0     2
26.0     2
29.0     1
38.0     1
42.0     1
27.0     1
41.0     1
35.0     1
49.0     1
34.0     1
33.0     1
31.0     1
Name: institute_service_up, dtype: int64
In [74]:
### Convering years of service to categories###

def categorize_service(val):
    if val >= 11:
        return 'Veteran'
    elif 7 <= val < 11:
        return 'Established'
    elif 3 <= val < 7:
        return 'Experienced'
    elif pd.isnull(val):
        return np.nan
    else:
        return 'New'

###Applying the formula with .apply())
combined_updated['service_cat'] = combined_updated['institute_service_up'].apply(categorize_service)

###Verifying Changes###

combined_updated['service_cat'].value_counts()
Out[74]:
New            193
Experienced    172
Veteran        136
Established     62
Name: service_cat, dtype: int64

Above, we were able to change the values in the _insituteservice category to allow us to more easily group them together, then did just that by way of broad categories to allow us to analyze further.

Performing Initial Analysis

We are going to be returning all NaN (8 of them) as false (because False is the most common value), and using that to determine the percentage of employees in each service category that resigned due to some form of dissatisfcation.

In [81]:
###Confirming number of True or False in dissatisfied column###

combined_updated['dissatisfied'].value_counts(dropna=False)

###Filling all reminaing null values with False###

combined_updated['dissatisfied'] = combined_updated['dissatisfied'].fillna(value=False)
In [84]:
###Calculating percentage of employees who resigned due to dissatisfaction###

dissatisfied_pct = combined_updated.pivot_table(index='service_cat', values='dissatisfied')

dissatisfied_pct
Out[84]:
dissatisfied
service_cat
Established 0.516129
Experienced 0.343023
New 0.295337
Veteran 0.485294
In [87]:
###Plotting the Table

#magic function allowing juptyer notebook to work with plots
%matplotlib inline

dissatisfied_pct.plot(kind='bar', rot=35) #rot = n parameter rotates labels n degrees
Out[87]:
<matplotlib.axes._subplots.AxesSubplot at 0x269b2596448>

As seen from our intial analysis above, it can be concluded that the employees most likely to resign due to dissatisfaction are those who have been with the given institute for 7 or more years.