# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: 'Python 3.9.1 64-bit (''base'': conda)'
#     name: python3
# ---

# # Bank Marketing Data Set
#
# ## Load a saved model and predict
#
# The data is related with direct marketing campaigns of a Portuguese banking institution.  The marketing campaigns were based on phone calls.  A number of features such as age, kind of job, marital status, education level, credit default, existence of housing loan, etc. were considered.  The classification goal is to predict if the client will subscribe (yes/no) a term deposit.
#
# More information regarding the data set is at https://archive.ics.uci.edu/ml/datasets/bank+marketing#.  For tutorials use only.
#
# <font color=blue>__ _The objective is to show the need for storing a model.  A rudimentary hack to load the model is implemented._ __</font>
#
# ## Attribute Information:
#
# ### Input variables:
# #### Bank client data:
# 1. age (numeric)
# 2. job : type of job (categorical: 'admin.','blue-collar','entrepreneur','housemaid','management','retired','self-employed','services','student','technician','unemployed','unknown')
# 3. marital : marital status (categorical: 'divorced','married','single','unknown'; note: 'divorced' means divorced or widowed)
# 4. education (categorical: 'basic.4y','basic.6y','basic.9y','high.school','illiterate','professional.course','university.degree','unknown')
# 5. default: has credit in default? (categorical: 'no','yes','unknown')
# 6. housing: has housing loan? (categorical: 'no','yes','unknown')
# 7. loan: has personal loan? (categorical: 'no','yes','unknown')
#
# #### Related with the last contact of the current campaign:
# 8. contact: contact communication type (categorical: 'cellular','telephone') 
# 9. month: last contact month of year (categorical: 'jan', 'feb', 'mar', ..., 'nov', 'dec')
# 10. day_of_week: last contact day of the week (categorical: 'mon','tue','wed','thu','fri')
# 11. duration: last contact duration, in seconds (numeric). Important note: this attribute highly affects the output target (e.g., if duration=0 then y='no'). Yet, the duration is not known before a call is performed. Also, after the end of the call y is obviously known. Thus, this input should only be included for benchmark purposes and should be discarded if the intention is to have a realistic predictive model.
#
# #### Other attributes:
# 12. campaign: number of contacts performed during this campaign and for this client (numeric, includes last contact)
# 13. pdays: number of days that passed by after the client was last contacted from a previous campaign (numeric; 999 means client was not previously contacted)
# 14. previous: number of contacts performed before this campaign and for this client (numeric)
# 15. poutcome: outcome of the previous marketing campaign (categorical: 'failure','nonexistent','success')
#
# #### Social and economic context attributes:
# 16. emp.var.rate: employment variation rate - quarterly indicator (numeric)
# 17. cons.price.idx: consumer price index - monthly indicator (numeric) 
# 18. cons.conf.idx: consumer confidence index - monthly indicator (numeric) 
# 19. euribor3m: euribor 3 month rate - daily indicator (numeric)
# 20. nr.employed: number of employees - quarterly indicator (numeric)
#
# ### Output variable (desired target):
# 21. y - has the client subscribed a term deposit? (binary: 'yes','no')
#
# ## Import packages

from hana_ml import dataframe
from hana_ml.algorithms.pal import linear_model
from hana_ml.algorithms.pal import clustering
from hdbcli import dbapi
import numpy as np
import matplotlib.pyplot as plt
import logging

# ## Setup logging

logging.basicConfig()
logger = logging.getLogger('hana_ml.ml_base')
logger.setLevel(logging.ERROR)
logger.addHandler(logging.NullHandler())

# ## Setup connection and data sets
# The data is loaded into 4 tables - full set, test set, training set, and the validation set:
# <li>DBM2_RFULL_TBL</li>
# <li>DBM2_RTEST_TBL</li>
# <li>DBM2_RTRAINING_TBL</li>
# <li>DBM2_RVALIDATION_TBL</li>
#
# To do that, a connection is created and passed to the loader.
#
# There is a config file, <b>config/e2edata.ini</b> that controls the connection parameters and whether or not to reload the data from scratch.  In case the data is already loaded, there would be no need to load the data.  A sample section is below.  If the config parameter, reload_data is true then the tables for test, training, and validation are (re-)created and data inserted into them.
#
# #########################<br>
# [hana]<br>
# url=host.sjc.sap.corp<br>
# user=username<br>
# passwd=<PASSWORD><br>
# port=3xx15<br>
# <br>
#
# #########################<br>

from hana_ml.algorithms.pal.utility import DataSets, Settings
url, port, user, pwd = Settings.load_config("../../config/e2edata.ini")
connection_context = dataframe.ConnectionContext(url, port, user, pwd)
full_set, training_set, validation_set, test_set = DataSets.load_bank_data(connection_context)
features = ['AGE','JOB','MARITAL','EDUCATION','DBM_DEFAULT', 'HOUSING','LOAN','CONTACT','DBM_MONTH','DAY_OF_WEEK','DURATION','CAMPAIGN','PDAYS','PREVIOUS','POUTCOME','EMP_VAR_RATE','CONS_PRICE_IDX','CONS_CONF_IDX','EURIBOR3M','NREMPLOYED']
label = "LABEL"

# # Load the model
# __ _lr = model_persistence.load(connection_context, 'nk_lr', 'DEVUSER')_ __

lr = linear_model.LogisticRegression(solver='newton',
                                thread_ratio=0.1, max_iter=1000, pmml_export='single-row',
                                stat_inf=True, tol=0.000001, class_map0='no', class_map1='yes')

lr.result_ = connection_context.table("MYMODEL")

# ## Load the model by model storage service

# +
from hana_ml.model_storage import ModelStorage

#MODEL_SCHEMA='STORAGE'
# model storage must use the same connection than the model
model_storage = ModelStorage(connection_context=connection_context,
                             #schema=MODEL_SCHEMA
                            )
lr = model_storage.load_model(name='Model A', version=1)
# -

# # Scoring
# Do the scoring on the validation and test sets

validation_accuracy_val = lr.score(validation_set, 'ID', features, label, class_map0='no', class_map1='yes')
print('Validation accuracy=%f' %(validation_accuracy_val))

test_accuracy_val = lr.score(test_set, 'ID', features, label)
print('Test accuracy=%f' %(test_accuracy_val))

predict_on_train_data = lr.predict(training_set, 'ID', features)
print(predict_on_train_data.head(2).collect())


# ### Define a simple predict function

def predict(connection_context, lr, age, job, marital_status, education, dbm_default, housing, loan, contact, dbm_month, day_of_week, duration, campaign, pdays, previous, poutcome, emp_var_rate, cons_price_idx, cons_conf_idx, euribor3m, nremployed):
    #sql = 'create local temporary table #t("ID" INTEGER CS_INT, \
    dt = 'drop table #t'
    sql = 'create local temporary table #t("ID" INTEGER CS_INT,\
           "AGE" INTEGER CS_INT,\
           "JOB" VARCHAR(256),\
           "MARITAL" VARCHAR(100),\
           "EDUCATION" VARCHAR(256),\
           "DBM_DEFAULT" VARCHAR(100),\
           "HOUSING" VARCHAR(100),\
           "LOAN" VARCHAR(100),\
           "CONTACT" VARCHAR(100),\
           "DBM_MONTH" VARCHAR(100),\
           "DAY_OF_WEEK" VARCHAR(100),\
           "DURATION" DOUBLE CS_DOUBLE,\
           "CAMPAIGN" INTEGER CS_INT,\
           "PDAYS" INTEGER CS_INT,\
           "PREVIOUS" INTEGER CS_INT,\
           "POUTCOME" VARCHAR(100),\
           "EMP_VAR_RATE" DOUBLE CS_DOUBLE,\
           "CONS_PRICE_IDX" DOUBLE CS_DOUBLE,\
           "CONS_CONF_IDX" DOUBLE CS_DOUBLE,\
           "EURIBOR3M" DOUBLE CS_DOUBLE,\
           "NREMPLOYED" INTEGER CS_INT)'
    insert = 'insert into #t values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
    with connection_context.connection.cursor() as cur:
        try:
            print('11111')
            cur.execute(dt)
        except:
            print('1111')
            pass
        print('1211')
        print(sql)
        ret = cur.execute(sql)
        if ret == True:
            ret = cur.execute(insert, [99999, age, job, marital_status, education, dbm_default, housing, loan, contact, dbm_month, day_of_week, duration, campaign, pdays, previous, poutcome, emp_var_rate, cons_price_idx, cons_conf_idx, euribor3m, nremployed])
    df = dataframe.DataFrame(connection_context, 'select * from #t')
    prediction = lr.predict(df, 'ID', ['AGE','JOB','MARITAL','EDUCATION','DBM_DEFAULT', 'HOUSING','LOAN','CONTACT','DBM_MONTH','DAY_OF_WEEK','DURATION','CAMPAIGN','PDAYS','PREVIOUS','POUTCOME','EMP_VAR_RATE','CONS_PRICE_IDX','CONS_CONF_IDX','EURIBOR3M','NREMPLOYED'])
    return prediction


p = predict(connection_context, lr, 56, "housemaid","married","basic.4y","no","no","no","telephone","may","mon",261,1,999,0,"nonexistent",1.1,93.994,-36.4,4.857,5191)

p.collect()

p2 = predict(connection_context, lr, 49,"entrepreneur","married","university.degree","unknown","yes","no","telephone","may","mon",1042,1,999,0,"nonexistent",1.1,93.994,-36.4,4.857,5191)

p2.collect()

predict_on_train_data.filter("CLASS='yes'").head(5).collect()

p3 = predict(connection_context, lr, 32,"admin.","married","university.degree","no","yes","no","cellular","aug","fri",1366,1,999,0,"nonexistent",1.4,93.444,-36.1,4.964,5228.1)

p3.collect()

# + jupyter={"outputs_hidden": true}


StarcoderdataJupyterScriptsDedupFiltered