UseCases/Complaints_Analysis_GenAI_Gemini/Sentiment_Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5ce3dd8a-f3e5-40d5-ab4c-d40cc358cf7f",
   "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",
    "       Sentiment Analysis 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": "57054f7e-befe-4029-b4ac-b82019240b1f",
   "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'>Sentiment analysis using <b>Teradata Vantage</b> and the advanced <b>Google Gemini</b> model involves leveraging cutting-edge technologies to extract insights from unstructured data. This process empowers businesses to swiftly identify and address customer concerns, enhancing overall customer satisfaction and loyalty.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Key Features:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Polarity Classification:</b> Identifies specific emotions such as happiness, anger, sadness, and more.</li>\n",
    "    <li><b>Emotion Detection:</b> The system comprehends the nuances of customer feedback, capturing subtle differences in tone and language.</li>\n",
    "    <li><b>Aspect-Based Sentiment Analysis:</b>  Analyzes sentiment towards specific features or aspects of a product or service.</li>\n",
    "    <li><b>Fine-Grained Sentiment Analysis:</b> Provides detailed sentiment analysis at the phrase or clause level.</li>\n",
    "    <li><b>Subjectivity Classification:</b> Distinguishes between objective and subjective text.</li>\n",
    "\n",
    "</ul>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Benefits:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Improved Customer Satisfaction:</b> Enhances customer experience by addressing concerns and improving products.</li>\n",
    "    <li><b>Competitive Advantage:</b> Provides valuable insights to stay ahead of competitors.</li> \n",
    "    <li><b>Objective Insights:</b> Offers unbiased and accurate sentiment analysis.</li>\n",
    "    <li><b>Real-Time Decision Making:</b> Enables swift responses to customer concerns and market trends.</li>\n",
    "    <li><b>Scalability:</b> Handles large volumes of data efficiently.</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233c'>Experience the transformative power of Generative AI in complaints classification.</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>Setup API key for Google Gemini</li>\n",
    "    <li>Complaints Sentiment Analysis</li>\n",
    "    <li>Cleanup</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e457db5-d4df-4e7f-a11a-fcb1416e462d",
   "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": "03eb4587-6632-4b08-a121-c98ea5a8fc48",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install -r requirements.txt --quiet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1fd81e4-0df9-4360-b3ac-72214f135296",
   "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": "dc278740-1511-4423-bf2b-92a5109a47bf",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>1.2 Import the required libraries</b></p>\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": "7f183178-9807-4538-b794-922820346ebc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import timeit\n",
    "from tqdm import tqdm\n",
    "from teradataml import *\n",
    "from sqlalchemy import func\n",
    "import plotly.express as px\n",
    "from wordcloud import WordCloud\n",
    "import matplotlib.pyplot as plt\n",
    "import plotly.subplots as subplots\n",
    "\n",
    "# GenAI libs\n",
    "import google.generativeai as genai\n",
    "from google.generativeai import protos\n",
    "\n",
    "display.max_rows = 5\n",
    "pd.set_option('display.max_colwidth', 50)\n",
    "pd.set_option('display.max_rows', 5)\n",
    "from IPython.display import display, Markdown"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1763fd95-5000-4a2d-8b86-7be261e20847",
   "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": "de5eba56-d38f-4204-b30d-232c7d894eb0",
   "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=Sentiment_Analysis_Bedrock.ipynb;' UPDATE FOR SESSION;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ce81699-6345-4dbf-a3a8-b9402c7b6a98",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8dca7332-48f5-4702-8b56-d8a03fbb87a2",
   "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": "c87c4008-c993-4109-be9d-191a15ded026",
   "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": "d556a860-e27f-432d-a4d1-f4bd903e34fd",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>3. 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": "d0f2a8a6-5a5b-483d-81e7-46682c764bec",
   "metadata": {},
   "outputs": [],
   "source": [
    "GOOGLE_API_KEY = getpass.getpass(prompt = 'Please enter GOOGLE_API_KEY: ')\n",
    "genai.configure(api_key = GOOGLE_API_KEY)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e3b179f-4649-483a-a470-db064266c3b6",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>3.1. Define the Gemini model and Prompt</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following section defines the type of Gemini model used. Here we use <b>gemini-1.5-pro-latest</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6feda0d-d9da-4e57-9fdb-00635c6bdf2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.generativeai.types import HarmCategory, HarmBlockThreshold\n",
    "\n",
    "model = genai.GenerativeModel(\n",
    "    model_name = \"models/gemini-1.5-pro-latest\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4332348c-f45e-4f03-9184-81746d39f566",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>4. Complaints Sentiment Analysis</b>\n",
    "<p style=\"font-size:16px;font-family:Arial;color:#00233C\">We'll analyze the sentiments of a sample of customer complaints data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff462b32-8742-4ac0-a40f-3e201120b3bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = DataFrame(in_schema('DEMO_ComplaintAnalysis', 'Consumer_Complaints'))\n",
    "tdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fee7277b-14ed-489e-8ffd-0a59bf2f0ca6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "pd_df = tdf.sample(20).to_pandas()\n",
    "pd_df['Sentiment'] = \"\"\n",
    "pd_df['Reasoning with Chain of Thought'] = ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d091617-4179-4953-ac50-7f9e966e4c0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sentiment\n",
    "for i in tqdm(range(len(pd_df))):\n",
    "    try:\n",
    "        prompt = f'''Analyze the following customer review:\n",
    "                \"{pd_df['consumer_complaint_narrative'][i]}\"\n",
    "\n",
    "                Classify the sentiment as Positive, Negative or Neutral.\n",
    "\n",
    "                Provide a detailed single-sentence explanation for your classification without using any special formatting (no commas or asterisks).\n",
    "\n",
    "                Your response should follow this format:\n",
    "                Sentiment: [Your classification]\n",
    "                Reasoning: [Your explanation]'''\n",
    "\n",
    "        output = model.generate_content([prompt])\n",
    "        finish_reason = output.candidates[0].finish_reason\n",
    "\n",
    "        if finish_reason ==  protos.Candidate.FinishReason.STOP:\n",
    "            output = output.candidates[0].content.parts[0].text\n",
    "\n",
    "            sentiment = re.search('Sentiment:(.*)', output).group(1)\n",
    "            reasoning = re.search('Reasoning:(.*)', output).group(1)\n",
    "        else:\n",
    "            sentiment = 'Neutral'\n",
    "            reasoning = ''\n",
    "\n",
    "        pd_df['Sentiment'][i] = sentiment\n",
    "        pd_df['Reasoning with Chain of Thought'][i] = reasoning\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f4f878b-c2e3-45a3-b7f4-1e4927f569a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_df['Sentiment'] = pd_df['Sentiment'].apply(lambda x: x.strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf52600c-81d5-4b26-8c61-616b90f78f7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_df[['complaint_id', 'consumer_complaint_narrative', 'Sentiment', 'Reasoning with Chain of Thought']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9162a5c4-a3ee-44c0-be09-5bfaa9b5d827",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now the results can be saved back to Vantage.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1626db2a-1159-4560-8ae3-704abcd9e081",
   "metadata": {},
   "outputs": [],
   "source": [
    "copy_to_sql(df = pd_df, table_name = 'complaints_sentiment', if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5245af62-abf4-4ed5-b4c2-7f06620587a8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sentiment_df = DataFrame('complaints_sentiment')\n",
    "sentiment_df = sentiment_df.assign(date_received = sentiment_df.date_received.cast(type_=DATE))\n",
    "print('Before: ', sentiment_df.shape)\n",
    "sentiment_df = sentiment_df.loc[sentiment_df.Sentiment.isin(['Positive', 'Negative', 'Neutral'])]\n",
    "print('After: ', sentiment_df.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c29ca4fd-3282-4eac-a402-0c72542a5a30",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.1 Consumer Sentiments Prediction vs Occurrences</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233c'>A graph illustrating the relationship between consumer sentiments (positive, negative, neutral) prediction and the number of occurrences. This visual representation helps identify trends, patterns, and areas for improvement, enabling data-driven decision making.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "faa3be08-0b27-4748-a7d8-32023b183761",
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import Counter\n",
    "data = Counter(sentiment_df[['Sentiment']].get_values().flatten())\n",
    "\n",
    "# Convert Counter data to DataFrame\n",
    "df = pd.DataFrame.from_dict(data, orient='index', columns=['Count']).reset_index()\n",
    "\n",
    "# Rename columns\n",
    "df.columns = ['Sentiment', 'Count']\n",
    "\n",
    "# Create bar graph using Plotly Express\n",
    "fig = px.bar(df, x='Sentiment', y='Count', color='Sentiment',\n",
    "             labels={'Count': 'Number of Occurrences', 'Sentiment': 'Sentiment'})\n",
    "\n",
    "# Show the plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1211a149-ec81-4cd3-8423-963952da4d01",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.2 Word Cloud for Negative Consumer Sentiment Prediction</b></p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233c'> Unlock the power of customer feedback with our intuitive word cloud visualization, which provides a comprehensive snapshot of <b>negative consumer complaints sentiment</b>. This innovative tool highlights the most frequently occurring words and pain points in customer feedback, empowering businesses to: </p> <ol style='font-size:16px;font-family:Arial;color:#00233c'> <li>Identify trends and sentiment patterns</li> <li>Pinpoint areas for improvement</li> <li>Make data-driven decisions to enhance customer satisfaction and loyalty</li> </ol> <p style='font-size:16px;font-family:Arial;color:#00233c'> By leveraging this word cloud, businesses can proactively address customer concerns, refine their products and services, and ultimately drive growth through a deeper understanding of their customers' needs and preferences. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afd54125-8159-4484-83c9-cb36f3a3951e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def display_helper(msg):\n",
    "    return display(Markdown(\n",
    "        f\"\"\"<div class=\"alert alert-block alert-info\">\n",
    "        <p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b>\n",
    "        <i>{msg}</i></p>\"\"\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7f3644c-c55d-4864-87ee-57403c86e9b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "neg = sentiment_df[sentiment_df['Sentiment'] == 'Negative'].to_pandas()\n",
    "neg_text = ' '.join(neg['consumer_complaint_narrative'])\n",
    "\n",
    "# Replace 'X' with blank space\n",
    "modified_string = neg_text.replace('X', '')\n",
    "\n",
    "if len(modified_string):\n",
    "    wordcloud = WordCloud(width=800, height=400, background_color='white').generate(modified_string)\n",
    "\n",
    "    # Display the word cloud\n",
    "    plt.imshow(wordcloud, interpolation='bilinear')\n",
    "    plt.tight_layout()\n",
    "    plt.axis(\"off\")\n",
    "    plt.show()\n",
    "else:\n",
    "    display_helper(\"\"\"We included positive, negative, and neutral categories to cover all bases. \n",
    "    But in this sample, it's possible that none of the complaints are actually negative.\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3c9017a-4470-4184-b7c3-334a2dd7ef4d",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.3 Word Cloud for Neutral Consumer Sentiment Prediction</b></p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233c'>Tap into the insights of customer feedback with our intuitive word cloud visualization, which offers a detailed overview of <b>neutral consumer complaints sentiment</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02aede14-b229-4835-a878-aed4bf1fef94",
   "metadata": {},
   "outputs": [],
   "source": [
    "neu = sentiment_df[sentiment_df['Sentiment'] == 'Neutral'].to_pandas()\n",
    "neu_text = ' '.join(neu['consumer_complaint_narrative'])\n",
    "\n",
    "# Replace 'X' with blank space\n",
    "modified_string = neu_text.replace('X', '')\n",
    "\n",
    "if len(modified_string):\n",
    "    wordcloud = WordCloud(width=800, height=400, background_color='white').generate(modified_string)\n",
    "\n",
    "    # Display the word cloud\n",
    "    plt.imshow(wordcloud, interpolation='bilinear')\n",
    "    plt.tight_layout()\n",
    "    plt.axis(\"off\")\n",
    "    plt.show()\n",
    "else:\n",
    "    display_helper(\"\"\"To cover all possible scenarios, we included positive, negative, and neutral categories in our analysis. \n",
    "    However, given that this dataset consists of complaints, it's expected that the model would rarely, if ever, encounter <b>neutral</b> responses.\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d63d3d37-cf05-4497-9d74-a612762fe3e7",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.4 Word Cloud for Positive Consumer Sentiment Prediction</b></p>\n",
    "\n",
    "<p style=\"font-size:16px;font-family:Arial;color:#00233c\">Explore customer feedback insights with our intuitive word cloud visualization, providing a detailed overview of consumer sentiment.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f881a57-4421-436b-9d2b-1103ba6ad005",
   "metadata": {},
   "outputs": [],
   "source": [
    "pos = sentiment_df[sentiment_df['Sentiment'] == 'Positive'].to_pandas()\n",
    "pos_text = ' '.join(pos['consumer_complaint_narrative'])\n",
    "\n",
    "# Replace 'X' with blank space\n",
    "modified_string = pos_text.replace('X', '')\n",
    "\n",
    "if len(modified_string):\n",
    "    wordcloud = WordCloud(width=800, height=400, background_color='white').generate(modified_string)\n",
    "\n",
    "    # Display the word cloud\n",
    "    plt.imshow(wordcloud, interpolation='bilinear')\n",
    "    plt.tight_layout()\n",
    "    plt.axis(\"off\")\n",
    "    plt.show()\n",
    "else:\n",
    "    display_helper(\"\"\"To cover all possible scenarios, we included positive, negative, and neutral categories in our analysis. \n",
    "    However, given that this dataset consists of complaints, it's expected that the model would rarely, \n",
    "    if ever, encounter <b>positive</b> responses.\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f81682d-268c-498a-b7d6-fa3d57698fd8",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.5 Negative Sentiment per Product Over Years</b></p>\n",
    "\n",
    "<p style=\"font-size:16px;font-family:Arial;color:#00233c\">This graph tracks the negative sentiment  associated with different products over time, offering valuable insights into evolving customer perceptions and pain points.</p>\n",
    "\n",
    "<p style=\"font-size:16px;font-family:Arial;color:#00233c\">We will use <b>Vantage in-db</b> function <b>OrdinalEncodingFit</b> which will identifies distinct categorical values from the input data or a user-defined list and generates the distinct categorical values along with the ordinal value for each category.<p?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3159a977-c88b-4ba8-9d83-e91c8a942ed4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "ordinal_fit = OrdinalEncodingFit(data=sentiment_df,\n",
    "                                 target_column=['Sentiment'],\n",
    "                                 approach = 'LIST',\n",
    "                                 categories = ['Negative', 'Neutral', 'Positive']\n",
    "                                )\n",
    "\n",
    "ordinal_fit.result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6da5cebc-9427-4bcf-8304-f8bb38a4c366",
   "metadata": {},
   "outputs": [],
   "source": [
    "out = ColumnTransformer(input_data=sentiment_df[['date_received', 'product', 'Sentiment']], ordinalencoding_fit_data=ordinal_fit.result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48293eb7-505a-4c64-bdb5-4eb73c168322",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "result = out.result\n",
    "result = result.assign(Sentiment = result.Sentiment - 1)\n",
    "result = result.assign(year = func.td_year_of_calendar(result.date_received.expression))\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "068e5e27-c4f4-4e59-9636-8d4805ae0366",
   "metadata": {},
   "outputs": [],
   "source": [
    "viz_neg = result[result['Sentiment'] == -1]\n",
    "\n",
    "viz_senti = viz_neg.select(['product','Sentiment', 'year']).groupby(['product', 'year']).agg(['sum']).to_pandas()\n",
    "\n",
    "if viz_senti.shape[0] > 0:\n",
    "    # Sorting the DataFrame by year for each product\n",
    "    pd_df_sorted = viz_senti.sort_values(by=['product', 'year'])\n",
    "\n",
    "    # Plotting using Plotly\n",
    "    fig = px.line(pd_df_sorted, x='year', y='sum_Sentiment', color='product', markers=True, title='Negative Sentiment per Product Over Years')\n",
    "    fig.update_layout(xaxis_title='Year', yaxis_title='Count', legend_title='Product', width=1000, height=600)\n",
    "\n",
    "    fig.show()\n",
    "else:\n",
    "    display_helper(\"\"\"We included positive, negative, and neutral categories to cover all bases. \n",
    "    But in this sample, it's possible that none of the complaints are actually negative.\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fc49e14-3beb-4206-811e-558026246e50",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.6 Neutral Sentiment per Product Over Years</b></p>\n",
    "\n",
    "<p style=\"font-size:16px;font-family:Arial;color:#00233c\">This graph tracks the neutral sentiment  associated with different products over time, offering valuable insights into evolving customer perceptions and pain points.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "04da72eb-c4c4-4087-9a1f-f47f0dc31e23",
   "metadata": {},
   "outputs": [],
   "source": [
    "viz_neg = result[result['Sentiment'] == 0]\n",
    "\n",
    "viz_senti = viz_neg.select(['product','Sentiment', 'year']).groupby(['product', 'year']).agg(['sum']).to_pandas()\n",
    "\n",
    "if viz_senti.shape[0] > 0:\n",
    "    # Sorting the DataFrame by year for each product\n",
    "    pd_df_sorted = viz_senti.sort_values(by=['product', 'year'])\n",
    "\n",
    "    # Plotting using Plotly\n",
    "    fig = px.line(pd_df_sorted, x='year', y='sum_Sentiment', color='product', markers=True, title='Neutral Sentiment per Product Over Years')\n",
    "    fig.update_layout(xaxis_title='Year', yaxis_title='Count', legend_title='Product', width=1000, height=600)\n",
    "\n",
    "    fig.show()\n",
    "else:\n",
    "    display_helper(\"\"\"To cover all possible scenarios, we included positive, negative, and neutral categories in our analysis. \n",
    "    However, given that this dataset consists of complaints, it's expected that the model would rarely, if ever, encounter <b>neutral</b> responses.\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "389e811a-737d-4953-965b-56b591409aad",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.7 Positive Sentiment per Product Over Years</b></p>\n",
    "\n",
    "<p style=\"font-size:16px;font-family:Arial;color:#00233c\">This graph tracks the positive sentiment  associated with different products over time, offering valuable insights into evolving customer perceptions and pain points.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b218073f-c7b5-4971-ac96-5bd2217e80dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "viz_neg = result[result['Sentiment'] == 1]\n",
    "\n",
    "viz_senti = viz_neg.select(['product','Sentiment', 'year']).groupby(['product', 'year']).agg(['sum']).to_pandas()\n",
    "\n",
    "if viz_senti.shape[0] > 0:\n",
    "    # Sorting the DataFrame by year for each product\n",
    "    pd_df_sorted = viz_senti.sort_values(by=['product', 'year'])\n",
    "\n",
    "    # Plotting using Plotly\n",
    "    fig = px.line(pd_df_sorted, x='year', y='sum_Sentiment', color='product', markers=True, title='Positive Sentiment per Product Over Years')\n",
    "    fig.update_layout(xaxis_title='Year', yaxis_title='Count', legend_title='Product', width=1000, height=600)\n",
    "\n",
    "    fig.show()\n",
    "else:\n",
    "    display_helper(\"\"\"To cover all possible scenarios, we included positive, negative, and neutral categories in our analysis. \n",
    "    However, given that this dataset consists of complaints, it's expected that the model would rarely, if ever, encounter <b>positive</b> responses.\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a44bc090-a387-41b6-90a5-638a16dc3d4f",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>5. Cleanup</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b6ba28d-07d2-4322-9f59-e81855e1389c",
   "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": "e572167a-a4db-42e5-8881-578dfbaa0c09",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_ComplaintAnalysis');\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7e82715-0855-4d59-990c-481d3a9d3f1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebbd137a-7bfc-49fa-a18e-ca34ba68919d",
   "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": "19bdc0df-dc13-4a13-bf73-c7039a54c3ba",
   "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
}