In this guided project, we'll work with exit surveys from employees of the Department of Education, Training and Employment (DETE) and the Technical and Further Education (TAFE) institute in Queensland, Australia.

In this project, we'll play the role of data analyst and pretend our stakeholders want to know the following:

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

Are younger employees resigning due to some kind of dissatisfaction? What about older employees?

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

ID: An id used to identify the participant of the survey

SeparationType: The reason why the person's employment ended

Cease Date: The year or month the person's employment ended

DETE Start Date: The year the person began employment with the DETE


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

Record ID: An id used to identify the participant of the survey

Reason for ceasing employment: The reason why the person's employment ended

LengthofServiceOverall. Overall Length of Service at Institute (in years): The length of the person's employment (in years)

In [1]:
import pandas as pd
import numpy as np
In [2]:
dete_survey=pd.read_csv("dete_survey.csv")
tafe_survey=pd.read_csv('tafe_survey.csv')
In [3]:
dete_survey.head()
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 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 rows × 56 columns

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

5 rows × 72 columns

In [5]:
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 [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
In [7]:
dete_survey.isnull().sum()
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

In dete_survey data we can see a significant missining data in some of the columns such as: 1-Classification 2-Business Unit 3-Aboriginal 4-Torres Strait 5-South Sea 6-Disability 7-NESB

In [8]:
tafe_survey.isnull().sum()
Out[8]:
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 [50]:
dete_survey=pd.read_csv('dete_survey.csv', na_values='Not Stated')
In [51]:
dete_survey.head()
Out[51]:
ID SeparationType Cease Date DETE Start Date Role Start Date Position Classification Region Business Unit Employment Status ... Kept informed Wellness programs Health & Safety Gender Age Aboriginal Torres Strait South Sea Disability NESB
0 1 Ill Health Retirement 08/2012 1984.0 2004.0 Public Servant A01-A04 Central Office Corporate Strategy and Peformance Permanent Full-time ... N N N Male 56-60 NaN NaN NaN NaN Yes
1 2 Voluntary Early Retirement (VER) 08/2012 NaN NaN Public Servant AO5-AO7 Central Office Corporate Strategy and Peformance Permanent Full-time ... N N N Male 56-60 NaN NaN NaN NaN NaN
2 3 Voluntary Early Retirement (VER) 05/2012 2011.0 2011.0 Schools Officer NaN Central Office Education Queensland Permanent Full-time ... N N N Male 61 or older NaN NaN NaN NaN NaN
3 4 Resignation-Other reasons 05/2012 2005.0 2006.0 Teacher Primary Central Queensland NaN Permanent Full-time ... A N A Female 36-40 NaN NaN NaN NaN NaN
4 5 Age Retirement 05/2012 1970.0 1989.0 Head of Curriculum/Head of Special Education NaN South East NaN Permanent Full-time ... N A M Female 61 or older NaN NaN NaN NaN NaN

5 rows × 56 columns

As the beginning of the project clearly mentiones we need to work only with couple of Columns here I am going to remove columns from DETE Dataframe

In [52]:
dete_survey_updated=dete_survey.drop(dete_survey.columns[28:49],axis=1)
In [53]:
dete_survey_updated.head()
Out[53]:
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

Here I am going to do the same for couple of columns in TEFE Dataframe as well

In [54]:
tafe_survey_updated=tafe_survey.drop(tafe_survey.columns[17:66],axis=1)
In [55]:
tafe_survey_updated.head()
Out[55]:
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

Since we want to merge these Two Dataframes it is good idea for both of them to have same name for columns so , here I will try to use the Vectorized string methods to unit them both.

In [56]:
dete_survey_updated.columns=dete_survey_updated.columns.str.lower().str.rstrip().str.replace(' ','_')
In [57]:
dete_survey_updated.columns
Out[57]:
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 [58]:
columns_change={'Record ID': 'id',
'CESSATION YEAR': 'cease_date',
'Reason for ceasing employment': 'separationtype',
'Gender. What is your Gender?': 'gender',
'CurrentAge. Current Age': 'age',
'Employment Type. Employment Type': 'employment_status',
'Classification. Classification': 'position',
'LengthofServiceOverall. Overall Length of Service at Institute (in years)': 'institute_service',
'LengthofServiceCurrent. Length of Service at current workplace (in years)': 'role_service'}

tafe_survey_updated=tafe_survey_updated.rename(columns=columns_change)
In [59]:
tafe_survey_updated.columns
Out[59]:
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 [19]:
dete_survey_updated['separationtype']
Out[19]:
0                     Ill Health Retirement
1          Voluntary Early Retirement (VER)
2          Voluntary Early Retirement (VER)
3                 Resignation-Other reasons
4                            Age Retirement
5                 Resignation-Other reasons
6                            Age Retirement
7                            Age Retirement
8                 Resignation-Other reasons
9                Resignation-Other employer
10                           Age Retirement
11     Resignation-Move overseas/interstate
12                Resignation-Other reasons
13                           Age Retirement
14               Resignation-Other employer
15         Voluntary Early Retirement (VER)
16                Resignation-Other reasons
17                           Age Retirement
18                           Age Retirement
19                           Age Retirement
20               Resignation-Other employer
21                Resignation-Other reasons
22                Resignation-Other reasons
23                Resignation-Other reasons
24                           Age Retirement
25                Resignation-Other reasons
26                           Age Retirement
27               Resignation-Other employer
28                           Age Retirement
29                           Age Retirement
                       ...                 
792                                   Other
793                   Ill Health Retirement
794    Resignation-Move overseas/interstate
795                          Age Retirement
796                          Age Retirement
797    Resignation-Move overseas/interstate
798    Resignation-Move overseas/interstate
799                                   Other
800                                   Other
801                   Ill Health Retirement
802    Resignation-Move overseas/interstate
803              Resignation-Other employer
804              Resignation-Other employer
805                          Age Retirement
806              Resignation-Other employer
807               Resignation-Other reasons
808               Resignation-Other reasons
809                          Age Retirement
810                          Age Retirement
811                        Contract Expired
812                          Age Retirement
813                          Age Retirement
814                          Age Retirement
815              Resignation-Other employer
816    Resignation-Move overseas/interstate
817                          Age Retirement
818                          Age Retirement
819    Resignation-Move overseas/interstate
820                   Ill Health Retirement
821    Resignation-Move overseas/interstate
Name: separationtype, Length: 822, dtype: object
In [20]:
dete_survey_updated['separationtype'].value_counts()
Out[20]:
Age Retirement                          285
Resignation-Other reasons               150
Resignation-Other employer               91
Resignation-Move overseas/interstate     70
Voluntary Early Retirement (VER)         67
Ill Health Retirement                    61
Other                                    49
Contract Expired                         34
Termination                              15
Name: separationtype, dtype: int64
In [21]:
tafe_survey_updated['separationtype']
Out[21]:
0              Contract Expired
1                    Retirement
2                    Retirement
3                   Resignation
4                   Resignation
5                   Resignation
6                   Resignation
7                   Resignation
8                   Resignation
9                   Resignation
10                  Resignation
11     Retrenchment/ Redundancy
12     Retrenchment/ Redundancy
13                  Resignation
14                  Resignation
15                  Resignation
16                  Resignation
17                  Resignation
18                  Resignation
19                  Resignation
20                  Resignation
21                  Resignation
22                  Resignation
23                  Resignation
24                  Resignation
25             Contract Expired
26                  Resignation
27                  Resignation
28                   Retirement
29                  Resignation
                 ...           
672                  Retirement
673    Retrenchment/ Redundancy
674    Retrenchment/ Redundancy
675                 Resignation
676                 Resignation
677                 Resignation
678                 Resignation
679                 Resignation
680    Retrenchment/ Redundancy
681                 Resignation
682                 Resignation
683                 Resignation
684                 Resignation
685                 Resignation
686                 Resignation
687                 Termination
688                 Resignation
689                 Resignation
690                 Resignation
691                 Resignation
692            Contract Expired
693                 Resignation
694                 Resignation
695            Contract Expired
696                 Resignation
697                 Resignation
698                 Resignation
699                 Resignation
700            Contract Expired
701                 Resignation
Name: separationtype, Length: 702, dtype: object
In [22]:
tafe_survey_updated['separationtype'].value_counts()
Out[22]:
Resignation                 340
Contract Expired            127
Retrenchment/ Redundancy    104
Retirement                   82
Transfer                     25
Termination                  23
Name: separationtype, dtype: int64
In [60]:
dete_resignations=dete_survey_updated.copy()
In [61]:
dete_resignations=dete_resignations.loc[dete_resignations['separationtype'].str.contains('Resignation')]
In [62]:
dete_resignations
Out[62]:
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
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
5 6 Resignation-Other reasons 05/2012 1994.0 1997.0 Guidance Officer NaN Central Office Education Queensland Permanent Full-time ... False False False Female 41-45 NaN NaN NaN NaN NaN
8 9 Resignation-Other reasons 07/2012 2009.0 2009.0 Teacher Secondary North Queensland NaN Permanent Full-time ... False False False Female 31-35 NaN NaN NaN NaN NaN
9 10 Resignation-Other employer 2012 1997.0 2008.0 Teacher Aide NaN NaN NaN Permanent Part-time ... False False False Female 46-50 NaN NaN NaN NaN NaN
11 12 Resignation-Move overseas/interstate 2012 2009.0 2009.0 Teacher Secondary Far North Queensland NaN Permanent Full-time ... False False False Male 31-35 NaN NaN NaN NaN NaN
12 13 Resignation-Other reasons 2012 1998.0 1998.0 Teacher Primary Far North Queensland NaN Permanent Full-time ... False False False Female 36-40 NaN NaN NaN NaN NaN
14 15 Resignation-Other employer 2012 2007.0 2010.0 Teacher Secondary Central Queensland NaN Permanent Full-time ... False False False Male 31-35 NaN NaN NaN NaN NaN
16 17 Resignation-Other reasons 2012 NaN NaN Teacher Aide NaN South East NaN Permanent Part-time ... False False False Male 61 or older NaN NaN NaN NaN NaN
20 21 Resignation-Other employer 2012 1982.0 1982.0 Teacher Secondary Central Queensland NaN Permanent Full-time ... False False True Male 56-60 NaN NaN NaN NaN NaN
21 22 Resignation-Other reasons 2012 1980.0 2009.0 Cleaner NaN Darling Downs South West NaN Permanent Part-time ... False False False Female 51-55 NaN NaN NaN NaN NaN
22 23 Resignation-Other reasons 2012 1997.0 1998.0 School Administrative Staff NaN Metropolitan NaN Permanent Part-time ... True False False Female 46-50 NaN NaN NaN NaN NaN
23 24 Resignation-Other reasons 2012 1973.0 2012.0 Teacher Primary North Queensland NaN Permanent Full-time ... True False False Female 61 or older NaN NaN NaN NaN NaN
25 26 Resignation-Other reasons 2012 1995.0 2002.0 Teacher Primary South East NaN Permanent Part-time ... True False False Female 41-45 NaN NaN NaN NaN NaN
27 28 Resignation-Other employer 2012 2005.0 2011.0 Public Servant AO5-AO7 Central Office Information and Technologies Permanent Full-time ... False False False Female 21-25 Yes NaN NaN NaN NaN
33 34 Resignation-Other reasons 2012 2003.0 2003.0 Teacher Secondary NaN NaN Permanent Full-time ... False False False Male 36-40 NaN NaN NaN Yes NaN
34 35 Resignation-Other reasons 2012 2006.0 2009.0 Cleaner NaN Central Office Education Queensland Permanent Part-time ... True False False Male 61 or older NaN NaN NaN NaN NaN
37 38 Resignation-Other reasons 2012 2011.0 2011.0 Teacher Aide NaN Central Queensland NaN Temporary Part-time ... False False True Female 21-25 NaN NaN NaN NaN NaN
39 40 Resignation-Move overseas/interstate 2012 NaN NaN Teacher NaN Central Queensland NaN Permanent Full-time ... True False False Female 21-25 NaN NaN NaN NaN NaN
40 41 Resignation-Other employer 2012 1977.0 1980.0 Teacher Primary South East NaN Permanent Full-time ... False False False Male 56-60 NaN NaN NaN NaN NaN
41 42 Resignation-Other reasons 2012 1974.0 1994.0 Head of Curriculum/Head of Special Education NaN Metropolitan NaN Permanent Full-time ... True False False Female 51-55 NaN NaN NaN NaN NaN
42 43 Resignation-Move overseas/interstate 2012 2011.0 2011.0 Cleaner NaN North Coast NaN Permanent Part-time ... False False True Female 41-45 NaN NaN NaN NaN NaN
43 44 Resignation-Other reasons 2012 1976.0 1976.0 Teacher Primary North Coast NaN Permanent Full-time ... True True False Male 51-55 NaN NaN NaN NaN NaN
48 49 Resignation-Move overseas/interstate 2012 2009.0 2010.0 Cleaner NaN South East NaN Permanent Full-time ... False False False Male 21-25 NaN NaN NaN NaN NaN
50 51 Resignation-Move overseas/interstate 2012 2009.0 2010.0 Cleaner NaN South East NaN Permanent Full-time ... False False False Male 21-25 NaN NaN NaN NaN NaN
51 52 Resignation-Other reasons 2012 1993.0 1993.0 Cleaner NaN South East NaN Permanent Full-time ... False False True Female 61 or older NaN NaN NaN NaN NaN
55 56 Resignation-Other employer 2012 2008.0 2008.0 Teacher Aide NaN Metropolitan NaN Permanent Part-time ... False False True Female 26-30 NaN NaN NaN NaN NaN
57 58 Resignation-Other employer 2012 2003.0 2012.0 Teacher Secondary Darling Downs South West NaN Permanent Full-time ... False False False Male 46-50 NaN NaN NaN NaN NaN
61 62 Resignation-Other reasons 2012 2011.0 2011.0 Schools Officer NaN Central Queensland NaN Temporary Part-time ... False False False Female 31-35 NaN NaN NaN NaN NaN
69 70 Resignation-Other reasons 2012 2006.0 NaN Public Servant AO5-AO7 Central Office Information and Technologies Permanent Full-time ... False False False Female 36-40 NaN NaN NaN NaN NaN
71 72 Resignation-Other reasons 2012 2011.0 2011.0 Teacher Aide NaN Far North Queensland NaN Permanent Part-time ... False False False Female 36-40 NaN NaN NaN NaN NaN
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
747 749 Resignation-Move overseas/interstate 01/2014 2008.0 2008.0 Teacher Primary North Coast NaN Permanent Full-time ... False False False Female 41-45 NaN NaN NaN NaN NaN
751 753 Resignation-Other reasons 12/2013 2005.0 2005.0 Cleaner NaN Central Office Education Queensland Permanent Part-time ... False False False Male 61 or older NaN NaN NaN NaN NaN
752 754 Resignation-Other reasons 11/2013 1998.0 NaN Teacher Aide NaN Darling Downs South West NaN Permanent Part-time ... False False False Female 46-50 NaN NaN NaN Yes NaN
753 755 Resignation-Other employer 11/2013 2004.0 2011.0 Teacher Primary Central Queensland NaN Permanent Full-time ... True False False Female 31-35 NaN NaN NaN NaN NaN
755 757 Resignation-Other employer 12/2013 2012.0 2013.0 Teacher Primary Central Queensland NaN Permanent Part-time ... False False True Female 41-45 NaN NaN NaN NaN NaN
762 764 Resignation-Other employer 07/2006 2006.0 2006.0 Teacher Primary Metropolitan NaN Permanent Part-time ... False False False Female 41-45 NaN NaN NaN NaN NaN
766 768 Resignation-Other employer 01/2014 2007.0 2007.0 Teacher Primary Metropolitan NaN Permanent Full-time ... False False False Male 26-30 NaN NaN NaN NaN NaN
769 771 Resignation-Other reasons 11/2013 2008.0 NaN Teacher Aide NaN South East NaN Permanent Full-time ... False False False Female 46-50 NaN NaN NaN NaN NaN
770 772 Resignation-Other reasons NaN 1987.0 1987.0 Cleaner NaN Darling Downs South West NaN Permanent Part-time ... False False True Female 61 or older NaN NaN NaN NaN NaN
771 773 Resignation-Other employer 01/2014 2002.0 2007.0 Head of Curriculum/Head of Special Education NaN Central Queensland NaN Permanent Full-time ... False False False Male 36-40 NaN NaN NaN NaN NaN
774 776 Resignation-Other employer NaN 2005.0 2005.0 Teacher Aide NaN Central Queensland NaN Permanent Part-time ... False False True Female 41-45 NaN NaN NaN NaN NaN
784 786 Resignation-Other reasons 10/2013 2013.0 2013.0 Teacher Secondary Central Queensland NaN Permanent Full-time ... True False False Female 21-25 NaN NaN NaN NaN NaN
786 788 Resignation-Other employer 01/2014 1994.0 2008.0 Teacher Secondary Darling Downs South West NaN Permanent Full-time ... False False False Female 41-45 NaN NaN NaN NaN NaN
788 790 Resignation-Other employer NaN 1990.0 2010.0 Teacher Secondary Metropolitan NaN Permanent Full-time ... False False False Female 41-45 NaN NaN NaN NaN NaN
789 791 Resignation-Other reasons 01/2014 1983.0 1999.0 School Based Professional Staff (Therapist, nu... NaN Metropolitan NaN Permanent Part-time ... False False True Female 51-55 NaN NaN NaN NaN NaN
790 792 Resignation-Other reasons 01/2014 2008.0 2008.0 Teacher Secondary North Coast NaN Permanent Full-time ... False True False Male 36-40 NaN NaN NaN NaN NaN
791 793 Resignation-Other reasons NaN 2007.0 2007.0 Public Servant A01-A04 Metropolitan NaN Permanent Part-time ... False False False Female 46-50 NaN NaN NaN NaN NaN
794 796 Resignation-Move overseas/interstate 11/2013 NaN NaN Cleaner NaN NaN NaN Permanent Part-time ... False False False Female 36-40 NaN NaN NaN NaN NaN
797 799 Resignation-Move overseas/interstate NaN 2000.0 2013.0 Public Servant A01-A04 South East NaN Permanent Part-time ... False False False Female 36-40 NaN NaN NaN NaN NaN
798 800 Resignation-Move overseas/interstate NaN 1995.0 NaN Teacher Aide NaN Darling Downs South West NaN Permanent Part-time ... False False False Female 36-40 NaN NaN NaN NaN NaN
802 804 Resignation-Move overseas/interstate 12/2013 NaN NaN Teacher Aide NaN Metropolitan NaN Permanent Part-time ... False False False NaN NaN NaN NaN NaN NaN NaN
803 805 Resignation-Other employer 01/2014 2004.0 2007.0 Teacher Primary Darling Downs South West NaN Permanent Full-time ... False False False Female 46-50 NaN NaN NaN NaN NaN
804 806 Resignation-Other employer 01/2014 2008.0 2013.0 Teacher Primary Darling Downs South West NaN Permanent Part-time ... False False False Female 26-30 NaN NaN NaN NaN NaN
806 808 Resignation-Other employer 12/2013 2005.0 2005.0 Cleaner NaN Central Queensland NaN Permanent Part-time ... False False False Female 41-45 NaN NaN NaN NaN NaN
807 809 Resignation-Other reasons 12/2013 2004.0 2004.0 Teacher Primary Central Queensland NaN Permanent Full-time ... True False False Female 36-40 NaN NaN NaN NaN NaN
808 810 Resignation-Other reasons 12/2013 2010.0 2010.0 Teacher Aide NaN NaN NaN Permanent Part-time ... False False False Female 26-30 NaN NaN NaN NaN NaN
815 817 Resignation-Other employer 01/2014 2012.0 2012.0 Teacher Primary Far North Queensland NaN Permanent Full-time ... False False False Male 21-25 NaN NaN NaN NaN NaN
816 818 Resignation-Move overseas/interstate 01/2014 2012.0 2012.0 Teacher Secondary North Coast NaN Permanent Full-time ... False False False Female 21-25 NaN NaN NaN NaN NaN
819 821 Resignation-Move overseas/interstate 01/2014 2009.0 2009.0 Public Servant A01-A04 Central Office Education Queensland Permanent Full-time ... True False False Female 31-35 NaN NaN NaN NaN NaN
821 823 Resignation-Move overseas/interstate 12/2013 NaN NaN Teacher Aide NaN Metropolitan NaN NaN ... False False False NaN NaN NaN NaN NaN NaN NaN

311 rows × 35 columns

In [63]:
tafe_resignations=tafe_survey_updated.copy()
In [64]:
tafe_resignations=tafe_resignations.loc[tafe_resignations['separationtype']=='Resignation']
In [65]:
tafe_resignations
Out[65]:
id Institute WorkArea cease_date separationtype Contributing Factors. Career Move - Public Sector Contributing Factors. Career Move - Private Sector Contributing Factors. Career Move - Self-employment Contributing Factors. Ill Health Contributing Factors. Maternity/Family ... Contributing Factors. Study Contributing Factors. Travel Contributing Factors. Other Contributing Factors. NONE gender age employment_status position institute_service role_service
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 6.341475e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - - - - - ... - - Other - Female 56 or older Contract/casual Teacher (including LVT) 7-10 7-10
6 6.341520e+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) 3-4 3-4
7 6.341537e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - - - - - ... - - Other - Male 46 50 Permanent Full-time Teacher (including LVT) 3-4 3-4
8 6.341579e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2009.0 Resignation - - - - - ... - - Other - Female 36 40 Temporary Full-time Tutor 3-4 3-4
9 6.341588e+17 Barrier Reef Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector - - - - ... - - Other - Female 21 25 Permanent Full-time Administration (AO) 1-2 1-2
10 6.341588e+17 Barrier Reef Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector - - - - ... - - - - Female 41 45 Temporary Part-time Administration (AO) Less than 1 year Less than 1 year
13 6.341725e+17 Barrier Reef Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - - Female 31 35 Temporary Full-time Administration (AO) 11-20 Less than 1 year
14 6.341726e+17 Central Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - - Female 31 35 Permanent Part-time Teacher (including LVT) 7-10 7-10
15 6.341761e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - - Other - Female 46 50 Permanent Part-time Technical Officer (TO) 11-20 11-20
16 6.341770e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
17 6.341771e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector - - - - ... - - - - Female 31 35 Permanent Full-time Administration (AO) 7-10 1-2
18 6.341779e+17 Brisbane North Institute of TAFE Delivery (teaching) 2010.0 Resignation NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
19 6.341820e+17 Southbank Institute of Technology Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - - - NONE NaN NaN NaN NaN NaN NaN
20 6.341821e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - - - - NaN NaN NaN NaN NaN NaN
21 6.341831e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - - NaN NaN NaN NaN NaN NaN
22 6.341847e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector - - - - ... - - Other - Male 26 30 Temporary Full-time Administration (AO) 5-6 5-6
23 6.341907e+17 Central Queensland Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - - - - Female 26 30 Contract/casual Administration (AO) Less than 1 year Less than 1 year
24 6.341907e+17 Central Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - - Female 31 35 Permanent Part-time Teacher (including LVT) 7-10 7-10
26 6.341934e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - - NaN NaN NaN NaN NaN NaN
27 6.341935e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector - - - - ... - - - - Male 56 or older Temporary Full-time Administration (AO) More than 20 years 7-10
29 6.341994e+17 Barrier Reef Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - - Female 31 35 Temporary Full-time Administration (AO) Less than 1 year Less than 1 year
32 6.342002e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - - Female 46 50 Temporary Part-time Professional Officer (PO) Less than 1 year Less than 1 year
36 6.342062e+17 Sunshine Coast Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector Career Move - Private Sector - - - ... - - Other - NaN NaN NaN NaN NaN NaN
37 6.342080e+17 Southbank Institute of Technology Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - Travel - - NaN NaN NaN NaN NaN NaN
39 6.342081e+17 Southbank Institute of Technology Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector - - - - ... - - - - NaN NaN NaN NaN NaN NaN
40 6.342090e+17 Sunshine Coast Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - - - - Female 26 30 Temporary Part-time Administration (AO) Less than 1 year Less than 1 year
41 6.342090e+17 Sunshine Coast Institute of TAFE Delivery (teaching) 2010.0 Resignation - - Career Move - Self-employment - - ... - - - - Female 36 40 Permanent Full-time Teacher (including LVT) 11-20 11-20
42 6.342090e+17 Sunshine Coast Institute of TAFE Delivery (teaching) 2010.0 Resignation - - Career Move - Self-employment - - ... - - - - Male 31 35 Permanent Full-time Teacher (including LVT) 3-4 3-4
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
659 6.349985e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - Career Move - Private Sector - - - ... Study - - - Female 46 50 Temporary Part-time Administration (AO) 1-2 1-2
660 6.349994e+17 Central Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - - Other - Female 41 45 Permanent Part-time Administration (AO) 3-4 3-4
661 6.350003e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - Career Move - Private Sector - - - ... - - - - Female 46 50 Permanent Full-time Administration (AO) 5-6 More than 20 years
665 6.350055e+17 Tropical North Institute of TAFE Delivery (teaching) 2013.0 Resignation - - - Ill Health - ... - - - - NaN NaN NaN NaN NaN NaN
666 6.350055e+17 Tropical North Institute of TAFE Delivery (teaching) 2013.0 Resignation - - - - - ... - - - - NaN NaN NaN NaN NaN NaN
669 6.350108e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - - Other - Female 26 30 Temporary Full-time Administration (AO) 3-4 3-4
670 6.350124e+17 Tropical North Institute of TAFE Delivery (teaching) 2013.0 Resignation NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
671 6.350127e+17 Tropical North Institute of TAFE Delivery (teaching) 2013.0 Resignation - - - - - ... - - - - Female 46 50 Temporary Full-time Teacher (including LVT) Less than 1 year Less than 1 year
675 6.350175e+17 Tropical North Institute of TAFE Delivery (teaching) 2013.0 Resignation - - - - - ... - - Other - Male 51-55 Temporary Full-time Teacher (including LVT) Less than 1 year Less than 1 year
676 6.350194e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation Career Move - Public Sector - - - - ... - - - - Female 41 45 Contract/casual Administration (AO) 1-2 Less than 1 year
677 6.350219e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - - Other - Female 36 40 Temporary Full-time Administration (AO) Less than 1 year Less than 1 year
678 6.350253e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - Career Move - Private Sector - - - ... - - - - Male 51-55 Permanent Full-time Administration (AO) 3-4 3-4
679 6.350279e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - Ill Health - ... - - - - Female 56 or older Temporary Part-time Operational (OO) 1-2 1-2
681 6.350314e+17 Central Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - - Other - Female 26 30 Temporary Full-time Administration (AO) Less than 1 year Less than 1 year
682 6.350357e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation Career Move - Public Sector - - - - ... - - - - Female 26 30 Permanent Part-time Administration (AO) Less than 1 year Less than 1 year
683 6.350374e+17 Central Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - - Other - Female 41 45 Temporary Full-time Administration (AO) Less than 1 year Less than 1 year
684 6.350375e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation Career Move - Public Sector - - - - ... - - - - Male 41 45 Contract/casual Administration (AO) 3-4 Less than 1 year
685 6.350402e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - - - - Female 26 30 Temporary Full-time Technical Officer (TO) 1-2 1-2
686 6.350426e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation Career Move - Public Sector - - - - ... - - - - Female 41 45 Temporary Full-time Administration (AO) 5-6 5-6
688 6.350479e+17 Tropical North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - - - NONE Female 46 50 Permanent Part-time Professional Officer (PO) 5-6 5-6
689 6.350480e+17 Central Queensland Institute of TAFE Delivery (teaching) 2013.0 Resignation - - - Ill Health - ... - - - - Male 41 45 Permanent Full-time Teacher (including LVT) Less than 1 year Less than 1 year
690 6.350496e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - Ill Health - ... - - - - NaN NaN NaN NaN NaN NaN
691 6.350496e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - Ill Health - ... - - - - Female 56 or older Permanent Part-time Operational (OO) 3-4 3-4
693 6.350599e+17 Tropical North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - Career Move - Private Sector - - - ... - - - - Female 26 30 Temporary Full-time Administration (AO) 1-2 1-2
694 6.350652e+17 Sunshine Coast Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - Career Move - Private Sector - - - ... - - - - NaN NaN NaN NaN NaN NaN
696 6.350660e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - Career Move - Private Sector - - - ... - - - - Male 21 25 Temporary Full-time Operational (OO) 5-6 5-6
697 6.350668e+17 Barrier Reef Institute of TAFE Delivery (teaching) 2013.0 Resignation Career Move - Public Sector - - - - ... - - - - Male 51-55 Temporary Full-time Teacher (including LVT) 1-2 1-2
698 6.350677e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation Career Move - Public Sector - - - - ... - - - - NaN NaN NaN NaN NaN NaN
699 6.350704e+17 Tropical North Institute of TAFE Delivery (teaching) 2013.0 Resignation - - - - - ... - - Other - Female 51-55 Permanent Full-time Teacher (including LVT) 5-6 1-2
701 6.350730e+17 Tropical North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - Career Move - Self-employment - - ... - Travel - - Female 26 30 Contract/casual Administration (AO) 3-4 1-2

340 rows × 23 columns

We have filtered out "SeperationType" for both Dataframes based on the Resignation and assigned the ew Dataframes toa new variables
In [66]:
dete_resignations['cease_date']
Out[66]:
3      05/2012
5      05/2012
8      07/2012
9         2012
11        2012
12        2012
14        2012
16        2012
20        2012
21        2012
22        2012
23        2012
25        2012
27        2012
33        2012
34        2012
37        2012
39        2012
40        2012
41        2012
42        2012
43        2012
48        2012
50        2012
51        2012
55        2012
57        2012
61        2012
69        2012
71        2012
        ...   
747    01/2014
751    12/2013
752    11/2013
753    11/2013
755    12/2013
762    07/2006
766    01/2014
769    11/2013
770        NaN
771    01/2014
774        NaN
784    10/2013
786    01/2014
788        NaN
789    01/2014
790    01/2014
791        NaN
794    11/2013
797        NaN
798        NaN
802    12/2013
803    01/2014
804    01/2014
806    12/2013
807    12/2013
808    12/2013
815    01/2014
816    01/2014
819    01/2014
821    12/2013
Name: cease_date, Length: 311, dtype: object
In [67]:
pat=r'([1-2][0-9]{3})'
dete_resignations['cease_date']=dete_resignations['cease_date'].str.extract(pat,expand=True).astype(float)
In [68]:
dete_resignations['cease_date'].value_counts()
Out[68]:
2013.0    146
2012.0    129
2014.0     22
2010.0      2
2006.0      1
Name: cease_date, dtype: int64
In [43]:
dete_resignations['dete_start_date'].value_counts()
Out[43]:
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 [31]:
tafe_resignations['cease_date']
Out[31]:
3      2010.0
4      2010.0
5      2010.0
6      2010.0
7      2010.0
8      2009.0
9      2010.0
10     2010.0
13     2010.0
14     2010.0
15     2010.0
16     2010.0
17     2010.0
18     2010.0
19     2010.0
20     2010.0
21     2010.0
22     2010.0
23     2010.0
24     2010.0
26     2010.0
27     2010.0
29     2010.0
32     2010.0
36     2010.0
37     2010.0
39     2010.0
40     2010.0
41     2010.0
42     2010.0
        ...  
659    2013.0
660    2013.0
661    2013.0
665    2013.0
666    2013.0
669    2013.0
670    2013.0
671    2013.0
675    2013.0
676    2013.0
677    2013.0
678    2013.0
679    2013.0
681    2013.0
682    2013.0
683    2013.0
684    2013.0
685    2013.0
686    2013.0
688    2013.0
689    2013.0
690    2013.0
691    2013.0
693    2013.0
694    2013.0
696    2013.0
697    2013.0
698    2013.0
699    2013.0
701    2013.0
Name: cease_date, Length: 340, dtype: float64
In [69]:
%matplotlib inline
dete_resignations.boxplot(column='cease_date')
dete_resignations.plot()
Out[69]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f4b0cc5f7f0>
In [70]:
tafe_resignations['institute_service']
Out[70]:
3                     NaN
4                     3-4
5                    7-10
6                     3-4
7                     3-4
8                     3-4
9                     1-2
10       Less than 1 year
13                  11-20
14                   7-10
15                  11-20
16                    NaN
17                   7-10
18                    NaN
19                    NaN
20                    NaN
21                    NaN
22                    5-6
23       Less than 1 year
24                   7-10
26                    NaN
27     More than 20 years
29       Less than 1 year
32       Less than 1 year
36                    NaN
37                    NaN
39                    NaN
40       Less than 1 year
41                  11-20
42                    3-4
              ...        
659                   1-2
660                   3-4
661                   5-6
665                   NaN
666                   NaN
669                   3-4
670                   NaN
671      Less than 1 year
675      Less than 1 year
676                   1-2
677      Less than 1 year
678                   3-4
679                   1-2
681      Less than 1 year
682      Less than 1 year
683      Less than 1 year
684                   3-4
685                   1-2
686                   5-6
688                   5-6
689      Less than 1 year
690                   NaN
691                   3-4
693                   1-2
694                   NaN
696                   5-6
697                   1-2
698                   NaN
699                   5-6
701                   3-4
Name: institute_service, Length: 340, dtype: object

Now that we've verified the years in the dete_resignations dataframe, we'll use them to create a new column. Recall that our end goal is to answer the following question:

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

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

We have already found out about the Tafe survay. Lets create the same column for Dete as well

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

In the last cell I have calculated the Years of service

In [74]:
dete_resignations['institute_service']
Out[74]:
3       7.0
5      18.0
8       3.0
9      15.0
11      3.0
12     14.0
14      5.0
16      NaN
20     30.0
21     32.0
22     15.0
23     39.0
25     17.0
27      7.0
33      9.0
34      6.0
37      1.0
39      NaN
40     35.0
41     38.0
42      1.0
43     36.0
48      3.0
50      3.0
51     19.0
55      4.0
57      9.0
61      1.0
69      6.0
71      1.0
       ... 
747     6.0
751     8.0
752    15.0
753     9.0
755     1.0
762     0.0
766     7.0
769     5.0
770     NaN
771    12.0
774     NaN
784     0.0
786    20.0
788     NaN
789    31.0
790     6.0
791     NaN
794     NaN
797     NaN
798     NaN
802     NaN
803    10.0
804     6.0
806     8.0
807     9.0
808     3.0
815     2.0
816     2.0
819     5.0
821     NaN
Name: institute_service, Length: 311, dtype: float64
Next, Based on the calculated years of service I am going to evaluate some other column to get better understanding of the reasons why exployee resigned.

There are couple of columns in both DataFrames which can help us to answer our question.

But before continuing we must analyze those columns and try to create a new column based on the values provided by employee in those specific columns.

In TAFE_SURVEY we will work with these columns:

1- Contributing Factors. Dissatisfaction

2- Contributing Factors. Job Dissatisfaction

In Dete_survey:

1-job_dissatisfaction
2-dissatisfaction_with_the_department
3-physical_work_environment
4-lack_of_recognition
5-lack_of_job_security
6-work_location
7-employment_conditions
8-work_life_balance
9-workload
In [95]:
tafe_resignations['Contributing Factors. Dissatisfaction'].value_counts(dropna=False)
Out[95]:
-                                         277
Contributing Factors. Dissatisfaction      55
NaN                                         8
Name: Contributing Factors. Dissatisfaction, dtype: int64
In [78]:
tafe_resignations['Contributing Factors. Job Dissatisfaction'].value_counts()
Out[78]:
-                      270
Job Dissatisfaction     62
Name: Contributing Factors. Job Dissatisfaction, dtype: int64
In [109]:
def update_vals(val):
    if pd.isnull(val):
        return np.nan
    elif val == '-':
        return False
    else:
        return True
In [118]:
tafe_resignations['dissatisfied']=tafe_resignations[['Contributing Factors. Dissatisfaction','Contributing Factors. Job Dissatisfaction']].applymap(update_vals).any(axis=1, skipna=False)
In [119]:
tafe_resignations
Out[119]:
id Institute WorkArea cease_date separationtype Contributing Factors. Career Move - Public Sector Contributing Factors. Career Move - Private Sector Contributing Factors. Career Move - Self-employment Contributing Factors. Ill Health Contributing Factors. Maternity/Family ... Contributing Factors. Travel Contributing Factors. Other Contributing Factors. NONE gender age employment_status position institute_service role_service dissatisfied
3 6.341399e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... Travel - - NaN NaN NaN NaN NaN NaN False
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 False
5 6.341475e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - - - - - ... - Other - Female 56 or older Contract/casual Teacher (including LVT) 7-10 7-10 False
6 6.341520e+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) 3-4 3-4 False
7 6.341537e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - - - - - ... - Other - Male 46 50 Permanent Full-time Teacher (including LVT) 3-4 3-4 False
8 6.341579e+17 Southern Queensland Institute of TAFE Delivery (teaching) 2009.0 Resignation - - - - - ... - Other - Female 36 40 Temporary Full-time Tutor 3-4 3-4 False
9 6.341588e+17 Barrier Reef Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector - - - - ... - Other - Female 21 25 Permanent Full-time Administration (AO) 1-2 1-2 False
10 6.341588e+17 Barrier Reef Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector - - - - ... - - - Female 41 45 Temporary Part-time Administration (AO) Less than 1 year Less than 1 year False
13 6.341725e+17 Barrier Reef Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - Female 31 35 Temporary Full-time Administration (AO) 11-20 Less than 1 year False
14 6.341726e+17 Central Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - Female 31 35 Permanent Part-time Teacher (including LVT) 7-10 7-10 True
15 6.341761e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - Other - Female 46 50 Permanent Part-time Technical Officer (TO) 11-20 11-20 False
16 6.341770e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
17 6.341771e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector - - - - ... - - - Female 31 35 Permanent Full-time Administration (AO) 7-10 1-2 True
18 6.341779e+17 Brisbane North Institute of TAFE Delivery (teaching) 2010.0 Resignation NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
19 6.341820e+17 Southbank Institute of Technology Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - - NONE NaN NaN NaN NaN NaN NaN False
20 6.341821e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - - - NaN NaN NaN NaN NaN NaN True
21 6.341831e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - NaN NaN NaN NaN NaN NaN False
22 6.341847e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector - - - - ... - Other - Male 26 30 Temporary Full-time Administration (AO) 5-6 5-6 False
23 6.341907e+17 Central Queensland Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - - - Female 26 30 Contract/casual Administration (AO) Less than 1 year Less than 1 year False
24 6.341907e+17 Central Queensland Institute of TAFE Delivery (teaching) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - Female 31 35 Permanent Part-time Teacher (including LVT) 7-10 7-10 False
26 6.341934e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - NaN NaN NaN NaN NaN NaN True
27 6.341935e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector - - - - ... - - - Male 56 or older Temporary Full-time Administration (AO) More than 20 years 7-10 False
29 6.341994e+17 Barrier Reef Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - Female 31 35 Temporary Full-time Administration (AO) Less than 1 year Less than 1 year False
32 6.342002e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - Career Move - Private Sector - - - ... - - - Female 46 50 Temporary Part-time Professional Officer (PO) Less than 1 year Less than 1 year False
36 6.342062e+17 Sunshine Coast Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector Career Move - Private Sector - - - ... - Other - NaN NaN NaN NaN NaN NaN False
37 6.342080e+17 Southbank Institute of Technology Non-Delivery (corporate) 2010.0 Resignation - - - - - ... Travel - - NaN NaN NaN NaN NaN NaN False
39 6.342081e+17 Southbank Institute of Technology Non-Delivery (corporate) 2010.0 Resignation Career Move - Public Sector - - - - ... - - - NaN NaN NaN NaN NaN NaN False
40 6.342090e+17 Sunshine Coast Institute of TAFE Non-Delivery (corporate) 2010.0 Resignation - - - - - ... - - - Female 26 30 Temporary Part-time Administration (AO) Less than 1 year Less than 1 year True
41 6.342090e+17 Sunshine Coast Institute of TAFE Delivery (teaching) 2010.0 Resignation - - Career Move - Self-employment - - ... - - - Female 36 40 Permanent Full-time Teacher (including LVT) 11-20 11-20 False
42 6.342090e+17 Sunshine Coast Institute of TAFE Delivery (teaching) 2010.0 Resignation - - Career Move - Self-employment - - ... - - - Male 31 35 Permanent Full-time Teacher (including LVT) 3-4 3-4 False
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
659 6.349985e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - Career Move - Private Sector - - - ... - - - Female 46 50 Temporary Part-time Administration (AO) 1-2 1-2 False
660 6.349994e+17 Central Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - Other - Female 41 45 Permanent Part-time Administration (AO) 3-4 3-4 False
661 6.350003e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - Career Move - Private Sector - - - ... - - - Female 46 50 Permanent Full-time Administration (AO) 5-6 More than 20 years True
665 6.350055e+17 Tropical North Institute of TAFE Delivery (teaching) 2013.0 Resignation - - - Ill Health - ... - - - NaN NaN NaN NaN NaN NaN False
666 6.350055e+17 Tropical North Institute of TAFE Delivery (teaching) 2013.0 Resignation - - - - - ... - - - NaN NaN NaN NaN NaN NaN False
669 6.350108e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - Other - Female 26 30 Temporary Full-time Administration (AO) 3-4 3-4 False
670 6.350124e+17 Tropical North Institute of TAFE Delivery (teaching) 2013.0 Resignation NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
671 6.350127e+17 Tropical North Institute of TAFE Delivery (teaching) 2013.0 Resignation - - - - - ... - - - Female 46 50 Temporary Full-time Teacher (including LVT) Less than 1 year Less than 1 year True
675 6.350175e+17 Tropical North Institute of TAFE Delivery (teaching) 2013.0 Resignation - - - - - ... - Other - Male 51-55 Temporary Full-time Teacher (including LVT) Less than 1 year Less than 1 year True
676 6.350194e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation Career Move - Public Sector - - - - ... - - - Female 41 45 Contract/casual Administration (AO) 1-2 Less than 1 year False
677 6.350219e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - Other - Female 36 40 Temporary Full-time Administration (AO) Less than 1 year Less than 1 year False
678 6.350253e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - Career Move - Private Sector - - - ... - - - Male 51-55 Permanent Full-time Administration (AO) 3-4 3-4 False
679 6.350279e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - Ill Health - ... - - - Female 56 or older Temporary Part-time Operational (OO) 1-2 1-2 False
681 6.350314e+17 Central Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - Other - Female 26 30 Temporary Full-time Administration (AO) Less than 1 year Less than 1 year False
682 6.350357e+17 Mount Isa Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation Career Move - Public Sector - - - - ... - - - Female 26 30 Permanent Part-time Administration (AO) Less than 1 year Less than 1 year False
683 6.350374e+17 Central Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - Other - Female 41 45 Temporary Full-time Administration (AO) Less than 1 year Less than 1 year False
684 6.350375e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation Career Move - Public Sector - - - - ... - - - Male 41 45 Contract/casual Administration (AO) 3-4 Less than 1 year False
685 6.350402e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - - - Female 26 30 Temporary Full-time Technical Officer (TO) 1-2 1-2 True
686 6.350426e+17 Brisbane North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation Career Move - Public Sector - - - - ... - - - Female 41 45 Temporary Full-time Administration (AO) 5-6 5-6 False
688 6.350479e+17 Tropical North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - - - ... - - NONE Female 46 50 Permanent Part-time Professional Officer (PO) 5-6 5-6 False
689 6.350480e+17 Central Queensland Institute of TAFE Delivery (teaching) 2013.0 Resignation - - - Ill Health - ... - - - Male 41 45 Permanent Full-time Teacher (including LVT) Less than 1 year Less than 1 year True
690 6.350496e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - Ill Health - ... - - - NaN NaN NaN NaN NaN NaN False
691 6.350496e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - - Ill Health - ... - - - Female 56 or older Permanent Part-time Operational (OO) 3-4 3-4 False
693 6.350599e+17 Tropical North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - Career Move - Private Sector - - - ... - - - Female 26 30 Temporary Full-time Administration (AO) 1-2 1-2 False
694 6.350652e+17 Sunshine Coast Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - Career Move - Private Sector - - - ... - - - NaN NaN NaN NaN NaN NaN False
696 6.350660e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - Career Move - Private Sector - - - ... - - - Male 21 25 Temporary Full-time Operational (OO) 5-6 5-6 False
697 6.350668e+17 Barrier Reef Institute of TAFE Delivery (teaching) 2013.0 Resignation Career Move - Public Sector - - - - ... - - - Male 51-55 Temporary Full-time Teacher (including LVT) 1-2 1-2 False
698 6.350677e+17 Southern Queensland Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation Career Move - Public Sector - - - - ... - - - NaN NaN NaN NaN NaN NaN False
699 6.350704e+17 Tropical North Institute of TAFE Delivery (teaching) 2013.0 Resignation - - - - - ... - Other - Female 51-55 Permanent Full-time Teacher (including LVT) 5-6 1-2 False
701 6.350730e+17 Tropical North Institute of TAFE Non-Delivery (corporate) 2013.0 Resignation - - Career Move - Self-employment - - ... Travel - - Female 26 30 Contract/casual Administration (AO) 3-4 1-2 False

340 rows × 24 columns

Here we have finished creating a column called Dissatisfies and next we will do the same for Dete Dataframe

In [120]:
 
Out[120]:
Contributing Factors. Dissatisfaction Contributing Factors. Job Dissatisfaction
3 - -
4 - -
5 - -
6 - -
7 - -
8 - -
9 - -
10 - -
13 - -
14 Contributing Factors. Dissatisfaction Job Dissatisfaction
15 - -
16 NaN NaN
17 - Job Dissatisfaction
18 NaN NaN
19 - -
20 Contributing Factors. Dissatisfaction Job Dissatisfaction
21 - -
22 - -
23 - -
24 - -
26 Contributing Factors. Dissatisfaction Job Dissatisfaction
27 - -
29 - -
32 - -
36 - -
37 - -
39 - -
40 Contributing Factors. Dissatisfaction -
41 - -
42 - -
... ... ...
659 - -
660 - -
661 - Job Dissatisfaction
665 - -
666 - -
669 - -
670 NaN NaN
671 - Job Dissatisfaction
675 Contributing Factors. Dissatisfaction Job Dissatisfaction
676 - -
677 - -
678 - -
679 - -
681 - -
682 - -
683 - -
684 - -
685 Contributing Factors. Dissatisfaction Job Dissatisfaction
686 - -
688 - -
689 - Job Dissatisfaction
690 - -
691 - -
693 - -
694 - -
696 - -
697 - -
698 - -
699 - -
701 - -

340 rows × 2 columns

In [121]:
dete_resignations.columns
Out[121]:
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', 'institute_service'],
      dtype='object')
In [122]:
dete_col=['job_dissatisfaction', 'dissatisfaction_with_the_department',
       'physical_work_environment', 'lack_of_recognition',
       'lack_of_job_security', 'work_location', 'employment_conditions','work_life_balance','workload']
In [126]:
dete_resignations['dissatisfied']=dete_resignations[dete_col].any(axis=1,skipna=False)
In [127]:
dete_resignations['dissatisfied']
Out[127]:
3      False
5       True
8      False
9       True
11     False
12     False
14      True
16      True
20     False
21     False
22      True
23      True
25      True
27     False
33      True
34      True
37     False
39      True
40     False
41      True
42     False
43      True
48     False
50     False
51     False
55     False
57     False
61     False
69      True
71     False
       ...  
747    False
751     True
752    False
753     True
755    False
762    False
766    False
769    False
770    False
771    False
774    False
784     True
786     True
788    False
789    False
790     True
791     True
794    False
797    False
798    False
802    False
803    False
804    False
806    False
807     True
808    False
815    False
816    False
819     True
821    False
Name: dissatisfied, Length: 311, dtype: bool
In [142]:
dete_col_with_dissatissfied=['job_dissatisfaction', 'dissatisfaction_with_the_department',
       'physical_work_environment', 'lack_of_recognition',
       'lack_of_job_security', 'work_location', 'employment_conditions','work_life_balance','workload','dissatisfied']

This is an over view of Dete Dataframe with Dissatisfied column

In [144]:
dete_resignations[dete_col_with_dissatissfied]
Out[144]:
job_dissatisfaction dissatisfaction_with_the_department physical_work_environment lack_of_recognition lack_of_job_security work_location employment_conditions work_life_balance workload dissatisfied
3 False False False False False False False False False False
5 False False False False False False True False False True
8 False False False False False False False False False False
9 True True False False False False False False False True
11 False False False False False False False False False False
12 False False False False False False False False False False
14 True True False False False False False False False True
16 False False False True False False False False False True
20 False False False False False False False False False False
21 False False False False False False False False False False
22 False True False True False False False True False True
23 True False False False False True False True False True
25 False False False False False False False True False True
27 False False False False False False False False False False
33 False False False True False False False False False True
34 False False False False False False False True False True
37 False False False False False False False False False False
39 True False False True False False True True False True
40 False False False False False False False False False False
41 False False False False False False False True False True
42 False False False False False False False False False False
43 True False False False False False False True True True
48 False False False False False False False False False False
50 False False False False False False False False False False
51 False False False False False False False False False False
55 False False False False False False False False False False
57 False False False False False False False False False False
61 False False False False False False False False False False
69 True False False False False True False False False True
71 False False False False False False False False False False
... ... ... ... ... ... ... ... ... ... ...
747 False False False False False False False False False False
751 True False False False False False False False False True
752 False False False False False False False False False False
753 True False False False False False False True False True
755 False False False False False False False False False False
762 False False False False False False False False False False
766 False False False False False False False False False False
769 False False False False False False False False False False
770 False False False False False False False False False False
771 False False False False False False False False False False
774 False False False False False False False False False False
784 True False False True False False False True False True
786 True False False False True False False False False True
788 False False False False False False False False False False
789 False False False False False False False False False False
790 False False False True False False True False True True
791 False False False True False False False False False True
794 False False False False False False False False False False
797 False False False False False False False False False False
798 False False False False False False False False False False
802 False False False False False False False False False False
803 False False False False False False False False False False
804 False False False False False False False False False False
806 False False False False False False False False False False
807 False True False False False False False True False True
808 False False False False False False False False False False
815 False False False False False False False False False False
816 False False False False False False False False False False
819 False False False False False False False True False True
821 False False False False False False False False False False

311 rows × 10 columns

In [145]:
dete_resignations['institute']='DETE'
In [146]:
tafe_resignations['institute']='TAFE'
In [150]:
len(tafe_resignations.columns)
Out[150]:
25
In [151]:
len(dete_resignations.columns)
Out[151]:
38
In [152]:
dete_resignations_up=dete_resignations.copy()
tafe_resignations_up=tafe_resignations.copy()
In [192]:
combined=pd.concat([dete_resignations_up,tafe_resignations_up],ignore_index=True)
In [193]:
combined.notnull().sum().sort_values()
Out[193]:
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
career_move_to_public_sector                           311
employment_conditions                                  311
work_location                                          311
lack_of_job_security                                   311
job_dissatisfaction                                    311
dissatisfaction_with_the_department                    311
workload                                               311
lack_of_recognition                                    311
interpersonal_conflicts                                311
maternity/family                                       311
none_of_the_above                                      311
physical_work_environment                              311
relocation                                             311
study/travel                                           311
traumatic_incident                                     311
work_life_balance                                      311
career_move_to_private_sector                          311
ill_health                                             311
Contributing Factors. Career Move - Private Sector     332
Contributing Factors. Other                            332
Contributing Factors. Career Move - Public Sector      332
Contributing Factors. Career Move - Self-employment    332
Contributing Factors. Travel                           332
Contributing Factors. Study                            332
Contributing Factors. Dissatisfaction                  332
Contributing Factors. Ill Health                       332
Contributing Factors. NONE                             332
Contributing Factors. Maternity/Family                 332
Contributing Factors. Job Dissatisfaction              332
Contributing Factors. Interpersonal Conflict           332
WorkArea                                               340
Institute                                              340
institute_service                                      563
gender                                                 592
age                                                    596
employment_status                                      597
position                                               598
cease_date                                             635
dissatisfied                                           643
id                                                     651
separationtype                                         651
institute                                              651
dtype: int64

Here I have removed all the columns which the non-null values are less than 500

In [280]:
combined_update=combined.dropna(thresh=500 ,axis=1).copy()
In [281]:
combined_update['institute_service'].value_counts()
Out[281]:
Less than 1 year      73
1-2                   64
3-4                   63
5-6                   33
11-20                 26
5.0                   23
1.0                   22
7-10                  21
3.0                   20
0.0                   20
6.0                   17
4.0                   16
9.0                   14
2.0                   14
7.0                   13
More than 20 years    10
13.0                   8
8.0                    8
20.0                   7
15.0                   7
14.0                   6
17.0                   6
12.0                   6
10.0                   6
22.0                   6
18.0                   5
16.0                   5
24.0                   4
23.0                   4
11.0                   4
39.0                   3
19.0                   3
21.0                   3
32.0                   3
36.0                   2
25.0                   2
26.0                   2
28.0                   2
30.0                   2
42.0                   1
35.0                   1
49.0                   1
34.0                   1
38.0                   1
33.0                   1
29.0                   1
27.0                   1
41.0                   1
31.0                   1
Name: institute_service, dtype: int64
In [282]:
patt=r'Less than | year|More than|s'
combined_update['institute_service']=combined_update['institute_service'].astype('str').str.replace(patt,'').str.replace('-','.').str.split('.').str.get(0).astype('float')

I have created the regex to clean the Institute Service column in the cell above Next I am going to categorize employee based on their service years:

We'll use the slightly modified definitions below:

New: Less than 3 years at a company

Experienced: 3-6 years at a company

Established: 7-10 years at a company

Veteran: 11 or more years at a company

In [283]:
def career_stage(val):
    if val < 3:
        return 'New'
    elif 3<val<6:
        return 'Experienced'
    elif 7<val<10:
        return 'Established'
    elif val > 11:
        return 'Veteran'
    elif pd.isnull(val):
        return np.nan


        
In [284]:
combined_update['service_cat']=combined_update['institute_service'].apply(career_stage)
In [285]:
combined_update
Out[285]:
age cease_date dissatisfied employment_status gender id institute institute_service position separationtype service_cat
0 36-40 2012.0 False Permanent Full-time Female 4.000000e+00 DETE 7.0 Teacher Resignation-Other reasons None
1 41-45 2012.0 True Permanent Full-time Female 6.000000e+00 DETE 18.0 Guidance Officer Resignation-Other reasons Veteran
2 31-35 2012.0 False Permanent Full-time Female 9.000000e+00 DETE 3.0 Teacher Resignation-Other reasons None
3 46-50 2012.0 True Permanent Part-time Female 1.000000e+01 DETE 15.0 Teacher Aide Resignation-Other employer Veteran
4 31-35 2012.0 False Permanent Full-time Male 1.200000e+01 DETE 3.0 Teacher Resignation-Move overseas/interstate None
5 36-40 2012.0 False Permanent Full-time Female 1.300000e+01 DETE 14.0 Teacher Resignation-Other reasons Veteran
6 31-35 2012.0 True Permanent Full-time Male 1.500000e+01 DETE 5.0 Teacher Resignation-Other employer Experienced
7 61 or older 2012.0 True Permanent Part-time Male 1.700000e+01 DETE NaN Teacher Aide Resignation-Other reasons NaN
8 56-60 2012.0 False Permanent Full-time Male 2.100000e+01 DETE 30.0 Teacher Resignation-Other employer Veteran
9 51-55 2012.0 False Permanent Part-time Female 2.200000e+01 DETE 32.0 Cleaner Resignation-Other reasons Veteran
10 46-50 2012.0 True Permanent Part-time Female 2.300000e+01 DETE 15.0 School Administrative Staff Resignation-Other reasons Veteran
11 61 or older 2012.0 True Permanent Full-time Female 2.400000e+01 DETE 39.0 Teacher Resignation-Other reasons Veteran
12 41-45 2012.0 True Permanent Part-time Female 2.600000e+01 DETE 17.0 Teacher Resignation-Other reasons Veteran
13 21-25 2012.0 False Permanent Full-time Female 2.800000e+01 DETE 7.0 Public Servant Resignation-Other employer None
14 36-40 2012.0 True Permanent Full-time Male 3.400000e+01 DETE 9.0 Teacher Resignation-Other reasons Established
15 61 or older 2012.0 True Permanent Part-time Male 3.500000e+01 DETE 6.0 Cleaner Resignation-Other reasons None
16 21-25 2012.0 False Temporary Part-time Female 3.800000e+01 DETE 1.0 Teacher Aide Resignation-Other reasons New
17 21-25 2012.0 True Permanent Full-time Female 4.000000e+01 DETE NaN Teacher Resignation-Move overseas/interstate NaN
18 56-60 2012.0 False Permanent Full-time Male 4.100000e+01 DETE 35.0 Teacher Resignation-Other employer Veteran
19 51-55 2012.0 True Permanent Full-time Female 4.200000e+01 DETE 38.0 Head of Curriculum/Head of Special Education Resignation-Other reasons Veteran
20 41-45 2012.0 False Permanent Part-time Female 4.300000e+01 DETE 1.0 Cleaner Resignation-Move overseas/interstate New
21 51-55 2012.0 True Permanent Full-time Male 4.400000e+01 DETE 36.0 Teacher Resignation-Other reasons Veteran
22 21-25 2012.0 False Permanent Full-time Male 4.900000e+01 DETE 3.0 Cleaner Resignation-Move overseas/interstate None
23 21-25 2012.0 False Permanent Full-time Male 5.100000e+01 DETE 3.0 Cleaner Resignation-Move overseas/interstate None
24 61 or older 2012.0 False Permanent Full-time Female 5.200000e+01 DETE 19.0 Cleaner Resignation-Other reasons Veteran
25 26-30 2012.0 False Permanent Part-time Female 5.600000e+01 DETE 4.0 Teacher Aide Resignation-Other employer Experienced
26 46-50 2012.0 False Permanent Full-time Male 5.800000e+01 DETE 9.0 Teacher Resignation-Other employer Established
27 31-35 2012.0 False Temporary Part-time Female 6.200000e+01 DETE 1.0 Schools Officer Resignation-Other reasons New
28 36-40 2012.0 True Permanent Full-time Female 7.000000e+01 DETE 6.0 Public Servant Resignation-Other reasons None
29 36-40 2012.0 False Permanent Part-time Female 7.200000e+01 DETE 1.0 Teacher Aide Resignation-Other reasons New
... ... ... ... ... ... ... ... ... ... ... ...
621 46 50 2013.0 False Temporary Part-time Female 6.349985e+17 TAFE 1.0 Administration (AO) Resignation New
622 41 45 2013.0 False Permanent Part-time Female 6.349994e+17 TAFE 3.0 Administration (AO) Resignation None
623 46 50 2013.0 True Permanent Full-time Female 6.350003e+17 TAFE 5.0 Administration (AO) Resignation Experienced
624 NaN 2013.0 False NaN NaN 6.350055e+17 TAFE NaN NaN Resignation NaN
625 NaN 2013.0 False NaN NaN 6.350055e+17 TAFE NaN NaN Resignation NaN
626 26 30 2013.0 False Temporary Full-time Female 6.350108e+17 TAFE 3.0 Administration (AO) Resignation None
627 NaN 2013.0 NaN NaN NaN 6.350124e+17 TAFE NaN NaN Resignation NaN
628 46 50 2013.0 True Temporary Full-time Female 6.350127e+17 TAFE 1.0 Teacher (including LVT) Resignation New
629 51-55 2013.0 True Temporary Full-time Male 6.350175e+17 TAFE 1.0 Teacher (including LVT) Resignation New
630 41 45 2013.0 False Contract/casual Female 6.350194e+17 TAFE 1.0 Administration (AO) Resignation New
631 36 40 2013.0 False Temporary Full-time Female 6.350219e+17 TAFE 1.0 Administration (AO) Resignation New
632 51-55 2013.0 False Permanent Full-time Male 6.350253e+17 TAFE 3.0 Administration (AO) Resignation None
633 56 or older 2013.0 False Temporary Part-time Female 6.350279e+17 TAFE 1.0 Operational (OO) Resignation New
634 26 30 2013.0 False Temporary Full-time Female 6.350314e+17 TAFE 1.0 Administration (AO) Resignation New
635 26 30 2013.0 False Permanent Part-time Female 6.350357e+17 TAFE 1.0 Administration (AO) Resignation New
636 41 45 2013.0 False Temporary Full-time Female 6.350374e+17 TAFE 1.0 Administration (AO) Resignation New
637 41 45 2013.0 False Contract/casual Male 6.350375e+17 TAFE 3.0 Administration (AO) Resignation None
638 26 30 2013.0 True Temporary Full-time Female 6.350402e+17 TAFE 1.0 Technical Officer (TO) Resignation New
639 41 45 2013.0 False Temporary Full-time Female 6.350426e+17 TAFE 5.0 Administration (AO) Resignation Experienced
640 46 50 2013.0 False Permanent Part-time Female 6.350479e+17 TAFE 5.0 Professional Officer (PO) Resignation Experienced
641 41 45 2013.0 True Permanent Full-time Male 6.350480e+17 TAFE 1.0 Teacher (including LVT) Resignation New
642 NaN 2013.0 False NaN NaN 6.350496e+17 TAFE NaN NaN Resignation NaN
643 56 or older 2013.0 False Permanent Part-time Female 6.350496e+17 TAFE 3.0 Operational (OO) Resignation None
644 26 30 2013.0 False Temporary Full-time Female 6.350599e+17 TAFE 1.0 Administration (AO) Resignation New
645 NaN 2013.0 False NaN NaN 6.350652e+17 TAFE NaN NaN Resignation NaN
646 21 25 2013.0 False Temporary Full-time Male 6.350660e+17 TAFE 5.0 Operational (OO) Resignation Experienced
647 51-55 2013.0 False Temporary Full-time Male 6.350668e+17 TAFE 1.0 Teacher (including LVT) Resignation New
648 NaN 2013.0 False NaN NaN 6.350677e+17 TAFE NaN NaN Resignation NaN
649 51-55 2013.0 False Permanent Full-time Female 6.350704e+17 TAFE 5.0 Teacher (including LVT) Resignation Experienced
650 26 30 2013.0 False Contract/casual Female 6.350730e+17 TAFE 3.0 Administration (AO) Resignation None

651 rows × 11 columns

In [286]:
combined_update['dissatisfied'].value_counts(dropna=False)
Out[286]:
False    403
True     240
NaN        8
Name: dissatisfied, dtype: int64
In [287]:
combined_update['dissatisfied']=combined_update['dissatisfied'].fillna(False)

Here I filled the NaN cells with most appeared value in Dissatisfied column

In [300]:
combined_update['dissatisfied']
Out[300]:
0      False
1       True
2      False
3       True
4      False
5      False
6       True
7       True
8      False
9      False
10      True
11      True
12      True
13     False
14      True
15      True
16     False
17      True
18     False
19      True
20     False
21      True
22     False
23     False
24     False
25     False
26     False
27     False
28      True
29     False
       ...  
621    False
622    False
623     True
624    False
625    False
626    False
627    False
628     True
629     True
630    False
631    False
632    False
633    False
634    False
635    False
636    False
637    False
638     True
639    False
640    False
641     True
642    False
643    False
644    False
645    False
646    False
647    False
648    False
649    False
650    False
Name: dissatisfied, Length: 651, dtype: bool
In [315]:
df_for_plot=combined_update.pivot_table(index='service_cat', values='dissatisfied')
In [309]:
dissatissfied=combined_update[combined_update['dissatisfied']==True]
In [311]:
dissatissfied['service_cat'].value_counts(dropna=False)
Out[311]:
NaN            82
Veteran        59
New            57
Experienced    26
Established    16
Name: service_cat, dtype: int64
In [317]:
%matplotlib inline

df_for_plot.plot(kind='bar')
Out[317]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f4b14924710>
In [ ]: