Logistic regression and project note¶
Logistic regression is a classification algorithm, used when the value of the target variable is categorical in nature.
Classification tasks have discrete categories, unlike regressions tasks that are continuous in nature i.e. integer and float numbers.
There are 2 types of Logistic Regression. They are
1) Binary logistic regression.¶
Binary logistic regression is a classification method that generalizes logistic regression to two possible outcomes. In this model,the dependent variable has two levels (categorical).This type is used to model the probability of a certain class or event existing such as pass/fail, win/lose, alive/dead or healthy/sick.
2) Multinomial logistic regression¶
Multinomial logistic regression is a classification method that generalizes logistic regression to multiclass problems.
In this model,the dependent variable has more than two levels (categorical). This model has several classes of events such as determining whether an image contains a cat, dog, lion, tiger etc
That is, it is a model that is used to predict the probabilities of the different possible outcomes of a categorically distributed dependent variable, given a set of independent variables.
In the example below, we will concentrate on Multinomial Logistic Regression.
Multinomial logistic regression : This is a real world example of Logistic Regression Multiclass Classification using HR data.¶
In this example, we need to determine which columns are relevant in regards to employee Pay Band. In Logistic Regression (Multiclass Classification) there are more than 2 dependent variables outcome. In our example below, there are 9 possible outcomes.
Pay_Band_Desc : We need to determine the Pay Band for a new employee based on all the x independent variables."Pay Band Desc" will be your dependent y column.
Pay_Band_Desc : SalaryAmount¶
Low Band 1 = 20000¶
Low Band 2 = 25000¶
Low Band 3 = 28000¶
Medium Band 1 = 30000¶
Medium Band 2 = 38000¶
Medium Band 3 = 45000¶
High Band 1 = 55000¶
High Band 2 = 65000¶
High Band 3 = 80000¶
SalaryAmount : lookup values for Pay_Band_Desc¶
Satisfaction_Level_At_Interview : This is your score grade at the interview. 1 = Highest , 0 = Lowest¶
Department : Employeee department¶
Education : Employee education¶
Years_Of_Experience : Employee years of experience¶
Gender : Male or Female¶
Age : Employee age¶
AgeBand: Grouping employee age into bands ie 16 to 19 years is Band 1¶
The data is stored in SQL Server tables.
[Tutorial].[dbo].[HR_EmployeePayBandActual] : This is our trained table, it contains all the actual live data.
[Tutorial].[dbo].[HR_EmployeePayBandPrediction] : This is the test table. We have to determine the value of empty Pay_Band_Desc column.
[Tutorial].[dbo].[HR_EmployeePayBand] : This is our main final table. It contains both trained(Actual data) and predicted data. The column namely Status contains 2 values. They are Predicted and Actual
In [97]:
# Import packages
import numpy as np
import pandas as pd
import pyodbc
from sklearn.linear_model import LogisticRegression
import matplotlib.pyplot as plt
from sklearn.metrics import mean_squared_error, r2_score
from sklearn.model_selection import train_test_split
# Step 1: get your data from SQL Server and assign it to a pandas variable
conn_str = pyodbc.connect('Driver={SQL Server};' 'Server=YourServerName;''Database=Tutorial;''Trusted_Connection=yes;')
query_str = "SELECT [Satisfaction_Level_At_Interview],[Department],[Pay_Band_Desc],[SalaryAmount],[Education] \
,[Years_Of_Experience],[Gender],[Age],[AgeBand] FROM [Tutorial].[dbo].[HR_EmployeePayBandActual]"
df = pd.read_sql(sql=query_str, con=conn_str)
df.head(10)
Out[97]:
In [98]:
# Step 2: This is where we will do a lot of data analysis in order to figure out the important independent variables x that have an effect on dependent variable y.
# From the averages below, it is safe to conclude that the higher the Satisfaction_Level_At_Interview, the higher your salary,
# hence, you are likely to be in the higher pay band. We can also conclude that the independent variable Age is irrelevant.
# Hmmmm... what about the AgeBand ? We will see.
averages = df.groupby(df.Pay_Band_Desc).mean()
averages.head(10)
Out[98]:
In [99]:
# Since Department,Education ,Years_Of_Experience ,Gender and AgeBand are textual and exluded from the step above,
# we need a separate groupby to see if these columns are relevant.
# Let's get a bar chat to display Department. From the Department bar chat, we can conclude that this column is not relevant.
# We can come to the same conclusion from the groupby query
pd.crosstab(df.Department,df.Pay_Band_Desc).plot(kind='barh')
df1 = df.groupby(["Department"]).agg({'SalaryAmount': ['mean']})
df1.columns = ["Avg Salary"]
df1.head(10)
Out[99]:
In [100]:
# We can easily conclude that the better education you have, the higher your salary ( higher pay band). Education is relevant.
df2 = df.groupby(["Education"]).agg({'SalaryAmount': ['mean']})
df2.columns = ["Avg Salary"]
df2.head(5)
Out[100]:
In [101]:
# We can easily conclude that the more experience you have, the higher your salary ( higher pay band). Experience is relevant.
df3 = df.groupby(["Years_Of_Experience"]).agg({'SalaryAmount': ['mean']})
df3.columns = ["Avg Salary"]
df3.head(8)
Out[101]:
In [102]:
# Is Gender relevant? The answer is NO. We can ignore this column in our final analysis.
df4 = df.groupby(["Gender"]).agg({'SalaryAmount': ['mean']})
df4.columns = ["Avg Salary"]
df4.head(3)
Out[102]:
In [103]:
# Lastly, we know that Age is irrelevant. But what about if we use AgeBand? We can come to the same conclusion that
# AgeBrand is also irrelevant
df4 = df.groupby(["AgeBand"]).agg({'SalaryAmount': ['mean']})
df4.columns = ["Avg Salary"]
df4.head(9)
Out[103]:
In [104]:
# Step 3: Now that we've completed our analysis, we can come to the conclusion that the relevant independent variable x
# that affects dependent variable y (Pay_Band_Desc) are : Satisfaction_Level_At_Interview ,Education and Years_Of_Experience.
# These are our independent variables x. We need to get dummies for Education and Years_Of_Experience text columns.
# Let's get dummies for Education column
dummies = pd.get_dummies(df.Education)
dummies.columns = ['First_Degree', 'Masters','No_Degree','PhD']
print(dummies.head())
In [105]:
# Let's get dummies for Years_Of_Experience column
dummies1 = pd.get_dummies(df.Years_Of_Experience)
dummies1.columns = ['1_Yr', '2_Yrs','3_Yrs','4_Yrs','5+_Yrs', 'None']
print(dummies1.head())
In [106]:
# merge the dataset using pandas function namely concat that takes in Lists arguments and specify axis as columns
merged = pd.concat([df,dummies,dummies1], axis = 'columns')
merged.head(10)
Out[106]:
In [107]:
# Let's drop all the irrelevant columns including one column each ('No_Degree','None') from the dummies columns in order to get
# independent variables x
# get columns for x
x = merged.drop(['Department', 'Pay_Band_Desc' , 'SalaryAmount' , 'Education' , 'Years_Of_Experience' , 'Gender' ,'Age', 'AgeBand','No_Degree','None'], axis = 'columns')
x.head()
Out[107]:
In [108]:
# get dependent variables y
y = df.Pay_Band_Desc
y.head()
Out[108]:
In [109]:
# Step 6: train and fit your model to use available datasets.
x_train, x_test, y_train, y_test = train_test_split(x, y, test_size=0.3, random_state=0)
x_train.head()
Out[109]:
In [110]:
# set max_iter in order to overcome this swarning : ConvergenceWarning: lbfgs failed to converge (status=1)
reg = LogisticRegression(max_iter=2000)
reg.fit(x_train,y_train) # fit your logistic regression model.
Out[110]:
In [111]:
# let's make some prediction and check accuracy of the model
y_predict = reg.predict(x_test)
print("The predictions are :",y_predict)
In [112]:
# Now, let's predict
# A score of 0.9 at interview, PhD and 5 years experience
test_predict = reg.predict([[0.9,0, 0,1,0,0,0,0,1]])
print("A score of 0.9 at interview, PhD and 5 years experience :",test_predict)
# A score of 0.778 at interview, First Degree and 1 year experience
test_predict1 = reg.predict([[0.778,1, 0,0,1,0,0,0,0]])
print("A score of 0.778 at interview, First Degree and 1 year experience :",test_predict1)
# A score of 0.1 at interview, No Degree and No Experience
test_predict2 = reg.predict([[0.1,0, 0,0,0,0,0,0,0]])
print("A score of 0.1 at interview, No Degree and No Experience :",test_predict2)
In [113]:
# How accurate is our model?
r2 = reg.score(x_test,y_test)
print("The accuracy of the model is :" ,r2)
Finally, the interesting part. Let's get the sql table and predict new employee pay band based on the various independent variables.¶
In [114]:
# Let's get the prediction table
query_str1 = "SELECT [Satisfaction_Level_At_Interview],[Department],[Pay_Band_Desc],[SalaryAmount],[Education] \
,[Years_Of_Experience],[Gender],[Age],[AgeBand] FROM [Tutorial].[dbo].[HR_EmployeePayBandPrediction]"
dfp = pd.read_sql(sql=query_str1, con=conn_str)
dfp.head()
Out[114]:
In [115]:
# Based on earlier analysis above, we are only interested in Satisfaction_Level_At_Interview,Years_Of_Experience and Education.
# Let's get dummies for education and as a rule of thump drop one of the column
dummies_p = pd.get_dummies(dfp.Education)
dummies_p.columns = ['First_Degree','Masters','No_Degree','PhD']
dummies_p.head()
Out[115]:
In [116]:
# Let's get dummies for Years_Of_Experience
dummies1_p = pd.get_dummies(dfp.Years_Of_Experience)
dummies1_p.columns = ['1_Yr', '2_Yrs','3_Yrs','4_Yrs','5+_Yrs', 'None']
dummies1_p.head()
Out[116]:
In [117]:
#Let's get all the x columns
x_columns = pd.concat([dfp.Satisfaction_Level_At_Interview,dummies_p,dummies1_p], axis = 'columns')
x_columns.head()
Out[117]:
IT IS IMPORTANT THAT THE ORDER OF YOUR FINAL X IN THIS PREDICTIVE TABLE SHOULD BE EXACTLY AS THE ONE ABOVE I.E THE ONE IN THE ACTUAL TABLE¶
In [118]:
# Let's drop No_Degree and None
x_final = x_columns.drop(['No_Degree','None'], axis = 'columns')
x_final.head()
Out[118]:
In [119]:
# Let's make some prediction
y_predict1 = reg.predict(x_final)
y_predict1
Out[119]:
In [120]:
# Let's put our prediction above into a column named Pay_Band_Desc
x_final['Pay_Band_Desc'] = y_predict1
x_final.head()
Out[120]:
In [121]:
# It is important to get x_final to have exacly the same columns as our final destination table
x_final1 = pd.concat([x_final,dfp.Department,dfp.SalaryAmount,dfp.Education,dfp.Years_Of_Experience,dfp.Gender,dfp.Age,dfp.AgeBand], axis = 'columns')
x_final1.head()
Out[121]:
In [122]:
x_final = x_final1.drop(['First_Degree','Masters','PhD', '1_Yr','2_Yrs','3_Yrs','4_Yrs','5+_Yrs'], axis = 'columns')
x_final.head()
Out[122]:
In [123]:
# Let's add Status column in order to be able to differentiate between actual and predicted values
x_final['Status'] = 'Predicted'
In [124]:
x_final.head()
Out[124]:
In [125]:
############# PLEASE TRY NOT TO USE CURSOR IN PRODUCTION ENVIRONMENT.##################
# lastly, insert predicted table into HR_EmployeePayBand table
cursor = conn_str.cursor()
cursor.execute("EXEC [LoadHR_EmployeePayBand]") # truncate table and only load it with actual data. See next cell below.
for i, row in x_final.iterrows():
cursor.execute("INSERT INTO [Tutorial].[dbo].[HR_EmployeePayBand]([Satisfaction_Level_At_Interview],[Department],[Pay_Band_Desc],[SalaryAmount], \
[Education],[Years_Of_Experience], [Gender],[Age], [AgeBand],[Status]) values(?,?,?,?,?,?,?,?,?,?)",
row['Satisfaction_Level_At_Interview'],row['Department'],row['Pay_Band_Desc'],row['SalaryAmount'], row['Education'],row['Years_Of_Experience'],\
row['Gender'],row['Age'], row['AgeBand'], row['Status'])
# Let's update the SalaryAmount column from the lookup table. For performance, do this update in your SQL Server backend.
cursor.execute(" UPDATE t \
SET t.[SalaryAmount] = t1.[SalaryAmount]\
FROM [Tutorial].[dbo].[HR_EmployeePayBand] t \
INNER JOIN [Tutorial].[dbo].[HR_EmployeePayBandLookup] t1 \
ON t.[Pay_Band_Desc] = t1.[Pay_Band_Desc]")
conn_str.commit()
cursor.close()
conn_str.close()
In [ ]:
''' The code below is for LoadHR_EmployeePayBand Stored Procedure
USE [Tutorial]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LoadHR_EmployeePayBand]
AS
BEGIN
TRUNCATE TABLE [Tutorial].[dbo].[HR_EmployeePayBand]
INSERT INTO [Tutorial].[dbo].[HR_EmployeePayBand]
SELECT [Satisfaction_Level_At_Interview]
,[Department]
,[Pay_Band_Desc]
,[SalaryAmount]
,[Education]
,[Years_Of_Experience]
,[Gender]
,[Age]
,AgeBand
,'Actual' AS [Status]
FROM [Tutorial].[dbo].[HR_EmployeePayBandActual]
END
'''
In [127]:
# Let's see some of the trained data(Status = Actual)
conn_str1 = pyodbc.connect('Driver={SQL Server};' 'Server=YourServerName;''Database=Tutorial;''Trusted_Connection=yes;')
query_str1 = "SELECT * FROM [Tutorial].[dbo].[HR_EmployeePayBand] WHERE Status = 'Actual' "
dftest = pd.read_sql(sql=query_str1, con=conn_str1)
dftest.head(5)
Out[127]:
In [128]:
# Let's see some of the predicted data(Status = Predicted)
conn_str1 = pyodbc.connect('Driver={SQL Server};' 'Server=YourServerName;''Database=Tutorial;''Trusted_Connection=yes;')
query_str1 = "SELECT * FROM [Tutorial].[dbo].[HR_EmployeePayBand] WHERE Status = 'Predicted' "
dftest = pd.read_sql(sql=query_str1, con=conn_str1)
dftest.head(5)
Out[128]: