<jupyter_start><jupyter_text>Clean and Analyze Employee Exit Surveys In this project we will be working with two datasets: exit surveys from employees of the Department of Education, Training, and Employment (DETE) and the Technical and Further Education (TAFE) institute in Queensland, Australia. The first one could be downloaded from [here](https://data.gov.au/dataset/ds-qld-fe96ff30-d157-4a81-851d-215f2a0fe26d/details?q=exit%20survey), second - from [here](https://data.gov.au/dataset/ds-qld-89970a3b-182b-41ea-aea2-6f9f17b5907e/details?q=exit%20survey).So we will work with surveys concerning employees resigning.First of all, Let's set **the goals** of the project:* Are employees who only worked for the institutes for a short period 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?In the beginning, we should prepare and clean datasets for analysis, combine the results for both surveys to answer these questions. However, although both used the same survey template, one of them customized some of the answers. A data dictionary wasn't provided with the dataset, so we will use general knowledge to define columns. Below some columns, that we will be using during this project: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 DETEBelow is a preview of a couple of 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)___Let's start and explore the structure of the datasets.  Exploring the structure<jupyter_code># importing useful libraries
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
% matplotlib inline
# reading datasets
tafe_survey = pd.read_csv('tafe_survey.csv')
dete_survey = pd.read_csv('dete_survey.csv')
tafe_survey.info()
tafe_survey.head()<jupyter_output><empty_output><jupyter_text>So, we can see that there are 702 rows and we have a lot of columns relating to the survey questions (each column represents a different question for the survey). Also from `.info()` notice, that we have values with a different number of the non-null data. In general, we have only 3 out of 72 columns with absolute non-null data. Below I've proved it.<jupyter_code># looking at the not-null columns
tafe_survey.notnull().sum()[tafe_survey.notnull().sum() == 702]
tafe_survey.isnull().sum()[tafe_survey.isnull().sum() == tafe_survey.isnull().sum().max()]<jupyter_output><empty_output><jupyter_text>We have even 589 null values for the row out of 702. It's more than 1/3 of all the rows<jupyter_code>dete_survey.info()<jupyter_output><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 environ[...]<jupyter_text>There is a better situation with not-null data, but we have a few rows with a rather small number of the not-null values like 23, 16 or even 7 and 3. These columns are in line to be dropped.Also, as you can notice, 18 columns are bool type in the middle of the dataset and other columns relating to the survey questions are objects.Comparing to the TAFE dataset we see that structure of data very similar (columns are survey questions), but there is another template used for that<jupyter_code>dete_survey.head()<jupyter_output><empty_output><jupyter_text>We see that few rows contain `Not Stated` values that obviously indicates values are missing, but they aren't represented as `NaN`.We can check the quantity of this kind of values by using a simple function and `.applymap()` method<jupyter_code># function to check number of the 'Not Stated' values
def func(x):
    if x == 'Not Stated':
        return 1

df = dete_survey.applymap(func)
df.sum()<jupyter_output><empty_output><jupyter_text>Preparing and Combining Datasets  Cleaning, renaming, dropping unnecessary columns `Region` contains most of such values. Let's read our data set indicating that these values must be treated as NaNAlso `tafe_survey` sometimes contains 'NONE' word instead of `NaN`, we will change it too.<jupyter_code>dete_survey = pd.read_csv('dete_survey.csv', na_values = 'Not Stated')
tafe_survey = pd.read_csv('tafe_survey.csv', na_values = 'NONE')<jupyter_output><empty_output><jupyter_text>___There are some main conclusions from brief exploring datasets:* Both the `dete_survey` and `tafe_survey` data frames contain many columns that we don't need to complete our analysis.* Each data frame contains many of the same columns, but the column names are different.* some multiple columns/answers indicate an employee resigned because they were dissatisfied.Let's drop some columns from each data frame that we won't use in our analysis (columns and more generally survey questions that aren't concerning issues about conditions of a job)<jupyter_code>dete_survey_updated = dete_survey.drop(dete_survey.columns[28:48], axis = 1)
tafe_survey_updated = tafe_survey.drop(tafe_survey.columns[17:66], axis = 1)<jupyter_output><empty_output><jupyter_text>Next, let's turn our attention to the column names. Each data frame contains many of the same columns, but the column names are different. For combining porpuses we should standardize the column names in both datasets<jupyter_code>dete_survey_updated.columns = (dete_survey_updated.columns.str.lower() # making capitalization lowercase
                               .str.strip() # removing any whitespace from the end of the strings
                               .str.replace(' ','_')) # making code more Python like, replace spaces with underscores
dete_survey_updated.columns<jupyter_output><empty_output><jupyter_text>*Renaming columns in `tafe_survey_updated`:*<jupyter_code>tafe_survey_updated = tafe_survey_updated.rename({'Record ID': 'id',
'CESSATION YEAR': 'cease_date',
'Reason for ceasing employment': 'separationtype',
'Gender. What is your Gender?': 'gender',
'CurrentAge. Current Age': 'age',
'Employment Type. Employment Type': 'employment_status',
'Classification. Classification': 'position',
'LengthofServiceOverall. Overall Length of Service at Institute (in years)': 'institute_service',
'LengthofServiceCurrent. Length of Service at current workplace (in years)': 'role_service'}, axis = 1)
tafe_survey_updated.head() # the rest of the column names we will handle later<jupyter_output><empty_output><jupyter_text>___Recall our goal of this analysis is to look at employees who resigned due to some kind of dissatisfaction. So, if we look at the unique values in the `separationtype` columns in each data frame, we'll see that each contains a couple of different separation types.<jupyter_code>tafe_survey_updated['separationtype'].unique()
dete_survey_updated['separationtype'].unique()<jupyter_output><empty_output><jupyter_text>We will keep all values relating to any kind of resignation. And we will keep the `Termination` and `Transfer` because these kinds of separation type can be interesting to include in our analysis as soon as we don't know why termination or transfer happened, maybe it was because of work conditions.<jupyter_code>dete_resignations = (dete_survey_updated[dete_survey_updated['separationtype'].isin(['Resignation-Other reasons', 'Resignation-Other employer',
                      'Resignation-Move overseas/interstate', 'Termination'])])
dete_resignations['separationtype'].unique()
tafe_resignations = (tafe_survey_updated[tafe_survey_updated['separationtype'].isin(['Resignation', 'Transfer',
                     'Termination'])])
tafe_resignations['separationtype'].unique()<jupyter_output><empty_output><jupyter_text>Finding inconsistencies Now, before we start cleaning and manipulating the rest of our data, let's verify that the data doesn't contain any major inconsistencies. Since the `cease_date` is the last year of the person's employment and the `dete_start_date` is the person's first year of employment, it wouldn't make sense to have years after the current date.Given that most people in this field start working in their 20s, it's also unlikely that the dete_start_date was before the year 1940. We should look if there are many years higher than the current date or lower than 1940.<jupyter_code>dete_resignations['cease_date'].value_counts(dropna=False).sort_index()
# setting the pattern and extracting the years, converting to float type
dete_resignations['cease_date'] = dete_resignations['cease_date'].copy().str.extract(r'([1-2][0-9]{3})', expand=False).astype('float')
dete_resignations['cease_date'].value_counts(dropna = False).sort_index()<jupyter_output>/dataquest/system/env/python3/lib/python3.4/site-packages/ipykernel/__main__.py:2: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy<jupyter_text>Searching any outliers in the other columns consist of the years:<jupyter_code>tafe_resignations['cease_date'].value_counts(dropna = False).sort_index()
dete_resignations['dete_start_date'].value_counts(dropna = False).sort_index()<jupyter_output><empty_output><jupyter_text>Everything looks fine and there are no serious problems with dates for further analysis, but we should remove any NaN values, as soon as we don't know dates and can't calculate the length of service. Also, we need to look closer at the consistency of the dates.<jupyter_code># dropping na through the needed columns
tafe_resignations = tafe_resignations.dropna(subset = ['cease_date'])
dete_resignations = dete_resignations.dropna(subset = ['cease_date','dete_start_date'])
dete_resignations['cease_date'].value_counts(dropna=False)

# exploring distribution of the dates using boxplot
plt.figure(figsize=(8, 6))
plt.figsize = (12,12)
ax1 = plt.subplot(1, 2, 1)
ax2 = plt.subplot(1, 2, 2)
x = [dete_resignations['dete_start_date'].values, dete_resignations['cease_date'].values, 
    tafe_resignations['cease_date'].values]
_ = ax1.boxplot(x[1], whis = 'range')
ax1.set_ylim(2004,2015)
ax1.set_xlabel('DETE cease date')
ax1.set_ylabel('Years')
ax1.set_xticks([])
_ = ax2.boxplot(x[2], whis = 'range')
ax2.set_ylim(2004,2015)
ax2.set_xlabel(' TAFE cease date')
ax2.set_xticks([])
ax2.set_yticks([])
plt.suptitle('Dates Distribution', weight = 'bold', fontsize = 14)
plt.show()<jupyter_output><empty_output><jupyter_text>We see that our data sets aren't in the same time range, I suppose, we should achieve the same time interval for truthful analysis. Let's look at unique() values and pick the range.<jupyter_code>print(tafe_resignations['cease_date'].unique())
dete_resignations['cease_date'].unique()<jupyter_output>[2010. 2009. 2011. 2012. 2013.]<jupyter_text>So, obviously, we need to exclude 2006 year, as we see above, only one row includes 2006 year of the cease. We don't lose essential data for analysis if we drop this row but build more consistent and related data frames.<jupyter_code>test = dete_resignations.loc[dete_resignations['cease_date'] > 2008, 'cease_date'] 
# look at the changes
plt.figure(figsize=(8, 6))
plt.figsize = (12,12)
ax1 = plt.subplot(1, 2, 1)
ax2 = plt.subplot(1, 2, 2)
x = [dete_resignations['dete_start_date'].values, test.values, 
    tafe_resignations['cease_date'].values]
_ = ax1.boxplot(x[1], whis = 'range')
ax1.set_ylim(2005,2015)
ax1.set_xlabel('DETE cease date')
ax1.set_ylabel('Years')
ax1.set_xticks([])
_ = ax2.boxplot(x[2], whis = 'range')
ax2.set_ylim(2005,2015)
ax2.set_xlabel(' TAFE cease date')
ax2.set_xticks([])
ax2.set_yticks([])
plt.suptitle('Dates Distribution', weight = 'bold', fontsize = 14)
plt.grid(False)
plt.show()<jupyter_output><empty_output><jupyter_text>Now it looks better and our datasets are in the same time interval. Next, apply this to the dataset.<jupyter_code>dete_resignations = dete_resignations[dete_resignations['cease_date'] > 2008]
dete_resignations['cease_date'].unique()<jupyter_output><empty_output><jupyter_text>The years of service The length of time an employee spent in a workplace is better to represent as their years of service. You may have noticed that the `tafe_resignations` data frame already contains a "service" column, which we renamed to `institute_service`. To analyze both surveys together, we'll have to create a corresponding `institute_service`column in `dete_resignations`.<jupyter_code>dete_resignations['institute_service'] = dete_resignations['cease_date'] - dete_resignations['dete_start_date']
dete_resignations['institute_service'].value_counts().sort_index()<jupyter_output><empty_output><jupyter_text>Okay, on the rapid sight, everything looks fine. By the way, we could set and answer one more question:* In which range of the years of service falls most of resignings?<jupyter_code># creating histogram to answer this question
fig, ax = plt.subplots()
plt.hist(dete_resignations['institute_service'], bins = 30)
plt.grid(False)
plt.box(False)
plt.xlabel('years of service')
plt.ylabel('frequency')
plt.show()<jupyter_output><empty_output><jupyter_text>___From this histogram we can notice that the years of service of the most people who resigned falls on the [0 - 10] range and rapidly decreasing over the rest of the years. It looks like exponential distribution in a rough approximation.  Separating dessatisfied employees Next, we'll identify any employees who resigned because they were dissatisfied. We will use some columns to identify employees as 'dissatisfied'.Then we will create the new column indicated employees as dissatisfied.<jupyter_code># tafe_resignations:
print(tafe_resignations['Contributing Factors. Dissatisfaction'].value_counts(dropna = False))
tafe_resignations['Contributing Factors. Job Dissatisfaction'].value_counts(dropna = False)<jupyter_output>-                                         297
Contributing Factors. Dissatisfaction      56
NaN                                        30
Name: Contributing Factors. Dissatisfaction, dtype: int64<jupyter_text>We will use the next approach:1) Convert the values in the columns indicated dissastifacation to `True`, `False`, or `NaN`2) If any of the columns contain a `True` value, we'll add a `True` value to a new column named dissatisfied, return `False` if none of the elements in the selected columns is `True`, return `NaN` if the value is `NaN`<jupyter_code># function to convert values either True or False, or NaN
def update_vals(val):
    if pd.isnull(val):
        return np.nan
    elif val == '-':
        return False
    else:
        return True
# applying function on each value of the selected columns     
tafe_resignations[['Contributing Factors. Dissatisfaction', 
                   'Contributing Factors. Job Dissatisfaction']] = tafe_resignations[['Contributing Factors. Dissatisfaction', 
                                                                                       'Contributing Factors. Job Dissatisfaction']].applymap(update_vals)
tafe_resignations[['Contributing Factors. Dissatisfaction', 
                   'Contributing Factors. Job Dissatisfaction']].head(10)<jupyter_output><empty_output><jupyter_text>___The following columns could be used to identify job dissatisfaction from `dete_resignations` dataframe:<jupyter_code>diss_cols = ['job_dissatisfaction',
'dissatisfaction_with_the_department',
'physical_work_environment',
'lack_of_recognition',
'lack_of_job_security',
'work_location',
'employment_conditions',
'work_life_balance',
'workload']

dete_resignations[diss_cols].head()<jupyter_output><empty_output><jupyter_text>These values are already bool type, so we don't need to convert them.Below is creating new columns indicated dissatisfaction, using the `.any()` method. If any `True`, return True, else: `False`. If all of the values np.nan - return `NaN`<jupyter_code>dete_resignations['dissatisfied'] = dete_resignations[diss_cols].any(axis = 1, skipna = False)
tafe_resignations['dissatisfied'] = tafe_resignations[['Contributing Factors. Dissatisfaction', 
                   'Contributing Factors. Job Dissatisfaction']].any(axis = 1, skipna = False)
# setting new variabls for data frames for convinience, printing shapes of them
dete_resignations_up = dete_resignations.copy()
tafe_resignations_up = tafe_resignations.copy()
shape_dete = dete_resignations_up.shape
shape_tafe = tafe_resignations_up.shape
print('dete_resignations_up: cols:{}\nrows={}\ntafe_resignations_up: cols = {}\nrows={}'.format(shape_dete[1],shape_dete[0],shape_tafe[1],shape_tafe[0]) )<jupyter_output>dete_resignations_up: cols:38
rows=286
tafe_resignations_up: cols = 24
rows=383<jupyter_text>Combining data frames Now, it's time to combine our dataframes! Our end goal is to aggregate the data according to the `institute_service` column<jupyter_code># any(dete_resignations_up['id'].values == tafe_resignations_up['id'].sample(287).values)
pd.merge(left = dete_resignations_up, right = tafe_resignations_up, how = 'inner', on = 'id')<jupyter_output><empty_output><jupyter_text>I've checked whether there are the same ID or notAll ID of the both dataframes are different, so let's unite dataframes using `.concat` method to get combined dataframe not separating the same named columns of the different dataframes (this would have happened like this if we used `pd.merge` with 'outher' parametr) First, let's add a column to each dataframe that will allow us to easily distinguish between the two.<jupyter_code>dete_resignations_up['institute'] = 'DETE'
tafe_resignations_up['institute'] = 'TAFE'

# combining data with .concat() method
combined = pd.concat([dete_resignations_up, tafe_resignations_up], ignore_index = True, axis = 0)
print('combined columns:{}\nnum_rows = {}'.format('gov',combined.shape[0]))
combined.head()
combined.isnull().sum()<jupyter_output><empty_output><jupyter_text>We will drop any columns with less than 500 non null values. We don't need them anymore for further analysis.<jupyter_code>combined_updated = combined.dropna(thresh = 500, axis = 1)
combined_updated.head()<jupyter_output><empty_output><jupyter_text>Treating the institute_service column Finally, we've got dataframe 670x10 with usefull columns for answering our main questions of this project.First, though, we'll have to clean up the `institute_service`column. As we see below it currently contains values in a couple different forms:<jupyter_code>combined_updated['institute_service'].value_counts()<jupyter_output><empty_output><jupyter_text>To analyze the data, we'll convert these numbers into categories.  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<jupyter_code># test - it's auxiliary dataframe to manipulate 
test = combined_updated.copy()
# replace less than 1 year with 0 - 1 (this is needed for function 'map_cat' work well)
test['institute_service'] = test['institute_service'].astype('str').str.replace('Less than 1 year', '0 - 1')
# extract all the years and convert it to float
test['institute_service'] = test['institute_service'].str.extract(r'(\d+)', expand = False).astype('float')
test['institute_service'].value_counts(dropna = False)
# function to convert values to the categories depending on their meaning
def map_cat(val):
    if pd.isnull(val):
        return np.nan
    elif val < 3:
        return 'New'
    elif 3 <= val <= 6:
        return 'Experienced'
    elif 7 <= val <= 10:
        return 'Established'
    else:
        return 'Veteran'
categ = test.loc[:,'institute_service'].copy()
categ = categ.apply(map_cat) # applying function on each element
combined_updated['service_cat'] = categ # creating new column in combined_updated
                                        # dataframe
combined_updated['service_cat'].value_counts()<jupyter_output>/dataquest/system/env/python3/lib/python3.4/site-packages/ipykernel/__main__.py:3: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy<jupyter_text>We've got a column with categorical values, this is what we wanted to get.Now, let's finally do our first piece of analysis!   Analysis of the data  Years of the service vs dissatisfaction First, drop the NaN values, because we don't know how to replace them, any unreasonable changes will affect the truth of the analysis, because of that we drop them<jupyter_code>combined_updated['dissatisfied'].value_counts(dropna = False)
combined_updated = combined_updated.dropna(subset=['dissatisfied'])
# using 'np.mean' lead to exeption, so I've done it manually.
sum_cut = pd.pivot_table(data = combined_updated, index ='service_cat', values='dissatisfied',aggfunc = np.sum)
count_cut = pd.pivot_table(data = combined_updated, index ='service_cat', values='dissatisfied',aggfunc = 'count')
percentage_cut = round((sum_cut / count_cut)*100, 1)
percentage_cut<jupyter_output><empty_output><jupyter_text>For the porpuses of the analysis, we will use visualization to easily understand and answer the question.<jupyter_code>sns.set_style('white')
fig,ax = plt.subplots(figsize = (8,6))
sns.barplot(x = percentage_cut.index, y = 'dissatisfied', data = percentage_cut, color = 'grey', alpha = 0.7)
plt.bar(-0.4, percentage_cut['dissatisfied'][0], color = 'darkred', alpha = 0.8)
plt.bar(2.6, percentage_cut['dissatisfied'][3], color = 'darkred', alpha = 0.8)
ax.set_xlabel('')
ax.set_ylabel('dissatisfied %')
ax.set_title('Resigning caused by dissatisfaction', weight = 'bold')
sns.despine(bottom = True, left = True)
plt.show()<jupyter_output>/dataquest/system/env/python3/lib/python3.4/site-packages/seaborn/categorical.py:1428: FutureWarning:

remove_na is deprecated and is a private function. Do not use.<jupyter_text>Previously, we saw that most people who resigned are new employees, but looking at the percentage of people who resigned because of some dissatisfaction we see that new employees have the smallest percentage. Half of the people in the Veteran and Established service category resigned due to some kind of dissatisfaction. I assume it's because of the inevitables changes that come from time to time, maybe they got tired of the current situation in workplaces and decided to resign.Nevertheless, we explored and build visualization describing the situation with the satisfaction of a job in each service category.  Age influence on the dissatisfaction Now, let's move on and answer the next question about how age impact on the satisfaction of a job.<jupyter_code>combined_updated['age'].value_counts(dropna = False)<jupyter_output><empty_output><jupyter_text>We will split up the `age` column just like we did with service category. We distinguish following categories:* 20 or younger* 20 - 25* 25 - 30* 30 - 40* 40 - 50* older than 50<jupyter_code># extract dates using regex
df = combined_updated.copy()
df['age'] = df['age'].str.extract(r'(\d+)', expand = False).astype('float')
# function to set categories
def map_cat2(val):
    if pd.isnull(val):
        return np.nan
    elif val <= 20:
        return '20 or younger'
    elif 20 < val <= 25:
        return '20 - 25'
    elif 25 < val <= 30:
        return '25 - 30'
    elif 30 < val <= 40:
        return '30 - 40'
    elif 40 < val <= 50:
        return '40 - 50'
    else:
        return 'older than 50'
# applying function on the column
combined_updated['age'] = df['age'].apply(map_cat2)
# do the same like with service to get percentage of dissatisfied people
# in different age ranges
sum_age = pd.pivot_table(data = combined_updated, index ='age', values='dissatisfied',aggfunc = np.sum)
count_age = pd.pivot_table(data = combined_updated, index ='age', values='dissatisfied',aggfunc = 'count')
percentage_age = round((sum_age / count_age)*100, 1)
# use CategoricalDtype to set categories and sort in needed order
from pandas.api.types import CategoricalDtype
categ = ['20 or younger',
'20 - 25',
'25 - 30',
'30 - 40',
'40 - 50',
'older than 50']
cat_type = CategoricalDtype(categories = categ,
                            ordered=True)
percentage_age = percentage_age.reset_index()
percentage_age['age'] = percentage_age['age'].astype(cat_type)
percentage_age = percentage_age.sort_values('age')
percentage_age
# creating visualization to analize 
fig,ax = plt.subplots(figsize = (8,6))
sns.barplot(x = 'age', y = 'dissatisfied', data = percentage_age, color = 'darkblue', alpha = 0.7)
plt.plot(percentage_age['dissatisfied'].values, color = 'black', marker='o', alpha = 0.8)
sns.despine()
plt.xlabel('')
plt.ylabel('dissatisfied %')
plt.title('Age vs Dissatisfaction of a job')<jupyter_output>/dataquest/system/env/python3/lib/python3.4/site-packages/seaborn/categorical.py:1428: FutureWarning:

remove_na is deprecated and is a private function. Do not use.<jupyter_text>From this graphic we see a tendency, that dissatisfaction is increasing with age. After 30 years old the percentage decreases a little bit (on 5%) but then increases again. The maximum level of dissatisfaction falls on the 25-30 range. I suppose, that in this period of life, people want to develop a career quickly, set their lives before settling families and because of that more seriously pay attention to conditions of work. Whereas for the young generation, this is something new, they get a new experience  and they haven't got tired yet from their job.  DETE vs TAFE ___Let's now answer one more interesting question about these datasets. * Did more employees in the DETE survey or TAFE survey end their employment because they were dissatisfied in some way?We will modify a table that we used to analyze the difference in dissatisfaction between years of service. We take into account survey institutes, build the pivot table, then melt the data into 'tidy' appearance and create a graphic according to the institutions. This is an approach to how we can analyze this difference.<jupyter_code># creating pivot table taking into account `institute` column
sum_cut2 = pd.pivot_table(data = combined_updated, index ='service_cat', values='dissatisfied', columns = 'institute', aggfunc = np.sum)
count_cut2 = pd.pivot_table(data = combined_updated, index ='service_cat', values='dissatisfied', columns = 'institute', aggfunc = 'count')
percentage_cut2 = round((sum_cut2 / count_cut2)*100, 1)
percentage_cut2 = percentage_cut2.reset_index()
percentage_cut2
# representing data in tidy way, melting data
percentage_cut2 = pd.melt(percentage_cut2, id_vars='service_cat', value_vars=['DETE','TAFE'])
# creating graphic
plt.subplots(figsize = (8,6))
sns.barplot(x = 'service_cat', y = 'value', hue = 'institute', data = percentage_cut2)
sns.despine(bottom = True, left = True)
plt.ylabel('dissatisfied %')
plt.xlabel('service categories')
plt.title('Dissatisfied %, Surveys Comparing')<jupyter_output>/dataquest/system/env/python3/lib/python3.4/site-packages/seaborn/categorical.py:1468: FutureWarning:

remove_na is deprecated and is a private function. Do not use.
{'fraction_non_alphanumeric': 0.09216201998947922, 'fraction_numerical': 0.01781518499035595, 'mean_word_length': 5.120626743936467, 'pattern_counts': {'":': 0, '<': 139, '<?xml version=': 0, '>': 127, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '33', 'dir': 'jupyter-structured-clean-dedup', 'id': '106426', 'license': 'MIT', 'n_tokens_mistral': 8946, 'n_tokens_neox': 8301, 'n_words': 2952, 'path': 'Employee_exit_surveys/Basics.ipynb', 'repo_name': 'kprokofi/Data_analysis_projects', 'size': '28515'}