Absenteesim Exercise - Integration

In [1]:
#Import previously saved model
from absenteeism_module import *
model = absenteeism_model('model', 'scaler')

#Import new data set
model.load_and_clean_data('Absenteeism_new_data.csv')

# Predict with new value
model.predicted_outputs()
Out[1]:
Reason_1 Reason_2 Reason_3 Reason_4 Month Value Transportation Expense Age Body Mass Index Education Children Pet Probability Prediction
0 0 0.0 0 1 6 179 30 19 1 0 0 0.105075 0
1 1 0.0 0 0 6 361 28 27 0 1 4 0.810120 1
2 0 0.0 0 1 6 155 34 25 0 2 0 0.241170 0
3 0 0.0 0 1 6 179 40 22 1 2 0 0.160729 0
4 1 0.0 0 0 6 155 34 25 0 2 0 0.751491 1
5 1 0.0 0 0 6 225 28 24 0 1 2 0.692978 1
6 1 0.0 0 0 6 118 46 25 0 2 0 0.632960 1
7 0 0.0 0 1 6 179 30 19 1 0 0 0.105075 0
8 0 0.0 0 1 6 118 37 28 0 0 0 0.133915 0
9 1 0.0 0 0 6 118 37 28 0 0 0 0.595337 1
10 0 0.0 0 1 6 378 36 21 0 2 4 0.286105 0
11 0 0.0 1 0 6 118 50 31 0 1 0 0.756993 1
12 0 0.0 1 0 6 233 31 21 1 1 8 0.221894 0
13 0 0.0 0 1 6 179 30 19 1 0 0 0.105075 0
14 0 0.0 0 0 6 235 48 33 0 1 5 0.066559 0
15 0 0.0 0 0 6 268 33 25 1 0 0 0.188043 0
16 0 0.0 1 0 6 118 50 31 0 1 0 0.756993 1
17 1 0.0 0 0 6 179 30 19 1 0 0 0.527669 1
18 0 0.0 0 1 6 291 40 25 0 1 1 0.326469 0
19 1 0.0 0 0 7 179 30 19 1 0 0 0.543492 1
20 0 0.0 0 1 7 118 37 28 0 0 0 0.141467 0
21 0 0.0 0 1 7 233 31 21 1 1 8 0.017512 0
22 1 0.0 0 0 7 118 37 28 0 0 0 0.610567 1
23 1 0.0 0 0 7 118 37 28 0 0 0 0.610567 1
24 0 0.0 0 1 7 233 31 21 1 1 8 0.017512 0
25 0 0.0 0 1 7 235 43 38 0 1 0 0.477286 0
26 0 0.0 1 0 7 233 31 21 1 1 8 0.233072 0
27 1 0.0 0 0 7 228 58 22 0 2 1 0.683749 1
28 0 0.0 0 1 7 118 37 28 0 0 0 0.141467 0
29 1 0.0 0 0 7 228 58 22 0 2 1 0.683749 1
30 0 0.0 0 1 7 189 33 25 0 2 2 0.187752 0
31 0 0.0 0 1 7 118 37 28 0 0 0 0.141467 0
32 0 0.0 0 1 7 361 28 27 0 1 4 0.323344 0
33 0 0.0 0 1 7 225 28 24 0 1 2 0.201788 0
34 1 0.0 0 0 7 369 31 25 0 3 0 0.964602 1
35 1 0.0 0 0 7 289 33 30 0 2 1 0.906619 1
36 1 0.0 0 0 7 235 37 29 1 1 1 0.731839 1
37 0 0.0 0 0 7 118 40 34 0 1 8 0.012369 0
38 0 0.0 0 0 7 231 39 35 0 2 2 0.266691 0
39 0 0.0 0 0 7 179 53 25 0 1 1 0.096073 0

Create Database cnnection

In [2]:
#Import library for mysql
import pymysql
In [3]:
# Specify the database details for further peocess.
conn = pymysql.connect(database = 'predicted_outputs', user = 'nativeuser', password = '365Pass')
In [4]:
#Create the cursor to involke the connection
cursor = conn.cursor()

# Checkpoint 'df_new_obs'

In [5]:
#Store the predicted output in the nwe data frame called 'df_new_obs'
df_new_obs = model.predicted_outputs()
df_new_obs
Out[5]:
Reason_1 Reason_2 Reason_3 Reason_4 Month Value Transportation Expense Age Body Mass Index Education Children Pet Probability Prediction
0 0 0.0 0 1 6 179 30 19 1 0 0 0.105075 0
1 1 0.0 0 0 6 361 28 27 0 1 4 0.810120 1
2 0 0.0 0 1 6 155 34 25 0 2 0 0.241170 0
3 0 0.0 0 1 6 179 40 22 1 2 0 0.160729 0
4 1 0.0 0 0 6 155 34 25 0 2 0 0.751491 1
5 1 0.0 0 0 6 225 28 24 0 1 2 0.692978 1
6 1 0.0 0 0 6 118 46 25 0 2 0 0.632960 1
7 0 0.0 0 1 6 179 30 19 1 0 0 0.105075 0
8 0 0.0 0 1 6 118 37 28 0 0 0 0.133915 0
9 1 0.0 0 0 6 118 37 28 0 0 0 0.595337 1
10 0 0.0 0 1 6 378 36 21 0 2 4 0.286105 0
11 0 0.0 1 0 6 118 50 31 0 1 0 0.756993 1
12 0 0.0 1 0 6 233 31 21 1 1 8 0.221894 0
13 0 0.0 0 1 6 179 30 19 1 0 0 0.105075 0
14 0 0.0 0 0 6 235 48 33 0 1 5 0.066559 0
15 0 0.0 0 0 6 268 33 25 1 0 0 0.188043 0
16 0 0.0 1 0 6 118 50 31 0 1 0 0.756993 1
17 1 0.0 0 0 6 179 30 19 1 0 0 0.527669 1
18 0 0.0 0 1 6 291 40 25 0 1 1 0.326469 0
19 1 0.0 0 0 7 179 30 19 1 0 0 0.543492 1
20 0 0.0 0 1 7 118 37 28 0 0 0 0.141467 0
21 0 0.0 0 1 7 233 31 21 1 1 8 0.017512 0
22 1 0.0 0 0 7 118 37 28 0 0 0 0.610567 1
23 1 0.0 0 0 7 118 37 28 0 0 0 0.610567 1
24 0 0.0 0 1 7 233 31 21 1 1 8 0.017512 0
25 0 0.0 0 1 7 235 43 38 0 1 0 0.477286 0
26 0 0.0 1 0 7 233 31 21 1 1 8 0.233072 0
27 1 0.0 0 0 7 228 58 22 0 2 1 0.683749 1
28 0 0.0 0 1 7 118 37 28 0 0 0 0.141467 0
29 1 0.0 0 0 7 228 58 22 0 2 1 0.683749 1
30 0 0.0 0 1 7 189 33 25 0 2 2 0.187752 0
31 0 0.0 0 1 7 118 37 28 0 0 0 0.141467 0
32 0 0.0 0 1 7 361 28 27 0 1 4 0.323344 0
33 0 0.0 0 1 7 225 28 24 0 1 2 0.201788 0
34 1 0.0 0 0 7 369 31 25 0 3 0 0.964602 1
35 1 0.0 0 0 7 289 33 30 0 2 1 0.906619 1
36 1 0.0 0 0 7 235 37 29 1 1 1 0.731839 1
37 0 0.0 0 0 7 118 40 34 0 1 8 0.012369 0
38 0 0.0 0 0 7 231 39 35 0 2 2 0.266691 0
39 0 0.0 0 0 7 179 53 25 0 1 1 0.096073 0

.execute() method

In [9]:
# To use .execute() method retrive the data from data base
cursor.execute('SELECT * FROM predicted_outputs;') 

# Output 0 shows there is no data present in the table
Out[9]:
0

Creating the INSERT Statement

In [10]:
#Asssign insert query to variable
insert_query = 'INSERT INTO predicted_outputs VALUES '
In [11]:
insert_query
Out[11]:
'INSERT INTO predicted_outputs VALUES '
In [12]:
df_new_obs.shape
Out[12]:
(40, 13)

So in our new data freame we have 40 rows and 13 column

In [15]:
df_new_obs['Age']
Out[15]:
0     30
1     28
2     34
3     40
4     34
5     28
6     46
7     30
8     37
9     37
10    36
11    50
12    31
13    30
14    48
15    33
16    50
17    30
18    40
19    30
20    37
21    31
22    37
23    37
24    31
25    43
26    31
27    58
28    37
29    58
30    33
31    37
32    28
33    28
34    31
35    33
36    37
37    40
38    39
39    53
Name: Age, dtype: int64
In [17]:
df_new_obs[df_new_obs.columns.values[6]][0]
Out[17]:
30

Use for loop to insert the data into data base

In [18]:
#The first iteration we want to check every row in the dataframe and then run insert command
for i in range(df_new_obs.shape[0]):
    insert_query += '(' 

    #Similarly we want to insert data in column. Here j represent column    
    for j in range(df_new_obs.shape[1]):
        insert_query += str(df_new_obs[df_new_obs.columns.values[j]][i]) + ', '
    
    insert_query = insert_query[:-2] + '), '
In [19]:
insert_query
Out[19]:
'INSERT INTO predicted_outputs VALUES (0, 0.0, 0, 1, 6, 179, 30, 19, 1, 0, 0, 0.1050748143291057, 0), (1, 0.0, 0, 0, 6, 361, 28, 27, 0, 1, 4, 0.810119898086104, 1), (0, 0.0, 0, 1, 6, 155, 34, 25, 0, 2, 0, 0.24117044969973478, 0), (0, 0.0, 0, 1, 6, 179, 40, 22, 1, 2, 0, 0.16072924168708813, 0), (1, 0.0, 0, 0, 6, 155, 34, 25, 0, 2, 0, 0.7514912643313632, 1), (1, 0.0, 0, 0, 6, 225, 28, 24, 0, 1, 2, 0.6929781448353303, 1), (1, 0.0, 0, 0, 6, 118, 46, 25, 0, 2, 0, 0.6329602375469388, 1), (0, 0.0, 0, 1, 6, 179, 30, 19, 1, 0, 0, 0.1050748143291057, 0), (0, 0.0, 0, 1, 6, 118, 37, 28, 0, 0, 0, 0.13391462868552698, 0), (1, 0.0, 0, 0, 6, 118, 37, 28, 0, 0, 0, 0.5953371990526135, 1), (0, 0.0, 0, 1, 6, 378, 36, 21, 0, 2, 4, 0.28610549839232, 0), (0, 0.0, 1, 0, 6, 118, 50, 31, 0, 1, 0, 0.7569925957613495, 1), (0, 0.0, 1, 0, 6, 233, 31, 21, 1, 1, 8, 0.22189357696108014, 0), (0, 0.0, 0, 1, 6, 179, 30, 19, 1, 0, 0, 0.1050748143291057, 0), (0, 0.0, 0, 0, 6, 235, 48, 33, 0, 1, 5, 0.0665590726801133, 0), (0, 0.0, 0, 0, 6, 268, 33, 25, 1, 0, 0, 0.18804298582412018, 0), (0, 0.0, 1, 0, 6, 118, 50, 31, 0, 1, 0, 0.7569925957613495, 1), (1, 0.0, 0, 0, 6, 179, 30, 19, 1, 0, 0, 0.5276685343597127, 1), (0, 0.0, 0, 1, 6, 291, 40, 25, 0, 1, 1, 0.32646901379851917, 0), (1, 0.0, 0, 0, 7, 179, 30, 19, 1, 0, 0, 0.543491979581516, 1), (0, 0.0, 0, 1, 7, 118, 37, 28, 0, 0, 0, 0.14146688138416358, 0), (0, 0.0, 0, 1, 7, 233, 31, 21, 1, 1, 8, 0.017511983341024603, 0), (1, 0.0, 0, 0, 7, 118, 37, 28, 0, 0, 0, 0.6105667631385643, 1), (1, 0.0, 0, 0, 7, 118, 37, 28, 0, 0, 0, 0.6105667631385643, 1), (0, 0.0, 0, 1, 7, 233, 31, 21, 1, 1, 8, 0.017511983341024603, 0), (0, 0.0, 0, 1, 7, 235, 43, 38, 0, 1, 0, 0.4772856504385141, 0), (0, 0.0, 1, 0, 7, 233, 31, 21, 1, 1, 8, 0.23307225933833162, 0), (1, 0.0, 0, 0, 7, 228, 58, 22, 0, 2, 1, 0.6837485499190792, 1), (0, 0.0, 0, 1, 7, 118, 37, 28, 0, 0, 0, 0.14146688138416358, 0), (1, 0.0, 0, 0, 7, 228, 58, 22, 0, 2, 1, 0.6837485499190792, 1), (0, 0.0, 0, 1, 7, 189, 33, 25, 0, 2, 2, 0.1877515810858458, 0), (0, 0.0, 0, 1, 7, 118, 37, 28, 0, 0, 0, 0.14146688138416358, 0), (0, 0.0, 0, 1, 7, 361, 28, 27, 0, 1, 4, 0.3233444855579362, 0), (0, 0.0, 0, 1, 7, 225, 28, 24, 0, 1, 2, 0.20178842764749713, 0), (1, 0.0, 0, 0, 7, 369, 31, 25, 0, 3, 0, 0.9646018697022684, 1), (1, 0.0, 0, 0, 7, 289, 33, 30, 0, 2, 1, 0.9066189840747448, 1), (1, 0.0, 0, 0, 7, 235, 37, 29, 1, 1, 1, 0.7318389674842187, 1), (0, 0.0, 0, 0, 7, 118, 40, 34, 0, 1, 8, 0.012369097946649985, 0), (0, 0.0, 0, 0, 7, 231, 39, 35, 0, 2, 2, 0.2666906934556679, 0), (0, 0.0, 0, 0, 7, 179, 53, 25, 0, 1, 1, 0.0960731564962269, 0), '

As we can see above insert query at the end it is , but in data base query it should be ;. To do that we need to follow below steps

In [20]:
#Remove ',' from insert query and pur ';' in that.
insert_query = insert_query[:-2] + ';'
In [21]:
# Now check again it has been corrected or not
insert_query
Out[21]:
'INSERT INTO predicted_outputs VALUES (0, 0.0, 0, 1, 6, 179, 30, 19, 1, 0, 0, 0.1050748143291057, 0), (1, 0.0, 0, 0, 6, 361, 28, 27, 0, 1, 4, 0.810119898086104, 1), (0, 0.0, 0, 1, 6, 155, 34, 25, 0, 2, 0, 0.24117044969973478, 0), (0, 0.0, 0, 1, 6, 179, 40, 22, 1, 2, 0, 0.16072924168708813, 0), (1, 0.0, 0, 0, 6, 155, 34, 25, 0, 2, 0, 0.7514912643313632, 1), (1, 0.0, 0, 0, 6, 225, 28, 24, 0, 1, 2, 0.6929781448353303, 1), (1, 0.0, 0, 0, 6, 118, 46, 25, 0, 2, 0, 0.6329602375469388, 1), (0, 0.0, 0, 1, 6, 179, 30, 19, 1, 0, 0, 0.1050748143291057, 0), (0, 0.0, 0, 1, 6, 118, 37, 28, 0, 0, 0, 0.13391462868552698, 0), (1, 0.0, 0, 0, 6, 118, 37, 28, 0, 0, 0, 0.5953371990526135, 1), (0, 0.0, 0, 1, 6, 378, 36, 21, 0, 2, 4, 0.28610549839232, 0), (0, 0.0, 1, 0, 6, 118, 50, 31, 0, 1, 0, 0.7569925957613495, 1), (0, 0.0, 1, 0, 6, 233, 31, 21, 1, 1, 8, 0.22189357696108014, 0), (0, 0.0, 0, 1, 6, 179, 30, 19, 1, 0, 0, 0.1050748143291057, 0), (0, 0.0, 0, 0, 6, 235, 48, 33, 0, 1, 5, 0.0665590726801133, 0), (0, 0.0, 0, 0, 6, 268, 33, 25, 1, 0, 0, 0.18804298582412018, 0), (0, 0.0, 1, 0, 6, 118, 50, 31, 0, 1, 0, 0.7569925957613495, 1), (1, 0.0, 0, 0, 6, 179, 30, 19, 1, 0, 0, 0.5276685343597127, 1), (0, 0.0, 0, 1, 6, 291, 40, 25, 0, 1, 1, 0.32646901379851917, 0), (1, 0.0, 0, 0, 7, 179, 30, 19, 1, 0, 0, 0.543491979581516, 1), (0, 0.0, 0, 1, 7, 118, 37, 28, 0, 0, 0, 0.14146688138416358, 0), (0, 0.0, 0, 1, 7, 233, 31, 21, 1, 1, 8, 0.017511983341024603, 0), (1, 0.0, 0, 0, 7, 118, 37, 28, 0, 0, 0, 0.6105667631385643, 1), (1, 0.0, 0, 0, 7, 118, 37, 28, 0, 0, 0, 0.6105667631385643, 1), (0, 0.0, 0, 1, 7, 233, 31, 21, 1, 1, 8, 0.017511983341024603, 0), (0, 0.0, 0, 1, 7, 235, 43, 38, 0, 1, 0, 0.4772856504385141, 0), (0, 0.0, 1, 0, 7, 233, 31, 21, 1, 1, 8, 0.23307225933833162, 0), (1, 0.0, 0, 0, 7, 228, 58, 22, 0, 2, 1, 0.6837485499190792, 1), (0, 0.0, 0, 1, 7, 118, 37, 28, 0, 0, 0, 0.14146688138416358, 0), (1, 0.0, 0, 0, 7, 228, 58, 22, 0, 2, 1, 0.6837485499190792, 1), (0, 0.0, 0, 1, 7, 189, 33, 25, 0, 2, 2, 0.1877515810858458, 0), (0, 0.0, 0, 1, 7, 118, 37, 28, 0, 0, 0, 0.14146688138416358, 0), (0, 0.0, 0, 1, 7, 361, 28, 27, 0, 1, 4, 0.3233444855579362, 0), (0, 0.0, 0, 1, 7, 225, 28, 24, 0, 1, 2, 0.20178842764749713, 0), (1, 0.0, 0, 0, 7, 369, 31, 25, 0, 3, 0, 0.9646018697022684, 1), (1, 0.0, 0, 0, 7, 289, 33, 30, 0, 2, 1, 0.9066189840747448, 1), (1, 0.0, 0, 0, 7, 235, 37, 29, 1, 1, 1, 0.7318389674842187, 1), (0, 0.0, 0, 0, 7, 118, 40, 34, 0, 1, 8, 0.012369097946649985, 0), (0, 0.0, 0, 0, 7, 231, 39, 35, 0, 2, 2, 0.2666906934556679, 0), (0, 0.0, 0, 0, 7, 179, 53, 25, 0, 1, 1, 0.0960731564962269, 0);'
In [22]:
# Now with the help of cursor execute the insert query
cursor.execute(insert_query)
Out[22]:
40
In [23]:
# You have to commit it for successful insertion in your data base
conn.commit()
In [24]:
# Close the connection
conn.close()