UseCases/Car_Complaints/Car_Complaints_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bc455d5d-9b02-4d4c-b5df-93a471670b24",
   "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",
    "       A Study of Car Complaints Data using Geospatial Analysis and Outlier Detection\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": "99847ea9-44e6-4321-bed2-5f5b8d5824aa",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Customer complaints are often tricky to handle, with multiple data sources not often used simultaneously (including text and geolocation of complaints, service centres, etc.) This demo highlights Vantage features that address this problem with assistance from graphics libraries in Python. <br>\n",
    "The demo seeks to provide the business user a fuller view of their customers where to focus highlighting cases for special attention. \n",
    "<br>Key benefits of this kind of analysis\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li>Better understanding of the current status through visual representation</li>\n",
    "<li>Easy implementation meant to scale as it leverages Vantage functionalities</li>\n",
    "<li>Unconventional usage of Vantage functions (geospatial for attribution, multi-level outlier detection)</li>   </ul>\n",
    "<br>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Data is sourced from a public database by the National Highway Traffic Safety Administration (NHTSA) of the USA with a few modifications to analyse data on a state-county level.\n",
    "  <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>1000 records of cars by Ford motors were randomly sampled</li>\n",
    "    <li>Each complaint was added a geolocation (latitude, longitude) to a location in Iowa (to simulate data coming from a single state)</li><li>Records were limited to 2019</li></ul>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will do two kind of analysis\n",
    "      <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Service Center Analysis</b> - A geospatial attribution of complaints to its nearby service centres and county-specific ranking to search for blind spots.</li>\n",
    "    <li><b>Defect Analysis</b> - Defect outlier detection to spot complaints with parts that defected earlier than expected.</li>\n",
    "    </ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6282261f-dddc-4a5f-b37a-c03e39006870",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd1e1ca1-d2c3-4965-a891-639257c9bd9a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Import python packages, connect to Vantage and explore the dataset</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b34dd27d-96dd-4e92-bb51-82fb4b369fbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import libraries\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",
    "warnings.simplefilter(action='ignore', category=UserWarning)\n",
    "import geopandas as gpd\n",
    "import matplotlib.pyplot as plt \n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import getpass\n",
    "\n",
    "from teradataml import *\n",
    "import plotly.express as px\n",
    "import json\n",
    "from pandas import json_normalize\n",
    "import numpy as np\n",
    "# import plotly.express as px\n",
    "from  ipywidgets import widgets, interact\n",
    "\n",
    "display.max_rows = 5 \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46fa9ebb-abcf-4eea-8862-b96a8291eec4",
   "metadata": {},
   "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 <b>use down arrow</b> to go to next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b53ae40-0682-47a1-b142-68c48a17235f",
   "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": "adf07f5d-8cf1-48b6-a1b0-cd4062b2b2cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Car_Complaints_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "665d0301-67aa-40ab-9870-931db7bb31b5",
   "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. 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.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9dbf4850-990a-4686-9ad8-0cad23eae28f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_Car_cloud');\"\n",
    " # takes about 30 seconds, estimated space: 0 MB\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_Car_local');\" \n",
    "# takes about 1min 40seconds, estimated space: 1.5 MB"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bcfb195-a849-4c4c-8c7f-5589c349829b",
   "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,
   "id": "07feb951-ea2f-418a-a0a0-b62aeee06ed8",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06f78d6f-3e70-431f-b153-7ae252f6873c",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea6799ae-dd34-4af4-ad26-c864592c5f53",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Initial Data Sets</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e091d9e-d476-4b49-8946-e7666df7dbfd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us take a look at the source tables we have.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93f572ce-aef7-45fb-9c50-b3ec6cf6f3a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = DataFrame(in_schema(\"DEMO_Car\", \"Complaint_Locations\"))\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c96f3999-cc26-4b44-926d-ca2729e3caad",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a18946c4-775b-4739-b11a-313bd417bdb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ece8a01-9bda-49a1-8415-e176ce04383d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From above we can see <i>Complaint_Locations</i> table has complaint id and the geo location from where the complaint was raised. It has 1000 records in it.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6881c21a-4796-44fa-bb88-142ace311381",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = DataFrame(in_schema(\"DEMO_Car\", \"Service_Centers\"))\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16ad0a9b-94fb-41f5-aeb0-c2a69946da55",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f70f1aa2-2cac-4a04-a29b-a2a59bd0e309",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4a9d052-1753-4f2e-8237-47a8f52f2fd1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From above we can see <i>Service_Centers</i> table has information of the service center. We have information on 138 service centers.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ccaa4cd-3485-44bb-baf5-b71970b6a259",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = DataFrame(in_schema(\"DEMO_Car\", \"Complaints\"))\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49936b60-890f-4615-9a47-c1a6dbabd0fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2dceef7-ee72-4c39-9c28-9d8227d38d19",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "830a6e02-d572-4593-9163-2c2a967a5632",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i>Complaint</i> table has the information of the complaint registered.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8dd7cdfe-3834-4d73-a461-ad185c1c286e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial> Additionaly we have taken the IOWA county data (https://geodata.iowa.gov/datasets/iowa::iowa-county-boundaries/) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "563199b0-ad40-4248-b40c-77dbc31e05a1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Additionally we have also taken the IOWA county boundary information from https://geodata.iowa.gov/datasets/iowa::iowa-county-boundaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6646fd22-a477-464c-9e0a-c07c4bf7f8e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df4 = DataFrame(in_schema(\"DEMO_Car\", \"Counties\"))\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27cef68f-31af-40b8-a8b5-64d559d31e87",
   "metadata": {},
   "outputs": [],
   "source": [
    "df4.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c0ad32c-45df-4fca-93e4-609dee687cfd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df4.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb7bfea1-479c-4e97-9c0a-4d5d5008cefc",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8038d26-04e3-4022-9ba5-3e8a094c2321",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Service Center Analysis\n",
    " </b></p>\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8392a7c1-2fba-4dee-8fda-ddd63f6295be",
   "metadata": {},
   "source": [
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'> \n",
    "        <li>Measure service center workload based on customer demand and location. For this we Calculated workload as an attribution score per service center using geo functions based on distances to customer locations.</li>\n",
    "<li>Identify problem areas or service blind spots based on population, customer demand, and distances between customers and centres. For this we calculated an aggregated performance score per area unit based on multiple metrics. </li> </ul>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Benefits from this kind of analysis is that clients can allocate resources to even out workload among service centers based on the attribution scores also this can help them to locate new store location.\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Distance-based Attribution\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> The typical approach to attribution is mainly as follows\n",
    "    <b>Attribution Function in Vantage</b>\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>     \n",
    "        <li>Row-wise time and transaction-based data</li>\n",
    "<li>Credit is distributed to events based on time<ul style = 'font-size:16px;font-family:Arial;color:#00233C'> \n",
    "        <li>FIRST_CLICK</li>\n",
    "        <li>LAST_CLICK</li>\n",
    "        <li>UNIFORM</li>\n",
    "    <li>EXPONENTIAL</li></ul>\n",
    "</li>\n",
    "        <br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In our case we have used distance based attribution where we distribute attribution to service centres within X km of a complaint. If none, give full credit to the nearest service center."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "add248f4-13f4-4d33-be76-46dab950a9f2",
   "metadata": {},
   "source": [
    "<img id=\"distance_attrib\" src=\"distance_attrib.PNG\" alt=\"Attribution score\" width=\"800\" />\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Distance based attribution.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a31eba7c-3cd4-436c-b46f-6645a5df2c1f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> We have used <b>ST_Geometry</b> data type in Vantage to load and used the geographic data. The coordinates (latitude and longitude) of the service centers and complaint locations are loaded as ST_Point; also the county information of the IOWA state is loaded as ST_POLYGON is loaded to get the country boundary.<br>We have used <b>ST_SphericalDistance</b> function to find the nearest service center to each complaint location and the respective distance between points and ST_Within in a WHERE clause to determine which county (ST_Polygon) a certain complaint (ST_Point) belongs to. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d587b82a-6cdc-4ccd-9f42-05a50acee32b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''\n",
    "REPLACE VIEW sc_attribution AS\n",
    " -- distances between service centers and complaints\n",
    "WITH distances as (\n",
    "    SELECT b.service_center_id as sc_id,\n",
    "        b.geometry as sc_geom,\n",
    "        a.cmplid as comp_id,\n",
    "        b.start_yearmonth,\n",
    "        b.end_yearmonth,\n",
    "        a.geometry.ST_SphericalDistance(b.geometry)/1000 as dist --distance in kilometers\n",
    "    FROM DEMO_CAR.complaint_locations a, DEMO_CAR.service_centers b),\n",
    "-- nearest service centers to each complaint\n",
    "nearest as (\n",
    "    SELECT sc_id,\n",
    "        sc_geom,\n",
    "        comp_id, \n",
    "        start_yearmonth,\n",
    "        end_yearmonth,\n",
    "        dist\n",
    "    FROM distances\n",
    "    QUALIFY ROW_NUMBER() OVER(PARTITION BY comp_id ORDER BY DIST) = 1)\n",
    "-- table of service centers and aggregated attribution scores \n",
    "\n",
    "    SELECT sc_id service_center_id,\n",
    "        'Station '||CAST(sc_id AS CHAR(3)) as service_center,\n",
    "        sc_geom.ST_Y() as lat,\n",
    "        sc_geom.ST_X() as \"long\",\n",
    "        start_yearmonth,\n",
    "        end_yearmonth,\n",
    "        SUM(attrib_score) as attribution_score, -- total attribution score is the sum of attribution scores across complaints \n",
    "        RANK() OVER (ORDER BY attribution_score DESC) as attribution_score_rank\n",
    "    FROM (\n",
    "        SELECT comp_id, \n",
    "            sc_id,\n",
    "            sc_geom,\n",
    "            start_yearmonth,\n",
    "            end_yearmonth,\n",
    "            1.000/ (COUNT(sc_id) OVER (PARTITION BY comp_id)) as attrib_score\n",
    "        FROM distances\n",
    "        WHERE dist < 15\n",
    "    \n",
    "        UNION ALL\n",
    "    \n",
    "        SELECT comp_id, \n",
    "            sc_id,\n",
    "            sc_geom,\n",
    "            start_yearmonth,\n",
    "            end_yearmonth,\n",
    "            1\n",
    "        FROM nearest\n",
    "        WHERE dist > 15\n",
    "    \n",
    "        UNION ALL\n",
    "    \n",
    "        SELECT comp_id, \n",
    "            sc_id,\n",
    "            sc_geom,\n",
    "            start_yearmonth,\n",
    "            end_yearmonth,\n",
    "            0\n",
    "        FROM distances\n",
    "        WHERE dist > 15\n",
    "        ) AS attrib_scores\n",
    "    GROUP BY 1,2,3,4,5,6\n",
    "; ''')    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5179efa-f9fe-45b7-b34b-43aeb3dd0d67",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's see how the data in this view looks like "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2005a859-87e9-4fc5-b9a2-76dcf001d358",
   "metadata": {},
   "outputs": [],
   "source": [
    "sc = DataFrame(\"sc_attribution\")\n",
    "sc"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa90c091-5b50-4ebe-8362-a5ae2e7d51fa",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now let us plot this data to see how the service center attribution changes over the years.<br><i>*Please click on the play button to start animation. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26bb6316-f8bb-4699-b7ae-3690e24b42fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = ''' \n",
    "select  service_center_id,service_center,lat,\"long\",start_yearmonth,attribution_score\n",
    "from sc_attribution group by 1,2,3,4,5,6 ;\n",
    "'''\n",
    "\n",
    "out1= DataFrame.from_query(qry)\n",
    "df=out1.to_pandas()\n",
    "fig1 = px.scatter_mapbox(df,lat=\"lat\", lon=\"long\", hover_name=\"service_center\",size=pd.to_numeric(df['attribution_score']),\n",
    "                         color=\"attribution_score\", size_max=70, zoom=6, \n",
    "                         animation_frame=\"start_yearmonth\",\n",
    "                         category_orders={\"start_yearmonth\": [201801,201802,202803,201804,201805,201806,201807,201808,\n",
    "                                                              201809,201810, 201811, 201812,201901, 201902, 201903,201904, \n",
    "                                                              201905, 201906, 201907,201908, 201909,201910, 201911, 201912]}, \n",
    "                         color_continuous_scale=px.colors.sequential.Bluered, \n",
    "                         height = 600\n",
    "                  )\n",
    "fig1.update_layout(mapbox_style=\"open-street-map\")\n",
    "fig1.update_layout(margin={\"r\":0,\"t\":0,\"l\":0,\"b\":0})\n",
    "fig1.update_layout(title_text = 'Service_center ranks over the years' ,title_y=1)\n",
    "fig1.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b70cdd2-718a-4ebb-924a-4b229c95adc0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the map above we can see the year wise attribution score based on the calculations we have done in the view earlier."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10d7ad02-5e40-48ba-962d-ce0a8541171c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Area Rankings \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Each area is ranked according by multiple metrics, and these rankings are combined to score the area.\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Population </li>\n",
    "    <li>No. Of Customers/ Complaints</li>\n",
    "    <li>No. Of Non-Covered Customers/ Complaints</li>\n",
    "    <li>Percent of Non-Covered Customers/ Complaints</li>\n",
    "    </ul>\n",
    "    </p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have created a view on the service center information and the IOWA county data to get the ranks    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d1d456d-02d2-491b-a42f-ea9eb1db128e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''\n",
    "REPLACE VIEW county_accessibility AS \n",
    "-- table of service centers nearest to each complaint\n",
    "    WITH nearest as (\n",
    "    SELECT a.cmplid,\n",
    "        a.geometry as cmpl_geom,\n",
    "        b.service_center_id as sc_id,\n",
    "        b.geometry as sc_geom,\n",
    "        a.geometry.ST_SphericalDistance(b.geometry)/1000 as dist\n",
    "    FROM demo_car.complaint_locations as a, demo_car.service_centers as b\n",
    "    QUALIFY ROW_NUMBER() OVER(PARTITION BY cmplid ORDER BY dist) = 1\n",
    "    )\n",
    "\n",
    "    SELECT county_id,\n",
    "        county_name,\n",
    "        population,\n",
    "        RANK() OVER(ORDER BY population DESC) as population_rank,\n",
    "        -- number of complaints per county whose nearest service center is more than x distance away\n",
    "        COUNT(\n",
    "            CASE WHEN (dist > 15) AND a.cmpl_geom.ST_Within(b.geometry) = 1 THEN cmplid\n",
    "                ELSE NULL\n",
    "            END) as noncovered_customers,\n",
    "        RANK() OVER(ORDER BY noncovered_customers DESC) as noncovered_customers_rank,\n",
    "        COUNT(\n",
    "            CASE WHEN a.cmpl_geom.ST_Within(b.geometry) = 1 THEN cmplid\n",
    "                ELSE NULL\n",
    "            END) as customers,\n",
    "        RANK() OVER(ORDER BY customers DESC) as customers_rank,\n",
    "        CASE WHEN customers = 0 THEN NULL\n",
    "            ELSE (noncovered_customers*1.0000)/(customers*1.0000) \n",
    "        END as noncovered_customers_pct,\n",
    "        COALESCE(noncovered_customers_pct,0) AS noncovered_customers_pct2,        \n",
    "        CASE WHEN noncovered_customers_pct IS NULL THEN NULL \n",
    "            ELSE RANK() OVER(ORDER BY noncovered_customers_pct DESC) \n",
    "        END as noncovered_customers_pct_rank\n",
    "    FROM nearest a,\n",
    "        demo_car.counties b\n",
    "        where a.cmpl_geom.st_x() between geo_mbr.xmin() and geo_mbr.xmax() \n",
    "        and a.cmpl_geom.st_y() between geo_mbr.ymin() and geo_mbr.ymax()\n",
    "    GROUP BY 1,2,3\n",
    "; ''')    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ff892b8-81a2-4d59-9ef8-924a15ca0344",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i>* The view query has complex geometry calculation, the below step takes approx 1min to run.</i>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d14c3de-db0f-42e2-9ec6-e534a0abc1ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = DataFrame.from_query('''select county_id,\n",
    "    county_name,\n",
    "    population_rank,\n",
    "    noncovered_customers_rank,\n",
    "    customers_rank,\n",
    "    noncovered_customers_pct_rank\n",
    "    from county_accessibility;''')\n",
    "ca=df.to_pandas()\n",
    "ca.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8cb61c9-3fef-4c12-b347-30dc7ab0eb98",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us visualize this data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15ff916f-5aff-434f-8267-2c739e6f2a20",
   "metadata": {},
   "outputs": [],
   "source": [
    "#read the geojson files to get boundry information of the counties\n",
    "with open (\"./data/Iowa_County_Boundaries.geojson\",'r') as infile:\n",
    "    counties = json.load(infile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55469e36-e8cb-435f-b0f8-5f517bf7c4f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def plot_map(candidate):    \n",
    "    plt.figure(figsize=(8, 6))\n",
    "    fig = px.choropleth_mapbox(\n",
    "        ca, geojson=counties, color=candidate,\n",
    "        locations=\"county_id\", featureidkey=\"properties.FIPS\",\n",
    "        center={\"lat\": 42.032974, \"lon\": -93.581543},\n",
    "                           mapbox_style=\"open-street-map\",\n",
    "                           zoom=5)\n",
    "    fig.update_geos(fitbounds=\"locations\", visible=False)\n",
    "    fig.update_layout(margin={\"r\":0,\"t\":0,\"l\":0,\"b\":0})\n",
    "    return fig\n",
    "\n",
    "# Create the dropdown widget\n",
    "candidates = [\"population_rank\",\"noncovered_customers_rank\", \"customers_rank\",\"noncovered_customers_pct_rank\"]\n",
    "candidate_dropdown = widgets.Dropdown(options=candidates, description='Candidate:', value='population_rank')\n",
    "\n",
    "# Call the plot_clusters function with the selected dropdown options\n",
    "def update_plot(candidate):    \n",
    "    plot_map(candidate).show()\n",
    "    \n",
    "widgets.interact(update_plot, candidate=candidate_dropdown)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0706cd6d-a214-4c21-8825-e350b655a7e4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i>*Please note that the map takes few seconds to refresh after dropdown change</i><br>As we can see the map above shows the counties based on the various ranks we have calculated"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "574a3002-ed28-42b5-b925-afb6519a6386",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e202080-e562-4eba-aed6-ed178d6ac3f8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Defect Analysis\n",
    " </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> In our demo we will find complaints where car parts had early defects from the date of purchase compared to other complaints. We will try to find  if there is any outliers or anomalies in the complaint data. In simple words an outlier is a data point that differs significantly from other observations.<br>The main benefits from this type of analysis are that <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Car insurance providers or manufacturers can detect suspicious car defect complaints.</li>                               <li>Helps car companies in determining faulty car models and car parts.</li>\n",
    "    <li>Car manufacturers can use this to know where to improve in models with numerous early defect complaints.</li></ul>\n",
    "        <p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use Vantage's TD_OutlierFit and Transforms functions to find the outliers in data and analyse them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a08d3377-740c-4f9b-9395-b13d170156a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#we have pulled the complaints table earlier we'll use that dataframe \n",
    "com=df3.to_pandas()\n",
    "com.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a01b89c5-7d0b-4f0d-91da-cf1676b5bdbe",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Let us plot a boxplot on the complaints data. A box plot is a graphical rendition of statistical data based on the minimum, first quartile, median, third quartile, and maximum. The term <b>box plot</b> comes from the fact that the graph looks like a rectangle with lines extending from the top and bottom."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62b6a412-6b23-468e-9367-5faa0a782b28",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(20,8))\n",
    "#plt.xlabel('Sales Date', fontsize=16, rotation=45)--- ye rotation waala parameter try karo \n",
    "#plt.xlabel('car_part', fontsize=16, rotation=45)\n",
    "plt.tick_params(axis='x', which='major', labelsize=14, rotation=90)\n",
    "plt.xlabel('car_part', fontsize=16);\n",
    "plt.ylabel('days_to_defect', fontsize=16);\n",
    "plt.title('Box plot of the data by car part', fontsize=20)\n",
    "ax = sns.boxplot(x = 'car_part', y = 'days_to_defect', data = com)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea83ed17-276c-4681-8869-a6b461e134c5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The widths of the box plot indicate the size of the samples. The wider the box, the larger the sample. As we have many car parts in our dataset let us select few and plot them again to get a better visual."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0febcff-df93-4c45-988d-3b458e248793",
   "metadata": {},
   "outputs": [],
   "source": [
    "options = ['STEERING' ,'ENGINE','STRUCTURE','POWER TRAIN'] \n",
    "\n",
    "#com[com['car_part']=='STEERING']\n",
    "plt.figure(figsize=(20,8))\n",
    "#plt.xlabel('Sales Date', fontsize=16, rotation=45)--- ye rotation waala parameter try karo \n",
    "#plt.xlabel('car_part', fontsize=16, rotation=45)\n",
    "plt.tick_params(axis='x', which='major', labelsize=14, rotation=90)\n",
    "plt.xlabel('car_part', fontsize=16);\n",
    "plt.ylabel('days_to_defect', fontsize=16);\n",
    "plt.title('Box plot of the data by car part', fontsize=20)\n",
    "ax = sns.boxplot(x = 'car_part', y = 'days_to_defect', data = com[com['car_part'].isin(options)])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30f14769-2899-4e6c-ad18-233793dc66dc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The mid line inside the box is the median(Q2) of data and lower(Q1) and top lines(Q3) of the box is 25% and 75% of the data. The lowest limit value equals Q1 – 1.5 * (Q3-Q1) and the upper limit value equals Q3 + 1.5 * (Q3-Q1). Any points that lie beyond the limit points are considered outliers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05aaa41b-163c-47dd-ae49-e20dc068d280",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Now let us try to find the outliers in the data. <b>OutlierFilterFit</b> from teradataml library calculates the lower_percentile, upper_percentile, count of rows and median for the specified input table columns. The calculated values are passed to <b>OutlierFilterTransform</b> function to filter out the outliers from the dataset. We are using Tukey method ([Q1 - k*(Q3-Q1), Q1 + k*(Q3-Q1)] where k is interquantile range multiplier) for outlier detection, other methods available are Percentile and Carling. Please refer to documentation for a full listing of parameters and return values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1273514-c6b4-4486-8a6e-fc14a2248dc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import OutlierFilterFit, OutlierFilterTransform\n",
    "\n",
    "fit_obj = OutlierFilterFit(data=df3,\n",
    "                               target_columns=\"days_to_defect\",\n",
    "                               outlier_method=\"TUKEY\",\n",
    "                               replacement_value=\"DELETE\",\n",
    "                               iqr_multiplier=0.1,\n",
    "                               remove_tail=\"LOWER\",\n",
    "                               group_columns=\"car_part\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94d8b756-3739-4252-9caf-b3f23f0eaad5",
   "metadata": {},
   "outputs": [],
   "source": [
    "fit_obj.result.sample(n = 5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa9c035c-c4a4-433b-a418-e30fa5341136",
   "metadata": {},
   "outputs": [],
   "source": [
    "obj = OutlierFilterTransform(data=df3,data_partition_column=\"car_part\",\n",
    "                             object=fit_obj.result,\n",
    "                             object_partition_column=\"car_part\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecb69cbb-460a-439f-842b-4601b734ecb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df5=(obj.result).to_pandas()\n",
    "df5.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9207649a-9763-44f8-8090-fe91accac2ba",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Our source input dataframe had 1000 records where as the transformed dataframe has 829 records. Let us minus the two dataframes to get only the records which are marked as outliers based on our input parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f912ba8-207c-4b29-858b-c127baf8a754",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml.dataframe.setop import td_minus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98b2c9d9-3068-429f-8324-041a3ef4311f",
   "metadata": {},
   "outputs": [],
   "source": [
    "idf = td_minus([df3, obj.result], allow_duplicates=False)\n",
    "df6=idf.to_pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "286248ad-f5b7-4523-b6fb-fd6705b1c454",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us plot this data to see which car parts have the higher number of complaints."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "532b5e0b-03bd-4940-a15d-a7b900b1b786",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(20,8))\n",
    "plt.tick_params(axis='x', which='major', labelsize=14, rotation=90)\n",
    "ax = sns.countplot(x=\"car_part\",data=df6)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0003327e-b388-4172-976d-c20e304f3645",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As we can see from the plot above the steering and power train have the most number of complaints. We can do similar analysis on car models also."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a52a7e98-3a51-448d-aa44-1a2ab1ca1bb3",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12168ad8-6034-4a87-9ff7-3e248124e902",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Cleanup</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>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "223c47a6-3423-46c9-969f-1a99fe27e080",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_Car');\" \n",
    "#Takes 10 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e938942a-b9e8-4fb8-8c1c-e68a13f86b3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1938d0e-a7d9-4796-a9ca-3165ca242d2d",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f66b060-1727-4ff9-9042-a604770f7c0b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Conclusion</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As we have seen in this demo that we can get great insights from our data if we augment the data with its geographical parameters. We have also seen that the anomalies happen in the data which may or may not be a cause of concern but analysis on them can lead to better insights on how business can enhance their processes or divert the resources where needed."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0efcc34-150e-410f-a340-d7c06beba754",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Reference Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'> \n",
    "       <li>Teradata Package for Python Function Reference: <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",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "601336e2-119d-4126-96f1-cc042546b63f",
   "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,2024. 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": 5
}