UseCases/Knee_Replacement/Knee_Replacement_nPath_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Total Knee Replacement: Path Analysis\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" styTotal Knee Replacement: Path Analysisle=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>\n",
    "<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'>Pathing is the process of discovering a sequence of antecedent actions that occur prior to a specific event of interest on sessionized data. Pathing discovers the most salient patterns across a group of individuals or entities based on which further actions are considered. Pathing allows you to provide an explanation of the relation and the relative importance of each factor.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The <code>nPath</code> function provides a flexible pattern-matching capability that lets you specify complex patterns in the input data and define the values that are output for each matched input set.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Value</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Comprehensive health predictions and a reduced number of false positive and false negative results.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Reduced cost to patients and hospitals.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Identify patterns and symptoms leading to total knee replacements.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Provides an understanding of various touchpoints that lead to total Knee Replacement.</li></p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Why Vantage?</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>With the advantages of Vantage and ClearScape Analytics, we bring key differentiating capabilities that set us apart from the current fragmented state many of our customers and prospects face. Analyzing behavior across multiple touchpoints poses many challenges.  Data is disconnected, or when it is available, typically exists in vast quantities that make timely, accurate analysis impossible.  If this analysis is available, it is typically only available inside of a closed, proprietary ecosystem.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Fortunately, Vantage allows users to unlock the value in their various journeys by</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Accessing data across multiple, disconnected silos, and</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Applying unique, massively-scalable analytics capabilities which allows organizations to understand a complete and accurate picture of the user journey.</li></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>With Vantage, these analytics can be run in real-time for any data volume or number of users – providing an accurate, timely view of overall user experience. And of course, Vantage and ClearScape Analytics allow for at-scale processing across all the digital interactions that make up these journeys.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Furthermore, this analysis can be passed to additional ML and AI capabilities available in ClearScape Analytics, helping to develop more accurate and actionable predictive models.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The goal is to prepare data using Vantage in-database analytic functions, then to visualize them in Sankey charts in order to view care pathways leading to a total knee replacement. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data used in this demonstration represents procedures for injuries to the knee.</p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>289,839 events for various members</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>51 states/territories</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>13 procedures</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>4 months</li>\n",
    "\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo, we will leverage teradataml virtual dataframes with Vantage Analytic in-database functions - Sessionize() and nPath() - to create a Sankey visualization using open source package, Plotly.</p>\n",
    "\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "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.</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the section, we import the required libraries and set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#%%capture \n",
    "#!pip install colorlover\n",
    "#!pip install teradataml --upgrade teradataml"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>The above statements may need to be uncommented if you run the notebooks on a platform other than ClearScape Analytics Experience that does not have the libraries installed. If you uncomment those installs, be sure to restart the kernel after executing those lines to bring the installed libraries into memory. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import getpass\n",
    "import pandas as pd\n",
    "\n",
    "from teradataml.dataframe.dataframe import DataFrame\n",
    "from teradataml import *\n",
    "\n",
    "from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot\n",
    "from collections import defaultdict\n",
    "import plotly.offline as offline\n",
    "import colorlover as cl\n",
    "offline.init_notebook_mode()\n",
    "display.max_rows=5\n",
    "\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)"
   ]
  },
  {
   "cell_type": "markdown",
   "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. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Knee_Replacement_nPath_Python.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. 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>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_KneeReplacement_cloud');\"\n",
    " # Takes about 10 seconds\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_KneeReplacement_local');\"\n",
    " # Takes about 30 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "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,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Analyze the raw data set</b></p>\n",
    "\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>\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "knee_claims = DataFrame(in_schema('DEMO_KneeReplacement', 'Knee_Replacement'))\n",
    "knee_claims"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>There are 10 columns mentioning the procedure code , diagnosis code and shortdesc which mentions the procedure to be conducted and the amount required for the procedure along with other personal details.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Sessionize() Function</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Sessionize() Function in the Advanced SQL Engine (SQLE) was originally designed to capture a sequence of web clicks by one user that are separated by at most *n* seconds. However, we've also found it useful to define a sequence of events along a longitudinal record of a member's patient journey. In this case, we will define a session as one day.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>one day = 60 seconds x 60 minutes x 24 hours = 86,400 seconds</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we rename the column shortdesc to procedure and then use Sessionize function on that data. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "knee_claims = knee_claims.assign(proceduredesc=knee_claims.shortdesc)\n",
    "knee_claims = knee_claims.drop('shortdesc', axis=1)\n",
    "knee_claims.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "knee_claims_sessions = Sessionize(data = knee_claims,\n",
    "                               data_partition_column = [\"memberid\"],\n",
    "                               data_order_column = [\"tstamp\"],\n",
    "                               time_column = \"tstamp\",\n",
    "                               time_out = 86400.0, # must be passed as a float\n",
    "                               )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note the SESSIONID column that is appended to the end.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "knee_claims_sessions.result.sort(['memberid', 'tstamp'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The result is ordered sessions per member ID.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. nPath() Function </b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>More insight is gained when combining the events into a path, then further analyzing a population of paths - that is, more common paths, paths with desirable or undesirable outcomes.</p> "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now let us build a path on the sessionization results.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ConvertTo\n",
    "knee_claims_sessions_out = ConvertTo(data = knee_claims_sessions.result,\n",
    "                           target_columns = ['memberid','proccode','diagcode','amount', 'tstamp','firstname','lastname','email', 'state','proceduredesc','SESSIONID'],\n",
    "                           target_datatype = [\"integer\",\"integer\",\"VARCHAR(charlen=20,charset=LATIN,casespecific=NO)\", \"integer\",\"timestamp\",\"VARCHAR(charlen=50,charset=LATIN,casespecific=NO)\",\n",
    "                                              \"VARCHAR(charlen=50,charset=LATIN,casespecific=NO)\",\"VARCHAR(charlen=255,charset=LATIN,casespecific=NO)\",\"VARCHAR(charlen=5,charset=LATIN,casespecific=NO)\",\n",
    "                                              \"VARCHAR(charlen=500,charset=LATIN,casespecific=NO)\",\"integer\"])\n",
    "knee_claims_sessions_out.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Create paths ending in Total Knee Replacement</b> </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "npath_sessions = NPath(data1 = knee_claims_sessions_out.result, \n",
    "                      data1_partition_column = ['memberid'], \n",
    "                      data1_order_column = ['tstamp','proceduredesc'], \n",
    "                      mode = 'NONOVERLAPPING', \n",
    "                      symbols = ['proceduredesc in (\\'TOTAL KNEE REPLACEMENT\\') as E', 'proceduredesc not in (\\'TOTAL KNEE REPLACEMENT\\') as A'], \n",
    "                      pattern = 'A{1,5}*.E', \n",
    "                      result = ['ACCUMULATE(proceduredesc OF ANY(A,E)) AS path',\n",
    "                            'FIRST(diagcode OF ANY(A,E)) AS diagnosis', \n",
    "                            'FIRST(memberid OF ANY(A,E)) AS unique_memberid',   \n",
    "                            'COUNT(proceduredesc OF ANY(A,E)) AS proc_cnt',\n",
    "                            'FIRST(tstamp OF ANY(E)) AS starttime'])\n",
    "\n",
    "npath_sessions.result\\\n",
    "                    .groupby(['path'])\\\n",
    "                    .count()\\\n",
    "                    .sort('count_unique_memberid',ascending=False)\\\n",
    "                    .head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "knee_path_results = npath_sessions.result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Create paths starting with Range of Motion Testing, has Physical Therapy NEC in the middle, and ends in Total Knee Replacement</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "knee_path_results.select(['path','unique_memberid']).groupby('path').count().sort('count_unique_memberid', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Sankey Charts</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Sankey charts can help visualize pathways and volume of events on the  most common paths.  Teradata Vantage Cloud does have built-in visualizations, but if users don't have access to these capabilities, or perhaps there is a preference to work in Python, the Plotly package provides Sankey charting capabilities.  Below the Python function reads output from nPath as pandas dataframe and plots Sankey chart. This method will be used throughout for plotting Sankey.</p>\n",
    "\n",
    "<p style = 'font-size:14px;font-family:Arial;color:#00233C'><i><b>**Note: The code in the below cell is just the definition of the function created for Sankey charts, so will not show any chart. The function is used later in the notebook below wherever charts are needed </b></i></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Convert Teradata nPath output to plotly Sankey\n",
    "#can handle paths up to 999 links in length\n",
    "import plotly.graph_objects as go\n",
    "def sankeyPlot(res, title_text=\"Basic Sankey Path\"):\n",
    "    npath_pandas = res\n",
    "    \n",
    "    dataDict = defaultdict(int)\n",
    "    eventDict = defaultdict(int)\n",
    "    maxPath = npath_pandas['count_unique_memberid'].max()\n",
    "    \n",
    "    for index, row in npath_pandas.iterrows():\n",
    "        rowList = row['path'].replace('[','').replace(']','').split(',')\n",
    "        pathCnt = row['count_unique_memberid']\n",
    "        pathLen = len(rowList)\n",
    "        for i in range(len(rowList)-1):\n",
    "            leftValue = str(150 + i + maxPath - pathLen) + rowList[i].strip()\n",
    "            rightValue = str(150 + i + 1 + maxPath - pathLen) + rowList[i+1].strip()\n",
    "            valuePair = leftValue + '+' + rightValue\n",
    "            dataDict[valuePair] += pathCnt\n",
    "            eventDict[leftValue] += 1\n",
    "            eventDict[rightValue] += 1\n",
    "    \n",
    "    eventList = []\n",
    "    for key,val in eventDict.items():\n",
    "        eventList.append(key)\n",
    "    \n",
    "    sortedEventList = sorted(eventList)\n",
    "    sankeyLabel = []\n",
    "    for event in sortedEventList:\n",
    "        sankeyLabel.append(event[3:])\n",
    "    \n",
    "    sankeySource = []\n",
    "    sankeyTarget = []\n",
    "    sankeyValue = []\n",
    "\n",
    "    for key,val in dataDict.items():\n",
    "        sankeySource.append(sortedEventList.index(key.split('+')[0]))\n",
    "        sankeyTarget.append(sortedEventList.index(key.split('+')[1]))\n",
    "        sankeyValue.append(val)\n",
    "    \n",
    "    sankeyColor = []\n",
    "    for i in sankeyLabel:\n",
    "        sankeyColor.append('blue')\n",
    "    \n",
    "    sankeyChart = dict(\n",
    "        type='sankey',\n",
    "        node = dict(\n",
    "          pad = 15,\n",
    "          thickness = 20,\n",
    "          line = dict(\n",
    "            color = 'black',\n",
    "            width = 0.5\n",
    "          ),\n",
    "          label = sankeyLabel,\n",
    "          color = sankeyColor\n",
    "        ),\n",
    "        link = dict(\n",
    "            source = sankeySource,\n",
    "            target = sankeyTarget,\n",
    "            value = sankeyValue\n",
    "        )\n",
    "      )\n",
    "    layout =  dict(\n",
    "        title = title_text,\n",
    "        font = dict(\n",
    "          size = 10\n",
    "        )\n",
    "    )\n",
    "\n",
    "    link = dict(source = sankeySource, target = sankeyTarget, value = sankeyValue, color='white')\n",
    "    node = dict(label=sankeyLabel, pad=15, thickness=10, color='orange')\n",
    "    data=go.Sankey(link=link, node=node)\n",
    "    # plot \n",
    "    fig=go.Figure(data)\n",
    "    fig.update_layout(\n",
    "            hovermode ='closest',\n",
    "            title = title_text,\n",
    "            title_font_size=20,\n",
    "            font = dict(size = 10, color = 'white'),\n",
    "            plot_bgcolor='black',\n",
    "            paper_bgcolor=\"#585958\"\n",
    "            )\n",
    "    \n",
    "    fig.show()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "warnings.simplefilter(action='ignore', category=DeprecationWarning)\n",
    "res = knee_path_results\\\n",
    "                    .groupby(['path'])\\\n",
    "                    .count()\\\n",
    "                    .sort('count_unique_memberid',ascending=False)\\\n",
    "                    .head(10)\\\n",
    "                    .to_pandas()\n",
    "sankeyPlot(res,\"Path to Total Knee Replacement\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TOP 3 events that led to <b>Total Knee Replacement</b> are <b>Knee Arthroscopy</b>, <b>Knee Joint Biopsy</b> and <b>Other Diagnostic Physical Therapy Procedure.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To check the details of any path or node we can move the mouse pointer over it and check details. For example if you move the pointer over the path having the largest width at the right most path going towards the right most node(Total Knee Replacement) it shows <b>6, source: Knee Arthroscopy, target: Total Knee Replacement.</b> It means there were 6 cases where Knee Arthroscopy led to recommendation of Total Knee Replacement.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When the pointer is moved over a Node, for example when the pointer is on the long Node at the right in the middle <b>OTH Arthrotomy-Knee </b>  it shows <b>incoming flow count: 2 and outgoing flow count: 3</b> which means that there are 2 different events which lead to OTH Arthrotomy-Knee after which the next 3 events are Knee Arthoroscopy, Knee Joint Biopsy and Other Diagnostic Physical. Similarly other nodes and paths can be analyzed.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Check paths that led to Knee Arthroscopy</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As seen in the above analysis ,Knee replacement was suggested most number of times after knee arthroscopy, so let's try to analyze what events led to Knee Arthroscopy</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "npath_sessions1 = NPath(data1 = knee_claims_sessions_out.result, \n",
    "                      data1_partition_column = ['memberid'], \n",
    "                      data1_order_column = ['tstamp','proceduredesc'], \n",
    "                      mode = 'NONOVERLAPPING', \n",
    "                      symbols = ['proceduredesc in (\\'KNEE ARTHROSCOPY\\') as E', 'proceduredesc not in (\\'KNEE ARTHROSCOPY\\') as A'], \n",
    "                      pattern = 'A{1,5}*.E', \n",
    "                      result = ['ACCUMULATE(proceduredesc OF ANY(A,E)) AS path',\n",
    "                            'FIRST(diagcode OF ANY(A,E)) AS diagnosis', \n",
    "                            'FIRST(memberid OF ANY(A,E)) AS unique_memberid',   \n",
    "                            'COUNT(proceduredesc OF ANY(A,E)) AS proc_cnt',\n",
    "                            'FIRST(tstamp OF ANY(E)) AS starttime'])\n",
    "\n",
    "npath_sessions1.result\\\n",
    "                    .groupby(['path'])\\\n",
    "                    .count()\\\n",
    "                    .sort('count_unique_memberid',ascending=False)\\\n",
    "                    .head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "knee_path_results1 = npath_sessions1.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "warnings.simplefilter(action='ignore', category=DeprecationWarning)\n",
    "res = knee_path_results1\\\n",
    "                    .groupby(['path'])\\\n",
    "                    .count()\\\n",
    "                    .sort('count_unique_memberid',ascending=False)\\\n",
    "                    .head(10)\\\n",
    "                    .to_pandas()\n",
    "sankeyPlot(res,\"Path to Knee ARTHROSCOPY\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TOP event that led to <b>Knee Arthroscopy</b> is <b>Excis Knee Semilun Cartl</b>.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To check the details of any path or node we can move the mouse pointer over it and check details. Similarly other nodes and paths can be analyzed using similar functions.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Now let's Check paths that led to Other Diagnostic Physical Therapy Procedure.</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As seen in the above analysis ,Knee replacement was suggested after these procedures also.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "npath_sessions_oth = NPath(data1 = knee_claims_sessions_out.result, \n",
    "                      data1_partition_column = ['memberid'], \n",
    "                      data1_order_column = ['tstamp','proceduredesc'], \n",
    "                      mode = 'NONOVERLAPPING', \n",
    "                      symbols = ['proceduredesc in (\\'OTHER DIAGNOSTIC PHYSICAL THERAPY PROCEDURE\\') as E', 'proceduredesc not in (\\'OTHER DIAGNOSTIC PHYSICAL THERAPY PROCEDURE\\') as A'], \n",
    "                      pattern = 'A{1,5}*.E', \n",
    "                      result = ['ACCUMULATE(proceduredesc OF ANY(A,E)) AS path',\n",
    "                            'FIRST(diagcode OF ANY(A,E)) AS diagnosis', \n",
    "                            'FIRST(memberid OF ANY(A,E)) AS unique_memberid',   \n",
    "                            'COUNT(proceduredesc OF ANY(A,E)) AS proc_cnt',\n",
    "                            'FIRST(tstamp OF ANY(E)) AS starttime'])\n",
    "\n",
    "npath_sessions_oth.result\\\n",
    "                    .groupby(['path'])\\\n",
    "                    .count()\\\n",
    "                    .sort('count_unique_memberid',ascending=False)\\\n",
    "                    .head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "knee_path_results_oth = npath_sessions_oth.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "warnings.simplefilter(action='ignore', category=DeprecationWarning)\n",
    "res = knee_path_results_oth\\\n",
    "                    .groupby(['path'])\\\n",
    "                    .count()\\\n",
    "                    .sort('count_unique_memberid',ascending=False)\\\n",
    "                    .head(10)\\\n",
    "                    .to_pandas()\n",
    "sankeyPlot(res,\"Path to Other Diagostic Procedures\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As seen in the above sankey diagram there are various events that led to Other Diagnostic Procedures. The TOP event being<b> Oth Arthrotomy-Knee</b> followed by <b>Knee Arthroscopy.</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To check the details of any path or node we can move the mouse pointer over it and check details. Thus, we have seen that using the nPath function we can check on various Events that led to different procedures and finally Total Knee Replacement.</p>\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Total Cost for Knee Replacements.</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's check how we can use the nPath function to find the average cost involved in different paths that lead to Total Knee Replacement.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "npath_sessions_cost = NPath(data1 = knee_claims_sessions_out.result, \n",
    "                      data1_partition_column = ['memberid'], \n",
    "                      data1_order_column = ['tstamp','proceduredesc'], \n",
    "                      mode = 'NONOVERLAPPING', \n",
    "                      symbols = ['proceduredesc in (\\'TOTAL KNEE REPLACEMENT\\') as E', 'proceduredesc not in (\\'TOTAL KNEE REPLACEMENT\\') as A'], \n",
    "                      pattern = 'A{1,5}*.E', \n",
    "                      result = ['ACCUMULATE(proceduredesc OF ANY(A,E)) AS path',\n",
    "                            'sum(amount OF ANY(A,E)) AS total_cost', \n",
    "                            'FIRST(memberid OF ANY(A,E)) AS unique_memberid',   \n",
    "                            'COUNT(proceduredesc OF ANY(A,E)) AS proc_cnt',\n",
    "                            'FIRST(tstamp OF ANY(E)) AS starttime'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "knee_path_cost_results=npath_sessions_cost.result\n",
    "# knee_path_cost_results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=knee_path_cost_results.select(['path','total_cost']).groupby('path').mean().to_pandas()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_graph=df.sort_values(['mean_total_cost'],ascending=False).groupby('path').head(10)\n",
    "df1=df_graph.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as mp\n",
    "df1.plot(x=\"path\", y=\"mean_total_cost\", kind=\"bar\",figsize=(20, 9))\n",
    "\n",
    "# displaying bar graph\n",
    "mp.xticks(rotation='vertical')\n",
    "mp.rcParams['xtick.labelsize'] = 20 \n",
    "mp.legend()\n",
    "mp.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph shows the Top 10 paths based on the Total Cost required for Total Knee Replacement.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Conclusion</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thus, using the unique analytic capabilities of Vantage for understanding customer and user behavior over time. In fact, these analytic techniques can be applied at massive scale to derive more accurate results. Then, these results can be combined with other analytics to create more advanced and accurate prediction models. Vantage also allows organizations to scale these models horizontally, by training segmented models per region, user type, etc., or vertically, by combining data from millions or billions of interactions. All of this can be deployed operationally to understand and predict actions in real-time.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Cleanup</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Databases and Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_KneeReplacement');\" \n",
    "#Takes 45 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Removing the connection and clearing session\n",
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Resources</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>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Links</b>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://docs.teradata.com/reader/eteIDCTX4O4IMvazRMypxQ/uDjppX7PJInABCckgu~KFg'>Teradata Python Package User Guide </a> </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://docs.teradata.com/reader/GsM0pYRZl5Plqjdf9ixmdA/MzdO1q_t80M47qY5lyImOA'>Teradataml Python Reference </a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://docs.teradata.com/reader/CWVY0AJy8wyyf7Sm0EsK~w/wjkE42ypEfeMkRFOIqVXfQ'>Teradata nPath Function Reference:</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://docs.teradata.com/reader/CWVY0AJy8wyyf7Sm0EsK~w/RNbOiUg9~r~cxSZHrR~sFQ'>Teradata Sessionize Function Reference</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://pandas.pydata.org/docs/user_guide/index.html'>Python Pandas Reference</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href = 'https://plotly.com/'>Plotly Reference</a></li>\n",
    "</p>    \n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Filters:</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Industry:</b> HealthCare </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Functionality:</b> Path Analytics</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Use Case:</b> Total Knee Replacement</li></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Related Resources:</b></p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href='https://teradata.seismic.com/Link/Content/DCGBP9J9gjD288TPcG3HFgXDHDW8'>Broken Digital Journeys CX Solution Accelerator Demo via Python Video - External - SP004183</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href='https://www.teradata.com/Blogs/Forecasting-COVID-19-Using-Teradata-Vantage'>Forecasting COVID-19 Using Teradata Vantage</a></li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><a href='https://usc-word-edit.officeapps.live.com/we/%E2%80%A2%09https:/www.teradata.com/Blogs/Predicting-Heart-Failure-with-Teradata'>Saving Lives, Saving Costs: Predicting Heart Failure with Teradata</a></li></p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}