UseCases/Data_Prep_and_Transformation/Data_Prep_and_Transformation_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Data Preparation and Discovery\n",
    "   <br>\n",
    "       Using Teradataml python package\n",
    "   <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Introduction</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This is a demonstration of the teradataml package that is designed for data management, exploration, and execution of analytic functions.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The current version of the teradataml package includes <b>over 100 functions</b>, organized into these functional areas:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Utility and database management functions</li>\n",
    "    <li>Data exploration and preparation functions</li>\n",
    "    <li>Analytic functions across Vantage</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>These functions leverage the full power and scale inside Vantage without:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Costly, slow export of data out of the DBMS</li>\n",
    "    <li>Being limited by client platform resources</li>\n",
    "    <li>Having to write complex SQL</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Contents</b></p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Configuring the Environment</li>\n",
    "    <li>Connect to Vantage</li>\n",
    "    <li>Create and Load Tables</li>\n",
    "    <li>Data Discovery</li>\n",
    "    <li>Working with Data</li>\n",
    "    <li>Advanced Data Preparation</li>\n",
    "    <li>Visualizing results.</li>\n",
    "    <li>Cleanup</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>1. Configuring the Environment</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the section, we import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import getpass\n",
    "import os\n",
    "import warnings\n",
    "#Suppress Warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "from teradataml import *\n",
    "\n",
    "from sqlalchemy import func\n",
    "display.max_rows=5\n",
    "\n",
    "import seaborn as sns\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>2. Connect to Vantage</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, then use down arrow to go to next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=DataPrepAndDiscovery.ipynb;' UPDATE FOR SESSION;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>3. Create and Load Tables</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.1  Create Demo Transaction data - simulated funds transfers.  Use FastLoad to create and import data</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Fastload protocol is excellent for row counts over 100K - shown here as an illustration. These Teradata functions have lots of parameters to help control behavior - the if_exists parameter is excellent, so we don't have to explicitly drop the table before loading it - or we can append it, etc. We can also use copy_to_sql for smaller row counts and more flexibility.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the CSV data into a local pandas dataframe\n",
    "ip_data = pd.read_csv('data/Transactions_60K.csv')\n",
    "\n",
    "# Little bit of code that creates an index\n",
    "ip_data['txn_id'] = range(1, len(ip_data) + 1)\n",
    "\n",
    "fastload(ip_data,\n",
    "         table_name='ip_data', \n",
    "         primary_index='txn_id',\n",
    "         if_exists = 'replace',\n",
    "         open_sessions=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=DataFrame.from_query('SELECT COUNT(*) as cnt FROM ip_data;')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.2  Create Simulated Customer Data - load from tdf using SQL to create the table</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the example above, we created our table automatically by calling Fastload. These functions allow us to define data types, encoding, and other parameters. However, we can use SQL to create the table if we want more control. In the below example, we need the \"ST_GEOMETRY\" data type, which python doesn't support. Hence we use Teradata SQL to overcome this limitation of python.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "CREATE MULTISET TABLE CUSTOMER, NO FALLBACK,\n",
    "     NO BEFORE JOURNAL,\n",
    "     NO AFTER JOURNAL,\n",
    "     CHECKSUM = DEFAULT,\n",
    "     DEFAULT MERGEBLOCKRATIO\n",
    "(\n",
    "    CUSTOMER_ID DECIMAL(18,0) NOT NULL,\n",
    "    F_NAME VARCHAR(30),\n",
    "    L_NAME VARCHAR(30),\n",
    "    VALIDITY VARCHAR(60),\n",
    "    CUST_ZIP VARCHAR(5),\n",
    "    CUST_LOCATION ST_GEOMETRY,\n",
    "    ETHNICITY VARCHAR(20),\n",
    "    GENDER CHAR(1),\n",
    "    CHURN_FLAG VARCHAR(1)\n",
    ")\n",
    "PRIMARY INDEX(CUSTOMER_ID);\n",
    "'''\n",
    "\n",
    "try:\n",
    "    execute_sql(qry)\n",
    "except:\n",
    "    execute_sql('DROP TABLE CUSTOMER;')\n",
    "    execute_sql(qry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Load the customer data - we're using the above table and reading the tdf file directly. Note that we have flexibility on different delimiters - in this case, it is a tab.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(pd.read_csv('data/CUSTOMER.tdf', sep='\\t'), table_name='CUSTOMER')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=DataFrame.from_query('SELECT COUNT(*) as cnt FROM CUSTOMER;')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.3 Create Simulated Customer Comment Table. Use copy_to_sql to create the table and load the data</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(pd.read_csv('data/CUST_COMMENT.csv'),\n",
    "            table_name='CUST_COMMENT',\n",
    "            if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=DataFrame.from_query('SELECT COUNT(*) as cnt FROM CUST_COMMENT;')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>3.4 Additional Simulated Data - Server Locations. Use SQL to handle the ST_GEOMETRY data type </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Following is an example where python does not support the ST_GEOMETRY data type, so we can use Teradata SQL to overcome the limitations.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "CREATE MULTISET TABLE SERVER, NO FALLBACK,\n",
    "     NO BEFORE JOURNAL,\n",
    "     NO AFTER JOURNAL,\n",
    "     CHECKSUM = DEFAULT,\n",
    "     DEFAULT MERGEBLOCKRATIO\n",
    "(\n",
    "    SERVER_ID VARCHAR(5) NOT NULL,\n",
    "    SERVER_ZIP VARCHAR(5),\n",
    "    SERVER_LOCATION ST_GEOMETRY\n",
    ")\n",
    "PRIMARY INDEX(SERVER_ID);\n",
    "'''\n",
    "\n",
    "try:\n",
    "    execute_sql(qry)\n",
    "except:\n",
    "    execute_sql('DROP TABLE SERVER;')\n",
    "    execute_sql(qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#load the data - read the csv file using pandas read_csv\n",
    "srvr = pd.read_csv('data/SERVER.csv')\n",
    "srvr.rename(columns = {'SERVER ZIP':'SERVER_ZIP','SERVER_LAT':'SERVER_LOCATION'}, inplace = True)\n",
    "\n",
    "copy_to_sql(srvr, table_name='SERVER')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=DataFrame.from_query('SELECT COUNT(*) as cnt FROM SERVER;')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>4. Data Discovery</b>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Look at table statistics, sample data, simple lookups</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>One of the most powerful features of the teradataml functions is that they push processing down to the Teradata system, allowing users to perform analysis without pulling all the data back to the client.\n",
    "<br>\n",
    "The following cell creates a pointer(virtual dataframe) to the <b>ip_data</b> table server. We retrieve the table's size and a small sample, i.e. five rows, back to this python environment to look at the sample data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get a teradata DataFrame - this creates a local reference to the large table on the server.\n",
    "tdf_ip_data = DataFrame(\"ip_data\")\n",
    "\n",
    "# Check the data - size and sample rows without returning all the data\n",
    "print(tdf_ip_data.shape)\n",
    "\n",
    "# Return a small set of the data back to a traditional Pandas DF for full-featured formatting.\n",
    "tdf_ip_data.to_pandas(num_rows = 5).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Please scroll down to the end of the notebook for detailed column descriptions of the above dataset.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check for null values.\n",
    "tdf_ip_data.info(null_counts = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above result shows that there are no nulls in the dataset.\n",
    "    <br>\n",
    "    <br>\n",
    "Generate Stats using Teradata DataFrame describe method - similar to pandas, but it runs on the server; we don't need to retrieve all the data. This cell shows the column-wise statistics.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_ip_data.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Apply a set of expressions on the virtual dataframe using loc (pandas set processing technique) to grab all fraudulent values. The logic here will filter fraudulent transactions of type 'TRANSFER' - as a view on the server, not move the data. Calling head(2) will only retrieve two values from the server.\n",
    "<br>\n",
    "<br>\n",
    "The next cell shows a sample of 2 Fraud transactions of type TRANSFER.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_ip_data.loc[(tdf_ip_data.isFraud == 1) & (tdf_ip_data.type == 'TRANSFER')].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following cell shows a sample of 2 Fraud transactions with only the 3 columns(amount, isFraud, type).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#filter the dataframe, then only retrieve two rows of results\n",
    "tdf_ip_data.loc[tdf_ip_data.isFraud == 1].filter(items = ['amount', 'isFraud', 'type']).head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>5. Working with Data at Scale</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Act on our data sets without having to return all the data and leverage the computing power of the Teradata Vantage cluster.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.1 Aggregations</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can use these \"fluent\" methods to keep the code as brief and expressive as possible. The following cell counts each instance of fraud grouped by transaction type. Note that the only data that moves out of the database is the final count() aggregation.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_ip_data.loc[tdf_ip_data.isFraud == 1].filter(items = ['amount', 'isFraud', 'type']).groupby('type').count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The next cell gives us min and max transaction amounts by transaction type. We can use multiple aggregates in the agg() function call.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_ip_data.loc[tdf_ip_data.isFraud == 1].filter(items = ['amount', 'isFraud', 'type']).groupby('type').agg({'amount' : ['min', 'max']})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.2 Simple Transformations</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Create new \"Virtual Dataframes\" that results from dropping columns or adding new ones via simple expression. The following cell creates a new Virtual DataFrame by dropping a few columns.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_data = tdf_ip_data.loc[tdf_ip_data.isFraud == 1].drop(['nameDest', 'nameOrig', 'isFlaggedFraud'], axis = 1)\n",
    "clean_data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The next cell assigns a new column which is the difference between newbalanceDest and amount of transaction.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_data = clean_data.assign(diff = clean_data['newbalanceDest'] - clean_data['amount'])\n",
    "clean_data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The next cell creates a new column which is the binary representation of transaction type.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_data = clean_data.assign(btype = clean_data['type'].str.contains('CASH_OUT'))\n",
    "clean_data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, binary representation of 1 means CASH_OUT and 0 means TRANSFER. This is similar to Ordinal Encoding.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>5.3 Joins</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Join dataframes using python pandas-style join methods. These are teradataml DataFrame methods and run completely in database.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_customer = DataFrame('CUSTOMER')\n",
    "tdf_customer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_cust_comment = DataFrame('CUST_COMMENT')\n",
    "tdf_cust_comment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The next cell performs a join between CUSTOMER and CUST_COMMENT tables on CUSTOMER_ID index.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Do an inner join and drop up fields we don't need.\n",
    "tdf_comment_full = tdf_cust_comment.join(other = tdf_customer, \n",
    "                                         on = ['CUSTOMER_ID = CUSTOMER_ID'], \n",
    "                                         how = 'inner', \n",
    "                                         lsuffix = 'cID_', \n",
    "                                         rsuffix = 'cOM_')\n",
    "tdf_comment_full.drop(['COMMENT_ID', 'cOM__CUSTOMER_ID', 'CHANNEL_ID', 'GENDER', 'CHANNEL_TYPE', 'ETHNICITY', 'VALIDITY'], axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output shows the join of two tables, CUSTOMER and CUST_COMMENT. Note that tdf_customer and tdf_cust_comment are just pointers to the Teradata Dataframe. The data is not moved in this process.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>6. Advanced Data Preparation</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TeradataML Python package has exposed many powerful SQL data transformation functions to the user.  We can apply these functions to Teradata Dataframes to operate on data at scale in the database.\n",
    "See the documentation for a complete list of functions (including aggregate, arithmetic, Bit/Byte, Date and Time, Hash, Regular Expression, and String Functions).</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.1 Aggregate Functions:</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#This import statement is also above, but rewritten here for emphasis. These functions are applied to the\n",
    "#Teradata dataframe via the SQLAlchemy func class\n",
    "\n",
    "from sqlalchemy import func\n",
    "\n",
    "#reuse our datasets from above\n",
    "clean_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Pearson correlation coefficient (r) is the most common way of measuring a linear correlation. A number between -1 and 1 measures the strength and direction of the relationship between two variables. For our fraudulent data set, Pearson Correlation Coefficient - in this example: What's the correlation between the original balance and the transfer amount?</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "corr_func = func.corr(clean_data['oldbalanceOrg'].expression, clean_data['amount'].expression)\n",
    "\n",
    "\n",
    "#Setting drop_columns = True here\n",
    "df_corr = clean_data.assign(drop_columns = True, corr_ = corr_func)\n",
    "\n",
    "print(df_corr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, the Pearson Correlation Coefficient is higher, meaning the transfer amount is also high when the original balance is high. Both variables are positively correlated.\n",
    "<br>\n",
    "<br>\n",
    "Kurtosis: Let's see what the variance from the normal distribution looks like for our complete data set, for example - transfer amounts grouped by the transfer type. A normal distribution has a kurtosis of 0; negative indicates fewer outliers, and positive represents more significant outliers.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kurtosis_func = func.kurtosis(tdf_ip_data['amount'].expression)\n",
    "\n",
    "#Can also set drop_columns positionally\n",
    "df_kurtosis = tdf_ip_data.groupby('type').assign(True, kurtosis_xfer_amt = kurtosis_func)\n",
    "\n",
    "print(df_kurtosis)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.2 Arithmetic Functions</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Natural Log: Let's add a column, the natural log of the transfer amount. As we see from above, in calling the describe() method for the amount column, the min is .79, and the max is 36946551.76. We can use the natural log to create a tighter range of values for possible use in the analysis.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_new = tdf_ip_data.assign(ln_amount = func.ln(tdf_ip_data['amount'].expression))\n",
    "\n",
    "print(tdf_new.filter(items = ['amount', 'ln_amount']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.3 String Functions</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Converting payment type to lower case.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_lower = tdf_ip_data.assign(False, type_lower = func.lower(tdf_ip_data['type'].expression))\n",
    "print(tdf_lower.filter(items = ['type', 'type_lower']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.4 Regular Expression Functions</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Return the substring based on a regular expression. In our demo data, the \"nameDest\" has a character code as the first character of the account name (example M1057061069)</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "regexp_func = func.regexp_substr(tdf_ip_data['nameDest'].expression, '^[A-Z]{1}')\n",
    "\n",
    "tdf_regex = tdf_ip_data.assign(False, acct_ind = regexp_func)\n",
    "\n",
    "print(tdf_regex.filter(items = ['nameDest', 'acct_ind']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>7. Visualizations</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.1 - Example - Geospatial query to return plot-able data</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code calculates the Spherical Distance between the customers and servers.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "SELECT C.CUSTOMER_ID, \n",
    "  S.SERVER_ID, \n",
    "  CAST(C.CUST_LOCATION.ST_SphericalDistance(S.SERVER_LOCATION)/1000 AS DECIMAL(10,0))AS KM_DISTANCE \n",
    "FROM CUSTOMER C, SERVER S\n",
    "WHERE S.SERVER_ZIP = C.CUST_ZIP\n",
    "'''\n",
    "\n",
    "tdf_distance = DataFrame.from_query(qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_distance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Sort by greatest distance away\n",
    "tdf_distance.sort('KM_DISTANCE', ascending = False).head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>7.2 Use Pandas/seaborn to create visualizations inline</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code creates a graph showing the distance between the customer and the server. A majority of customers and servers are in a 4000 km range.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "\n",
    "sns.distplot(tdf_distance.to_pandas()['KM_DISTANCE'].astype(float), bins=50);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Do a bunch of work to filter, group, aggregate, retrieve, and format our chart\n",
    "tdf_ip_data.drop(['step', 'isFraud', 'isFlaggedFraud'], axis = 1).groupby('type').sum().to_pandas().set_index('type').plot(kind = 'bar');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the sum of amount, oldbalanceOrig, newbalanceOrig, oldbalanceDest, and newbalanceDest grouped by transaction type.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>8. Cleanup</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Work Tables</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Cleanup work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['CUSTOMER', 'CUST_COMMENT','SERVER','ip_data']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name=table)\n",
    "    except:\n",
    "        pass\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<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:16px;font-family:Arial;color:#00233C'><b>Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial'>\n",
    "    <li>Teradataml Python reference: <a href = 'https://docs.teradata.com/search/all?query=Python+Package+User+Guide&content-lang=en-US'>here</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}