UseCases/Credit_Risk_Assessment/Credit_Risk_Assessment_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "754c26f0-cd04-4fbd-8fc2-a9df54f9a89a",
   "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",
    "       Credit Risk Assessment using Teradataml OpenSource Functions  <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": "499aaef5-228f-4f95-9ab4-a4690c8a007c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Credit risk assessment is the process of evaluating the likelihood that a borrower will default on a financial obligation, such as repaying a loan or meeting other credit obligations. It involves analyzing various factors related to the borrower's financial health, credit history, and overall ability to repay debt. The primary goal of credit risk assessment is to enable lenders, such as banks, credit unions, or lending institutions, to make informed decisions about whether to extend credit to an individual or entity and under what terms.</p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Value</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'> One of the primary objectives of credit risk assessment is to minimize the risk of default on loans or credit lines. </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Effective credit risk assessment enables financial institutions to make more informed lending decisions. By evaluating factors such as the borrower's credit history, income, debt levels, and other relevant data, institutions can tailor loan terms and interest rates to reflect the level of risk associated with each borrower.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>By implementing robust credit risk assessment processes, institutions can ensure compliance with regulatory mandates such as Basel III, which requires banks to maintain adequate capital reserves based on the risk profiles of their lending portfolios. </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>By accurately assessing credit risk, institutions can offer competitive loan terms to creditworthy borrowers, thereby facilitating access to credit for individuals and businesses with demonstrated repayment capacity.</li>\n",
    "    </p>\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'>Vantage's ClearScape Analytics provides a comprehensive library of in-database (inDb) functions tailored for various stages of the data science lifecycle, including data exploration, preparation, feature engineering, model training, and evaluation. These functions seamlessly execute within the Vantage SQL engine, ensuring high performance and scalability while eliminating the need for data movement.<br>By leveraging the Teradata Python Package, specifically the teradataml OpenSourceML component, users gain the ability to utilize popular open-source machine learning packages like scikit-learn directly within the database environment. This means that data doesn't need to be transferred to the client for analysis, streamlining the workflow and enhancing efficiency.<br>The OpenSourceML package simplifies the integration of open-source machine learning functionalities into Vantage, offering a consistent interface for executing these algorithms. Users can leverage familiar syntax and arguments, making it easier to transition from traditional open-source environments to the Vantage platform.<br>Running these machine learning functions within Vantage enables organizations to scale their models effectively. Horizontal scaling allows for the training of segmented models based on various criteria such as region or user type. Meanwhile, vertical scaling enables the consolidation of data from millions or even billions of interactions, empowering organizations to leverage vast datasets for model training and analysis.<br>Furthermore, the models developed using these functionalities can be seamlessly deployed for operational use, enabling real-time understanding and prediction of actions. This operational deployment capability ensures that organizations can derive actionable insights and make informed decisions in a timely manner, driving value across various business domains.</p> "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a15f66f0-06dd-4b73-b63a-303f553fec9d",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us start by checking the version of the teradataml installed. The Openml functions used in this notebook will require Version 20.0.0.0.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e88980d-8f59-4c99-82ae-0fbad739a4c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "pip show teradataml"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a92123de-5042-46f3-bc13-a847f5de3d89",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>If the VM has lower version, please uncomment the below code and execute the cell.  After the cell executes, please 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": "ab9cc66f-260b-4d83-b37c-fb2181bd21a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install --upgrade teradataml"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4967176-2052-4d6b-a8f4-07f0ae3f3a0f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Import the required libraries, set environment variables and connect to Vantage.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0c4ff52-42ba-41f7-b54d-78bdd9482a29",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import libraries\n",
    "import getpass\n",
    "import warnings\n",
    "\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.metrics import ConfusionMatrixDisplay\n",
    "\n",
    "from teradataml import *\n",
    "\n",
    "\n",
    "display.max_rows = 5\n",
    "\n",
    "# Suppress warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "warnings.simplefilter(action='ignore', category=DeprecationWarning)\n",
    "warnings.simplefilter(action='ignore', category=RuntimeWarning)\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "warnings.simplefilter(action='ignore', category=UserWarning)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cccc3dc-4588-4000-a614-fda177feaa2f",
   "metadata": {},
   "source": [
    "<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. Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "222a3f20-7b4c-4311-ac55-661765b1aa0e",
   "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": "markdown",
   "id": "02b759b6-e6e9-43d6-9aa9-338360927d9c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a3befa1-34f4-4f68-8caa-8b77b7d7d11f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Credit_Risk_Assessment_Python.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7cdeef9-dd0d-4e6d-a4fe-5bff5127e143",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. 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.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f1a7f35-9119-4190-9f92-bb693c89da5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_CreditRisk_cloud');\"\n",
    "# takes about 1 minute, estimated space: 0 MB\n",
    "# %run -i ../run_procedure.py \"call get_data('DEMO_CreditRisk_local');\"\n",
    "# takes about 2 minutes, estimated space: 23 MB"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d93cb376-bb5c-45c7-b8d2-06c7da912436",
   "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,
   "id": "f95c4c54-0f5c-48cf-b34a-e52b5f91371c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6da895a6-7d3e-4c97-b39e-00046b802322",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Analyze the data set</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Let us start by analyzing the credit data of customers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a9ce9bf-92b7-4f39-9dba-eb5d8442256e",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = DataFrame(in_schema('DEMO_CreditRisk', 'Credit_Customers'))\n",
    "tdf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b6cba86-fb32-40c5-b2ad-0c485a9a3b12",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Checking the demographics of data</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4305ec1-e26c-4c89-b9a0-e02ffce22402",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59c556a1-91e4-4416-9513-71ae1250dd8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c950baf-1bd7-4746-a07b-70e8cde2c846",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see from above that we have 1000 records with 23 columns.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38e2d88c-5012-4377-97d3-bf7728d5b991",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;color:#00233C'><b>Summary of columns</b><br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;'>The <b>ColumnSummary</b> 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": "347b35aa-2b25-4ef7-b146-7d0e52231211",
   "metadata": {},
   "outputs": [],
   "source": [
    "colsum = ColumnSummary(data=tdf,\n",
    "                        target_columns=[':']\n",
    "                       )\n",
    "colsum.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2f55fd8-29ad-48d4-a4b6-7d5d7897e938",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;color:#00233C'><b>Checking the null percenatge of columns</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27fe826c-a944-44fd-8b8d-02aa4f138b3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "cs = colsum.result.filter(items = ['ColumnName', 'Datatype', 'NullPercentage'])\n",
    "cs[cs['NullPercentage'] > 0.0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0944d3b5-83fa-4e22-ba0d-105b81a1d819",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;'>From above we can see that there is no nulls in the data.<br>Let us know create lists of features which are categorical and numerical, we will use them in functions later.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c59f21b-9ec7-4118-b724-6dfc204d126e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a list of column names with data type 'str'\n",
    "str_cols = [col.split()[0] for col in str(tdf.dtypes).split('\\n') if col.split()[1] == 'str']\n",
    "str_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90fadc80-200a-46da-b753-09286ed1b851",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a list of column names with data type not 'str'\n",
    "num_cols = [col.split()[0] for col in str(tdf.dtypes).split('\\n') if col.split()[1] != 'str']\n",
    "num_cols"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8596c8e-5a14-459e-b9a4-a21311e3167f",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Data Preprocessing</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96716dd4-3e67-498c-af29-01aa1b08011d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Before the data can be used for model creation, we will need to do some data cleansing and transformation on it. We can do this InDb with Teradata Vantage's inbuilt functions.<br>The <b>CategoricalSummary</b> function displays the distinct values and their counts for each specified input DataFrame column.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eaccf5ee-8e8f-4650-baa2-1a92e60ad9da",
   "metadata": {},
   "outputs": [],
   "source": [
    "catsum = CategoricalSummary(data=tdf,\n",
    "                             target_columns=str_cols\n",
    "                            )\n",
    " \n",
    "catsum.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58e3b2ea-82d9-4260-8511-2213e553d448",
   "metadata": {},
   "source": [
    " <p style = 'font-size:16px;font-family:Arial;color:#00233C'>Checking the number of distinct values in each of categorical columns.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e39e16d6-e7c9-4835-8024-51fe235b9955",
   "metadata": {},
   "outputs": [],
   "source": [
    "c1=catsum.result\n",
    "count_column = c1.DistinctValue.count(distinct=True)\n",
    "\n",
    "d2=c1.groupby(\"ColumnName\").assign(count_=count_column)\n",
    "d2.sort('count_').head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ed36efa-1421-4866-a360-1ca0d64cdc41",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Ordinal Encoding</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now let us convert the string categorical values to numerical values. We can do this by using <b>Clearscape Analytics OrdinalEncoding Fit and Transform </b>functions. The Fit function creates a table which gives numerical value to each of the category of the column and the Transform function will replace the value of category string to its assigned numerical value.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1526408-32cb-4358-bc96-8b15d22b53c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Perform OrdinalEncoding\n",
    "ordinal_obj = OrdinalEncodingFit(target_column=str_cols,\n",
    "                                 data=tdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11479386-172a-475e-a6d5-c34626f82b8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "ordinal_obj.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dda44d43-f7ec-4434-b95c-aa868740796b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Transforming the encoded data.\n",
    "tdf2 = ordinal_obj.transform(data=tdf,\n",
    "                     accumulate=num_cols).result\n",
    "\n",
    "tdf2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6cea127-ae79-491d-9b63-70f3eb0fa9ee",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Feature Engineering</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will create 2 new features to add to our dataset for model training.<br>We will add below new columns\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li>debt_to_income_ratio</li><li>credit_utilization</li></ul></p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "99e12245-b1c5-42eb-b695-5d02f1beb1d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf3 = tdf2.assign(debt_to_income_ratio = tdf2.credit_amount/tdf2.duration * tdf2.installment_commitment,\n",
    "                  credit_utilization = tdf2.credit_amount/ tdf2.existing_credits)\n",
    "tdf3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b5357ad-ee3d-47e4-a05e-be09f5c51c0d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Bin Encoding</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now let us convert the age in age groups. We can do this by using <b>Clearscape Analytics BinEncoding Fit and Transform </b>functions. Let us first create the fit table which divides age in different groups.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c51708ee-41b4-4f60-a721-2b0af79ae9a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the data as a dictionary\n",
    "data = {\n",
    "    'ColumnName': ['age', 'age', 'age', 'age', 'age','age','age'],\n",
    "    'MinValue': [0,21,31,41,51,61,71],\n",
    "    'MaxValue': [20,30,40,50,60,70,150],\n",
    "    'Label': ['0-20', '21-30', '31-40', '41-50', '51-60','61-70','70+'],\n",
    "    'Label_Added' :['1','2','3','4','5','6','7']\n",
    "}\n",
    "\n",
    "\n",
    "# Create the DataFrame\n",
    "bin_fit_df = pd.DataFrame(data)\n",
    "bin_fit_df\n",
    "\n",
    "#copy the dataframe to Vantage\n",
    "copy_to_sql(bin_fit_df, table_name='bin_fit', schema_name='demo_user', if_exists='replace')\n",
    "bin_fit = DataFrame('bin_fit')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70bda86c-9c62-44c9-898b-521a007dd510",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create the Bin Fit table\n",
    "bin_fit = BincodeFit(data=tdf3,\n",
    "                            fit_data=bin_fit,\n",
    "                            fit_data_order_column = ['MinValue', 'MaxValue'],\n",
    "                            target_columns='age',\n",
    "                            minvalue_column='MinValue',\n",
    "                            maxvalue_column='MaxValue',\n",
    "                            label_column='Label_Added',\n",
    "                            method_type='Variable-Width',\n",
    "                            label_prefix='label_prefix'\n",
    "                           )\n",
    " \n",
    "# Print the result.\n",
    "bin_fit.output"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77006c1c-f58a-435d-a0e1-df4893cc4874",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From above you can see the fit table that was created by BinEncodingFit function. Now let us apply this to our data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6010f8a8-1bb5-41bf-a415-690ceb582919",
   "metadata": {},
   "outputs": [],
   "source": [
    "acc_col = tdf3.columns\n",
    "acc_col.remove('age')\n",
    "\n",
    "#Transform the data\n",
    "obj = BincodeTransform(data=tdf3,\n",
    "                           object=bin_fit.output,\n",
    "                           object_order_column=\"TD_MinValue_BINFIT\",\n",
    "                           accumulate=acc_col\n",
    "                          )\n",
    " \n",
    "#display result\n",
    "obj.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f160cf3-df5c-4a82-bef2-7e41ba9ae984",
   "metadata": {},
   "outputs": [],
   "source": [
    "td4=obj.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30c04bc7-ad12-498e-9ea8-3805c9a8c11d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>ScaleFit and ScaleTransform function scales specified input table columns i.e perform the specific scale methods like standard deviation, mean etc to the input columns </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b5bef00-1654-4842-8f54-59f26b83e14f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create the Scale fit table\n",
    "sf_fit = ScaleFit(\n",
    "    data = td4,\n",
    "    scale_method = 'RANGE',\n",
    "    target_columns = ['duration', 'credit_amount', 'installment_commitment', 'residence_since',\n",
    "            'debt_to_income_ratio' ,'credit_utilization', 'existing_credits' ])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ad84de7-9a8d-43e4-9da6-714fb07af0c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "str_cols.append(\"age\")\n",
    "str_cols.append(\"id\")\n",
    "\n",
    "#Transform the data\n",
    "res = sf_fit.transform(\n",
    "    data = td4,\n",
    "    accumulate = str_cols).result\n",
    "\n",
    "#display result\n",
    "res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5af9c2db-825d-4525-a541-7f0009fe6ac6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the cleansed data table in the database\n",
    "copy_to_sql(res, table_name = 'clean_data', if_exists = 'replace')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7351d14d-8d42-4339-982c-3f45b34b326d",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Model Creation</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us take a look at our data that we have cleansed and processed above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62000edd-0d59-429b-91fc-cc9ea8a3cac3",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = DataFrame('clean_data')\n",
    "tdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1d5f884-616b-4517-8701-d444220eab5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38b241d1-7e22-4fca-bd1b-944484137a1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = tdf.assign(age = tdf.age.cast(type_ = INTEGER))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a524d64b-f3a6-4447-b21d-c5e1d90ef650",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Train and Test Datasets</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now let us divide our data in training and test datasets for model creation. We can do this by using Clearscape Analytics TrainTestSplit function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10657fc2-67a7-4a25-9cf0-7a69e1a80d4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "TrainTestSplit_out = TrainTestSplit(\n",
    "                                    data = tdf,\n",
    "                                    id_column = \"id\",\n",
    "                                    train_size = 0.80,\n",
    "                                    test_size = 0.20,\n",
    "                                    seed = 25\n",
    "                                   )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3673fe7-3d0b-4922-a3d2-56ac6a1ae5ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a4ea8d2-c966-44d7-81a3-e87e18f2dde2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4767397c-e018-4fbb-a2c0-7f4166a2e781",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Divide the datasets in features and output label to be used in model training functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8709e8d5-4af4-4956-bfdc-649e390487de",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_train = df_train.drop(['output_class','id'], axis = 1)\n",
    "y_train = df_train.select([\"output_class\"])\n",
    "X_test = df_test.drop(['output_class','id'], axis = 1)\n",
    "y_test = df_test.select([\"output_class\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71bfa785-b19c-4d0f-928e-6e36212648b3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.1 DecisionTree</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Decision trees are a popular and intuitive machine learning algorithm used for both classification and regression tasks. Decision trees are a machine learning algorithm that organizes data into a tree-like structure, where each internal node represents a decision based on a feature, and each leaf node represents the outcome. By recursively splitting the data based on the most informative features, decision trees can efficiently partition the dataset into homogeneous subsets. They offer interpretability, as the tree structure can be easily understood, and implicit feature selection, revealing which features are most important for prediction. <br> With the addition of teradataml OpenSourceML functions we can seamlessly use the DecisionTreeClassifier available in scikit-learn python module as an inDb function.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b880502f-607e-4a58-b6d7-6f53e6a769dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Instantiate scikit-learn DecisionTreeClassifier object through teradataml OpenSourceML's 'td_sklearn'.\n",
    "from teradataml import td_sklearn as osml\n",
    "DT_classifier = osml.DecisionTreeClassifier(random_state=42)\n",
    "DT_classifier.fit(X_train, y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b26ea3d-5a69-4647-95ac-f5e38fc248fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#accuracy of the model\n",
    "accuracy_DT = DT_classifier.score(X_test, y_test)\n",
    "accuracy_DT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af461db1-e998-4e80-95fb-775d76fa19a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#model predictions\n",
    "predict_DT =DT_classifier.predict(X_test,y_test)\n",
    "predict_DT"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0af79bd-bc5e-4dd7-b589-1513e85fc4bb",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.1.1 Confusion Matrix for DecisionTree</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A confusion matrix is a matrix that summarizes the performance of a machine learning model on a set of test data. It is a means of displaying the number of accurate and inaccurate instances based on the model’s predictions. It is often used to measure the performance of classification models, which aim to predict a categorical label for each input instance.<br>Let us create the confusion matrix for the DecisionTreeClassifier we have used above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3d6a5d3-aaa7-4e08-96ff-2941d9845267",
   "metadata": {},
   "outputs": [],
   "source": [
    "cm_DT = osml.confusion_matrix(y_true=predict_DT.select([\"output_class\"]),y_pred=predict_DT.select([\"decisiontreeclassifier_predict_1\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7d0e3e7-1ce0-434e-9ec8-52e98a22c015",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can cross check the values our encoder has given to output_class 'Good' and 'Bad'</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8316237a-9346-44a5-ab66-9ee5e5d87e5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "ordinal_obj.result.select([\"TD_ColumnName_ORDFIT\",\"TD_Category_ORDFIT\",\"TD_Value_ORDFIT\"])[ordinal_obj.result['TD_ColumnName_ORDFIT'] == 'output_class']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3babfb81-0ea2-4d71-940c-8008d5a8ec38",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define class labels\n",
    "class_names = ['Bad', 'Good']\n",
    "\n",
    "# Display confusion matrix\n",
    "disp = ConfusionMatrixDisplay(confusion_matrix=cm_DT, display_labels=class_names)\n",
    "disp.plot(cmap=plt.cm.Blues)\n",
    "plt.title('Confusion Matrix')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42686306-538c-46df-8ffc-f1bfec938ad2",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.2 K-Nearest Neighbors</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Let us check one more classifier for our data.<br>KNN is a non-parametric, supervised learning classifier. It uses the proximity of data points to make predictions or classifications. Given an input data point, KNN identifies the k nearest neighbors (other data points) based on a chosen distance metric (usually Euclidean distance). The class label of the input point is determined by a majority vote among its k neighbors.<br> We will use scikit-learn's KNeighborsClassifier for this</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22eb48ee-b69e-484d-94be-7030899e9e60",
   "metadata": {},
   "outputs": [],
   "source": [
    "#define the classifier\n",
    "KN_classifier = osml.KNeighborsClassifier()\n",
    "KN_classifier.fit(X_train, y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90526134-376f-4843-a3e5-6477fae7a6e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#accuracy of the model\n",
    "accuracy_KN = KN_classifier.score(X_test, y_test)\n",
    "accuracy_KN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6e92d29-e18a-4ac5-8508-39e3fea45de8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#model predictions\n",
    "predict_KN =KN_classifier.predict(X_test,y_test)\n",
    "predict_KN"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a8743b1-c20f-4b48-8cab-7df9b4b4e2d0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.2.1 Confusion Matrix for KNN</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us create the confusion matrix for the KNeighborsClassifier we have used above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12d77828-6658-4802-9ab0-ad1c94b404bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "cm_knn = osml.confusion_matrix(y_true=predict_KN.select([\"output_class\"]),y_pred=predict_KN.select([\"kneighborsclassifier_predict_1\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75ac8959-226e-4c5c-a941-cb3b7799f3d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define class labels\n",
    "class_names = ['Bad', 'Good']\n",
    "\n",
    "# Display confusion matrix\n",
    "disp = ConfusionMatrixDisplay(confusion_matrix=cm_knn, display_labels=class_names)\n",
    "disp.plot(cmap=plt.cm.Blues)\n",
    "plt.title('Confusion Matrix for KNN classifier')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5832d10-d4a5-4143-b909-dc62d2589d84",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Conclusion</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this notebook we have seen the seamless integration of Teradata Vantage and ClearScape Analytics inDb functions with OpensourceML functions, empowering users with scalable data processing capabilities. By leveraging these functionalities within the database environment, users gain the flexibility to analyze vast amounts of data without the need to transfer it to client tools. This integration not only streamlines the workflow but also provides users with the choice of utilizing open-source machine learning functions directly within the database, further enhancing efficiency and expanding the range of analytical tools available.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa9c8d70-eeb5-4e7e-9479-b483d9468d13",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Cleanup</b></p>\n",
    "<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": "c070ec3a-2d84-494d-9173-64c6f87f085a",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_drop_table(table_name='clean_data') "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b2d359b-179c-44ba-af5e-01baa8f934a5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Database 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": "84f1d405-7109-4e75-a4e4-c31dc9e4336e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_CreditRisk');\" \n",
    "#Takes 10 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8873a213-ee72-44b7-9109-10ae587d4031",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cba4d575-f1bf-4356-b140-c9823c55bcab",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If you have updated the teradataml package, reinstall the package by uncommenting and running the below code cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34f7e846-32da-4b3e-8b64-d77b3445f69a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install teradataml==17.20.0.6 --force-reinstall"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c14e3ed-305b-4aad-9605-c7e9acfefa96",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\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",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Industry:</b> Financial</li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Functionality:</b> Open-Source Machine Learning Functions</li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Use Case:</b> Credit Risk Assessment</li></p>\n",
    "    <p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Related Resources:</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://www.teradata.com/customers/swedbank-cloud-modernization'>How Swedbank Builds Trust Using Cloud Analytics and Data</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href='https://www.teradata.com/Blogs/Fraud-Busting-AI'>Fraud-Busting-AI</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href='https://www.teradata.com/Industries/Financial-Services'>Financial Services</a></li>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9526e1f-c0b9-40c3-88a7-930f4434e34f",
   "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",
    "            © 2024 Teradata. 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": 5
}