UseCases/Financial_Fraud_Detection_InDB/Financial_Fraud_Detection_InDB_R.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a9003da9-fc9b-4b14-a804-70accec73258",
   "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 R and tdplyr\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",
   "id": "07f36c06-b1e6-42a3-b5d0-ae070be88183",
   "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",
   "id": "0333aea0-cf5b-4e5a-8ad2-1cc7c40f1a9d",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Install and load the necessary Packages and Libraries</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Installing R only needs to be done once for this environment.\n",
    "<br><br>\n",
    "To install the package, copy this line into the clipboard:\n",
    "<blockquote><i>\n",
    "Rscript -e \"install.packages('tdplyr',repos=c('https://r-repo.teradata.com','https://cloud.r-project.org'))\"\n",
    "</i></blockquote> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Open a terminal window by selecting File --> New --> and the select Terminal.  Then paste the line at the Command Prompt and press Enter.  This will take a few minutes.  It's finished when you are returned to the <b>(base) jovyan@de76f2e68a54:~/JupyterLabRoot$ </b> Command Prompt.\n",
    "</p>\n",
    "    \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,
   "id": "6be90518-b98b-43e5-891a-80e9cf5357a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "suppressMessages(install.packages(\"dbplot\", quiet = TRUE))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91570c95-5de4-49f1-b343-d52c1b6de5a0",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>The above statement will install the required libraries and may give a message about the installation. In case of any message please make sure its just a message or a warning and not any error. After installation make sure to restart the kernel. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4755467a-940d-4a50-b3d7-499ac23d48ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "suppressWarnings(suppressMessages({\n",
    "library(tdplyr)\n",
    "library(dbplyr)\n",
    "library(dplyr)\n",
    "library(DBI) \n",
    "library(dbplot)\n",
    "library(ggplot2)    \n",
    "    }))     "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8def18e0-e5eb-43f3-80c6-df19c8e3c5c3",
   "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,
   "id": "0312ba01-3913-4636-91e3-cde200e8c5e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "con <- td_create_context(host = 'host.docker.internal', uid = \"demo_user\", pwd = getPass(\"Enter your password: \")\n",
    "                         , dType = \"NATIVE\", logmech = \"TD2\")\n",
    "     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b3d237f-3f63-4ed4-868a-b37a2cd7243c",
   "metadata": {},
   "outputs": [],
   "source": [
    "dbExecute(con,\"SET query_band='DEMO=PP_Financial_Fraud_Detection_InDB_R.ipynb;' UPDATE FOR SESSION;\") "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b9178f4-236e-416d-a9dd-71d2a508ce29",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fef4338-40bf-4c00-8610-f04f6e6f30dd",
   "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,
   "id": "8dbc1128-3993-4b57-b49a-74e9597dbd99",
   "metadata": {},
   "outputs": [],
   "source": [
    "# dbExecute(con,\"call get_data('DEMO_GLM_Fraud_cloud');\")       # Takes 1 minute\n",
    "dbExecute(con,\"call get_data('DEMO_GLM_Fraud_local');\") # Takes 2min to load the data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5cc02d7-7efe-4a1b-8115-c2c022edcb8e",
   "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,
   "id": "99b012a6-0f7a-40f6-9374-ea09ff0f95e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "txn_data <- tbl(con, in_schema(\"DEMO_GLM_Fraud\", \"transaction_data\"))\n",
    "sprintf(\"No of Rows :%s\",td_nrow(txn_data))\n",
    "txn_data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4007960f-90af-49a7-9bbb-b2b17e2725aa",
   "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",
   "id": "f1c85be9-86d4-4639-919e-b86bae1b0c93",
   "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,
   "id": "48a82dbd-bbd6-49f8-950c-3982cd6e9584",
   "metadata": {},
   "outputs": [],
   "source": [
    "# There are 92 fraud transactions i.e. 0.14% of fraud transactions in the dataset.\n",
    "df_isFraud <- txn_data %>% filter(isFraud == 1) \n",
    "sprintf(\"No of fraud transactions: %s\" ,td_nrow(df_isFraud))\n",
    "sprintf(\"Percentage of fraud transactions: %.2f%%\" ,td_nrow(df_isFraud)/td_nrow(txn_data)*100)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f59a0ac1-894b-45ae-b7c3-8ff369d74004",
   "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,
   "id": "84b0fc05-82b7-483c-8a25-f4d528d6525f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter data for fraud transactions and group by 'type'\n",
    "transactions_by_type = txn_data %>% group_by(type) %>% summarise(count_txn_id = n(txn_id)) \n",
    "transactions_by_type %>% arrange(desc(count_txn_id))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "449e6c26-693f-4a1f-abdf-f98e265c7fd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "txn_data %>%\n",
    "  dbplot_bar(type,count(txn_id)) +\n",
    "  labs(title = \"Number of Transaction -  Transaction Type\",\n",
    "      x = \"Transaction Type\",\n",
    "    y = \"Number of transactions\",\n",
    "    colour = \"blue\") +\n",
    "  theme_light() "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c840f94-e784-4417-91c2-e3a38af782a5",
   "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,
   "id": "d35b1e33-af94-405f-8d7c-64e93dcbabb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "fraud_transactions_by_type <- txn_data %>% filter(isFraud == 1)%>% group_by(type) %>% summarise(count_txn_id = n(txn_id))\n",
    "# fraud_transactions_by_type %>% arrange(desc(count_txn_id))\n",
    "x = td_nrow(df_isFraud)\n",
    "fraud_transactions_by_type <- fraud_transactions_by_type %>% \n",
    "                            mutate(total_fraud = x\n",
    "                                ,type_int = case_when(type == 'TRANSFER' ~ 0, TRUE ~ 1)) \n",
    "                                    \n",
    "\n",
    "fraud_transactions_by_type %>% arrange(desc(count_txn_id))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c1c3502-183a-4aec-8d9d-6b3559b56b76",
   "metadata": {},
   "outputs": [],
   "source": [
    "fraud_transactions_by_type %>%\n",
    "  dbplot_bar(type,avg(count_txn_id)) +\n",
    "  labs(title = \"Number of Fraud Transaction -  Transaction Type\",\n",
    "      x = \"Transaction Type\",\n",
    "    y = \"Number of transactions\",\n",
    "    colour = \"blue\") +\n",
    "  theme_light() "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba8e8aa0-033a-4604-afe3-e2ef770454e5",
   "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",
   "id": "5be9019d-e3fb-49bd-ab87-c83e2ace595c",
   "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,
   "id": "d6ffe3db-b320-4e04-8593-3b3c64241199",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cleanout <- txn_data %>% filter(amount == oldbalanceOrig) %>% filter(isFraud == 1) \n",
    "sprintf(\"No of cleanout fraud transactions: %s\" ,td_nrow(df_cleanout))\n",
    "sprintf(\"Percentage of fraud transactions: %.2f%%\" ,td_nrow(df_cleanout)/td_nrow(df_isFraud)*100)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86d27bc2-3608-4c6d-bce3-b95f6d862ca2",
   "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",
   "id": "012fa9b9-81ec-4a8c-b5cb-43b74c34c0e8",
   "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",
   "id": "a2a74f39-3c73-464f-b935-6e6353247a6f",
   "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,
   "id": "10295b3a-46cb-4164-8b74-2330aa7fc018",
   "metadata": {},
   "outputs": [],
   "source": [
    "glimpse(txn_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "791b3256-16b3-4e93-8e58-1d7fc4507fcc",
   "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,
   "id": "2d797be5-6507-49ab-bf76-8c96fa5dca73",
   "metadata": {},
   "outputs": [],
   "source": [
    "colsum <- td_column_summary_sqle(data=txn_data,\n",
    "                        target.columns=c(\"amount\",\"oldbalanceOrig\",\"newbalanceOrig\",\"oldbalanceDest\",\"newbalanceDest\")\n",
    "                       )\n",
    "    \n",
    "# Print the result.\n",
    "print(colsum$result)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f05842b4-e53f-4958-95ee-86884deb00b7",
   "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 Teradata R library 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,
   "id": "c3e4e327-93c8-43ae-a590-5c1e12aa5d5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "fit_object <- td_outlier_filter_fit_sqle (\n",
    "      data = txn_data,\n",
    "      target.columns = c(\"amount\",\"oldbalanceOrig\",\"newbalanceOrig\",\"oldbalanceDest\",\"newbalanceDest\")\n",
    "      )\n",
    "\n",
    "res <- td_outlier_filter_transform_sqle(data=txn_data,\n",
    "                                             object=fit_object)\n",
    "    \n",
    "# Print the result.\n",
    "print(res$result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "867f502c-e16a-4ba3-8641-a9c3be730d03",
   "metadata": {},
   "outputs": [],
   "source": [
    "sprintf(\"Rows before removing outliers: %s\" ,td_nrow(txn_data))\n",
    "sprintf(\"Rows after removing outliers: %s\" ,td_nrow(res$result))\n",
    "sprintf(\"Total outliers: %s\" ,td_nrow(txn_data)-td_nrow(res$result))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a1b7fa8-1576-4790-827c-78c4ebb1fb1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_setdiff <- txn_data %>% setdiff(res$result)\n",
    "df_setdiff"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f195bff1-04d9-4f74-92f0-60543e58a47a",
   "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",
   "id": "0bfa8b49-3ab3-4c61-8d1b-087686f806da",
   "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,
   "id": "9e6affb7-5cf4-48af-8a76-dcd1f9d3db18",
   "metadata": {},
   "outputs": [],
   "source": [
    "txn_data = select(txn_data, -c(\"nameDest\", \"nameOrig\", \"isFlaggedFraud\"))\n",
    "txn_data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ada1406-fa6a-4215-9d4f-f8a422a4377e",
   "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,
   "id": "ea8ca9c1-33e8-4af6-b739-601ea796a9fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "txn_data <- txn_data %>% rename(txn_type = type)\n",
    "fit_obj <- td_one_hot_encoding_fit_sqle(\n",
    "                                data=txn_data,\n",
    "                                is.input.dense=TRUE,\n",
    "                                target.column=\"txn_type\",\n",
    "                                categorical.values=c('CASH_IN', 'CASH_OUT', 'DEBIT', 'PAYMENT', 'TRANSFER'))\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "819ab021-b0db-4be9-b07b-aee0e5ee870b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Encode \"male\" and \"female\" values of column \"sex\".\n",
    "# Note that tbl_teradata representing the model is passed as\n",
    "# input to \"object\".\n",
    "obj <- td_one_hot_encoding_transform_sqle(\n",
    "                                  data=txn_data,\n",
    "                                  object=fit_obj$result,\n",
    "                                  is.input.dense=TRUE)\n",
    "    \n",
    "# Print the result.\n",
    "txn_trans = obj$result\n",
    "txn_trans"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2baf7772-5fbd-4676-aa7d-64f3d8c30502",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.3 Feature Scaling</b></p>\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>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,
   "id": "0b00910b-d90a-4ecd-8ad7-1a63e0a24f58",
   "metadata": {},
   "outputs": [],
   "source": [
    "fit_obj <- td_scale_fit_sqle(data=txn_trans,\n",
    "                                 target.columns=c(\"step\", \"amount\",\"newbalanceOrig\", \"oldbalanceDest\",\"newbalanceDest\",\"oldbalanceOrig\"),\n",
    "                                 scale.method=\"STD\")\n",
    "\n",
    "# Print the result.\n",
    "print(fit_obj$result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e52695a7-e8a2-4c62-be2e-31761ad1e612",
   "metadata": {},
   "outputs": [],
   "source": [
    "obj <- td_scale_transform_sqle(data=txn_trans,\n",
    "                               object=fit_obj$result,\n",
    "                               accumulate=c(\"txn_id\", \"isFraud\", \"txn_type_CASH_IN\", \"txn_type_CASH_OUT\",\"txn_type_DEBIT\",\n",
    "                                            \"txn_type_PAYMENT\", \"txn_type_TRANSFER\"))\n",
    "    \n",
    "# Print the result.\n",
    "# print(obj$result)\n",
    "cleandf = obj$result\n",
    "cleandf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09df00ca-b65a-4c12-94ee-9661aca1f885",
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_data_df <- as.data.frame(cleandf)\n",
    "copy_to(con, clean_data_df , name=\"clean_data\", overwrite=TRUE)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e662263b-7fd2-48cf-92f9-d8e84d358205",
   "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.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5987addb-5ae8-42a0-b530-a73d6a934b42",
   "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,
   "id": "82483a72-b7bf-4f8c-811f-72161882780f",
   "metadata": {},
   "outputs": [],
   "source": [
    "TrainTestSplit_out <- td_train_test_split_sqle(\n",
    "                            data = cleandf,\n",
    "                            id.column=\"txn_id\",\n",
    "                            train.size=0.90,\n",
    "                            test.size=0.10,\n",
    "                            seed=24)\n",
    "TrainTestSplit_out$result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11f52dee-4a01-4038-957a-055d6a3288ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = TrainTestSplit_out$result %>% filter(TD_IsTrainRow == 1) \n",
    "df_train = select(df_train, -c(\"TD_IsTrainRow\"))\n",
    "df_test = TrainTestSplit_out$result %>% filter(TD_IsTrainRow == 0)\n",
    "df_test = select(df_test, -c(\"TD_IsTrainRow\"))\n",
    "sprintf(\"Training Set = %s\" , td_nrow(df_train))\n",
    "sprintf(\"Testing Set = %s\" , td_nrow(df_test))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "802e8131-35ea-4ca9-8978-bcad3c25046f",
   "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": "code",
   "execution_count": null,
   "id": "f7e9f251-788a-40b9-b8ee-a8e8c6060789",
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df <- as.data.frame(df_train)\n",
    "copy_to(con, train_df , name=\"train_data\", overwrite=TRUE)\n",
    "test_df <- as.data.frame(df_test)\n",
    "copy_to(con, test_df , name=\"test_data\", overwrite=TRUE)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "916eb9de-ec60-42db-a2a8-dd24bc6bac8d",
   "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 GLM model training</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bb7bd75-2f4b-48e4-81b3-752953ce3800",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TD_GLM function as a generalized linear model (GLM) for performing regression and classification analyses on data sets, where the response follows an exponential or binomial family distribution.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When using the function, we should provide only numeric features. We need to convert the categorical features to numeric values before training. Additionally, during training, TD_GLM function skips the rows with missing (null) values.</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_GLM(\n",
    "    ON \"DEMO_USER\".\"clean_data_train\" AS inputtable\n",
    "    PARTITION BY ANY\n",
    "    OUT TABLE MetaInformationTable(\"DEMO_USER\".\"glm_output\")\n",
    "    USING\n",
    "    InputColumns('[2:12]')\n",
    "    ResponseColumn('isFraud')\n",
    "    Family('Binomial')\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,
   "id": "34eff2ae-3c71-41cc-96c4-a3c78d7a1dd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train <- tbl(con, \"train_data\")\n",
    "df_test <- tbl(con, \"test_data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50174941-37f8-4e44-b86a-86041091bb43",
   "metadata": {},
   "outputs": [],
   "source": [
    "GLM_out <- td_glm(input.columns= \n",
    "                                    c(\"txn_type_CASH_IN\",\"txn_type_CASH_OUT\",\"txn_type_DEBIT\",\n",
    "                                      \"txn_type_PAYMENT\",\"txn_type_TRANSFER\",\"step\",\"amount\",\"newbalanceOrig\",\n",
    "                                      \"oldbalanceDest\",\"newbalanceDest\",\"oldbalanceOrig\"),\n",
    "                             response.column = \"isFraud\",\n",
    "                             data = df_train,\n",
    "                             family = 'BINOMIAL',\n",
    "                             learning.rate = 'OPTIMAL',\n",
    "                             iter.max = 6,\n",
    "                             batch.size = 18,\n",
    "                             lambda1 = 0.003,\n",
    "                              tolerance = 0.08,\n",
    "                              momentum = 0.0065)\n",
    "GLM_out$result    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c8b639a-6c55-488f-9ae0-5217f385d6c0",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The function output is a trained GLM model, and we can input it to the TDGLMPredict function for prediction. Our model also contains model statistics like MSE, Loglikelihood, AIC, and BIC.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96ca38d5-6de2-414f-abf9-31bb3b0c479c",
   "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 GLM model scoring</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "349170b6-6be5-4041-883b-0feaed232d15",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TDGLMPredict function predicts target values (regression) and class labels (classification) for test data using a GLM model trained by the GLM function.</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,
   "id": "c6103e3b-af76-4d60-9017-a6791e416c97",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "TDGLMPredict_out <- td_glm_predict(\n",
    "                                    object=GLM_out$result,\n",
    "                                    newdata=df_test,\n",
    "                                    accumulate=\"isFraud\",\n",
    "                                    id.column=\"txn_id\",\n",
    "                                    output.prob=TRUE,\n",
    "                                    output.responses = c('0','1'))\n",
    "    \n",
    "# Print the result.\n",
    "# print(TDGLMPredict_out$result)\n",
    "glm_predict = TDGLMPredict_out$result\n",
    "glm_predict"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5945aa6a-a27a-4cbf-8784-7e4621d9ad93",
   "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,
   "id": "69003ceb-5dde-42b1-a5c5-93a5e8c65108",
   "metadata": {},
   "outputs": [],
   "source": [
    "predicted_data <- td_convert_to_sqle(data = glm_predict,\n",
    "                                         target.columns = c(\"isFraud\",\"prediction\"),\n",
    "                                         target.datatype = c(\"integer\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ddaba26-a3a4-46ac-ae38-6e53b34af317",
   "metadata": {},
   "outputs": [],
   "source": [
    "ClassificationEvaluator_obj <- td_classification_evaluator_sqle(\n",
    "                                    data=predicted_data$result,\n",
    "                                    observation.column='isFraud',\n",
    "                                    prediction.column='prediction',\n",
    "                                    labels=c('0','1'))\n",
    "    \n",
    "# Print the result tbl_teradata objects.\n",
    "# print(ClassificationEvaluator_obj$result)\n",
    "print(ClassificationEvaluator_obj$output.data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b3d51b6-101f-48ef-a92a-c61cf0a6fe56",
   "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",
   "id": "6a24d33e-e38c-4fda-a19c-ce7110028d3a",
   "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,
   "id": "5bcf2513-01c8-4c9a-9880-7535ed052942",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "predicted_data <- td_convert_to_sqle(data = glm_predict,\n",
    "                                         target.columns = c(\"isFraud\"),\n",
    "                                         target.datatype = c(\"integer\"))\n",
    "                                         #target.datatype = c(\"NUMERIC\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f44ec295-d60f-4070-9be2-067325f67f55",
   "metadata": {},
   "outputs": [],
   "source": [
    "roc_out <- td_roc(\n",
    "                probability.column=\"prob_1\",\n",
    "                observation.column=\"isFraud\",\n",
    "                positive.class=\"1\",\n",
    "                data=predicted_data$result,\n",
    "                num_thresholds=300)\n",
    "    \n",
    "    \n",
    "# Print the result.\n",
    "print(roc_out$result)\n",
    "print(roc_out$output.data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f77a52d7-c57c-4c36-9de8-dd508be8975f",
   "metadata": {},
   "outputs": [],
   "source": [
    "roc_df = roc_out$output.data\n",
    "ggplot(data=roc_df, aes(x=fpr, y=tpr)) +\n",
    "  geom_line()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d39f6698-41b5-4dfe-9c1b-1a8883b68140",
   "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 0.65 and resonates with our understanding that the model is performing ok.</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",
   "id": "fbc79ec5-472b-4060-8e07-b70e202a915b",
   "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",
   "id": "db3d111d-69cd-4e93-a373-b31786e4d54a",
   "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,
   "id": "e08b5c19-eff7-448f-89c0-14018406aadd",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = c('clean_data', 'train_data', 'test_data')\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for (table in tables)\n",
    "{ tryCatch(\n",
    "        db_drop_table(con, table)\n",
    "   )}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54428bb6-d820-45ba-ac70-6835093adb4a",
   "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,
   "id": "0d42eaa6-c021-4f87-97a6-9bfde4767e26",
   "metadata": {},
   "outputs": [],
   "source": [
    "dbExecute(con,\"call remove_data('demo_glm_fraud');\")  # Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "108d0619-6566-4b9d-bd97-c8be92a3ab24",
   "metadata": {},
   "outputs": [],
   "source": [
    "td_remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da9d9813-d364-49f8-8075-950f8c12dd41",
   "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 R reference: <a href = 'https://docs.teradata.com/r/Enterprise/Teradata-Package-for-R-Function-Reference'>here</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6046e1f5-e78f-41fc-9e07-ab77002b4144",
   "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": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.1.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}