UseCases/Financial_Fraud_Detection_InDB/Financial_Fraud_Detection_InDB_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 TeradataML\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;color:#00233C'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    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. \n",
    "    <br>\n",
    "    <br>\n",
    "    With ClearScape Analytics, 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;color:#00233C'><b>Business Values</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\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;color:#00233C'><b>Why Vantage?</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To maximize the business value of advanced analytic techniques including Machine Learning and Artificial Intelligence, it is estimated that organizations must scale their model development and deployment pipelines to 100s or 1000s of times greater amounts of data, models, or both.\n",
    "    <br>\n",
    "    <br>\n",
    "    ClearScape Analytics provides powerful, flexible end-to-end data connectivity, feature engineering, model training, evaluation, and operational functions that can be deployed at scale as enterprise data assets; treating the products of ML and AI as first-class analytic processes in the enterprise.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>1. Configuring the Environment</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Standard Libraries\n",
    "import os\n",
    "import getpass\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "# Teradata Libraries\n",
    "from teradataml import *\n",
    "\n",
    "# Configuration\n",
    "spacing_large = \" \"*95\n",
    "spacing_small = \" \"*12\n",
    "display.max_rows = 5\n",
    "configure.val_install_location = 'val'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>2. Connect to Vantage</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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": {
    "tags": []
   },
   "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_InDB_Python.ipynb;' UPDATE FOR SESSION;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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;color:#00233C'>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;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>3. Data Exploration</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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;color:#00233C'><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": {
    "tags": []
   },
   "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;color:#00233C'>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;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><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;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><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;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><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;color:#00233C'>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;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><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;color:#00233C'>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;color:#00233C'>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;background-color:#00233C;\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Below are some insights about the dataset:</b></p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\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;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.5 Univariate statistics</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.6 Checking for Null Values</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.7 Checking for Outliers</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we are using teradataml syntax for the function. The same can be achived using the following SQL as well.</p>\n",
    "\n",
    "<code>SELECT * FROM TD_OutlierFilterFit(\n",
    "    ON \"DEMO_GLM_Fraud\".\"transaction_data\" AS InputTable\n",
    "    OUT TABLE OutputTable(\"DEMO_USER\".\"Outlier_output\")\n",
    "    USING\n",
    "    TargetColumns('amount','newbalanceOrig','oldbalanceDest','newbalanceDest','oldbalanceOrig')\n",
    ") as dt;</code>\n",
    "\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'><b><i>*Please note that both the versions run in-database and there is no data transfer involved.</i></b></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;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>4. Data Preparation</b>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'><b>We'll perform the following steps:</b></p>\n",
    "<ul style='font-size:16px;font-family:Arial;color:#00233C'>\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;color:#00233C'>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;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.1 Drop redundant columns</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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": {
    "tags": []
   },
   "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;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.2 One-hot encoding</b></p>\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>\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": {
    "tags": []
   },
   "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": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output shows that we have transformed the data into a transfromed dataset.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(txn_trans, table_name = 'clean_data', if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>5. Create training and testing datasets in Vantage</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We'll create two datasets for training and testing in the ratio of 80:20.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "TrainTestSplit_out = TrainTestSplit(\n",
    "    data = txn_trans,\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": {
    "tags": []
   },
   "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": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_train"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>6. In-Database XGBoost model training</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The XGBoost() function, also known as eXtreme Gradient Boosting, is an implementation of the gradient boosted decision tree algorithm designed for speed and performance. It has recently been dominating applied machine learning.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In gradient boosting, each iteration fits a model to the residuals (errors) of the previous iteration to correct the errors made by existing models. The predicted residual is multiplied by this learning rate and then added to the previous prediction. Models are added sequentially until no further improvements can be made. It is called gradient boosting because it uses a gradient descent algorithm to minimize the loss when adding new models.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we are using teradataml syntax for the function. The same can be achived using the following SQL as well.</p>\n",
    "\n",
    "<code>SELECT * FROM TD_XGBoost(\n",
    "\tON \"DEMO_USER\".\"clean_data_train\" AS \"input\"\n",
    "\tPARTITION BY ANY\n",
    "\tUSING InputColumns('amount','newbalanceOrig','oldbalanceDest','newbalanceDest','oldbalanceOrig','CASH_IN_type','CASH_OUT_type','DEBIT_type','PAYMENT_type','TRANSFER_type')\n",
    "\tResponseColumn('isFraud')\n",
    "\tMaxDepth(7)\n",
    "\tSeed(42)\n",
    "\tModelType('Classification')\n",
    "\tRegularizationLambda(120.0)\n",
    "\tShrinkageFactor(0.1)\n",
    ") as sqlmr</code>\n",
    "\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'><b><i>*Please note that both the versions run in-database and there is no data transfer involved.</i></b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "cols = df_train.columns\n",
    "cols.remove('txn_id')\n",
    "cols.remove('step')\n",
    "cols.remove('isFraud')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "XGBoost_out = XGBoost(\n",
    "    data=df_train,\n",
    "    input_columns=cols,\n",
    "    response_column = 'isFraud',\n",
    "    lambda1 = 120.0,\n",
    "    model_type='Classification',\n",
    "    seed=42,\n",
    "    shrinkage_factor=0.1,\n",
    "    max_depth=7\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "XGBoost_out.output_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The function output is a trained XGBoost model, and we can input it to the XGBoostPredict() function for prediction.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>7. In-Database XGBoost model scoring</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The XGBoostPredict() function runs the predictive algorithm based on the model generated by XGBoost(). The XGBoost() function, also known as eXtreme Gradient Boosting, performs classification or regression analysis on datasets.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "When using the function, we should provide only numeric features. We need to convert the categorical features to numeric values before prediction.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "XGBoostPredict_out = XGBoostPredict(\n",
    "    newdata=df_test,\n",
    "    object=XGBoost_out.result,\n",
    "    model_type='Classification',\n",
    "    id_column='txn_id',\n",
    "    object_order_column=['task_index', 'tree_num',\n",
    "                       'iter', 'tree_order'],\n",
    "    accumulate='isFraud',\n",
    "    output_prob=True,\n",
    "    output_responses=['0', '1']\n",
    ").result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "XGBoostPredict_out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output above shows our prob_1, i.e., the transaction is fraud, and prob_0, i.e., the transaction is not a fraud. We use these probabilities in our prediction column to assign a class label.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "combined_df = df_test.join(XGBoostPredict_out, on='txn_id', lsuffix='test', rsuffix='pred')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "combined_df[combined_df['Prediction']==1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "out = XGBoostPredict_out.assign(Prediction = XGBoostPredict_out.Prediction.cast(type_ = BYTEINT))\n",
    "out = out.assign(Prediction = out.Prediction.cast(type_ = VARCHAR(2)))\n",
    "out = out.assign(isFraud = out.isFraud.cast(type_ = VARCHAR(2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "ClassificationEvaluator_obj = ClassificationEvaluator(\n",
    "    data = out,\n",
    "    observation_column = 'isFraud',\n",
    "    prediction_column = 'Prediction',\n",
    "    labels = ['0', '1']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "ClassificationEvaluator_obj.output_data.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>8. Visualize the results (ROC curve and AUC)</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from teradataml import ROC\n",
    "\n",
    "roc_out = ROC(\n",
    "    probability_column = '\"Prob_1\"',\n",
    "    observation_column = \"isFraud\",\n",
    "    positive_class = \"1\",\n",
    "    data = XGBoostPredict_out,\n",
    "    num_thresholds=300\n",
    ")\n",
    "\n",
    "roc_out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "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'XGBoost 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;color:#00233C'>Looking at the above ROC Curve, we can confidently say that our model has performed well on testing data. The AUC value is 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;color:#00233C'><b>Conclusion</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demonstration, we have illustrated a simplified - but complete - overview of how we can implement a typical machine learning workflow completely inside the database using Vantage. This allows us to leverage Vantage's operational scale, power, and stability.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>9. Cleanup</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We need to clean up our work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['clean_data', 'clean_data_train', 'clean_data_test']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name = table)\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>Databases and Tables </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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;background-color:#00233C;\">\n",
    "\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Required Materials</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>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;color:#00233C'><b>Filters:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\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;color:#00233C'><b>Related Resources:</b></p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\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;color:#00233C'>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;color:#00233C'><b>Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\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; background:#f9f9f9; border-bottom:3px solid #00233C\">\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 - 2024. 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
}