################################################################################
# * The contents of this file are Teradata Public Content and have been released
# * to the Public Domain.
# * Tim Miller & Alexander Kolovos - October 2019 - v.1.0
# * Copyright (c) 2019 by Teradata
# * Licensed under BSD; see "license.txt" file in the bundle root folder.
#
# ##############################################################################
# R and Python TechBytes Demo - Part 5: Python in-nodes with SCRIPT
# ------------------------------------------------------------------------------
# File: R_Py_TechBytes-Part_5-Demo.py
# ------------------------------------------------------------------------------
# The R and Python TechBytes Demo comprises of 5 parts:
# Part 1 consists of only a Powerpoint overview of R and Python in Vantage
# Part 2 demonstrates the Teradata R package tdplyr for clients
# Part 3 demonstrates the Teradata Python package teradataml for clients
# Part 4 demonstrates using R in-nodes with the SCRIPT and ExecR Table Operators
# Part 5 demonstrates using Python in-nodes with the SCRIPT Table Operator
# ##############################################################################
#
# This TechBytes demo utilizes a use case to predict the propensity of a
# financial services customer base to open a credit card account.
#
# The present demo Part 5 showcases two scenarios: We show the 2 use case types
# that are most broadly used with the Vantage SCRIPT table operator:
#
# 1) Fitting and scoring a single model
#
#    We take a 25% sample extracted from the database and build a Random
#    Forest model on a Python client. This Random Forest model is then saved
#    as an encoded serialized file and installed back on the Vantage Advanced
#    SQL Engine.
#    We formulate a scoring script and execute this on the Advanced SQL Engine
#    using the SCRIPT table operator to score the entire data set.
#
#    For this use case, we start from the same analytic data set that was
#    used in the teradataml demo (Part 3).
#
# 2) Fitting and scoring multiple models
#
#    We utilize the statecode variable as a partition to built a Random
#    Forest model for every state. This is done by using SCRIPT Table Operator
#    to run a model fitting script with a PARTITION BY statecode in the query.
#    This creates a model for each of the CA, NY, TX, IL, AZ, OH and Other
#    state codes, and perists the model in the database via CREATE TABLE AS
#    statement.
#    Then we run a scoring script via the SCRIPT Table Operator against
#    these persisted Random Forest models to score the entire data set.
#
#    For this use case, we build an analytic data set nearly identical to the
#    one in the teradataml demo (Part 3), with one change as indicated by item
#    (d) below. This is so we can demonstrate the in-database capability of
#    simultaneously building many models.
#    Various features will be generated by joining and aggregating from three
#    Vantage-based tables (10K customers, 100K accounts, 1M+ transactions) into
#    an analytic data set. We show how to use teradataml functions to do the
#    following:
#
#    a) Pull through the cust_id, income, age, years_with_bank, nbr_children
#       from the Customer table.
#    b) Create a gender indicator variable (female_ind) from gender in the
#       Customer table.
#    c) Create marital status indicator variables (single_ind, married_ind,
#       separated_ind) from marital_status in the Customer table.
#    d) Recode the state_code variable into, CA, NY, TX, IL, AZ, OH and Other
#       in Customer table.  This replaces the location indicator variables
#       (ca_resident, ny_resident, tx_resident, il_resident, az_resident,
#       oh_resident) in the Analytic Data Set used in the tdplyr demo.
#    e) Create account indicator variables (ck_acct_ind, cc_acct_ind,
#       sv_acct_ind) from acct_type in the Account table.
#    f) Create average balance variables (ck_avg_bal, cc_avg_bal, sv_avg_bal)
#       by taking the mean of the beginning_balance and ending_balance in the
#       Account table.
#    g) Create average transaction amounts (ck_avg_tran_amt, cc_avg_tran_amt,
#       sv_avg_tran_amt) by taking the average of the principal_amt and
#       interest_amt in the Transactions table.
#    h) Create quarterly transaction counts (q1_nbr_trans, q2_nbr_trans,
#       q3_nbr_trans, q4_nbr_trans) by taking the count of tran_id's based upon
#       tran_date in the Transactions table.
#
#    Then we sample 60% of the analytic data set rows to create a training
#    subset. The remaining 40% is used to create a testing/scoring dataset.
#    The train and test/score datasets are used in the SCRIPT operations.
################################################################################

# Load teradataml and dependency packages to use in both use cases.
# For use case [1], also load the sklearn package to create a random forest
# model on the client.
from teradataml import create_context, DataFrame, get_context, copy_to_sql, in_schema
from sqlalchemy.sql.expression import select, or_, extract, text, join, case as case_when
from sklearn.ensemble import RandomForestClassifier
import pandas as pd
import numpy as np
import pickle
import base64

###
### Connection
###

# Establish a connection to Teradata Vantage server with the Teradata SQL Driver
# for Python. Before you execute the following statement, replace the variables
# <HOSTNAME>, <UID>, and <PWD> with the target Vantage system hostname, your
# database user ID, and password, respectively.
td_context = create_context(host="<HOSTNAME>", username="<UID>", password="<PWD>")


################################################################################
################################################################################
# USE CASE [1]: Single model fitting and scoring example
################################################################################
################################################################################

################################################################################
# USE CASE [1] - Section 1: Fit and save model (on client)
#                Note: To proceed with the present section, ensure you have
#                      first executed Section 1 of the R and Python TechBytes
#                      Demo - Part 3. You will need to have the Analytic Data
#                      Set (ADS) ADS_Py table in the target Vantage system.
################################################################################

###
### Create a training dataset from the persisted table 'ADS_Py'
###

# Select a 25% sample of the analytic data set to use for model fitting
# Note: Upcoming teradataml release will feature a Sample API to assist in
#       sampling tasks without the need for SQL statements.

# Before you execute the following statement, replace the variable <DBNAME>
# with the target Vantage system database name where 'ADS_Py' resides.
ADS_Train = "SELECT * FROM <DBNAME>.ADS_Py SAMPLE .25"

# Ceate a DataFrame with from_query() and take a glimpse at it
tdTrain = DataFrame.from_query(ADS_Train)

# Convert to pandas DataFrame to enable subsequent DataFrame operations
tdTrain_df = tdTrain.to_pandas()
tdTrain_df.head()


###
### Classification model training with Random Forests
###

# Determine the columns that the predictor accounts for
predictor_columns = ["tot_income", "tot_age", "tot_cust_years", "tot_children",
                     "female_ind", "single_ind", "married_ind", "separated_ind",
                     "ck_acct_ind", "sv_acct_ind", "ck_avg_bal", "sv_avg_bal",
                     "ck_avg_tran_amt", "sv_avg_tran_amt", "q1_trans_cnt",
                     "q2_trans_cnt", "q3_trans_cnt", "q4_trans_cnt"]

# Note: At time of creation of this TechByte, in-nodes Python has RF classifier
#       from scikit-learn package v.0.20.3. Keep an eye for potential
#       incompatibilites, if a package version on your client differs from the
#       in-nodes.
#       In the present TechByte, the client carries scikit-learn package
#       v.0.21.2. No issues were observed when using a model built with this
#       later version for in-nodes scoring.
#       In case errors may be produced due to different scikit-learn versions
#       on the client and in-nodes, you can try switching your client's
#       scikit-learn version to match in-nodes (by using the command
#       "pip install scikit-learn==<version>").

# For the classifier, specify the following parameters:
# ntree: n_estimators=500, mtry: max_features=5, nodesize: min_samples_leaf=1
# (default; skipped)

classifier = RandomForestClassifier(n_estimators=500, max_features=5, random_state=0)
X = tdTrain_df[predictor_columns]
y = tdTrain_df["cc_acct_ind"]

# Train the Random Forest model to predict Credit Card account ownership based
# upon specified independent variables.
classifier = classifier.fit(X, y)


###
### Exporting the Random Forest model
###

# Save model into a file; we name it "RFmodel_py.out".
# Note: In the following, we are using both the libraries pickle (to serialize)
#       and base64 (to encode) the model prior to saving it into a file. If
#       model is only pickled, then unplickling in database might produce a
#       pickle AttributeError that claims an "X object has no attribute Y".
#       This is related to namespaces in client and target systems.
#       For some more insight, see:
#       https://docs.python.org/3/library/pickle.html#pickling-class-instances
classifierPkl = pickle.dumps(classifier)
classifierPklB64 = base64.b64encode(classifierPkl)
with open('RFmodel_py.out', 'wb') as fOut   # Use "wb" to write in binary format
    fOut.write(classifierPklB64)

# The saved model will then need to be installed on the target Vantage system
# (see following Section 2) together with the scoring script in file
# "stoRFScore.py" so you can perform the scoring operation with the SCRIPT
# Table Operator.
#
# Before you proceed to Section 2, look up the full path of the location
# where the model file is saved on your client machine. Let us call this
# path <modelPATH>. You will need to specify the <modelPath> value later
# in Section 2 within the SQL script file "R_Py_TechBytes-Part_5-Demo.sql"
# that calls the SCRIPT Table Operator.
# Speficy the <modelPath> suitably for your platform. Here are some example
# <modelPATH> strings for different client platforms:
# On Windows OS, specify a path like: "C:\\Users\\me\\Path\\To\\RFmodel_py.out"
# On MacOS, specify a path like     : "/Users/me/Path/To/RFmodel_py.out"
# On Linux, specify a path like     : "/home/me/Path/To/RFmodel_py.out"
#
# At this point, you can review the scoring script file "stoRFScore.py" that is
# distributed with the present TechBytes demo material.
# Similarly to the model path, also look up the full path of the script file
# location. Let us call this path as the <scriptPath>. You will need to specify
# the <scriptPath> value, too, later in the "R_Py_TechBytes-Part_5-Demo.sql"
# SQL script file.
# Like for the model path, speficy the <scriptPath> suitably for your platform.
# For example, on Linux specify a path like: "/home/me/Path/To/stoRFScore.py"

################################################################################
# USE CASE [1] - Section 2: Score with model (in database; uses SCRIPT)
################################################################################

# Scoring the test dataset with the saved model takes place in the target
# Vantage system Advanced SQL Engine. To perform this task, simply review and
# execute the code in the SQL script file "R_Py_TechBytes-Part_5-Demo.sql".
#
# In a few places across the SQL script file "R_Py_TechBytes-Part_5-Demo.sql"
# you will need to specify the database where your model and Python script
# files will reside. You will then also need to edit the scoring script file
# "stoRFScore.py" to specify the same database name where needed, so that the
# scoring script can find the model.
#
# Use only the code in the "Use Case [1]" section of the SQL script file.
# You can execute the SQL code in a SQL interpreter such as Teradata Studio,
# by connecting to the target Vantage system and the database <DBNAME> where
# the testing/scoring dataset "ADS_Py" resides.
# The above SQL script file demonstrates using Python with the SCRIPT Table
# Operator. The SQL script needs to know the location of the files that contain
# the Random Forest model that was created in the preceding Section 1, and the
# scoring Python script "stoRFScore.py". For this scoring task, the SCRIPT
# Table Operator uses as input the testing/scoring table "ADS_Py". The output
# of this scoring task is the sought information about the propensity of the
# financial services customers in the test table to open a credit card account.

################################################################################
################################################################################
# END OF USE CASE [1]: Single model scoring example
################################################################################
################################################################################


################################################################################
################################################################################
# USE CASE [2]: Multiple models fitting and scoring example
################################################################################
################################################################################

################################################################################
# USE CASE [2] - Section 1: Transform data and create model and test data sets
################################################################################

def processColumnToReplaceNullWithZero(column_expr, column_label):
    """
    DESCRIPTION:
        Function process SQLAlchemy column expression to replace NULL values with zeros,
        else keep the value as is.

    PARAMETERS:
        column_expr:
            Required Argument.
            SQLAlchemy column expression.

        column_label:
            Required Argument.
            New name of the processed column.

    RETURNS:
         SQLAlchemy Case object.
    """
    return case_when([(column_expr == None, 0)], else_=column_expr).expression.label(column_label)

# Create DataFrames for the Customer, Accounts and Transactions tables in the
# Vantage Advanced SQL Engine.
# Note: Use in_schema() function if tables reside in database other than
# connecting user's default database.

# Before you execute each one of the following statements, replace the variable
# <DBNAME> with the target Vantage system database name where the corresponding
# table resides.
tdCustomer = DataFrame(in_schema("<DBNAME>", "Customer"))
tdAccounts = DataFrame(in_schema("<DBNAME>", "Accounts"))
tdTransactions = DataFrame(in_schema("<DBNAME>", "Transactions"))

###
### Data Pre-Processing
###

# First, grab the customer demographic variables and create indicator variables
# for gender and marital_status. For the purpose of multiple model fitting,
# we wish to create separate partitions of the states with the most customers.
# Per the analysis in TechBytes Part 3 of the demo, we build 7 classes: We place
# the CA, NY, TX, IL, AZ, and OH customers in individual classes, and everyboy
# else in a class 'OTHER'. We save these in column 'statecode'.
# We construct a list of columns to be projected in SQL by using the SQLAlchemy
# objects 'Column' and 'Case'.
# Note: The case_when() function is from SQLAlchemy. In the future, it is
#       planned to have this function implemented in teradataml, too.
cust_select_query_column_projection = [tdCustomer.cust_id.expression,
                                       tdCustomer.income.expression,
                                       tdCustomer.age.expression,
                                       tdCustomer.gender.expression,
                                       tdCustomer.years_with_bank.expression,
                                       tdCustomer.nbr_children.expression,
                                       tdCustomer.marital_status.expression,
                                       tdCustomer.state_code.expression,
                                       case_when([(tdCustomer.gender.expression == "F", 1)], else_=0).expression.label("female"),
                                       case_when([(tdCustomer.marital_status.expression == "1", 1)], else_=0).expression.label("single"),
                                       case_when([(tdCustomer.marital_status.expression == "2", 1)], else_=0).expression.label("married"),
                                       case_when([(tdCustomer.marital_status.expression == "3", 1)], else_=0).expression.label("separated"),
                                       case_when(
                                           [
                                               (tdCustomer.state_code.expression == "CA", "CA"),
                                               (tdCustomer.state_code.expression == "NY", "NY"),
                                               (tdCustomer.state_code.expression == "TX", "TX"),
                                               (tdCustomer.state_code.expression == "IL", "IL"),
                                               (tdCustomer.state_code.expression == "AZ", "AZ"),
                                               (tdCustomer.state_code.expression == "OH", "OH")
                                           ], else_="OTHER").expression.label("statecode")
                                       ]
# The above is the column projection list that contains SQLAlchmey expressions.
# The following statement constructs the SQL from the list, and uses it to
# create a DataFrame.
cust = DataFrame.from_query(str(select(cust_select_query_column_projection).compile(compile_kwargs={"literal_binds": True})))
# Using to_pandas() for a cleaner display format:
cust.to_pandas().head(10)

# Next: Get the account information required for the aggregation, and create
# the indicator variables for acct_type.
acct_balance = tdAccounts.starting_balance + tdAccounts.ending_balance
acct_select_query_column_projection = [tdAccounts.cust_id.expression,
                                       tdAccounts.acct_type.expression,
                                       tdAccounts.starting_balance.expression,
                                       tdAccounts.ending_balance.expression,
                                       tdAccounts.acct_nbr.expression,
                                       case_when(
                                           [
                                               (
                                                   tdAccounts.acct_type.expression == "CK", 1
                                               )
                                           ], else_=0).expression.label("ck_acct"),
                                       case_when(
                                           [
                                               (
                                                   tdAccounts.acct_type.expression == "SV", 1
                                               )
                                           ], else_=0).expression.label("sv_acct"),
                                       case_when(
                                           [
                                               (
                                                   tdAccounts.acct_type.expression == "CC", 1
                                               )
                                           ], else_=0).expression.label("cc_acct"),
                                       case_when(
                                           [
                                               (
                                                   tdAccounts.acct_type.expression == "CK", acct_balance.expression
                                               )
                                           ], else_=0).expression.label("ck_bal"),
                                       case_when(
                                           [
                                               (
                                                   tdAccounts.acct_type.expression == "SV", acct_balance.expression
                                               )
                                           ], else_=0).expression.label("sv_bal"),
                                       case_when(
                                           [
                                               (
                                                   tdAccounts.acct_type.expression == "CC", acct_balance.expression
                                               )
                                           ], else_=0).expression.label("cc_bal")
                                       ]
acct = DataFrame.from_query(str(select(acct_select_query_column_projection).compile(compile_kwargs={"literal_binds": True})))
# Using to_pandas() for a cleaner display format:
acct.to_pandas().head(10)

# Next: Get the transaction information required for the aggregation. Pull out
# the quarter the transaction was made.
trans_select_query_column_projection = [tdTransactions.acct_nbr.expression,
                                        tdTransactions.principal_amt.expression,
                                        tdTransactions.interest_amt.expression,
                                        tdTransactions.tran_id.expression,
                                        tdTransactions.tran_date.expression,
                                        extract('month', tdTransactions.tran_date.expression).expression.label("acct_mon"),
                                        case_when(
                                            [
                                                (
                                                    or_(
                                                        text("acct_mon = '1'"), text("acct_mon = '2'"), text("acct_mon = '3'"),
                                                    ), 1
                                                )
                                            ], else_=0).expression.label("q1_trans"),
                                        case_when(
                                            [
                                                (
                                                    or_(
                                                        text("acct_mon = '4'"), text("acct_mon = '5'"), text("acct_mon = '6'"),
                                                    ), 1
                                                )
                                            ], else_=0).expression.label("q2_trans"),
                                        case_when(
                                            [
                                                (
                                                    or_(
                                                        text("acct_mon = '7'"), text("acct_mon = '8'"), text("acct_mon = '9'"),
                                                    ), 1
                                                )
                                            ], else_=0).expression.label("q3_trans"),
                                        case_when(
                                            [
                                                (
                                                    or_(
                                                        text("acct_mon = '10'"), text("acct_mon = '11'"), text("acct_mon = '12'"),
                                                    ), 1
                                                )
                                            ], else_=0).expression.label("q4_trans")
                                        ]

trans = DataFrame.from_query(str(select(trans_select_query_column_projection).compile(compile_kwargs={"literal_binds": True})))
# Using to_pandas() for a cleaner display format:
trans.to_pandas().head(10)

### The final task is to bring together the 3 transformed tables

# Initially, we wish to left join the acct and trans on 'acct_nbr' into a
# acct_trans DataFrame.
#
# We construct the list of all column expressions to be projected. In this
# process, we create new columns with the transaction amounts as follows:
acct_trans_amt = trans.principal_amt + trans.interest_amt
acct_trans_join_select_query_column_projection = [acct.cust_id.expression,
                                                  acct.acct_type.expression,
                                                  acct.starting_balance.expression,
                                                  acct.ending_balance.expression,
                                                  acct.acct_nbr.expression,
                                                  trans.principal_amt.expression,
                                                  trans.interest_amt.expression,
                                                  trans.tran_id.expression,
                                                  trans.tran_date.expression,
                                                  trans.acct_mon.expression,
                                                  trans.q1_trans.expression,
                                                  trans.q2_trans.expression,
                                                  trans.q3_trans.expression,
                                                  trans.q4_trans.expression,
                                                  acct.ck_acct.expression,
                                                  acct.sv_acct.expression,
                                                  acct.cc_acct.expression,
                                                  acct.ck_bal.expression,
                                                  acct.sv_bal.expression,
                                                  acct.cc_bal.expression,
                                                  case_when(
                                                      [
                                                          (
                                                              acct.acct_type.expression == 'CK', acct_trans_amt.expression
                                                          )
                                                      ], else_=0).expression.label("ck_tran_amt"),
                                                  case_when(
                                                      [
                                                          (
                                                              acct.acct_type.expression == 'SV', acct_trans_amt.expression
                                                          )
                                                      ], else_=0).expression.label("sv_tran_amt"),
                                                  case_when(
                                                      [
                                                          (
                                                              acct.acct_type.expression == 'CC', acct_trans_amt.expression
                                                          )
                                                      ], else_=0).expression.label("cc_tran_amt")
                                                  ]

# Construct SQL left outer join from clause statement by using the SQLAlchemy
# join() function.
join_tbl_sqlalcmy = join(acct.acct_nbr.table, trans.acct_nbr.table, acct.acct_nbr.expression == trans.acct_nbr.expression, isouter=True)

# Let us construct the SQL from the same and use it to create a DataFrame.
acct_trans = DataFrame.from_query(str(select(acct_trans_join_select_query_column_projection).select_from(join_tbl_sqlalcmy).compile(compile_kwargs={"literal_binds": True})))
acct_trans.to_pandas().head(10)

# Then, perform a left outer join of acct_trans with cust on 'cust_id'.
#
# Now, we can use teradataml join API to join the two DataFrames, but we also
# want to process few columns in a way that, if the column contains NULL, then
# replace it with zero. To do so, we need a CASE object; for this reason, we
# choose to use SQLAlchemy-style column selection and join operation. In the
# following, we are using our DataFrame objects to pass ColumnExpression's
# expression, which is SQLAlchmey Column object.
ADS_Py2_join_select_query_column_projection = [cust.cust_id.expression,
                                               cust.income.expression,
                                               cust.age.expression,
                                               cust.gender.expression,
                                               cust.years_with_bank.expression,
                                               cust.nbr_children.expression,
                                               cust.marital_status.expression,
                                               cust.state_code.expression,
                                               cust.female.expression,
                                               cust.single.expression,
                                               cust.married.expression,
                                               cust.separated.expression,
                                               cust.statecode.expression,
                                               acct_trans.acct_type.expression,
                                               acct_trans.starting_balance.expression,
                                               acct_trans.ending_balance.expression,
                                               acct_trans.acct_nbr.expression,
                                               acct_trans.principal_amt.expression,
                                               acct_trans.interest_amt.expression,
                                               acct_trans.tran_id.expression,
                                               acct_trans.tran_date.expression,
                                               acct_trans.acct_mon.expression,
                                               processColumnToReplaceNullWithZero(acct_trans.q1_trans.expression, "q1_trans"),
                                               processColumnToReplaceNullWithZero(acct_trans.q2_trans.expression, "q2_trans"),
                                               processColumnToReplaceNullWithZero(acct_trans.q3_trans.expression, "q3_trans"),
                                               processColumnToReplaceNullWithZero(acct_trans.q4_trans.expression, "q4_trans"),
                                               processColumnToReplaceNullWithZero(acct_trans.ck_acct.expression, "ck_acct"),
                                               processColumnToReplaceNullWithZero(acct_trans.sv_acct.expression, "sv_acct"),
                                               processColumnToReplaceNullWithZero(acct_trans.cc_acct.expression, "cc_acct"),
                                               processColumnToReplaceNullWithZero(acct_trans.ck_bal.expression, "ck_bal"),
                                               processColumnToReplaceNullWithZero(acct_trans.sv_bal.expression, "sv_bal"),
                                               processColumnToReplaceNullWithZero(acct_trans.cc_bal.expression, "cc_bal"),
                                               processColumnToReplaceNullWithZero(acct_trans.ck_tran_amt.expression, "ck_tran_amt"),
                                               processColumnToReplaceNullWithZero(acct_trans.sv_tran_amt.expression, "sv_tran_amt"),
                                               processColumnToReplaceNullWithZero(acct_trans.cc_tran_amt.expression, "cc_tran_amt")
                                              ]

# Construct SQL left outer join from clause statement by using the SQLAlchemy
# join() function.
join_cust_acct_trans_tbl_sqlalcmy = join(cust.cust_id.table, acct_trans.cust_id.table, cust.cust_id.expression == acct_trans.cust_id.expression, isouter=True)

# Let us construct the SQL from the same and use it to create a DataFrame.
ADS_Py2_join = DataFrame.from_query(str(select(ADS_Py2_join_select_query_column_projection).select_from(join_cust_acct_trans_tbl_sqlalcmy).compile(compile_kwargs={"literal_binds": True})))
ADS_Py2_join.to_pandas().head(10)

# Finally, aggregate and roll up by 'cust_id' all variables in the above join
# operation.
#
# This pulls everything together into the analytic data set.
ADS_Py2 = ADS_Py2_join.groupby("cust_id").agg(
    {
        "income" : "min",
        "age" : "min",
        "years_with_bank" : "min",
        "nbr_children" : "min",
        "single" : "min",
        "female" : "min",
        "married" : "min",
        "separated" : "min",
        "statecode" : "min",
        "ck_acct" : "max",
        "sv_acct" : "max",
        "cc_acct" : "max",
        "ck_bal" : "mean",
        "sv_bal" : "mean",
        "cc_bal" : "mean",
        "ck_tran_amt" : "mean",
        "sv_tran_amt" : "mean",
        "cc_tran_amt" : "mean",
        "q1_trans" : "count",
        "q2_trans" : "count",
        "q3_trans" : "count",
        "q4_trans" : "count"
    }
)

# Column names have been affected by aggregation. Assign final names.
# Note: In teradataml, the rename() and column methods are unavailable yet.
#       We use the assign() method combined with select() to achieve the task.
columns = ['cust_id', 'tot_income', 'tot_age', 'tot_cust_years', 'tot_children',
           'female_ind', 'single_ind', 'married_ind', 'separated_ind',
           'statecode', 'ck_acct_ind', 'sv_acct_ind', 'cc_acct_ind',
           'ck_avg_bal', 'sv_avg_bal', 'cc_avg_bal',
           'ck_avg_tran_amt', 'sv_avg_tran_amt', 'cc_avg_tran_amt',
           'q1_trans_cnt', 'q2_trans_cnt', 'q3_trans_cnt', 'q4_trans_cnt']

ADS_Py2 = ADS_Py2.assign(drop_columns = True,
                         cust_id         = ADS_Py2.cust_id,
                         tot_income      = ADS_Py2.min_income,
                         tot_age         = ADS_Py2.min_age,
                         tot_cust_years  = ADS_Py2.min_years_with_bank,
                         tot_children    = ADS_Py2.min_nbr_children,
                         female_ind      = ADS_Py2.min_female,
                         single_ind      = ADS_Py2.min_single,
                         married_ind     = ADS_Py2.min_married,
                         separated_ind   = ADS_Py2.min_separated,
                         statecode       = ADS_Py2.min_statecode,
                         ck_acct_ind     = ADS_Py2.max_ck_acct,
                         sv_acct_ind     = ADS_Py2.max_sv_acct,
                         cc_acct_ind     = ADS_Py2.max_cc_acct,
                         ck_avg_bal      = ADS_Py2.mean_ck_bal,
                         sv_avg_bal      = ADS_Py2.mean_sv_bal,
                         cc_avg_bal      = ADS_Py2.mean_cc_bal,
                         ck_avg_tran_amt = ADS_Py2.mean_ck_tran_amt,
                         sv_avg_tran_amt = ADS_Py2.mean_sv_tran_amt,
                         cc_avg_tran_amt = ADS_Py2.mean_cc_tran_amt,
                         q1_trans_cnt    = ADS_Py2.count_q1_trans,
                         q2_trans_cnt    = ADS_Py2.count_q2_trans,
                         q3_trans_cnt    = ADS_Py2.count_q3_trans,
                         q4_trans_cnt    = ADS_Py2.count_q4_trans).select(columns)

# teradataml DataFrame creates views at the backend which are temporary. At the
# end of the context removal, all temporary table/views perish. For this reason,
# persist the output of ADS_Py2 as a table in the Advanced SQL Engine.
# First, DROP the ADS_Py2 table, if it previously exists.

# Before you execute each one of the following statements, replace the variable
# <DBNAME> with the target Vantage system database name where the corresponding
# table resides.
try:
    get_context().execute("DROP TABLE <DBNAME>.ADS_Py2")
except:
    pass
copy_to_sql(ADS_Py2, schema_name="<DBNAME>", table_name="ADS_Py2", if_exists="replace")

# Create a DataFrame and take a glimpse at it.
tdADS_Py2 = DataFrame(in_schema("<DBNAME>", "ADS_Py2"))
tdADS_Py2.to_pandas().head(10)

###
### Create training and testing datasets from the persisted table 'ADS_Py2'
###

# Split the analytic data set into training and testing data sets (60/40%)
# Note: A future version of teradataml will feature a Sample API for sampling
# tasks so that SQL can be avoided.

# Before you execute the following statement, replace the variable <DBNAME> with
# the target Vantage system database name where the corresponding table resides.
ADS_Train_Test2 = "SELECT cust_id\
                          ,tot_income\
                          ,tot_age\
                          ,tot_cust_years\
                          ,tot_children\
                          ,female_ind\
                          ,single_ind\
                          ,married_ind\
                          ,separated_ind\
                          ,statecode\
                          ,ck_acct_ind\
                          ,sv_acct_ind\
                          ,cc_acct_ind\
                          ,ck_avg_bal\
                          ,sv_avg_bal\
                          ,cc_avg_bal\
                          ,ck_avg_tran_amt\
                          ,sv_avg_tran_amt\
                          ,cc_avg_tran_amt\
                          ,q1_trans_cnt\
                          ,q2_trans_cnt\
                          ,q3_trans_cnt\
                          ,q4_trans_cnt\
                          ,SAMPLEID AS sample_id\
                   FROM <DBNAME>.ADS_Py2 SAMPLE .60, .40"

# Create a DataFrame from_query() and take a glimpse at it
tdTrain_Test2 = DataFrame.from_query(ADS_Train_Test2)
tdTrain_Test2.to_pandas().head(10)

# Use the 60% sample to train

MultiModelTrain_Py = tdTrain_Test2[tdTrain_Test2.sample_id == "1"]
# Persist the MultiModelTrain_Py dataset
# Before you execute each one of the following statements, replace the variable
# <DBNAME> with the target Vantage system database name where the corresponding
# table resides.
try:
    get_context().execute("DROP TABLE <DBNAME>.MultiModelTrain_Py")
except:
    pass
copy_to_sql(MultiModelTrain_Py, schema_name="<DBNAME>", table_name="MultiModelTrain_Py", if_exists = "replace")

# Use the 40% sample to test/score

MultiModelTest_Py = tdTrain_Test2[tdTrain_Test2.sample_id == "2"]
# Persist the MultiModelTest_Py dataset
# Before you execute each one of the following statements, replace the variable
# <DBNAME> with the target Vantage system database name where the corresponding
# table resides.
try:
    get_context().execute("DROP TABLE <DBNAME>.MultiModelTest_Py")
except:
    pass
copy_to_sql(MultiModelTest_Py, schema_name="<DBNAME>", table_name="MultiModelTest_Py", if_exists = "replace")

###
### End of session
###

# Remove the context of present teradataml session and terminate the Python
# session. It is recommended to call the remove_context() function for session
# cleanup. Temporary objects are removed at the end of the session.
from teradataml import remove_context
remove_context()

# At this point, we have created the training and testing/scoring tables
# in the target Vantage server in database <DBNAME> that we need for the rest
# of this use case.
# You can now review the Python scripts "stoRFFitMM.py" (for fitting) and
# "stoRFScoreMM.py" (for scoring) that are distributed with the present
# TechBytes demo material.
# Note: Save the full path of the location where these script files are stored.
#       You will need to specify these paths later in the SQL script
#       "R_Py_TechBytes-Part_5-Demo.sql" that calls the SCRIPT Table Operator.
#       The SQL script file
#       -- refers to the fitting script path variable as <mmfitscrPATH>
#       -- refers to the scoring script path variable as <mmscoscrPATH>
#       As advised in USE CASE [1] Section 1, speficy the path names suitably
#       for your platform.
#       For example, on Linux platforms specify paths like:
#         mmfitscrPATH = /home/me/Path/To/stoRFFitMM.py
#         mmscoscrPATH = /home/me/Path/To/stoRFScoreMM.py

################################################################################
# USE CASE [2] - Section 2: Build multiple models (in database; uses SCRIPT)
################################################################################

# Fitting models with the training dataset takes place in the target
# Vantage system Advanced SQL Engine. To perform this task, inspect and
# execute the code in the "Use Case [2]" section of the SQL script file
# "R_Py_TechBytes-Part_5-Demo.sql" as guided in the file.
#
# In a few places across the SQL script file "R_Py_TechBytes-Part_5-Demo.sql"
# you will need to specify the database where your Python fitting and scoring
# script files will reside.
#
# Then, focus on "Part (A): Model fitting" of the "Use Case [2]" section.
# You can execute the SQL code in a SQL interpreter such as Teradata Studio,
# by connecting to the target Vantage system and the database <DBNAME> where
# the training and scoring tables reside.
# The code in the SQL script file demonstrates using Python with the SCRIPT
# Table Operator. The SQL script needs to know the location of the fitting
# Python script "stoRFFitMM.py" file. For this fitting task, the SCRIPT Table
# Operator uses as input the "MultiModelTrain_Py" table with the training data.
# The output is the "RFStateCodeModelsPy" table that contains the fitted models
# for all state codes in the use case.

################################################################################
# USE CASE [2] - Section 3: Score multiple models (in database; uses SCRIPT)
################################################################################

# Following up from the previous Section 2, scoring the test dataset with the
# saved models in the table "RFStateCodeModelsPy" also takes place in the
# target Vantage system Advanced SQL Engine. To perform this task, review and
# execute the code in the SQL script file "R_Py_TechBytes-Part_5-Demo.sql".
# Specifically, use the code in the "Use Case [2]" section of the SQL script
# file, and focus on "Part (B): Scoring with models".
# You can execute the SQL code in a SQL interpreter such as Teradata Studio,
# by connecting to the target Vantage system and the database <DBNAME> where
# the training and scoring tables reside.
# The code in the SQL script file demonstrates again using Python with the
# SCRIPT Table Operator. The SQL script needs to know the location of the
# scoring Python script "stoRFFitMM.py" file. For this scoring task, the
# SCRIPT Table Operator uses as input the table "MultiModelTest_Py" with the
# test/score data, in addition to the "RFStateCodeModelsPy" table that contins
# the information about the models in all state codes.
# The SCRIPT Table Operator output is rows of the sought information about the
# propensity, partitioned by state code, of the financial services customers
# in the test table to open a credit card account.

################################################################################
################################################################################
# END OF USE CASE [2]: Multiple models fitting and scoring example
################################################################################
################################################################################
