UseCases/Complaints_Analysis_Azure_OpenAI/Complaints_Clustering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "473a7d4a-dc19-4b44-a062-7da705197114",
   "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",
    "       Complaints Clustering using Vantage and Azure OpenAI\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": "0b39f161-0198-4044-9956-3dc9c9b304b0",
   "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\">This feature uses advanced clustering techniques powered by <b>Teradata Vantage</b> and <b>Azure OpenAI Embeddings</b> model to group similar customer complaints together. By identifying common themes and patterns, this functionality provides valuable insights into the key issues and pain points experienced by customers.</p>\n",
    "\n",
    "\n",
    "<p style=\"font-size:16px;font-family:Arial;color:#00233c\"><b>Key Features of Complaints Clustering:</b></p>\n",
    "<ul style=\"font-size:16px;font-family:Arial;color:#00233c\">\n",
    "  <li>Leverages advanced clustering algorithms powered by <b>Teradata Vantage</b> and <b>Azure OpenAI Embeddings.</b></li>\n",
    "  <li>Groups similar customer complaints together, revealing common themes and pain points.</li>\n",
    "  <li>Provides clients with a deeper understanding of the key issues affecting their customers.</li>\n",
    "  <li>Enables clients to prioritize and address the most pressing concerns more effectively.</li>\n",
    "  <li>Helps clients identify opportunities for product improvements and enhanced customer experience.</li>\n",
    "</ul>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233c'>Unlock the revolutionary potential of Generative AI to categorize and analyze complaints with unparalleled efficiency.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233c'><b>Steps in the analysis:</b></p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Configuring the environment</li>\n",
    "    <li>Connect to Vantage</li>\n",
    "    <li>Data Exploration</li>\n",
    "    <li>Configuring Azure OpenAI and Generating Embeddings</li>\n",
    "    <li>Cluster the Complaints</li>\n",
    "    <li>Cleanup</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16ffb0c2-17a5-475f-9e98-1f86919a9ec9",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>1. Configuring the environment</b>\n",
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>1.1 Downloading and installing additional software needed</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e97af3c-0907-4a05-adf1-40b4ea1cded0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install -r requirements.txt --upgrade --quiet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a81070d-da02-49db-a422-c36143018277",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>Please restart the kernel after executing these two lines. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "034d8e59-1e9e-410c-9e56-d77543163727",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>1.2 Import the required libraries</b></p>\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": "c9f49cf5-f7b9-461a-a59c-867a414d3bcc",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Data manipulation and analysis\n",
    "import pandas as pd\n",
    "\n",
    "# Suppress warnings\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\", category=DeprecationWarning)\n",
    "\n",
    "# General imports\n",
    "import os\n",
    "import getpass\n",
    "\n",
    "# Plotting packages\n",
    "import plotly.express as px\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "# Teradata library\n",
    "from teradataml import *\n",
    "from sqlalchemy import func\n",
    "from tdapiclient import TDApiClient\n",
    "\n",
    "# Display settings\n",
    "display.max_rows = 5\n",
    "display.print_sqlmr_query = False\n",
    "display.suppress_vantage_runtime_warnings = True\n",
    "configure.val_install_location = \"val\"\n",
    "configure.byom_install_location = \"byom\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8452ad4b-84f7-4b7d-b523-5f24125264c7",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>2. Connect to Vantage</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will be prompted to provide the password. We will enter the password, press the Enter key, and then use the down arrow to go to the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c25f3d12-a0db-43ce-8244-294ce0132097",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)\n",
    "execute_sql('''SET query_band='DEMO=Complaints_Analysis_GenAI.ipynb;' UPDATE FOR SESSION;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11417221-3825-471d-af67-2f38de4059b7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bf3f8c4-1b5f-4378-bf2a-594e50b471f2",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>2.1 Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. We have the option of either running the demo using foreign tables to access the data without using any storage on our environment or downloading the data to local storage, which may yield somewhat faster execution. However, we need to consider available storage. There are two statements in the following cell, and one is commented out. We may switch which mode we choose by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "151fde7f-77b5-4c50-87b7-b182a07d19c0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_ComplaintAnalysis_cloud');\"        # Takes 1 minute\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_ComplaintAnalysis_local');\"        # Takes 2 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62bbb47c-6490-45a9-b3c0-f9281f49ae35",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>3. Data Exploration</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09c6a41f-08af-4c99-8b02-ef1e9e6759e4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = DataFrame(in_schema('DEMO_ComplaintAnalysis', 'Consumer_Complaints'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cb7fca4-365a-41b3-a2f8-feb472be6786",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1 Graph for Count of Product Complaints Over Years</b></p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233c'>The provided graph visualizes the count of complaints over the past few years, categorized by product names.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ab30b1b-13e4-4720-9df2-a2901cf92785",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "viz_df = df.assign(year = func.td_year_of_calendar(df.date_received.expression))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b692e80-8976-4240-9599-5aac385c94ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_df = viz_df.select(['product','year','complaint_id']).groupby(['product', 'year']).agg(['count']).to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4161f6ec-4250-4c0c-823f-c832daea1192",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Sorting the DataFrame by year for each product\n",
    "pd_df_sorted = pd_df.sort_values(by = ['product', 'year'])\n",
    "\n",
    "# Plotting using Plotly\n",
    "fig = px.line(\n",
    "    pd_df_sorted,\n",
    "    x = 'year',\n",
    "    y = 'count_complaint_id',\n",
    "    color = 'product',\n",
    "    markers = True,\n",
    "    title = 'Count of Product Complaints Over Years'\n",
    ")\n",
    "\n",
    "fig.update_layout(\n",
    "    xaxis_title = 'Year',\n",
    "    yaxis_title = 'Count',\n",
    "    legend_title = 'Product',\n",
    "    width = 1200,\n",
    "    height = 600\n",
    ")\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d595d54c-e906-4411-b744-3cc61585dd5e",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'> \n",
    "<p style='font-size:18px;font-family:Arial;color:#00233c'><b>3.2 Graph for Count of Complaints by Months</b></p> \n",
    "<p style='font-size:16px;font-family:Arial;color:#00233c'>The provided graph visualizes the count of complaints by months. We can see that the mean count is above 500, and the July and August months have the maximum complaints count.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d99c576-05c8-470c-b7a5-afaa1c71086c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.assign(complaint_month = func.td_month_of_year(df.date_received.expression))\n",
    "grp_gen = df.select(['complaint_month','complaint_id']).groupby(['complaint_month']).agg(['count']).to_pandas()\n",
    "\n",
    "# Define a reverse mapping dictionary\n",
    "reverse_month_mapping = {1: 'January', 2: 'February', 3: 'March', 4: 'April', 5: 'May', 6: 'June',\n",
    "                         7: 'July', 8: 'August', 9: 'September', 10: 'October', 11: 'November', 12: 'December'}\n",
    "\n",
    "# Create a new column with month names based on reverse mapping\n",
    "grp_gen['month'] = grp_gen['complaint_month'].map(reverse_month_mapping)\n",
    "\n",
    "\n",
    "fig = px.bar(\n",
    "    grp_gen.sort_values(by = 'complaint_month'),\n",
    "    x = 'month', y = 'count_complaint_id',\n",
    "    labels = {\n",
    "        'count_complaint_id': 'Number of Complaints',\n",
    "        'month': 'Complaint Month'\n",
    "    },\n",
    "    title = 'Number of Complaints by Month'\n",
    ")\n",
    "\n",
    "# Add hover information\n",
    "fig.update_traces(hovertemplate = 'Month: %{x}<br>Number of Complaints: %{y:,}')\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c43d615-4487-4b26-9339-c5a26515e949",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'> \n",
    "\n",
    "<p style='font-size:18px;font-family:Arial;color:#00233c'><b>3.3 Graph for Number of Complaints by Product</b></p> <p style='font-size:16px;font-family:Arial;color:#00233c'>The graph displays the number of complaints received for different products. The data shows that the highest number of complaints are related to credit cards or prepaid cards, as well as credit reporting and credit repair services.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1797ad2-aa25-4945-aa85-e30611ae96da",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "grp_gen = df.select(['product','complaint_id']).groupby(['product']).agg(['count']).to_pandas()\n",
    "\n",
    "fig = px.bar(\n",
    "    grp_gen,\n",
    "    x = 'product',\n",
    "    y = 'count_complaint_id',\n",
    "    labels = {\n",
    "        'count_complaint_id': 'Number of Complaints',\n",
    "        'product': 'Product'\n",
    "    },\n",
    "    title = 'Number of Complaints by Product'\n",
    ")\n",
    "\n",
    "# Add hover information\n",
    "fig.update_traces(hovertemplate = 'Product: %{x}<br>Number of Complaints: %{y:,}')\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b83e400-c23c-438f-8e40-218e90e36e7f",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'> \n",
    "\n",
    "<p style='font-size:18px;font-family:Arial;color:#00233c'><b>3.4 Graph for Number of Complaints by Issue</b></p> <p style='font-size:16px;font-family:Arial;color:#00233c'>The graph displays the number of complaints received for different issues. The data shows that the highest number of complaints are related to issue of incorrect information on your report.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eab68ba2-e74b-418d-bee4-46750a90a444",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "grp_gen = df.select(['issue','complaint_id']).groupby(['issue']).agg(['count']).to_pandas()\n",
    "\n",
    "grp_gen = grp_gen.sort_values('count_complaint_id', ascending = False)[:10]\n",
    "\n",
    "fig = px.bar(\n",
    "    grp_gen,\n",
    "    x = 'issue',\n",
    "    y = 'count_complaint_id',\n",
    "    labels = {\n",
    "        'count_complaint_id': 'Number of Complaints',\n",
    "        'issue': 'Issue'\n",
    "    },\n",
    "    title = 'Number of Complaints by Issue(Top 10)'\n",
    ")\n",
    "\n",
    "# Add hover information\n",
    "fig.update_traces(hovertemplate = 'Issue: %{x}<br>Number of Complaints: %{y:,}')\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9655211c-4ba2-4a33-b3f2-777633fbc889",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'> \n",
    "\n",
    "<p style='font-size:18px;font-family:Arial;color:#00233c'><b>3.5 Graph for Number of Complaints by Sub-Issue</b></p> \n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233c'>The graph displays the number of complaints received for different sub-issues. The data shows that the highest number of complaints are related to issue of information belongs to someone else.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d456215-beee-4133-9ff5-70131299bdf0",
   "metadata": {},
   "outputs": [],
   "source": [
    "grp_gen = df.select(['sub_issue','complaint_id']).groupby(['sub_issue']).agg(['count']).to_pandas()\n",
    "\n",
    "grp_gen = grp_gen.sort_values('count_complaint_id', ascending = False)[:10]\n",
    "\n",
    "fig = px.bar(\n",
    "    grp_gen,\n",
    "    x = 'sub_issue',\n",
    "    y = 'count_complaint_id',\n",
    "    labels = {\n",
    "        'count_complaint_id': 'Number of Complaints',\n",
    "        'sub_issue': 'Sub-Issue'\n",
    "    },\n",
    "    title='Number of Complaints by Sub-Issue(Top 10)'\n",
    ")\n",
    "\n",
    "# Add hover information\n",
    "fig.update_traces(hovertemplate = 'Sub-Issue: %{x}<br>Number of Complaints: %{y:,}')\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17a98018-d49e-49a3-9e98-b0e5cd2bdc86",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'> \n",
    "\n",
    "<p style='font-size:18px;font-family:Arial;color:#00233c'><b>3.6 Graph for Number of Complaints by Channel</b></p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233c'>The graph displays the number of complaints received for different issues. The data shows that the all the complaints are submitted by web channel.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c63e282f-4e6f-40d5-8edf-852c07b1cb4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "grp_gen = df.select(['submitted_via','complaint_id']).groupby(['submitted_via']).agg(['count']).to_pandas()\n",
    "\n",
    "# Create a mapping of numbers to product names\n",
    "product_mapping = {i: product for i, product in enumerate(grp_gen['submitted_via'])}\n",
    "\n",
    "# Replace product names with numbers in the DataFrame\n",
    "grp_gen['product_num'] = grp_gen['submitted_via'].map(\n",
    "    {product: i for i, product in enumerate(grp_gen['submitted_via'])}\n",
    ")\n",
    "\n",
    "fig = px.bar(\n",
    "    grp_gen,\n",
    "    x = 'submitted_via',\n",
    "    y = 'count_complaint_id',\n",
    "    labels = {\n",
    "                'count_complaint_id': 'Number of Complaints',\n",
    "                'submitted_via': 'Submitted Via'\n",
    "    },\n",
    "    title = 'Number of Complaints by Channel'\n",
    ")\n",
    "\n",
    "# Add hover information\n",
    "fig.update_traces(hovertemplate = 'Submitted Via: %{x}<br>Number of Complaints: %{y:,}')\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03493ca9-52b0-4546-96d8-e4d26485f557",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style='font-size:20px;font-family:Arial;color:#00233C'>4. Configuring Azure OpenAI and Generating Embeddings</b>\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Before proceeding, you need to provide the following information:</p>\n",
    "<ul style='font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li><b>Endpoint</b>: Enter your Azure OpenAI deployment endpoint.</li>\n",
    "<li><b>Azure OpenAI API Key</b>: Enter your Azure OpenAI API Key.</li>\n",
    "</ul>\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>If you haven't retrieved your API Key and Endpoint yet, follow the instructions <a href=\"https://learn.microsoft.com/en-us/azure/ai-services/openai/quickstart?tabs=command-line%2Cpython-new&pivots=programming-language-python\" target=\"_blank\" style=\"color:#0066CC;text-decoration:none;\"><b>here</b></a>.</p>\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Don't have an Azure OpenAI resource yet? Follow this guide:</p>\n",
    "<a href=\"./Azure-OpenAI.ipynb\" style=\"text-decoration:none;\" target=\"_blank\">\n",
    "    <button style=\"font-size:16px;font-family:Arial;color:#fff;background-color:#00233C;border:none;border-radius:5px;cursor:pointer;height:50px;line-height:50px;display:flex;align-items:center;\">\n",
    "        Azure OpenAI Guide <span style=\"margin-left:10px;\">&#8658;</span>\n",
    "    </button>\n",
    "</a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad7f0826-0555-467c-acc2-f0c77b161bae",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Prompt user for Azure OpenAI endpoint securely\n",
    "os.environ[\"ENDPOINT\"] = getpass.getpass(prompt=\"\\nPlease enter your Azure OpenAI endpoint(text-embedding-3-small Model): \")\n",
    "# Prompt user for Azure OpenAI API key securely\n",
    "os.environ[\"API_KEY\"] = getpass.getpass(prompt=\"\\nPlease enter your Azure OpenAI API key(text-embedding-3-small Model): \")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f27ce2c8-dc10-45cc-b08d-9fdc249afce7",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    " \n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.2 Do you want to generate the embeddings?</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have already generated embeddings for the comment text and stored them in <b>Vantage</b> table.</p>\n",
    " \n",
    "<center><img src=\"images/decision_emb_gen_cmt.svg\" alt=\"embeddings_decision\" width=300 height=400/></center>\n",
    " \n",
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note: If you would like to skip the embedding generation step to save the time and move quickly to next step, please enter \"No\" in the next prompt.</b></i></p>\n",
    "</div>\n",
    " \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To save time, you can move to the already generated embeddings section. However, if you would like to see how we generate the embeddings, or if you need to generate the embeddings for a different dataset, then continue to the following section.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81fc5101-9387-4846-ac0e-23f23495502c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Request user's input\n",
    "generate = input(\"Do you want to generate embeddings? ('yes'/'no'): \")\n",
    "\n",
    "# Check the user's input\n",
    "if generate.lower() == 'yes':\n",
    "    print(\"\\nGreat! We'll start by generating embeddings.\")\n",
    "\n",
    "    print(\"\\nGenerating embeddings, please wait...\")\n",
    "\n",
    "    # API request for embeddings\n",
    "    embedding_df = TDApiClient.API_Request(\n",
    "        dataframe = df.iloc[:1000],\n",
    "        api_type = \"az-ai-embedding\",\n",
    "        model_name = \"text-embedding-3-small\",\n",
    "        authorization = f'''{{\"Key\":\"{os.environ[\"API_KEY\"]}\"}}''',\n",
    "        endpoint = os.environ[\"ENDPOINT\"],\n",
    "        text_column = 'consumer_complaint_narrative'\n",
    "    )\n",
    "\n",
    "    # Save embeddings to SQL\n",
    "    print(\"\\nSaving embeddings to the database...\")\n",
    "    copy_to_sql(\n",
    "        df = embedding_df,\n",
    "        table_name = 'complaints_embeddings',\n",
    "        if_exists = 'replace'\n",
    "    )\n",
    "\n",
    "    print(\"\\nEmbeddings generated and saved successfully!\")\n",
    "\n",
    "elif generate.lower() == 'no':\n",
    "    print(\"\\nLoading embeddings from the local file 'embeddings.parquet.gzip'.\")\n",
    "    # Save them to SQL\n",
    "    copy_to_sql(\n",
    "        df = pd.read_parquet('./data/embeddings.parquet.gzip'),\n",
    "        table_name = 'complaints_embeddings',\n",
    "        if_exists = 'replace'\n",
    "    )\n",
    "\n",
    "    print(\"\\nEmbeddings loaded and saved successfully!\")\n",
    "\n",
    "else:\n",
    "    print(\"\\nInvalid input. Please enter 'yes' or 'no' to proceed.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32cea86f-a288-42b6-8838-8431721fab50",
   "metadata": {},
   "source": [
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>With TDApiClient, a companion package to  teradataml, data scientists can build rich sets of features using Vantage, AND leverage the power of our 3rd partner cloud partners API’s for AI/ML with a seamless, Pythonic experience.</p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Azure provides <a href = 'https://learn.microsoft.com/en-us/azure/ai-services/openai/reference'>Azure OpenAI Rest API</a> Python SDK to interact with their OpenAI offering. We use the <b>TDApiClient</b> to generate embeddings from Azure OpenAI models.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c4e6736-9070-4713-a010-bbb1a7a1f9d0",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>5. Cluster the Complaints</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For our complaint clustering task, we'll be using a sample of the data to cluster the complaints. This approach will allow us to effectively analyze and categorize the complaints without using the entire dataset.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8511855-c7f5-4783-8e5a-5a2b14b79b4f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "KMeans_Model = KMeans(\n",
    "    data = DataFrame('complaints_embeddings').iloc[:, 17:],\n",
    "    id_column = \"complaint_id\",\n",
    "    target_columns = DataFrame('complaints_embeddings').columns[18:],\n",
    "    output_cluster_assignment = True,\n",
    "    num_clusters = 5\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63a55e88-1632-46eb-b86c-3ee3d16edd8a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "print(\"Data information: \\n\", KMeans_Model.model_data.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d5a3743-a51a-46cd-888c-eecd85e3f3ab",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "KMeans_Model.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7cd95cbc-7434-4248-84f8-20260410bd7e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "embeddings_cluster = DataFrame('complaints_embeddings').join(\n",
    "    other = KMeans_Model.result,\n",
    "    how = \"inner\",\n",
    "    on = \"complaint_id=complaint_id\",\n",
    "    lprefix =  \"L_\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94563f0e-6d80-4b73-ac5a-8aa787ddfc9b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# View complaints in cluster 1\n",
    "embeddings_cluster[['td_clusterid_kmeans','complaint_id','consumer_complaint_narrative']] \\\n",
    "                    .loc[embeddings_cluster.td_clusterid_kmeans == 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83a1dec8-8fe4-4afa-833f-9739dd76297e",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'> \n",
    "\n",
    "<p style='font-size:18px;font-family:Arial;color:#00233c'><b>5.1 Visualization of Clusters with Complaints</b></p> \n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233c'>The graph displays the clustering of complaints into distinct groups. Based on the analysis, the data has been divided into 5 optimal clusters, each representing a unique pattern or category of complaints. This clustering approach helps to identify the key areas or types of complaints that are most prevalent, allowing for more targeted investigation and resolution efforts.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6fa92119-3d20-4f89-909c-9a12c96eceb2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "clus = embeddings_cluster.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4f83453-3872-4001-b5b3-ac442984ba0a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from sklearn.manifold import TSNE\n",
    "\n",
    "tsne = TSNE(n_components = 2, random_state = 42)\n",
    "tsne_result = tsne.fit_transform(clus.iloc[:, 19:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ebb8195-da62-47bc-b5bf-5d8868def61a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "tsne_df = pd.DataFrame(tsne_result, columns = ['tsne_1', 'tsne_2'])\n",
    "tsne_df['cluster_id'] = clus['td_clusterid_kmeans']\n",
    "tsne_df['complaint_id'] = clus['consumer_complaint_narrative']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "244cbc6d-6596-4daf-8bea-f8fb4fb08f9f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create a new DataFrame combining t-SNE results with complaint information\n",
    "tsne_complaint_df = pd.DataFrame(tsne_result, columns = ['tsne_1', 'tsne_2'])\n",
    "tsne_complaint_df['cluster_id'] = clus['td_clusterid_kmeans']\n",
    "tsne_complaint_df['complaint_id'] = clus['complaint_id']\n",
    "tsne_complaint_df['complaint'] = clus['consumer_complaint_narrative']\n",
    "\n",
    "# Truncate text for hover data\n",
    "max_chars = 50  # Maximum characters to display\n",
    "tsne_complaint_df['truncted_complaint'] = clus['consumer_complaint_narrative'].apply(\n",
    "    lambda x: x[:max_chars] + '...' if len(x) > max_chars else x\n",
    ")\n",
    "\n",
    "# Plot using Plotly Express\n",
    "fig = px.scatter(\n",
    "    tsne_complaint_df,\n",
    "    x = 'tsne_1',\n",
    "    y = 'tsne_2',\n",
    "    color = 'cluster_id',\n",
    "    hover_data = ['complaint_id', 'truncted_complaint', 'cluster_id']\n",
    ")\n",
    "\n",
    "fig.update_traces(marker=dict(size=15))\n",
    "fig.update_layout(\n",
    "    title = 't-SNE Visualization of Clusters with Complaints',\n",
    "    xaxis_title = 'dimension-1',\n",
    "    yaxis_title = 'dimension-2',\n",
    "    xaxis = dict(tickangle=45),\n",
    "    width = 1000,\n",
    "    height = 800,\n",
    "    hoverlabel = dict(\n",
    "        bgcolor = \"white\",\n",
    "        font_size = 16,\n",
    "        font_family = \"Rockwell\"\n",
    "    ),\n",
    "    autosize = False,\n",
    ")\n",
    "\n",
    "# Customize the hovertemplate\n",
    "fig.update_traces(\n",
    "    hovertemplate = \"<b>Complaint ID:</b> %{customdata[0]}<br>\"\n",
    "                    \"<b>Complaint:</b> %{customdata[1]}<br>\"\n",
    "                    \"<b>Cluster ID:</b> %{customdata[2]}<br>\"\n",
    ")\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8f29be4-9609-4c96-a7ab-e2f4a60ec106",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>6. Cleanup</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9140d843-0e89-4f1a-9018-dbde4e41f389",
   "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": "9da55306-cfe5-4d34-978d-16c236504630",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "tables = ['complaints_embeddings']\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": "73a8ead8-0698-4449-bc11-5a07351e3fb6",
   "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": "dc34cf17-ecc6-441b-a69d-21a34de7205d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_ComplaintAnalysis');\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e697922-8d63-4709-b7b3-0f705a938255",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75e7e6a6-1413-4b18-b800-4899bcdfb31a",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>Dataset:</b>\n",
    "<br>\n",
    "<br>\n",
    "<p style='font-size: 16px; font-family: Arial; color: #00233C;'>The dataset is sourced from <a href='https://www.consumerfinance.gov/data-research/consumer-complaints/'>Consumer Financial Protection Bureau</a></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9586bbb-6521-4988-bc00-40a67b08b695",
   "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 - 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
}