R_Py-Part_5/R_Py_TechBytes-Part_5-Demo.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "################################################################################\n",
    "# The contents of this file are Teradata Public Content and have been released\n",
    "# to the Public Domain.\n",
    "# Tim Miller & Alexander Kolovos - April 2020 - v.1.1\n",
    "# Copyright (c) 2020 by Teradata\n",
    "# Licensed under BSD; see \"license.txt\" file in the bundle root folder.\n",
    "#\n",
    "################################################################################\n",
    "# R and Python TechBytes Demo - Part 5: Python in-nodes with SCRIPT\n",
    "# ------------------------------------------------------------------------------\n",
    "# File: R_Py_TechBytes-Part_5-Demo.py\n",
    "# ------------------------------------------------------------------------------\n",
    "# The R and Python TechBytes Demo comprises of 5 parts:\n",
    "# Part 1 consists of only a Powerpoint overview of R and Python in Vantage\n",
    "# Part 2 demonstrates the Teradata R package tdplyr for clients\n",
    "# Part 3 demonstrates the Teradata Python package teradataml for clients\n",
    "# Part 4 demonstrates using R in-nodes with the SCRIPT and ExecR Table Operators\n",
    "# Part 5 demonstrates using Python in-nodes with the SCRIPT Table Operator\n",
    "################################################################################\n",
    "#\n",
    "# This TechBytes demo utilizes a use case to predict the propensity of a\n",
    "# financial services customer base to open a credit card account.\n",
    "#\n",
    "# The present demo Part 5 showcases two scenarios: We show the 2 use case types\n",
    "# that are most broadly used with the Vantage SCRIPT table operator:\n",
    "#\n",
    "# 1) Fitting and scoring a single model\n",
    "#\n",
    "#    We take a 25% sample extracted from the database and build a Random\n",
    "#    Forest model on a Python client. This Random Forest model is then saved\n",
    "#    as an encoded serialized file and installed back on the Vantage Advanced\n",
    "#    SQL Engine.\n",
    "#    We formulate a scoring script and execute this on the Advanced SQL Engine\n",
    "#    using the SCRIPT table operator to score the entire data set.\n",
    "#\n",
    "#    For this use case, we start from the same analytic data set that was\n",
    "#    used in the teradataml demo (Part 3).\n",
    "#\n",
    "# 2) Fitting and scoring multiple models\n",
    "#\n",
    "#    We utilize the statecode variable as a partition to built a Random\n",
    "#    Forest model for every state. This is done by using SCRIPT Table Operator\n",
    "#    to run a model fitting script with a PARTITION BY statecode in the query.\n",
    "#    This creates a model for each of the CA, NY, TX, IL, AZ, OH and Other\n",
    "#    state codes, and perists the model in the database via CREATE TABLE AS\n",
    "#    statement.\n",
    "#    Then we run a scoring script via the SCRIPT Table Operator against\n",
    "#    these persisted Random Forest models to score the entire data set.\n",
    "#\n",
    "#    For this use case, we build an analytic data set nearly identical to the\n",
    "#    one in the teradataml demo (Part 3), with one change as indicated by item\n",
    "#    (d) below. This is so we can demonstrate the in-database capability of\n",
    "#    simultaneously building many models.\n",
    "#    Various features will be generated by joining and aggregating from three\n",
    "#    Vantage-based tables (10K customers, 100K accounts, 1M+ transactions) into\n",
    "#    an analytic data set. We show how to use teradataml functions to do the\n",
    "#    following:\n",
    "#\n",
    "#    a) Pull through the cust_id, income, age, years_with_bank, nbr_children\n",
    "#       from the Customer table.\n",
    "#    b) Create a gender indicator variable (female_ind) from gender in the\n",
    "#       Customer table.\n",
    "#    c) Create marital status indicator variables (single_ind, married_ind,\n",
    "#       separated_ind) from marital_status in the Customer table.\n",
    "#    d) Recode the state_code variable into, CA, NY, TX, IL, AZ, OH and Other\n",
    "#       in Customer table.  This replaces the location indicator variables\n",
    "#       (ca_resident, ny_resident, tx_resident, il_resident, az_resident,\n",
    "#       oh_resident) in the Analytic Data Set used in the tdplyr demo.\n",
    "#    e) Create account indicator variables (ck_acct_ind, cc_acct_ind,\n",
    "#       sv_acct_ind) from acct_type in the Account table.\n",
    "#    f) Create average balance variables (ck_avg_bal, cc_avg_bal, sv_avg_bal)\n",
    "#       by taking the mean of the beginning_balance and ending_balance in the\n",
    "#       Account table.\n",
    "#    g) Create average transaction amounts (ck_avg_tran_amt, cc_avg_tran_amt,\n",
    "#       sv_avg_tran_amt) by taking the average of the principal_amt and\n",
    "#       interest_amt in the Transactions table.\n",
    "#    h) Create quarterly transaction counts (q1_nbr_trans, q2_nbr_trans,\n",
    "#       q3_nbr_trans, q4_nbr_trans) by taking the count of tran_id's based upon\n",
    "#       tran_date in the Transactions table.\n",
    "#\n",
    "#    Then we sample 60% of the analytic data set rows to create a training\n",
    "#    subset. The remaining 40% is used to create a testing/scoring dataset.\n",
    "#    The train and test/score datasets are used in the SCRIPT operations.\n",
    "#\n",
    "# Note: Code executed successfully on Python v.3.6.8, and by using teradataml\n",
    "#       v.16.20.00.05 to connect to a Vantage system that runs Advanced SQL\n",
    "#       Engine database v.16.20.40.01.\n",
    "################################################################################\n",
    "# File Changelog\n",
    "#  v.1.0     2019-10-29     First release\n",
    "#  v.1.1     2020-04-02     Code simplified with case, sample teradataml funcs.\n",
    "#                           Additional information about connections.\n",
    "# ##############################################################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load teradataml and dependency packages to use in both use cases.\n",
    "# For use case [1], also load the sklearn package to create a random forest\n",
    "# model on the client.\n",
    "from teradataml import create_context, DataFrame, get_context, copy_to_sql, in_schema\n",
    "from teradataml.dataframe.sql_functions import case\n",
    "from sqlalchemy.sql.expression import select, or_, extract, text, join, case as case_when\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle\n",
    "import base64"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Establish a connection to Teradata Vantage server with the Teradata SQL Driver\n",
    "# for Python. Before you execute the following statement, replace the variables\n",
    "# <HOSTNAME>, <UID>, and <PWD> with the target Vantage system hostname, your\n",
    "# database user ID, and password, respectively.\n",
    "td_context = create_context(host=\"<HOSTNAME>\", username=\"<UID>\", password=\"<PWD>\")\n",
    "\n",
    "# Notes and alternatives:\n",
    "# 1. In any connection function, you can specify for an argument the getpass()\n",
    "#    function of the Python standard library. First, you will need to execute:\n",
    "#    import getpass\n",
    "#    getpass() enables you to type your password secretly during runtime\n",
    "#    without having to hard-code it in the script.\n",
    "# Example: Specifying the argument password = getpass.getpass(\"Password: \") will\n",
    "#          produce a prompt string \"Password: \" that expects you to type in\n",
    "#          a password to proceed.\n",
    "# 2. Specifying the optional argument logmech enables you to specify particular\n",
    "#    logging mechanisms that may apply on your target server. For example, you\n",
    "#    can connect via an active directory with LDAP credentials by specifying\n",
    "#    the argument: logmech = \"LDAP\" as follows:\n",
    "# td_context = create_context(host=\"<HOSTNAME>\", username=\"<UID>\", password=\"PWD\", logmech=\"LDAP\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# USE CASE [1]: Single model fitting and scoring example"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *USE CASE [1] - Section 1: Fit and save model (on client)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Note: To proceed with the present section, ensure you have first executed \n",
    "# Section 1 of the R and Python TechBytes Demo - Part 3. You will need to have\n",
    "# the Analytic Data Set (ADS) ADS_Py table in the target Vantage system."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a training dataset from the persisted table 'ADS_Py'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Create a DataFrame and take a glimpse at it.\n",
    "tdADS_Py = DataFrame(\"ADS_Py\")\n",
    "tdADS_Py.to_pandas().head(10)\n",
    "\n",
    "# Select a 25% sample of the analytic data set to use for model fitting\n",
    "ADS_Train = tdADS_Py.sample(frac = [0.25])\n",
    "\n",
    "try:\n",
    "    get_context().execute(\"DROP TABLE ADS_Train\")\n",
    "except:\n",
    "    pass\n",
    "\n",
    "copy_to_sql(ADS_Train, table_name=\"ADS_Train\", if_exists=\"replace\")\n",
    "\n",
    "# Create a DataFrame and take a glimpse at it.\n",
    "tdTrain = DataFrame(\"ADS_Train\")\n",
    "# Convert to pandas DataFrame to enable subsequent DataFrame operations\n",
    "tdTrain_df = tdTrain.to_pandas()\n",
    "tdTrain_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Classification model training with Random Forests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Determine the columns that the predictor accounts for\n",
    "predictor_columns = [\"tot_income\", \"tot_age\", \"tot_cust_years\", \"tot_children\",\n",
    "                     \"female_ind\", \"single_ind\", \"married_ind\", \"separated_ind\",\n",
    "                     \"ck_acct_ind\", \"sv_acct_ind\", \"ck_avg_bal\", \"sv_avg_bal\",\n",
    "                     \"ck_avg_tran_amt\", \"sv_avg_tran_amt\", \"q1_trans_cnt\",\n",
    "                     \"q2_trans_cnt\", \"q3_trans_cnt\", \"q4_trans_cnt\"]\n",
    "\n",
    "# Note: At time of creation of this TechByte, in-nodes Python has RF classifier from scikit-learn package v.0.20.3.\n",
    "#       Keep an eye for potential incompatibilites, if a package version on your client differs from the in-nodes.\n",
    "#       In the present TechByte, the client carries scikit-learn package v.0.21.2. No issues were observed when\n",
    "#       using a model built with this later version for in-nodes scoring.\n",
    "#       In case errors may be produced due to different scikit-learn versions on the client and in-nodes, you can\n",
    "#       try switching your client's scikit-learn version to match in-nodes (\"pip install scikit-learn==<version>\").\n",
    "\n",
    "# For the classifier, specify the following parameters:\n",
    "# ntree: n_estimators=500, mtry: max_features=5, nodesize: min_samples_leaf=1 (default; skipped)\n",
    "\n",
    "classifier = RandomForestClassifier(n_estimators=500, max_features=5, random_state=0)\n",
    "X = tdTrain_df[predictor_columns]\n",
    "y = tdTrain_df[\"cc_acct_ind\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Train the Random Forest model to predict Credit Card account ownership based upon specified independent variables.\n",
    "\n",
    "classifier = classifier.fit(X, y)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting the Random Forest model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Save model into a file; we name it \"RFmodel_py.out\".\n",
    "# Note: In the following, we are using both the libraries pickle (to serialize)\n",
    "#       and base64 (to encode) the model prior to saving it into a file. If\n",
    "#       model is only pickled, then unplickling in database might produce a\n",
    "#       pickle AttributeError that claims an \"X object has no attribute Y\".\n",
    "#       This is related to namespaces in client and target systems.\n",
    "#       For some more insight, see:\n",
    "#       https://docs.python.org/3/library/pickle.html#pickling-class-instances\n",
    "\n",
    "classifierPkl = pickle.dumps(classifier)\n",
    "classifierPklB64 = base64.b64encode(classifierPkl)\n",
    "with open('RFmodel_py.out', 'wb') as fOut:           # Using \"wb\" to write in binary format\n",
    "    fOut.write(classifierPklB64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The saved model will then need to be installed on the target Vantage system\n",
    "# (see following Section 2) together with the scoring script in file\n",
    "# \"stoRFScore.py\" so you can perform the scoring operation with the SCRIPT\n",
    "# Table Operator.\n",
    "#\n",
    "# Before you proceed to Section 2, look up the full path of the location\n",
    "# where the model file is saved on your client machine. Let us call this\n",
    "# path <modelPATH>. You will need to specify the <modelPath> value later\n",
    "# in Section 2 within the SQL script file \"R_Py_TechBytes-Part_5-Demo.sql\"\n",
    "# that calls the SCRIPT Table Operator.\n",
    "# Speficy the <modelPath> suitably for your platform. Here are some example\n",
    "# <modelPATH> strings for different client platforms:\n",
    "# On Windows OS, specify a path like: \"C:\\\\Users\\\\me\\\\Path\\\\To\\\\RFmodel_py.out\"\n",
    "# On MacOS, specify a path like     : \"/Users/me/Path/To/RFmodel_py.out\"\n",
    "# On Linux, specify a path like     : \"/home/me/Path/To/RFmodel_py.out\"\n",
    "#\n",
    "# At this point, you can review the scoring script file \"stoRFScore.py\" that is\n",
    "# distributed with the present TechBytes demo material.\n",
    "# Similarly to the model path, also look up the full path of the script file\n",
    "# location. Let us call this path as the <scriptPath>. You will need to specify\n",
    "# the <scriptPath> value, too, later in the \"R_Py_TechBytes-Part_5-Demo.sql\"\n",
    "# SQL script file.\n",
    "# Like for the model path, speficy the <scriptPath> suitably for your platform.\n",
    "# For example, on Linux specify a path like: \"/home/me/Path/To/stoRFScore.py\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *USE CASE [1] - Section 2: Score with model (in database; uses SCRIPT)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Scoring the test dataset with the saved model takes place in the target\n",
    "# Vantage system Advanced SQL Engine. To perform this task, simply review and\n",
    "# execute the code in the SQL script file \"R_Py_TechBytes-Part_5-Demo.sql\".\n",
    "#\n",
    "# In a few places across the SQL script file \"R_Py_TechBytes-Part_5-Demo.sql\"\n",
    "# you will need to specify the database where your model and Python script\n",
    "# files will reside. You will then also need to edit the scoring script file\n",
    "# \"stoRFScore.py\" to specify the same database name where needed, so that the\n",
    "# scoring script can find the model.\n",
    "#\n",
    "# Use only the code in the \"Use Case [1]\" section of the SQL script file.\n",
    "# You can execute the SQL code in a SQL interpreter such as Teradata Studio,\n",
    "# by connecting to the target Vantage system and the database <DBNAME> where\n",
    "# the testing/scoring dataset \"ADS_Py\" resides.\n",
    "# The above SQL script file demonstrates using Python with the SCRIPT Table\n",
    "# Operator. The SQL script needs to know the location of the files that contain\n",
    "# the Random Forest model that was created in the preceding Section 1, and the\n",
    "# scoring Python script \"stoRFScore.py\". For this scoring task, the SCRIPT\n",
    "# Table Operator uses as input the testing/scoring table \"ADS_Py\". The output\n",
    "# of this scoring task is the sought information about the propensity of the\n",
    "# financial services customers in the test table to open a credit card account."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# USE CASE [2]: Multiple models fitting and scoring example"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *USE CASE [2] - Section 1: Transform data and create model and test data sets*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create DataFrames for the Customer, Accounts and Transactions tables in the Vantage Advanced SQL Engine\n",
    "# Note: Use in_schema() function if tables reside in database other than connecting user's default database.\n",
    "\n",
    "# Before you execute each one of the following statements, replace the variable <DBNAME>\n",
    "# with the target Vantage system database name where the corresponding table resides.\n",
    "tdCustomer = DataFrame(in_schema(\"<DBNAME>\", \"Customer\"))\n",
    "tdAccounts = DataFrame(in_schema(\"<DBNAME>\", \"Accounts\"))\n",
    "tdTransactions = DataFrame(in_schema(\"<DBNAME>\", \"Transactions\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Pre-Processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First, grab the customer demographic variables and create indicator variables for gender and marital_status.\n",
    "# For the purpose of multiple model fitting, we wish to create separate partitions of the states with the most\n",
    "# customers. Per the analysis in TechBytes Part 3 of the demo, we build 7 classes: We place the CA, NY, TX, IL, AZ,\n",
    "# and OH customers in individual classes, and everyboy else in a class 'OTHER'. We save these in column 'statecode'.\n",
    "cust = tdCustomer.assign(drop_columns = True,\n",
    "           cust_id = tdCustomer.cust_id,\n",
    "           income = tdCustomer.income,\n",
    "           age = tdCustomer.age,\n",
    "           gender = tdCustomer.gender,\n",
    "           years_with_bank = tdCustomer.years_with_bank,\n",
    "           nbr_children = tdCustomer.nbr_children,\n",
    "           marital_status = tdCustomer.marital_status,\n",
    "           state_code = tdCustomer.state_code,\n",
    "           female = case([(tdCustomer.gender == \"F\", 1)], else_ = 0 ),\n",
    "           single    = case( [(tdCustomer.marital_status == 1, 1)], else_ = 0 ),\n",
    "           married   = case( [(tdCustomer.marital_status == 2, 1)], else_ = 0 ),\n",
    "           separated = case( [(tdCustomer.marital_status == 3, 1)], else_ = 0 ),\n",
    "           statecode = case( [(tdCustomer.state_code == \"CA\", \"CA\"),\n",
    "                              (tdCustomer.state_code == \"NY\", \"NY\"),\n",
    "                              (tdCustomer.state_code == \"TX\", \"TX\"),\n",
    "                              (tdCustomer.state_code == \"IL\", \"IL\"),\n",
    "                              (tdCustomer.state_code == \"AZ\", \"AZ\"),\n",
    "                              (tdCustomer.state_code == \"OH\", \"OH\")],\n",
    "                             else_ = \"OTHER\" )\n",
    "                        )\n",
    "cust.to_pandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Next: Get the account information required for the aggregation, and create\n",
    "# the indicator variables for acct_type.\n",
    "acct_balance = tdAccounts.starting_balance + tdAccounts.ending_balance\n",
    "acct = tdAccounts.assign(ck_acct = case( [(tdAccounts.acct_type == \"CK\", 1)], else_ = 0 ),\n",
    "                         sv_acct = case( [(tdAccounts.acct_type == \"SV\", 1)], else_ = 0 ),\n",
    "                         cc_acct = case( [(tdAccounts.acct_type == \"CC\", 1)], else_ = 0 ),\n",
    "                         ck_bal = case( [(tdAccounts.acct_type == \"CK\", acct_balance.expression)], else_ = 0 ),\n",
    "                         sv_bal = case( [(tdAccounts.acct_type == \"SV\", acct_balance.expression)], else_ = 0 ),\n",
    "                         cc_bal = case( [(tdAccounts.acct_type == \"CC\", acct_balance.expression)], else_ = 0 )\n",
    "                        )\n",
    "acct.to_pandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Next: Get the transaction information required for the aggregation. Pull out the quarter the transaction was made.\n",
    "acct_mon = extract('month', tdTransactions.tran_date.expression).expression\n",
    "trans = tdTransactions.assign(q1_trans = case( [(acct_mon ==  \"1\", 1), (acct_mon ==  \"2\", 1), (acct_mon ==  \"3\", 1)], else_ = 0 ),\n",
    "                              q2_trans = case( [(acct_mon ==  \"4\", 1), (acct_mon ==  \"5\", 1), (acct_mon ==  \"6\", 1)], else_ = 0 ),\n",
    "                              q3_trans = case( [(acct_mon ==  \"7\", 1), (acct_mon ==  \"8\", 1), (acct_mon ==  \"9\", 1)], else_ = 0 ),\n",
    "                              q4_trans = case( [(acct_mon == \"10\", 1), (acct_mon == \"11\", 1), (acct_mon == \"12\", 1)], else_ = 0 ),\n",
    "                             )\n",
    "trans.to_pandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### The final task is to bring together the 3 transformed tables into an analytic data set.\n",
    "\n",
    "# Initially, we wish to left join the acct and trans on 'acct_nbr' into a acct_trans DataFrame.\n",
    "acct_trans_cols = ['cust_id', 'acct_type', 'starting_balance', 'ending_balance',\n",
    "                   'acct_acct_nbr', 'principal_amt', 'interest_amt', 'tran_id',\n",
    "                   'tran_date', 'q1_trans', 'q2_trans', 'q3_trans', 'q4_trans',\n",
    "                   'cc_acct', 'cc_bal', 'ck_acct', 'ck_bal', 'sv_acct', 'sv_bal']\n",
    "\n",
    "acct_trans_tmp = acct.join(other = trans,\n",
    "                           on = [acct.acct_nbr == trans.acct_nbr],\n",
    "                           how = \"left\", lsuffix = \"acct\", rsuffix = \"trans\").select(acct_trans_cols)\n",
    "\n",
    "# We create the target acct_trans DataFrame by also adding new columns with the\n",
    "# transaction amounts for each type of account, according to the following.\n",
    "acct_trans_amt = trans.principal_amt + trans.interest_amt\n",
    "\n",
    "acct_trans = acct_trans_tmp.assign(\n",
    "                 ck_tran_amt = case( [(acct_trans_tmp.acct_type == \"CK\", acct_trans_amt.expression)], else_ = 0 ),\n",
    "                 sv_tran_amt = case( [(acct_trans_tmp.acct_type == \"SV\", acct_trans_amt.expression)], else_ = 0 ),\n",
    "                 cc_tran_amt = case( [(acct_trans_tmp.acct_type == \"CC\", acct_trans_amt.expression)], else_ = 0 )\n",
    "                                  )\n",
    "acct_trans.to_pandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Then, perform a left outer join of acct_trans with cust on 'cust_id'.\n",
    "# This time, obtain the resulting table by dropping all the temporary table\n",
    "# columns and assigning from scratch the ones we want. For select columns,\n",
    "# use the case() function to replace any None occurrences with zeros.\n",
    "ADS_Py2_join_tmp = cust.join(other = acct_trans,\n",
    "                             on = [cust.cust_id == acct_trans.cust_id],\n",
    "                             how = \"left\", lsuffix = \"cust\", rsuffix = \"actr\")\n",
    "\n",
    "ADS_Py2_join = ADS_Py2_join_tmp.assign(drop_columns = True,\n",
    "                  cust_id = ADS_Py2_join_tmp.cust_cust_id,\n",
    "                  income = ADS_Py2_join_tmp.income,\n",
    "                  age = ADS_Py2_join_tmp.age,\n",
    "                  gender = ADS_Py2_join_tmp.gender,\n",
    "                  years_with_bank = ADS_Py2_join_tmp.years_with_bank,\n",
    "                  nbr_children = ADS_Py2_join_tmp.nbr_children,\n",
    "                  marital_status = ADS_Py2_join_tmp.marital_status,\n",
    "                  state_code = ADS_Py2_join_tmp.state_code,\n",
    "                  female = ADS_Py2_join_tmp.female,\n",
    "                  single = ADS_Py2_join_tmp.single,\n",
    "                  married = ADS_Py2_join_tmp.married,\n",
    "                  separated = ADS_Py2_join_tmp.separated,\n",
    "                  statecode = ADS_Py2_join_tmp.statecode,\n",
    "                  acct_type = ADS_Py2_join_tmp.acct_type,\n",
    "                  starting_balance = ADS_Py2_join_tmp.starting_balance,\n",
    "                  ending_balance = ADS_Py2_join_tmp.ending_balance,\n",
    "                  acct_nbr = ADS_Py2_join_tmp.acct_acct_nbr,\n",
    "                  principal_amt = ADS_Py2_join_tmp.principal_amt,\n",
    "                  interest_amt = ADS_Py2_join_tmp.interest_amt,\n",
    "                  tran_id = ADS_Py2_join_tmp.tran_id,\n",
    "                  tran_date = ADS_Py2_join_tmp.tran_date,\n",
    "                  q1_trans = case( [(ADS_Py2_join_tmp.q1_trans == None, 0)], else_ = ADS_Py2_join_tmp.q1_trans ),\n",
    "                  q2_trans = case( [(ADS_Py2_join_tmp.q2_trans == None, 0)], else_ = ADS_Py2_join_tmp.q2_trans ),\n",
    "                  q3_trans = case( [(ADS_Py2_join_tmp.q2_trans == None, 0)], else_ = ADS_Py2_join_tmp.q2_trans ),\n",
    "                  q4_trans = case( [(ADS_Py2_join_tmp.q2_trans == None, 0)], else_ = ADS_Py2_join_tmp.q2_trans ),\n",
    "                  ck_acct = case( [(ADS_Py2_join_tmp.ck_acct == None, 0)], else_ = ADS_Py2_join_tmp.ck_acct ),\n",
    "                  sv_acct = case( [(ADS_Py2_join_tmp.sv_acct == None, 0)], else_ = ADS_Py2_join_tmp.sv_acct ),\n",
    "                  cc_acct = case( [(ADS_Py2_join_tmp.cc_acct == None, 0)], else_ = ADS_Py2_join_tmp.cc_acct ),\n",
    "                  ck_bal = case( [(ADS_Py2_join_tmp.ck_bal == None, 0)], else_ = ADS_Py2_join_tmp.ck_bal ),\n",
    "                  sv_bal = case( [(ADS_Py2_join_tmp.sv_bal == None, 0)], else_ = ADS_Py2_join_tmp.sv_bal ),\n",
    "                  cc_bal = case( [(ADS_Py2_join_tmp.cc_bal == None, 0)], else_ = ADS_Py2_join_tmp.cc_bal ),\n",
    "                  ck_tran_amt = case( [(ADS_Py2_join_tmp.ck_tran_amt == None, 0)], else_ = ADS_Py2_join_tmp.ck_tran_amt ),\n",
    "                  sv_tran_amt = case( [(ADS_Py2_join_tmp.sv_tran_amt == None, 0)], else_ = ADS_Py2_join_tmp.sv_tran_amt ),\n",
    "                  cc_tran_amt = case( [(ADS_Py2_join_tmp.cc_tran_amt == None, 0)], else_ = ADS_Py2_join_tmp.cc_tran_amt )\n",
    "                                    )\n",
    "ADS_Py2_join.to_pandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Finally, aggregate and roll up by 'cust_id' all variables in the above join operation.\n",
    "#\n",
    "# This pulls everything together into the analytic data set.\n",
    "ADS_Py2 = ADS_Py2_join.groupby(\"cust_id\").agg(\n",
    "    {\n",
    "        \"income\" : \"min\",\n",
    "        \"age\" : \"min\",\n",
    "        \"years_with_bank\" : \"min\",\n",
    "        \"nbr_children\" : \"min\",\n",
    "        \"female\" : \"min\",\n",
    "        \"single\" : \"min\",\n",
    "        \"married\" : \"min\",\n",
    "        \"separated\" : \"min\",\n",
    "        \"statecode\" : \"min\",\n",
    "        \"ck_acct\" : \"max\",\n",
    "        \"sv_acct\" : \"max\",\n",
    "        \"cc_acct\" : \"max\",\n",
    "        \"ck_bal\" : \"mean\",\n",
    "        \"sv_bal\" : \"mean\",\n",
    "        \"cc_bal\" : \"mean\",\n",
    "        \"ck_tran_amt\" : \"mean\",\n",
    "        \"sv_tran_amt\" : \"mean\",\n",
    "        \"cc_tran_amt\" : \"mean\",\n",
    "        \"q1_trans\" : \"count\",\n",
    "        \"q2_trans\" : \"count\",\n",
    "        \"q3_trans\" : \"count\",\n",
    "        \"q4_trans\" : \"count\"\n",
    "    }\n",
    ")\n",
    "\n",
    "# Column names have been affected by aggregation. Assign final names.\n",
    "# Note: In teradataml, the rename() and column methods are unavailable yet.\n",
    "#       We use the assign() method combined with select() to achieve the task.\n",
    "columns = ['cust_id', 'tot_income', 'tot_age', 'tot_cust_years', 'tot_children',\n",
    "           'female_ind', 'single_ind', 'married_ind', 'separated_ind',\n",
    "           'statecode', 'ck_acct_ind', 'sv_acct_ind', 'cc_acct_ind',\n",
    "           'ck_avg_bal', 'sv_avg_bal', 'cc_avg_bal',\n",
    "           'ck_avg_tran_amt', 'sv_avg_tran_amt', 'cc_avg_tran_amt',\n",
    "           'q1_trans_cnt', 'q2_trans_cnt', 'q3_trans_cnt', 'q4_trans_cnt']\n",
    "\n",
    "ADS_Py2 = ADS_Py2.assign(drop_columns = True,\n",
    "                         cust_id         = ADS_Py2.cust_id,\n",
    "                         tot_income      = ADS_Py2.min_income,\n",
    "                         tot_age         = ADS_Py2.min_age,\n",
    "                         tot_cust_years  = ADS_Py2.min_years_with_bank,\n",
    "                         tot_children    = ADS_Py2.min_nbr_children,\n",
    "                         female_ind      = ADS_Py2.min_female,\n",
    "                         single_ind      = ADS_Py2.min_single,\n",
    "                         married_ind     = ADS_Py2.min_married,\n",
    "                         separated_ind   = ADS_Py2.min_separated,\n",
    "                         statecode       = ADS_Py2.min_statecode,\n",
    "                         ck_acct_ind     = ADS_Py2.max_ck_acct,\n",
    "                         sv_acct_ind     = ADS_Py2.max_sv_acct,\n",
    "                         cc_acct_ind     = ADS_Py2.max_cc_acct,\n",
    "                         ck_avg_bal      = ADS_Py2.mean_ck_bal,\n",
    "                         sv_avg_bal      = ADS_Py2.mean_sv_bal,\n",
    "                         cc_avg_bal      = ADS_Py2.mean_cc_bal,\n",
    "                         ck_avg_tran_amt = ADS_Py2.mean_ck_tran_amt,\n",
    "                         sv_avg_tran_amt = ADS_Py2.mean_sv_tran_amt,\n",
    "                         cc_avg_tran_amt = ADS_Py2.mean_cc_tran_amt,\n",
    "                         q1_trans_cnt    = ADS_Py2.count_q1_trans,\n",
    "                         q2_trans_cnt    = ADS_Py2.count_q2_trans,\n",
    "                         q3_trans_cnt    = ADS_Py2.count_q3_trans,\n",
    "                         q4_trans_cnt    = ADS_Py2.count_q4_trans).select(columns)\n",
    "\n",
    "# teradataml DataFrame creates views at the backend which are temporary. At the end of the context removal,\n",
    "# all temporary table/views perish. For this reason, persist the output of ADS_Py2 as a table in the\n",
    "# Advanced SQL Engine.\n",
    "# First, DROP the ADS_Py2 table, if it previously exists.\n",
    "\n",
    "# Before you execute each one of the following statements, replace the variable\n",
    "# <DBNAME> with the target Vantage system database name where the corresponding\n",
    "# table resides.\n",
    "try:\n",
    "    get_context().execute(\"DROP TABLE <DBNAME>.ADS_Py2\")\n",
    "except:\n",
    "    pass\n",
    "\n",
    "copy_to_sql(ADS_Py2, schema_name=\"<DBNAME>\", table_name=\"ADS_Py2\", if_exists=\"replace\")\n",
    "\n",
    "# Create a DataFrame and take a glimpse at it.\n",
    "tdADS_Py2 = DataFrame(in_schema(\"<DBNAME>\", \"ADS_Py2\"))\n",
    "tdADS_Py2.to_pandas().head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create training and testing datasets from the persisted table 'ADS_Py2'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split the analytic data set into training and testing data sets (60/40%)\n",
    "# with the sample() function.\n",
    "# Before you execute each one of the following statements, replace the variable\n",
    "# <DBNAME> with the target Vantage system database name where the corresponding\n",
    "# table resides.\n",
    "ADS_Train_Test2 = tdADS_Py2.sample(frac = [0.60, 0.40])\n",
    "\n",
    "try:\n",
    "    get_context().execute(\"DROP TABLE <DBNAME>.ADS_Train_Test2\")\n",
    "except:\n",
    "    pass\n",
    "\n",
    "copy_to_sql(ADS_Train_Test2, schema_name=\"<DBNAME>\", table_name=\"ADS_Train_Test2\", if_exists=\"replace\")\n",
    "\n",
    "# Create a DataFrame from_query() and take a glimpse at it\n",
    "tdTrain_Test2 = DataFrame(in_schema(\"<DBNAME>\", \"ADS_Train_Test2\"))\n",
    "tdTrain_Test2.to_pandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use the 60% sample to train\n",
    "\n",
    "MultiModelTrain_Py = tdTrain_Test2[tdTrain_Test2.sampleid == \"1\"]\n",
    "# Persist the MultiModelTrain_Py dataset\n",
    "# Before you execute each one of the following statements, replace the variable\n",
    "# <DBNAME> with the target Vantage system database name where the corresponding\n",
    "# table resides.\n",
    "try:\n",
    "    get_context().execute(\"DROP TABLE <DBNAME>.MultiModelTrain_Py\")\n",
    "except:\n",
    "    pass\n",
    "\n",
    "copy_to_sql(MultiModelTrain_Py, schema_name=\"<DBNAME>\", table_name=\"MultiModelTrain_Py\", if_exists = \"replace\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use the 40% sample to test/score\n",
    "\n",
    "MultiModelTest_Py = tdTrain_Test2[tdTrain_Test2.sampleid == \"2\"]\n",
    "# Persist the MultiModelTest_Py dataset\n",
    "# Before you execute each one of the following statements, replace the variable\n",
    "# <DBNAME> with the target Vantage system database name where the corresponding\n",
    "# table resides.\n",
    "try:\n",
    "    get_context().execute(\"DROP TABLE <DBNAME>.MultiModelTest_Py\")\n",
    "except:\n",
    "    pass\n",
    "\n",
    "copy_to_sql(MultiModelTest_Py, schema_name=\"<DBNAME>\", table_name=\"MultiModelTest_Py\", if_exists = \"replace\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## End of session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove the context, i.e., end the python session.\n",
    "# One should call remove_context() for session cleanup. Temporary objects are removed at the end of the session.\n",
    "\n",
    "from teradataml import remove_context\n",
    "remove_context()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# At this point, we have created the training and testing/scoring tables\n",
    "# in the target Vantage server in database <DBNAME> that we need for the rest\n",
    "# of this use case.\n",
    "# You can now review the Python scripts \"stoRFFitMM.py\" (for fitting) and\n",
    "# \"stoRFScoreMM.py\" (for scoring) that are distributed with the present\n",
    "# TechBytes demo material.\n",
    "# Note: Save the full path of the location where these script files are stored.\n",
    "#       You will need to specify these paths later in the SQL script\n",
    "#       \"R_Py_TechBytes-Part_5-Demo.sql\" that calls the SCRIPT Table Operator.\n",
    "#       The SQL script file\n",
    "#       -- refers to the fitting script path variable as <mmfitscrPATH>\n",
    "#       -- refers to the scoring script path variable as <mmscoscrPATH>\n",
    "#       As advised in USE CASE [1] Section 1, speficy the path names suitably\n",
    "#       for your platform.\n",
    "#       For example, on Linux platforms specify paths like:\n",
    "#         mmfitscrPATH = /home/me/Path/To/stoRFFitMM.py\n",
    "#         mmscoscrPATH = /home/me/Path/To/stoRFScoreMM.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *USE CASE [2] - Section 2: Build multiple models (in database; uses SCRIPT)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fitting models with the training dataset takes place in the target\n",
    "# Vantage system Advanced SQL Engine. To perform this task, inspect and\n",
    "# execute the code in the \"Use Case [2]\" section of the SQL script file\n",
    "# \"R_Py_TechBytes-Part_5-Demo.sql\" as guided in the file.\n",
    "#\n",
    "# In a few places across the SQL script file \"R_Py_TechBytes-Part_5-Demo.sql\"\n",
    "# you will need to specify the database where your Python fitting and scoring\n",
    "# script files will reside.\n",
    "#\n",
    "# Then, focus on \"Part (A): Model fitting\" of the \"Use Case [2]\" section.\n",
    "# You can execute the SQL code in a SQL interpreter such as Teradata Studio,\n",
    "# by connecting to the target Vantage system and the database <DBNAME> where\n",
    "# the training and scoring tables reside.\n",
    "# The code in the SQL script file demonstrates using Python with the SCRIPT\n",
    "# Table Operator. The SQL script needs to know the location of the fitting\n",
    "# Python script \"stoRFFitMM.py\" file. For this fitting task, the SCRIPT Table\n",
    "# Operator uses as input the \"MultiModelTrain_Py\" table with the training data.\n",
    "# The output is the \"RFStateCodeModelsPy\" table that contains the fitted models\n",
    "# for all state codes in the use case."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *USE CASE [2] - Section 3: Score multiple models (in database; uses SCRIPT)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Following up from the previous Section 2, scoring the test dataset with the\n",
    "# saved models in the table \"RFStateCodeModelsPy\" also takes place in the\n",
    "# target Vantage system Advanced SQL Engine. To perform this task, review and\n",
    "# execute the code in the SQL script file \"R_Py_TechBytes-Part_5-Demo.sql\".\n",
    "# Specifically, use the code in the \"Use Case [2]\" section of the SQL script\n",
    "# file, and focus on \"Part (B): Scoring with models\".\n",
    "# You can execute the SQL code in a SQL interpreter such as Teradata Studio,\n",
    "# by connecting to the target Vantage system and the database <DBNAME> where\n",
    "# the training and scoring tables reside.\n",
    "# The code in the SQL script file demonstrates again using Python with the\n",
    "# SCRIPT Table Operator. The SQL script needs to know the location of the\n",
    "# scoring Python script \"stoRFFitMM.py\" file. For this scoring task, the\n",
    "# SCRIPT Table Operator uses as input the table \"MultiModelTest_Py\" with the\n",
    "# test/score data, in addition to the \"RFStateCodeModelsPy\" table that contins\n",
    "# the information about the models in all state codes.\n",
    "# The SCRIPT Table Operator output is rows of the sought information about the\n",
    "# propensity, partitioned by state code, of the financial services customers\n",
    "# in the test table to open a credit card account."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}