UseCases/Credit_Card_Data_Preparation/Credit_Card_Data_Preparation_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6190d421-3eb0-4cd4-a5d7-e3472140ecd1",
   "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 Card Fraud Detection - Data Cleansing and Feature Engineering Pipeline\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": "9e758467-3d86-4780-a34e-d94f6e7af44e",
   "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'>\n",
    "This is a demonstration of Vantage capabilities for functional demos using the Teradataml Python Functions e.g.\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Data Cleansing Functions - like  GetFutileColumns, SimpleInputeFit and SimpleImputeTransform </li>\n",
    "        <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Data Exploration Functions - like  ColumnSummary and CategoicalSummary </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> Feature Engineering Functions - like BinCodeFit & Transform, OrdinalEncodingFit & Transform, OnehotencodingFit & Transform, ScaleFit & Transform and ColumnTransformer </li>\n",
    "</p>\n",
    "<br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "In a typical Data Science project there are multiple pre-processing steps involved to process the raw incoming data before it can actually be used in a model for predictions. On an estimate about 70-80% of the time and effort goes into the pre-processing steps. With the help of Vantage's in Db functions we can perform these functions very effectively and at scale.\n",
    "In this demo notebook we are using a sample financial data of credit card application with target of loan defaulters, we will go through the general pre-processing steps using the Teradataml Python Functions that are involved in getting the source data and making the data useable for model creation.\n",
    "</p>  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f2f601a-a441-430a-8896-67d96e5df1e6",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1aff8a4-fe78-4c05-a803-60f653abac8d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Import python packages, connect to Vantage and explore the dataset</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "543a7ccb-d80d-4b0a-8bb9-4e6346e84361",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import libraries\n",
    "import getpass\n",
    "from teradataml import *\n",
    "\n",
    "display.max_rows = 5 "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e6e18ba-4bf6-4d1d-840e-063f01252abd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, then <b>use down arrow</b> to go to next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5636ce90-1f17-47a4-9567-b288ded744b9",
   "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": "b623063d-60ec-43fc-ad0e-28ddb685a0d8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Setup for execution of notebook. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a423113-3432-4fad-b2af-127dfc23f6e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Credit_Card_Data_Preparation_Python.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "435d11ff-12d6-4ffc-9a00-dc2aa4b87cba",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;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. In this demo since we are using Temporal table we will be creating databases and tables in local storage and use them in the notebook. Please execute the procedure in the next cell.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d887126-d474-40e1-a845-0219e2c03aaa",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_CreditCard_cloud');\" \n",
    "# takes about 20seconds, estimated space: 0 MB\n",
    "#%run -i ../run_procedure.py \"call get_data('DEMO_CreditCard_local');\" \n",
    "# takes about 35 seconds, estimated space: 11 MB"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d962e96f-9bf1-4a1e-8293-dacf3f9c4b63",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – if you want to see status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c4eb17a-4400-4954-9f9d-6485d9435999",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23572866-f0e6-4885-9c2f-2d7b32466966",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b> Access data in Vantage  </b> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For this demo, data is already resident in Object Storage which we are accessing via ReadNOS, create a reference to the table, and sample the contents using the get_data procedure used above.  Data could just as easily reside in permanent tables, another RDBMS, or another Vantage system.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afc3ac55-976d-4e80-bdd6-7198783e0660",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_cc = DataFrame(in_schema(\"DEMO_CreditCard\",\"Credit_Card\"))\n",
    "tdf_cc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5e7ed51-8391-4828-9c6f-35ac8f8c4c9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_cc.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba753364-cca4-465f-8816-59d3da3cc0c9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>There are 50000 rows with 15 columns in Credit_Card table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "daae6379-331c-419f-90ff-0a5a31631f80",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> Teradataml Python Package Function Reference\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> The Teradata Package for Python (teradataml) is an open-source Python library package that combines the benefits of open-source Python language environment with the massive parallel processing capabilities of Teradata Vantage. More information can be found at \n",
    "<a href = 'https://docs.teradata.com/search/all?query=Welcome+to+Teradata+Package+for+Python&filters=prodname~%2522Teradata+Package+for+Python%2522&content-lang=en-US'>\n",
    "        Teradataml Python Reference\n",
    "    </a>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> The below command will list all the analytical functions present in the package.   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d24bf3f-4824-4899-85aa-9d6f828ec11b",
   "metadata": {},
   "outputs": [],
   "source": [
    "display_analytic_functions()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c8d8693-c6a1-4c6f-a854-a647da3a71d4",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5269fde-69b4-460f-8f2b-669e1181952b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b> 2. Checking data demographics  </b> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>ColumnSummary </b>function  displays Column name, datatype and other demographics like count of NULLs etc for each specified input table column</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0042aaa-b809-475d-9782-b6011c91ff49",
   "metadata": {},
   "outputs": [],
   "source": [
    "colsum = ColumnSummary(data=tdf_cc,\n",
    "                        target_columns=[':']\n",
    "                       )\n",
    "colsum.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6eb855d1-7efe-42c4-9214-2153f01f357a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cs = colsum.result.filter(items = ['ColumnName', 'Datatype', 'NullPercentage'])\n",
    "cs[cs['NullPercentage'] > 0.0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8be39f15-abd7-4c99-8fc7-ff5fa6a503dd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As the column HOUSETYPE_MODE has more than 50% of null values we can remove this column from our model calculations.<br>\n",
    "    Let's check the other varchar columns. \n",
    "OCCUPATION_TYPE also has high % of null values.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3b362ea-66d1-40b1-88fb-2fb65a4d8593",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>CATEGORICALSUMMARY </b>function   displays the distinct values and their counts for each specified input table column</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b007f175-7145-4a88-ab87-ef5ccfa16d12",
   "metadata": {},
   "outputs": [],
   "source": [
    "catsum = CategoricalSummary(data=tdf_cc,\n",
    "                             target_columns=['CODE_GENDER','NAME_CONTRACT_TYPE','NAME_FAMILY_STATUS'\n",
    "                                             ,'FLAG_OWN_CAR','OCCUPATION_TYPE']\n",
    "                            )\n",
    " \n",
    "catsum.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ecf779a-19c6-4d39-a5f3-6f2548f8f48d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>GETFUTILECOLUMNS </b>function displays the categorical columns which will have no effect on the model i.e if all the values are same or unique or If the count of distinct values in the columns divided by the count of the total number of rows in the input\n",
    "table is greater than or equal to the threshold value</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae466a6b-72aa-4dd0-9f4f-0f85657964d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "futilecol = GetFutileColumns(data=tdf_cc,\n",
    "                             object=catsum,\n",
    "                             category_summary_column=\"ColumnName\",\n",
    "                             threshold_value=0.05)\n",
    "futilecol.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eae82675-e250-40d4-be70-3998701c2c4d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we can see that FLAG_OWN_CAR will have no effect on the model as all the values in this column are same so we can remove this column from model creation</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3101c1a3-2437-4289-9902-8445c884ec3b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us check the values in the OCCUPATION_TYPE column to see what we can do for the NULLs in the column \n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d52acdf1-eb93-4763-838b-9f392744963d",
   "metadata": {},
   "outputs": [],
   "source": [
    "c1=catsum.result\n",
    "c1[c1['ColumnName'] == 'OCCUPATION_TYPE'].sort('DistinctValueCount', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "recognized-academy",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b> Impute Missing Values  </b> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>SimpleImputeFit </b>will output a table with the values that will be used to substitute the missing values<br>\n",
    "    <b>SimpleImputeTransform</b> will return the input data set with the missing values filled in.\n",
    "Verify the NULL values have been removed.<br>\n",
    "    *Note one can also use the Fit table as input to <b>ColumnTransformer</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2377e184-7774-4925-a728-4789e47444f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# fit the SimpleImpute function on categorical columns\n",
    "impute_fit_cat_output = SimpleImputeFit(data=tdf_cc,\n",
    "                              literals_columns=\"OCCUPATION_TYPE\",\n",
    "                              literals=\"not provided\")\n",
    " \n",
    "# Print the result DataFrame.\n",
    "impute_fit_cat_output.output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e5f7506-1b4d-4c23-8eed-9633523541a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assign imputed data to new dataframe\n",
    "occupationimputedf = SimpleImputeTransform(data=tdf_cc, object=impute_fit_cat_output.output).result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8d3eb00-95de-490f-8ab5-e8251edf764e",
   "metadata": {},
   "outputs": [],
   "source": [
    "occupationimputedf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26fa00c0-8ba8-4e0b-b41c-79c5b6a0a9b3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>SimpleImputeFit and SimpleImputeTransform function works on integer columns also and we can use them for filling the missing column values based on min/max/mean/median of the values in the column</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9cc0ecf-9bc6-42fc-93fa-8a125706640c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In model creation we usually prefer Numerical inputs instead of Characters, now let us check how many distinct values we have for our character columns so that we can encode them in numerals</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "933c229b-3586-4568-8564-8f65951402e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "count_column = c1.DistinctValue.count(distinct=True)\n",
    "\n",
    "df=c1.groupby(\"ColumnName\").assign(count_=count_column)\n",
    "df.sort('count_')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7bd6dc2-e37b-4b10-b737-761c5d0b299b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We are not using FLAG_OWN_CAR column in model creation, for other columns we can use <b>OneHotEncodingFit</b> and <b>OrdinalEncodingFit</b> and transform functions to convert character categories to numerals, for that we need to check the exact values present in the columns</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5604e5fc-13b7-4a6e-8a1a-ad160e8b6a5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "c1[c1['ColumnName'].isin(['CODE_GENDER','NAME_CONTRACT_TYPE'])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f8fb2a3-00f7-429f-8294-c60210bc3bfa",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a8a6f24-ebe1-4006-aab6-bfd7672898f7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b> 3. Feature Engineering Functions  </b> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>OneHotEncodingFit </b>outputs a table of attributes and categorical values to input to <b>OneHotEncodingTransform </b> which encodes them as one-hot numeric vectors.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5f822ea-6ad9-4a49-af8a-ff7856c9cc92",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create fit object to encode \"gender\" and \"contract type\" columns\n",
    "hot_fit = OneHotEncodingFit(data=tdf_cc,\n",
    "                                is_input_dense=True,\n",
    "                                target_column=['CODE_GENDER','NAME_CONTRACT_TYPE'],\n",
    "                                category_counts=[2,2],\n",
    "                                approach=\"auto\",\n",
    "                                other_column=\"other\")\n",
    " \n",
    "# Print the result DataFrame.\n",
    "hot_fit.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d0bfa70-f646-4a64-9f50-e64e355617b6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can check how the fit table looks like</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d69ec7b0-b07d-4c54-bb16-d07d5351b4b8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For categorical columns which have many values we can use <b>OrdinalEncoding</b> instead</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "04e1c52e-48a6-4502-b619-791df9f126ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "ordinal_fit = OrdinalEncodingFit(target_column=['NAME_FAMILY_STATUS','OCCUPATION_TYPE'],\n",
    "                                 data=tdf_cc,\n",
    "                                 default_value=-1\n",
    "                                )\n",
    "\n",
    "ordinal_fit.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c779c79-a51b-4e7a-a6a1-ee55da2ad9c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "ordinal_fit.result.tdtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "763bdc0b-d4fc-4444-b6c6-5139b781859d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>BinCodeFit and BinCodeTransform </b>bin-codes the\n",
    "specified input table columns. Bin-coding is typically used to convert numeric data to categorical data by binning the numeric data into multiple numeric bins (intervals).</p> "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b61a2ede-9732-46b2-ba62-7edbc9111376",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For variable width bins, we need to provide the bin table to the function. Let's create the table and use that in the BinCodeFit function</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7089b102-1440-48f8-961f-0695898150c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "query1 = '''\n",
    "CREATE MULTISET TABLE DEMO_User.FitInputTable \n",
    "     (\n",
    "      ColumnName varchar(20), \n",
    "      MinValue integer, \n",
    "      MaxValue integer, \n",
    "      Label varchar(20)\n",
    "  )\n",
    "NO PRIMARY INDEX;\n",
    "'''\n",
    "\n",
    "query2 = '''\n",
    "insert into FitInputTable values('age', 0, 18, '1-Children')\n",
    ";insert into FitInputTable values('age', 19, 25, '2-Young Adult')    \n",
    ";insert into FitInputTable values('age', 26, 45, '3-Middle Adult')\n",
    ";insert into FitInputTable values('age', 46, 60, '4-Old Adult')   \n",
    ";insert into FitInputTable values('age', 61 ,120, '5-Senior Citizen')\n",
    ";\n",
    "'''\n",
    "\n",
    "execute_sql(query1)\n",
    "execute_sql(query2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "378cb48b-57e1-42e4-85ef-f64620e23239",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_bin_data=DataFrame(in_schema(\"DEMO_User\",\"FitInputTable\"))\n",
    "tdf_bin_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2bbfebc6-a8c4-4dce-af0a-20f67f051055",
   "metadata": {},
   "outputs": [],
   "source": [
    "bin_code = BincodeFit(data=tdf_cc,\n",
    "                      fit_data=tdf_bin_data,\n",
    "                            fit_data_order_column = ['MinValue', 'MaxValue'],\n",
    "                            target_columns='AGE',\n",
    "                            minvalue_column='MinValue',\n",
    "                            maxvalue_column='MaxValue',\n",
    "                            label_column='Label',\n",
    "                            method_type='Variable-Width'\n",
    "                           )\n",
    " \n",
    "# Print the result.\n",
    "bin_code.output"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32a69cf1-ae9b-48ff-9e01-d9cd55dbd58a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The fit table looks like above.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48c2ed48-0a96-473d-97c4-0f7792fa1666",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>ScaleFit and ScaleTransform </b>scales specified input\n",
    "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": "ad518805-4dd6-479a-bd42-4ff239f44df1",
   "metadata": {},
   "outputs": [],
   "source": [
    "scale_fit = ScaleFit(data=tdf_cc,\n",
    "                       target_columns=\"AMT_INCOME_TOTAL\",\n",
    "                       scale_method=\"RANGE\",\n",
    "                       miss_value=\"KEEP\",\n",
    "                       global_scale=False)\n",
    "scale_fit.output"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "def2b5b6-9a2d-4a72-be0a-d1835ed6a960",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b> ColumnTransformer  </b> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ColumnTransformer function transforms the entire dataset in a single operation. You only need\n",
    "to provide the FIT tables to the function, and the function runs all transformations that you require in a\n",
    "single operation. Running all the it table transformations together in one-go gives approx. 30% performance improvement over running each transformation sequentially.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd1faa82-b74a-46c7-856b-31a50529a456",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us put all the fit tables we have created and transform the dataset</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a2254e8-d777-4280-a746-5b152b35bc2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "out1 = ColumnTransformer(input_data=tdf_cc,\n",
    "                                          simpleimpute_fit_data=impute_fit_cat_output.output,\n",
    "                                          bincode_fit_data=bin_code.output,\n",
    "                                          scale_fit_data=scale_fit.output,\n",
    "                                          onehotencoding_fit_data=hot_fit.result,\n",
    "                                        )\n",
    "# Print the result DataFrame.\n",
    "#out1 = ColumnTransformer_out.result \n",
    "out2 = ColumnTransformer(input_data=out1.result,\n",
    "                                         ordinalencoding_fit_data=ordinal_fit.result,\n",
    "                                        )\n",
    "tdf = out2.result   \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2289d186-bb9e-4c23-a7d3-18d237620dac",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can drop the columns from the dataframe and create a table in the database to use it further in the model.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "521a586c-06a8-4aef-9dde-6b72785edd51",
   "metadata": {},
   "outputs": [],
   "source": [
    "# now lets drop the extra columns, rename the columns in dataframe\n",
    "obj = StrApply(data=tdf,\n",
    "                   target_columns='AGE',\n",
    "                   output_columns='AGE_GROUP',\n",
    "                   string_operation='SUBSTRING',\n",
    "                   in_place=False,\n",
    "                   string_length=1,\n",
    "                   accumulate=[':']\n",
    "                )\n",
    "t1 = obj.result\n",
    "\n",
    "transformed_df = t1.assign(drop_columns=True\n",
    "                  ,SK_ID_CURR=t1.SK_ID_CURR  \n",
    "                  ,TARGET=t1.TARGET \n",
    "                  ,CNT_CHILDREN=t1.CNT_CHILDREN\n",
    "                  ,AMT_INCOME_TOTAL=t1.AMT_INCOME_TOTAL\n",
    "                  ,NAME_FAMILY_STATUS=t1.NAME_FAMILY_STATUS\n",
    "                  ,REGION_POPULATION_RELATIVE=t1.REGION_POPULATION_RELATIVE\n",
    "                  ,AGE_GROUP=t1.AGE_GROUP      \n",
    "                  ,FLAG_MOBIL=t1.FLAG_MOBIL     \n",
    "                  ,FLAG_EMP_PHONE=t1.FLAG_EMP_PHONE     \n",
    "                  ,CNT_FAM_MEMBERS=t1.CNT_FAM_MEMBERS        \n",
    "                  ,OCCUPATION_TYPE=t1.OCCUPATION_TYPE    \n",
    "                  ,MALE=t1.CODE_GENDER_1         \n",
    "                  ,FEMALE=t1.CODE_GENDER_0         \n",
    "                  ,REVOLVING_LOANS=t1.NAME_CONTRACT_TYPE_1     \n",
    "                  ,CASH_LOANS=t1.NAME_CONTRACT_TYPE_0            \n",
    "              ) \n",
    "\n",
    "transformed_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cef4d061-254e-45a9-b048-445f75320d87",
   "metadata": {},
   "outputs": [],
   "source": [
    "#copy the dataframe to table \n",
    "transformed_df.to_sql(\"transformed_data\", if_exists=\"replace\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c06a170f-74f0-4f57-9142-8f8ea0b47aba",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = DataFrame(in_schema(\"DEMO_User\",\"Transformed_Data\"))\n",
    "tdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e02d4560-8adb-4468-8c18-db1db5cd06b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1814912-3d04-410d-b0ba-4c4b795c4ad6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now that we’ve shown you how you can use ClearScape in-database functions for preparing the data, you’ve now got a set of data that is cleansed and processed you could proceed to use this as an input in data science model creation. \n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37a88c42-19ac-4a92-91bc-05b3d6c8fa91",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba1cde98-0cbe-4ccb-94aa-2193cc407922",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Cleanup</b> </p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Worktables</b> </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "318e8e00-bf75-4e92-bc92-322b84b89cda",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_drop_table(table_name=\"Transformed_Data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03f04dc9-762e-49f5-a557-0b83329937f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_drop_table(table_name=\"FitInputTable\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a304a4b0-7802-4d70-bbdd-dae74558d5db",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b>Database and Tables </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f47fe183-b4ee-4169-91cd-f5f8d3df37c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_CreditCard');\"        # Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c51e0a10-35ff-4f43-8d7f-2b5906df48f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e7910ac-3a21-41e5-b5c5-aa0c862efd01",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c0dd647-1687-4f10-967e-1e7c10de2ce9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b> 5. Conclusion </b> </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this notebook we have seen some of the Teradata Vantage Clearscape's new inDb functions for data cleansing, data exploration and feature engineering. Many of these functions can be applied in one go using the ColumnTransform function which gives is approx. 30% faster than serial processing.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f6c48eb-3742-4dea-8519-944b002a86ee",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Reference Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li>Teradata Analytic Function Reference:\n",
    "        <a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Analytics-Database-Analytic-Functions-Overview'>\n",
    "        https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Analyzing-Your-Data/Analytics-Database-Analytic-Functions/Analytics-Database-Analytic-Functions-Overview</a></li>\n",
    "  \n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efa00671-7e69-467d-9adf-4890e2dcf7ec",
   "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 - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}