# import libraries
import datetime as dt
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import seaborn as sns
# read in the dete survey
dete_df = pd.read_csv("dete_survey.csv")
# print tabular information
dete_df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 822 entries, 0 to 821
Data columns (total 56 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 ID 822 non-null int64
1 SeparationType 822 non-null object
2 Cease Date 822 non-null object
3 DETE Start Date 822 non-null object
4 Role Start Date 822 non-null object
5 Position 817 non-null object
6 Classification 455 non-null object
7 Region 822 non-null object
8 Business Unit 126 non-null object
9 Employment Status 817 non-null object
10 Career move to public sector 822 non-null bool
11 Career move to private sector 822 non-null bool
12 Interpersonal conflicts 822 non-null bool
13 Job dissatisfaction 822 non-null bool
14 Dissatisfaction with the department 822 non-null bool
15 Physical work environment 822 non-null bool
16 Lack of recognition 822 non-null bool
17 Lack of job security 822 non-null bool
18 Work location 822 non-null bool
19 Employment conditions 822 non-null bool
20 Maternity/family 822 non-null bool
21 Relocation 822 non-null bool
22 Study/Travel 822 non-null bool
23 Ill Health 822 non-null bool
24 Traumatic incident 822 non-null bool
25 Work life balance 822 non-null bool
26 Workload 822 non-null bool
27 None of the above 822 non-null bool
28 Professional Development 808 non-null object
29 Opportunities for promotion 735 non-null object
30 Staff morale 816 non-null object
31 Workplace issue 788 non-null object
32 Physical environment 817 non-null object
33 Worklife balance 815 non-null object
34 Stress and pressure support 810 non-null object
35 Performance of supervisor 813 non-null object
36 Peer support 812 non-null object
37 Initiative 813 non-null object
38 Skills 811 non-null object
39 Coach 767 non-null object
40 Career Aspirations 746 non-null object
41 Feedback 792 non-null object
42 Further PD 768 non-null object
43 Communication 814 non-null object
44 My say 812 non-null object
45 Information 816 non-null object
46 Kept informed 813 non-null object
47 Wellness programs 766 non-null object
48 Health & Safety 793 non-null object
49 Gender 798 non-null object
50 Age 811 non-null object
51 Aboriginal 16 non-null object
52 Torres Strait 3 non-null object
53 South Sea 7 non-null object
54 Disability 23 non-null object
55 NESB 32 non-null object
dtypes: bool(18), int64(1), object(37)
memory usage: 258.6+ KB
# print summary statistics
dete_df.describe(include="all")
| IDfloat641 – 823 | SeparationTypeobject 8229.1% 3 others27.3% Missing63.6% | Cease Dateobject 8229.1% 3 others27.3% Missing63.6% | DETE Start Dateobject 8229.1% 3 others27.3% Missing63.6% | Role Start Dateobject 8229.1% 3 others27.3% Missing63.6% | Positionobject 8179.1% 3 others27.3% Missing63.6% | Classificationobject 4559.1% 3 others27.3% Missing63.6% | Regionobject 8229.1% 3 others27.3% Missing63.6% | Business Unitobject 1269.1% 3 others27.3% Missing63.6% | Employment Statusobject 8179.1% 3 others27.3% Missing63.6% | Career move to public sectorobject 8229.1% 3 others27.3% Missing63.6% | Career move to private sectorobject 8229.1% 3 others27.3% Missing63.6% | Interpersonal conflictsobject 8229.1% 3 others27.3% Missing63.6% | Job dissatisfactionobject 8229.1% 3 others27.3% Missing63.6% | Dissatisfaction with the departmentobject 8229.1% 3 others27.3% Missing63.6% | Physical work environmentobject 8229.1% 3 others27.3% Missing63.6% | Lack of recognitionobject 8229.1% 3 others27.3% Missing63.6% | Lack of job securityobject 8229.1% 3 others27.3% Missing63.6% | Work locationobject 8229.1% 3 others27.3% Missing63.6% | Employment conditionsobject 8229.1% 3 others27.3% Missing63.6% | Maternity/familyobject 8229.1% 3 others27.3% Missing63.6% | Relocationobject 8229.1% 3 others27.3% Missing63.6% | Study/Travelobject 8229.1% 3 others27.3% Missing63.6% | Ill Healthobject 8229.1% 3 others27.3% Missing63.6% | Traumatic incidentobject 8229.1% 3 others27.3% Missing63.6% | Work life balanceobject 8229.1% 3 others27.3% Missing63.6% | Workloadobject 8229.1% 3 others27.3% Missing63.6% | None of the aboveobject 8229.1% 3 others27.3% Missing63.6% | Professional Developmentobject 8089.1% 3 others27.3% Missing63.6% | Opportunities for promotionobject 7359.1% 3 others27.3% Missing63.6% | Staff moraleobject 8169.1% 3 others27.3% Missing63.6% | Workplace issueobject 7889.1% 3 others27.3% Missing63.6% | Physical environmentobject 8179.1% 3 others27.3% Missing63.6% | Worklife balanceobject 8159.1% 3 others27.3% Missing63.6% | Stress and pressure supportobject 8109.1% 3 others27.3% Missing63.6% | Performance of supervisorobject 8139.1% 3 others27.3% Missing63.6% | Peer supportobject 8129.1% 3 others27.3% Missing63.6% | Initiativeobject 8139.1% 3 others27.3% Missing63.6% | Skillsobject 8119.1% 3 others27.3% Missing63.6% | Coachobject 7679.1% 3 others27.3% Missing63.6% | Career Aspirationsobject 7469.1% 3 others27.3% Missing63.6% | Feedbackobject 7929.1% 3 others27.3% Missing63.6% | Further PDobject 7689.1% 3 others27.3% Missing63.6% | Communicationobject 8149.1% 3 others27.3% Missing63.6% | My sayobject 8129.1% 3 others27.3% Missing63.6% | Informationobject 8169.1% 3 others27.3% Missing63.6% | Kept informedobject 8139.1% 3 others27.3% Missing63.6% | Wellness programsobject 7669.1% 3 others27.3% Missing63.6% | Health & Safetyobject 7939.1% 3 others27.3% Missing63.6% | Genderobject 7989.1% 3 others27.3% Missing63.6% | Ageobject 8119.1% 3 others27.3% Missing63.6% | Aboriginalobject 1618.2% 2 others18.2% Missing63.6% | Torres Straitobject 318.2% 2 others18.2% Missing63.6% | South Seaobject 718.2% 2 others18.2% Missing63.6% | Disabilityobject 2318.2% 2 others18.2% Missing63.6% | NESBobject 3218.2% 2 others18.2% Missing63.6% | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
count | 822 | 822 | 822 | 822 | 822 | 817 | 455 | 822 | 126 | 817 | 822 | 822 | 822 | 822 | 822 | 822 | 822 | 822 | 822 | 822 | 822 | 822 | 822 | 822 | 822 | 822 | 822 | 822 | 808 | 735 | 816 | 788 | 817 | 815 | 810 | 813 | 812 | 813 | 811 | 767 | 746 | 792 | 768 | 814 | 812 | 816 | 813 | 766 | 793 | 798 | 811 | 16 | 3 | 7 | 23 | 32 | |
unique | nan | 9 | 25 | 51 | 46 | 15 | 8 | 9 | 14 | 5 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 2 | 10 | 1 | 1 | 1 | 1 | 1 | |
top | nan | Age Retirement | 2012 | Not Stated | Not Stated | Teacher | Primary | Metropolitan | Education Queensland | Permanent Full-time | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | A | A | A | A | A | A | A | A | A | A | A | A | A | A | A | A | A | A | A | A | A | Female | 61 or older | Yes | Yes | Yes | Yes | Yes | |
freq | nan | 285 | 344 | 73 | 98 | 324 | 161 | 135 | 54 | 434 | 800 | 742 | 788 | 733 | 761 | 806 | 765 | 794 | 795 | 788 | 760 | 754 | 785 | 710 | 794 | 605 | 735 | 605 | 413 | 242 | 335 | 357 | 467 | 359 | 342 | 349 | 401 | 396 | 372 | 345 | 246 | 348 | 293 | 399 | 400 | 436 | 401 | 253 | 386 | 573 | 222 | 16 | 3 | 7 | 23 | 32 | |
mean | 411.6934306569343 | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | |
| Expand rows 5 - 5 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
min | 1 | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | |
25% | 206.25 | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | |
50% | 411.5 | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | |
75% | 616.75 | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | |
max | 823 | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | |
| 11 rows × 56 columns | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
# print the first 5 rows
dete_df.head()
| IDint64 | SeparationTypeobject | Cease Dateobject | DETE Start Dateobject | Role Start Dateobject | Positionobject | Classificationobject | Regionobject | Business Unitobject | Employment Statusobject | Career move to public sectorbool | Career move to private sectorbool | Interpersonal conflictsbool | Job dissatisfactionbool | Dissatisfaction with the departmentbool | Physical work environmentbool | Lack of recognitionbool | Lack of job securitybool | Work locationbool | Employment conditionsbool | Maternity/familybool | Relocationbool | Study/Travelbool | Ill Healthbool | Traumatic incidentbool | Work life balancebool | Workloadbool | None of the abovebool | Professional Developmentobject | Opportunities for promotionobject | Staff moraleobject | Workplace issueobject | Physical environmentobject | Worklife balanceobject | Stress and pressure supportobject | Performance of supervisorobject | Peer supportobject | Initiativeobject | Skillsobject | Coachobject | Career Aspirationsobject | Feedbackobject | Further PDobject | Communicationobject | My sayobject | Informationobject | Kept informedobject | Wellness programsobject | Health & Safetyobject | Genderobject | Ageobject | Aboriginalobject | Torres Straitobject | South Seaobject | Disabilityobject | NESBobject | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | Ill Health Retirement | 08/2012 | 1984 | 2004 | Public Servant | A01-A04 | Central Office | Corporate Strategy and Peformance | Permanent Full-time | true | false | false | true | false | false | true | false | false | false | false | false | false | false | false | false | false | true | A | A | N | N | N | A | A | A | A | N | N | N | A | A | A | N | A | A | N | N | N | Male | 56-60 | nan | nan | nan | nan | Yes | |
1 | 2 | Voluntary Early Retirement (VER) | 08/2012 | Not Stated | Not Stated | Public Servant | AO5-AO7 | Central Office | Corporate Strategy and Peformance | Permanent Full-time | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | A | A | N | N | N | N | A | A | A | N | N | N | A | A | A | N | A | A | N | N | N | Male | 56-60 | nan | nan | nan | nan | nan | |
2 | 3 | Voluntary Early Retirement (VER) | 05/2012 | 2011 | 2011 | Schools Officer | nan | Central Office | Education Queensland | Permanent Full-time | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | true | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | A | A | N | N | N | N | Male | 61 or older | nan | nan | nan | nan | nan | |
3 | 4 | Resignation-Other reasons | 05/2012 | 2005 | 2006 | Teacher | Primary | Central Queensland | nan | Permanent Full-time | false | true | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | A | N | N | N | A | A | N | N | A | A | A | A | A | A | A | A | A | A | A | N | A | Female | 36-40 | nan | nan | nan | nan | nan | |
4 | 5 | Age Retirement | 05/2012 | 1970 | 1989 | Head of Curriculum/Head of Special Education | nan | South East | nan | Permanent Full-time | false | false | false | false | false | false | false | false | false | false | false | false | false | false | false | true | false | false | A | A | N | N | D | D | N | A | A | A | A | A | A | SA | SA | D | D | A | N | A | M | Female | 61 or older | nan | nan | nan | nan | nan | |
| 5 rows × 56 columns | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
# read in the tafe survey
tafe_df = pd.read_csv("tafe_survey.csv", encoding="cp1252")
# print tabular information
tafe_df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 702 entries, 0 to 701
Data columns (total 72 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Record ID 702 non-null int64
1 Institute 702 non-null object
2 WorkArea 702 non-null object
3 CESSATION YEAR 695 non-null float64
4 Reason for ceasing employment 701 non-null object
5 Contributing Factors. Career Move - Public Sector 437 non-null object
6 Contributing Factors. Career Move - Private Sector 437 non-null object
7 Contributing Factors. Career Move - Self-employment 437 non-null object
8 Contributing Factors. Ill Health 437 non-null object
9 Contributing Factors. Maternity/Family 437 non-null object
10 Contributing Factors. Dissatisfaction 437 non-null object
11 Contributing Factors. Job Dissatisfaction 437 non-null object
12 Contributing Factors. Interpersonal Conflict 437 non-null object
13 Contributing Factors. Study 437 non-null object
14 Contributing Factors. Travel 437 non-null object
15 Contributing Factors. Other 437 non-null object
16 Contributing Factors. NONE 437 non-null object
17 Main Factor. Which of these was the main factor for leaving? 113 non-null object
18 InstituteViews. Topic:1. I feel the senior leadership had a clear vision and direction 608 non-null object
19 InstituteViews. Topic:2. I was given access to skills training to help me do my job better 613 non-null object
20 InstituteViews. Topic:3. I was given adequate opportunities for personal development 610 non-null object
21 InstituteViews. Topic:4. I was given adequate opportunities for promotion within %Institute]Q25LBL% 608 non-null object
22 InstituteViews. Topic:5. I felt the salary for the job was right for the responsibilities I had 615 non-null object
23 InstituteViews. Topic:6. The organisation recognised when staff did good work 607 non-null object
24 InstituteViews. Topic:7. Management was generally supportive of me 614 non-null object
25 InstituteViews. Topic:8. Management was generally supportive of my team 608 non-null object
26 InstituteViews. Topic:9. I was kept informed of the changes in the organisation which would affect me 610 non-null object
27 InstituteViews. Topic:10. Staff morale was positive within the Institute 602 non-null object
28 InstituteViews. Topic:11. If I had a workplace issue it was dealt with quickly 601 non-null object
29 InstituteViews. Topic:12. If I had a workplace issue it was dealt with efficiently 597 non-null object
30 InstituteViews. Topic:13. If I had a workplace issue it was dealt with discreetly 601 non-null object
31 WorkUnitViews. Topic:14. I was satisfied with the quality of the management and supervision within my work unit 609 non-null object
32 WorkUnitViews. Topic:15. I worked well with my colleagues 605 non-null object
33 WorkUnitViews. Topic:16. My job was challenging and interesting 607 non-null object
34 WorkUnitViews. Topic:17. I was encouraged to use my initiative in the course of my work 610 non-null object
35 WorkUnitViews. Topic:18. I had sufficient contact with other people in my job 613 non-null object
36 WorkUnitViews. Topic:19. I was given adequate support and co-operation by my peers to enable me to do my job 609 non-null object
37 WorkUnitViews. Topic:20. I was able to use the full range of my skills in my job 609 non-null object
38 WorkUnitViews. Topic:21. I was able to use the full range of my abilities in my job. ; Category:Level of Agreement; Question:YOUR VIEWS ABOUT YOUR WORK UNIT] 608 non-null object
39 WorkUnitViews. Topic:22. I was able to use the full range of my knowledge in my job 608 non-null object
40 WorkUnitViews. Topic:23. My job provided sufficient variety 611 non-null object
41 WorkUnitViews. Topic:24. I was able to cope with the level of stress and pressure in my job 610 non-null object
42 WorkUnitViews. Topic:25. My job allowed me to balance the demands of work and family to my satisfaction 611 non-null object
43 WorkUnitViews. Topic:26. My supervisor gave me adequate personal recognition and feedback on my performance 606 non-null object
44 WorkUnitViews. Topic:27. My working environment was satisfactory e.g. sufficient space, good lighting, suitable seating and working area 610 non-null object
45 WorkUnitViews. Topic:28. I was given the opportunity to mentor and coach others in order for me to pass on my skills and knowledge prior to my cessation date 609 non-null object
46 WorkUnitViews. Topic:29. There was adequate communication between staff in my unit 603 non-null object
47 WorkUnitViews. Topic:30. Staff morale was positive within my work unit 606 non-null object
48 Induction. Did you undertake Workplace Induction? 619 non-null object
49 InductionInfo. Topic:Did you undertake a Corporate Induction? 432 non-null object
50 InductionInfo. Topic:Did you undertake a Institute Induction? 483 non-null object
51 InductionInfo. Topic: Did you undertake Team Induction? 440 non-null object
52 InductionInfo. Face to Face Topic:Did you undertake a Corporate Induction; Category:How it was conducted? 555 non-null object
53 InductionInfo. On-line Topic:Did you undertake a Corporate Induction; Category:How it was conducted? 555 non-null object
54 InductionInfo. Induction Manual Topic:Did you undertake a Corporate Induction? 555 non-null object
55 InductionInfo. Face to Face Topic:Did you undertake a Institute Induction? 530 non-null object
56 InductionInfo. On-line Topic:Did you undertake a Institute Induction? 555 non-null object
57 InductionInfo. Induction Manual Topic:Did you undertake a Institute Induction? 553 non-null object
58 InductionInfo. Face to Face Topic: Did you undertake Team Induction; Category? 555 non-null object
59 InductionInfo. On-line Topic: Did you undertake Team Induction?process you undertook and how it was conducted.] 555 non-null object
60 InductionInfo. Induction Manual Topic: Did you undertake Team Induction? 555 non-null object
61 Workplace. Topic:Did you and your Manager develop a Performance and Professional Development Plan (PPDP)? 608 non-null object
62 Workplace. Topic:Does your workplace promote a work culture free from all forms of unlawful discrimination? 594 non-null object
63 Workplace. Topic:Does your workplace promote and practice the principles of employment equity? 587 non-null object
64 Workplace. Topic:Does your workplace value the diversity of its employees? 586 non-null object
65 Workplace. Topic:Would you recommend the Institute as an employer to others? 581 non-null object
66 Gender. What is your Gender? 596 non-null object
67 CurrentAge. Current Age 596 non-null object
68 Employment Type. Employment Type 596 non-null object
69 Classification. Classification 596 non-null object
70 LengthofServiceOverall. Overall Length of Service at Institute (in years) 596 non-null object
71 LengthofServiceCurrent. Length of Service at current workplace (in years) 596 non-null object
dtypes: float64(1), int64(1), object(70)
memory usage: 395.0+ KB
# print summary statistics
tafe_df.describe(include="all")
| Record IDfloat64702 – 635073030973791000 | Instituteobject 7029.1% 3 others27.3% Missing63.6% | WorkAreaobject 7029.1% 3 others27.3% Missing63.6% | CESSATION YEARfloat640.906 – 2013 | Reason for ceasing employmentobject 7019.1% 3 others27.3% Missing63.6% | Contributing Factors. Career Move - Public Sector object 4379.1% 3 others27.3% Missing63.6% | Contributing Factors. Career Move - Private Sector object 4379.1% 3 others27.3% Missing63.6% | Contributing Factors. Career Move - Self-employmentobject 4379.1% 3 others27.3% Missing63.6% | Contributing Factors. Ill Healthobject 4379.1% 3 others27.3% Missing63.6% | Contributing Factors. Maternity/Familyobject 4379.1% 3 others27.3% Missing63.6% | Contributing Factors. Dissatisfactionobject 4379.1% 3 others27.3% Missing63.6% | Contributing Factors. Job Dissatisfactionobject 4379.1% 3 others27.3% Missing63.6% | Contributing Factors. Interpersonal Conflictobject 4379.1% 3 others27.3% Missing63.6% | Contributing Factors. Studyobject 4379.1% 3 others27.3% Missing63.6% | Contributing Factors. Travelobject 4379.1% 3 others27.3% Missing63.6% | Contributing Factors. Otherobject 4379.1% 3 others27.3% Missing63.6% | Contributing Factors. NONEobject 4379.1% 3 others27.3% Missing63.6% | Main Factor. Which of these was the main factor for leaving?object 1139.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:1. I feel the senior leadership had a clear vision and directionobject 6089.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:2. I was given access to skills training to help me do my job betterobject 6139.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:3. I was given adequate opportunities for personal developmentobject 6109.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:4. I was given adequate opportunities for promotion within %Institute]Q25LBL%object 6089.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:5. I felt the salary for the job was right for the responsibilities I hadobject 6159.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:6. The organisation recognised when staff did good workobject 6079.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:7. Management was generally supportive of meobject 6149.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:8. Management was generally supportive of my teamobject 6089.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:9. I was kept informed of the changes in the organisation which would affect meobject 6109.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:10. Staff morale was positive within the Instituteobject 6029.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:11. If I had a workplace issue it was dealt with quicklyobject 6019.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:12. If I had a workplace issue it was dealt with efficientlyobject 5979.1% 3 others27.3% Missing63.6% | InstituteViews. Topic:13. If I had a workplace issue it was dealt with discreetlyobject 6019.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:14. I was satisfied with the quality of the management and supervision within my work unitobject 6099.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:15. I worked well with my colleaguesobject 6059.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:16. My job was challenging and interestingobject 6079.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:17. I was encouraged to use my initiative in the course of my workobject 6109.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:18. I had sufficient contact with other people in my jobobject 6139.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:19. I was given adequate support and co-operation by my peers to enable me to do my jobobject 6099.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:20. I was able to use the full range of my skills in my jobobject 6099.1% 3 others27.3% Missing63.6% | 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]object 6089.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:22. I was able to use the full range of my knowledge in my jobobject 6089.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:23. My job provided sufficient varietyobject 6119.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:24. I was able to cope with the level of stress and pressure in my jobobject 6109.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:25. My job allowed me to balance the demands of work and family to my satisfactionobject 6119.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:26. My supervisor gave me adequate personal recognition and feedback on my performanceobject 6069.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:27. My working environment was satisfactory e.g. sufficient space, good lighting, suitable seating and working areaobject 6109.1% 3 others27.3% Missing63.6% | 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 dateobject 6099.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:29. There was adequate communication between staff in my unitobject 6039.1% 3 others27.3% Missing63.6% | WorkUnitViews. Topic:30. Staff morale was positive within my work unitobject 6069.1% 3 others27.3% Missing63.6% | Induction. Did you undertake Workplace Induction?object 6199.1% 3 others27.3% Missing63.6% | InductionInfo. Topic:Did you undertake a Corporate Induction?object 4329.1% 3 others27.3% Missing63.6% | InductionInfo. Topic:Did you undertake a Institute Induction?object 4839.1% 3 others27.3% Missing63.6% | InductionInfo. Topic: Did you undertake Team Induction?object 4409.1% 3 others27.3% Missing63.6% | InductionInfo. Face to Face Topic:Did you undertake a Corporate Induction; Category:How it was conducted?object 5559.1% 3 others27.3% Missing63.6% | InductionInfo. On-line Topic:Did you undertake a Corporate Induction; Category:How it was conducted?object 5559.1% 3 others27.3% Missing63.6% | InductionInfo. Induction Manual Topic:Did you undertake a Corporate Induction?object 5559.1% 3 others27.3% Missing63.6% | InductionInfo. Face to Face Topic:Did you undertake a Institute Induction?object 5309.1% 3 others27.3% Missing63.6% | InductionInfo. On-line Topic:Did you undertake a Institute Induction?object 5559.1% 3 others27.3% Missing63.6% | InductionInfo. Induction Manual Topic:Did you undertake a Institute Induction?object 5539.1% 3 others27.3% Missing63.6% | InductionInfo. Face to Face Topic: Did you undertake Team Induction; Category?object 5559.1% 3 others27.3% Missing63.6% | InductionInfo. On-line Topic: Did you undertake Team Induction?process you undertook and how it was conducted.]object 55518.2% 2 others18.2% Missing63.6% | InductionInfo. Induction Manual Topic: Did you undertake Team Induction?object 5559.1% 3 others27.3% Missing63.6% | Workplace. Topic:Did you and your Manager develop a Performance and Professional Development Plan (PPDP)?object 6089.1% 3 others27.3% Missing63.6% | Workplace. Topic:Does your workplace promote a work culture free from all forms of unlawful discrimination?object 5949.1% 3 others27.3% Missing63.6% | Workplace. Topic:Does your workplace promote and practice the principles of employment equity?object 5879.1% 3 others27.3% Missing63.6% | Workplace. Topic:Does your workplace value the diversity of its employees?object 5869.1% 3 others27.3% Missing63.6% | Workplace. Topic:Would you recommend the Institute as an employer to others?object 5819.1% 3 others27.3% Missing63.6% | Gender. What is your Gender?object 5969.1% 3 others27.3% Missing63.6% | CurrentAge. Current Ageobject 5969.1% 3 others27.3% Missing63.6% | Employment Type. Employment Typeobject 5969.1% 3 others27.3% Missing63.6% | Classification. Classificationobject 5969.1% 3 others27.3% Missing63.6% | LengthofServiceOverall. Overall Length of Service at Institute (in years)object 5969.1% 3 others27.3% Missing63.6% | LengthofServiceCurrent. Length of Service at current workplace (in years)object 5969.1% 3 others27.3% Missing63.6% | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
count | 702 | 702 | 702 | 695 | 701 | 437 | 437 | 437 | 437 | 437 | 437 | 437 | 437 | 437 | 437 | 437 | 437 | 113 | 608 | 613 | 610 | 608 | 615 | 607 | 614 | 608 | 610 | 602 | 601 | 597 | 601 | 609 | 605 | 607 | 610 | 613 | 609 | 609 | 608 | 608 | 611 | 610 | 611 | 606 | 610 | 609 | 603 | 606 | 619 | 432 | 483 | 440 | 555 | 555 | 555 | 530 | 555 | 553 | 555 | 555 | 555 | 608 | 594 | 587 | 586 | 581 | 596 | 596 | 596 | 596 | 596 | 596 | |
unique | nan | 12 | 2 | nan | 6 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 11 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 9 | 5 | 9 | 7 | 7 | |
top | nan | Brisbane North Institute of TAFE | Non-Delivery (corporate) | nan | Resignation | - | - | - | - | - | - | - | - | - | - | - | - | Dissatisfaction with %[Institute]Q25LBL% | Agree | Agree | Agree | Neutral | Agree | Agree | Agree | Agree | Agree | Neutral | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Yes | Yes | Yes | Yes | - | - | - | - | - | - | - | - | - | Yes | Yes | Yes | Yes | Yes | Female | 56 or older | Permanent Full-time | Administration (AO) | Less than 1 year | Less than 1 year | |
freq | nan | 161 | 432 | nan | 340 | 375 | 336 | 420 | 403 | 411 | 371 | 360 | 410 | 421 | 415 | 331 | 391 | 23 | 233 | 275 | 247 | 175 | 255 | 212 | 267 | 268 | 284 | 154 | 216 | 209 | 226 | 234 | 281 | 284 | 253 | 331 | 286 | 230 | 232 | 237 | 296 | 298 | 290 | 231 | 269 | 234 | 300 | 236 | 541 | 232 | 441 | 285 | 412 | 502 | 539 | 270 | 473 | 518 | 366 | 555 | 541 | 382 | 536 | 512 | 488 | 416 | 389 | 162 | 237 | 293 | 147 | 177 | |
mean | 634602619806772100 | nan | nan | 2011.4230215827338 | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | |
| Expand rows 5 - 5 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
min | 634133009996094000 | nan | nan | 2009 | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | |
25% | 634395439564804700 | nan | nan | 2011 | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | |
50% | 634583506367890400 | nan | nan | 2011 | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | |
75% | 634800514223046800 | nan | nan | 2012 | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | |
max | 635073030973791000 | nan | nan | 2013 | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | |
| 11 rows × 72 columns | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
# print the first 5 rows from this dataset
tafe_df.head()
| Record IDint64 | Instituteobject | WorkAreaobject | CESSATION YEARfloat64 | Reason for ceasing employmentobject | Contributing Factors. Career Move - Public Sector object | Contributing Factors. Career Move - Private Sector object | Contributing Factors. Career Move - Self-employmentobject | Contributing Factors. Ill Healthobject | Contributing Factors. Maternity/Familyobject | Contributing Factors. Dissatisfactionobject | Contributing Factors. Job Dissatisfactionobject | Contributing Factors. Interpersonal Conflictobject | Contributing Factors. Studyobject | Contributing Factors. Travelobject | Contributing Factors. Otherobject | Contributing Factors. NONEobject | Main Factor. Which of these was the main factor for leaving?object | InstituteViews. Topic:1. I feel the senior leadership had a clear vision and directionobject | InstituteViews. Topic:2. I was given access to skills training to help me do my job betterobject | InstituteViews. Topic:3. I was given adequate opportunities for personal developmentobject | InstituteViews. Topic:4. I was given adequate opportunities for promotion within %Institute]Q25LBL%object | InstituteViews. Topic:5. I felt the salary for the job was right for the responsibilities I hadobject | InstituteViews. Topic:6. The organisation recognised when staff did good workobject | InstituteViews. Topic:7. Management was generally supportive of meobject | InstituteViews. Topic:8. Management was generally supportive of my teamobject | InstituteViews. Topic:9. I was kept informed of the changes in the organisation which would affect meobject | InstituteViews. Topic:10. Staff morale was positive within the Instituteobject | InstituteViews. Topic:11. If I had a workplace issue it was dealt with quicklyobject | InstituteViews. Topic:12. If I had a workplace issue it was dealt with efficientlyobject | InstituteViews. Topic:13. If I had a workplace issue it was dealt with discreetlyobject | WorkUnitViews. Topic:14. I was satisfied with the quality of the management and supervision within my work unitobject | WorkUnitViews. Topic:15. I worked well with my colleaguesobject | WorkUnitViews. Topic:16. My job was challenging and interestingobject | WorkUnitViews. Topic:17. I was encouraged to use my initiative in the course of my workobject | WorkUnitViews. Topic:18. I had sufficient contact with other people in my jobobject | WorkUnitViews. Topic:19. I was given adequate support and co-operation by my peers to enable me to do my jobobject | WorkUnitViews. Topic:20. I was able to use the full range of my skills in my jobobject | 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]object | WorkUnitViews. Topic:22. I was able to use the full range of my knowledge in my jobobject | WorkUnitViews. Topic:23. My job provided sufficient varietyobject | WorkUnitViews. Topic:24. I was able to cope with the level of stress and pressure in my jobobject | WorkUnitViews. Topic:25. My job allowed me to balance the demands of work and family to my satisfactionobject | WorkUnitViews. Topic:26. My supervisor gave me adequate personal recognition and feedback on my performanceobject | WorkUnitViews. Topic:27. My working environment was satisfactory e.g. sufficient space, good lighting, suitable seating and working areaobject | 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 dateobject | WorkUnitViews. Topic:29. There was adequate communication between staff in my unitobject | WorkUnitViews. Topic:30. Staff morale was positive within my work unitobject | Induction. Did you undertake Workplace Induction?object | InductionInfo. Topic:Did you undertake a Corporate Induction?object | InductionInfo. Topic:Did you undertake a Institute Induction?object | InductionInfo. Topic: Did you undertake Team Induction?object | InductionInfo. Face to Face Topic:Did you undertake a Corporate Induction; Category:How it was conducted?object | InductionInfo. On-line Topic:Did you undertake a Corporate Induction; Category:How it was conducted?object | InductionInfo. Induction Manual Topic:Did you undertake a Corporate Induction?object | InductionInfo. Face to Face Topic:Did you undertake a Institute Induction?object | InductionInfo. On-line Topic:Did you undertake a Institute Induction?object | InductionInfo. Induction Manual Topic:Did you undertake a Institute Induction?object | InductionInfo. Face to Face Topic: Did you undertake Team Induction; Category?object | InductionInfo. On-line Topic: Did you undertake Team Induction?process you undertook and how it was conducted.]object | InductionInfo. Induction Manual Topic: Did you undertake Team Induction?object | Workplace. Topic:Did you and your Manager develop a Performance and Professional Development Plan (PPDP)?object | Workplace. Topic:Does your workplace promote a work culture free from all forms of unlawful discrimination?object | Workplace. Topic:Does your workplace promote and practice the principles of employment equity?object | Workplace. Topic:Does your workplace value the diversity of its employees?object | Workplace. Topic:Would you recommend the Institute as an employer to others?object | Gender. What is your Gender?object | CurrentAge. Current Ageobject | Employment Type. Employment Typeobject | Classification. Classificationobject | LengthofServiceOverall. Overall Length of Service at Institute (in years)object | LengthofServiceCurrent. Length of Service at current workplace (in years)object | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 634133009996094000 | Southern Queensland Institute of TAFE | Non-Delivery (corporate) | 2010 | Contract Expired | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | Agree | Agree | Agree | Neutral | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Strongly Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Neutral | Agree | Agree | Yes | Yes | Yes | Yes | Face to Face | - | - | Face to Face | - | - | Face to Face | - | - | Yes | Yes | Yes | Yes | Yes | Female | 26 – 30 | Temporary Full-time | Administration (AO) | 1-2 | 1-2 | |
1 | 634133654064531000 | Mount Isa Institute of TAFE | Non-Delivery (corporate) | 2010 | Retirement | - | - | - | - | - | - | - | - | - | Travel | - | - | nan | Agree | Agree | Agree | Agree | Agree | Strongly Agree | Strongly Agree | Agree | Strongly Agree | Agree | Agree | Agree | Disagree | Strongly Agree | Strongly Agree | Strongly Agree | Agree | Agree | Agree | Strongly Agree | Agree | Agree | Agree | Strongly Agree | Agree | Strongly Agree | Strongly Agree | Agree | Agree | Strongly Agree | No | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | Yes | Yes | Yes | Yes | Yes | nan | nan | nan | nan | nan | nan | |
2 | 634138845606563000 | Mount Isa Institute of TAFE | Delivery (teaching) | 2010 | Retirement | - | - | - | - | - | - | - | - | - | - | - | NONE | nan | Agree | Agree | Agree | Agree | Agree | Agree | Strongly Agree | Agree | Agree | Agree | Agree | Neutral | Neutral | Strongly Agree | Strongly Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | No | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | nan | Yes | Yes | Yes | Yes | Yes | nan | nan | nan | nan | nan | nan | |
3 | 634139903350000000 | Mount Isa Institute of TAFE | Non-Delivery (corporate) | 2010 | Resignation | - | - | - | - | - | - | - | - | - | Travel | - | - | nan | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Yes | No | Yes | Yes | - | - | - | nan | - | - | - | - | - | Yes | Yes | Yes | Yes | Yes | nan | nan | nan | nan | nan | nan | |
4 | 634146578511788000 | Southern Queensland Institute of TAFE | Delivery (teaching) | 2010 | Resignation | - | Career Move - Private Sector | - | - | - | - | - | - | - | - | - | - | nan | Agree | Agree | Strongly Agree | Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Agree | Strongly Agree | Strongly Agree | Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Strongly Agree | Yes | Yes | Yes | Yes | - | - | Induction Manual | Face to Face | - | - | Face to Face | - | - | Yes | Yes | Yes | Yes | Yes | Male | 41 – 45 | Permanent Full-time | Teacher (including LVT) | 3-4 | 3-4 | |
| 5 rows × 72 columns | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
# read in the dete dataset, converting the "Not Started" values to null
dete_df = pd.read_csv("dete_survey.csv", na_values="Not Stated")
dete_df = dete_df.drop(dete_df.columns[28:49], axis = 1)
tafe_df = tafe_df.drop(tafe_df.columns[17:66], axis = 1)
# Update column names for DETE
dete_df.columns = dete_df.columns.str.lower().str.strip().str.replace(" ", "_")
dete_df.columns
# update column names for TAFE to match those of DETE
tafe_df.columns = tafe_df.columns.str.replace('\s+', ' ', regex=True)
tafe_df = tafe_df.rename({
'Record ID': 'id',
'CESSATION YEAR': 'cease_date',
'Reason for ceasing employment': 'separationtype',
'Gender. What is your Gender?': 'gender',
'CurrentAge. Current Age': 'age',
'Employment Type. Employment Type': 'employment_status',
'Classification. Classification': 'position',
'LengthofServiceOverall. Overall Length of Service at Institute (in years)': 'institute_service',
'LengthofServiceCurrent. Length of Service at current workplace (in years)': 'role_service'
}, axis=1)
tafe_df.columns
# list the unique values in DETE separationtype column
dete_df["separationtype"].value_counts()
# Retain rows with resignation listed as the separation type
dete_df = dete_df[dete_df["separationtype"].str.contains(r"Resignation.+")]
# list the unique values in TAFE separationtype column
tafe_df["separationtype"].value_counts()
# Retain rows with resignation listed as the separation type
tafe_df = tafe_df[tafe_df["separationtype"].str.contains(r"Resignation", na=False)]
# regex pattern to extract years from the DETE dataset
pattern = r"([0-9]{4})"
# first, extract the year values from these columns
dete_df["cease_date"] = dete_df["cease_date"].str.extract(pattern)
dete_df["dete_start_date"] = dete_df["dete_start_date"].astype(str).str.extract(pattern)
dete_df["role_start_date"] = dete_df["role_start_date"].astype(str).str.extract(pattern)
# convert these values to date types
dete_df["cease_date"] = pd.to_datetime(dete_df["cease_date"])
dete_df["dete_start_date"] = pd.to_datetime(dete_df["dete_start_date"])
dete_df["role_start_date"] = pd.to_datetime(dete_df["role_start_date"])
# show the info about these columns
dete_df[["cease_date", "dete_start_date", "role_start_date"]].info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 311 entries, 3 to 821
Data columns (total 3 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 cease_date 300 non-null datetime64[ns]
1 dete_start_date 283 non-null datetime64[ns]
2 role_start_date 270 non-null datetime64[ns]
dtypes: datetime64[ns](3)
memory usage: 9.7 KB
# regex pattern to extract years from the TAFE dataset
pattern = r"([0-9]{4})"
# extract the year values
tafe_df["cease_date"] = tafe_df["cease_date"].astype(str).str.extract(pattern)
# convert these values to dates
tafe_df["cease_date"] = pd.to_datetime(tafe_df["cease_date"])
# show the info about these new column
tafe_df[["cease_date"]].info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 340 entries, 3 to 701
Data columns (total 1 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 cease_date 335 non-null datetime64[ns]
dtypes: datetime64[ns](1)
memory usage: 5.3 KB
# function for converting values to True, False or NaN
def update_values(data):
if pd.isnull(data):
return np.nan
elif data == "-": # recall that we're interpreting this as false
return False
return True
# update the following columns to either be True, False or NaN
tafe_df[['Contributing Factors. Dissatisfaction', 'Contributing Factors. Job Dissatisfaction']] = tafe_df[['Contributing Factors. Dissatisfaction', 'Contributing Factors. Job Dissatisfaction']].applymap(update_values)
# add dissatsified column for DETE
dete_df["dissatisfied"] = dete_df[["job_dissatisfaction", "dissatisfaction_with_the_department", "employment_conditions", "work_life_balance", "workload"]].any(axis=1)
# add dissatsified column for TAFE
tafe_df["dissatisfied"] = tafe_df[['Contributing Factors. Dissatisfaction', 'Contributing Factors. Job Dissatisfaction']].any(axis=1)
# create a new institute_service column
dete_df["institute_service"] = pd.DatetimeIndex(dete_df["cease_date"]).year - pd.DatetimeIndex(dete_df["dete_start_date"]).year
# create identifier columns for the datasets
dete_df["institute"] = "DETE"
tafe_df["institute"] = "TAFE"
combined_df = pd.concat([dete_df, tafe_df])
combined_df.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 651 entries, 3 to 701
Data columns (total 53 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 id 651 non-null int64
1 separationtype 651 non-null object
2 cease_date 635 non-null datetime64[ns]
3 dete_start_date 283 non-null datetime64[ns]
4 role_start_date 270 non-null datetime64[ns]
5 position 598 non-null object
6 classification 161 non-null object
7 region 265 non-null object
8 business_unit 32 non-null object
9 employment_status 597 non-null object
10 career_move_to_public_sector 311 non-null object
11 career_move_to_private_sector 311 non-null object
12 interpersonal_conflicts 311 non-null object
13 job_dissatisfaction 311 non-null object
14 dissatisfaction_with_the_department 311 non-null object
15 physical_work_environment 311 non-null object
16 lack_of_recognition 311 non-null object
17 lack_of_job_security 311 non-null object
18 work_location 311 non-null object
19 employment_conditions 311 non-null object
20 maternity/family 311 non-null object
21 relocation 311 non-null object
22 study/travel 311 non-null object
23 ill_health 311 non-null object
24 traumatic_incident 311 non-null object
25 work_life_balance 311 non-null object
26 workload 311 non-null object
27 none_of_the_above 311 non-null object
28 gender 592 non-null object
29 age 596 non-null object
30 aboriginal 7 non-null object
31 torres_strait 0 non-null object
32 south_sea 3 non-null object
33 disability 8 non-null object
34 nesb 9 non-null object
35 dissatisfied 651 non-null bool
36 institute_service 563 non-null object
37 institute 651 non-null object
38 Institute 340 non-null object
39 WorkArea 340 non-null object
40 Contributing Factors. Career Move - Public Sector 332 non-null object
41 Contributing Factors. Career Move - Private Sector 332 non-null object
42 Contributing Factors. Career Move - Self-employment 332 non-null object
43 Contributing Factors. Ill Health 332 non-null object
44 Contributing Factors. Maternity/Family 332 non-null object
45 Contributing Factors. Dissatisfaction 332 non-null object
46 Contributing Factors. Job Dissatisfaction 332 non-null object
47 Contributing Factors. Interpersonal Conflict 332 non-null object
48 Contributing Factors. Study 332 non-null object
49 Contributing Factors. Travel 332 non-null object
50 Contributing Factors. Other 332 non-null object
51 Contributing Factors. NONE 332 non-null object
52 role_service 290 non-null object
dtypes: bool(1), datetime64[ns](3), int64(1), object(48)
memory usage: 270.2+ KB
combined_df = combined_df.dropna(thresh=500, axis=1)
combined_df
| idint644 – 635073030973791000 | separationtypeobject Resignation52.2% Resignation-Other reasons23% 2 others24.7% | cease_datedatetime64[ns]2006-01-01T00:00:00.000000 – 2014-01-01T00:00:00.000000 | positionobject Administration (AO)22.7% 20 others69.1% Missing8.1% | employment_statusobject Permanent Full-time39.3% 5 others52.4% Missing8.3% | genderobject Female65.1% Male25.8% Missing9.1% | ageobject 51-5510.9% 16 others80.6% Missing8.4% | dissatisfiedbool False66.8% True33.2% | institute_serviceobject Less than 1 year11.2% 48 others75.3% Missing13.5% | instituteobject TAFE52.2% DETE47.8% | ||
|---|---|---|---|---|---|---|---|---|---|---|---|
3 | 4 | Resignation-Other reasons | 2012-01-01T00:00:00.000000 | Teacher | Permanent Full-time | Female | 36-40 | false | 7 | DETE | |
5 | 6 | Resignation-Other reasons | 2012-01-01T00:00:00.000000 | Guidance Officer | Permanent Full-time | Female | 41-45 | true | 18 | DETE | |
8 | 9 | Resignation-Other reasons | 2012-01-01T00:00:00.000000 | Teacher | Permanent Full-time | Female | 31-35 | false | 3 | DETE | |
9 | 10 | Resignation-Other employer | 2012-01-01T00:00:00.000000 | Teacher Aide | Permanent Part-time | Female | 46-50 | true | 15 | DETE | |
11 | 12 | Resignation-Move overseas/interstate | 2012-01-01T00:00:00.000000 | Teacher | Permanent Full-time | Male | 31-35 | false | 3 | DETE | |
| Expand rows 5 - 645 | |||||||||||
696 | 635065973820625000 | Resignation | 2013-01-01T00:00:00.000000 | Operational (OO) | Temporary Full-time | Male | 21 – 25 | false | 5-6 | TAFE | |
697 | 635066785175197000 | Resignation | 2013-01-01T00:00:00.000000 | Teacher (including LVT) | Temporary Full-time | Male | 51-55 | false | 1-2 | TAFE | |
698 | 635067716405666000 | Resignation | 2013-01-01T00:00:00.000000 | nan | nan | nan | nan | false | nan | TAFE | |
699 | 635070442972541000 | Resignation | 2013-01-01T00:00:00.000000 | Teacher (including LVT) | Permanent Full-time | Female | 51-55 | false | 5-6 | TAFE | |
701 | 635073030973791000 | Resignation | 2013-01-01T00:00:00.000000 | Administration (AO) | Contract/casual | Female | 26 – 30 | false | 3-4 | TAFE | |
| 651 rows × 10 columns | |||||||||||
# show what the different year values look like
combined_df["institute_service"].value_counts(dropna=False).head(10)
# match numbers after the "-", of before the ".". This will grab "5" of "5.0", but "6" of "5-6"
combined_df["institute_service"] = combined_df["institute_service"].astype(str).str.extract(r"(?<!\.)\b(\d+)\b(?!-)").astype(float)
# function that maps the number of years to a service category
def map_institute_service(data):
if pd.isnull(data):
return np.nan
elif data < 3:
return "New"
elif data < 7:
return "Experienced"
elif data < 11:
return "Established"
return "Veteran"
# apply the mapped function to the institute_service column, putting the results in a new column service_category
combined_df["service_category"] = combined_df["institute_service"].apply(map_institute_service)
# create a bar chart showing proportion of dissatisfied employees in each service category
ax = combined_df.pivot_table(index="service_category", values="dissatisfied").loc[["New", "Experienced", "Established", "Veteran"]].plot(kind="bar", figsize=(12,6), rot=0)
# create x label, y label and title
ax.set_xlabel("Service Category")
ax.set_ylabel("Proportion of Employees")
plt.title("Proportion of Dissatisfied Employees in Each Service Category")
# show plot
plt.show()
# create a bar chart showing proportion of dissatisfied employees by employment status
ax = combined_df.pivot_table(index="employment_status", values="dissatisfied").loc[["Casual", "Contract/casual", "Temporary Part-time", "Permanent Part-time", "Temporary Full-time", "Permanent Full-time"]].plot(kind="bar", figsize=(12,6))
# create x label, y label and title
ax.set_xlabel("Employment Status")
ax.set_ylabel("Proportion of Employees")
plt.title("Proportion of Dissatisfied Employees for Each Employment Status")
# show plot
plt.show()
# create a bar chart showing proportion of dissatisfied employees by employment status
ax = combined_df.pivot_table(index="gender", values="dissatisfied").loc[["Male", "Female"]].plot(kind="bar", figsize=(12,6))
# create x label, y label and title
ax.set_xlabel("Gender")
ax.set_ylabel("Proportion of Employees")
plt.title("Proportion of Dissatisfied Employees for Each Gender")
# show plot
plt.show()