UseCases/Complaints_Analysis_GenAI_Gemini/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 Google Gemini\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>Google Gemini's Generative AI 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>Google Gemini's Generative AI 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>Setup API key for Google Gemini</li>\n",
    "    <li>Cluster the Complaints</li>\n",
    "    <li>Cleanup</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26a0c08c-7782-40f2-af3e-27d1adc952fa",
   "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",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>1.1 Install the required libraries</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e97af3c-0907-4a05-adf1-40b4ea1cded0",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install -r requirements.txt --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>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13c3807b-2c66-4726-9182-5d15dc3e2051",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\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": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import timeit\n",
    "import tqdm\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import plotly.express as px\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "from tqdm.notebook import *\n",
    "tqdm_notebook.pandas()\n",
    "\n",
    "# plotting packages\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "sns.set(style = \"whitegrid\")\n",
    "\n",
    "# teradata lib\n",
    "from teradataml import *\n",
    "import teradataml\n",
    "from teradataml import KMeans\n",
    "from teradataml.analytics.valib import *\n",
    "from teradataml import configure\n",
    "from sqlalchemy import func\n",
    "configure.val_install_location = \"val\"\n",
    "configure.byom_install_location = \"byom\"\n",
    "\n",
    "\n",
    "# genai\n",
    "import getpass\n",
    "import os\n",
    "import google.generativeai as genai\n",
    "\n",
    "# Suppress warnings\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\", category=DeprecationWarning)\n",
    "display.max_rows = 5\n",
    "display.max_rows = 5\n",
    "pd.set_option('display.max_colwidth', 50)\n",
    "pd.set_option('display.max_rows', 5)\n",
    "\n",
    "display.print_sqlmr_query = False\n",
    "display.suppress_vantage_runtime_warnings = True\n",
    "from IPython.display import display, Markdown"
   ]
  },
  {
   "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:18px;font-family:Arial;color:#00233c'><b>2.1 Connect to Vantage</b></p>\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": {},
   "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",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>2.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>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "151fde7f-77b5-4c50-87b7-b182a07d19c0",
   "metadata": {},
   "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>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, We will load the data and explore it for further steps/</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09c6a41f-08af-4c99-8b02-ef1e9e6759e4",
   "metadata": {},
   "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": {},
   "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(pd_df_sorted, x='year', y='count_complaint_id', color='product', markers=True, title='Count of Product Complaints Over Years')\n",
    "fig.update_layout(xaxis_title='Year', yaxis_title='Count', legend_title='Product', width=1200, height=600)\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(grp_gen.sort_values(by = 'complaint_month'), x='month', y='count_complaint_id',\n",
    "             labels={'count_complaint_id': 'Number of Complaints', 'month': 'Complaint Month'},\n",
    "             title='Number of Complaints by Month')\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": {},
   "outputs": [],
   "source": [
    "# Assuming df is your DataFrame and sns is seaborn\n",
    "\n",
    "grp_gen = df.select(['product','complaint_id']).groupby(['product']).agg(['count']).to_pandas()\n",
    "\n",
    "fig = px.bar(grp_gen, x='product', y='count_complaint_id',\n",
    "             labels={'count_complaint_id': 'Number of Complaints', 'product': 'Product'},\n",
    "             title='Number of Complaints by Product')\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": {},
   "outputs": [],
   "source": [
    "# Assuming df is your DataFrame and sns is seaborn\n",
    "\n",
    "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(grp_gen, x='issue', y='count_complaint_id',\n",
    "             labels={'count_complaint_id': 'Number of Complaints', 'issue': 'Issue'},\n",
    "             title='Number of Complaints by Issue(Top 10)')\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": [
    "# Assuming df is your DataFrame and sns is seaborn\n",
    "\n",
    "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(grp_gen, x='sub_issue', y='count_complaint_id',\n",
    "             labels={'count_complaint_id': 'Number of Complaints', 'sub_issue': 'Sub-Issue'},\n",
    "             title='Number of Complaints by Sub-Issue(Top 10)')\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({product: i for i, product in enumerate(grp_gen['submitted_via'])})\n",
    "\n",
    "fig = px.bar(grp_gen, x='submitted_via', y='count_complaint_id',\n",
    "             labels={'count_complaint_id': 'Number of Complaints', 'submitted_via': 'Submitted Via'},\n",
    "             title='Number of Complaints by Channel')\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. Setup API key for Google Gemini</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Please enter the Google API Key, if you don't have one, please get it from <a href = 'https://ai.google.dev/gemini-api/docs/api-key'>here</a></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b630738-a5b1-4815-afcc-b06444ffc2e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "GOOGLE_AI_API_KEY = getpass.getpass(prompt = 'Please enter GOOGLE_AI_API_KEY: ')\n",
    "genai.configure(api_key = GOOGLE_AI_API_KEY)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f279ee0c-6f51-4f8c-854d-b61d01278dcc",
   "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": "markdown",
   "id": "4833e1db-387b-49e8-bd92-1ae9a794ee92",
   "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 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": "6970948e-0a65-4164-afbe-be4585eacd94",
   "metadata": {},
   "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",
    "    display(Markdown(\"\"\"<br><div class=\"alert alert-block alert-warning\">\n",
    "            <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note: The embedding generation step is estimated to take approximately 5 minutes to complete. Please be patient.</b></i></p>\n",
    "        </div>\"\"\"))\n",
    "    \n",
    "    display(Markdown(\"\"\"<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here we have used <b>text-embedding-004</b> model google gemini offerings. \n",
    "please click here to learn more about this embeddings model <a href=\"https://ai.google.dev/gemini-api/docs/embeddings\">embeddings</a></p>\"\"\"))\n",
    "    \n",
    "    from google.api_core import retry\n",
    "    def make_embed_text_fn(model):\n",
    "\n",
    "        @retry.Retry(timeout=300.0)\n",
    "        def embed_fn(text: str) -> list[float]:\n",
    "            # Set the task_type to CLUSTERING.\n",
    "            embedding = genai.embed_content(model=model,\n",
    "                                            content=text,\n",
    "                                            task_type=\"clustering\")\n",
    "            return embedding[\"embedding\"]\n",
    "\n",
    "        return embed_fn\n",
    "\n",
    "    def create_embeddings(df):\n",
    "        model = 'models/text-embedding-004'\n",
    "        df['Embeddings'] = df['consumer_complaint_narrative'].progress_apply(make_embed_text_fn(model))\n",
    "\n",
    "        # Generate all the embeddings columns from the \"embeddings\" column.\n",
    "        for i in range(len(df['Embeddings'][0])):\n",
    "            df[\"embeddings_{}\".format(i)] = df[\"Embeddings\"].apply(lambda x: x[i])\n",
    "\n",
    "        # drop embedding \n",
    "        df = df.drop(\"Embeddings\", axis=1)\n",
    "        return df\n",
    "\n",
    "    # API request for embeddings\n",
    "    embedding_df = create_embeddings(df.to_pandas(num_rows=10))\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",
    "    \n",
    "    display(Markdown(\"\"\"<br><p style = 'font-size:16px;font-family:Arial;color:#00233C'>The code above first reads the data from the files. The files contain information about the complaints embeddings. The code then loads the data into a permanent table in SQL. Once the data is loaded, we will use the Vantage in-database function <code>KMeans</code> to find the clusters from the embeddings. The data contains complaints embeddings, which are lists of numerical values, or vectors.</p>\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>The embeddings file contains 1000 records, each with 1536 numerical features. This means that the file is quite large and it may take some time to load it into SQL.</p>\n",
    "    <div class=\"alert alert-block alert-info\" id=\"no-azure\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note</b>: Please be patient. The code above is loading data from files and copying it to SQL. This process may take 10-15 seconds.</i></p>\n",
    "</div>\"\"\"))\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": "code",
   "execution_count": null,
   "id": "7533ed89-6523-407d-9ca0-ce7c47a2e878",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_cols = DataFrame('complaints_embeddings').iloc[:, 17].columns + DataFrame('complaints_embeddings').iloc[:, 19:].columns\n",
    "KMeans_Model = KMeans(\n",
    "    data = DataFrame('complaints_embeddings')[data_cols],\n",
    "    id_column = \"complaint_id\",\n",
    "    target_columns = DataFrame('complaints_embeddings').columns[21:],\n",
    "    output_cluster_assignment = True,\n",
    "    num_clusters = 5\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ec9b387-929f-4fe6-a339-787149fda058",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Data information: \\n\", KMeans_Model.model_data.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de5dc3b2-4131-4b8c-9ecc-2d4d9bc29707",
   "metadata": {},
   "outputs": [],
   "source": [
    "KMeans_Model.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f7b8090-3484-4737-9e09-feb5987ff68c",
   "metadata": {},
   "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": "ee2eebe1-91bb-4d93-9e0d-054e21ba2a5a",
   "metadata": {},
   "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": {},
   "outputs": [],
   "source": [
    "# emb = DataFrame('kmeans_features').to_pandas()\n",
    "clus = embeddings_cluster.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4f83453-3872-4001-b5b3-ac442984ba0a",
   "metadata": {},
   "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[:, 20:-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ebb8195-da62-47bc-b5bf-5d8868def61a",
   "metadata": {},
   "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": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import plotly.express as px\n",
    "\n",
    "# Assuming you have already computed tsne_df as per the previous example\n",
    "\n",
    "# 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(lambda x: x[:max_chars] + '...' if len(x) > max_chars else x)\n",
    "\n",
    "# Plot using Plotly Express\n",
    "fig = px.scatter(tsne_complaint_df, x='tsne_1', y='tsne_2', color='cluster_id',\n",
    "                 hover_data=['complaint_id', 'truncted_complaint', 'cluster_id'])\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(hovertemplate=\"<b>Complaint ID:</b> %{customdata[0]}<br>\"\n",
    "                                 \"<b>Complaint:</b> %{customdata[1]}<br>\"\n",
    "                                 \"<b>Cluster ID:</b> %{customdata[2]}<br><extra></extra>\")\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": "b3416c12-d50d-4f30-9e65-39a18cd9e8c4",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>6.1 Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We need to clean up our work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f36eb92-0950-4911-8ca8-6612a0bd8bb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "for table in db_list_tables()['TableName'].tolist():\n",
    "    try:\n",
    "        db_drop_table(table_name=table, schema_name=\"demo_user\")\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73a8ead8-0698-4449-bc11-5a07351e3fb6",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'> <b>6.2 Databases and Tables </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the following code to clean up tables and databases created for this demonstration.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "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
}