<jupyter_start><jupyter_text>GLM ExampleGeneralized linear models are a generalization of linear regression. In linear regression, responses are modeled as coming from gaussian distributions, each of equal variance, with each distribution's mean given by a linear combination of the predictor variables. GLMs allow other distributions where the variance might vary with the mean, and they allow the mean to be given by a function of the linear predictor instead of taking the linear predictor directly. Boston housing pricesWe'll demonstrate by using GLMs to predict the median price of a home in Boston.  The variables are crime rate, zoning information,proportion of non-retail business, etc.  This dataset has median prices in Boston for 1972.  Even though the data is pretty old, the methodology for analytics is valid for more recent datasets.The dataset is from Kaggle. https://www.kaggle.com/c/boston-housing. For tutorials use only. Housing Values in Suburbs of Boston in 1972The medv variable is the target variable. Data descriptionThe Boston data frame has 506 rows and 14 columns.This data frame contains the following columns:1. __crim__: per capita crime rate by town.2. __zn__: proportion of residential land zoned for lots over 25,000 sq.ft.3. __indus__: proportion of non-retail business acres per town.4. __chas__: Charles River dummy variable (1 if tract bounds river; 0 otherwise).5. __nox__: nitrogen oxides concentration (parts per 10 million).6. __rm__: average number of rooms per dwelling.7. __age__: proportion of owner-occupied units built prior to 1940.8. __dis__: weighted mean of distances to five Boston employment centres.9. __rad__: index of accessibility to radial highways.10. __tax__: full-value property-tax rate per \$1000011. __ptratio__: pupil-teacher ratio by town.12. __black__: 1000(Bk - 0.63)^2 where Bk is the proportion of blacks by town.13. __lstat__: lower status of the population (percent).14. __medv__: median value of owner-occupied homes in $1000s. FactoidsThe prices in Boston across years are below.  If we had a historical dataset, an analysis could be done to account for the macro trends as well.The second graph shows the intuition we have with respect to prices in relation to crime rate.  It is expected that house prices will be lower in areas where crime rates are higher.The third figure is a chart showing how inflation may affect prices.  So, for deeper analysis and prediction, we may want to consider inflation.In this notebook, these factors are not considered.  They are here to demonstrate the need for deep domain analysis.In this notebook, we will use the dataset for Boston housing prices and predict the price based on numerous factors.<jupyter_code>from hana_ml import dataframe
from hana_ml.algorithms.pal import regression
import numpy as np
import matplotlib.pyplot as plt
import logging<jupyter_output><empty_output><jupyter_text>Load data
The data is loaded into 4 tables, for full, training, validation, and test sets:
BOSTON_HOUSING_PRICES
BOSTON_HOUSING_PRICES_TRAINING
BOSTON_HOUSING_PRICES_VALIDATION
BOSTON_HOUSING_PRICES_TEST

To do that, a connection is created and passed to the loader.

There is a config file, config/e2edata.ini 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.


[hana]
url=host.sjc.sap.corp
user=username
passwd=<PASSWORD>
port=3xx15

[bostonhousingdataset]
reload_data=true

 Define Datasets - Training, validation, and test sets
Data frames represent data in HANA and HANA-side queries on that data, so computation on large data sets in HANA can happen in HANA.  Trying to bring the entire data set into the client may be impractical or impossible for large data sets.

The original/full dataset is split into training, test and validation sets.  In the example below, they reside in different tables.<jupyter_code>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_boston_housing_data(connection_context, force=True)<jupyter_output><empty_output><jupyter_text>Simple ExplorationLet us look at the number of rows in the data set.<jupyter_code>print('Number of rows in full set: {}'.format(full_set.count()))
print('Number of rows in training set: {}'.format(training_set.count()))
print('Number of rows in validation set: {}'.format(validation_set.count()))
print('Number of rows in test set: {}'.format(test_set.count()))<jupyter_output><empty_output><jupyter_text>Let's look at the columns<jupyter_code>print(full_set.columns)<jupyter_output><empty_output><jupyter_text>Let's look at the data types<jupyter_code>full_set.dtypes()<jupyter_output><empty_output><jupyter_text>Set up the features and labels for the model<jupyter_code>features=['CRIM', 'ZN', 'INDUS', 'CHAS', 'NOX', 'RM', 'AGE', 'DIS', 'RAD', 'TAX', 'PTRATIO', 'BLACK', 'LSTAT']
label='MEDV'<jupyter_output><empty_output><jupyter_text>Create model using training dataThe first GLM we create will use the default settings of `family='gaussian'` and `link='identity'`. A GLM using these settings is equivalent to linear regression.  PreprocessingSAP HANA Predictive Analytics Library takes DOUBLE and INTEGER data types for most numeric types.  Since we have DECIMALs and TINYINTs in our data set, we cast them to the types required by PAL.<jupyter_code># Cast to correct types so PAL can consume it.
dfts = training_set.cast(['CRIM', "ZN", "INDUS", "NOX", "RM", "AGE", "DIS", "PTRATIO", "BLACK", "LSTAT", "MEDV"], "DOUBLE")
dfts = dfts.cast(["CHAS", "RAD", "TAX"], "INTEGER")
dfts = dfts.to_head("ID")
dfts.head(5).collect()<jupyter_output><empty_output><jupyter_text>Create the model<jupyter_code>linear_model = regression.GLM()
linear_model.fit(dfts, key='ID', features=features, label=label)<jupyter_output><empty_output><jupyter_text>Let's see how well this model does. We'll compute the R^2 score of its predictions on the test set.<jupyter_code>df_test = test_set.cast(['CRIM', "ZN", "INDUS", "NOX", "RM", "AGE", "DIS", "PTRATIO", "BLACK", "LSTAT", "MEDV"], "DOUBLE")
df_test = df_test.cast(["CHAS", "RAD", "TAX"], "INTEGER")
df_test = df_test.to_head("ID")
linear_model.score(df_test, key='ID', features=features, label=label)<jupyter_output><empty_output><jupyter_text>Let's try a few others. We'll experiment with gamma distributions and inverse or logarithmic link functions.<jupyter_code>gaussian_idlink = regression.GLM(family='gaussian', link='identity')
gamma_invlink = regression.GLM(family='gamma', link='inverse')
gaussian_invlink = regression.GLM(family='gaussian', link='inverse')
gamma_loglink = regression.GLM(family='gamma', link='log')
gaussian_loglink = regression.GLM(family='gaussian', link='log')
for model in [gaussian_idlink, gamma_invlink, gaussian_invlink, gamma_loglink, gaussian_loglink]:
    model.fit(dfts, key='ID', features=features, label=label)
    print(model.score(df_test, key='ID', features=features, label=label))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08864864864864865, 'fraction_numerical': 0.00945945945945946, 'mean_word_length': 5.878252788104089, 'pattern_counts': {'":': 0, '<': 42, '<?xml version=': 0, '>': 42, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '45460', 'license': 'Apache-2.0', 'n_tokens_mistral': 2251, 'n_tokens_neox': 2143, 'n_words': 864, 'path': 'Python-API/pal/notebooks/bostonHousingGLM.ipynb', 'repo_name': 'pbaumann76/hana-ml-samples', 'size': '7402'}