<jupyter_start><jupyter_text>Estimate car price - Apply saved model <NAME>This 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-tutoria…hana-with-python/The purpose of this notebook is to apply a trained Machine Learning model to predict a car's price.  Steps in this notebook-  Connect to SAP HANA-  Load the trained model from SAP HANA-  Create a SAP HANA DataFrame which points to the data that is to be used for a prediction-  Apply the model that has been loaded on the data and predict the prices 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(key = '<KEY>')<jupyter_output><empty_output><jupyter_text>Load modelLoad the trained model from SAP HANA.<jupyter_code>import hana_ml as hana_ml
from hana_ml.algorithms.pal import trees
df_model_saved = hana_ml.dataframe.DataFrame(connection_context = conn,
                                             select_statement = 'select * from ML.USEDCARPRICES_MODEL_REGTREE')
tree_reg_saved = trees.DecisionTreeRegressor(conn_context = conn, algorithm = 'cart')
tree_reg_saved.model_ = df_model_saved.select('ROW_INDEX', 'MODEL_CONTENT')<jupyter_output><empty_output><jupyter_text>Predict price of "new" carsCreate a SAP HANA DataFrame, which points to the table that contains the cars for which the prices are to be predicted. No data is extracted.<jupyter_code>df_pushdown_new = conn.table(table = 'USEDCARPRICES_TOPREDICT', schema = 'ML')<jupyter_output><empty_output><jupyter_text>Peak at the cars that are to be predicted.<jupyter_code>df_pushdown_new.head(3).collect()<jupyter_output><empty_output><jupyter_text>Apply the trained model on the cars to estimate the price.<jupyter_code>features = ['VEHICLETYPE', 'YEAR', 'MODEL', 'HP', 'FUELTYPE', 'KILOMETER', 'GEARBOX']
df_pushdown_predict = tree_reg_saved.predict(data = df_pushdown_new,
                                             features = features, 
                                             key = 'CAR_ID').select('CAR_ID', 'SCORE')
df_predict = df_pushdown_predict.collect()
df_predict<jupyter_output><empty_output><jupyter_text>Compare the two prices in a bar chart.<jupyter_code>%matplotlib inline
import pandas as pd
df_predict['SCORE'] = pd.to_numeric(df_predict['SCORE'])
df_predict['CAR_ID'] = df_predict['CAR_ID'].astype(str)
df_predict.plot.bar(x = 'CAR_ID', y = 'SCORE', title = 'Estimated Price');<jupyter_output><empty_output><jupyter_text>Remember that both cars are identical apart from their mileage. The car that has driven an additional 100.000 kilometers is worth over 4.000 Euros less. We are satisfied with the preditions and persist them in a SAP HANA table.<jupyter_code>df_pushdown_result = df_pushdown_predict.save(where = ('ML', 'USEDCARPRICES_PREDICTED'), 
                                              table_type = 'COLUMN', 
                                              force = True)<jupyter_output><empty_output><jupyter_text>Close connection<jupyter_code>conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08243004789513486, 'fraction_numerical': 0.017897655659188303, 'mean_word_length': 4.369418132611638, 'pattern_counts': {'":': 0, '<': 35, '<?xml version=': 0, '>': 35, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 11, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '63201', 'license': 'BSD-Source-Code', 'n_tokens_mistral': 1276, 'n_tokens_neox': 1199, 'n_words': 364, 'path': 'Python-API/usecase-examples/estimate-car-price/30 Apply saved model.ipynb', 'repo_name': 'rschop/hana-ml-samples', 'size': '3979'}