UseCases/Complaints_Analysis_Azure_OpenAI/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 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": "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>Azure OpenAI</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>Configuring Azure OpenAI</li>\n",
    "    <li>Complaints Sentiment Analysis</li>\n",
    "    <li>Cleanup</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa85be84-f59c-4bd9-95b5-2d0f805d33d5",
   "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": "03eb4587-6632-4b08-a121-c98ea5a8fc48",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install -r requirements.txt --upgrade --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>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "035d9ed5-5fde-431e-bce4-f358f8223a1b",
   "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": "70311ba4-fad6-42b7-b0bc-bf6f45b4d73b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Data manipulation and analysis\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# Visualization\n",
    "import plotly.express as px\n",
    "import matplotlib.pyplot as plt\n",
    "from wordcloud import WordCloud\n",
    "\n",
    "# Progress bar\n",
    "from tqdm import tqdm\n",
    "\n",
    "# Machine learning and other utilities from Teradata\n",
    "from teradataml import *\n",
    "from sqlalchemy import func\n",
    "\n",
    "# Requests\n",
    "import requests\n",
    "\n",
    "# Display settings\n",
    "display.max_rows = 5\n",
    "pd.set_option('display.max_colwidth', None)"
   ]
  },
  {
   "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: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": {
    "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=Sentiment_Analysis_OpenAI.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",
    "<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": "c87c4008-c993-4109-be9d-191a15ded026",
   "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": "c4aedced-59c4-4887-9ff1-25cfe56e0403",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style='font-size:20px;font-family:Arial;color:#00233C'>3. Configuring Azure OpenAI</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": "4c768569-e23c-4601-a673-85219da5a925",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Prompt user for Azure OpenAI endpoint securely\n",
    "os.environ[\"ENDPOINT\"] = getpass.getpass(prompt=\"\\nPlease enter your Azure OpenAI endpoint(gpt-4o-mini): \")\n",
    "# Prompt user for Azure OpenAI API key securely\n",
    "os.environ[\"API_KEY\"] = getpass.getpass(prompt=\"\\nPlease enter your Azure OpenAI API key(gpt-4o-mini): \")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5fed3ec9-5527-4a9c-af43-597532854827",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:18px;font-family:Arial;color:#00233C'>3.1 Initialize the Azure OpenAI API request</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9ee8d70-231f-4fff-b416-6588acd6ac88",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "headers = {\n",
    "    \"Content-Type\": \"application/json\",\n",
    "    \"api-key\": os.environ[\"API_KEY\"],\n",
    "}\n",
    "\n",
    "def get_payload(complaint):\n",
    "    \n",
    "    prompt = f'''\n",
    "        User prompt:\n",
    "        The following is text from a review:\n",
    "\n",
    "        “{complaint}”\n",
    "\n",
    "        Categorize the review as one of the following: Positive, Negative, Neutral\n",
    "\n",
    "        My output comes in the format:\n",
    "        Sentiment:\n",
    "        Reasoning:\n",
    "    '''\n",
    "    \n",
    "    # Payload for the request\n",
    "    payload = {\n",
    "      \"messages\": [\n",
    "        {\n",
    "          \"role\": \"system\",\n",
    "          \"content\": [\n",
    "            {\n",
    "              \"type\": \"text\",\n",
    "              \"text\": \"You are an assistant that analyzes sentiment of a text and gives reasoning for the categorization as well.\\n\"\n",
    "            }\n",
    "          ]\n",
    "        },\n",
    "        {\n",
    "          \"role\": \"user\",\n",
    "          \"content\": [\n",
    "            {\n",
    "              \"type\": \"text\",\n",
    "              \"text\": prompt\n",
    "            }\n",
    "          ]\n",
    "        }\n",
    "      ],\n",
    "      \"temperature\": 0.7,\n",
    "      \"top_p\": 1,\n",
    "      \"max_tokens\": 512\n",
    "    }\n",
    "\n",
    "    return(payload)"
   ]
  },
  {
   "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": [
    "df = tdf.to_pandas(num_rows = 20)\n",
    "df['Sentiment'] = \"\"\n",
    "df['Reasoning with Chain of Thought'] = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d091617-4179-4953-ac50-7f9e966e4c0d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "for i in tqdm(range(len(df))):\n",
    "    # Send request\n",
    "    try:\n",
    "        response = requests.post(os.environ[\"ENDPOINT\"], headers = headers, json = get_payload(df['consumer_complaint_narrative'][i]))\n",
    "        response.raise_for_status()\n",
    "    except requests.RequestException as e:\n",
    "        raise SystemExit(f\"Failed to make the request. Error: {e}\")\n",
    "\n",
    "    output = response.json()['choices'][0]['message']['content']\n",
    "\n",
    "    try:\n",
    "        category = re.search('Sentiment:(.*)', output).group(1)\n",
    "        reasoning = re.search('Reasoning:(.*)', output).group(1)\n",
    "        df['Sentiment'][i] = category.strip()\n",
    "        df['Reasoning with Chain of Thought'][i] = reasoning.strip()\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf52600c-81d5-4b26-8c61-616b90f78f7b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df[['complaint_id', 'consumer_complaint_narrative', 'Sentiment', 'Reasoning with Chain of Thought']].head(5)"
   ]
  },
  {
   "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": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "copy_to_sql(df = 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": "31955976-23c1-4897-9140-77768fd4400c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from IPython.display import display, Markdown\n",
    "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": "faa3be08-0b27-4748-a7d8-32023b183761",
   "metadata": {
    "tags": []
   },
   "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": "d7f3644c-c55d-4864-87ee-57403c86e9b7",
   "metadata": {
    "tags": []
   },
   "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) > 0:\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. 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": {
    "tags": []
   },
   "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) > 0:\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. However, given that this dataset consists of complaints, it's expected that the model would rarely, if ever, encounter positive or neutral 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": {
    "tags": []
   },
   "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) > 0:\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. However, given that this dataset consists of complaints, it's expected that the model would rarely, if ever, encounter positive or neutral 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(\n",
    "    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": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "out = ColumnTransformer(\n",
    "    input_data = sentiment_df[['date_received', 'product', 'Sentiment']],\n",
    "    ordinalencoding_fit_data = ordinal_fit.result\n",
    ")"
   ]
  },
  {
   "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": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "viz_neg = result[result['Sentiment'] == -1]\n",
    "\n",
    "if viz_neg.shape[0] > 0:\n",
    "\n",
    "    viz_senti = viz_neg.select(['product','Sentiment', 'year']).groupby(['product', 'year']).agg(['sum']).to_pandas()\n",
    "\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. 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": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "viz_neu = result[result['Sentiment'] == 0]\n",
    "\n",
    "if viz_neu.shape[0] > 0:\n",
    "    viz_senti = viz_neu.select(['product','Sentiment', 'year']).groupby(['product', 'year']).agg(['sum']).to_pandas()\n",
    "\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. However, given that this dataset consists of complaints, it's expected that the model would rarely, if ever, encounter positive or neutral 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": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "viz_pos = result[result['Sentiment'] == 1]\n",
    "\n",
    "if viz_pos.shape[0] > 0:\n",
    "    viz_senti = viz_pos.select(['product','Sentiment', 'year']).groupby(['product', 'year']).agg(['sum']).to_pandas()\n",
    "\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. However, given that this dataset consists of complaints, it's expected that the model would rarely, if ever, encounter positive or neutral 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": "daefb5ac-30ad-4eec-aa1d-ea5d1f232626",
   "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": "bc7d7637-276b-4c0e-a79c-82aaa33e9a51",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['complaints_sentiment']\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": "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
}