<jupyter_start><jupyter_text>Estimate car price - Introduction to Python wrapper for SAP HANAThis notebook is part of a Machine Learning project that is described and available to download on https://blogs.sap.com/2019/11/05/hands-on-tutorial-machine-learning-push-down-to-sap-hana-with-python/The purpose of this notebook is for you to become familiar with the most important steps to train a Machine Learning model in SAP HANA through Python. The following notebooks contain a more realistic example.  Steps in this notebook-  Connect to SAP HANA-  Create a SAP HANA DataFrame which points to the data-  Take a brief look at the data-  Deal with missing values by ignoring all rows that are not complete-  Train a Descision Tree in SAP HANA to estimate the price of a vehicle-  Calculate the model's quality on the training data Documentation-  SAP HANA Python Client API for Machine Learning Algorithms:      https://help.sap.com/doc/0172e3957b5946da85d3fde85ee8f33d/latest/en-US/html/hana_ml.html-  SAP HANA Predictive Analysis Library (PAL):     https://help.sap.com/viewer/2cfbc5cf2bc14f028cfbe2a2bba60a50/latest/en-US/f652a8186a144e929a1ade7a3cb7abe8.html-  Dataset: https://www.kaggle.com/bozungu/ebay-used-car-sales-data  Create a SAP HANA DataFrame, which points to the training dataInstantiate a connecton object to SAP HANA.-  For simplicity, to help you get started, these values are hardcoded here. -  We recommend keeping these credentials in the Secure User Store of the SAP HANA Client. Retrieving the credentials from the Secure User Store prevents having to specify these credentials in clear text. See the blog on the SAP Commmunity to which these notebooks belong, for steps on how to use that Secure User Store.<jupyter_code>import hana_ml.dataframe as dataframe
conn = dataframe.ConnectionContext(userkey = '<KEY>', encrypt = 'true', sslValidateCertificate = 'false')<jupyter_output><empty_output><jupyter_text>Create the SAP HANA DataFrame, which points to the table with historic sales. No data is extracted.<jupyter_code># Create the HANA dataframe in the structure of the specified table
df_remote = conn.table(table = 'USEDCARPRICES')<jupyter_output><empty_output><jupyter_text>Peek at the data and retrieve a small number of rowsNotice how no data is displayed, when calling the HANA DataFrame. You will only see the object type: hana_ml.dataframe.DataFrame. At the top of this page you find a link to the SAP HANA Python Client API documentation, where you find all details about the hana_ml package.<jupyter_code>df_remote<jupyter_output><empty_output><jupyter_text>To retrieve data into Python, you need to call the collect() function on the DataFrame object. In order to reduce the number of rows that are retrieved, use the head() function beforehand.<jupyter_code>df_remote.head(3).collect()<jupyter_output><empty_output><jupyter_text>Descriptive statistics Display most important data column statistics. All values were calculated within SAP HANA. Notice how some columns have null values. These are rows with missing values.<jupyter_code>df_remote.describe().collect()<jupyter_output><empty_output><jupyter_text>Plot number of vehicles by modelThe hana_ml package can also create a number of plots, whose underlying data was calculated within SAP HANA. For more specific requirements you can also push down further calculations to SAP HANA and retrieve the result with the collect() function as pandas data frame to create your own plot. Now display the number of vehicles by model.<jupyter_code>%matplotlib inline
from hana_ml.visualizers.eda import EDAVisualizer
import matplotlib.pyplot as plt
f = plt.figure()
ax1 = f.add_subplot(111) # 111 refers to 1x1 grid, 1st subplot
eda = EDAVisualizer(ax1)
ax, bar_data = eda.bar_plot(data = df_remote,
                            column = 'MODEL', 
                            aggregation = {'MODEL':'count'},
                            title = 'Number of vehicles by model')<jupyter_output><empty_output><jupyter_text>Drop rows with missing values Many algorithms require the data to be complete without missing values. Descriptive statistics above showed that various columns miss data. There are various options to deal with such missing values, ie to impute or remove the row or column. In the following notebook we will impute. In this introductory example we remove the rows with missing values from the SAP HANA Data Frame.The rows are not removed from the physical table. They are dropped from the logical construct of the SAP HANA Data Frame. Hence any process or application that might be using the underlying data is not affected.<jupyter_code>df_remote = df_remote.dropna()<jupyter_output><empty_output><jupyter_text>The SAP HANA Data Frame's SELECT statement shows how the rows with missing values were filtered out.<jupyter_code>df_remote.select_statement<jupyter_output><empty_output><jupyter_text>Train decision tree regressionWe will train a decision tree to estimate the price. The algorithm does not support the column type INT for the target. Hence convert the PRICE column to type DOUBLE in the SAP HANA Data Frame. The data type is not changed in the physical table.<jupyter_code>df_remote = df_remote.cast('PRICE', 'DOUBLE')<jupyter_output><empty_output><jupyter_text>Train the decision tree with some hardcoded parameters. In the following notebooks we will search for parameters that lead to a stronger models. This notebook is just introducing the basic concept of training Machine Learning models within SAP HANA.<jupyter_code>from hana_ml.algorithms.pal import trees
tree_reg = trees.DecisionTreeRegressor(algorithm = 'cart',
                                       min_records_of_parent = 10,
                                       min_records_of_leaf = 2,
                                       thread_ratio = 0.4,
                                       split_threshold = 1e-5,
                                       model_format = 'json',
                                       output_rules = True)

# Specify the tree's predictors
features = ['GEARBOX', 'VEHICLETYPE', 'YEAR', 'MODEL', 'HP', 'FUELTYPE', 'KILOMETER']

# Train the tree
tree_reg.fit(data = df_remote, 
             key = 'CAR_ID', 
             label = 'PRICE', 
             features = features)<jupyter_output><empty_output><jupyter_text>Once the above cell has been executed, a model has been trained. To see the DecisionTreeRegressor function's signature move the cursor into the round brackets of the function, ie place it after conn_context=conn and press SHIFT+TAB. The signature will be shown as tooltip.  Quality metricCalculate the model's performance on the training data. In the following notebooks, the data will be split for training and testing, leading to a more meaninful quality indicators. We calculate R^2, the coefficient of determination. https://en.wikipedia.org/wiki/Coefficient_of_determination<jupyter_code>print('R^2 on training data: ' + str(round(tree_reg.score(data = df_remote, 
                                                          key = 'CAR_ID'), 3)))<jupyter_output>R^2 on training data: 0.211
{'fraction_non_alphanumeric': 0.05961754780652419, 'fraction_numerical': 0.012232845894263216, 'mean_word_length': 3.953342618384401, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 45, 'https://': 5, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 9, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '23475', 'license': 'Apache-2.0', 'n_tokens_mistral': 2002, 'n_tokens_neox': 1869, 'n_words': 813, 'path': 'Python-API/usecase-examples/estimate-car-price/05 Introduction.ipynb', 'repo_name': 'pbaumann76/hana-ml-samples', 'size': '7115'}