UseCases/Vantage_Analytics_Library/VAL_teradataml_Demo_Python.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",
    "       Vantage Analytic Library and teradataml Demo Notebook \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>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This notebook shows the power of Vantage and the Analytic Library through a series of functions that use the Teradata SQLE to perform descriptive statistics, data engineering and transformation, model building, model evaluation and model scoring.  All Analytic Library functions are called through Python using the teradataml interfaces for VAL.  Once the heavy lifting is done by the SQL engine, Python (Matplotlib and plotly) are used to generate the plots herein.  The analyses that are used include the following.  The overall use case is also summarized below.\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Analytic Library and teradataml Demo Use Case</b></p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Demo data - Financial Customers/Accounts/Transactions</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following data has been put into the Demo_Financial database on Teradata database for the examples in this Python  Notebook.  It's a simplistic fictitious dataset of banking customers (approx. 10K rows), Accounts (approx. 20K rows) and Transactions (approx. 1Million rows).  They are related to each other in the following ways:</p>\n",
    "\n",
    "<img src=\"images/DemoData.png\">\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Within this use case, we explore the raw data using the VAL Descriptive Statistical functions along with many different plots and graphs.  Based upon our findings, we begin to assemble an analytic data set through a combination of VAL Transformation functions, as well as the pandas/SQLAlchemy support in teradataml.  Finally, we use Logistic Regression to predict which of our existing customer base has the highest propensity to open a credit card account.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Descriptive Statistics</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Values Analysis</b>\n",
    "    \n",
    "<li>Data Types</li>\n",
    "<li>Count</li>\n",
    "<li>Number NULLs, Positive, Negative and Unique, Zero and Blank values</li>\n",
    "</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Univariate Statistical Analysis</b>\n",
    "<li>Count/Minimum/Maximum/Mean</li>\n",
    "<li>Standard Deviation</li>\n",
    "<li>Standard Mean Error</li>\n",
    "<li>Variance & Coefficient of Variance</li>\n",
    "<li>Skewness & Kurtosis</li>\n",
    "<li>Uncorrected Sum of Squares</li>\n",
    "<li>Corrected Sum of Squares</li>\n",
    "<li>Modes</li>\n",
    "<li>Quantiling & Ranking</li>\n",
    "<li>Top 10/Bottom 10 Percentiles*</li>\n",
    "<li>Deciles/Quartiles/Tertiles*</li>\n",
    "<li>Top 5/Bottom 5 Ranks and Values*</li>\n",
    "    </p>    \n",
    "\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Distribution Analysis</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Frequency of Discrete Variables</b></p>\n",
    "\n",
    "<table style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "  <tr>\n",
    "    <th>Style</th>\n",
    "    <th>Description/Example</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>basic</td>\n",
    "    <td>Counts frequencies of individual column values (default).</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>pairwise</td>\n",
    "    <td>Counts frequencies of pair-wise combinations of values of selected columns rather than individually. Not available if the cumulative option is selected.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>crosstab</td>\n",
    "    <td>Counts frequencies of combinations of values of selected columns rather than individually.</td>\n",
    "  </tr>\n",
    "    \n",
    "</table>\n",
    "     \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Histograms of Continuous Variables</b></p>\n",
    "\n",
    "\n",
    "<table style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "  <tr>\n",
    "    <th>Option</th>\n",
    "    <th>Description/Example</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>bins</td>\n",
    "    <td>The number of equal width bins to create.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td> </td>\n",
    "    <td>For example, bins=5. If multiple columns are requested, multiple bin sizes may be specified, such as bins=5, 10. If fewer sizes are specified than columns, left-over columns are associated with the default size of 10 bins.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>widths</td>\n",
    "    <td>The width of the bins to create.</td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td> </td>\n",
    "    <td>For example, widths=100. If multiple columns are requested, multiple widths must be specified, such as widths=5, 10. If fewer sizes are specified than columns, an error message displays.</td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>quantiles</td>\n",
    "    <td>The number of approximately equally populated bins to create.</td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td> </td>\n",
    "    <td>For example, quantiles=10. If multiple columns are requested, multiple quantile sizes may be specified, such as quantiles=5, 10. If fewer sizes are specified than columns, left-over columns are associated with the default size of 10 quantiles. </td>  \n",
    "  </tr> \n",
    "  <tr>\n",
    "    <td>boundaries</td>\n",
    "    <td>Specific boundaries that define the bins.</td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td> </td>\n",
    "    <td>For example, boundaries=0,50,100,150 provides 3 bins between 0 and 150 (0 to 50, 50 to 100, and 100 to 150). If multiple columns are requested, multiple sets of parameters must be specified, such as boundaries={0, 50000, 100000, 150000}, {0, 50, 100}.</td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>binwithminmax</td>\n",
    "    <td>The number of bins spanning a range specified by the minimum and maximum values.</td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td> </td>\n",
    "    <td>For example, binwithminmax=5,0,200 creates 5 bins ranging from 0 to 200. If multiple columns are requested, multiple sets of parameters must be specified, such as binwithminmax={10, 0, 200000}, {5, 0, 100}.</td>  \n",
    "  </tr>  \n",
    "</table>\n",
    "    \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A separate (Adaptive Histogram) analysis is offered to intelligently bin data by offering options to further subdivide the distribution.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Matrix Analysis</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Create a matrix for input to Linear Regression (or Factor Analysis in the next release) or analysis of any of the following types of matrices:\n",
    "<li>SSCP = sum-of-squares-and-cross-products matrix (the default)</li>\n",
    "<li>ESSCP = Extended-sum-of-squares-and-cross-products matrix</li>\n",
    "<li>CSSCP = Corrected-sum-of-squares-and-cross-products matrix</li>\n",
    "<li>COV = Covariance matrix</li>\n",
    "<li>COR = Correlation matrix</li>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Variable Transformation Functions</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Variable Transformation analysis reads a single table or view and produces a Select, Create Table, or Create View statement containing transformed columns.  Each function below can be called individually or in any combination.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Bin Coding</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Bin Coding replaces a continuous numeric column with a categorical one and produce ordinal values (for example, numeric categorical values where order is meaningful). Bin Coding uses the same techniques used in Histogram analysis, allowing you to choose between equal-width bins, equal-width bins with a user-specified minimum and maximum range, bins with a user-specified width, evenly distributed bins, or bins with user-specified boundaries.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Design Coding</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Design Coding is useful when a categorical data element must be re-expressed as one or more numeric data elements, creating a binary numeric field for each categorical data value. Design coding is offered in two forms: dummy-coding and contrast-coding.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Recoding</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Recoding a categorical data column is done to re-express existing values of a column (variable) into a new coding scheme or to correct data quality problems and focus an analysis on a particular value. It allows for mapping individual values, NULL values, or any number of remaining values (ELSE option) to a new value, a NULL value or the same value.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Rescaling</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Rescaling limits the upper and lower boundaries of the data in a continuous numeric column using a linear rescaling function based on maximum and minimum data values. Rescale is useful with algorithms that require or work better with data within a certain range.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Sigmoid Transformation</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A Sigmoid transformation provides rescaling of continuous numeric data in a more sophisticated way than the Rescaling transformation function. In a Sigmoid transformation a numeric column is transformed using a type of sigmoid or s-shaped function. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Z-Score Transformation</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Similar to a Sigmoid transformation, a Z-Score transformation provides rescaling of continuous numeric data in a more sophisticated way than a Rescaling transformation.  Z-Score transforms each column value into the number of standard deviations from the mean value of the column.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>NULL Value Replacement</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>NULL value replacement is offered as a transformation function. A literal value, the mean, median, mode, or an imputed value joined from another table can be used as the replacement value. The median value can be requested with or without averaging of two middle values when there is an even number of values.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Formula Derivations</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Derive transformation requires the free-form transformation be specified as a formula using the following operators, arguments, and functions:  +, -, **, *, /, %, (, ), x, y, z, abs, exp, ln, log, sqrt</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Logistic Regression</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Logistic Regression is one of the most widely used types of statistical analysis.  In Logistic Regression, a set of independent variables (in this case columns) is processed to predict the value of a dependent variable (column) that assumes two values referred to as response (1) and non-response (0).  Actually, the user specifies what value of the dependent variable to treat as the response, and all other values assumed by the dependent variable are treated as non-respsonse.  The result is not however a continuous numeric variable as seen in Linear Regression, but rather a probability between 0 and 1 that the response value is assumed by the dependent variable.  teradataml splits separate function out for model building, evaluation and scoring.</p>\n",
    "\n",
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage and import the required libraries.</b></p>    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-05T15:55:01.150305Z",
     "start_time": "2021-02-05T15:55:01.134716Z"
    }
   },
   "outputs": [],
   "source": [
    "# Load teradataml and dependency packages\n",
    "\n",
    "from teradataml import create_context, DataFrame, get_context, copy_to_sql, in_schema, remove_context\n",
    "from teradataml.dataframe.sql_functions import case\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib import cm\n",
    "import plotly.express as px\n",
    "import plotly.graph_objects as go\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import getpass as gp\n",
    "from sqlalchemy.sql.expression import select, or_, extract, text, join, case as case_when\n",
    "from sqlalchemy import func\n",
    "\n",
    "# Import \"valib\" object from teradataml to execute Vantage Analytic Library functions.\n",
    "# Set 'configure.val_install_location' to the database name where Vantage analytic library functions are installed.\n",
    "\n",
    "from teradataml import *\n",
    "from teradataml.analytics.valib import *\n",
    "from teradataml import configure\n",
    "configure.val_install_location = \"val\"\n",
    "\n",
    "#from pycaret.utils import enable_colab\n",
    "#enable_colab()\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "import xml.dom.minidom\n",
    "\n",
    "import pylab\n",
    "import getpass\n",
    "display.max_rows=5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-05T17:05:53.773657Z",
     "start_time": "2021-02-05T17:05:53.754406Z"
    }
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "\n",
    "# define matplotlib styles for this notebook\n",
    "plt.style.use('fivethirtyeight')\n",
    "pylab.rcParams['figure.figsize'] = (20, 6)\n",
    "pylab.rcParams['font.size'] = 16\n",
    "pylab.rcParams['text.color'] = 'black'\n",
    "pylab.rcParams['axes.labelcolor'] = 'black'\n",
    "pylab.rcParams['xtick.color'] =  'black'\n",
    "pylab.rcParams['ytick.color'] =  'black'  \n",
    "pylab.rcParams['ytick.labelsize'] = 'large'\n",
    "\n",
    "#pylab.rcParams.keys() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, then use down arrow to go to next cell. Begin running steps with Shift + Enter keys.</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=VAL_teradataml_Demo_Python.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage.  You have the option of either running the demo using foreign tables to access the data without using any storage on your environment or downloading the data to local storage which may yield somewhat faster execution, but there could be considerations of available storage.  There are two statements in the following cell, and one is commented out.  You may switch which mode you choose by changing the comment string. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_Financial_cloud');\"\n",
    " # Takes about 20 seconds\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_Financial_local');\" \n",
    "# Takes about 3 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Creation and loading of tables</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Accounts table\n",
    "query = '''\n",
    "CREATE MULTISET TABLE Accounts \n",
    "     (\n",
    "      acct_nbr VARCHAR(18) CHARACTER SET UNICODE NOT CASESPECIFIC,\n",
    "      cust_id INTEGER,\n",
    "      acct_type VARCHAR(2) CHARACTER SET UNICODE NOT CASESPECIFIC,\n",
    "      account_active VARCHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,\n",
    "      acct_start_date DATE FORMAT 'YY/MM/DD',\n",
    "      starting_balance DECIMAL(11,3),\n",
    "      ending_balance DECIMAL(11,3))\n",
    "UNIQUE PRIMARY INDEX ( acct_nbr );\n",
    "'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('Accounts')\n",
    "    execute_sql(query)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Customer table\n",
    "query = '''\n",
    "CREATE MULTISET TABLE Customer \n",
    "     (\n",
    "      cust_id INTEGER,\n",
    "      income DECIMAL(15,1),\n",
    "      age INTEGER,\n",
    "      years_with_bank INTEGER,\n",
    "      nbr_children INTEGER,\n",
    "      gender VARCHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,\n",
    "      marital_status VARCHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,\n",
    "      postal_code VARCHAR(5) CHARACTER SET UNICODE NOT CASESPECIFIC,\n",
    "      state_code VARCHAR(2) CHARACTER SET UNICODE NOT CASESPECIFIC)\n",
    "UNIQUE PRIMARY INDEX ( cust_id );\n",
    "'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('Customer')\n",
    "    execute_sql(query)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Transctions table\n",
    "query = '''\n",
    "CREATE MULTISET TABLE Transactions \n",
    "     (\n",
    "      tran_id INTEGER,\n",
    "      acct_nbr VARCHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC,\n",
    "      tran_amt DECIMAL(9,2),\n",
    "      principal_amt DECIMAL(15,2),\n",
    "      interest_amt DECIMAL(11,3),\n",
    "      new_balance DECIMAL(9,2),\n",
    "      tran_date DATE FORMAT 'YY/MM/DD',\n",
    "      tran_time INTEGER,\n",
    "      channel VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,\n",
    "      tran_code VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC)\n",
    "UNIQUE PRIMARY INDEX ( tran_id ,acct_nbr );\n",
    "'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('Transactions')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "execute_sql('INSERT into Accounts select acct_nbr,cust_id,acct_type,account_active,acct_start_date,starting_balance,ending_balance from DEMO_Financial.Accounts;')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    " execute_sql('INSERT into Customer select cust_id,income,age,years_with_bank,nbr_children,gender,marital_status,postal_code,state_code from DEMO_Financial.Customer;')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "execute_sql('INSERT into Transactions select tran_id,acct_nbr,tran_amt,principal_amt,interest_amt,new_balance,tran_date,tran_time,channel,tran_code from DEMO_Financial.Transactions;')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create DataFrames for the Customer, Accounts and Transactions tables in the\n",
    "# Vantage Advanced SQL Engine. \n",
    "tdCustomer = DataFrame(in_schema(\"DEMO_Financial\", \"Customer\"))\n",
    "tdCustomer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdAccounts = DataFrame(in_schema(\"DEMO_Financial\", \"Accounts\"))\n",
    "tdAccounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdTransactions = DataFrame(in_schema(\"DEMO_Financial\", \"Transactions\"))\n",
    "tdTransactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdTransactions.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Values Function:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>SIGNATURE:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>> valib.Values(data, columns=None, exclude_columns=None, group_columns=None, distinct=False, filter=None)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>DESCRIPTION:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Use Values analysis as the first type of analysis performed on unknown data. Values analysis determines the nature and quality of the data. For example, whether the data is categorical or continuously numeric, how many null values it contains, and so on.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A Values analysis provides a count of rows, rows with non-null values, rows with null values, rows with value 0, rows with a positive value, rows with a negative value, and the number of rows containing blanks in the given column. By default, unique values are counted, but this calculation can be inhibited for performance reasons if desired. For a column of non-numeric type, the zero, positive, and negative counts are always zero (for example, 000 is not counted as 0). A Values analysis can be performed on columns of any data type, though the measures displayed vary according to column type. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>PARAMETERS:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>data:</b></p>\n",
    "<li>Required Argument.</li>\n",
    "<li>Specifies the input data to perform Values analysis.</li>\n",
    "<li>Types: teradataml DataFrame</li>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>columns:</b></p>\n",
    "<li>Required Argument.</li>\n",
    "<li>Specifies the name(s) of the column(s) to analyze. Occasionally, it can also accept permitted strings to specify all columns, or all numeric columns, or all character columns.</li>\n",
    "<li>Permitted Values:</li>\n",
    "<ol style = 'font-size:14px;font-family:Arial;color:#00233C'>Name(s) of the column(s) in \"data\".\n",
    "- Pre-defined strings: \n",
    "    <li>'all' – all columns</li>\n",
    "    <li>'allnumeric' – all numeric columns</li>\n",
    "    <li>'allcharacter' - all numeric and date columns</li>\n",
    "</ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>exclude_columns:</b></p>\n",
    "<li>Optional Argument.</li>\n",
    "<li>Specifies the name(s) of the column(s) to exclude from the analysis, if a column specifier such as 'all', 'allnumeric', 'allcharacter' is used in the \"columns\" argument.</li>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>group_columns:</b></p>\n",
    "<li>Optional Argument.</li>\n",
    "<li>Specifies the name(s) of column(s) to perform separate analysis for each group.</li>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>distinct:</b></p>\n",
    "<li>Optional Argument.</li>\n",
    "<li>Specifies whether to select unique values count for each selected column.</li>\n",
    "<li>Default Value: False</li>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>filter:</b>\n",
    "<li>Optional Argument.</li>\n",
    "<li>Specifies the clause to filter rows selected for analysis within Values.</li>\n",
    "<li>For example,</li>\n",
    "    filter = \"cust_id > 0\"\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RETURNS:</b>\n",
    "<li>An instance of Values.</li>\n",
    "<li>Output teradataml DataFrames can be accessed using attribute references, such as ValuesObj.\"attribute_name\".</li>\n",
    "<li>Output teradataml DataFrame attribute name is: result.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RAISES:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TeradataMlException, TypeError, ValueError</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example 1: Perform Values analysis using default values on 'income' and 'marital_status' columns.\n",
    "tdCustomer_val1 = valib.Values(data=tdCustomer, columns=[\"all\"])\n",
    "# Print the results\n",
    "# Using to_pandas() for a cleaner display format\n",
    "tdCustomer_val1.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Plots</b></p>\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Bar Chart - Unique Values</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove Index from Values plots data\n",
    "tdCustomer_bar_pd = tdCustomer_val1.result.to_pandas().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-05T16:29:44.204757Z",
     "start_time": "2021-02-05T16:29:44.132935Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "fig = px.bar(tdCustomer_bar_pd, x='xcol', y=[\"xunique\"])\n",
    "fig.update_layout(height=450, width=1100, title=\"Unique Values\")\n",
    "fig.update_xaxes(tickangle = 0, title=\"Column\",categoryorder='total descending')   \n",
    "fig.update_yaxes(title=\"Number of Uniques\")   \n",
    "fig.update_layout(showlegend=False)\n",
    "fig"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above chart we show the number of unique values for each of the columns mentioned along the X axis.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Stacked Bar Chart - Count, NULL, Unique, Blank, Zero, Positive and Negative Values</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-05T00:53:20.525032Z",
     "start_time": "2021-02-05T00:53:20.429415Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "fig = px.bar(tdCustomer_bar_pd, x='xcol', y=[\"xcnt\", \"xnull\", \"xunique\",\"xblank\",\"xzero\",\"xpos\",\"xneg\"])\n",
    "fig.update_layout(height=500, width=1000)\n",
    "fig.update_xaxes(tickangle = 0, title=\"Column\",categoryorder='total descending')   \n",
    "fig.update_yaxes(title=\"\")   \n",
    "fig.update_layout(legend=dict(\n",
    "    title=\"\",\n",
    "    orientation=\"h\",\n",
    "    yanchor=\"bottom\",\n",
    "    y=1.02,\n",
    "    xanchor=\"right\",\n",
    "    x=1\n",
    "))\n",
    "\n",
    "fig.show() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As mentioned in the title above the graph shows the number of unique values , the count , the number of NULLS etc in the different columns mentioned on the X axis.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Horizontal Bar Chart - Count, NULL, Unique, Blank, Zero, Positive and Negative Values</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-05T00:53:36.613853Z",
     "start_time": "2021-02-05T00:53:36.536942Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "fig = px.bar(tdCustomer_bar_pd, x=[\"xcnt\", \"xnull\", \"xunique\",\"xblank\",\"xzero\",\"xpos\",\"xneg\"], y='xcol')\n",
    "fig.update_layout(height=500)\n",
    "fig.update_yaxes(title=\"Column\",categoryorder='total ascending')   \n",
    "fig.update_xaxes(title=\"\") \n",
    "fig.update_layout(legend=dict(\n",
    "    title=\"\",\n",
    "    orientation=\"h\",\n",
    "    yanchor=\"bottom\",\n",
    "    y=1.02,\n",
    "    xanchor=\"right\",\n",
    "    x=1\n",
    "))\n",
    "fig.show() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As mentioned in the title above the horizontal graph shows the number of unique values , the count , the number of NULLS etc in the different columns mentioned on the Y axis.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example 2: Perform Values analysis on 'income' column with values grouped by 'gender' and only for rows with income greater than 0.\n",
    "tdCustomer_val2 = valib.Values(data=tdCustomer, columns=[\"all\"], group_columns=\"gender\", filter=\"income > 0\")\n",
    "# Print the results\n",
    "# Using to_pandas() for a cleaner display format\n",
    "tdCustomer_val2.result.to_pandas().head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3-D Chart - Unique Values by Gender</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-23T15:29:39.395111Z",
     "start_time": "2021-02-23T15:29:16.060963Z"
    }
   },
   "outputs": [],
   "source": [
    "def draw_3d_bar(data):\n",
    "    unique_genders = data.reset_index().gender.unique()\n",
    "    unique_xcols = data.reset_index()[\"xcol\"].unique()\n",
    "    x = [[data.loc[gender].at[col] for col in unique_xcols] for gender in unique_genders]\n",
    "    #x = [[data.at[gender, col] for col in unique_xcols ] for gender in unique_genders]\n",
    "    result = np.array(x, dtype=np.int64)\n",
    "    fig=plt.figure(figsize=(10, 12), dpi=500)\n",
    "    ax1=fig.add_subplot(111, projection='3d')    \n",
    "    xlabels = np.array(unique_xcols)\n",
    "    xpos = np.arange(xlabels.shape[0])\n",
    "    ylabels = tdCustomer_bar_pd[\"gender\"].unique()\n",
    "    ypos = np.arange(ylabels.shape[0])\n",
    "    xposM, yposM = np.meshgrid(xpos, ypos, copy=False)\n",
    "    zpos=result\n",
    "    zpos = zpos.ravel()\n",
    "    dx=1\n",
    "    dy=.3\n",
    "    dz=zpos\n",
    "    ax1.w_xaxis.set_ticks(xpos )\n",
    "    ax1.w_xaxis.set_ticklabels(xlabels)\n",
    "    ax1.w_yaxis.set_ticks(ypos + dy/2.)\n",
    "    ax1.w_yaxis.set_ticklabels(ylabels)\n",
    "    values = (dz-dz.min())/np.float_(dz.max()-dz.min())\n",
    "    colors = cm.rainbow(values)\n",
    "    ax1.bar3d(xposM.ravel(), yposM.ravel(), dz*0, dx, dy, dz, color=colors)\n",
    "    plt.title(\"Unique Values\")\n",
    "    plt.xticks(rotation=70)\n",
    "    return plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-05T16:29:40.428338Z",
     "start_time": "2021-02-05T16:29:40.157658Z"
    }
   },
   "outputs": [],
   "source": [
    "# Remove Index from Values plots data\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "tdCustomer_bar_pd = tdCustomer_val2.result.to_pandas().reset_index()\n",
    "data = tdCustomer_val2.result.to_pandas().reset_index().set_index([\"gender\", \"xcol\"])[\"xunique\"]\n",
    "draw_3d_bar(data).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph is the 3-dimensional chart for unique values in each column mentioned along the X axis. The 3rd dimension is the gender column which shows unique values for each column.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Statistics Function</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>SIGNATURE:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>> valib.Statistics(data, columns=None, exclude_columns=None, extended_options='none', group_columns=None, statistical_method='population', stats_options=None, filter=None)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>DESCRIPTION:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Statistics analysis provides several common and not so common statistical measures for numeric data columns. Extended options include additional analyses and measures such as Values, Modes, Quantiles, and Ranks. Use statistical measures to understand the characteristics and properties of each numeric column, and to look for outlying values and anomalies. Statistics analysis can be performed on columns of numeric or date data type. For columns of type DATE, statistics other than count, minimum, maximum, and mean are calculated by first converting to the number of days since 1900.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>PARAMETERS:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>data:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "    <li>Required Argument.Specifies the input data to perform Statistics analysis.</li>\n",
    "</p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: teradataml DataFrame</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "<li>Required Argument.Specifies the name(s) of the column(s) to analyze. Occasionally, it can also accept permitted strings to specify all columns, or all numeric columns, or all numeric and date columns.</li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Permitted Values:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "<li>Name(s) of the column(s) in \"data\".</li>\n",
    "<li>Pre-defined strings: </li>\n",
    "<ol style = 'font-size:14px;font-family:Arial;color:#00233C'>    \n",
    "    <li>'all' – all columns</li>\n",
    "    <li>'allnumeric' – all numeric columns</li>\n",
    "    <li>'allnumericanddate' - all numeric and date columns</li>\n",
    "</ol>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>exclude_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "<li>Optional Argument.Specifies the name(s) of the column(s) to exclude from the analysis, if a column specifier such as 'all', 'allnumeric', 'allnumericanddate' is used in the \"columns\" argument.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>extended_options:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the extended options for calculating statistics.</li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Permitted Values: </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>'all'</li>\n",
    "<li>'none'</li>\n",
    "<li>'modes'</li>\n",
    "<li>'quantiles'</li>\n",
    "<li>'values'</li>\n",
    "<li>'rank'</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: 'none'</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>group_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the name(s) of column(s) to perform separate analysis for each group.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>statistical_method:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the statistical method.</li>\n",
    "    <li>Permitted Values: 'sample', 'population'</li>\n",
    "    <li>Default Value: 'population'</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>stats_options:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "    <li>Optional Argument.Specifies the basic statistics to be calculated.</li>\n",
    "</p> \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Permitted Values: </p>\n",
    "<p style = 'font-size:12px;font-family:Arial;color:#00233C'>\n",
    "<li>all</li>\n",
    "<li>count (cnt)</li>\n",
    "<li>minimum (min)</li>\n",
    "<li>maximum (max)</li>\n",
    "<li>mean</li>\n",
    "<li>standarddeviation (std)</li>\n",
    "<li>skewness (skew)</li>\n",
    "<li>kurtosis (kurt)</li>\n",
    "<li>standarderror (ste)</li>\n",
    "<li>coefficientofvariance (cv)</li>\n",
    "<li>variance (var)</li>\n",
    "<li>sum</li>\n",
    "<li>uncorrectedsumofsquares (uss)</li>\n",
    "<li>correctedsumofsquares (css)</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: ['cnt', 'min', 'max', 'mean', 'std']</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>filter:</b></p>\n",
    "    <li>Optional Argument.Specifies the clause to filter rows selected for analysis within Statistics.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example,\n",
    "    filter = \"cust_id > 0\"\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RETURNS:</b>\n",
    "    <li>An instance of Statistics.</li>\n",
    "    <li>Output teradataml DataFrames can be accessed using attribute references, such as StatisticsObj.\"attribute_name\".</li>\n",
    "    <li>Output teradataml DataFrame attribute name is: result.</li>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RAISES:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TeradataMlException, TypeError, ValueError</p>    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example 1: First call the Statistics function on all numeric and date columns in the Customer table, taking basic statistics (min/max/mean/std).\n",
    "tdCustomer_stats1 = valib.Statistics(data=tdCustomer, columns=\"allnumericanddate\")\n",
    "# Print the results\n",
    "tdCustomer_stats1.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example 2: Next call the Statistics function on the same variables in the Customer table, generating all statistics, including the extended options.\n",
    "tdCustomer_stats2 = valib.Statistics(data=tdCustomer, columns=\"allnumericanddate\", stats_options=\"all\", extended_options=\"all\")\n",
    "# Print the results\n",
    "tdCustomer_stats2.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Percentiles Plot</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-05T16:49:08.408009Z",
     "start_time": "2021-02-05T16:49:07.476950Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data = tdCustomer_stats2.result.to_pandas().reset_index()\n",
    "fig = px.scatter(data, facet_col=\"xcol\", x=\"xtbl\",  y= [col for col in data.columns if 'xpctile' in col], height=800)\n",
    "fig.update_yaxes(matches=None)\n",
    "fig.update_traces(marker=dict(size=12,line=dict(width=2,color='DarkSlateGrey')),selector=dict(mode='markers'))\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above scatter plot is showing the percentile of values in each column. The X axis shows the different columns for which we are comparing the values in columns. The different coloured dots in the above scatter plot shows the percentile of the different values in each column.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example 3: Next call the Statistics function on the same variables in the Customer table, generating basic statistics, grouping by gender and filtering anyone with an income of 0.\n",
    "tdCustomer_stats3 = valib.Statistics(data=tdCustomer, columns=\"allnumericanddate\", group_columns=\"gender\", filter=\"income > 0\")\n",
    "# Print the results\n",
    "tdCustomer_stats3.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Plots</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>\"Box and Whiskers\" by Gender Plot</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-05T16:42:18.055538Z",
     "start_time": "2021-02-05T16:42:17.390158Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data = tdCustomer_stats3.result.to_pandas().reset_index()\n",
    "fig = px.scatter(data, facet_row=\"xcol\",  x=\"gender\", y=[\"xmin\",\"xmax\",\"xmean\", \"xstd\"], height=900)\n",
    "fig.update_yaxes(matches=None)\n",
    "fig.update_traces(marker=dict(size=12,line=dict(width=2,color='DarkSlateGrey')),selector=dict(mode='markers'))\n",
    "fig.update_layout(legend=dict(\n",
    "    title=\"\",\n",
    "    orientation=\"h\",\n",
    "    yanchor=\"bottom\",\n",
    "    y=1.02,\n",
    "    xanchor=\"right\",\n",
    "    x=1\n",
    "))\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above scatter plot represents the percentiles for each column based on gender. The x axis shows the gender and the y axis shows the columns on the right side and on the left side  y axis shows the percentile values of each column.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Frequency Function</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>SIGNATURE:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>> valib.Frequency(data, columns=None, exclude_columns=None, cumulative_option=False, agg_filter=None, min_percentage=None, pairwise_columns=None, stats_columns=None, style=\"basic\", top_n=None, filter=None)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>DESCRIPTION:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Frequency analysis counts the occurrences of individual data values in columns that contain categorical data. Frequency analysis is useful in understanding the meaning of a particular data element and can point out the need to recode some of the data values found, either permanently or in the course of building an analytic data set. This function is also useful in analyzing combinations of values occurring in two or more columns. A Frequency analysis calculates the number of occurrences of each value of the column or columns individually or in combination. Additionally, for each value, the percentage of rows in the selected DataFrame is provided in descending order starting with the most frequently occurring value.</p> \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>PARAMETERS:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>data:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Required Argument.Specifies the input data to perform frequency analysis.</li>\n",
    "</p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: teradataml DataFrame</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Required Argument.Specifies the name(s) of the column(s) to analyze. Occasionally, it can also accept permitted strings to specify all columns, or all numeric columns, or all character columns.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Permitted Values:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "<li>Name(s) of the column(s) in \"data\".</li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Pre-defined strings:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>'all' – all columns</li>\n",
    "    <li>'allnumeric' – all numeric columns</li>\n",
    "    <li>'allcharacter' - all numeric and date columns</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>exclude_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the name(s) of the column(s) to exclude from the analysis, if a column specifier such as 'all', 'allnumeric', 'allcharacter' is used in the \"columns\" argument.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>cumulative_option:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies whether to include rank, cumulative count, and cumulative percent information for each frequency value. When set to 'True', this information is included otherwise not.</li>\n",
    "    </p>\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'>Note:This argument should not be set to 'True' when style is 'pairwise'.\n",
    "    <li>Default Value: False</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>agg_filter:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "    <li>Optional Argument.Specifies the clause to restrict returned aggregations.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example,\n",
    "    <li>agg_filter=\"xpct > 1\"</li>\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'>Note:This argument should not be used when \"min_percentage\" argument is used.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>min_percentage:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies a value to determine whether to include only frequency values that occur a minimum percentage of the time.</li>\n",
    "    <li>Setting this to 0 or 0.0 is equivalent to not including the argument at all. </li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float, int\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>pairwise_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specified the columns to be paired up with the frequency columns.</li></p>\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'>Note:Use only when \"style\" is set to 'pariwise'.\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>stats_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the name(s) of column(s) for which the minimum, maximum, mean value, and standard deviation are included in the result with the values computed over the rows corresponding to the individual values of the frequency columns.</li></p>\n",
    "\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'>Note:This argument can be used only when \"style\" is 'basic'.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>style:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the frequency style for the analysis.</li>\n",
    "    </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Permitted Values:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>'basic' - Counts frequencies of individual column values.</li>\n",
    "<li>'pairwise' - Counts frequencies of pair-wise combinations of values of selected columns rather than individually. This should not be used when \"cumulative_option\" is set to 'True'.</li>\n",
    "<li>'crosstab' - Counts frequencies of combinations of values of selected columns rather than individually.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: 'basic'</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>top_n:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the number of frequency values to include. Using this argument shows frequency only for the number of top occurring values entered. </li></p>\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'>Note:This argument is enabled only if \"cumulative_option\" is set to 'True'.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: int</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>filter:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the clause to filter rows selected for analysis within Frequency.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example,\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>filter = \"cust_id > 0\"\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RETURNS:</b>\n",
    "    <li>An instance of Frequency.</li>\n",
    "    <li>Output teradataml DataFrames can be accessed using attribute references, such as FrequencyObj.\"attribute_name\".</li>\n",
    "    <li>Output teradataml DataFrame attribute name is: result.</li>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RAISES:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TeradataMlException, TypeError, ValueError</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example 1: First call the Frequency function on the years_with_bank column in the Customer table.\n",
    "tdCustomer_freq1 = valib.Frequency(data=tdCustomer, columns=\"years_with_bank\")\n",
    "# Print the results\n",
    "tdCustomer_freq1.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Box Plot - Number of years with the financial institution</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-23T21:05:00.853822Z",
     "start_time": "2021-02-23T21:05:00.637825Z"
    }
   },
   "outputs": [],
   "source": [
    "import plotly.graph_objs as go\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "data = tdCustomer_freq1.result.to_pandas().reset_index().sort_values('xcnt')\n",
    "\n",
    "fig = go.Figure(data=[go.Bar(x=data.xval, y=data.xcnt)])\n",
    "fig.update_layout(height=500)\n",
    "fig.update_yaxes(title=\"Number of Customers\")\n",
    "fig.update_xaxes(title=\"Member Years\", dtick=1)\n",
    "fig.update_layout(showlegend=False)\n",
    "fig.show() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above bar graph shows the Number of Customers depending on the number of years they have been a part of the financial institution.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example 2: Next call the Frequency function on the gender and marital_status columns in the Customer table, generating a cross-tabulation.\n",
    "tdCustomer_freq2 = valib.Frequency(data=tdCustomer, columns=[\"gender\", \"marital_status\"], style=\"crosstab\")\n",
    "# Print the results\n",
    "tdCustomer_freq2.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Box Plot - Gender by Marital Status</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-23T21:05:49.267913Z",
     "start_time": "2021-02-23T21:05:48.919795Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data = tdCustomer_freq2.result.to_pandas().reset_index().sort_values('marital_status')\n",
    "# get center and width\n",
    "fig = px.bar(data, x=\"marital_status\", y=\"xcnt\",\n",
    "             color='gender', barmode='group',\n",
    "             height=500)\n",
    "\n",
    "fig.update_yaxes(title=\"Number of Customers\")\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above bar chart shows the number of customers based on the marital status and the gender of the customers.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Box Plot - Marital Status by Gender</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-23T21:06:00.662405Z",
     "start_time": "2021-02-23T21:06:00.213574Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data = tdCustomer_freq2.result.to_pandas().reset_index()\n",
    "# get center and widht\n",
    "fig = px.bar(data, x=\"gender\", y=\"xcnt\",\n",
    "             color='marital_status', barmode='group',\n",
    "             height=500)\n",
    "\n",
    "fig.update_yaxes(title=\"Num customers\")\n",
    "\n",
    "fig['layout']['xaxis']['autorange'] = \"reversed\"\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above bar chart shows the number of customers based on the marital status and the gender of the customers. The above chart shows another way of representing the same data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example 3: Next call the Frequency function on the state_code column in the Customer table, taking statistics on income.\n",
    "tdCustomer_freq3 = valib.Frequency(data=tdCustomer, columns=\"state_code\", stats_columns=\"income\")\n",
    "# Print the results\n",
    "tdCustomer_freq3.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Box Plot - Membership by State</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-23T21:06:20.330320Z",
     "start_time": "2021-02-23T21:06:19.748474Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data = tdCustomer_freq3.result.to_pandas().reset_index()\n",
    "# get center and widht\n",
    "fig = px.bar(data, x=\"xval\", y=\"xcnt\",height=500)\n",
    "fig.update_xaxes(title=\"State\",categoryorder='total descending')\n",
    "fig.update_yaxes(title=\"Number of Customers\")\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above bar chart shows the number of customers for each state.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Box Plot - \"Box and Whiskers\" Income Levels by State</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-23T16:17:18.144465Z",
     "start_time": "2021-02-23T16:17:17.762523Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data = tdCustomer_freq3.result.to_pandas().reset_index()\n",
    "fig = px.scatter(data, x=\"xval\", y=[\"xmin_income\",\"xmean_income\",\"xmax_income\",\"xstd_income\"],height=500,width=1000)\n",
    "fig.update_traces(marker=dict(size=12,line=dict(width=2,color='DarkSlateGrey')),selector=dict(mode='markers'))\n",
    "fig.update_layout(legend=dict(\n",
    "    title=\"\",\n",
    "    orientation=\"h\",\n",
    "    yanchor=\"bottom\",\n",
    "    y=1.02,\n",
    "    xanchor=\"right\",\n",
    "    x=1\n",
    "))\n",
    "\n",
    "fig.update_yaxes(title=\"Income\")\n",
    "fig.update_xaxes(title=\"State\")\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above scatter plot shows the Income of various customers in different states. The points show the minimum, maximum, mean and standard deviation of the income of various customers in different states. We can observe that the max income is in the state Texas followed by Illinois and California. The state of Virginia has the highest average (xmean) income.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Histogram Function</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>SIGNATURE:</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>> valib.Histogram(data, columns=None, bins=10, bins_with_boundaries=None, boundaries=None, quantiles=10, widths=None, exclude_columns=None, overlay_columns=None, stats_columns=None, hist_style=\"basic\", filter=None)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>DESCRIPTION:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Histogram analysis reveals the distribution of continuous numeric or date values in a column. Histogram analysis is also referred to as binning because it counts the occurrence of values in a series of numeric ranges called bins. The histogram analysis provides a number of ways to define bins, allowing multidimensional binning, overlaying of categorical data, and the calculation of numeric statistics within bins. If you set the desired number of equal sized data bins, the desired number of bins with a nearly equal number of values, a desired width, or the specific boundaries, the Histogram analysis separates the data to show its distributional properties. It does this by separating the data by bin number and gives counts and percentages over the requested rows. Percentages always sum to 100%. Separate options are available to specify a number of equal sized data bins in which the analysis determines the minimum and maximum value, as well as a user-specified minimum and maximum value. If the minimum and maximum are specified, all values less than the minimum are put in bin 0, while all values greater than the maximum are put in bin N+1. The same is true when the boundary option is specified. The Histogram analysis optionally provides subtotals within each bin of the count, percentage within the bin and percentage overall for each value or combination of values of one or more overlaid columns. Another option is provided to collect simple statistics for a binned column or another column of numeric or date type within the table, providing the minimum, maximum, mean, and standard deviation. When statistics are collected for a date type column, the standard deviation is given in units of days. Histogram analysis can be performed on columns of numeric or date data type.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>PARAMETERS:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>data:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Required Argument.Specifies the input data to perform Histogram analysis.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: teradataml DataFrame</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Required Argument.Specifies the name(s) of the column(s) to analyze. Occasionally, it can also accept permitted strings to specify all columns, or all numeric columns, or all numeric and date columns.</li>\n",
    "    </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Permitted Values: \n",
    "    <li>Name(s) of the column(s) in \"data\".</li>\n",
    "    <li>Pre-defined strings: </li>\n",
    "    <li style = 'font-size:14px;font-family:Arial;color:#00233C'>'all' – all columns</li>\n",
    "    <li style = 'font-size:14px;font-family:Arial;color:#00233C'>'allnumeric' – all numeric columns</li>\n",
    "    <li style = 'font-size:14px;font-family:Arial;color:#00233C'>'allnumericanddate' - all numeric and date columns</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>bins:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the number of equal width bins to create.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If multiple columns are requested, multiple bin sizes may be specified, such as bins = [5, 10]. If fewer sizes are specified than columns, left-over columns are associated with the default size of 10 bins.\n",
    "Default Value: 10</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: int OR list of Integers (int)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>bins_with_boundaries:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the number of bins spanning a range specified by the minimum and maximum values.</li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example, \n",
    "    <li>bins_with_boundaries = [5,0,200] </li>\n",
    "    <li>creates 5 bins ranging from 0 to 200.</li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If multiple columns are requested, multiple sets of parameters must be specified, such as bins_with_boundaries = [\"{10, 0, 200000}\", \"{5, 0, 100}\"]. Note that multiple values are provided as string with numbers enclosed in curly braces '{}'. Each such value corresponds to the value in \"columns\" argument.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: int, str OR list of Integers (int) or Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>boundaries:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the boundaries that define the bins.</li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example, \n",
    "    <li>boundaries = [0,50,100,150]</li>\n",
    "    <li>provides 3 bins between 0 and 150 (0 to 50, 50 to 100, and 100 to 150). </li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If multiple columns are requested, multiple sets of parameters must be specified, such as boundaries = [\"{0, 50000, 100000, 150000}\", \"{0, 50, 100}\"]. Note that multiple values are provided as string with numbers enclosed in curly braces '{}'. Each such value corresponds to the value in \"columns\" argument.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: int, str OR list of Integers (int) or Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>quantiles:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the number of approximately equally populated bins to create.</li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If multiple columns are requested, multiple quantile sizes may be specified, such as quantiles=5, 10. If fewer sizes are specified than columns, left-over columns are associated with the default size of 10 quantiles.\n",
    "Default Value: 10\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: int OR list of Integers (int)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>widths: </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the width of the bins to create.</li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If multiple columns are requested, multiple widths must be specified, such as widths = [5, 10]. If fewer sizes are specified than columns, an error message displays.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: int OR list of Integers (int)\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>exclude_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the name(s) of the column(s) to exclude from the analysis, if a column specifier such as 'all', 'allnumeric', 'allnumericanddate' is used in the \"columns\" argument.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>overlay_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies a categorical variable with only a few values. If an overlay column is specified, frequencies within each bin are calculated for each value of that overlay column (frequencies for crosstabs of values are given if more than one overlay column is requested). </li>\n",
    "</p>\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'>Note:Use a specific column in either \"overlay_columns\" or \"stats_columns\", but not both.\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>stats_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies a list of numeric columns/aliases for which simple statistics are calculated (minimum, maximum, mean and standard deviation) in each bin. This argument is not available for DATE columns.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'>Note: Use a specific column in either \"overlay_columns\" or \"stats_columns\", but not both.\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>hist_style:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the histogram style to use for analysis.</li>\n",
    "</p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Permitted Values:\n",
    "    <li>'basic' - Creates a histogram for individual columns.</li>\n",
    "    <li>'crosstab' - Creates a multidimensional histogram by combining columns.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: 'basic'</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>filter:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the clause to filter rows selected for analysis within Histogram.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example,\n",
    "    filter = \"cust_id > 0\"\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RETURNS:</b>\n",
    "    <li>An instance of Histogram.</li>\n",
    "    <li>Output teradataml DataFrames can be accessed using attribute references, such as HistogramObj.'attribute_name'.</li>\n",
    "    <li>Output teradataml DataFrame attribute name is: result.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RAISES:</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TeradataMlException, TypeError, ValueError\n",
    "</p>    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example 1: Perform Histogram analysis on income by specifying number of \"bins\" to show skewed distribution\n",
    "tdCustomer_hist1 = valib.Histogram(data=tdCustomer, columns=\"income\", bins=25)\n",
    "# Print the results\n",
    "tdCustomer_hist1.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Histogram of Income - 25 even width bins</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-19T16:19:27.051230Z",
     "start_time": "2021-02-19T16:19:26.696461Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data = tdCustomer_hist1.result.to_pandas().reset_index()\n",
    "data[\"xcenter\"] =  (data.xend + data.xbeg) / 2\n",
    "data[\"xwidth\"] =  data.xend - data.xbeg\n",
    "fig = go.Figure(data=[go.Bar(x=data.xcenter, y=data.xcnt, width=data.xwidth)])\n",
    "\n",
    "fig.update_layout(height=500)\n",
    "fig.update_yaxes(title=\"Number of Cusomers\")\n",
    "fig.update_xaxes(title=\"Income\")\n",
    "fig.update_layout(showlegend=False)\n",
    "fig.show() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above Histogram shows the different bins created based on the Income range and the Y axis is the number of customers which fall into the respective income range.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example 2: Perform Histogram analysis on income using \"quantiles\" to show skewed distribution\n",
    "tdCustomer_hist2 = valib.Histogram(data=tdCustomer, columns=\"income\", quantiles=25)\n",
    "# Print the results.\n",
    "tdCustomer_hist2.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Histogram of Income - 25 Quantile bins</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-19T15:57:54.853695Z",
     "start_time": "2021-02-19T15:57:54.572835Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data = tdCustomer_hist2.result.to_pandas().reset_index()\n",
    "# get center and widht\n",
    "income_center = (data.xend + data.xbeg) / 2\n",
    "income_width = data.xend - data.xbeg\n",
    "count = data.xcnt\n",
    "fig = go.Figure(data=[go.Bar(x=income_center, y=count, width=income_width)])\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above Histogram shows the representation of the Income range in bins created using the Quantile values of the Income range and the number of customers in each bin depending on the quantile value of income. This shows that the frequency of occurrence of customers in the range 130k-350k is maximum. The width of the bars indicate the frequency of the occurrence of customers between the income range.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example 3: Perform Histogram analysis on age, overlayed by state_code\n",
    "tdCustomer_hist3 = valib.Histogram(data=tdCustomer, columns=\"income\", bins=25, overlay_columns=\"state_code\")\n",
    "# Print the results\n",
    "tdCustomer_hist3.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-19T16:32:45.121620Z",
     "start_time": "2021-02-19T16:32:44.669705Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data = tdCustomer_hist3.result.to_pandas().reset_index()\n",
    "# get center and widht\n",
    "data[\"xcenter\"] =  (data.xend + data.xbeg) / 2\n",
    "data[\"xwidth\"] =  data.xend - data.xbeg\n",
    "fig = px.bar(data, x=\"xcenter\", y=\"xocnt\", color=\"ovly_state_code\")\n",
    "fig.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above Histogram shows the representation of the Income range in bins created using the Quantile values of the Income range and the number of customers in each bin depending on the quatile value of income. The income quantiles are represented by different colour for each state.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-19T17:20:28.839857Z",
     "start_time": "2021-02-19T17:20:26.373163Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data = tdCustomer_hist3.result.to_pandas().reset_index()\n",
    "# get center and widht\n",
    "data[\"xcenter\"] =  (data.xend + data.xbeg) / 2\n",
    "data[\"xwidth\"] =  data.xend - data.xbeg\n",
    "fig = px.bar(data, x=\"xcenter\", y=\"xocnt\", facet_col=\"ovly_state_code\", color=\"ovly_state_code\", facet_col_wrap=3,\n",
    "              facet_row_spacing=0.04, # default is 0.07 when facet_col_wrap is used\n",
    "              facet_col_spacing=0.04, # default is 0.03\n",
    "              height=1500, width=1200,)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graphs show the representation of the Income range for different states and represent the income range bins in each state.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>9. Data Preparation</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Transform Function</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>SIGNATURE:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>>valib.Transform(data, bins=None, derive=None, one_hot_encode=None, fillna=None, label_encode=None, rescale=None, retain=None, sigmoid=None, zscore=None, fallback=False, index_columns=None, unique_index=False, key_columns=None, allow_duplicates=None, nopi=None, filter=None)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>DESCRIPTION:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Variable Transformation analysis reads a teradataml DataFrame and produces an output containing transformed columns. This is useful when preparing data for input to an analytic algorithm. For example, a K-Means Clustering algorithm typically produces better results when the input columns are first converted to their Z-Score values to put all input variables on an equal footing, regardless of their magnitude.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Function supports following transformations:\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'> Binning – Binning replaces a continuous numeric column with a categorical one to produce ordinal values (for example, numeric categorical values where order is meaningful).\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'> Derive – The Derive transformation requires the free-form transformation be specified as a formula.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>One Hot Encoding – One Hot Encoding is useful when a categorical data element must be re-expressed as one or more numeric data elements, creating a binary numeric field for each categorical data value.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Missing Value Treatment or Null Replacement.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Label Encoding – Allows to re-express existing values of a categorical data column (variable) into a new coding scheme or to correct data quality problems and focus an analysis on a value.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Min-Max Scaling – Limits the upper and lower boundaries of the data in a continuous numeric column using a linear rescaling function based on maximum and minimum data values.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Retain - Allows copying of one or more columns into the final analytic data set.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Sigmoid – Provides rescaling of continuous numeric data using a type of sigmoid or s-shaped function.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>ZScore – Provides rescaling of continuous numeric data using Z-Scores.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>PARAMETERS:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>data:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Required Argument.Specifies the input data to perform variable transformations.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: teradataml DataFrame</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>bins:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies one or more instances of Binning Transformation. Binning replaces a continuous numeric column with a categorical one to produce ordinal values (for example, numeric categorical values where order is meaningful). Check the documentation of 'teradataml.analytics.Transformation.Binning' to know more about Binning.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: Binning OR List of Binning</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>derive:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies one or more instances of Derive Transformation. This argument allows user to perform a free form transformation using arithmetic formula. Check the documentation of 'teradataml.analytics.Transformation.Derive' to know more about Derive.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: Derive OR List of Derive</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>one_hot_encode:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies one or more instances of OneHotEncoder Transformation. One hot encoding allows user to re-express categorical data as one or more numeric data elements, creating a binary numeric field for each categorical data value. Check the documentation of 'teradataml.analytics.Transformation.OneHotEncoder' to know more about OneHotEncoder.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: OneHotEncoder OR List of OneHotEncoder</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>fillna:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies one or more instances of FillNa Transformation. This argument allows user to perform a missing value/null replacement transformation. Check the documentation of 'teradataml.analytics.Transformation.FillNa' to know more about FillNa.</li>\n",
    "</p>    \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: FillNa OR List of FillNa</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>label_encode:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies one or more instances of LabelEncoder Transformation. This allows to re-express existing values of a categorical data column (variable) into a new coding scheme. Check the documentation of 'teradataml.analytics.Transformation.LabelEncoder' to know more about LabelEncoder.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: LabelEncoder OR List of LabelEncoder</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>rescale:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies one or more instances of MinMaxScalar Transformation. This limits the upper and lower boundaries of the data in a continuous numeric column using a linear rescaling function based on maximum and minimum data values. Check the documentation of 'teradataml.analytics.Transformation.MinMaxScalar' to know more about MinMaxScalar.</li>\n",
    "</p>    \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: MinMaxScalar OR List of MinMaxScalar</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>retain:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies one or more instances of Retain Transformation. This argument allows user to retain columns from input to output. Check the documentation of 'teradataml.analytics.Transformation.Retain' to know more about Retain.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: Retain OR List of Retain</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>sigmoid:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies one or more instances of Sigmoid Transformation. This argument allows user to perform a rescaling using sigmoid transformation. Check the documentation of 'teradataml.analytics.Transformation.Sigmoid' to know more about Sigmoid.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: Sigmoid OR List of Sigmoid</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>zscore:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies one or more instances of ZScore Transformation. This argument allows user to perform a rescaling using Z-Score transformation. Check the documentation of 'teradataml.analytics.Transformation.ZScore' to know more about ZScore.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: ZScore OR List of ZScore\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>fallback:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies whether a mirrored copy of underlying table of output DataFrame is required or not.</li>\n",
    "</p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: False\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>index_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the name(s) of the output column(s) to be used as index in output DataFrame.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR List of Strings (str)\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>unique_index:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies whether the underlying output table should contain a unique primary index or not.</li>\n",
    "</p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: False\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>key_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the name(s) of the column(s) that can be unique key in input and output teradataml DataFrame. When null replacement is requested, i.e., \"fillna\" argument is used either in FillNa transformation or in combination with a Binning, Derive, OneHotEncoder, LabelEncoder, MinMaxScalar, Sigmoid, or ZScore transformation, the \"key_columns\" argument must be specified.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR List of Strings (str)\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>allow_duplicates:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies whether output should contain duplicate rows or not.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>nopi:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies whether the underlying output table should contain no index columns. When True, output table does not contain index columns.</li>\n",
    "</p>    \n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'>Note:When this argument is set to True, \"allow_duplicates\" must also be set to True.\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Types: bool\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>filter:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the clause to filter rows selected for transformation.</li>\n",
    "</p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example,\n",
    "    filter = \"cust_id > 0\"\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Types: str\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RETURNS:</b>\n",
    "    <li>An instance of Transform.</li>\n",
    "<li>Output teradataml DataFrames can be accessed using attribute references, such as TransformObj.'attribute_name'.</li>\n",
    "<li>Output teradataml DataFrame attribute name is: result.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RAISES:</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TeradataMlException, TypeError, ValueError\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First, grab customer demographic variables and create binned variables and one-hot encoded variables from the customer table.\n",
    "\n",
    "income_t = Binning(style = \"bins\", value = 100, columns = \"income\", out_columns = \"income_bins\")\n",
    "age_t = Binning(style = \"bins\", value = 10, columns = \"age\", out_columns = \"age_bins\")\n",
    "gender_t = OneHotEncoder(values = {\"M\":\"male_ind\", \"F\":\"female_ind\"}, columns = \"gender\")\n",
    "marital_status_t = OneHotEncoder(values = {1:\"single_ind\", 2:\"married_ind\", 3:\"separated_ind\", 4:\"widower_ind\"}, \n",
    "                                 columns = \"marital_status\")\n",
    "state_code_t = OneHotEncoder(values = {\"CA\":\"ca_resident_ind\", \"NY\":\"ny_resident_ind\", \n",
    "                                       \"TX\":\"tx_resident_ind\", \"IL\":\"il_resident_ind\",\n",
    "                                       \"AZ\":\"az_resident_ind\", \"OH\":\"oh_resident_ind\"}, \n",
    "                             columns = \"state_code\")\n",
    "fillna_t1 = FillNa(style = \"median\", columns = \"years_with_bank\", out_columns = \"tot_cust_years\", datatype = 'integer')\n",
    "fillna_t2 = FillNa(style = \"median\", columns = \"nbr_children\", out_columns = \"tot_children\", datatype = 'integer')\n",
    "labelencoder_t = LabelEncoder(values={\"CA\": \"CA\", \"NY\": \"NY\", \"TX\": \"TX\", \"OH\": \"OH\", \"AZ\": \"AZ\", \"IL\": \"IL\"}, \n",
    "                              columns=\"state_code\", default=\"OTHER\", datatype = 'char,6')\n",
    "\n",
    "cust = valib.Transform(data = tdCustomer,\n",
    "                       bins = [income_t, age_t],\n",
    "                       one_hot_encode = [gender_t, marital_status_t, state_code_t],\n",
    "                       fillna = [fillna_t1, fillna_t2],\n",
    "                       label_encode = labelencoder_t,\n",
    "                       key_columns = \"cust_id\",\n",
    "                      index_columns = \"cust_id\")\n",
    "\n",
    "cust.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Next, create account indicators and then calculate account balances\n",
    "\n",
    "account_type_t = OneHotEncoder(values = {\"CC\":\"cc_acct_ind\", \"CK\":\"ck_acct_ind\", \n",
    "                                         \"SV\":\"sv_acct_ind\"}, \n",
    "                               columns = \"acct_type\")\n",
    "fillna_t = FillNa(style = \"median\", columns = [\"cust_id\", \"starting_balance\", \"ending_balance\"])\n",
    "\n",
    "acct = valib.Transform(data = tdAccounts,\n",
    "                       one_hot_encode = [account_type_t],\n",
    "                       fillna = fillna_t,\n",
    "                       key_columns = \"cust_id\",\n",
    "                       index_columns= \"acct_nbr\")\n",
    "\n",
    "acct_bal = acct.result.starting_balance + acct.result.ending_balance\n",
    "\n",
    "# acct.result = acct.result.assign(cc_bal = case_when( [(acct.result.cc_acct_ind.expression == 1, acct_bal.expression)\n",
    "#                                                      ], else_=0 )\n",
    "#                         ).assign(ck_bal = case_when( [(acct.result.ck_acct_ind.expression == 1, acct_bal.expression)\n",
    "#                                                      ], else_=0 )\n",
    "#                         ).assign(sv_bal = case_when( [(acct.result.sv_acct_ind.expression == 1, acct_bal.expression)\n",
    "#                                                      ], else_=0 )\n",
    "#                         )\n",
    "\n",
    "acct.result = acct.result.assign(cc_bal = case( [(acct.result.cc_acct_ind.expression == 1, acct_bal.expression)\n",
    "                                                     ], else_=0 )\n",
    "                        ).assign(ck_bal = case( [(acct.result.ck_acct_ind.expression == 1, acct_bal.expression)\n",
    "                                                     ], else_=0 )\n",
    "                        ).assign(sv_bal = case( [(acct.result.sv_acct_ind.expression == 1, acct_bal.expression)\n",
    "                                                     ], else_=0 )\n",
    "                        )\n",
    "acct.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Next get the transaction information required for the Quarterly aggregation by pulling out the quarter the transaction was made.\n",
    "\n",
    "acct_mon = extract('month', tdTransactions.tran_date.expression).expression\n",
    "\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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join the transformed Customer table to the transformed Account table\n",
    "\n",
    "cust_acct = cust.result.join(other = acct.result, how = \"left\", on = [\"cust_id\"],\n",
    "                             lsuffix = \"cust\", rsuffix = \"acct\")\n",
    "\n",
    "\n",
    "cust_acct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Next Join the transformed Transaction table to the transformed Account table\n",
    "\n",
    "acct_tran_amt = trans.principal_amt + trans.interest_amt\n",
    "\n",
    "cust_acct_tran = cust_acct.join(other = trans, how = \"left\", on = [\"acct_nbr\"], \n",
    "                                lsuffix = \"cu_ac\", rsuffix = \"trans\"\n",
    "                       ).assign(cc_tran_amt = \n",
    "                                case( [(cust_acct.cc_acct_ind.expression == 1, acct_tran_amt.expression)\n",
    "                                           ], else_=0 )\n",
    "                       ).assign(ck_tran_amt = \n",
    "                                case( [(cust_acct.ck_acct_ind.expression == 1, acct_tran_amt.expression)\n",
    "                                           ], else_=0 )\n",
    "                       ).assign(sv_tran_amt = \n",
    "                                case( [(cust_acct.sv_acct_ind.expression == 1, acct_tran_amt.expression)\n",
    "                                           ], else_=0 )\n",
    "                       )\n",
    "\n",
    "cust_acct_tran"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Finally, aggregate and roll up by 'cust_id' all variables in the above join operation.  This pulls everything together into the \n",
    "# analytic data set.\n",
    "\n",
    "ADS_Py = cust_acct_tran.groupby(\"cust_cust_id\").agg(\n",
    "                       {\n",
    "                        \"income_bins\"     : \"max\",\n",
    "                        \"age_bins\"        : \"max\",\n",
    "                        \"tot_cust_years\"  : \"max\",\n",
    "                        \"tot_children\"    : \"max\",\n",
    "                        \"female_ind\"      : \"max\",\n",
    "                        \"single_ind\"      : \"max\",\n",
    "                        \"married_ind\"     : \"max\",\n",
    "                        \"separated_ind\"   : \"max\",\n",
    "                        \"ca_resident_ind\" : \"max\",\n",
    "                        \"ny_resident_ind\" : \"max\",\n",
    "                        \"tx_resident_ind\" : \"max\",\n",
    "                        \"il_resident_ind\" : \"max\",\n",
    "                        \"az_resident_ind\" : \"max\",\n",
    "                        \"oh_resident_ind\" : \"max\",\n",
    "                        \"state_code\"      : \"max\",\n",
    "                        \"ck_acct_ind\"     : \"max\",\n",
    "                        \"sv_acct_ind\"     : \"max\",\n",
    "                        \"cc_acct_ind\"     : \"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\"        : \"sum\",\n",
    "                        \"q2_trans\"        : \"sum\",\n",
    "                        \"q3_trans\"        : \"sum\",\n",
    "                        \"q4_trans\"        : \"sum\"\n",
    "                       }\n",
    "             )\n",
    "\n",
    "# Rename Columns because of VAL bug with MEAN parsing\n",
    "\n",
    "columns = ['cust_id','income_bins','age_bins','tot_cust_years','tot_children','female_ind',\n",
    "           'single_ind', 'married_ind', 'separated_ind', 'state_code', 'ca_resident_ind', 'ny_resident_ind',\n",
    "           'tx_resident_ind','il_resident_ind','az_resident_ind', 'oh_resident_ind',\n",
    "           'ck_acct_ind','sv_acct_ind','cc_acct_ind', 'ck_avg_bal','sv_avg_bal','cc_avg_bal',\n",
    "           'ck_avg_tran_amt','sv_avg_tran_amt','cc_avg_tran_amt','q1_trans_cnt',\n",
    "           'q2_trans_cnt','q3_trans_cnt','q4_trans_cnt']\n",
    "\n",
    "ADS_Py = ADS_Py.assign(drop_columns = True,\n",
    "                       cust_id         = ADS_Py.cust_cust_id,\n",
    "                       income_bins     = ADS_Py.max_income_bins,\n",
    "                       age_bins        = ADS_Py.max_age_bins,\n",
    "                       tot_cust_years  = ADS_Py.max_tot_cust_years,\n",
    "                       tot_children    = ADS_Py.max_tot_children,\n",
    "                       female_ind      = ADS_Py.max_female_ind,\n",
    "                       single_ind      = ADS_Py.max_single_ind,\n",
    "                       married_ind     = ADS_Py.max_married_ind,\n",
    "                       separated_ind   = ADS_Py.max_separated_ind,\n",
    "                       state_code      = ADS_Py.max_state_code,\n",
    "                       ca_resident_ind = ADS_Py.max_ca_resident_ind,\n",
    "                       ny_resident_ind = ADS_Py.max_ny_resident_ind,\n",
    "                       tx_resident_ind = ADS_Py.max_tx_resident_ind,\n",
    "                       il_resident_ind = ADS_Py.max_il_resident_ind,\n",
    "                       az_resident_ind = ADS_Py.max_az_resident_ind,\n",
    "                       oh_resident_ind = ADS_Py.max_oh_resident_ind,\n",
    "                       ck_acct_ind     = ADS_Py.max_ck_acct_ind,\n",
    "                       sv_acct_ind     = ADS_Py.max_sv_acct_ind,\n",
    "                       cc_acct_ind     = ADS_Py.max_cc_acct_ind,\n",
    "                       ck_avg_bal      = ADS_Py.mean_ck_bal,\n",
    "                       sv_avg_bal      = ADS_Py.mean_sv_bal,\n",
    "                       cc_avg_bal      = ADS_Py.mean_cc_bal,\n",
    "                       ck_avg_tran_amt = ADS_Py.mean_ck_tran_amt,\n",
    "                       sv_avg_tran_amt = ADS_Py.mean_sv_tran_amt,\n",
    "                       cc_avg_tran_amt = ADS_Py.mean_cc_tran_amt,\n",
    "                       q1_trans_cnt    = ADS_Py.sum_q1_trans,\n",
    "                       q2_trans_cnt    = ADS_Py.sum_q2_trans,\n",
    "                       q3_trans_cnt    = ADS_Py.sum_q3_trans,\n",
    "                       q4_trans_cnt    = ADS_Py.sum_q4_trans).select(columns)\n",
    "\n",
    "copy_to_sql(ADS_Py, table_name=\"ADS_Py\", if_exists=\"replace\")\n",
    "ADS_Py = DataFrame(\"ADS_Py\")\n",
    "ADS_Py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is an alternative step to the above - however VAL 2.0.0.2 has a bug parsing column names with \"MEAN\" in them.  Therefore, this step is skipped\n",
    "# in lieu of the cell above.\n",
    "\n",
    "# Perform final NULL value replacement for customers without a checking, savings or credit account.\n",
    "\n",
    "#retain_t = Retain(columns=[\"cust_id\", \"income_bins\", \"age_bins\", \"tot_cust_years\", \"tot_children\", \n",
    "#                           \"female_ind\", \"single_ind\", \"married_ind\", \"separated_ind\", \n",
    "#                           \"ca_resident_ind\", \"ny_resident_ind\", \"tx_resident_ind\", \"il_resident_ind\", \n",
    "#                           \"az_resident_ind\", \"oh_resident_ind\", \"state_code\"])\n",
    "\n",
    "#fillna_t = FillNa(style=\"literal\", \n",
    "#                  value=0, \n",
    "#                  columns=[\"ck_acct_ind\", \"sv_acct_ind\", \"cc_acct_ind\", \"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_Py = valib.Transform(data = ADS_Py,\n",
    "#                         retain = retain_t,\n",
    "#                         fillna = fillna_t,\n",
    "#                         key_columns = \"cust_id\")\n",
    "\n",
    "#copy_to_sql(ADS_Py.result, table_name=\"ADS_Py\", if_exists=\"replace\")\n",
    "#ADS_Py = DataFrame(\"ADS_Py\")\n",
    "#ADS_Py.to_pandas().head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>10. Matrix Function</b></p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Correlation, Covariance (E)SSCP</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>SIGNATURE:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>>valib.Matrix(data, columns=None, exclude_columns=None, group_columns=None, matrix_output=\"columns\", type=\"ESSCCP\", handle_nulls=\"IGNORE\", filter=None)\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>DESCRIPTION:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Matrix builds an extended sum-of-squares-and-cross-products (ESSCP) matrix or other derived matrix type from a teradataml DataFrame. Matrix does this with the help of Teradata CALCMATRIX table operator provided in Teradata Vantage. The purpose in building a matrix depends on the type of matrix built. For example, when a correlation matrix is built, view it to determine the correlations or relationships between the various columns in the matrix.\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>PARAMETERS:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>data:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Required Argument.Specifies the input data to build matrix from.</li>\n",
    "</p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: teradataml DataFrame\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Required Argument.Specifies the name(s) of the column(s) used in building one or more matrices. Occasionally, it can also accept permitted strings to specify all columns, or all numeric columns.</li>\n",
    "    </p>\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'>Note:Do not use the following column names, as these are reserved for use by the CALCMATRIX table operator:'rownum', 'rowname', 'c', or 's'.\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Permitted Values: </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Name(s) of the columns in \"data\".</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Pre-defined strings: </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>'all' – all columns</li>\n",
    "    <li>'allnumeric' – all numeric columns</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>exclude_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the name(s) of the column(s) to exclude from the analysis, if a column specifier such as 'all', 'allnumeric' is used in the \"columns\" argument.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For convenience, when the \"exclude_columns\" argument is used, dependent variable and group by columns, if any, are automatically excluded as input columns and do not need to be included in this argument.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>group_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the name(s) of the column(s) in input teradataml DataFrame to perform build a separate matrix for each combination. If specified, group by columns divide the input into parts, one for each combination of values in the group by columns. For each combination of values, a separate matrix is built, though they are all stored in the same output.</li>\n",
    "</p>\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'>Note:Do not use the following column names, as these are reserved for use by the CALCMATRIX table operator:'rownum', 'rowname', 'c', or 's'.\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Types: str OR list of Strings (str)\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>matrix_output:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the type of matrix output. Matrix output can either be returned as COLUMNS in a output teradataml DataFrame or as VARBYTE values, one per column, in a reduced output teradataml DataFrame. </li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "Permitted Values: 'columns', 'varbyte'\n",
    "Default Value: 'columns'\n",
    "\n",
    "Types: str\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>type:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the type of matrix to build.</li></p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Permitted Values: \n",
    "    <li>'SSCP' - sum-of-squares-and-cross-products matrix</li>\n",
    "    <li>'ESSCP' - Extended-sum-of-squares-and-cross-products matrix </li>\n",
    "    <li>'CSSCP' - Corrected-sum-of-squares-and-cross-products matrix</li>\n",
    "    <li>'COV' - Covariance matrix</li>\n",
    "    <li>'COR' - Correlation matrix</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: 'ESSCCP'\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str \n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>handle_nulls:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies a way to treat null values in selected columns. When set to IGNORE, the row that contains the NULL value in a selected column is omitted from processing. When set to ZERO, the NULL value is replaced with zero (0) in calculations.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Permitted Values: 'IGNORE', 'ZERO'\n",
    "Default Value: 'IGNORE'\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>filter:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the clause to filter rows selected for building the matrix.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "For example,filter = \"cust_id > 0\"\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RETURNS:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>An instance of Matrix.</li>\n",
    "    <li>Output teradataml DataFrames can be accessed using attribute references, such as MatrixObj.'attribute_name'.</li>\n",
    "    <li>Output teradataml DataFrame attribute name is: result.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RAISES:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "TeradataMlException, TypeError, ValueError\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Cor_Mat = valib.Matrix(data=ADS_Py, columns='all', exclude_columns=\"state_code\", type=\"COR\")\n",
    "# Print the results.\n",
    "Cor_Mat.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-23T18:24:49.282004Z",
     "start_time": "2021-02-23T18:24:49.279336Z"
    }
   },
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "import plotly.figure_factory as ff\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-23T21:58:06.713822Z",
     "start_time": "2021-02-23T21:58:02.818673Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "data = Cor_Mat.result.to_pandas().reset_index()\n",
    "shaped_data = data.set_index(\"rowname\").iloc[:,-len(data):].reindex(sorted(data.set_index(\"rowname\").iloc[:,-len(data):]), axis=0).reindex(sorted(data.set_index(\"rowname\").iloc[:,-len(data):]), axis=1)\n",
    "\n",
    "fig = px.imshow(shaped_data, \n",
    "                x=shaped_data.index, \n",
    "                y=shaped_data.columns,\n",
    "                color_continuous_scale=[\"lightblue\",\"red\"], \n",
    "                zmin=-1,\n",
    "                zmax=1,\n",
    "                height=1000,\n",
    "               )\n",
    "\n",
    "#z= [[np.round(float(i), 2) for i in nested] for nested in z]\n",
    "fig.update_xaxes(side=\"top\")          \n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the corelated matrix of each column as specified on the x and y axis and the colour of the squares represent the range in which the values of these columns.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-23T21:58:25.597852Z",
     "start_time": "2021-02-23T21:58:17.605765Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "plt.rcParams['figure.figsize'] = (20.0, 20.0)\n",
    "p = sns.heatmap(shaped_data, cmap='coolwarm', annot=True, annot_kws={'size':10})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the corelated matrix of each column as specified on the x and y axis and the colour bar and the colour of the squares represent the range of the values of these columns and shows the values for each intersection.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-02-23T21:58:41.167077Z",
     "start_time": "2021-02-23T21:58:39.545711Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "plt.rcParams['figure.figsize'] = (25, 10)\n",
    "\n",
    "z = shaped_data.values\n",
    "x=list(shaped_data.index)\n",
    "y=list(shaped_data.columns)\n",
    "z_text = [[np.round(float(i), 2) for i in nested] for nested in z]\n",
    "colorscale = [[0,'lightblue'], [1, 'red']]\n",
    "font_colors = ['black','white']\n",
    "\n",
    "\n",
    "fig = ff.create_annotated_heatmap(z, x=x, y=y, annotation_text=z_text, colorscale=colorscale, font_colors=font_colors)\n",
    "\n",
    "# Make text size smaller\n",
    "for i in range(len(fig.layout.annotations)):\n",
    "    fig.layout.annotations[i].font.size = 8\n",
    "\n",
    "fig['layout'].update(\n",
    "    width=1050,\n",
    "    height=1200,\n",
    "    autosize=False\n",
    ")\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the corelated matrix of each column as specified on the x and y axis and the colour of the squares represent the range of the values of these columns and shows the values for each intersection.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Cor_Mat = valib.Matrix(data=ADS_Py, columns='all', exclude_columns=\"cust_id\", group_columns = \"state_code\", type=\"COR\")\n",
    "\n",
    "# Print the results.\n",
    "Cor_Mat.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>11. Logistic Regression </b></p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>LogReg() Function:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>SIGNATURE:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>> valib.LogReg(data, matrix_data=None, columns=None, response_column=None, backward=None, backward_only=None, exclude_columns=None, cond_ind_threshold=30, constant=True, convergence=0.001, entrance_criterion=0.05, forward=None, forward_only=None, group_columns=None, lift_output=None, max_iter=100, mem_size=None, near_dep_report=None, remove_criterion=0.05, response_value=None, sample=None, stats_output=False, stepwise=False, success_output=False, start_threshold=None, end_threshold=None, increment_threshold=None, threshold_output=False, variance_prop_threshold=0.5)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>DESCRIPTION:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Logistic Regression is one of the most widely used types of statistical analysis. In Logistic Regression, a set of independent variables (in this case, columns) is processed to predict the value of a dependent variable (column) that assumes two values referred to as response (1) and non-response (0). The user can specify which value of the dependent variable to treat as the response, and all other values assumed by the dependent variable are treated as non-respsonse. The result is not, however, a continuous numeric variable as seen in Linear Regression, but rather a probability between 0 and 1 that the response value is assumed by the dependent variable.\n",
    "There are many types of analysis that lend themselves to the use of Logistic Regression, and when scoring a model, benefit from the estimation of a probability rather than a fixed value. For example, when predicting who should be targeted for a marketing campaign, the scored customers can be ordered by the predicted probability from most to least likely, and the top n values taken from the customer list.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Some of the key features of Logistic Regression are outlined below.</p>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The Teradata table operator CALCMATRIX is used to build an ESSCP matrix for purposes of validating the input data, such as by checking for constant values. Also, to avoid rebuilding this matrix every time the algorithm is run, the user may run the Matrix Analysis separately, saving an ESSCP matrix in a teradataml DataFrame that can then be input to Logistic Regression. Refer \"matrix_data\" argument.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>One or more group by columns can optionally be specified so that an input matrix is built for each combination of group by column values, and subsequently a separate Logistic Regression model is built for each matrix. To achieve this, the names of the group by columns are passed to CALCMATRIX as parameters, so it includes them as columns in the matrix data it creates. Refer \"group_columns\" argument.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The stepwise feature for Logistic Regression is a technique for selecting the independent variables in a logistic model. It consists of different methods of 'trying' variables and adding or removing them from a model through a series of forward and backward steps described in the parameter section. </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>A Statistics data is available, displaying the mean and standard deviation of each model variable. Refer to the \"stats_output\" argument.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>A Success data is available, displaying counts of predicted versus actual values of the dependent variable in the logistic model.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>A Multi-Threshold Success Table is available. Refer \"threshold_output\" argument.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>A Lift Table, such as would be used to build a Lift Chart, is available. Refer \"lift_output\" argument.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>A Near Dependency Report is available to identify two or more columns that may be collinear. </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The algorithm is partially scalable because the size of each input matrix depends only on the number of independent variables (columns) and not on the size of the input data. The calculations performed on the client workstation however are not scalable when group by columns are used, because each model is built serially based on each matrix in the matrix data.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>PARAMETERS:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>data:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Required Argument.Specifies the input data to build a logistic regression model from.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: teradataml DataFrame</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Required Argument.Specifies the name(s) of the column(s) representing the independent variables used in building a logistic regression model. Occasionally, it can also accept permitted strings to specify all columns, or all numeric columns.</li>\n",
    "</p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Permitted Values: </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Name(s) of the column(s) in \"data\".</li>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Pre-defined strings: </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>'all' – all columns</li>\n",
    "    <li>'allnumeric' – all numeric columns</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>response_column:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li>Required Argument.Specifies the name of the column that represents the dependent variable being predicted.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>backward:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies whether to take backward steps or not. Backward steps, i.e., removing variables from a model, use the P-value of the T-statistic, i.e., the ratio of a B-coefficient to its standard error. The variable (column) with the largest P-value is removed if the P-value exceeds the criterion to remove.</li>\n",
    "    </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>backward_only:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies whether to use only backward technique or not. This technique is similar to the backward technique, except that a forward step is not performed. It starts with all independent variables in the model. Backward steps are executed until no more are possible.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>exclude_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the name(s) of the column(s) to exclude from the analysis, if a column specifier such as 'all', 'allnumeric' is used in the \"columns\" argument. By default, when the \"exclude_columns\" argument is used, dependent variable and group by columns, if any, are automatically excluded as input columns and do not need to be included as \"exclude_columns\".</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>cond_ind_threshold:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the condition index threshold value to use while generating near dependency report. This is used when \"near_dep_report\" is set to True.</li>\n",
    "Default Value: 30\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: int\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>constant:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies whether the logistic model includes a constant term or not. When set to True, model includes a constant term.</li>\n",
    "    </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: True</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>convergence:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the convergence criterion such that the algorithm stops iterating when the change in log likelihood function falls below this value.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: 0.001</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>entrance_criterion:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the criterion to enter a variable into the model. The W-statistic chi-square P-value must be less than this value for a variable to be added.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: 0.05</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>forward:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies whether to use forward technique or not. When set to True, in this technique, starting with no independent variables in the model, a forward step is performed, adding the \"best\" choice, followed by a backward step, removing the \"worst\" choice. Refer to the \"stepwise\" argument for a description of the steps in this technique.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>forward_only:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies whether to use only forward technique or not. This technique is similar to the forward technique, except that a backward step is not performed. </li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>group_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li>Optional Argument.Specifies the name(s) of the column(s) dividing the input into partitions, one for each combination of values in the group by columns. For each partition or combination of values a separate logistic model and XML report is built.</li>\n",
    "</p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str) </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>lift_output:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies whether to build a lift chart or not and add it in the functions output string. It splits up the computed probability values into deciles with the usual counts and percentages to demonstrate what happens when more and more rows of ordered probabilities are accumulated.</li>\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>matrix_data:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the input matrix data to use for the analysis. Instead of internally building a matrix with the Matrix function each time this analysis is performed, the user may build an ESSCP Matrix once with the Matrix.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Analysis using Matrix() function. The matrix can subsequently be read from this data instead of re-building it each time. If this is specified, the columns specified with the \"columns\" argument should be a subset of the columns in this matrix and can be specified in any order. The columns must however all be present in the matrix. Further, if group by columns are specified in the matrix, these same group by columns must be specified in this analysis.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: teradataml DataFrame</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>max_iter:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies The maximum number of attempts to converge on a solution.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: 100</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: int</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>mem_size:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the memory size in megabytes to allocate for in-memory Logistic Regression. If there is too much data to fit in this amount of memory or is set to 0 or argument is not specified, normal SQL processing is performed. \n",
    "</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: int</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>near_dep_report:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies whether to produce an XML report showing columns that may be collinear as part of the output or not. The report is included in the XML output only if collinearity is detected.</li>\n",
    "<li>Two threshold arguments are available for this report, \"cond_ind_threshold\" and \"variance_prop_threshold\". </li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>remove_criterion:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the criterion to remove a variable from the model. The T-Statistic P-value must be greater than this value for a variable to be removed.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: 0.05</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>response_value:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the value assumed by the dependent column that is to be treated as the response value.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>sample:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies whether to use sample of the data to be read into memory for processing, if the memory size available is less than the amount of data to process. When set to True, a sample of data is read.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>stats_output:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies whether an optional data quality report should be delivered in the function's XML output string or not, which includes the mean and standard deviation of each model variable, derived from an ESSCP matrix.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: False</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>stepwise:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies whether to perform a stepwise procedure or not.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Forward steps, i.e., adding variables to a model, add the variable with the smallest chi-square P-value connected to its special W-statistic, provided the P-value is less than the criterion to enter. \n",
    "Backward steps, i.e., removing variables from a model, use the P-value of the T-statistic, i.e., the ratio of a B-coefficient to its standard error. The variable (column) with the largest P-value is removed if the P-value exceeds the criterion to remove.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: False</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>success_output:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies whether an optional success report should be delivered in the function's XML output string or not, which includes the displaying counts of predicted versus actual values of the dependent variable of the logistic regression model. This report is similar to the Decision Tree Confusion Matrix, but the success report only includes two values of the dependent variable, namely response versus non-response.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: False</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>start_threshold:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the beginning threshold value utilized in the Multi-Threshold Success output.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float, int</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>end_threshold:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the ending threshold value utilized in the Multi-Threshold Success output.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float, int</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>increment_threshold:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the difference in threshold values between adjacent rows in the Multi-Threshold Success output.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float, int</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>threshold_output:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies whether the Multi-Threshold Success output should be produced or not and included in the XML output string in the result. </li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This report can be thought of as a table where each row is a Prediction Success Table, and each row has a different threshold value as generated by the \"start_threshold\", \"end_threshold\", and \"increment_threshold\" arguments. What is meant by a threshold here is the value above which the predicted probability indicates a response.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: False</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: bool</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>variance_prop_threshold:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the variance proportion threshold value to use while generating near dependency report. This is used when \"near_dep_report\" is set to True.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: 0.5</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RETURNS:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>An instance of LogReg.</li>\n",
    "<li>Output teradataml DataFrames can be accessed using attribute references, such as LogRegObj.'attribute_name'.</li>\n",
    "    <li>Output teradataml DataFrame attribute names are:</li>\n",
    "    <ol style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "        <li>model</li>\n",
    "        <li>statistical_measures</li>\n",
    "        <li>xml_reports</li>\n",
    "    </ol>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RAISES:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TeradataMlException, TypeError, ValueError</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1.  Using the ADS_Py table, let's build a logistic regression model to predict the customer bases propensity to open a credit card account (cc_acct_ind) based upon all non-credit card variables in the analytic data set.  The model coefficients and variable statistics are created within the model and statistical_measure objects.  The reports for the success table, threshold table and lift table are returned in an XML string within the xml_reports object.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Logit_Model = valib.LogReg(data=ADS_Py, \n",
    "                           columns='all', \n",
    "                           exclude_columns=\"cust_id, state_code, cc_avg_tran_amt, cc_avg_bal\",\n",
    "                           response_column=\"cc_acct_ind\", \n",
    "                           response_value=1,\n",
    "                           threshold_output='true',\n",
    "                           near_dep_report='true', \n",
    "                           cond_ind_threshold=25,\n",
    "                           variance_prop_threshold=0.5)\n",
    "\n",
    "# Print the results using pandas for readability"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Logit_Model.model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Logit_Model.statistical_measures.to_pandas().head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#obj = valib.XmlToHtmlReport(data=Logit_Model.model, analysis_type=\"logistic\")\n",
    "#print(obj.result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We execute a SQL statement to convert the XML reports to HTML for display purposes.  The following block of code does just that:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "cursor=eng.raw_connection().cursor()\n",
    "parms = 'database=' + Logit_Model.xml_reports._table_name.split('.')[0] + ';tablename=' + Logit_Model.xml_reports._table_name.split('.')[1] + ';analysistype=logistic'\n",
    "cursor.callproc(\"val.td_analyze\", ['report',parms])\n",
    "cursor.nextset()\n",
    "z=cursor.fetchall()\n",
    "# Loop in case of group by generating multiple reports\n",
    "for i in range(len(z)):\n",
    "    display(HTML(z[i][1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>2.  Using the same table, let's build a logistic regression model to predict the customer bases propensity to open a credit card account (cc_acct_ind), by state,  based upon all non-credit card variables in the analytic data set.  This type of micro-modelling is done using the groupby option.  The model coefficients and variable statistics are created within the model and statistical_measure objects.  The reports for the success table, threshold table and lift table are returned in an XML string within the xml_reports object.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Logit_Model2 = valib.LogReg(data=ADS_Py, \n",
    "                            columns='all', \n",
    "                            exclude_columns=\"cust_id, az_resident_ind, il_resident_ind, oh_resident_ind, tx_resident_ind, ny_resident_ind, ca_resident_ind, cc_avg_tran_amt, cc_avg_bal\",\n",
    "                            response_column=\"cc_acct_ind\", \n",
    "                            response_value=1,\n",
    "                            threshold_output='true',\n",
    "                            near_dep_report='true', \n",
    "                            cond_ind_threshold=25,\n",
    "                            variance_prop_threshold=0.5,\n",
    "                            group_columns=\"state_code\")\n",
    "\n",
    "# Print the results using pandas for readability"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Logit_Model2.model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Logit_Model2.statistical_measures.to_pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We execute a SQL statement to convert the XML reports to HTML for display purposes.  The following block of code does just that:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "cursor=eng.raw_connection().cursor()\n",
    "parms = 'database=' + Logit_Model2.xml_reports._table_name.split('.')[0] + ';tablename=' + Logit_Model2.xml_reports._table_name.split('.')[1] + ';analysistype=logistic'\n",
    "cursor.callproc(\"val.td_analyze\", ['report',parms])\n",
    "cursor.nextset()\n",
    "z=cursor.fetchall()\n",
    "# Loop in case of group by generating multiple reports\n",
    "for i in range(len(z)):\n",
    "    display(HTML(z[i][1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>12. Logistic Regression Evaluation</b></p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>LogRegEvaluator() Function:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>SIGNATURE:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>>valib.LogRegEvaluator(data, model, estimate_column=None, index_columns=None, prob_column=None, accumulate=None, prob_threshold=0.5, start_threshold=None, end_threshold=None, increment_threshold=None)\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>DESCRIPTION:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Logistic Regression function model can be passed to this function to generate evaluation reports. Function produces the result containing the following reports in XML format:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Success result – This output is delivered in the function's XML output string, displaying counts of predicted versus actual values of the dependent variable of the logistic regression model. This report is similar to the Decision Tree Confusion Matrix, but the Success output only includes two values of the dependent variable, namely response versus non-response.</li>\n",
    "<li>Multi-Threshold Success result - This output is delivered in the function's XML output string. Report can be thought of as a table where each row is a Prediction Success Output, and each row has a different threshold value as generated by the \"start_threshold\", \"end_threshold\", and \"increment_threshold\" arguments. What is meant by a threshold here is the value above which the predicted probability indicates a response.</li>\n",
    "<li>Lift result – Result containing information, such as would be required to build a lift chart is available. It splits up the computed probability values into deciles with the usual counts and percentages to demonstrate what happens when more and more rows of ordered probabilities are accumulated. It is delivered in the function's XML output string.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>PARAMETERS:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>data:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Required Argument.Specifies the input data to evaluate.</li>\n",
    "</p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: teradataml DataFrame</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>model:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Required Argument.Specifies the input containing the logistic model to use in scoring. This must be the \"model\" teradataml DataFrame generated by LogReg() function from VALIB or a teradataml DataFrame created on a table generated by 'logistic' function from Vantage Analytic Library.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: teradataml DataFrame</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>estimate_column:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Required Argument.Specifies the name of a column in the score output containing the estimated value of the dependent variable (column).</li></p>\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'>Notes:\n",
    "    <ol style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "        <li>Either \"estimate_column\" or \"prob_column\" must be requested.</li>\n",
    "    <li>If the estimate column is not unique in the score output, '_tm_' is automatically placed in front of the name.</li>\n",
    "    </ol></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>index_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the name(s) of the column(s) representing the primary index of the score output. By default, the primary index columns of the score output are the primary index columns of the input. In addition, the index columns need to form a unique key for the score output. Otherwise, there are more than one score for a given observation.\n",
    "    </li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>prob_column:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the name of a column in the score output containing the probability that the dependent value is equal to the response value.</li></p>\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'>Notes:\n",
    "    <ol style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "        <li>Either \"estimate_column\" or \"prob_column\" must be requested.</li>\n",
    "    <li>If the probability column is not unique in the score output, '_tm_' is automatically placed in front of the name.</li>\n",
    "</ol></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>accumulate:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the name(s) of the column(s) from the input to retain in the output. </li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>prob_threshold:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the probability threshold value. When the probability of the dependent variable being 1 is greater than or equal to this value, the estimated value of the dependent variable is 1. If less than this value, the estimated value is 0.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: 0.5</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float, int</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>start_threshold:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Optional Argument.Specifies the beginning threshold value utilized in the Multi-Threshold Success output.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float, int</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>end_threshold:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the ending threshold value utilized in the Multi-Threshold Success output.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float, int</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>increment_threshold:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the difference in threshold values between adjacent rows in the Multi-Threshold Success output.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float, int</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RETURNS:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>An instance of LogRegEvaluator.</li>\n",
    "<li>Output teradataml DataFrames can be accessed using attribute references, such as LogRegEvaluatorObj.'attribute_name'.</li>\n",
    "    <li>Output teradataml DataFrame attribute name is: result</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RAISES:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TeradataMlException, TypeError, ValueError</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>3. Evaluate the logistic regression model - note that this option is not available when the group by option is used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Logit_Model_Eval = valib.LogRegEvaluator(data=ADS_Py, \n",
    "                                         model=Logit_Model.model, \n",
    "                                         index_columns=\"cust_id\",\n",
    "                                         prob_column=\"Probability\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "cursor=eng.raw_connection().cursor()\n",
    "parms = 'database=' + Logit_Model_Eval.result._table_name.split('.')[0] + ';tablename=' + Logit_Model_Eval.result._table_name.split('.')[1] + ';analysistype=logisticscore'\n",
    "cursor.callproc(\"val.td_analyze\", ['report',parms])\n",
    "cursor.nextset()\n",
    "z=cursor.fetchall()\n",
    "# Loop in case of group by generating multiple reports\n",
    "for i in range(len(z)):\n",
    "    display(HTML(z[i][1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>13. Logistic Regression Scoring</b></p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>LogRegPredict() Function:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>SIGNATURE:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>> valib.LogRegPredict(data, model, estimate_column=None, index_columns=None, prob_column=None, accumulate=None, prob_threshold=0.5)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>DESCRIPTION:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Logistic Regression function model can be passed to a Logistic Regression Scoring function to create a score output containing predicted values of the dependent variable. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>PARAMETERS:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>data:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Required Argument.Specifies the input data to score.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: teradataml DataFrame</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>model:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Required Argument.Specifies the input containing the logistic model to use in scoring. This must be the \"model\" teradataml DataFrame generated by LogReg() function from VALIB or a teradataml DataFrame created on a table generated by 'logistic' function from Vantage Analytic Library.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: teradataml DataFrame</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>estimate_column:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Required Argument.Specifies the name of a column in the score output containing the estimated value of the dependent variable (column).</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Notes:\n",
    "    <li>Either \"estimate_column\" or \"prob_column\" must be requested.</li>\n",
    "    <li>If the estimate column is not unique in the score output, '_tm_' is automatically placed in front of the name.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>index_columns:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the name(s) of the column(s) representing the primary index of the score output. By default, the primary index columns of the score output are the primary index columns of the input. In addition, the index columns need to form a unique key for the score output. Otherwise, there are more than one score for a given observation.</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>prob_column:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the name of a column in the score output containing the probability that the dependent value is equal to the response value.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Notes:\n",
    "    <li>Either \"estimate_column\" or \"prob_column\" must be requested.</li>\n",
    "    <li>If the probability column is not unique in the score output, '_tm_' is automatically placed in front of the name.</li>\n",
    "    </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>accumulate:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the name(s) of the column(s) from the input to retain in the output. </li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: str OR list of Strings (str)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>prob_threshold:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Optional Argument.Specifies the probability threshold value. When the probability of the dependent variable being 1 is greater than or equal to this value, the estimated value of the dependent variable is 1. If less than this value, the estimated value is 0.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Default Value: 0.5</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Types: float, int</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RETURNS:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>An instance of LogRegPredict.</li>\n",
    "<li>Output teradataml DataFrames can be accessed using attribute references, such as LogRegPredictObj.'attribute_name'.</li>\n",
    "    <li>Output teradataml DataFrame attribute name is: result</li></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RAISES:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>TeradataMlException, TypeError, ValueError</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>4. Score the Logistic Regression Models</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Logit_Model_Score = valib.LogRegPredict(data=ADS_Py, \n",
    "                                        model=Logit_Model.model, \n",
    "                                        index_columns=\"cust_id\",\n",
    "                                        estimate_column=\"cc_acct_ind\",\n",
    "                                        prob_column=\"Probability\")\n",
    " \n",
    "# Print the results using pandas for readability\n",
    "\n",
    "Logit_Model_Score.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Logit_Model_Score2 = valib.LogRegPredict(data=ADS_Py, \n",
    "                                         model=Logit_Model2.model, \n",
    "                                         index_columns=\"cust_id\",\n",
    "                                         estimate_column=\"cc_acct_ind\",\n",
    "                                         prob_column=\"Probability\")\n",
    " \n",
    "# Print the results using pandas for readability\n",
    "\n",
    "Logit_Model_Score2.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>14. Cleanup</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Clean up work tables to prevent error next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['Accounts', 'Customer','Transactions']\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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Databases and Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note:</b> In case you plan to execute the other notebooks in the VantageAnalyticLibrary , do not run the <b>remove_data</b> procedure below. Also in case you are not removing the databases and tables here , please make sure that you do not execute the <b>get_data</b> procedure in the next VantageAnalyticLibrary notebook.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_Financial');\" \n",
    "#Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "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
}