UseCases/Telco_Customer_Churn/Telco_Customer_Churn_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "78ad8a32",
   "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",
    "       Telco Customer Churn\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "454c6ae9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Customer churn is a concern for all companies, but the complexity makes it difficult to track. Customers may leave due to various reasons such dissatisfaction with service quality, pricing, customer service, or finding better alternatives from competitors. Although some churn may be expected, companies aim to retain their customers to avoid using additional resources to find new customers. Thus, with the help of Teradata Vantage, companies can attain their goal of identifying the factors contributing to the churn, so they can take appropriate measures to retain customers. Vantage’s capabilities allow companies to analyze large amounts of customer data, such as usage patterns, billing information, demographics, and interactions, to find patterns that may indicate customers who are at risk of churning. Plus, Teradata’s machine learning and predictive analytics can be used to build models to predict customers which are likely to churn in the future. This information will give companies the chance to intervene, including sending targeted marketing campaigns, personalized offers, improved customer service, or addressing customer concern.</p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Values</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Determine characteristics of the company that are less favorable to consumers.</li>\n",
    "    <li>Identify customers at risk of leaving.</li>\n",
    "    <li>Identify customer behavior before churning to allow for time to intervene to save customers.</li>\n",
    "    <li>Determine when to begin targeted marketing or offer promotions.</li>\n",
    "    <li>Increase customer retention and reduce churn rate.</li>\n",
    " </ul>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Why Vantage?</b></p>  \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Traditional ML and AI development and deployment pipelines require users to manually combine various tools and techniques across the lifecycle.  This leads to lengthy, fragile, manual, error-prone processes that are, in many cases, impossible to migrate out of the lab and into production in order to realize business value.<br>ClearScape Analytics helps to solve this “development to deployment gap” by providing highly scalable, performant, and easy-to-use analytic capabilities that address all aspects of the development lifecycle.  The same tools and techniques that data scientists use in development can be seamlessly deployed into production using the same code, platform, and operational pipeline.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Managing telco churn is complex and requires continuous monitoring, analysis, and proactive customer engagement strategies. By using data and advanced analytics, telecom companies can better understand customer behavior and preferences, and take proactive measures to retain customers and maintain profitability.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Let's demonstrate this use case with sample data using InDb analytics in Vantage which can pre-process and analyze huge amounts of data and at scale.   \n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22173c1b-a4eb-4cd7-b0ae-ba68bc39aba2",
   "metadata": {},
   "source": [
    "<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, Import python packages and explore the dataset</b></p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5199b325-11f8-4dbd-a13f-fadf334c58c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import libraries\n",
    "import matplotlib.pyplot as plt \n",
    "import getpass\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "warnings.simplefilter(action='ignore', category=DeprecationWarning)\n",
    "warnings.simplefilter(action='ignore', category=RuntimeWarning)\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "\n",
    "from teradataml import *\n",
    "\n",
    "import plotly.express as px\n",
    "from plotly.subplots import make_subplots\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "\n",
    "from sklearn.metrics import mean_absolute_error\n",
    "from sklearn.metrics import roc_auc_score\n",
    "from sklearn.metrics import roc_curve\n",
    "display.max_rows=5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "113ada1d-a5ff-4d5e-9145-f0f03b26b3f2",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will be prompted to provide the password. We will enter the password, press the Enter key, and then use the down arrow to go to the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb0165cd-c7eb-40cc-8eac-84ad0ec3ba52",
   "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,
   "id": "fb882973-a186-4a9b-addd-da9cad1feba1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Telco_Customer_Churn_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d8f8388-e318-4bf4-84c1-7dfcf2d1dd40",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'> <b>Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. We have the option of either running the demo using foreign tables to access the data without using any storage on our environment or downloading the data to local storage, which may yield somewhat faster execution. However, we need to consider available storage. There are two statements in the following cell, and one is commented out. We may switch which mode we choose by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3460a89-d570-488e-a97a-39423afa7b27",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_Telco_cloud');\"\n",
    " # takes about 30 seconds, estimated space: 0 MB\n",
    "#%run -i ../run_procedure.py \"call get_data('DEMO_Telco_local');\" \n",
    "# takes about 1 minute 30 seconds, estimated space: 4 MB"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53237f92-8be4-40eb-941b-6c5b9a778146",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – We should execute the below step only if we want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1dbc1f31-38b7-4d42-8573-5f05767c1591",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdd6dd8c",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Data Exploration</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13288769-f1b3-40a5-8cad-95e5f4ae92fd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Customer Churn</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us start by creating a \"Virtual DataFrame\" that points directly to the dataset in Vantage. We then begin our analysis by checking the shape of the DataFrame and examining the data types of all its columns.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d40df274-d9cb-439b-93bb-343d317f052c",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = DataFrame(in_schema(\"DEMO_Telco\", \"Customer_Churn\"))\n",
    "tdf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d620292-c936-4546-89eb-59fd50c35221",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can check the demographics of data by shape and info method.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "279ee6be-9288-41ae-b21a-f2389add4623",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Shape of the data: \", tdf.shape)\n",
    "tdf.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d9927f9-f0f6-4f45-966d-7b7b2ca36f84",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> As we can see from above result our dataset has 7043 rows with 21 columns.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b03454cf-d47a-4edc-aea4-5b517b7da9d6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;color:#00233C'><b>Summary of Columns</b><br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;'>We can use the <b>ColumnSummary</b> function for quickly examining the columns, their datatypes, and summary of NULLs/non-NULLs for a given table. </p>  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21eece2c-533a-40e3-bcad-4ed4bb2b6cf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ColumnSummary\n",
    "obj = ColumnSummary(data=tdf,\n",
    "                        target_columns=[':']\n",
    "                       )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7eeb1e86-c00a-48d5-8e3b-363118c847ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "obj.result.head(21)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a91e3850-12c5-4b74-b17d-852092e81925",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Exploratory Data Analysis</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "befaaeb8-fab9-43f6-8a0a-efdb0e486377",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Exploratory Data Analysis (EDA) is a process where we visually and statistically examine, analyze, and summarize data to comprehend its characteristics, patterns, and relationships. This approach is crucial for gaining insights and a deeper understanding of the dataset at hand.<br>First let us analyse the Gender and Churn distributions in our data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e342ab59-0673-4b4d-b017-b14b278a5277",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1=tdf.select(['Gender','CustomerID']).groupby('Gender').count()\n",
    "d1 = d1.assign(drop_columns=True,\n",
    "          Gender=d1.Gender,\n",
    "          Count=d1.count_CustomerID)\n",
    "d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75cadab6-8ad8-46bb-914c-24985f0a64a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "d2=tdf.select(['Churn','CustomerID']).groupby('Churn').count()\n",
    "d2 = d2.assign(drop_columns=True,\n",
    "          Churn=d2.Churn,\n",
    "          Count=d2.count_CustomerID)\n",
    "d2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25a968ef-4b70-4b6b-a31d-f185be1f2609",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "We can see that the aggregated data is available to us in teradataml dataframe. Let's visualize this data to better understand the Churn and gender distributions. Clearscape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI or many python modules available like plotly, seaborn etc. We can do all the calculations and pre-processing on Vantage and pass only the necessary information to visulazation tools, this will not only make the calculation faster but also reduce the overall time due to less data movement between tools.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d38b54fe-bbb5-45dd-b685-c82be89211f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1=d1.to_pandas().reset_index()\n",
    "d2=d2.to_pandas().reset_index()\n",
    "#Gender and Churn percentage distribution\n",
    "# Create subplots: use 'domain' type for Pie subplot\n",
    "fig = make_subplots(rows=1, cols=2, specs=[[{'type':'domain'}, {'type':'domain'}]])\n",
    "fig.add_trace(go.Pie(labels=d1['Gender'], values=d1['Count'], name=\"Gender\"),\n",
    "              1, 1)\n",
    "fig.add_trace(go.Pie(labels=d2['Churn'], values=d2['Count'], name=\"Churn\"),\n",
    "              1, 2)\n",
    "\n",
    "# Use `hole` to create a donut-like pie chart\n",
    "fig.update_traces(hole=.4, hoverinfo=\"label+percent+name\", textfont_size=16)\n",
    "\n",
    "fig.update_layout(\n",
    "    title_text=\"Gender and Churn Distributions\",\n",
    "    # Add annotations in the center of the donut pies.\n",
    "    annotations=[dict(text='Gender', x=0.16, y=0.5, font_size=20, showarrow=False),\n",
    "                 dict(text='Churn', x=0.84, y=0.5, font_size=20, showarrow=False)])\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d394641-ba1c-44de-9db3-be2286aa3d13",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above plot we can see that 26.6 % of customers switched to another firm.<br>And of total customers 49.5 % are female and 50.5 % are male.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58fc0a42-d5d8-404c-bafd-49f40457bd2a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, let us see the chrun with respect to gender.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56d127df-8eea-4d51-9f68-7179a3969884",
   "metadata": {},
   "outputs": [],
   "source": [
    "d3=tdf.select(['Churn','Gender','CustomerID']).groupby(['Churn','Gender']).count()\n",
    "d3 = d3.assign(drop_columns=True,\n",
    "          Churn=d3.Churn,\n",
    "          Gender=d3.Gender,     \n",
    "          Count=d3.count_CustomerID)\n",
    "d3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8075c4b0-f668-4924-bf32-ef8c3a375c92",
   "metadata": {},
   "outputs": [],
   "source": [
    "d3=d3.to_pandas().reset_index()\n",
    "fig2=px.sunburst(d3,path=['Churn','Gender'],values='Count')\n",
    "fig2.update_layout(\n",
    "    title_text=\"Churn Distribution w.r.t Gender\")\n",
    "fig2.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eedfa546-3b86-4aa8-a4b9-1c47f922c5db",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that there is negligible difference in customer count who changed the service provider. Both genders behaved in similar fashion when it comes to migrating to another service provider.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43b27db1-3a04-440b-a382-da72669babb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "d4=tdf.select(['Churn','Contract','CustomerID']).groupby(['Churn','Contract']).count()\n",
    "d4 = d4.assign(drop_columns=True,\n",
    "          Churn=d4.Churn,\n",
    "          Contract=d4.Contract,     \n",
    "          Count=d4.count_CustomerID)\n",
    "d4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0d4bd6b-f9fa-4930-ba88-379b1308c795",
   "metadata": {},
   "outputs": [],
   "source": [
    "d4=d4.to_pandas().reset_index()\n",
    "fig4 = px.bar(d4,x=\"Churn\",y=\"Count\", color=\"Contract\", barmode=\"group\", title=\"<b>Customer contract distribution<b>\")\n",
    "fig4.update_layout(width=700, height=500, bargap=0.1)\n",
    "fig4.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb97cf05-b143-43e9-bf3a-b7ed267c1ad7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> We can see that about 75% of customer with Month-to-Month Contract opted to move out as compared to 13% of customers with One Year Contract and 3% with Two Year Contract.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57466f9f-ab11-4e32-ae9f-7c0fce65ed4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "d5=tdf.select(['PaymentMethod','CustomerID']).groupby('PaymentMethod').count()\n",
    "d5 = d5.assign(drop_columns=True,\n",
    "          PaymentMethod=d5.PaymentMethod,\n",
    "          Count=d5.count_CustomerID)\n",
    "d5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21d214d3-db22-4602-9fa2-9c694282d056",
   "metadata": {},
   "outputs": [],
   "source": [
    "d5=d5.to_pandas().reset_index()\n",
    "fig5 = go.Figure(data=[go.Pie(labels=d5['PaymentMethod'], values=d5['Count'], hole=.3)])\n",
    "fig5.update_layout(title_text=\"<b>Payment Method Distribution</b>\")\n",
    "fig5.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea725ada-aa8c-4dec-9648-1cdc26a17cd9",
   "metadata": {},
   "outputs": [],
   "source": [
    "d6=tdf.select(['Churn','PaymentMethod','CustomerID']).groupby(['Churn','PaymentMethod']).count()\n",
    "d6 = d6.assign(drop_columns=True,\n",
    "          Churn=d6.Churn,\n",
    "          PaymentMethod=d6.PaymentMethod,     \n",
    "          Count=d6.count_CustomerID)\n",
    "d6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a576fade-bb95-4c62-ab6f-94c06d0b5ddf",
   "metadata": {},
   "outputs": [],
   "source": [
    "d6=d6.to_pandas().reset_index()\n",
    "fig6 = px.bar(d6,x=\"Churn\",y=\"Count\", color=\"PaymentMethod\", barmode=\"stack\", title=\"<b>Customer Payment Method distribution w.r.t. Churn<b>\")\n",
    "fig6.update_layout(width=700, height=500, bargap=0.1)\n",
    "fig6.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fd84375-3d85-47cd-9076-6a6b8ea3e496",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Major customers who moved out were having Electronic Check as Payment Method.\n",
    "<br>Customers who opted for Credit-Card automatic transfer or Bank Automatic Transfer and Mailed Check as Payment Method were less likely to move out. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb375b22-f520-4bc9-a154-3ff05514d85a",
   "metadata": {},
   "outputs": [],
   "source": [
    "d7=tdf.select(['Churn','InternetService','Gender','CustomerID']).groupby(['Churn','InternetService','Gender']).count()\n",
    "d7 = d7.assign(drop_columns=True,\n",
    "          Churn=d7.Churn,\n",
    "          InternetService=d7.InternetService, \n",
    "          Gender=d7.Gender,\n",
    "          Count=d7.count_CustomerID)\n",
    "d7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c909c210-8342-44a1-be13-b648198251a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "d7.sort([\"InternetService\"]).head(21)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4945610-0f80-4bc2-8bfd-cc1e0901a999",
   "metadata": {},
   "outputs": [],
   "source": [
    "d7=d7.to_pandas().reset_index()\n",
    "fig7 = go.Figure()\n",
    "\n",
    "for t in d7['Churn'].unique():\n",
    "    dfp = d7[d7['Churn']==t]\n",
    "    fig7.add_traces(go.Bar(x=[dfp['InternetService'], dfp['Gender']],\n",
    "                          y=dfp['Count'],\n",
    "                          width=0.75,\n",
    "                          customdata=d7['Churn'],\n",
    "                          name='Churn :' +str(dfp['Churn'].values[0]) \n",
    "                         )\n",
    "                  )\n",
    "\n",
    "fig7.update_layout(barmode='stack',\n",
    "                  title_text=\"<b>Churn Distribution w.r.t. Internet Service and Gender</b>\")\n",
    "fig7.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bc9f214-7877-43ad-853a-c780e6e22dba",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> We can see that a lot of customers choose the Fiber optic service as compared to DSL but it's also evident that the customers who use Fiber optic have high churn rate, this might suggest a dissatisfaction with this type of internet service.\n",
    "<br> Customers having DSL service have less churn rate compared to Fiber optic service.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c63e5181-b134-4335-9c25-fd2e02b0a5f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "d8=tdf.select(['Churn','Dependents','CustomerID']).groupby(['Churn','Dependents']).count()\n",
    "d8 = d8.assign(drop_columns=True,\n",
    "          Churn=d8.Churn,\n",
    "          Dependents=d8.Dependents,\n",
    "          Count=d8.count_CustomerID)\n",
    "d8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8de41aa2-1e2c-43aa-ac1b-cb38daa44861",
   "metadata": {},
   "outputs": [],
   "source": [
    "d8=d8.to_pandas().reset_index()\n",
    "color_map = {\"Yes\": \"#FF97FF\", \"No\": \"#AB63FA\"}\n",
    "fig8 = px.bar(d8, x=\"Churn\",y=\"Count\", color=\"Dependents\", barmode=\"group\", title=\"<b>Dependents distribution</b>\", color_discrete_map=color_map)\n",
    "fig8.update_layout(width=700, height=500, bargap=0.1)\n",
    "fig8.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4af82750-0c91-41e9-9ec6-06f3e82eefc1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customers without dependents are more likely to churn.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4ca94e9-b31f-4fb5-824b-d21f58ef3055",
   "metadata": {},
   "outputs": [],
   "source": [
    "d9=tdf.select(['Churn','Partner','CustomerID']).groupby(['Churn','Partner']).count()\n",
    "d9 = d9.assign(drop_columns=True,\n",
    "          Churn=d9.Churn,\n",
    "          Partner=d9.Partner,\n",
    "          Count=d9.count_CustomerID)\n",
    "d9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e587308-8648-406b-9aca-f8906d8abe60",
   "metadata": {},
   "outputs": [],
   "source": [
    "d9=d9.to_pandas().reset_index()\n",
    "color_map = {\"Yes\": '#FFA15A', \"No\": '#00CC96'}\n",
    "fig9 = px.bar(d9, x=\"Churn\",y=\"Count\", color=\"Partner\", barmode=\"group\", title=\"<b>Chrun distribution w.r.t. Partners</b>\", color_discrete_map=color_map)\n",
    "fig9.update_layout(width=700, height=500, bargap=0.1)\n",
    "fig9.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "649567b1-3232-49bf-840a-8518b38c29b4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customers that don't have partners are more likely to churn.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5efe37b2-f14b-4499-abde-0f155cb8e3f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "d10=tdf.select(['Churn','PaperlessBilling','CustomerID']).groupby(['Churn','PaperlessBilling']).count()\n",
    "d10 = d10.assign(drop_columns=True,\n",
    "          Churn=d10.Churn,\n",
    "          PaperlessBilling=d10.PaperlessBilling,\n",
    "          Count=d10.count_CustomerID)\n",
    "d10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f24940fb-b15f-4460-803a-8c82ffbef623",
   "metadata": {},
   "outputs": [],
   "source": [
    "d10=d10.to_pandas().reset_index()\n",
    "color_map = {\"Yes\": '#FFA15A', \"No\": '#00CC96'}\n",
    "fig10 = px.bar(d10, x=\"Churn\",y=\"Count\", color=\"PaperlessBilling\",  title=\"<b>Chrun distribution w.r.t. Paperless Billing</b>\", color_discrete_map=color_map)\n",
    "fig10.update_layout(width=700, height=500, bargap=0.1)\n",
    "fig10.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd6697e7-1dcc-44b1-9428-290856a1cb0c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Customers with Paperless Billing are most likely to churn.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa5ea57f-b8ba-44eb-9d9b-cf07d37b77b5",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Data Preprocessing</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b58490d2-1f7a-4941-a62a-e99a4b7f7543",
   "metadata": {},
   "source": [
    " <p style = 'font-size:16px;font-family:Arial;color:#00233C'>Before the data can be used for model creation; we will need to do some data cleansing and transformation on it. We can do this InDb with Teradata Vantage's inbuilt functions.<br>We will use the <b>CategoricalSummary</b> function to showcase the distinct values and their corresponding counts for each specified column in the input DataFrame. This function provides a concise summary of categorical data, aiding in a quick understanding of the distribution of values within the specified columns.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ddf75f76-eb84-4f9d-856b-4051f0df6d70",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import CategoricalSummary\n",
    "CatSum = CategoricalSummary(data=tdf,target_columns=[\"MultipleLines\",\"InternetService\",\"OnlineSecurity\",\"OnlineBackup\",\"DeviceProtection\",\"TechSupport\",\"StreamingTV\",\"StreamingMovies\"])\n",
    "CatSum.result.sort(\"ColumnName\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84680551-fbca-45e4-83e9-c5f90fa3078b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "As we can see from the sample data above and the categorical summary values, the columns </p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li>OnlineSecurity </li>  \n",
    "<li>OnlineBackup</li>     \n",
    "<li>DeviceProtection</li> \n",
    "<li>TechSupport</li>      \n",
    "<li>StreamingTV</li>      \n",
    "<li>StreamingMovies</li>\n",
    "</ul><p style = 'font-size:16px;font-family:Arial;color:#00233C'>are related to InternetService, wherever InternetService value is \"No\" the column have value of \"No internet service\". For our model let us replace \"No internet service\" to No in our  column. We will do similar operation for replacing \"No phone service\" to \"No\".<br>We will use sqlalchemy's oreplace function to replace the respective strings to desired value.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f0f4876-7f8d-4177-8afc-f7c474b6ac4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import func\n",
    "\n",
    "\n",
    "tdf = tdf.assign(oreplace_MultipleLines=func.oreplace(tdf.MultipleLines.expression, \"No phone service\",\"No\"),\n",
    "                oreplace_OnlineSecurity=func.oreplace(tdf.OnlineSecurity.expression, \"No internet service\",\"No\"),\n",
    "                oreplace_OnlineBackup=func.oreplace(tdf.OnlineBackup.expression, \"No internet service\",\"No\"),\n",
    "                oreplace_DeviceProtection=func.oreplace(tdf.DeviceProtection.expression, \"No internet service\",\"No\"),                     oreplace_TechSupport=func.oreplace(tdf.TechSupport.expression, \"No internet service\",\"No\"),\n",
    "                oreplace_StreamingTV=func.oreplace(tdf.StreamingTV.expression, \"No internet service\",\"No\"),\n",
    "                oreplace_StreamingMovies=func.oreplace(tdf.StreamingMovies.expression, \"No internet service\",\"No\"))\n",
    "tdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5439705-8a1a-4060-b344-2f3245b18e57",
   "metadata": {},
   "outputs": [],
   "source": [
    "# now lets drop the extra columns, rename the columns in dataframe\n",
    "from teradataml.dataframe.sql_functions import case\n",
    "\n",
    "tdf2 = tdf.assign(drop_columns=True\n",
    "                ,CustomerID=tdf.CustomerID  \n",
    "                ,Gender=tdf.Gender \n",
    "                ,SeniorCitizen=tdf.SeniorCitizen\n",
    "                ,Partner=tdf.Partner\n",
    "                ,Dependents=tdf.Dependents\n",
    "                ,Tenure=tdf.Tenure\n",
    "                ,PhoneService=tdf.PhoneService      \n",
    "                ,MultipleLines=tdf.oreplace_MultipleLines     \n",
    "                ,InternetService=tdf.InternetService     \n",
    "                ,OnlineSecurity=tdf.oreplace_OnlineSecurity      \n",
    "                ,OnlineBackup=tdf.oreplace_OnlineBackup        \n",
    "                ,DeviceProtection=tdf.oreplace_DeviceProtection    \n",
    "                ,TechSupport=tdf.oreplace_TechSupport         \n",
    "                ,StreamingTV=tdf.oreplace_StreamingTV         \n",
    "                ,StreamingMovies=tdf.oreplace_StreamingMovies     \n",
    "                ,Contract=tdf.Contract            \n",
    "                ,PaperlessBilling=tdf.PaperlessBilling    \n",
    "                ,PaymentMethod=tdf.PaymentMethod       \n",
    "                ,MonthlyCharges=tdf.MonthlyCharges      \n",
    "                ,TotalCharges=tdf.TotalCharges        \n",
    "                ,Churn = case({ \"Yes\" : 1, \"No\" : 0},value=tdf.Churn,else_=0) ) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5bddfa5-6a90-477b-8c38-aa2985736fe6",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d256d17f-1c10-4bc2-978c-4b9a2a184437",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;color:#00233C'><b>Onehotencoding & Ordinal encoding</b></p> \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From our categorical attributes we can see that there are limited distinct values in each of these columns. We will use Teradata's <b>OneHotEncodingFit and Transform </b>and <b>OrdinalEncodingFit and Transform</b> functions to convert the categorical attributes to numerical.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc112884-d0bf-462d-9024-003bb4c2640a",
   "metadata": {},
   "outputs": [],
   "source": [
    "onehotfit_df = OneHotEncodingFit(data=tdf2,\n",
    "                                 is_input_dense=True,\n",
    "                                 approach=\"auto\",\n",
    "                                 target_column=[\"Gender\",\"Partner\",\"Dependents\",\"PhoneService\",\"MultipleLines\",\"OnlineSecurity\"\n",
    "                                                ,\"OnlineBackup\",\"DeviceProtection\",\"TechSupport\",\"StreamingTV\",\"StreamingMovies\",\n",
    "                                                \"PaperlessBilling\"],\n",
    "                                 category_counts=[2,2,2,2,2,2,2,2,2,2,2,2])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c136b69-41c1-42f1-bf47-89d1dae800a3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "The other categorical columns  </p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>InternetService </li>  \n",
    "<li>Contract</li>     \n",
    "<li>PaperlessBilling</li> \n",
    "<li>PaymentMethod</li>      \n",
    "</ul><p style = 'font-size:16px;font-family:Arial;color:#00233C'>have more values where we can apply ordinalencoding on it   </p>      \n",
    "      \n",
    "         "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e6b2946-b138-4ad6-aaa7-dff619fcf1e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "ordinalfit_df = OrdinalEncodingFit(target_column=['InternetService','Contract','PaperlessBilling','PaymentMethod'],\n",
    "                                   default_value=-1,\n",
    "                                   data=tdf2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ffe66188-9511-485c-84e5-2638f758f4c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "ordinalfit_df.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a437c68-b650-4300-a3e8-cd39a5e21054",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;color:#00233C'><b>Scale the numerical values</b><p style = 'font-size:16px;font-family:Arial;color:#00233C'>For the numercial attributes we will use <b>ScaleFit and ScaleTransform </b>function to scale the specified input table columns i.e perform the specific scale methods like standard deviation, mean etc to the input columns.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "059dd73a-3751-46a8-85bf-7a11a7eacb7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "scalefit_df = ScaleFit(data=tdf2,\n",
    "                       target_columns=['MonthlyCharges','TotalCharges'],\n",
    "                       scale_method=\"RANGE\",\n",
    "                       miss_value=\"KEEP\",\n",
    "                       global_scale=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "faec04de-8197-4c10-bcc5-3bc3f605e81e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;color:#00233C'><b>Putting it altogether</b><p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use <b> ColumnTransformer</b> function to apply all the transformations from the fit tables created below in one go.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d108b118-0970-4c84-9371-df4ef125ce69",
   "metadata": {},
   "outputs": [],
   "source": [
    "ColumnTransformer_out = ColumnTransformer(fillrowid_column_name=\"output_value\",\n",
    "                                              input_data=tdf2,\n",
    "                                              onehotencoding_fit_data=onehotfit_df.result,\n",
    "                                              ordinalencoding_fit_data=ordinalfit_df.result,\n",
    "                                              scale_fit_data=scalefit_df.output)\n",
    "                                              "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4da18dae-fec0-4bba-b87c-ab7045fd21e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "Transformed_data= ColumnTransformer_out.result.assign(drop_columns=True,\n",
    "                   Churn=ColumnTransformer_out.result.Churn,\n",
    "                   CustomerID=ColumnTransformer_out.result.CustomerID,\n",
    "                   SeniorCitizen=ColumnTransformer_out.result.SeniorCitizen,\n",
    "                   Tenure=ColumnTransformer_out.result.Tenure,\n",
    "                   InternetService=ColumnTransformer_out.result.InternetService,\n",
    "                   Contract=ColumnTransformer_out.result.Contract,\n",
    "                   PaperlessBilling=ColumnTransformer_out.result.PaperlessBilling,\n",
    "                   PaymentMethod=ColumnTransformer_out.result.PaymentMethod,\n",
    "                   MonthlyCharges=ColumnTransformer_out.result.MonthlyCharges,\n",
    "                   TotalCharges=ColumnTransformer_out.result.TotalCharges,\n",
    "                   Gender_0=ColumnTransformer_out.result.Gender_0,\n",
    "                   Gender_1=ColumnTransformer_out.result.Gender_1,\n",
    "                   Partner_0=ColumnTransformer_out.result.Partner_0,\n",
    "                   Partner_1=ColumnTransformer_out.result.Partner_1,\n",
    "                   Dependents_0=ColumnTransformer_out.result.Dependents_0,\n",
    "                   Dependents_1=ColumnTransformer_out.result.Dependents_1,\n",
    "                   PhoneService_0=ColumnTransformer_out.result.PhoneService_0,\n",
    "                   PhoneService_1=ColumnTransformer_out.result.PhoneService_1,\n",
    "                   MultipleLines_0=ColumnTransformer_out.result.MultipleLines_0,\n",
    "                   MultipleLines_1=ColumnTransformer_out.result.MultipleLines_1,\n",
    "                   OnlineSecurity_0=ColumnTransformer_out.result.OnlineSecurity_0,\n",
    "                   OnlineSecurity_1=ColumnTransformer_out.result.OnlineSecurity_1,\n",
    "                   OnlineBackup_0=ColumnTransformer_out.result.OnlineBackup_0,\n",
    "                   OnlineBackup_1=ColumnTransformer_out.result.OnlineBackup_1,\n",
    "                   DeviceProtection_0=ColumnTransformer_out.result.DeviceProtection_0,\n",
    "                   DeviceProtection_1=ColumnTransformer_out.result.DeviceProtection_1,\n",
    "                   TechSupport_0=ColumnTransformer_out.result.TechSupport_0,\n",
    "                   TechSupport_1=ColumnTransformer_out.result.TechSupport_1,\n",
    "                   StreamingTV_0=ColumnTransformer_out.result.StreamingTV_0,\n",
    "                   StreamingTV_1=ColumnTransformer_out.result.StreamingTV_1,\n",
    "                   StreamingMovies_0=ColumnTransformer_out.result.StreamingMovies_0,\n",
    "                   StreamingMovies_1=ColumnTransformer_out.result.StreamingMovies_1,\n",
    "                   PaperlessBilling_0=ColumnTransformer_out.result.PaperlessBilling_0,\n",
    "                   PaperlessBilling_1=ColumnTransformer_out.result.PaperlessBilling_1)\n",
    "                                                      \n",
    "                                                      "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e4c30b0-4989-4540-bf60-3e4631afeacd",
   "metadata": {},
   "outputs": [],
   "source": [
    "Transformed_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f38c7f0f-6da8-4ef9-acaf-0774d29c92e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "Transformed_data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0edef2c8-e568-4626-9377-e189d66e3350",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see from above how our data is transformed from the original values.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05c50e66-97a9-4fc9-8bd1-20654dc318fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Copying the intermediate table to database\n",
    "Transformed_data.to_sql(\"Transformed_data\",primary_index = \"CustomerID\", if_exists = \"replace\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37551d5e-2366-42cf-83f9-1a48ee438c6c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;color:#00233C'><b>Create train and test data</b><p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now we have transformed our data and it is fit to be used in machine learning models, let us split the whole dataset into train and test sets for model training and scoring. We will use <b>TrainTestSplit</b> function for this task.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b22bf0d0-255e-4ea9-8bdc-ffac9ea02f34",
   "metadata": {},
   "outputs": [],
   "source": [
    "TrainTestSplit_out = TrainTestSplit(\n",
    "                                    data = DataFrame('Transformed_data'),\n",
    "                                    id_column = \"CustomerID\",\n",
    "                                    train_size = 0.75,\n",
    "                                    test_size = 0.25,\n",
    "                                    seed = 21\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19d382b8-7f46-43f8-aee9-5598d6f24ebf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split into 2 virtual dataframes\n",
    "df_train = TrainTestSplit_out.result[TrainTestSplit_out.result['TD_IsTrainRow'] == 1].drop(['TD_IsTrainRow'], axis = 1)\n",
    "df_test = TrainTestSplit_out.result[TrainTestSplit_out.result['TD_IsTrainRow'] == 0].drop(['TD_IsTrainRow'], axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88e87734-1eb6-47a6-87d4-3b2d0585088a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have done our preprocessing of data and we created our training and test datasets, let's now create some predictive models."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3624ff0f-2e80-450e-a76e-85398a8c73da",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. InDb Model Training and Scoring</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2893fa15-2812-473d-b91c-5949ba436461",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.1 Logistic Regression</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4561645-5edd-4e2b-9983-59e77ab4745a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For our model we will use logistic regression.<br>\n",
    "  <b>Logistic regression</b> is a statistical algorithm used for binary classification problems. It is a type of supervised learning algorithm that predicts the probability of an input belonging to a certain class (e.g., positive or negative) based on its features.<br>Logistic regression works by modeling the relationship between the input features and the probability of belonging to a certain class using a logistic function. The logistic function takes the input feature values and maps them onto a probability scale between 0 and 1, which represents the probability of belonging to the positive class.<br>\n",
    "    The <b>GLM </b>function is a generalized linear model (GLM) that performs regression and classification analysis on data sets.\n",
    "<br>Please refer <a href ='https://docs.teradata.com/r/Enterprise/Teradata-Package-for-Python-Function-Reference-17.20/teradataml-Analytic-Database-SQL-Engine-Analytic-Functions/Supported-on-Database-Version-17.20.xx/MODEL-TRAINING-functions/GLM'>GLM</a> for function elements and output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc6639e3-2427-42d2-b302-08e18196b2b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e92723cc-1aec-4fee-97fe-96b3e86e7802",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import GLM, TDGLMPredict\n",
    "\n",
    "glm_model = GLM(data = df_train,\n",
    "                input_columns = ['1:8','10:33'], \n",
    "                response_column = 'Churn',\n",
    "                family = 'Binomial')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e3b4239-40a5-4b2c-9589-7d974c574641",
   "metadata": {},
   "outputs": [],
   "source": [
    "glm_model.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a24bb51b-7115-486e-957a-848bad5bc4d9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have created our model, let's do the predictions on the test dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ebd11f4-bb9a-4b71-a6ca-7fe9f6fe9755",
   "metadata": {},
   "outputs": [],
   "source": [
    "glm_prediction = TDGLMPredict(newdata = df_test,\n",
    "                           id_column = 'CustomerID',\n",
    "                           object = glm_model.result,\n",
    "                           accumulate = 'Churn',\n",
    "                           output_prob=True,\n",
    "                           output_responses = ['0', '1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "498799ce-2607-4cba-94e6-26a40abd7d0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "out_glm = glm_prediction.result.assign(prediction = glm_prediction.result.prediction.cast(type_ = BYTEINT))\n",
    "out_glm = out_glm.assign(prediction = out_glm.prediction.cast(type_ = VARCHAR(2)))\n",
    "out_glm = out_glm.assign(Churn = out_glm.Churn.cast(type_ = VARCHAR(2)))\n",
    "out_glm"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "121887bb-4fb2-429f-99d7-120c6dd47e51",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output above shows prob_1, i.e. customer will Churn and prob_0, i.e. customer will not Churn. The prediction column uses these probabilities to give a class label, i.e. prediction column.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fa0a4c0-c4f8-499b-9e60-8bc87684a5c8",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.2 Evaluation of Logistic Regression Model</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the <b>ClassificationEvaluator</b> function to evaluate the trained glm model on test data. This will let us know how well our model has performed on unseen data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "683015ad-d54b-4a33-a71f-345a348ee912",
   "metadata": {},
   "outputs": [],
   "source": [
    "ClassificationEvaluator_glm = ClassificationEvaluator(\n",
    "                                                        data = out_glm,\n",
    "                                                        observation_column = 'Churn',\n",
    "                                                        prediction_column = 'prediction',\n",
    "                                                        labels = ['0', '1']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "679b7a22-e08e-414a-aec3-e8ae6b1e0701",
   "metadata": {},
   "outputs": [],
   "source": [
    "ClassificationEvaluator_glm.output_data.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d281b3fa-0793-4e86-986b-26bda09833ec",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output shows recall, and F1-score values of confusion matrix.</p>\n",
    "<table style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "  <tr>\n",
    "    <th>Column</th>\n",
    "    <th>Description</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Precision</td>\n",
    "    <td>The positive predictive value. Refers to the fraction of relevant instances among\n",
    "the total retrieved instances.\n",
    "        Precision answers the following question: what proportion of predicted Positives is truly Positive? \n",
    "        Precision = (TP)/(TP+FP)</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Recall</td>\n",
    "    <td>Refers to the fraction of relevant instances retrieved over the total amount of\n",
    "relevant instances. Recall answers a different question: what proportion of actual Positives is correctly classified?\n",
    "Recall = (TP)/(TP+FN)</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>F1</td>\n",
    "    <td>F1 score, defined as the harmonic mean of the precision and recall and is a number between 0 and 1. F1 score maintains a balance between the precision and recall for your classifier.                                         \n",
    "                      F1 = 2*(precision*recall/precision+recall)</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Support</td>\n",
    "    <td>The number of times a label displays in the Observation Column.</td>\n",
    "  </tr>\n",
    "</table>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>**TP:- True Positive , FP :- False Positive, TN :- True Negative , FN :- False Negative</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6aaf3776-22c7-4697-a7f8-e5e334067b18",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can also calculate mean absolute error and AUC(Area Under the Curve) for Receiver Operating Characteristic Curve.<br>Mean Absolute Error is the summation of the difference between actual and predicted values averaged over the number of observations.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "937f54ea-2776-472d-896f-3a707d645be0",
   "metadata": {},
   "outputs": [],
   "source": [
    "glm_pred = glm_prediction.result.to_pandas()\n",
    "print(mean_absolute_error(glm_pred['Churn'], glm_pred['prob_1']))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35733201-e225-4ba5-b712-105992bf177e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ROC curve is a graph between TPR(True Positive Rate) and FPR(False Positive Rate). The area under the ROC curve is a metric of how well the model can distinguish between positive and negative classes. The higher the AUC, the better the model's performance in distinguishing between the positive and negative classes.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5962c501-8e8e-4594-ab63-d6e0df2e07a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "AUC = roc_auc_score(glm_pred['Churn'], glm_pred['prob_1'])\n",
    "AUC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "483506fb-d59d-4df0-a2fe-9d1e86ffdf72",
   "metadata": {},
   "outputs": [],
   "source": [
    "fpr, tpr, thresholds = roc_curve(glm_pred['Churn'], glm_pred['prob_1'])\n",
    "plt.plot(fpr, tpr, color='orange', label='ROC. AUC = {}'.format(str(AUC)))\n",
    "plt.plot([0, 1], [0, 1], color='darkblue', linestyle='--')\n",
    "plt.xlabel('False Positive Rate')\n",
    "plt.ylabel('True Positive Rate')\n",
    "plt.title('Receiver Operating Characteristic (ROC) Curve')\n",
    "plt.legend()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae6647ac-3dd6-406a-8d73-fb793b54d28f",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.3 XGB</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <b>XGBoost (eXtreme Gradient Boosting) </b> is based on the gradient boosting framework, which is an ensemble learning method that combines multiple weak or base models (typically decision trees) to create a more accurate and robust predictive model. XGBoost improves upon traditional gradient boosting by using a number of optimization techniques, including parallelization, regularization, and efficient handling of missing values, to achieve faster training times and better model performance.<br>\n",
    "    Teradata's <b>XGBoost </b>function is an implementation of the gradient boosted decision tree designed for speed and performance. In gradient boosting, each iteration fits a model to the residuals (errors) of the previous iteration to correct the errors made by existing models. The predicted residual is multiplied by this learning rate and then added to the previous prediction. Models are added sequentially until no further improvements can be made. It is called gradient boosting because it uses a gradient descent algorithm to minimize the loss when adding new models.\n",
    "<br>Please refer <a href ='https://docs.teradata.com/search/all?query=XGBoost&filters=prodname~%2522Teradata+Package+for+Python%2522&content-lang=en-US'>XGBoost</a> for function elements and output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aff1ebc7-ecb6-44e7-8c8d-b1da9505f9bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "XGBoost_model = XGBoost(\n",
    "                            data = df_train,\n",
    "                            input_columns = ['1:8','10:33'],\n",
    "                            response_column = 'Churn',\n",
    "                            model_type = 'CLASSIFICATION',\n",
    "                            \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "062f024d-5e3d-4a59-93d3-763812d70053",
   "metadata": {},
   "outputs": [],
   "source": [
    "XGBoostPredict_out = XGBoostPredict(\n",
    "                                        newdata = df_test,\n",
    "                                        object = XGBoost_model.result,\n",
    "                                        id_column = 'CustomerID',\n",
    "                                        accumulate = 'Churn',\n",
    "                                        model_type = 'CLASSIFICATION',\n",
    "                                        object_order_column = ['task_index', 'tree_num', 'iter', 'class_num', 'tree_order'],\n",
    "                                        output_responses = ['0', '1'],\n",
    "                                        output_prob = True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c70378d-9b30-41e9-bfc3-ae901884f9c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "out_xgb = XGBoostPredict_out.result.assign(Prediction = XGBoostPredict_out.result.Prediction.cast(type_ = BYTEINT))\n",
    "out_xgb = out_xgb.assign(Prediction = out_xgb.Prediction.cast(type_ = VARCHAR(2)))\n",
    "out_xgb = out_xgb.assign(Churn = out_xgb.Churn.cast(type_ = VARCHAR(2)))\n",
    "out_xgb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48a42d5b-5fba-4036-b094-19858e2c560f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have created our model, let's do the predictions on the test dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bd173a4-10ef-4528-a6e2-b67d5f4df07e",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>4.4 Evaluation of XGB Model</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23fd8827-dc00-4604-9c9e-4ef7352cf834",
   "metadata": {},
   "outputs": [],
   "source": [
    "ClassificationEvaluator_xgb = ClassificationEvaluator(\n",
    "                                                        data = out_xgb,\n",
    "                                                        observation_column = 'Churn',\n",
    "                                                        prediction_column = 'Prediction',\n",
    "                                                        labels = ['0', '1']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2aefe83b-aedc-4173-9396-68572258fcd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "ClassificationEvaluator_xgb.output_data.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e7466ae-b42c-4005-a094-729bb1230b33",
   "metadata": {},
   "outputs": [],
   "source": [
    "xgb_pred = XGBoostPredict_out.result.to_pandas().reset_index().sort_values(\"CustomerID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26c69f90-824e-4f84-a5d1-46e771a0ee00",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(mean_absolute_error(xgb_pred['Churn'], xgb_pred['Prob_1']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c71ab9ce-e83c-4f1b-82fb-550119a98704",
   "metadata": {},
   "outputs": [],
   "source": [
    "AUC = roc_auc_score(xgb_pred['Churn'], xgb_pred['Prob_1'])\n",
    "AUC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78aa9c8e-e5e0-4a89-af63-27e1e638023f",
   "metadata": {},
   "outputs": [],
   "source": [
    "fpr, tpr, thresholds = roc_curve(xgb_pred['Churn'], xgb_pred['Prob_1'])\n",
    "plt.plot(fpr, tpr, color='orange', label='ROC. AUC = {}'.format(str(AUC)))\n",
    "plt.plot([0, 1], [0, 1], color='darkblue', linestyle='--')\n",
    "plt.xlabel('False Positive Rate')\n",
    "plt.ylabel('True Positive Rate')\n",
    "plt.title('Receiver Operating Characteristic (ROC) Curve')\n",
    "plt.legend()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e91ddacb-4ca3-405c-93c8-c587788191c4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Conclusion</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "516e0588-5e98-4373-8f30-8e8f40898835",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo we have seen how we can do analysis and pre-processing of the data in Vantage using InDb functions. We have also used created two commonly used predictive models for classification and predicted the customers that are likely to churn. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35ebb886-8da9-479a-8995-c6dd7ccebffd",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Cleanup</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0b01f3e-03fa-4a14-b388-02eeb210b8c1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;'>\n",
    "We need to clean up our work tables to prevent errors next time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "914cfbcf-f229-496c-be13-b63c62729291",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['Transformed_data']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name = table)\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98028152-2401-429a-9141-0488eaecb0f5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C;color:#00233C'><b>Databases and Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the following code to clean up tables and databases created for this demonstration.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7aae8012-ca09-4541-9389-1bb82f283ff1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_Telco');\" \n",
    "#Takes 10 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ad94d1e-d82d-4611-b5c7-4180397f6c94",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10724002-0091-4ef1-b091-71c0a2fdda5a",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3eee6695-147e-4b5b-a0e0-ae6a1d9629db",
   "metadata": {},
   "source": [
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Required Materials</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let’s look at the elements we have available for reference for this notebook:</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90d6c2a3-92e9-4121-a46a-2beaba63cac2",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Filters:</b></p>\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Industry:</b> Telco</li>\n",
    "    <li><b>Functionality:</b> Machine Learning</li>\n",
    "    <li><b>Use Case:</b> Customer Retention</li>\n",
    "    </ul>\n",
    "    <p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Related Resources:</b></p>\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><a href = 'https://www.teradata.com/Blogs/NPS-is-a-metric-not-the-goal'>·In the fight to improve customer experience, NPS is a metric, not the goal</a></li>\n",
    "    <li><a href = 'https://www.teradata.com/Blogs/Hyper-scale-time-series-forecasting-done-right'>·Hyper-scale time series forecasting done right</a></li>\n",
    "    <li><a href = 'https://www.teradata.com/Resources/Datasheets/Digital-Identity-Management-and-Great-CX?utm_campaign=i_coremedia-AMS&utm_source=google&utm_medium=paidsearch&utm_content=GS_CoreMedia_NA-US_BKW&utm_creative=Brand-Vantage&utm_term=teradata%20analytic%20platform&gclid=Cj0KCQjwnMWkBhDLARIsAHBOftrWZxDktHkKMsaWjMmNRnQ6Ys-bZBAUhXjWTo1Xa02fsci-IHWBV_waAppkEALw_wcB'>·Close the Gap Between Digital Identity Management and Great Customer Experiences</a></li>\n",
    "        </ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9094fe39-0a98-43d6-a62b-7c2d36acf654",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Reference Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'> \n",
    "       <li>Teradata Vantage™ - Analytics Database Analytic Functions - 17.20: <a href = 'https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Introduction-to-Analytics-Database-Analytic-Functions '>https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-VantageTM-Analytics-Database-Analytic-Functions-17.20/Introduction-to-Analytics-Database-Analytic-Functions </a></li>    \n",
    "  <li>Teradata® Package for Python User Guide - 17.20: <a href = 'https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-Package-for-Python-User-Guide-17.20/Introduction-to-Teradata-Package-for-Python'>https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-Package-for-Python-User-Guide-17.20/Introduction-to-Teradata-Package-for-Python</a></li>\n",
    "  <li>Teradata® Package for Python Function Reference - 17.20: <a href = 'https://docs.teradata.com/r/Enterprise/Teradata-Package-for-Python-Function-Reference-17.20/Teradata-Package-for-Python-Function-Reference'>https://docs.teradata.com/r/Enterprise/Teradata-Package-for-Python-Function-Reference-17.20/Teradata-Package-for-Python-Function-Reference</a></li>      \n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8f9f644",
   "metadata": {},
   "source": [
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Dataset:</b>\n",
    "\n",
    "- `CustomerID`: unique id of customer\n",
    "- `Gender`: Whether the customer is a male or a female\n",
    "- `SeniorCitizen`:Whether the customer is a senior citizen or not (1, 0)\n",
    "- `Partner`:Whether the customer has a partner or not (Yes, No)\n",
    "- `Dependents`:Whether the customer has dependents or not (Yes, No)\n",
    "- `Tenure`:Number of months the customer has stayed with the company\n",
    "- `PhoneService`:Whether the customer has a phone service or not (Yes, No)\n",
    "- `MultipleLines`:Whether the customer has multiple lines or not (Yes, No, No phone service)\n",
    "- `InternetService`:Customer’s internet service provider (DSL, Fiber optic, No)\n",
    "- `OnlineSecurity`:Whether the customer has online security or not (Yes, No, No internet service)\n",
    "- `OnlineBackup`:Whether the customer has online backup or not (Yes, No, No internet service)\n",
    "- `DeviceProtection`:Whether the customer has device protection or not (Yes, No, No internet service)\n",
    "- `TechSupport`:Whether the customer has tech support or not (Yes, No, No internet service)\n",
    "- `StreamingTV`:Whether the customer has streaming TV or not (Yes, No, No internet service)\n",
    "- `StreamingMovies`:Whether the customer has streaming movies or not (Yes, No, No internet service)\n",
    "- `Contract`:The contract term of the customer (Month-to-month, One year, Two year)\n",
    "- `PaperlessBilling`:Whether the customer has paperless billing or not (Yes, No)\n",
    "- `PaymentMethod`:The customer’s payment method (Electronic check, Mailed check, Bank transfer (automatic), Credit card (automatic))\n",
    "- `MonthlyCharges`:The amount charged to the customer monthly\n",
    "- `TotalCharges`:The total amount charged to the customer\n",
    "- `Churn`:Whether the customer churned or not (Yes or No)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c30802a4-8141-47f6-971d-0bb79be6f5bf",
   "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"
  },
  "toc-autonumbering": true
 },
 "nbformat": 4,
 "nbformat_minor": 5
}