UseCases/Financial_Fraud_Detection_BYOM/Financial_Fraud_Detection_BYOM_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Financial Fraud Detection with Python and Vantage BYOM\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial'>In recent years we have seen a massive increase in fraud attempts, making fraud detection imperative for Banking and Financial Institutions. Despite countless efforts and human supervision, hundreds of millions of dollars are lost due to fraud. Fraud can happen using various methods, i.e., stolen credit cards, misleading accounting, phishing emails, etc. Due to small cases in significant populations, fraud detection has become more and more challenging. Fortunately, with Teradata Vantage’s Bring Your Own Model capabilities, data scientists can use their preferred language, tools and platform to develop models to identify this fraud. Even in large scale operations, users have the guarantee that Vantage can scale to their needs and reduce fraud.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>Business Values</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial'>\n",
    "    <li>Identification of financial fraud in multiple accounts</li>\n",
    "    <li>Pattern recognition of fraudulent versus normal transactions</li>\n",
    "    <li>Reduction of money lost due to recovering fraudulent charges</li>\n",
    "    <li>Improved customer satisfaction and reduction of customer churn</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>Why Vantage?</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>The ML and AI industry continues to innovate at an unprecedented rate.  Tools, technologies, and algorithms\t are being developed and improved in both the open source and commercial communities.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>Unfortunately, many of these techniques haven’t matured to the point where they are readily deployable to a stable, mature operational environment.  Furthermore, many open-source techniques rely on fragile, manual enabling technologies.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>ClearScape Analytics Bring Your Own Model capabilities allow organizations to leverage third party and open-source models for scoring inside the Vantage Platform; providing enterprise-class scalability and operational stability for any number of users, applications, or volume of data.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;\">\n",
    "<b style = 'font-size:20px;font-family:Arial'>1. Configuring the Environment</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial'><b>Installing some dependencies</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%capture\n",
    "# # '%%capture' suppresses the display of installation steps of the following packages\n",
    "# !pip install sklearn2pmml jdk4py --user"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial'><b>Note: </b><i>The above statements may need to be uncommented if you run the notebooks on a platform other than ClearScape Analytics Experience that does not have the libraries installed. If you uncomment those installs, be sure to restart the kernel after executing those lines to bring the installed libraries into memory. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>\n",
    "</div>\n",
    "<p style = 'font-size:16px;font-family:Arial'>Here, we import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import statements\n",
    "import os\n",
    "import getpass\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "from teradataml import *\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn2pmml.pipeline import PMMLPipeline\n",
    "from sklearn2pmml import sklearn2pmml\n",
    "from jdk4py import JAVA, JAVA_HOME, JAVA_VERSION\n",
    "\n",
    "configure.byom_install_location = 'mldb'\n",
    "configure.val_install_location = 'val'\n",
    "spacing_large = \" \"*95\n",
    "spacing_small = \" \"*12\n",
    "display.max_rows = 5\n",
    "\n",
    "# Set java path\n",
    "os.environ['PATH'] = os.environ['PATH'] + os.pathsep + str(JAVA_HOME)\n",
    "os.environ['PATH'] = os.environ['PATH'] + os.pathsep + str(JAVA)[:-5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;\">\n",
    "<b style = 'font-size:20px;font-family:Arial'>2. Connect to Vantage</b>\n",
    "<p style = 'font-size:16px;font-family:Arial'>We will be prompted to provide the password. We will enter the password, press the Enter key, and then use the down arrow to go to the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql(\"SET query_band='DEMO=Financial_Fraud_Detection_BYOM_Python.ipynb;' UPDATE FOR SESSION;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial'>We begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial'><b>Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial'>We have provided data for this demo on cloud storage. We have the option of either running the demo using foreign tables to access the data without using any storage on our environment or downloading the data to local storage, which may yield somewhat faster execution. However, we need to consider available storage. There are two statements in the following cell, and one is commented out. We may switch which mode we choose by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_GLM_Fraud_cloud');\"        # Takes 1 minute\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_GLM_Fraud_local');\"        # Takes 2 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial'>Optional step – We should execute the below step only if we want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;\">\n",
    "<b style = 'font-size:20px;font-family:Arial'>3. Data Exploration</b>\n",
    "<p style = 'font-size:16px;font-family:Arial'>We loaded the data from <a href = 'https://www.kaggle.com/code/georgepothur/4-financial-fraud-detection-xgboost/data'>https://www.kaggle.com/code/georgepothur/4-financial-fraud-detection-xgboost/data</a> into Vantage in a table named \"transaction_data\". We checked the data size and printed sample rows: 63k rows and 12 columns.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial'><b><i>*Please scroll down to the end of the notebook for detailed column descriptions of the dataset.</i></b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "txn_data = DataFrame(in_schema('DEMO_GLM_Fraud', 'transaction_data'))\n",
    "\n",
    "print(txn_data.shape)\n",
    "txn_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial'>In this simulated scenario, deceptive agents engage in transactions with the objective of taking control of customers' accounts, transferring funds to another account, and ultimately cashing out for profit.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;\">\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>3.1 How many fraudulent transactions do we have in our dataset?</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# There are 92 fraud transactions i.e. 0.14% of fraud transactions in the dataset.\n",
    "print(\"No of fraud transactions: %d\\nPercentage of fraud transactions: %.2f%%\"%(\n",
    "    txn_data.loc[txn_data.isFraud == 1].shape[0],\n",
    "    txn_data.loc[txn_data.isFraud == 1].shape[0]/txn_data.shape[0]*100)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;\">\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>3.2 How many transactions do we have group by transaction type?</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter data for fraud transactions and group by 'type'\n",
    "transactions_by_type = txn_data.groupby('type').count().get(['type','count_txn_id'])\n",
    "\n",
    "\n",
    "# Sort by 'count_step' column in descending order\n",
    "transactions_by_type = transactions_by_type.sort('count_txn_id', ascending = False)\n",
    "\n",
    "transactions_by_type = transactions_by_type.assign(\n",
    "    type_int = case([\n",
    "        (transactions_by_type.type == 'CASH_IN', 0),\n",
    "        (transactions_by_type.type == 'CASH_OUT', 1),\n",
    "        (transactions_by_type.type == 'DEBIT', 2),\n",
    "        (transactions_by_type.type == 'PAYMENT ', 3),\n",
    "        (transactions_by_type.type == 'TRANSFER', 4),\n",
    "    ])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions_by_type.plot(\n",
    "    x = transactions_by_type.type_int,\n",
    "    y = transactions_by_type.count_txn_id,\n",
    "    kind = 'bar',\n",
    "    legend = ['Count by Type'],\n",
    "    ylabel = 'Count of Transactions',\n",
    "    xlabel = spacing_small.join(sorted(list(transactions_by_type[['type']].get_values().flatten()))),\n",
    "    title = \"Number of Transactions per Transaction Type\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;\">\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>3.3 How many fraudulent transactions do we have group by transaction type?</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Filter data for fraud transactions and group by 'type'\n",
    "fraud_transactions_by_type = txn_data.loc[txn_data.isFraud == 1].groupby('type').count().get(['type','count_txn_id'])\n",
    "\n",
    "# Sort by 'count_step' column in descending order\n",
    "fraud_transactions_by_type = fraud_transactions_by_type.sort('count_txn_id', ascending = False)\n",
    "\n",
    "fraud_transactions_by_type = fraud_transactions_by_type.assign(\n",
    "    total_fraud = txn_data.loc[txn_data.isFraud == 1].shape[0],\n",
    "    type_int = case([(fraud_transactions_by_type.type == 'TRANSFER', 0)], else_ = 1)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "fraud_transactions_by_type.plot(\n",
    "    x = fraud_transactions_by_type.type_int,\n",
    "    y = [fraud_transactions_by_type.total_fraud, fraud_transactions_by_type.count_txn_id],\n",
    "    kind = 'bar',\n",
    "    figsize = (800, 500),\n",
    "    legend = ['Total Fraud', 'Count by Type'],\n",
    "    ylabel = 'Count of Fraud Transactions',\n",
    "    xlabel = 'TRANSFER' + spacing_large + 'CASH_OUT',\n",
    "    title = \"Number of Fraud Transactions by Transaction Type\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial'>From the above result, we can see that out of the 92 fraud transactions, 47 are from transaction type \"TRANSFER\" and 45 are from \"CASH_OUT\".</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;\">\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>3.4 What percentage of fraudulent transactions do we have where transaction amount is equal to old balance in the origin account?</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>This might be the case where the fraudster emptied the account of the victim.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"No of cleanout fraud transactions: %d\\nPercentage of cleanout fraud transactions: %.2f%%\"%(\n",
    "    txn_data.loc[txn_data['amount'] == txn_data.oldbalanceOrig].loc[txn_data['isFraud'] == 1].shape[0],\n",
    "    txn_data.loc[txn_data['amount'] == txn_data.oldbalanceOrig].loc[txn_data['isFraud'] == 1].shape[0] / txn_data.loc[txn_data.isFraud == 1].shape[0]*100)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial'>From the above result, we can see that out of 92 Fraud transactions, the amount involved in 90 fraud transactions was equal to the total balance in the account. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;\">\n",
    "<p style = 'font-size:16px;font-family:Arial'><b>Below are some insights about the dataset:</b></p>\n",
    "<ol style = 'font-size:16px;font-family:Arial'>\n",
    "    <li>We have 92 fraud transactions, which account for 0.14% of the dataset.</li>\n",
    "    <li>Out of these 92 fraud transactions, 47 are of type TRANSFER, and 45 are of type CASH_OUT.</li>\n",
    "    <li>Approximately 97.83% of our fraud transactions have a transaction amount equal to oldbalanceOrig, indicating account cleanout.</li>\n",
    "    <li>About 71.74% of our fraud transactions have the recipient's old balance as zero.</li>\n",
    "    <li>The isFlaggedFraud indicator is correct only two times among our 92 fraud transactions.</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;\">\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>3.5 Univariate statistics</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>The describe funtion computes the count, mean, std, min, percentiles, and max for numeric columns.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "txn_data.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;\">\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>3.6 Checking for Null Values</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial'>The ColumnSummary() function can be used to take a quick look at the columns, their datatypes, and summary of NULLs/non-NULLs for a given table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "colsum = ColumnSummary(\n",
    "    data  = txn_data,\n",
    "    target_columns = [':']\n",
    ")\n",
    "colsum.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;\">\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>3.7 Checking for Outliers</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial'>The OutlierFilterFit() function calculates the lower percentile, upper percentile, count of rows and median for all the \"target_columns\" provided by the user. These metrics for each column help the function OutlierTransform() detect outliers in data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fit_object = OutlierFilterFit(\n",
    "    data = txn_data,\n",
    "    target_columns = ['amount','newbalanceOrig', 'oldbalanceDest','newbalanceDest','oldbalanceOrig']\n",
    ")\n",
    "\n",
    "res = fit_object.transform(data = txn_data).result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"Rows before removing outliers: {txn_data.shape[0]}\\n\\\n",
    "Rows after removing outliers: {res.shape[0]}\\n\\\n",
    "Total outliers: {txn_data.shape[0] - res.shape[0]}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "outliers = td_minus([txn_data, res])\n",
    "outliers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;\">\n",
    "<b style = 'font-size:20px;font-family:Arial'>4. Data Preparation</b>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial'><b>We'll perform the following steps:</b></p>\n",
    "<ul style='font-size:16px;font-family:Arial'>\n",
    "    <li>We will one-hot encode the categorical \"type\" column.</li>\n",
    "    <li>We will perform feature scaling using ScaleFit and ScaleTransform on numerical columns.</li>\n",
    "    <li>We will split the data into training and testing datasets (80:20 split).</li>\n",
    "</ul>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial'>We perform feature scaling during data pre-processing to handle highly varying magnitudes, values, or units. If feature scaling is not done, then a machine learning algorithm tends to weigh greater values higher and consider smaller values as lower ones, regardless of the unit of the values.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;\">\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>4.1 Drop redundant columns</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial'>We don't need nameDest, nameOrigin, and isFlaggedFraud for model training as they do not impact the outcome. We have txn_id to uniquely identify each transaction.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "txn_data = txn_data.drop(['nameDest', 'nameOrig', 'isFlaggedFraud'], axis = 1)\n",
    "txn_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;\">\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>4.2 One-hot encoding</b></p>\n",
    "<p style='font-size:16px;font-family:Arial'>\n",
    "Here, we are one-hot encoding the \"type\" column. We find one-hot encoding necessary in many cases to represent categorical variables as binary values, enable numerical processing, ensure feature independence, handle non-numeric data, and improve the performance and interpretability of our machine learning models.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "txn_type_encoder = OneHotEncoder(\n",
    "    values = [\"CASH_IN\", \"CASH_OUT\", \"DEBIT\", \"PAYMENT\", \"TRANSFER\"],\n",
    "    columns = \"type\"\n",
    ")\n",
    "\n",
    "retain = Retain(\n",
    "    columns = ['step', 'amount','newbalanceOrig','oldbalanceDest','newbalanceDest','oldbalanceOrig', 'isFraud']\n",
    ")\n",
    "\n",
    "obj = valib.Transform(\n",
    "    data = txn_data,\n",
    "    one_hot_encode = txn_type_encoder,\n",
    "    retain = retain,\n",
    "    index_columns = 'txn_id'\n",
    ")\n",
    "txn_trans = obj.result\n",
    "txn_trans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;\">\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>4.3 Feature Scaling</b></p>\n",
    "<p style='font-size:16px;font-family:Arial'>Here, we are using ScaleFit and ScaleTransform for scaling our numerical columns using Standard Deviation as the scale method.\n",
    "<br><br>\n",
    "Feature scaling is important in our machine learning process to avoid numerical instability, ensure fair comparison of features, improve our model performance, enhance interpretability, and handle distance-based algorithms.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ScaleFit, ScaleTransform\n",
    "\n",
    "sf_fit = ScaleFit(\n",
    "    data = txn_trans,\n",
    "    scale_method = 'STD',\n",
    "    target_columns = ['step', 'amount','newbalanceOrig', 'oldbalanceDest','newbalanceDest','oldbalanceOrig'])\n",
    "\n",
    "res = sf_fit.transform(\n",
    "    data = txn_trans,\n",
    "    accumulate = [\"txn_id\", \"isFraud\", 'CASH_IN_type', 'CASH_OUT_type',\n",
    "                  'DEBIT_type', 'PAYMENT_type', 'TRANSFER_type']).result\n",
    "\n",
    "# Create the source data table in the database\n",
    "copy_to_sql(res, table_name = 'clean_data', if_exists = 'replace')\n",
    "\n",
    "res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial'>The above output shows that we have transformed the data into a scaled dataset.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;\">\n",
    "<b style = 'font-size:20px;font-family:Arial'>5. Create training and testing datasets in Vantage</b>\n",
    "<p style = 'font-size:16px;font-family:Arial'>We'll create two datasets for training and testing in the ratio of 80:20.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "TrainTestSplit_out = TrainTestSplit(\n",
    "    data = res,\n",
    "    id_column = \"txn_id\",\n",
    "    train_size = 0.80,\n",
    "    test_size = 0.20,\n",
    "    seed = 25\n",
    ")\n",
    "\n",
    "df_train = TrainTestSplit_out.result[TrainTestSplit_out.result['TD_IsTrainRow'] == 1].drop(['TD_IsTrainRow'], axis = 1)\n",
    "df_test = TrainTestSplit_out.result[TrainTestSplit_out.result['TD_IsTrainRow'] == 0].drop(['TD_IsTrainRow'], axis = 1)\n",
    "\n",
    "print(\"Training Set = \" + str(df_train.shape[0]) + \". Testing Set = \" + str(df_test.shape[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(df_train, table_name = 'clean_data_train', if_exists = 'replace')\n",
    "copy_to_sql(df_test, table_name = 'clean_data_test', if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial'>The above output shows that we have transformed the data into a scaled dataset. Scaling our data makes it easy for our model to learn and understand the problem.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;\">\n",
    "<b style = 'font-size:20px;font-family:Arial'>5. Create a PMML pipeline locally using sklearn and train it</b>\n",
    "<p style = 'font-size:16px;font-family:Arial'>Here, we will use Logistic Regression as our classifier model in the PMML pipeline. We leverage the Predictive Model Markup Language (PMML), which is an XML-based language that provides a way for us to define machine learning, statistical, and data mining models and to share models between PMML-compliant applications.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial'><b>5.1 Create a local pandas dataframe.</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial'>We export data outside Vantage and train the model using sklearn. We do this to simulate model training outside Vantage. Later, we save the trained model, which is in PMML format, into Vantage, and we perform in-database scoring at scale.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_data_train_df = df_train.to_pandas(all_rows = True)\n",
    "clean_data_train_df.reset_index(inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial'><b>5.2 Train the Pipeline and save the model locally</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial'>We would save the model in a file called <b>mm_fraud_glm_model.pmml</b>.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "y_train = clean_data_train_df[['isFraud']].astype(int)\n",
    "X_train = clean_data_train_df[['step','amount','newbalanceOrig','oldbalanceDest','newbalanceDest','oldbalanceOrig']].astype(float)\n",
    "\n",
    "\n",
    "pipeline = PMMLPipeline([\n",
    "     (\"classifier\", LogisticRegression())\n",
    "     ])\n",
    "pipeline.fit(X_train, y_train)\n",
    "\n",
    "sklearn2pmml(pipeline, \"mm_fraud_glm_model.pmml\", with_repr = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;\">\n",
    "<b style = 'font-size:20px;font-family:Arial'>6. Load trained PMML Model into Vantage</b>\n",
    "<p style = 'font-size:16px;font-family:Arial'>We pass the local PMML file to the save_byom function. This function loads the <b>mm_fraud_glm_model.pmml</b> file into Vantage in a table called <b>mm_glm</b>. If there is already a model with the same name, we will delete the previous one, and a new one will be loaded.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the PMML file into Vantage\n",
    "try:\n",
    "    res = save_byom(model_id = 'mm_glm1', model_file = 'mm_fraud_glm_model.pmml', table_name = 'mm_glm')\n",
    "\n",
    "except Exception as e:\n",
    "    # if our model exists, delete and rewrite\n",
    "    if str(e.args).find('TDML_2200') >= 1:\n",
    "        res = delete_byom(model_id = 'mm_glm1', table_name = 'mm_glm')\n",
    "        res = save_byom(model_id = 'mm_glm1', model_file = 'mm_fraud_glm_model.pmml', table_name = 'mm_glm')\n",
    "        pass\n",
    "    else:\n",
    "        raise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DataFrame('mm_glm')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;\">\n",
    "<b style = 'font-size:20px;font-family:Arial'>7. Score the model directly in Vantage</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>We will use PMMLPredict to score the model on data residing in Vantage without moving data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "model_tdf = retrieve_byom(\"mm_glm1\", table_name = 'mm_glm')\n",
    "\n",
    "# Run the PMMLPredict function in Vantage\n",
    "result = PMMLPredict(\n",
    "            modeldata = model_tdf,\n",
    "            newdata = df_test,\n",
    "            accumulate = ['step','amount','newbalanceOrig','oldbalanceDest','newbalanceDest','oldbalanceOrig', 'isFraud'],\n",
    "            overwrite_cached_models = '*',\n",
    "            model_output_fields = ['probability(1)', 'probability(0)']\n",
    "            )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style='font-size:16px;font-family:Arial'>In our output above, we show probability(1), indicating a transaction being fraud, and probability(0), indicating a transaction being genuine.</p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial'>We observe that the value in the prediction field of the result column in json_report appears only if we have a prediction field in the result column. The prediction output column could be empty if the scoring performed with a classification model does not return a predicted value.\n",
    "    <br>\n",
    "    <br>\n",
    "Read more about it <a href = 'https://docs.teradata.com/search/all?query=PMMLPredict+Output&content-lang=en-US'>here</a></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;\">\n",
    "<b style = 'font-size:20px;font-family:Arial'>8. Visualize the results (ROC curve and AUC)</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial'>We create the ROC curve, which is a graph between TPR (True Positive Rate) and FPR (False Positive Rate). We use the area under the ROC curve as a metric to evaluate how well our model can distinguish between positive and negative classes. A higher AUC indicates better performance in distinguishing between the positive and negative categories. We generally consider an AUC above 0.75 as decent.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ROC\n",
    "\n",
    "roc_out = ROC(probability_column='\"probability(1)\"',\n",
    "              observation_column=\"isFraud\",\n",
    "              positive_class=\"1\",\n",
    "              data=result.result,\n",
    "              num_thresholds = 300)\n",
    "\n",
    "roc_out.output_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "roc_out.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assigning new index column\n",
    "roc_out.result = roc_out.result.assign(row = 1)\n",
    "# Changing the index label.\n",
    "roc_out.result._index_label = [\"row\"]\n",
    "auc = roc_out.result.get_values()[0][0]\n",
    "\n",
    "figure = Figure(width=500, height=400, heading=\"Receiver Operating Characteristic (ROC) Curve\")\n",
    "\n",
    "plot = roc_out.output_data.plot(\n",
    "    x=roc_out.output_data.fpr,\n",
    "    y=[roc_out.output_data.tpr, roc_out.output_data.fpr],\n",
    "    xlabel='False Positive Rate',\n",
    "    ylabel='True Positive Rate',\n",
    "    color='carolina blue',\n",
    "    figure=figure,\n",
    "    legend=[f'Log-Reg AUC = {round(auc, 4)}', 'AUC Baseline'],\n",
    "    legend_style='lower right',\n",
    "    grid_linestyle='--',\n",
    "    grid_linewidth=0.5,\n",
    "    linestyle = ['-', '--']\n",
    ")\n",
    "\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial'>Looking at the above ROC Curve, we can confidently say that our model has performed well on testing data. The AUC value is way above 0.75 and resonates with our understanding that the model is performing well.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>Conclusion</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial'>In this demonstration, we have illustrated a simplified - but complete - overview of how we can improve a typical machine learning workflow using Vantage in conjunction with open-source tools and techniques. This combination allows us to leverage open-source innovation with Vantage's operational scale, power, and stability.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;\">\n",
    "<b style = 'font-size:20px;font-family:Arial'>9. Cleanup</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial'>We need to clean up our work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_drop_table(table_name = 'mm_glm')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial'> <b>Databases and Tables </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial'>We will use the following code to clean up tables and databases created for this demonstration.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('demo_glm_fraud');\"        # Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;\">\n",
    "\n",
    "<b style = 'font-size:20px;font-family:Arial'>Required Materials</b>\n",
    "<p style = 'font-size:16px;font-family:Arial'>Let’s look at the elements we have available for reference for this notebook:</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>Filters:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial'>\n",
    "    <li><b>Industry:</b> Finance</li>\n",
    "    <li><b>Functionality:</b> Machine Learning</li>\n",
    "    <li><b>Use Case:</b> Fraud Detection</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>Related Resources:</b></p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial'>\n",
    "    <li><a href='https://www.teradata.com/Blogs/Fraud-Busting-AI'>Fraud-Busting-AI</a></li>\n",
    "    <li><a href='https://www.teradata.com/Industries/Financial-Services'>Financial Services</a></li>\n",
    "    <li><a href='https://www.teradata.com/Resources/Datasheets/Move-from-Detection-to-Prevention-and-Outsmart-Fraudsters'>Move from Detection to Prevention and Outsmart Tech-Savvy Fraudsters</a></li>\n",
    "</ul>\n",
    "\n",
    "<b style = 'font-size:20px;font-family:Arial'>Dataset:</b>\n",
    "\n",
    "- `txn_id`: transaction id\n",
    "- `step`: maps a unit of time in the real world. In this case 1 step is 1 hour of time. Total steps 744 (31 days simulation).\n",
    "- `type`: CASH-IN, CASH-OUT, DEBIT, PAYMENT and TRANSFER\n",
    "- `amount`: amount of the transaction in local currency\n",
    "- `nameOrig`: customer who started the transaction\n",
    "- `oldbalanceOrig`: customer's balance before the transaction\n",
    "- `newbalanceOrig`: customer's balance after the transaction\n",
    "- `nameDest`: customer who is the recipient of the transaction\n",
    "- `oldbalanceDest`: recipient's balance before the transaction\n",
    "- `newbalanceDest`: recipient's balance after the transaction\n",
    "- `isFraud`: identifies a fraudulent transaction (1) and non fraudulent (0)\n",
    "- `isFlaggedFraud`: flags illegal attempts to transfer more than 200,000 in a single transaction\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial'><b>Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial'>\n",
    "    <li>Uses a dataset and feature discovery methods outlined here: <a href = 'https://www.kaggle.com/georgepothur/4-financial-fraud-detection-xgboost/notebook'>https://www.kaggle.com/georgepothur/4-financial-fraud-detection-xgboost/notebook</a></li>\n",
    "    <li>Teradataml Python reference: <a href = 'https://docs.teradata.com/search/all?query=Python+Package+User+Guide&content-lang=en-US'>here</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; border-bottom:3px solid #91A0Ab\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}