<jupyter_start><jupyter_text>Python HANA ML API Train a model and show the confusion matrix.   Train the model  Create an HANA Dataframe for the training data<jupyter_code>import pandas as pd 
# Connect using the HANA secure user store
from hana_ml import dataframe as hd
conn = hd.ConnectionContext(userkey='MLMDA_KEY')
# Get Training Data
sql_cmd = 'SELECT * FROM "APL_SAMPLES"."AUTO_CLAIMS_FRAUD" ORDER BY CLAIM_ID'
training_data = hd.DataFrame(conn, sql_cmd)<jupyter_output><empty_output><jupyter_text>Put a subset of the data in a Pandas Dataframe and display it<jupyter_code>training_data.head(5).collect()<jupyter_output><empty_output><jupyter_text>Build a Classification model with APL Ridge Regression<jupyter_code># Create the model
from hana_ml.algorithms.apl.classification import AutoClassifier
model = AutoClassifier(conn_context=conn)
# Train the model
model.fit(training_data, label='IS_FRAUD', key='CLAIM_ID')<jupyter_output><empty_output><jupyter_text>Confusion Matrix  Define Functions<jupyter_code>indicators_table_name = None
for table_name in model._artifact_tables:
    if table_name.startswith('#INDICATORS_'):
        indicators_table_name = table_name
def create_artifact_table(conn, table_name, table_spec):
    conn = model.conn_context.connection
    cursor = conn.cursor()
    try:
        cursor.execute(f'drop table {table_name}')
    except:
        pass
    cursor.execute(f'create local temporary table {table_name} {table_spec}')
def get_confusion_matrix():
    conn = model.conn_context.connection
    cursor = conn.cursor()

    model_table_name = model.model_table_.name  # the temp table where the model is saved

    # --- Create temp tables for input / output
    create_artifact_table(conn=conn,
                         table_name='#FUNC_HEADER',
                         table_spec='(KEY NVARCHAR(50), VALUE NVARCHAR(255))')

    create_artifact_table(conn=conn,
                         table_name='#OPERATION_CONFIG',
                         table_spec='(KEY NVARCHAR(1000), VALUE NCLOB, CONTEXT NVARCHAR(100))')

    create_artifact_table(conn=conn,
                         table_name='#SUMMARY',
                         table_spec='(OID NVARCHAR(50), KEY NVARCHAR(100), VALUE NVARCHAR(100))')

    create_artifact_table(conn=conn,
                         table_name='#CONF_MATRIX',
                         table_spec='(OID VARCHAR(50),VARIABLE VARCHAR(255),TARGET VARCHAR(255),KEY VARCHAR(100),'
                          'VALUE NCLOB,DETAIL NCLOB)')

    # Call APL
    sql = 'call "SAP_PA_APL"."sap.pa.apl.base::COMPUTE_CONFUSION_MATRIX"(#FUNC_HEADER, #OPERATION_CONFIG,  {indicators_input}, #CONF_MATRIX) with overview'
    sql = sql.format(indicators_input=indicators_table_name)
    cursor.execute(sql)<jupyter_output><empty_output><jupyter_text>Calling COMPUTE_CONFUSION_MATRIX<jupyter_code>get_confusion_matrix()<jupyter_output><empty_output><jupyter_text>Put indicators data in a Pandas Dataframe<jupyter_code>sql_cmd = 'SELECT * FROM #CONF_MATRIX'
hf = hd.DataFrame(conn, sql_cmd)
indicators_df = hf.collect()
indicators_df = indicators_df[['KEY','VALUE']]<jupyter_output><empty_output><jupyter_text>Matrix In Absolute Values<jupyter_code>df = indicators_df[indicators_df.KEY.isin(['True_Positive', 'True_Negative', 'False_Negative', 'False_Positive'])].copy()
df['VALUE'] = df['VALUE'].astype(int)
df['KEY'] = df['KEY'].str.replace('True_Positive', 'Yes Yes')
df['KEY'] = df['KEY'].str.replace('True_Negative', 'No No')
df['KEY'] = df['KEY'].str.replace('False_Negative', 'Yes No')
df['KEY'] = df['KEY'].str.replace('False_Positive', 'No Yes')
df[['Actual', 'Predicted']] = df.KEY.str.split(expand=True)
df.drop('KEY', axis=1, inplace=True)
df.columns = ['Nb of Cases', 'Actual', 'Predicted']
pd.pivot_table(df,index=["Actual"], values=["Nb of Cases"], columns=["Predicted"])<jupyter_output><empty_output><jupyter_text>Matrix In Percentage<jupyter_code>df = indicators_df[indicators_df.KEY.isin(['Percent_True_Positive', 'Percent_True_Negative', 'Percent_False_Negative', 'Percent_False_Positive'])].copy()
df['VALUE'] = df['VALUE'].astype(float).round(2)
df['KEY'] = df['KEY'].str.replace('Percent_True_Positive', 'Yes Yes')
df['KEY'] = df['KEY'].str.replace('Percent_True_Negative', 'No No')
df['KEY'] = df['KEY'].str.replace('Percent_False_Negative', 'Yes No')
df['KEY'] = df['KEY'].str.replace('Percent_False_Positive', 'No Yes')
df[['Actual', 'Predicted']] = df.KEY.str.split(expand=True)
df.drop('KEY', axis=1, inplace=True)
df.columns = ['% Cases', 'Actual', 'Predicted']
pd.pivot_table(df,index=["Actual"], values=["% Cases"], columns=["Predicted"])<jupyter_output><empty_output><jupyter_text>Confusion Matrix Indicators<jupyter_code>df = indicators_df[indicators_df.KEY.isin(['Accuracy', 'Sensitivity', 'Specificity', 'Precision', 'F1_Score'])].copy()
df['VALUE'] = df['VALUE'].astype(float).round(4)
df.columns = ['Indicator', 'Value']
df.style.hide_index()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14533360209635154, 'fraction_numerical': 0.007458173755291272, 'mean_word_length': 5.546174142480211, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 37, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '291073', 'license': 'Apache-2.0', 'n_tokens_mistral': 1758, 'n_tokens_neox': 1666, 'n_words': 297, 'path': 'Python-API/apl/notebooks/HANA_ML_APL_CONFUSION_MATRIX.ipynb', 'repo_name': 'SAP-samples/hana-ml-samples', 'size': '4961'}