UseCases/Graph_Analysis/Graph_Analysis_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5f462bad-9f88-4416-a3de-bae6324319cb",
   "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",
    "       Graph Analysis using Script Table Operator(STO)\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": "05bc242a-7ee9-4b33-b837-ff52a9cd76d2",
   "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'>Call Detail Records (CDRs) contain valuable information about communication patterns and interactions between users. By leveraging community detection algorithms on CDR data, businesses can gain insights into the underlying network structure and uncover meaningful communities within their user base.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The objective of this analysis is to identify distinct communities or groups of users within the CDR network. Communities are like smaller social circles or friend groups within the larger group of friends. It helps us understand how people naturally form different clusters based on their interactions and relationships. This analysis also identifies influential people in the graph.\n",
    "<br>\n",
    "<br>\n",
    "By grouping users into communities based on their calling patterns, the business can better understand the dynamics and relationships among users, leading to several potential applications and benefits like Customer Segmentation, Fraud Detection, Network Optimization, Cross-Selling and Upselling Opportunities, Customer Support and Retention, etc.\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo, we'll be using <b>Script Table Operator(STO)</b> to execute custom python scripts on Vantage. The STO operates by executing R and Python scripts from the command line of the Advanced SQL Engine underlying operating system, according to\n",
    "the following sequence:\n",
    "</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "  <li>The language script is installed on the Advanced SQL Engine of the target Teradata Vantage system via a call to\n",
    "an External Stored Procedure (XSP)</li>\n",
    "  <li>The script is invoked by executing a SQL query that calls the STO</li>\n",
    "  <li>Each Advanced SQL Engine AMP provides its own portion of input table data, if any, to the script. The script\n",
    "reads its input from the standard input STDIN</li>\n",
    "  <li>Each Advanced SQL Engine AMP runs a different instance of the same script. Hence, the script execution is an\n",
    "operation that scales through system architecture as the same script is run concurrently on all AMPs</li>\n",
    "  <li>The script executes its code and sends its results to the standard output STDOUT. Each Advanced SQL Engine\n",
    "AMP individually picks up the corresponding script instance results, and returns them to the STO</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92c9fc4a-42bb-454a-8f7e-5b0983004501",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Downloading and installing additional software needed</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2c074f7-9866-4af0-b822-434435c361a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "# '%%capture' suppresses the display of installation steps of the following packages\n",
    "!pip install python-louvain\n",
    "!pip install mplcursors"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d035bbaa-84c0-4401-af88-8bff031caa9d",
   "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>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01e63639-879d-4c8c-afd4-981d8aa49f79",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "import pandas as pd\n",
    "import networkx as nx\n",
    "from teradataml import *\n",
    "import community\n",
    "import matplotlib.pyplot as plt\n",
    "import warnings\n",
    "import mplcursors\n",
    "\n",
    "# Suppress warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ced12adf-b2c4-49aa-b5c8-67dd090ff080",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>1. Connect to Vantage</b>\n",
    "<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, and then use the down arrow to go to the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d506591-4e0e-4139-bb6a-b96f41377227",
   "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": "e7579c1f-eafd-4dc4-9ba9-f1396da4cde6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Graph_Analysis_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3732eef0-96bd-417b-95e3-403c1999afa8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a96f1c6-cb43-440f-9682-a851fe09165f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;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 can either run the demo using foreign tables to access the data without any storage on your environment or download the data to local storage, which may yield faster execution. Still, there could be considerations of available storage. Two statements are 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": "57a490a6-75b8-4d4d-8986-46b03fc57081",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_GraphAnalysis_cloud');\"        # Takes 30 seconds\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_GraphAnalysis_local');\"        # Takes 1 minute"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "658a3111-ab72-451c-85c2-de0a5f7a0188",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34c657c8-2122-4426-9d38-c5a619f7817a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e601e5e6-c6b3-466a-9a7b-f5ff16d2610a",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>2. Using Script Table Operator</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Below is a sample of the data provided, where 'fromuserid' represents the caller and 'touserid' represents the callee.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7142faca-a943-4bef-bb62-1b09db8fff1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "DataFrame(in_schema('DEMO_GraphAnalysis', 'graph_data'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97340221-0690-4a0f-814b-5a25c14a5c8a",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Community Detection using Louvain Algorithm</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The below cell will perform the following steps:</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Set SEARCHUIFDBPATH to demo_user</li>\n",
    "    <li>Install the communities.py file on Vantage</li>\n",
    "    <li>If the file is already installed, it will remove the file and install it again. This ensures we always have latest script in Vantage.</li>\n",
    "</ol>\n",
    "\n",
    "Code: [communities.py](./communities.py)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0e5c5a1-b81d-4bc8-ab87-4c2cb41d3441",
   "metadata": {},
   "outputs": [],
   "source": [
    "out=execute_sql(\"SET SESSION SEARCHUIFDBPATH = demo_user;\")\n",
    "\n",
    "try:\n",
    "    execute_sql(\"CALL SYSUIF.install_file('communities', 'communities.py', 'cz!/home/jovyan/JupyterLabRoot/UseCases/Graph_Analysis/communities.py');\")\n",
    "except:\n",
    "    execute_sql(\"CALL SYSUIF.remove_file('communities', 1);\")\n",
    "    execute_sql(\"CALL SYSUIF.install_file('communities', 'communities.py', 'cz!/home/jovyan/JupyterLabRoot/UseCases/Graph_Analysis/communities.py');\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5916690f-42d6-40b9-a663-669cd0349c1c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The below cell will run the script installed in the above step and store the result in the community table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b05c8cb0-6af7-4f53-a4a1-99b03ec14ed5",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = '''CREATE TABLE community AS (\n",
    "        SELECT * FROM SCRIPT(\n",
    "        ON DEMO_GraphAnalysis.graph_data\n",
    "        SCRIPT_COMMAND('tdpython3 ./demo_user/communities.py')\n",
    "        RETURNS('node INT, community_id INT')\n",
    "    ) AS tab\n",
    ") with data;'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('community')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f19a5c8c-528a-464d-81ea-777ee9fcf7a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "DataFrame('community')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f15a9780-7fb3-4994-9d77-0e4d7a6be492",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "We have a big group of customers, and they all like to talk to each other on the phone. When we talk about communities, we are interested in finding groups of users who are closely connected to each other and interact more frequently among themselves.\n",
    "<br>\n",
    "<br>\n",
    "In our usecase, communities would be like different groups of users who are really close and talk to each other frequently. It's like having different circles of friends within the larger group. Some friends may belong to multiple communities if they have connections with people in different groups.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b95dfc16-8e0d-4729-80d7-58d582ab8a75",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Eigenvector Centrality</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Eigenvector Centrality is an algorithm that measures the transitive influence of nodes. Relationships originating from high-scoring nodes contribute more to the score of a node than connections from low-scoring nodes. A high eigenvector score means that a node is connected to many nodes who themselves have high scores.\n",
    "    <br>\n",
    "    <br>\n",
    "The below cell will perform the following steps:</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Set SEARCHUIFDBPATH to demo_user</li>\n",
    "    <li>Install the centralities.py file on Vantage</li>\n",
    "    <li>If the file is already installed, it will remove the file and install it again. This ensures we always have latest script in Vantage.</li>\n",
    "</ol>\n",
    "\n",
    "Code: [centralities.py](./centralities.py)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97574e9d-77b5-48b1-8049-b571a92391c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "out=execute_sql(\"SET SESSION SEARCHUIFDBPATH = demo_user;\")\n",
    "\n",
    "try:\n",
    "    execute_sql(\"CALL SYSUIF.install_file('centralities', 'centralities.py', 'cz!/home/jovyan/JupyterLabRoot/UseCases/Graph_Analysis/centralities.py');\")\n",
    "except:\n",
    "    execute_sql(\"CALL SYSUIF.remove_file('centralities', 1);\")\n",
    "    execute_sql(\"CALL SYSUIF.install_file('centralities', 'centralities.py', 'cz!/home/jovyan/JupyterLabRoot/UseCases/Graph_Analysis/centralities.py');\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "238f5791-0d9f-46b2-b6af-dd3b21ff6048",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The below cell will run the script installed in the above step and store the result in the centrality table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "602d8868-2d93-4dbf-acc5-c87c89018572",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = '''CREATE TABLE centrality AS (\n",
    "        SELECT * FROM SCRIPT(\n",
    "        ON DEMO_GraphAnalysis.graph_data\n",
    "        SCRIPT_COMMAND('tdpython3 ./demo_user/centralities.py')\n",
    "        RETURNS('node INT, centrality FLOAT')\n",
    "    ) AS tab\n",
    ") with data;'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('centrality')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39b85736-a1cd-4105-a5e0-e6a12f0c0346",
   "metadata": {},
   "outputs": [],
   "source": [
    "DataFrame('centrality').sort('centrality', ascending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b51bfbc5-66a4-4eda-b7f8-b5a7bd93abb0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "We have a large group of customers, and they to talk to each other on the phone. Some of the customers are very popular and talk to lots of other customers, while others talk to only a few customers. Eigenvector centrality is a way to measure how important or popular each person is in this group based on the phone calls they make. So, in our graph with phone calls, eigenvector centrality helps us identify the people who are most connected to others and who have important connections. These people are considered more influential or popular in the group. This information can be used to efficiently target the the influential users and in turn the respective communities.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e691b4f-0599-40cc-992d-2d12b02544a1",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Betweenness Centrality</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Betweenness centrality is a way of detecting the amount of influence a node has over the flow of information in a graph. It is often used to find nodes that serve as a bridge from one part of a graph to another. The algorithm calculates shortest paths between all pairs of nodes in a graph.\n",
    "    <br>\n",
    "    <br>\n",
    "The below cell will perform the following steps:</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Set SEARCHUIFDBPATH to demo_user</li>\n",
    "    <li>Install the betweenness.py file on Vantage</li>\n",
    "    <li>If the file is already installed, it will remove the file and install it again. This ensures we always have latest script in Vantage.</li>\n",
    "</ol>\n",
    "\n",
    "Code: [betweenness.py](./betweenness.py)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91f4af6d-5696-4fd4-904f-48dadf351ff2",
   "metadata": {},
   "outputs": [],
   "source": [
    "out=execute_sql(\"SET SESSION SEARCHUIFDBPATH = demo_user;\")\n",
    "\n",
    "try:\n",
    "    execute_sql(\"CALL SYSUIF.install_file('betweenness', 'betweenness.py', 'cz!/home/jovyan/JupyterLabRoot/UseCases/Graph_Analysis/betweenness.py');\")\n",
    "except:\n",
    "    execute_sql(\"CALL SYSUIF.remove_file('betweenness', 1);\")\n",
    "    execute_sql(\"CALL SYSUIF.install_file('betweenness', 'betweenness.py', 'cz!/home/jovyan/JupyterLabRoot/UseCases/Graph_Analysis/betweenness.py');\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66bc9ec7-7134-44d0-b435-d4a6a0a41022",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The below cell will run the script installed in the above step and store the result in the betweenness_table table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e381a5e5-b0d3-4c65-84c1-a80ba5257b38",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = '''CREATE TABLE betweenness_table AS (\n",
    "        SELECT * FROM SCRIPT(\n",
    "        ON DEMO_GraphAnalysis.graph_data\n",
    "        SCRIPT_COMMAND('tdpython3 ./demo_user/betweenness.py')\n",
    "        RETURNS('node INT, betweenness FLOAT')\n",
    "    ) AS tab\n",
    ") with data;'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('betweenness_table')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07884ff6-c7d8-4d88-9d7c-e1a0732ba6b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "DataFrame('betweenness_table').sort('betweenness', ascending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ae8c526-04af-441f-aa8f-2a8180b49c60",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "We have a group of customers, and they all like to talk to each other on the phone. Betweenness centrality is a way to measure how important or influential you are in this group based on the phone calls made by everyone. So, if you have a lot of customers who rely on you to connect with each other, it means you have high betweenness centrality. You're like a central hub in the group, helping users communicate and making sure everyone stays connected.\n",
    "<br>\n",
    "<br>\n",
    "Betweenness centrality helps us identify the people who are important in keeping the group connected and maintaining communication. They are like the go-to person when someone needs to reach another user. They play a special role in making sure that everyone in the group stays connected and can talk to each other easily.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bf951e4-016b-4fbf-b82f-29e3aac86ca0",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Closeness Centrality</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Closeness centrality is a way of detecting nodes that are able to spread information very efficiently through a graph. The closeness centrality of a node measures its average farness (inverse distance) to all other nodes. Nodes with a high closeness score have the shortest distances to all other nodes.\n",
    "    <br>\n",
    "    <br>\n",
    "The below cell will perform the following steps:</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Set SEARCHUIFDBPATH to demo_user</li>\n",
    "    <li>Install the closeness.py file on Vantage</li>\n",
    "    <li>If the file is already installed, it will remove the file and install it again. This ensures we always have latest script in Vantage.</li>\n",
    "</ol>\n",
    "\n",
    "Code: [closeness.py](./closeness.py)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e023db9a-42bb-452d-8f4d-c7b6af2a6488",
   "metadata": {},
   "outputs": [],
   "source": [
    "out=execute_sql(\"SET SESSION SEARCHUIFDBPATH = demo_user;\")\n",
    "\n",
    "try:\n",
    "    execute_sql(\"CALL SYSUIF.install_file('closeness', 'closeness.py', 'cz!/home/jovyan/JupyterLabRoot/UseCases/Graph_Analysis/closeness.py');\")\n",
    "except:\n",
    "    execute_sql(\"CALL SYSUIF.remove_file('closeness', 1);\")\n",
    "    execute_sql(\"CALL SYSUIF.install_file('closeness', 'closeness.py', 'cz!/home/jovyan/JupyterLabRoot/UseCases/Graph_Analysis/closeness.py');\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9e647d9-cc01-4ec6-a561-fc2e38b68412",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The below cell will run the script installed in the above step and store the result in the closeness_table table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a7670d3-95e9-49a9-b740-0a0ead62ef23",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = '''CREATE TABLE closeness_table AS (\n",
    "        SELECT * FROM SCRIPT(\n",
    "        ON DEMO_GraphAnalysis.graph_data\n",
    "        SCRIPT_COMMAND('tdpython3 ./demo_user/closeness.py')\n",
    "        RETURNS('node INT, closeness FLOAT')\n",
    "    ) AS tab\n",
    ") with data;'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('closeness_table')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "367821ef-bdda-4ce6-95c2-3177b63adf41",
   "metadata": {},
   "outputs": [],
   "source": [
    "DataFrame('closeness_table').sort('closeness', ascending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "418b16e6-6090-4169-ad45-066df91b56d3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "We have a group of customers, and you all enjoy talking to each other on the phone. Closeness centrality is a way to measure how close or connected you are to all users in the group. When we talk about closeness centrality, we are interested in figuring out how quickly you can reach all the users when you make a phone call. If you can reach the users easily and quickly, then you have high closeness centrality.\n",
    "<br>\n",
    "<br>\n",
    "Think of it like this: Imagine you want to invite all your friends to a party at your house. If your house is located in the middle of the neighborhood and all your friends live nearby, it will be easy and quick for them to come to your party. In the same way, if you can reach most of your friends with just a few phone calls, it means you are centrally located in the group and have high closeness centrality.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b5b1d8b-18b5-4236-b05e-83d265030882",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>3. Visualization</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9b00e2a-6c38-4fb5-8424-b591dc0c5ec6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib widget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58998c7e-b1a8-463c-8beb-0859356cd9e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "G = nx.from_pandas_edgelist(DataFrame(in_schema('DEMO_GraphAnalysis', 'graph_data')).to_pandas().reset_index(), source='fromuserid', target='touserid', create_using=nx.Graph())\n",
    "centrality = DataFrame('centrality').to_pandas().reset_index()\n",
    "cdf = DataFrame('community').to_pandas().reset_index()\n",
    "# cdf[\"node\"] = cdf[\"node\"].astype('int64')\n",
    "\n",
    "# Define the leader nodes\n",
    "df_sorted = pd.merge(DataFrame('community').to_pandas(), DataFrame('centrality').to_pandas(), left_on='node', right_on='node', how='inner', suffixes=('_community', '_centrality')).reset_index().sort_values('centrality', ascending=False)\n",
    "leader_nodes = df_sorted.groupby('community_id')['node'].first().tolist()\n",
    "\n",
    "# Draw the graph with nodes colored based on communities\n",
    "pos = nx.spring_layout(G)\n",
    "cmap = plt.get_cmap('tab10')  # Color map for communities\n",
    "\n",
    "plt.figure(figsize=(10, 6))\n",
    "\n",
    "# Create a dictionary to store community colors\n",
    "community_colors = {}\n",
    "\n",
    "init_nodes = nx.draw_networkx_nodes(G, pos, node_size=200)\n",
    "\n",
    "for community_id in set(cdf.community_id):\n",
    "    nodes = list(cdf[cdf['community_id'] == community_id].node)\n",
    "\n",
    "    # Check if a node is a leader node\n",
    "    node_sizes = [800 if node in leader_nodes else 200 for node in nodes]\n",
    "    node_colors = [cmap(community_id) for node in nodes]\n",
    "\n",
    "    scatter = nx.draw_networkx_nodes(G, pos, nodelist=nodes, node_color=node_colors, node_size=node_sizes)\n",
    "\n",
    "    # Store community color in the dictionary\n",
    "    community_colors[community_id] = scatter.get_facecolor()[0]\n",
    "\n",
    "nx.draw_networkx_edges(G, pos, width=0.5, alpha=0.5)\n",
    "# nx.draw_networkx_labels(G, pos, font_size=8)\n",
    "\n",
    "# Create a custom legend with community colors\n",
    "legend_labels = [f'Community {community_id}' for community_id in set(cdf.community_id)]\n",
    "custom_legend = [plt.Line2D([], [], marker='o', markersize=10, color=community_colors[community_id], linestyle='', label=label) for community_id, label in zip(set(cdf.community_id), legend_labels)]\n",
    "plt.legend(handles=custom_legend)\n",
    "\n",
    "plt.title('Graph with Communities')\n",
    "plt.axis('off')\n",
    "\n",
    "# Add hover functionality to nodes\n",
    "\n",
    "def update_annot(sel):\n",
    "    node_index = sel.target.index\n",
    "    node_name = list(G.nodes)[node_index]\n",
    "    text = 'Customer ID: ' + str(node_name) + ', EigenVector Centrality Score: ' + str(max(centrality[centrality['node'] == node_name]['centrality']))\n",
    "    sel.annotation.set_text(text)\n",
    "\n",
    "cursor = mplcursors.cursor(init_nodes, hover=True)\n",
    "cursor.connect('add', update_annot)\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad1c330f-85d2-4022-88bd-acb4700d52a8",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note</b>: Please hover over the nodes to see additional information.</i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5db12928-d747-4f04-bcd3-671092f1aab7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above graph displays the data in graph format. On hovering on the node, you might see Customer ID and the EigenVector Centrality Score i.e., the influence score. The larger nodes are influential and are connected to other influential nodes. These are the leader nodes of the respective communities.\n",
    "    <br>\n",
    "    <br>Targeting the leader of the communities in a telecom dataset can provide several benefits to a telecom company. Here are some ways it can help:</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "  <li><strong>Influencing the community:</strong> Leaders of communities often hold significant influence over their members. By targeting and engaging with these leaders, a telecom company can leverage their influence to promote their products or services within the community. This can lead to increased brand awareness, customer acquisition, and loyalty.</li>\n",
    "  \n",
    "  <li><strong>Word-of-mouth marketing:</strong> Community leaders are typically respected and trusted individuals within their communities. When they endorse or recommend a telecom company's offerings, it can have a powerful impact on the community members' decisions. This word-of-mouth marketing can result in positive brand perception, organic referrals, and a higher likelihood of community members becoming customers.</li>\n",
    "  \n",
    "  <li><strong>Feedback and insights:</strong> Community leaders often have a deep understanding of their community's needs, preferences, and pain points. By establishing a relationship with these leaders, a telecom company can gain valuable insights and feedback about their products, services, and customer experiences. This information can be used to refine offerings, address specific community concerns, and tailor marketing strategies to better serve the target audience.</li>\n",
    "  \n",
    "  <li><strong>Co-creation and partnerships:</strong> Collaborating with community leaders can lead to mutually beneficial partnerships. Telecom companies can work with community leaders to co-create content, events, or initiatives that cater to the community's interests. This collaborative approach enhances engagement, fosters a sense of community, and establishes the telecom company as a trusted ally within the community.</li>\n",
    "  \n",
    "  <li><strong>Customer retention and satisfaction:</strong> Targeting community leaders allows a telecom company to prioritize customer satisfaction and address any issues or concerns promptly. By providing personalized support and assistance to these influential individuals, the company can improve overall customer experience, potentially leading to higher customer retention rates within the community.</li>\n",
    "</ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In summary, targeting leaders of telecom communities enables a company to tap into their influence, leverage word-of-mouth marketing, gain valuable insights, foster partnerships, and enhance customer satisfaction. These efforts can result in increased brand visibility, customer acquisition, and long-term success for the telecom company.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a811fed-0b58-4770-ae1f-aae0e6b9be23",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>4. Cleanup</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "775149df-63f6-48fd-8019-76c708cf72c8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Cleanup work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89645ecf-03a1-4cd5-aa94-f501cb5315a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['community', 'centrality','betweenness_table','closeness_table']\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": "23c6332a-22e7-422b-a121-0b68540ca5e8",
   "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,
   "id": "9472c2ca-65d0-4116-9c45-6411fcea52ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_GraphAnalysis');\"        # Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "adjacent-romantic",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "592b1e85-5365-43e1-a7cc-e07eb1a35210",
   "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": 5
}