Feature engineering for Machine Learning¶

import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
from datetime import date
pd.options.display.max_columns = None

df = pd.read_csv('employeeAbsenteeism.csv')
df.head()

df.to = pd.to_datetime(df.to) # converts string to type datetime
df['from'] = pd.to_datetime(df['from'])
df.head()

df.describe(include='all', datetime_is_numeric=True)

df['leave_time'] = ((df['to'] - df['from'])/np.timedelta64(1, 'D')).astype(int) # takes the difference in days

f, ax = plt.subplots(1, figsize = (20,10))
df['leave_time'].plot.hist()
plt.title('Histogram of the leave period')
plt.show()

df_for_eda = df.copy()

# Creating class for the continuous variable
df_for_eda['leave_time_class'] = np.where(df_for_eda['leave_time'].isna(), 'None', 'Not None')

# Splitting the continuous variable into classes
df_for_eda['leave_time_class'] = pd.cut(df_for_eda.leave_time, 
                                    [0, 1, 5, 10, 15, 20, 25, 29, 30], 
                                    labels = ['1 day', '1-5days', '5-10 days','10-15 days', '15-20 days', '20-25 days', '25-29 days', '30 days'])
grouped_df_for_eda = df_for_eda.groupby('leave_time_class').aggregate(
    number_of_instances = ('leave_time_class', 'count'),
    unique_employees = ('employee', 'nunique')
).reset_index().assign(instance_percentage = lambda a:round(a.number_of_instances*100/(sum(a.number_of_instances)),2)).\
    assign(cumulative_percentage = lambda x:x.instance_percentage.cumsum())

fs, axs = plt.subplots(1, figsize=(20,10))
grouped_df_for_eda.index = grouped_df_for_eda[['leave_time_class']]
grouped_df_for_eda.number_of_instances.plot(kind='bar', ax=axs)
axs.set_ylabel('No of instances')
axs.set_xlabel('leave period (days)')
axs.set_title('Histogram of leave period')
axs.bar_label(axs.containers[0], 
              labels = round(grouped_df_for_eda.instance_percentage, 1).astype(str)+str("% - ")+grouped_df_for_eda.number_of_instances.astype(str) , label_type='edge')

plt.show();
grouped_df_for_eda

df.groupby('reason').\
    aggregate({'leave_time':'sum'}).\
    plot.pie(y='leave_time', figsize=(10, 10), 
             autopct='%1.1f%%', # to add the percentages text
             wedgeprops=dict(width=.5)
            )
plt.ylabel("")
plt.title('Proportion of the number of days leaves were taken due to different reasons')
plt.show()

grouped_df_for_eda = df_for_eda.groupby(['reason', 'leave_time_class']).aggregate(
    number_of_instances = ('leave_time_class', 'count')
).reset_index().pivot(index = 'leave_time_class', columns=['reason'],
          values = ['number_of_instances']).reset_index()
fs, axs = plt.subplots(1, figsize=(20,10))
grouped_df_for_eda.index = grouped_df_for_eda[['leave_time_class']]
grouped_df_for_eda.number_of_instances.plot(kind='bar', ax=axs)
axs.set_ylabel('No of instances')
axs.set_xlabel('leave period (days)')
axs.set_title('Histogram of leave period')
plt.show();

# Creating class for the categorical variable
df_for_eda['reason_class'] = np.where(df_for_eda['reason']=='Common sickness  or accident not related to the job', 'sick', 'others')

grouped_df_for_eda = df_for_eda.groupby(['reason_class', 'leave_time_class']).aggregate(
    number_of_instances = ('leave_time_class', 'count')
).reset_index().pivot(index = 'leave_time_class', columns=['reason_class'],
          values = ['number_of_instances']).reset_index()
# Naming columns
grouped_df_for_eda.columns = ['leave_time_class', 'others_number_of_instances', 'sick_number_of_instances']
# Adding columns for percentages
grouped_df_for_eda = grouped_df_for_eda.\
    assign(others_percentage = lambda x:round(x.others_number_of_instances*100/(sum(x.others_number_of_instances)),2)).\
    assign(others_cumulative_perc = lambda a:round(a.others_percentage.cumsum(), 2)).\
    assign(sick_percentage = lambda x:round(x.sick_number_of_instances*100/(sum(x.sick_number_of_instances)),2)).\
    assign(sick_cumulative_perc = lambda a:round(a.sick_percentage.cumsum(), 2))

# Plotting
fs, axs = plt.subplots(1, 2, sharex='col', sharey='row', figsize = (15,7))
grouped_df_for_eda.index = grouped_df_for_eda[['leave_time_class']]
grouped_df_for_eda.sick_number_of_instances.plot(kind='bar', ax=axs[0])
grouped_df_for_eda.others_number_of_instances.plot(kind='bar', ax=axs[1])

axs[0].bar_label(axs[0].containers[0], labels = round(grouped_df_for_eda.sick_percentage, 1).astype(str)+str("%"), label_type='edge')
axs[1].bar_label(axs[1].containers[0], labels = round(grouped_df_for_eda.others_percentage, 1).astype(str)+str("%"), label_type='edge')

axs[0].set_ylabel('No of leaves')
axs[0].set_xlabel('(leave period)')
axs[0].set_title('Common sickness  or accident not related to the job')
axs[1].set_xlabel('(leave period)')
axs[1].set_title('Other type of leaves')

plt.show();
grouped_df_for_eda

df['date'] = df['from'].dt.date.astype('datetime64[ns]') # gets only the date part from the timeframe
df['from_day'] = df['from'].dt.day
df['to_day'] = df['to'].dt.day

f, ax = plt.subplots(1, 2, figsize = (20,10), sharey = True)
df.groupby(df['from'].dt.day)['employee'].count().\
    plot(kind='bar', ax=ax[0], title = 'Number of leaves with "from" date')
df.groupby(df['to'].dt.day)['employee'].count().\
    plot(kind='bar', ax=ax[1], title = 'Number of leaves with "to" date')
plt.show();

# Feature engineering on absentism dataset
# creating a dataframe with leaves as per employee and dates
leave_df = pd.DataFrame({'date':[], 'employee':[], 'reason':[]})
for index, row in df.iterrows():
    dates = pd.date_range(row['from'].date(), row['to'].date())
    for date in dates:
        leave_df = pd.concat([leave_df, pd.DataFrame({'date':[date], 'employee':[row['employee']], 'reason':[row['reason']]})])
leave_df['on_leave'] = 1

# Creating a cumulative sum to get tehe number of leaves taken by the employee till date
leave_df['no_leaves_till_date'] = leave_df.groupby('employee')['on_leave'].transform(lambda x: x.cumsum().shift()).fillna(0)

f, ax = plt.subplots(1, figsize = (20,10))
leave_df.groupby(['date'])['on_leave'].sum().reset_index().plot(x = 'date', y = 'on_leave', kind = 'scatter', ax=ax)
plt.title('Number of employees on leave')
plt.show()

employee_details = pd.read_csv("lastParticipationExists.csv")

# Convert to datetime
employee_details.lastParticipationDate = pd.to_datetime(employee_details.lastParticipationDate)
employee_details.deletedOn = pd.to_datetime(employee_details.deletedOn)

# Convert to integer
employee_details.stillExists = employee_details.stillExists.astype(int)
employee_details

employee_details.describe(include='all', datetime_is_numeric=True)

employee_details.groupby('timezone').\
    aggregate({'lastParticipationDate':'count'}).\
    plot.pie(y='lastParticipationDate', figsize=(10, 10), 
             autopct='%1.1f%%', # to add the percentages text
             wedgeprops=dict(width=.5)
            )
plt.ylabel("")
plt.title('Employee Locations')
plt.show()

f, ax = plt.subplots(1, figsize = (20,10))

employee_details.loc[employee_details.lastParticipationDate.__ne__(None)].\
    loc[employee_details.stillExists == 0].\
    groupby('lastParticipationDate').aggregate({'stillExists':'count'}).reset_index().\
    plot(x = 'lastParticipationDate', y = 'stillExists', kind = 'scatter', ax=ax)

plt.title('Number of employees who have left over time')
plt.xlabel('Number of employees')
plt.ylabel('Date')
plt.show()

votes = pd.read_csv("votes.csv")

# Convert to datetime
votes['voteDate'] = pd.to_datetime(votes['voteDate'])

# Sort the dataset based on time
votes = votes.sort_values(['employee', 'voteDate'])
votes

votes.describe(include='all', datetime_is_numeric=True)

f, ax = plt.subplots(1, figsize = (20,10))
votes.vote.hist(grid = False, ax = ax)
plt.title('Histogram of the votes')
plt.xlabel('Vote')
plt.ylabel('Frequency')
plt.show()

# Feature engineering on the votes dataset
votes['min_date'] = votes.groupby('employee')['voteDate'].transform(min)

# new employee is 100 day since inception of the software
votes['new_employee'] = (votes.min_date >= '13-06-2017 00:00:00').astype(int) 

# Getting the difference in days since first vote in days
votes['no_of_days_since_first_vote'] = ((votes.voteDate - votes.min_date)/np.timedelta64(1, 'D')).astype(int)+1

# Calculating the cumulative number of votes till date (only considering the past: this step should be done after order by)
votes['no_of_votes_till_date'] = votes.groupby('employee')['voteDate'].transform('cumcount')+1

# calculating the percentage of days that the employee has voted till date
votes['perc_days_voted'] = votes['no_of_votes_till_date']/votes['no_of_days_since_first_vote']

# average of the vote till date by the employee
votes['avg_vote_till_date'] = votes.groupby('employee')['vote'].transform('cumsum')/(votes['no_of_votes_till_date'])

# Average vote in general of the employee
votes['avg_vote'] = votes.groupby('employee')['vote'].transform(np.mean)

# Converting to datetime
votes['date'] = votes.voteDate.dt.date.astype('datetime64[ns]')

# The average of the last two votes (The last two votes might redict the probability of taking a leave now)
votes = votes.sort_values(['employee', 'voteDate'])
votes['last_2_votes_avg'] = votes.groupby('employee').vote.\
    transform(lambda s: s.rolling(2, closed='left').mean())

# The previous vote
votes['prev_vote'] = votes.groupby('employee').vote.transform(lambda s: s.shift(periods = 1))

fs, axs = plt.subplots(1, figsize=(20,10))
votes.groupby(['new_employee', 'no_of_days_since_first_vote']).agg({'vote':'mean'}).reset_index().\
    plot.scatter(x='no_of_days_since_first_vote', y = 'vote', c='new_employee', alpha = 0.4, 
                cmap = sns.diverging_palette(10, 133,as_cmap=True), ax = axs)#, vmin = 2.0, vmax=3.5)
axs.set_ylim([2, 3.5])
plt.title('Averge vote across new and old employee')
plt.xlabel('Number of days since first vote')
plt.show()

fs, axs = plt.subplots(1, figsize=(20,10))
votes.groupby(['voteDate']).agg({'vote':'mean'}).reset_index().\
    plot.scatter(x='voteDate', y = 'vote', alpha = 0.4, ax = axs)
plt.title('Seasonality in the average vote')
plt.xlabel('Date of vote')
plt.show()

# joining the votes table with employee details to add the details of the employee
votes = votes.merge(employee_details, how = 'left', on = 'employee')

people_who_left = votes[votes.stillExists == 0].copy()
people_who_left.loc[:,'no_of_days_since_exit'] = ((people_who_left.voteDate - people_who_left.deletedOn)/np.timedelta64(1, 'D')).astype(int)-1

fs, axs = plt.subplots(1, figsize=(20,10))
people_who_left.groupby(['new_employee', 'no_of_days_since_exit']).agg({'vote':'mean'}).reset_index().\
    plot.scatter(x='no_of_days_since_exit', y = 'vote', alpha = 0.4, ax = axs)
axs.set_xlim([-200, 0])
axs.set_ylim([2, 3.5])
plt.title('Averge vote for employees who have left the organisation')
plt.xlabel('Number of days since exit date')
plt.show()

fs, axs = plt.subplots(1, figsize=(20,10))
sns.scatterplot(data = votes.groupby(['timezone', 'voteDate']).agg({'vote':'mean'}).reset_index(), 
                x='voteDate', y = 'vote', hue = 'timezone', alpha = 0.4, ax = axs)
plt.title('Averge vote for employees in different geographies')
plt.xlabel('Vote date')
plt.show()

feedback = pd.read_csv("comments_by_employees_in_anonymous_forum.csv")
feedback

feedback.describe(include='all', datetime_is_numeric=True)

feedback['log_comment_length'] = np.log10(feedback.commentLength)
feedback['total_interactions'] = feedback.likes+feedback.dislikes
feedback['mean_feedback'] = (feedback.likes-feedback.dislikes)/feedback.total_interactions

fs, axs = plt.subplots(1, figsize=(20,10))
sns.scatterplot(data = feedback, 
                x='total_interactions', y = 'mean_feedback', hue = 'log_comment_length', alpha = 0.8, ax = axs)
plt.title('Mean interaction vs response level for employee')
plt.xlabel('Number of interactions')
plt.ylabel('Mean of feedback')
plt.show()

feedback.groupby('feedbackType').\
    aggregate({'commentId':'count'}).\
    plot.pie(y='commentId', figsize=(10, 10), 
             autopct='%1.1f%%', # to add the percentages text
             wedgeprops=dict(width=.5)
            )
plt.ylabel("")
plt.title('Frequency of somments')
plt.show()

fs, axs = plt.subplots(1, figsize=(20,10))
sns.scatterplot(data = feedback, 
                x='likes', y = 'dislikes', hue = 'feedbackType', alpha = 0.4, ax = axs)
plt.title('Total likes and dislikes for comments')
plt.show()

feedback.groupby('feedbackType').aggregate({'likes':'mean', 'dislikes':'mean'})

f, ax = plt.subplots(2, figsize = (20,20), sharex = True)

sns.boxplot(data = feedback.groupby(['feedbackType', 'employee'])['commentId'].count().reset_index().assign(
        log_count_comments = lambda df: np.log10(df.commentId)
    ), 
            x = 'feedbackType', y = 'log_count_comments', ax = ax[0])

sns.boxplot(data = feedback, x = 'feedbackType', y = 'log_comment_length', ax = ax[1])

ax[0].set_ylabel('Log of number of comments')
ax[1].set_ylabel('Log of comment length')
ax[0].set_title('Number of comments and comment legth across different feedback types')
plt.show()

# feature engineering on the feedback dataset
feedback['commentDate'] = pd.to_datetime(feedback['commentDate'])

# sort by date in ascending to do cumulative sum and rolling calculations
feedback = feedback.sort_values(['employee', 'commentDate'])

# Rolling two likes and dislikes, indicating the last two likes and dislikes
feedback['last_2_likes'] = feedback.groupby('employee').likes.transform(lambda s: s.rolling(2, closed='left').mean())
feedback['last_2_dislikes'] = feedback.groupby('employee').dislikes.transform(lambda s: s.rolling(2, closed='left').mean())
feedback['date'] = feedback.commentDate.dt.date.astype('datetime64[ns]')
feedback

# Count the likes and dislikes till the date given
feedback['likes_till_date']= feedback.groupby('employee')['likes'].transform('cumsum')
feedback['dislikes_till_date']= feedback.groupby('employee')['dislikes'].transform('cumsum')
feedback['comments_till_date']= feedback.groupby('employee')['commentId'].transform('cumcount')

# Creating a dataframe containing the time from the start to the end of voting period
time_dataframe = pd.DataFrame({'date':pd.date_range(min(votes['voteDate']).date(), max(votes['voteDate']).date())})
time_dataframe['tmp'] = 1

# Creating a dataframe with all employees in the absenteeism dataset
complete_df = pd.DataFrame({'employee':df.employee.unique()})
complete_df['tmp'] = 1

# Creating a dataset that contains the combinations of all days for all employees
complete_df = pd.merge(complete_df, time_dataframe, on=['tmp']).drop('tmp', axis = 1)

# Joining the feedback (comments) given by the employees
complete_df = pd.merge(complete_df, feedback, how = 'left', on=['date', 'employee'])
complete_df = complete_df[['employee', 'date', 'likes', 'dislikes', 'feedbackType', 'likes_till_date', 'dislikes_till_date', 'last_2_likes', 'last_2_dislikes', 'commentDate']]

# Filling the last available feedback data the days when there was no feedback data for an employee.
complete_df[['date', 'likes', 'dislikes', 'feedbackType', 'likes_till_date', 'dislikes_till_date', 'last_2_likes', 'last_2_dislikes', 'commentDate']] = \
    complete_df.groupby('employee').fillna(method='ffill')

# Creating new features
complete_df['days_since_last_comment'] = (complete_df.date-complete_df.commentDate)
complete_df['days_since_last_comment'] = (complete_df['days_since_last_comment'].fillna(pd.Timedelta(seconds=0))/np.timedelta64(1, 'D')).astype(int)

# Assuming that for the employees that hae not commented (yet) the feedback is 0.
complete_df = \
    complete_df[['employee', 'date', 'likes', 'dislikes', 'feedbackType', 'likes_till_date', 'dislikes_till_date', 'last_2_likes', 'last_2_dislikes', 'days_since_last_comment']].\
    fillna(0)

# Joining the votes dataset fr every eployee-date
complete_df = pd.merge(complete_df, votes, how = 'left', on=['date', 'employee'])

# Filling the last available votes data the days when there was no vote for an employee.
complete_df[[ 'no_of_votes_till_date','perc_days_voted', 'deletedOn', 'new_employee', 'min_date', 'stillExists', 'vote',
             'avg_vote_till_date', 'avg_vote','last_2_votes_avg', 'voteDate', 'timezone']] = \
    complete_df.groupby('employee')[['no_of_votes_till_date','perc_days_voted', 'deletedOn', 'new_employee', 'min_date', 'stillExists', 'vote',
                                     'avg_vote_till_date', 'avg_vote','last_2_votes_avg', 'voteDate', 'timezone']].\
    fillna(method='ffill')

# Remove the data before the employee joined
complete_df = complete_df[complete_df.avg_vote >= 0]

# Remove data after employee left
complete_df = complete_df[(complete_df.stillExists == 1) | ((complete_df.stillExists == 0) & (complete_df.date <= complete_df.deletedOn))]

# Recomputing no_of_days_since_first_vote
complete_df.no_of_days_since_first_vote = ((complete_df.date - complete_df.min_date)/np.timedelta64(1, 'D')).astype(int)+1

# Adding new features
# assuming a 60 day notice period
# 60 days before the employee leaves are recorded
complete_df.deletedOn = complete_df.deletedOn.fillna(pd.to_datetime(date.today()))
complete_df['countdown_to_last_day'] = ((complete_df.date - complete_df.deletedOn)/np.timedelta64(1, 'D')).astype(int)+1
complete_df.loc[complete_df.countdown_to_last_day < -15, 'countdown_to_last_day'] = 999

# computing days since last vote
complete_df['days_since_last_vote'] = (complete_df.date-complete_df.voteDate)
complete_df['days_since_last_vote'] = (complete_df['days_since_last_vote'].fillna(pd.Timedelta(seconds=0))/np.timedelta64(1, 'D')).astype(int)

# Imputing still exists column
complete_df[['timezone', 'stillExists']] = complete_df.groupby('employee')[['timezone', 'stillExists']].fillna(method = 'bfill')

# Selecting the features used in the model
complete_df = complete_df[
    ['employee', 'date', 'likes', 'dislikes', 'feedbackType', 
     'likes_till_date', 'dislikes_till_date', 'last_2_likes', 'last_2_dislikes', 
     'days_since_last_comment', 'vote', 'timezone', 'stillExists',
       'no_of_days_since_first_vote', 'no_of_votes_till_date',
       'perc_days_voted', 'avg_vote_till_date', 'avg_vote', 'last_2_votes_avg', 'prev_vote',
       'days_since_last_vote', 'new_employee', 'countdown_to_last_day']].fillna(0)
complete_df

# Combining the leaves dataset
data = pd.merge(complete_df, leave_df, how = 'left', on=['date', 'employee'])
data.on_leave = data.on_leave.fillna(0)

# Get the leave status of the previous day
data['previous_day_leave'] = data.groupby('employee')['on_leave'].shift().fillna(0)

# Renaming columns to more suitable ones
data.rename(columns={"likes": "last_likes", "dislikes": "last_dislikes", "feedback_type":"last_feedback_type",
                    "vote":"last_vote", "new_employee":"employee_joined_after_jun17"}, inplace = True)

# Creating new columns
data.no_leaves_till_date = data.no_leaves_till_date.fillna(method = 'bfill').fillna(method = 'ffill')

# Get the number of days the person was on leave in the last two days
def get_rolling_sum(grp, freq, col):
    return(grp.rolling(freq, on='date')[col].sum())
data = data.sort_values(['employee', 'date'])
data['last_2_days_leaves']  = data.groupby('employee', as_index = False, group_keys=False).\
    apply(get_rolling_sum, '2D', 'on_leave') # 2d for 2 days

red_green_cmap = sns.diverging_palette(10, 133,as_cmap=True)

f = plt.figure(figsize=(20, 20))
plt.matshow(data.corr(), fignum = f, cmap = red_green_cmap, vmin=-1, vmax=1)
plt.xticks(range(data.select_dtypes(['number']).shape[1]), 
           data.select_dtypes(['number']).columns, rotation=90, fontsize = 15)
plt.yticks(range(data.select_dtypes(['number']).shape[1]), 
           data.select_dtypes(['number']).columns, rotation=0, fontsize = 15)
cb = plt.colorbar()
cb.ax.tick_params(labelsize=14)
plt.title("Correlation between different parameters", fontsize=20,loc='left')
plt.show()

_ = data.hist(figsize = (20,20))

day_name= ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday','Sunday']
month_name = [None, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
data['weekday'] = data.date.dt.weekday.apply(lambda x:day_name[x])
data['month'] = data.date.dt.month.apply(lambda x:month_name[x])
data['week'] = data.date.dt.day % 7 # Which week in the month
data

data.to_csv('data_after_feature_engg.csv')