UseCases/Chat_Powered_Product_Recommendation/Chat_powered_product_recommendation_based_on_search_output.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "21d484b2-fd8a-4e18-a341-715cac5ab0ea",
   "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",
    "       Chat Powered Product Recommendation based on chatbot output\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": "b4cb4a7a-f812-41ba-a561-3ac22bbacbe7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>This is a Beta Release of this notebook!</b></p>   \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>   \n",
    "This should be considered a Beta Release.  Every effort has been made to ensure it's functionality and completeness.  If you discover any issue with any part of this notebook, your suggestions will be reviewed and, if approved, merged in the next round of notebook commits.   \n",
    "<br>   \n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>How to submit a Review of this Notebook</b></p>   \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>   \n",
    "First, please read through and execute the notebook.   \n",
    "<br> \n",
    "If you think the notebook is fine as it is, great.  Please send us a quick email and let us know. When the notebook is taken out of beta, this paragraph will be removed and the Beta designation the index page will be removed.   \n",
    "<br> \n",
    "If you find something that you question or you think needs to be changed or if you think it's great as it is:   \n",
    "</p>       \n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>   \n",
    "  <li>Send us an email: <a href=\"mailto:ClearScapeAnalyticsNotebookReviews@Teradata.com?subject=Beta Notebook Review\">Click here.</a></li>   \n",
    "  <li>Please include the name of the notebook in email.</li>   \n",
    "  <li>Include a screen shot or a clear description of the section in the notebook you would like us to look at.</li>   \n",
    "  <li>Paste the screen shot or enter the description of the section into the email.</li>   \n",
    "  <li>Describe how you would change it.</li>   \n",
    "  <li>Send It!</li>   \n",
    "</ol>   \n",
    "</p>   \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If we have any follow-up questions, we'll reach out to you at the return email address. </p>   \n",
    "<hr style=\"height:4px;border:none;background-color:#00233C;\"> "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36a6596b-33d1-4883-b9fe-6e205584d45c",
   "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'>The combination of <b>Large Language Models</b> and <b>Vantage in-DB functions</b> assists us in providing product recommendations based on our customer's behavior.</p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>In this demo, we will build a product recommendation system using <b>AWS Bedrock</b> as our LLM, <b>GIST Embedding</b> from HuggingFace, and <b>Vantage in-DB functions</b> like <b>KMeans</b>, <b>VectorDistance</b>, etc. Recommendation systems are a type of information filtering system that seeks to predict the rating or preference that we would give to an item. They are often used on e-commerce websites to recommend products based on our past purchase history, browsing behavior, and other factors. In this demo, we use product-to-product recommendations based on embedding distances. The <b>VectorDistance</b> function will return the closest products from the databases as recommendations.</p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>To demonstrate these capabilities, we've developed a recipe assistant chatbot aimed at delivering a seamless and delightful cooking experience for all users. Let us guide you through the inner workings of our system, which tailors personalized and delectable recipes, along with ingredient recommendations that suit your tastes.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfb1a589-6d29-40f3-b4cc-e9f3dd1994dc",
   "metadata": {},
   "source": [
    "<center><img src=\"images/header_recipe2.jpg\" alt=\"kitchen recipe\" width=800 height=800/></center>\n",
    "\n",
    "<center>image source: <a href=\"https://unsplash.com/photos/fruit-salad-on-gray-bowls-HlNcigvUi4Q\">unsplash.com</a></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17e34068-ec3e-4c05-8131-08379928c3ce",
   "metadata": {},
   "source": [
    "<br>\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Imagine this: We’re in the kitchen, eager to prepare a meal but unsure of what to make. We open up our chatbot and ask, <b>Can you suggest a recipe for dinner tonight?</b> We go to work, using advanced Language Modeling (LLM) technology to analyze our query and generate a list of recipes that match our request.</p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Once the suggested recipes are ready, our bot presents us with a recipe including ingredient quantities, cooking instructions, and a checklist of ingredients required to make each one. We can easily mark which ones we already have at home and which ones we still need to purchase. This not only helps us plan our grocery list but also keeps things organized as we cook.</p>\n",
    " \n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Our system meticulously analyzes our chosen ingredients, determining the specific ingredient clusters they belong to based on their proximity in the embedding space. This intricate process ensures that we suggest not only compatible but also enhancing ingredients for our dish, improving its flavor and nutritional worth.</p>\n",
    " \n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>To explain this further, when we select items, our system first identifies which ingredient clusters they fall under. Subsequently, it filters out related embeddings from the larger dataset based on these clusters. Lastly, it calculates the vector distance between our chosen items and each remaining embedding within that subset to generate accurate and complementary recommendations. In addition to this, we are applying customer behavior analysis, so the system will suggest products based on transaction history.</p>\n",
    " \n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>These suggestions help expand our cooking horizons while keeping things simple and convenient for us in the kitchen! </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52163a22-5b07-4d79-a4e7-9354d2def001",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We illustrate the architecture of this demo in the following diagram.</p>\n",
    "\n",
    "<center><img src=\"images/chat_powered_v6.png\" alt=\"Product_search_architecture\"  width=1000 height=800/></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56bb6589-347e-45cc-ab6f-471e430575c0",
   "metadata": {},
   "source": [
    "<br>\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>From the above architecture diagram, we can divide this entire demo into 3 steps.:</p>\n",
    "\n",
    "<ol style='font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Batch LLM Processes:</b> In this stage, we're creating embeddings from product metadata using the GIST  embedding model and saving them to Vantage for later analysis. Then, we utilize Vantage's in-DB function, specifically <b>KMeans</b>, to construct clusters of similar products.</li> \n",
    "    <li><b>Recipe Assistant Chatbot:</b> During this step, you can request a recipe based on the ingredients you have on hand. Chatbot will then provide you with a recipe including ingredient quantities and cooking instructions. The next step is for you to select the ingredient that you need to complete the recipe by selecting the options provided by chatbot.</li>\n",
    "    <li><b>Product Recommendtion:</b> In the final step, we will take your selection, generate its embeddings, and calculate the vector distance using the Vantage in-DB function <b>VectorDistance</b>. This function will return the closest products from product clusters. Additionally, we will apply customer behavior analysis to recommend the most suitable product to you.</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8aa766ed-f02a-41e9-92cd-1a0228352a4a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Before going any farther, let's get a better understanding of Cosine similarity(distance measure method) and Embeddings</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6a68893-8b18-4015-b0a6-1840a6a39230",
   "metadata": {},
   "source": [
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li> <b>Cosine similarity:</b></li></ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> &emsp;  &emsp; In natural language processing (NLP), a vector is a way of representing a word or phrase as a set of numbers. These numbers represent the meaning of the word or phrase in a way that can be understood by computers.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Cosine distance is a way of <b>measuring the similarity between two vectors</b>. It works by calculating the cosine of the angle between the two vectors. The cosine of an angle is a number between -1 and 1, where 0 means that the vectors are perpendicular, 1 means that they are pointing in the same direction and -1 means that they are pointing in the opposite directions</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>So, if you have two vectors that are very similar, the cosine of the angle between them will be close to 1. And if you have two vectors that are very different, the cosine of the angle between them will be close to 0.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Imagine you have a bunch of products, and you want to know how similar they are to each other. You could represent each product as a vector of numbers, where each number represents a different feature of the product. For example, you could have a vector for <b>cheese</b> that looks like this: <b>[0.6, -0.2, 0.8, 0.9, -0.1, -0.7]</b> Once you have represented each product as a vector, you can use cosine similarity to measure how similar they are.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example, the <b>The cosine of an angle would be close to 1 </b> between <b>cheese</b> and <b>butter, </b> because they have many similar features and they both are dairy products. However, the <b>The cosine of an angle would be close to 0 or less than 0</b> between <b>cheese and eggs</b>, because they are not as similar.</p>\n",
    "\n",
    "<center><img src=\"images/cosine.png\" alt=\"cosine\" width=1000 height=800/></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53aa706c-8370-4b0d-9c18-e89cc26fd3b0",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li> <b>Embeddings:</b></li></ul>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> &emsp;  &emsp; Embeddings are the A.I-native way to represent any kind of data, making them the perfect fit for working with all kinds of A.I-powered tools and algorithms. They can represent text, images, and soon audio and video. There are many options for creating embeddings, whether locally using an installed library, or by calling an API.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Imagine you have a bunch of words, and you want to find a way to represent them in a way that captures their meaning. One way to do this is to create a word embedding. A word embedding is a vector of numbers that represents the meaning of a word. The numbers in the vector are chosen so that words that are similar in meaning have similar vectors.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example, the word \"cheese\", \"butter\", \"chocolate\" and \"sauce\" might have a vector that looks like below:</p>\n",
    "\n",
    "<center><img src=\"images/word_embeddings.png\" alt=\"word_embeddings\"  width=1000 height=800/></center>\n",
    "\n",
    "<br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The numbers in this vector don't have any special meaning by themselves. They just represent the way that the word \"cheese\" is related to other words in the vocabulary.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can use word embeddings to find the similarity between words. For example, we can calculate the cosine similarity between the vector for \"cheese\" and the vector for \"butter\". The cosine similarity is a measure of how similar two vectors are, and it ranges from 0 to 1. A cosine similarity of 1 means that the two vectors are perfectly aligned, and a cosine similarity of 0 means that the two vectors are completely unrelated.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this case, the cosine similarity between the vector for \"cheese\" and the vector for \"butter\" would be very high. This is because the words \"cheese\" and \"butter\" are very similar in meaning. They are both foods that are made from milk, and they are both often used in cooking.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can also use word embeddings to find related words. For example, we can find all of the words that are similar in meaning to \"cheese\". This would include words like \"milk\", \"cream\", \"yogurt\", and \"feta\".</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Word embeddings are a powerful tool for natural language processing. They can be used for a variety of tasks, such as sentiment analysis, machine translation, and question answering.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Above is a visual representation of how word embeddings work</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Imagine a bunch of points in a high-dimensional space. Each point represents a word, and the position of the point in space represents the meaning of the word. Words that are similar in meaning will be close together in space, and words that are different in meaning will be far apart.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, imagine that we take a slice through this high-dimensional space. This slice will be a two-dimensional space, and the points in the two-dimensional space will represent the word embeddings. The distance between two points in the two-dimensional space will be a measure of the similarity between the two words.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this way, word embeddings can be used to represent the meaning of words in a way that is both compact and informative.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9ddd5cb-dbc3-4c9f-bb70-7e2ae7aa2c01",
   "metadata": {},
   "source": [
    "<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>Generate the embeddings</li>\n",
    "    <li>Load the existing embeddings to DB</li>\n",
    "    <li>Calculate the K-Mean Clusters using Teradata Vantage in-DB function</li>\n",
    "    <li>Setup LLM</li>\n",
    "    <li>Launch the Chatbot</li>\n",
    "    <li>Calculate the VectorDistance using Teradata Vantage in-DB function</li>\n",
    "    <li>Display the recommended products for the users</li>\n",
    "    <li>Cleanup</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f31aa4b-2c4d-4217-9961-4aa732c5bf7c",
   "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",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>1.1 Install the required libraries</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1534bc96-0f28-4c96-80a9-86cc814af478",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b>The installation of the required libraries will take approximately <b>4 to 5 minutes</b> for the first-time installation. However, if the libraries are already installed, the execution will complete within 5 seconds.</i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91ed909b-4411-4ae4-810f-313bf740c1db",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.environ[\"TOKENIZERS_PARALLELISM\"] = \"false\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3ff3f79-c2d1-402a-9a15-c547d888228d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install --upgrade -r requirements.txt --quiet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31ba04f9-32f6-461c-b951-badc91513178",
   "metadata": {},
   "source": [
    "<br>\n",
    "<br>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <i>The above statements will install the required libraries to run this demo. To gain access to installed libraries after running this, restart the kernel.</i></p>\n",
    "    \n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b> The above statements may need to be uncommented if you run the notebooks on a platform other than ClearScape Analytics Experience that does not have the libraries installed. If you uncomment those installs, be sure to restart the kernel after executing those lines to bring the installed libraries into memory. The simplest way to restart the Kernel is by typing zero zero: <b>0 0</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d122293f-cd07-4b86-9285-0ce04744cb9d",
   "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": "812fe319-3920-440f-bcb7-e955170766d2",
   "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",
    "# 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",
    "configure.val_install_location = \"val\"\n",
    "configure.byom_install_location = \"byom\"\n",
    "\n",
    "# helper functions\n",
    "from utils.sql_helper_func import *\n",
    "from utils.bedorck_helper_func import *\n",
    "from utils.opensource_helper_func import *\n",
    "\n",
    "# genai\n",
    "import getpass\n",
    "import os\n",
    "from langchain.prompts import ChatPromptTemplate\n",
    "from langchain.prompts import PromptTemplate\n",
    "from langchain.evaluation import load_evaluator\n",
    "from langchain.tools import tool\n",
    "from langchain.agents import AgentType\n",
    "from langchain.prompts import ChatPromptTemplate\n",
    "from langchain.tools.render import format_tool_to_openai_function\n",
    "from langchain.agents.output_parsers import OpenAIFunctionsAgentOutputParser\n",
    "from langchain.prompts import MessagesPlaceholder\n",
    "from langchain.schema.runnable import RunnablePassthrough\n",
    "from langchain.agents.format_scratchpad import format_to_openai_functions\n",
    "from langchain.agents import create_sql_agent\n",
    "from langchain.agents.agent_toolkits import SQLDatabaseToolkit\n",
    "from langchain.sql_database import SQLDatabase\n",
    "from langchain.llms.openai import OpenAI\n",
    "from langchain.agents import AgentExecutor\n",
    "from langchain.chat_models import ChatOpenAI\n",
    "from langchain.tools import tool\n",
    "import boto3\n",
    "from langchain_community.embeddings import BedrockEmbeddings\n",
    "from langchain.llms.bedrock import Bedrock\n",
    "\n",
    "# markdown\n",
    "from IPython.display import display, Markdown\n",
    "\n",
    "# Suppress warnings\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\", category=DeprecationWarning)\n",
    "display.max_rows = 5\n",
    "\n",
    "display.print_sqlmr_query = False\n",
    "display.suppress_vantage_runtime_warnings = True"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95d962be-1952-4b6c-9512-07a3a6ffbb77",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i>The code above will download the necessary models to generate the embeddings required to run this demo. The initial download may take approximately 50-60 seconds minutes if you are running this demo for the first time in this environment. However, subsequent runs will be much faster since the models will already be available locally.</i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6e0c1ee-451d-4e6e-95dd-c8571cef9f1b",
   "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>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab749c23-3c8c-43c4-90df-d2c435a3b33a",
   "metadata": {},
   "source": [
    "<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": "525f2af9-4af2-498c-ae00-4c0e1c96f0fe",
   "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= Chat_powered_product_recommendation_based_on_search_output.ipynb;' UPDATE FOR SESSION;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33d529a9-f743-4bfd-bf95-24cdb00225ab",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdf4e45f-3f13-41b1-9e4e-2c8e0a61ebd3",
   "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.3 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": "5581b789-ed6b-4b68-a03c-ca3aad854e6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_Grocery_Data_cloud');\"        # Takes 1 minute\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_Grocery_Data_local');\"        # Takes 2 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc348393-5b07-4f58-b622-bbd4657a6716",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will be adding the \"Customer Transaction\" Tables into the database for customer behaviour analysis.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c06d9faf",
   "metadata": {},
   "outputs": [],
   "source": [
    "files_dict = {'customer_transactions': 'transaction_id'}\n",
    "for file in files_dict:\n",
    "    print(file, '|', files_dict[file])\n",
    "    df = pd.read_csv(os.path.join('./data/', f'{file}.csv'))\n",
    "    print('file: ', file)\n",
    "    print(df.shape)\n",
    "    copy_to_sql(df, table_name=file, primary_index=files_dict[file], if_exists='replace')\n",
    "    print('--'*50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1b46836-bc67-4711-b9b3-21031adcf757",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – We should execute the below step only if we want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc78ebbd-0ef9-4474-8677-3ad35fdb8d3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59e18f3d-a27c-4712-9c7a-5b0d0322993a",
   "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'>Product recommendation systems are a type of recommender system that suggests products to users based on the recipe what they asked for in the search box and their previous order history. To recommend products to users, we will use GIST Embeddings from HuggingFace and Vantage in db_function.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The data for this demo comes from the products table of Instacart. There are also a few other tables, such as orders, aisles, departments, and order_products_prior. However, for this demo, we will only use the products table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The products table contains information about all of the products that are available on Instacart. This includes the product id, product name, etc. The table also includes the product's department and aisle, which can be used to group products together.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The other tables in the Instacart dataset contain additional information about orders, aisles, departments, and product purchases. However, for this demo, we will only focus on the products table.<p/>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Each row is a snapshot of data taken from the products table, Below are the list of columns in the product table:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> \n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>product_id</li>\n",
    "    <li>product_name</li>\n",
    "    <li>aisle_id</li>\n",
    "   <li>department_id</li>\n",
    "\n",
    "</ol>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The source data from <a href=\"https://www.kaggle.com/competitions/instacart-market-basket-analysis/data\">kaggle</a> is loaded in Vantage with table named <i>Products</i>.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b><i>*Please scroll down to the end of the notebook for detailed column descriptions of the dataset.</i></b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f928d357-c163-406d-91ea-f080951cb2e9",
   "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.1 Examine the Products table</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's look at the sample data in the Products table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ac4a8ad-0d8f-4acd-a063-01f6d1305b83",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = DataFrame(in_schema(\"DEMO_Grocery_Data\", \"products\"))\n",
    "print(\"Data information: \\n\", tdf.shape)\n",
    "tdf.sort(\"product_id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db5ee595-4cbc-4c6e-af53-b0a0d93e5651",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>There are approx 50K records in all, and there are 4 variables. Products are listed from different departments. We shall recommend the products to the user when user is searching for some items from the page.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a871a80-800b-4e70-9d3d-43cbcff71ec8",
   "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.1.1 Analyze Number of products per aisle.</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, let's do some data exploration with aisle and number of products. \n",
    "A histogram of the number of products per aisle is a useful tool for understanding the distribution of products in a store. It can be used to identify aisles with a high or low number of products, as well as aisles with a wide or narrow range of products.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18aff544-69f0-4335-b7f3-21543a452101",
   "metadata": {},
   "outputs": [],
   "source": [
    "product_count_by_aisle = tdf.groupby(['aisle_id']).agg({'product_id':['count']}).sort('count_product_id', False)\n",
    "tdf_aisles = DataFrame(in_schema(\"DEMO_Grocery_Data\", \"aisles\"))\n",
    "\n",
    "\n",
    "# join aisle and product\n",
    "product_count_by_aisle = product_count_by_aisle.join(tdf_aisles, on='aisle_id', how='left', lsuffix='p_', rsuffix='a_').sort('count_product_id', False)\n",
    "product_count_by_aisle"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff89196f-d193-46e5-93c8-17aec1cb2d6e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that we have calculated the histogram values using the teradataml functions. Vantage's Clearscape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI or many python modules available like plotly, seaborn etc. We can do all the calculations and pre-processing on Vantge and pass only the necessary information to visualization tools, this will not only make the calculation faster but also reduce the time due to less data movement between tools. We do the data transfer for this and the subsequent visualizations wherever necessary.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06dc7fe1-3714-46e9-813a-94ac8679a053",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "\n",
    "res = product_count_by_aisle.to_pandas()[1:11]\n",
    "ax = res.plot(\n",
    "    x=\"aisle\",\n",
    "    y=\"count_product_id\",\n",
    "    kind=\"bar\",\n",
    "    figsize=(15, 7),\n",
    "    legend=False,\n",
    "    xlabel=\"aisle\",\n",
    "    ylabel=\"no_products\",\n",
    "    rot=45,\n",
    "    fontsize=12,\n",
    ")\n",
    "\n",
    "# Display y-axis values on bars\n",
    "for p in ax.patches:\n",
    "    ax.annotate(\n",
    "        str(p.get_height()),\n",
    "        (p.get_x() + p.get_width() / 2.0, p.get_height()),\n",
    "        ha=\"center\",\n",
    "        va=\"center\",\n",
    "        xytext=(0, 10),\n",
    "        textcoords=\"offset points\",\n",
    "        fontsize=12,\n",
    "    )\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b45abf1-1b43-4e86-8bf1-ec9c2f4662a2",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Based on the graph presented above, it is evident that <b>Candy chocolate</b> boasts the highest number of products, with a total of 1,246 items. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "013e3ac8-250b-49ca-9b63-bfb2ba730d1f",
   "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.1.2 Sample the data.</b></p>   \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo will use <b>250 Samples from all the Departments</b>. This will allow us to test the system quickly. Once we have validated the system, we can then consider expanding it to include more products. Here, a quick look into the samples</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "513fd812-6af2-4985-a3c8-a08fe3f3b88d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Firstly we find out the number of departments from the entire dataset. This information helps us with the sampling process. However, we identified certain non-cooking-related departments such as pets, personal care, household, babies, and missing items—which we subsequently excluded from the sampling process.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e6b7dad-5305-4ec8-8370-deb77dd98e79",
   "metadata": {},
   "outputs": [],
   "source": [
    "# we first initialize a empty dataframe \"tdf_sample\", which will be used later to concatinate the samples.\n",
    "tdf_sample = tdf.loc[tdf[\"department_id\"] == 0].head(10)\n",
    "print(tdf_sample.shape)\n",
    "print(\"Please wait, we are collecting samples from each departments\")\n",
    " \n",
    "#we then find out the total number of departments\n",
    "total_dept = tdf.select(\"department_id\").agg(\"unique\").get_values()[0][0]\n",
    "\n",
    "# dept like pets, personal care, etc.\n",
    "non_food_departments = [2,5,8,11,17,18,21]\n",
    " \n",
    "st = timeit.default_timer()\n",
    "for dept_id in tqdm(range(1, total_dept+1), desc=\"Overall progress \",):\n",
    "    if dept_id in non_food_departments:\n",
    "        continue\n",
    "    tdf_sample1 = tdf.loc[tdf[\"department_id\"] == dept_id] # get the respective department\n",
    "    tdf_sample2 = tdf_sample1.sample(n=250, seed=10, id_column = \"product_id\") # sample 250 entries\n",
    "    tdf_sample2 = tdf_sample2.drop(['sampleid'], axis=1) # drop sampleid column from the sample result\n",
    "    tdf_sample = tdf_sample.concat(tdf_sample2) # concatinate the results (append)\n",
    "print(f\"total time taken for sampling the products: {timeit.default_timer() - st}\")\n",
    " \n",
    "print(f\"Total sample: {tdf_sample.shape}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cf969d6-9d96-4d68-bbb6-b9ecd10fa3e5",
   "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.2 Do you want to generate the embeddings?</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have already generated embeddings for the snacks department and stored them in files.</p>\n",
    "\n",
    "<center><img src=\"images/decision_emb_gen.jpg\" alt=\"embeddings_decision\" width=300 height=300/></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 and move on to the next section, pleasse click  <a href=\"#section51\">here</a> to skip.</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": "markdown",
   "id": "72d40316-4837-4909-bbc4-9257feae93c5",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<a id='section4'></a>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>4. Generate the embeddings </b>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.1 Generate the embeddings for product table</b></p>    \n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To generate embeddings for a product table, we will use the GIST small Embedding repo from HuggingFace, which uses the <code>GIST-small-Embedding-v0</code> model to generate embeddings. Embedding are a type of word embedding that can be used to represent products in a way that captures their semantic meaning. This means that the embeddings will reflect the meaning of the product name, not just the words that are used. Please refer to the <a href=\"https://huggingface.co/avsolatorio/GIST-small-Embedding-v0\"> Embeddings documentation</a> for more information about embeddings.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Embeddings model takes a text string as input and returns a vector of numbers that represent the embedding. The length of the vector depends on the model that you are using. For example, the <code>GIST-small-Embedding-v0</code> model returns a vector of 384 numbers.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo, we will use <code>GIST-small-Embedding-v0</code> as the model which is rank 19th in <a href=\"https://huggingface.co/spaces/mteb/leaderboard\"> MTEB leaderboard</a> (At the Time of writing)</p> "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6115921-56bb-4717-88d7-9389ae805a26",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To generate the embeddings, we will call the <b>get_embeddings_hf()</b> function. This function will use the Teradata DataFrame generate the embeddings. Once the embeddings are generated, we will store them in separate columns so that we can pass them to the <b>VectorDistance()</b> function later on.</p>\n",
    "\n",
    "<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. If you prefer to skip this step and proceed to the next section, please click  <a href=\"#section5\">here</a> to skip.</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2565eed",
   "metadata": {},
   "outputs": [],
   "source": [
    "def recursive_emb_generator(table_name, file_name, chunksize=100):\n",
    "    wallclock_time_start = timeit.default_timer()\n",
    "\n",
    "    # delete the records\n",
    "    delete_emb_from_sql(table_name, eng)\n",
    "\n",
    "    # Read the data in chunks of 1000 rows\n",
    "    temp_df = pd.read_csv(file_name, chunksize=chunksize)\n",
    "\n",
    "    # Iterate over the chunks\n",
    "    for chunk in tqdm(\n",
    "        temp_df,\n",
    "        desc=\"Overall progress \",\n",
    "    ):\n",
    "        start = timeit.default_timer()\n",
    "        print(\"Data size in current chunk: \", chunk.shape)\n",
    "        df_chunk = get_embeddings_hf(chunk)\n",
    "\n",
    "        copy_emb_to_sql(table_name=table_name, tdf=df_chunk)\n",
    "        print(f\"{df_chunk.shape[0]} products saved to sql \\n\")\n",
    "        end = timeit.default_timer()\n",
    "        print(f\"time taken for {df_chunk.shape[0]} products: {end - start}\")\n",
    "\n",
    "    wallclock_time_end = timeit.default_timer()\n",
    "    wallclock_time = wallclock_time_end - wallclock_time_start\n",
    "    print(\"wallclock time:\\t\", wallclock_time)\n",
    "    print(\"-\" * 50, \" complete \", \"-\" * 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1937bb1-814e-404a-ae60-8f7a16d20738",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note: Please be patient. It will take the code below about 3 - 4 minutes to generate the embeddings for more than 3k+ products. Products are being passed in batches of 1000, and after that, the embeddings are created and stored in Vantage. Therefore, getting through will take time. </b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "048894e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# export df to csv\n",
    "tdf_sample.to_csv('./df_products.csv')\n",
    "\n",
    "recursive_emb_generator(\n",
    "    table_name=\"product_embeddings\", file_name=\"df_products.csv\", chunksize=1000\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b317622-12d6-480c-9a27-66ab9e6a8fbf",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<a id='section42'></a>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.2 Display the product embeddings</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae6961d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "product_embeddings = DataFrame(\"product_embeddings\")\n",
    "print(\"Data information: \\n\", product_embeddings.shape)\n",
    "product_embeddings"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d745d1d1-f7ed-46d5-b3db-55d63c6f90bf",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that generated embeddings for all of the products are in vector of 384 columns. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example: The generated embeddings for product name: <b>Chocolate Sandwich Cookies</b> consists of 384 numbers and looks like:<br>\n",
    "<code>-0.008196\t0.012901\t0.008759\t-0.002950\t-0.019805\t-0.010412</code></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db7470ae-ce98-4b66-9206-c2ba2c90150d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, we have generated the embeddings from the product names and saved the product embeddings dataframe into a vantage table named <b>product_embeddings</b> to use it further.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ee006f2-f551-47a0-b055-940cbde8b0c3",
   "metadata": {},
   "source": [
    "<a id='section51'></a>\n",
    "\n",
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>5. Load the existing embeddings to DB</b>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>5.1 Load the products and searched products embeddings</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo, we will load existing embeddings from files to a database. This will allow us to perform further processing on the embeddings.</p>\n",
    "\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>: If you have already executed the Generate the embeddings section, then below code will be skipped automatically.</i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c49fd6e1-c637-4091-b522-54fa5591c8f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "is_section4_executed = False\n",
    "try:\n",
    "    is_section4_executed = DataFrame.from_query('select count(*) as emb_cnt from product_embeddings').get_values()[0][0] > 0\n",
    "except:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21851d5a-561b-4c84-8e55-dd7da1c2137e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_section5_desc_start():\n",
    "    return \"\"\"<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 product embeddings and the customer's searched product 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>VectorDistance</code> to calculate the distance between the product embeddings and the customer's searched product embeddings. The data contains product 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 over 5000 records, each with 384 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",
    "\n",
    "\n",
    "def get_section5_desc_end():\n",
    "    return \"\"\"<a id='section52'></a><p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>5.2 Display the product embeddings</b></p>\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>To give you a better idea of what the embeddings look like, here are the first five rows of the product embeddings:</p>\"\"\"\n",
    "\n",
    "\n",
    "def get_section5_desc_sample():\n",
    "    return \"\"\"<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that generated embeddings for all of the products are in vector of 384 columns. </p>\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example: The generated embeddings for product name: <b>Chocolate Sandwich Cookies</b> consists of 384 numbers and looks like:<br>\n",
    "    <code>-0.00819\t0.01290\t0.00875\t-0.00294\t-0.01980\t-0.01041</code></p>\"\"\"\n",
    "\n",
    "\n",
    "def load_the_emb():\n",
    "    is_section5_executed = False\n",
    "\n",
    "    if not is_section4_executed:\n",
    "        is_section5_executed = True\n",
    "        start = timeit.default_timer()\n",
    "        display(Markdown(get_section5_desc_start()))\n",
    "\n",
    "        # load product_embeddings to sql\n",
    "        df_product_embeddings_prq = pd.read_parquet(\n",
    "            \"./embeddings/product_embeddings_os_3k.parquet\"\n",
    "        )\n",
    "        \n",
    "        delete_and_copy_embeddings(\n",
    "            table_name=\"product_embeddings\",\n",
    "            tdf=df_product_embeddings_prq,\n",
    "            eng=eng,\n",
    "        )\n",
    "\n",
    "        end = timeit.default_timer()\n",
    "        load_time = end - start\n",
    "        print(f\"embeddings load time:\\t\", load_time)\n",
    "\n",
    "        display(Markdown(get_section5_desc_end()))\n",
    "        product_embeddings = DataFrame(\"product_embeddings\")\n",
    "        print(product_embeddings.shape)\n",
    "        display(Markdown(get_section5_desc_sample()))\n",
    "        return product_embeddings, is_section5_executed\n",
    "    else:\n",
    "        # print(\"Section 4: Generate the embeddings is already executed!\")\n",
    "        display(\n",
    "            Markdown(\n",
    "                \"\"\"<br><div class=\"alert alert-block alert-success\">\n",
    "        <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i>Section 4: Generate the embeddings is already executed! So, skipping the execution of above code.</i></p></div>\"\"\"\n",
    "            )\n",
    "        )\n",
    "        return None, is_section5_executed\n",
    "\n",
    "\n",
    "sample_embeddings, flag = load_the_emb()\n",
    "sample_embeddings.sort(\"product_id\") if sample_embeddings is not None else None"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8321d666-69ce-4c7b-a130-f5bc37aa6892",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The code below will not run if Section 5 has already been skipped.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5306b908-6305-41a6-bf58-2802a2ec12b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "display(Markdown(get_section5_desc_sample())) if flag else None"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "564a2f57-442d-4712-9145-4bcfd2c2d526",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>6. Calculate the K-Mean Clusters using Teradata Vantage in-DB function</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The k-means algorithm groups a set of observations into k clusters in which each observation belongs to the cluster with the nearest mean (cluster centers or cluster centroid). This algorithm minimizes the objective function, that is, the total Euclidean distance of all data points from the center of the cluster as follows:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>Specify or randomly select k initial cluster centroids.</li>\n",
    "<li>Assign each data point to the cluster that has the closest centroid.</li>\n",
    "<li>Recalculate the positions of the k centroids.</li>\n",
    "<li>Repeat steps 2 and 3 until the centroids no longer move.</li>\n",
    "    </ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53c2adb4-f705-47f6-ba51-ba36edbd5156",
   "metadata": {},
   "source": [
    "<a id='section41'></a>\n",
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>6.1 Filter columns from the embeddings</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the steps above we took a sample of the dataset, The sample consisted of 250 sample from 21 departments so that equates to 3000+ products. we need to find clusters in these products. In order to find Kmeans clusters we just need the embeddings information so we discard remaining columns from the dataframe</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e292ab96-0920-4117-96cd-1eb005a33ef4",
   "metadata": {},
   "outputs": [],
   "source": [
    "product_embeddings = sample_embeddings if flag else product_embeddings\n",
    "embedding_column_list = product_embeddings.drop(columns=['product_id', 'product_name', 'aisle_id', 'department_id'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e78a1b00-beca-45ff-ae58-6d72f25916df",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The next question we face is <b>How many clusters?</b>. To find this number we use a technique called <b>Elbow method</b>. The Elbow Method is a technique used in data science to help determine the optimal number of clusters in a dataset. In the code snippet below we try cluster values ranging from 1 to 40 and record the distortion value. The visualizer shows us where the elbow lies in the graph.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Vantage's Clearscape Analytics can easily integrate with 3rd party visualization python modules available like plotly, seaborn etc. We can do all the calculations and pre-processing on Vantge and pass only the necessary information to visualization tools, this will not only make the calculation faster but also reduce the time due to less data movement between tools. We do the data transfer for this and the subsequent visualizations wherever necessary.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ec3af9d-7f70-498b-9852-c19c5a8e14e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "from yellowbrick.cluster import KElbowVisualizer\n",
    "from sklearn.cluster import KMeans\n",
    "\n",
    "model = KMeans()\n",
    "visualizer = KElbowVisualizer(model, k=(1,40))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c52f205-6861-411c-a257-e049a01e5f8b",
   "metadata": {},
   "source": [
    "<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. We are currently performing some mathematical calculations. This process may take 3-5 minutes.</i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d8d2f25-c392-463d-9d73-3ad4cb6a8783",
   "metadata": {},
   "outputs": [],
   "source": [
    "visualizer.fit(embedding_column_list.to_pandas())\n",
    "no_clusters = visualizer.elbow_value_\n",
    "print(f\"optimal number of clusters: {no_clusters}\")\n",
    "visualizer.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ae6ca48-04d7-443c-a0e1-3955a67ac6b8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We observe that elbow lies between 10 to 15, so thats the optimum number of clusters. With that information established we now begin the process of clustering</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76832e4b-cbb4-45d0-b45d-d27e7dc853a7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "This first Query allows us to get the cluster information and that is stored in ModelTable in the database named KMeans_Model</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbf1d3ae-ddef-45bf-999c-9f45dbedf638",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Model Table\n",
    "query = f\"\"\"\n",
    "    SELECT * from TD_KMeans (\n",
    "    ON product_embeddings as InputTable\n",
    "    OUT TABLE ModelTable(KMeans_Model)\n",
    "    USING\n",
    "        IdColumn('product_id')\n",
    "        TargetColumns{tuple(embedding_column_list.columns)}\n",
    "        NumClusters({no_clusters})\n",
    "        OutputClusterAssignment('true')\n",
    ") AS dt;\"\"\"\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table(\"KMeans_Model\")\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d50efb8-892b-460a-9557-0273fdfe8283",
   "metadata": {},
   "outputs": [],
   "source": [
    "KMeans_Model = DataFrame(\"KMeans_Model\")\n",
    "print(\"Data information: \\n\", KMeans_Model.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e78606be-3d88-4ee8-80fd-97dd592df747",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>With the cluster information established and stored in the ModelTable. we also need to find which clusters each of the 3000+ products we took in the sample belings to.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05a570cb-6ae3-4a94-8efe-0e118ffd02f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# for Cluster info, used later for filtering\n",
    "query1 = f\"\"\"\n",
    "    SELECT * from TD_KMeans (\n",
    "    ON product_embeddings as InputTable\n",
    "    USING\n",
    "        IdColumn('product_id')\n",
    "        TargetColumns{tuple(embedding_column_list.columns) } \n",
    "        NumClusters({no_clusters})\n",
    "        OutputClusterAssignment('true')\n",
    ") AS dt;\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dcdcbee6-16bf-4d7d-b0c0-180730a7e8be",
   "metadata": {},
   "outputs": [],
   "source": [
    "kmeans_out_sql = DataFrame.from_query(query1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f2dca5f-428f-4c91-8366-a3143303c3d1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We now combine each of the cluster information with their embeddings. We will use this information later when we are recommending products</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a896344d-6fcc-4abf-bb54-2b8c8dfce0be",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "product_embeddings_cluster = product_embeddings.join(other=kmeans_out_sql, how=\"full\", on=\"product_id=product_id\", lprefix= \"L_\")\n",
    "\n",
    "# view products in cluster 1\n",
    "product_embeddings_cluster[['td_clusterid_kmeans','product_id','product_name','aisle_id','department_id']].loc[product_embeddings_cluster.td_clusterid_kmeans==1]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d45ba8f-8455-4411-8875-f1ebd3e6c1d0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We observe that items in a same cluster have similar characteristics.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc972656-e202-44b6-81ee-301f23589e59",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>7. Setup LLM </b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this section we will configure the AWS Command Line Interface (AWS CLI) and access the AWS Bedrock model</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4c60a2e-c832-4b82-93a8-9411fac2d7ed",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>7.1 Configure AWS</b></p> \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Configure the settings that the AWS Command Line Interface (AWS CLI) uses to interact with AWS. These include the following:</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Credentials</b> identify who is calling the API. Access credentials are used to encrypt the request to the AWS servers to confirm your identity and retrieve associated permissions policies. These permissions determine the actions you can perform. For information on setting up your credentials, see Authentication and access credentials.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To get the AWS access key and secret key, follow  <a href=\"https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_access-keys.html#Using_CreateAccessKey\">Managing access keys</a> document from AWS.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "127dc74e-825c-428c-b4a3-a8e1160c161d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def configure_aws():\n",
    "    print(\"configure the AWS CLI\")\n",
    "    # enter the access_key/secret_key\n",
    "    access_key = getpass.getpass(\"aws_access_key_id \")\n",
    "    secret_key = getpass.getpass(\"aws_secret_access_key \")\n",
    "    region_name = getpass.getpass(\"region name\")\n",
    "\n",
    "    #set to the env\n",
    "    !aws configure set aws_access_key_id {access_key}\n",
    "    !aws configure set aws_secret_access_key {secret_key}\n",
    "    !aws configure set default.region {region_name}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c426e66b-65fb-49d7-8a14-c7af24cbb71f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will need to configure the AWs CLI only first time, if it will be already configured then then below code will be skipped. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ac429a4-73f9-42ca-9d8b-44d431700097",
   "metadata": {},
   "outputs": [],
   "source": [
    "does_access_key_exists = !aws configure get aws_access_key_id\n",
    "\n",
    "if len(does_access_key_exists) == 0:\n",
    "    configure_aws()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2166b12-1189-4fb6-aab9-d541bc276dcc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To Verify the added credentials run the below code</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d94b77c-c81f-462c-8129-d2d1d7b74b3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "!aws configure list"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfe1877e-3d0b-4a85-a0da-6454036afc1a",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>7.2 Access Bedrock model</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1e36b87-2768-4fef-b25b-a40075e4c2c7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo we will be using AI21 Labs <code>Jurassic-2 Mid</code> on Amazon Bedrock model as LLM. AI21’s mid-sized model for advanced text generation tasks that require both quality and affordability. To view list of available models on AWS Bedrock <a href='https://aws.amazon.com/bedrock/'>click here</a></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Inference parameters:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Temperature (temperature)– Use a lower value to decrease randomness in the response.</li>\n",
    "    <li>Max completion length (maxTokens) – Specify the maximum number of tokens to use in the generated response.</li>\n",
    "    <li>Stop sequences (stopSequences) – Configure stop sequences that the model recognizes and after which it stops generating further tokens. Press the Enter key to insert a newline character in a stop sequence. Use the Tab key to finish inserting a stop sequence.</li>\n",
    "    <li>Presence penalty (presencePenalty) – Use a higher value to lower the probability of generating new tokens that already appear at least once in the prompt or in the completion.</li>\n",
    "    <li>Count penalty (countPenalty) – Use a higher value to lower the probability of generating new tokens that already appear at least once in the prompt or in the completion. Proportional to the number of appearances.</li>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d9482d6-bb7c-43d7-afd6-759c094762a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Bedrock Clients\n",
    "bedrock=boto3.client(service_name=\"bedrock-runtime\", region_name='us-east-1')\n",
    "\n",
    "def get_llm():\n",
    "    return Bedrock(model_id=\"ai21.j2-mid-v1\",client=bedrock,\n",
    "                model_kwargs={'temperature': 0.9, 'maxTokens': 1500, \"stopSequences\":[\"$$\"],\"countPenalty\":{\"scale\":0},\"presencePenalty\":{\"scale\":0}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e39ebb1-b747-4ac6-b8d6-56527bf26965",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.evaluation import EvaluatorType\n",
    "custom_criterion = {\n",
    "    \"recipe\": \"Does the output contain ingredients and steps?\"\n",
    "}\n",
    "\n",
    "def check_for_recipe(response):\n",
    "    \"\"\"Check if the Input is a recipe or Not\"\"\"\n",
    "    evaluator = load_evaluator(EvaluatorType.CRITERIA, criteria=custom_criterion, llm=llm)\n",
    "    eval_result = evaluator.evaluate_strings(\n",
    "        prediction=response,\n",
    "        input=\"Does this look like a recipe?\")\n",
    "    print(eval_result)\n",
    "    return eval_result[\"score\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13aa445d-ac6e-403f-8fe4-e70144281e03",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We use <code>String Evaluators</code>. These evaluators assess the predicted string for a given input, usually comparing it against a reference string. In scenarios where we wish to assess a model’s output using a specific rubric or criteria set, the criteria evaluator proves to be a handy tool. It allows us to verify if our LLM or Chain’s output complies with a defined set of criteria.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88dcd042-56ef-43e2-b368-570a6c1ac266",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the llm model\n",
    "llm = get_llm()\n",
    "\n",
    "# propmt to extarct the ingredients\n",
    "ingredient_prompt = PromptTemplate.from_template(\n",
    "    '''You are a helpful AI bot to extract the ingredients from given recipe's Section 1: {input}. \n",
    "    Do not give quantity and Ingredient keywords\n",
    "    Do not repeat the ingredient in the response.\n",
    "    Give output in comma separated list'''\n",
    ")\n",
    "\n",
    "ingredient_chain = ingredient_prompt | llm\n",
    "\n",
    "def get_ingredients(response):\n",
    "    \"\"\"Get the Ingredients from the Recipie Provided\"\"\"\n",
    "    answer1 = ingredient_chain.invoke({\"input\" : response})\n",
    "    ingredients_answer = list(answer1.split(\", \"))\n",
    "    return ingredients_answer"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e12ba907-8004-4025-94d5-7d0867cb66fd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the code above, we have instructed the LLM to extract the ingredients from the recipe. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e28c983-783a-4318-84ac-3f023b7f6387",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>8. Launch the Chatbot</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo we are using models on Amazon Bedrock, specifically AI21 Labs Jurassic-2 Mid model. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc1888c8-7835-4a87-b505-26bc3f56ce59",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b>Please note that our chatbot is specifically designed to address questions related to recipe suggestions. If you have a question outside of this scope, we won't give an answer.</i></p>\n",
    "</div>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fcb4baf-972f-47c4-80ba-a15d9046b57d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Chain\n",
    "from typing import List, Tuple\n",
    "\n",
    "from langchain_core.utils.function_calling import convert_to_openai_function\n",
    "from langchain.tools.render import format_tool_to_openai_function\n",
    "from langchain.memory import ConversationBufferMemory, ConversationBufferWindowMemory\n",
    "from langchain.memory import (\n",
    "    ConversationSummaryMemory,\n",
    "    ChatMessageHistory,\n",
    "    ConversationSummaryBufferMemory,\n",
    ")\n",
    "from langchain.prompts import MessagesPlaceholder\n",
    "from langchain.schema.runnable import RunnablePassthrough\n",
    "from langchain.agents.output_parsers import OpenAIFunctionsAgentOutputParser\n",
    "from langchain.agents import AgentExecutor\n",
    "# from langchain.agents.format_scratchpad import format_to_openai_function_messages\n",
    "from typing import List, Tuple\n",
    "from langchain.prompts import (\n",
    "    ChatPromptTemplate,\n",
    "    FewShotChatMessagePromptTemplate,\n",
    ")\n",
    "\n",
    "examples = [\n",
    "    {\"input\": \"I have bread, butter and eggs\", \n",
    "     \"output\": '''\n",
    "         ## Recipe Name: Egg in Toast Recipe\n",
    "         ### Section 1: Ingredients with Quantity\n",
    "           - Bread: 2 slices\n",
    "           - Butter: enough to spread on the bread\n",
    "           - Eggs: as many as desired\n",
    "\n",
    "         ### Section 2: Cooking Instructions\n",
    "              - Preheat a non-stick pan over medium heat.\n",
    "              - Spread butter evenly on one side of each slice of bread.\n",
    "              - Place the buttered sides down in the pan and toast until golden brown. Flip and repeat on the other side. Set aside.\n",
    "              - In the same pan, melt some additional butter if needed. Crack an egg into the pan and cook according to your preference (over easy, over hard, sunny side up). Repeat for each egg.\n",
    "              - Assemble the dish by placing the cooked egg(s) atop the toasted bread. Enjoy!'''},\n",
    "    {\"input\": \" I have beans, rice and butter\", \n",
    "     \"output\": '''\n",
    "         ## Recipe Name: Beans and Rice with Butter\n",
    "         ### Section 1: Ingredients with Quantity\n",
    "            - Beans (cooked): 2 cups\n",
    "            - Rice (uncooked): 1 cup\n",
    "            - Water: 2 cups\n",
    "            - Salt: to taste\n",
    "            - Butter: 2 tablespoons\n",
    "\n",
    "         ### Section 2: Cooking Instructions\n",
    "           - Rinse the rice under cold water and drain it. Heat 1 tablespoon of butter in a pot over medium heat. Add the drained rice and stir constantly for about 2 minutes or until lightly toasted.\n",
    "           - Add 2 cups of water and bring to a boil. Reduce heat to low, cover, and simmer for approximately 18 minutes or until all the water is absorbed and the rice is tender. Remove from heat and let stand for 5 minutes before fluffing with a fork.\n",
    "           - While the rice is cooking, heat the remaining 1 tablespoon of butter in another pot over medium heat. Once hot, add the cooked beans along with salt to taste. Stir occasionally and cook for around 5 minutes to allow the flavors to meld together.\n",
    "           - Serve the beans over the cooked rice. Enjoy!'''},\n",
    "]\n",
    "\n",
    "example_prompt = ChatPromptTemplate.from_messages(\n",
    "    [\n",
    "        (\"human\", \"{input}\"),\n",
    "        (\"ai\", \"{output}\"),\n",
    "    ]\n",
    ")\n",
    "few_shot_prompt = FewShotChatMessagePromptTemplate(\n",
    "    example_prompt=example_prompt,\n",
    "    examples=examples,\n",
    ")\n",
    "\n",
    "\n",
    "final_prompt = ChatPromptTemplate.from_messages(\n",
    "    [\n",
    "        (\"system\", '''\n",
    "         As a seasoned culinary expert, I am delighted to offer my assistance in providing delicious recipe suggestions \n",
    "            along with detailed cooking instructions. Upon your request, I will promptly provide a list of fresh and flavorful \n",
    "            ingredients, as well as easy-to-follow very Detailed steps with all the details for creating delectable dishes that are sure to impress even the most \n",
    "            discerning palates. Let's get started!\n",
    "            My output comes in the format:\n",
    "            \n",
    "            ## Recipe Name:\n",
    "            ### Section 1: Ingredients with Quantity\n",
    "                        - Ingredient 1: Quantity\n",
    "                        - Ingredient 2: Quantity\n",
    "                        - Ingredient 3: Quantity\n",
    "                        - ...\n",
    "            ### Section 2: Cooking Instructions\n",
    "                        - Step 1 of the cooking process.\n",
    "                        - Step 2 of the cooking process.\n",
    "                        - Step 3 of the cooking process.\n",
    "                        ...\n",
    "            In Section 1: All the Ingredients with their respective quantities\n",
    "            In Section 2: Cooking recipe with excruciating detail\n",
    "            \n",
    "            I have Expertise Only in the Following Domain: Culinary Knowledge, Food Science, Cooking Tips and Nutritional Knowledge\n",
    "            I will not answer any other question outside of my domain and reply with \"I cannot Help you with this query, Please ask another question related to Culinary, Food Science, Cooking Tips and Nutritional Knowledge\"\n",
    "            \n",
    "            Only based on user's input, generate the recipe, If user start with greeting, then greet it as recipe assistant.\n",
    "            Must give recipe Ingredients and  Cooking Instructions in the response.\n",
    "            What kind of dish would you like to prepare today?\n",
    "        '''),\n",
    "        few_shot_prompt,\n",
    "        (\"human\", \"{input}\"),\n",
    "    ]\n",
    ")\n",
    "\n",
    "llm = get_llm()\n",
    "\n",
    "chain = final_prompt | llm"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f079cef6-7a74-4025-8e90-7303daf3fab3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As part of our demonstration, we will showcase the use of a chatbot. To request meal recommendations from a recipe assistant, one could input a query like \"<b>I have eggs, wine, and cheese at my disposal; could you kindly propose a dinner recipe for us?</b>\" into the conversation window.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Additionally, the chatbot we'll discuss has the capability to extract required ingredients from recommended recipes and suggest substitutes when specific items are missing in our kitchen inventory. This feature simplifies grocery shopping by providing tailored product recommendations based on our unique needs.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Once we have identified the necessary ingredients, we will proceed to submit them in order to receive customized recommendations tailored to our selection.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5500785c-dba8-4f3b-b173-bbe7d60c00de",
   "metadata": {},
   "outputs": [],
   "source": [
    "import panel as pn\n",
    "from panel.chat import ChatMessage\n",
    "pn.extension(design=\"material\")\n",
    "\n",
    "\n",
    "def send_checkboxes(content):\n",
    "    global ingredients\n",
    "    ingredients = get_ingredients(content)\n",
    "    global checkbox_group\n",
    "    checkbox_group = pn.widgets.CheckBoxGroup(\n",
    "                name='Checkbox Group', options=ingredients,\n",
    "                    inline=False)\n",
    "    column = pn.Column(\"Please choose the ingredients that aren't in your home pantry. So, we can provide you with recommendations.\", checkbox_group, button, styles=dict(background='WhiteSmoke'))\n",
    "    chat_interface.send(column, respond=False, user=\"System\",avatar=\"🛒\")\n",
    "\n",
    "\n",
    "def submit_values(event):\n",
    "    global values_checkbox\n",
    "    values_checkbox = checkbox_group.value\n",
    "    print(values_checkbox)\n",
    "    chat_interface.send(\"We have received your values submission. Please continue with the notebook to get the recommendations\", respond=False, user=\"System\", avatar=\"🛒\")\n",
    "\n",
    "\n",
    "def callback(contents, user, instance):\n",
    "    answer = chain.invoke({\"input\": contents})\n",
    "    answer = answer.replace('AI:', '')\n",
    "    if check_for_recipe(answer):\n",
    "        send_checkboxes(answer)\n",
    "        return answer\n",
    "    else :\n",
    "        return answer\n",
    "global button\n",
    "button = pn.widgets.Button(name='Submit', button_type='primary')\n",
    "button.on_click(submit_values)\n",
    "\n",
    "\n",
    "chat_interface = pn.chat.ChatInterface(\n",
    "    callback=callback,\n",
    "    show_rerun=False,\n",
    "    show_undo=False,\n",
    "    width=800,\n",
    "    height=400,\n",
    "    callback_exception='verbose',\n",
    ")\n",
    "\n",
    "chat_interface.servable()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9fcdc68-d658-4e16-a614-ac1f9184b56d",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>If the chatbot didn't work when you pressed ENTER, on your first time using this demo on your environment, did you use F5 to reload the site? See instructions at the top of the notebook.\n",
    "If you asked a question and got no response after a few minutes, it is possible that you will need to type 0 0 to restart the kernel and re-run the demo. Questions outside the model seem to confuse the chatbot.</p></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "878fb38f-5781-4bb5-89d7-f4ee58408b19",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_selected_item_desc(values_checkbox):\n",
    "    txt = \",\".join(values_checkbox)\n",
    "    return f\"\"\"<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have selected <b>{txt}</b>, We'll get the recommendations for the selected products.\n",
    "    </p>\"\"\"\n",
    "\n",
    "def get_mandatory_sel_prod_desc():\n",
    "    return \"\"\"<div class=\"alert alert-block alert-warning\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b>Please note that, to proceed further with this demo, it is essential for us to <b>ask the questions</b> first and then <b>select the suggested ingredients</b> from the checkbox list. Based on the selected ingredients, we will proceed with our recommendations.</p></div>\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "410fd7f8-bacc-4f28-b65a-686e5025c0f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    if len(values_checkbox) < 0:\n",
    "        pass\n",
    "    else: \n",
    "        display(Markdown(get_selected_item_desc(values_checkbox))) if values_checkbox else None\n",
    "except Exception as e:\n",
    "    display(Markdown(get_mandatory_sel_prod_desc()))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97b9cb1f-31b1-4341-b74b-6d0d76e0a4c1",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>9. Calculate the VectorDistance using Teradata Vantage in-DB function</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afc6d416-76c4-43d2-bdd0-2259feb42f61",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TD_VectorDistance function accepts a table of target vectors and a table of reference vectors and returns a table that contains the distance between target-reference pairs.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The function computes the distance between the target pair and the reference pair from the same table if you provide only one table as the input.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a91765b5-e0af-435c-8173-ff09bbb2528c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The VectorDistance function calculates the distance between a target vector and a reference vector. We use the cosine distance metric, which measures the similarity between two vectors. The function can return the maximum of 1 to 100 closest reference vectors to include in the output table for each target vector. In this demo, we want the top 2 closest reference vectors to the target vector.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The VectorDistance function have a parameter <b>distance_measure</b>. You can pass anyone from the below list. Default value is cosine.</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Cosine distance measures</b> the similarity between two vectors by calculating the cosine of the angle between them. It is a good measure of similarity for high-dimensional data, as it is not affected by the magnitude of the vectors.</li>\n",
    "    <li><b>Euclidean distance measures</b> the distance between two points in a Euclidean space. It is the most common distance measure, and it is a good measure of similarity for low-dimensional data.</li>\n",
    "    <li><b>Manhattan distance measures</b> the distance between two points in a Manhattan space. It is similar to Euclidean distance, but it uses the absolute value of the difference between the coordinates instead of the square of the difference.</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a51aa88e-40ed-45ec-8f14-ecd8835f14d4",
   "metadata": {},
   "source": [
    "<center><img src=\"images/distance_measure.png\" alt=\"distance_measure\"  width=600 height=600/></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfc073c1-a651-4177-b035-d9a1b106d96e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The below function, <code>TD_VECTORDISTANCE</code>, will take the target table, reference table, embedding column names, and number of recommendations.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59072271-8a8c-4dff-89ac-6809ce141985",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_vector_distance(target_table, reference_table, emb_column_names, topk):\n",
    "    start = timeit.default_timer()\n",
    "    VectorDistance_out = VectorDistance(target_id_column=\"product_id\",\n",
    "                                        target_feature_columns=emb_column_names,\n",
    "                                        ref_id_column=\"product_id\",\n",
    "                                        ref_feature_columns=emb_column_names,\n",
    "                                        distance_measure=['Cosine'],\n",
    "                                        topk=topk,\n",
    "                                        target_data=target_table,\n",
    "                                        reference_data=reference_table)\n",
    "\n",
    "    print(f'vector-distance calcuatation time:\\t',  timeit.default_timer() - start)\n",
    "    return VectorDistance_out.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2d001ae-cced-4e9f-9d03-0886638524ac",
   "metadata": {},
   "source": [
    "<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>: by default, we suggest 2 recommendations for each searched product. If you want to change this, you can update the value of the <code>number_of_recommendations</code> variable.</i></p>\n",
    "</div>\n",
    "\n",
    "<div class=\"alert alert-block alert-info\" id=\"no-azure\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Please be aware that calculating vector distances may take approximately 80-100 seconds. This is due to the fact that our platform is still small and we are employing advanced mathematical algorithms to determine the cosine distance between products. This process can be computationally intensive, resulting in a slightly longer processing time.</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e7700b9-8fc7-4b6a-a9c3-b7cae7496ae2",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b> 9.1 Get the embedding for the selected Items</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0fbbe6e-aad7-4b91-8cd2-347427c69966",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's take <b>the selected products</b> to check their recommended products from our database. To do this, we need to follow the same process as before: generate the embeddings for the products and store them back to the Vantage table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f41acefc-ab50-4e03-abbd-64a1673d3f9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "search_products = pd.DataFrame(values_checkbox)\n",
    "search_products = search_products.rename(columns={0:'product_name'})\n",
    "search_products.insert(0, 'product_id', range(1, 1 + len(search_products)))\n",
    "search_products"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf7810a8-1c62-43ad-af13-48b75eb5fc08",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The get_embeddings() function uses the HuggingFace GIST-small-Embedding-v0 model to generate the embeddings.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "195fceb7-36a8-4639-957c-529b425f9ed9",
   "metadata": {},
   "outputs": [],
   "source": [
    "from warnings import simplefilter\n",
    "simplefilter(action=\"ignore\", category=pd.errors.PerformanceWarning)\n",
    "\n",
    "start = timeit.default_timer()\n",
    "df_search_products = get_embeddings_hf(search_products)\n",
    "end = timeit.default_timer()\n",
    "load_time = end - start\n",
    "print(\n",
    "    f\"generate the embeddings for {df_search_products.shape[0]} search products:\\t\",\n",
    "    load_time,\n",
    ")\n",
    "print(\"----- complete -----\")\n",
    "\n",
    "# Print the DataFrame.\n",
    "# df_search_products.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2b1a92d-f406-4f3c-aa73-d472eb9a020c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Since the product names were searched, we have now generated the embeddings. The product embeddings dataframe must therefore be saved into a new table called <b>search_product_embeddings</b> before we can utilize it further..</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d352f26-1523-49e7-a0f9-c21fe8b238d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "warnings.filterwarnings(\"ignore\", category=FutureWarning)\n",
    "copy_to_sql(df_search_products, table_name=\"search_product_embeddings\",primary_index='product_id', if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "742a68e4-c575-416e-a4cc-2ce9990375a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "search_product_embeddings = DataFrame(\"search_product_embeddings\")\n",
    "\n",
    "print(\"Data information: \\n\", search_product_embeddings.shape)\n",
    "search_product_embeddings"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da2668e3-d5e1-4e9b-acb4-da7f3a0c1924",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b> 9.2 Figure out the clusters for the selected items</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To enhance the quality of our recommendations, we plan to locate the cluster containing the selected ingredients among those we previously generated in Section 6.1</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dcabaab3-b991-4500-9d25-8b1c0d3e64da",
   "metadata": {},
   "outputs": [],
   "source": [
    "# KMEans Predict\n",
    "query = f\"\"\"\n",
    "    SELECT * FROM TD_KMeansPredict (\n",
    "        ON search_product_embeddings AS InputTable\n",
    "        ON KMeans_Model AS ModelTable DIMENSION\n",
    "        USING\n",
    "            OutputDistance('true')\n",
    "            Accumulate('product_name')\n",
    "        ) AS dt;\n",
    "    \"\"\"\n",
    "\n",
    "kmeans_predict_out_sql = DataFrame.from_query(query)\n",
    "kmeans_predict_out_sql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07cc1b4c-29c0-42df-a1cd-129d22e1adb8",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b> 9.3 Filter data only from the selected clusters and calculate the VectorDistance</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the below cell, we will filter the cluster first and then calculate the <code>VectorDistance</code> using vantage in-DB function.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36c1bcde-bb19-4190-a9a8-799f033a4299",
   "metadata": {},
   "outputs": [],
   "source": [
    "vals = kmeans_predict_out_sql.get_values()[:, 1]\n",
    "product_embeddings_cluster_filtered = product_embeddings_cluster.loc[product_embeddings_cluster['td_clusterid_kmeans'].isin(vals.tolist())]\n",
    "\n",
    "# copy results to sql for improve the performance\n",
    "copy_to_sql(product_embeddings_cluster_filtered, table_name=\"product_embeddings_cluster_filtered\", if_exists=\"replace\")\n",
    "\n",
    "product_embeddings_cluster_filtered = DataFrame(\"product_embeddings_cluster_filtered\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1399d9fa-3a36-4a60-ad3c-28e299a020ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "emb_column_names = DataFrame(\"search_product_embeddings\").columns[2:]\n",
    "\n",
    "# select top matching\n",
    "number_of_recommendations = 3\n",
    "\n",
    "vector_distance_df = calculate_vector_distance(\n",
    "    target_table= search_product_embeddings,\n",
    "    reference_table = product_embeddings_cluster_filtered,\n",
    "    emb_column_names=emb_column_names,\n",
    "\n",
    "    topk=number_of_recommendations,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25939903-ac22-4c4d-becb-1c87ba5ceaa9",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>10. Display the recommended products for the users.</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a96ef74-4d19-4b5b-ba26-b1459d35204f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To view the recommendations, we need to join two tables together. First, we will join the vector distance result table with the product embeddings table. This will give us a table that contains the vector distance scores for each product, as well as the product embeddings. Then, we will join this table with the search products table. This will give us a final table that contains the recommendations for the search products.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0ac1910",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_final_recommendations(\n",
    "    vector_distance_df, product_embeddings_df, search_product_embeddings_df\n",
    "):\n",
    "    product_embeddings_df_selected_columns = (product_embeddings_df.select([\"product_id\", \"product_name\"]))\n",
    "\n",
    "    # join vector-distance results and products\n",
    "    vec_prod_join_result = vector_distance_df.merge(right = product_embeddings_df_selected_columns, left_on = \"reference_id\", right_on=\"product_id\", lsuffix = \"t1\", rsuffix = \"t2\")\n",
    "\n",
    "\n",
    "    # join the above joined table with search products\n",
    "    vec_prod_join_result_selected = vec_prod_join_result[[\"product_id\", \"product_name\", \"target_id\", \"distancetype\", \"distance\"]]\n",
    "\n",
    "\n",
    "    # join_result_sorted_selected\n",
    "    df_search_products_selected = (search_product_embeddings_df.select([\"product_id\", \"product_name\"]))\n",
    "\n",
    "    # recommendation results\n",
    "    df_recommendations = df_search_products_selected.merge(right=vec_prod_join_result_selected,\n",
    "        left_on=\"product_id\",\n",
    "        right_on=\"target_id\",\n",
    "        how=\"inner\",\n",
    "        lsuffix=\"_search\", rsuffix = \"_recommended\"\n",
    "    )\n",
    "\n",
    "\n",
    "    # filter with extact match\n",
    "    df_recommendations = df_recommendations[df_recommendations.distance > 0.001]\n",
    "    \n",
    "    # sort by distance\n",
    "    df_recommendations = df_recommendations.sort([\"_search_product_id\", \"distance\"], ascending=True)\n",
    "\n",
    "    return df_recommendations[\n",
    "        [\n",
    "            \"_search_product_id\",\n",
    "            \"_search_product_name\",\n",
    "            \"_recommended_product_id\",\n",
    "            \"_recommended_product_name\",\n",
    "            \"distance\",\n",
    "        ]\n",
    "    ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39f9e238-893e-4e57-ad0e-096ee3062e1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_recommendations = get_final_recommendations(\n",
    "    vector_distance_df, product_embeddings_cluster_filtered, search_product_embeddings\n",
    ")\n",
    "\n",
    "# copy results to sql for improve the performance\n",
    "copy_to_sql(df_recommendations, table_name=\"df_recommendations\", if_exists=\"replace\")\n",
    "\n",
    "df_recommendations = DataFrame(\"df_recommendations\")\n",
    "df_recommendations.sort('_search_product_name')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d97633b-9d36-45d4-82d1-ba4306ffcf1e",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>10.1 Dataframes for Customer_transactions</b></p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>We'll analyze the customer transaction data to review the products they've purchased in the past. Using this information, we'll tailor product recommendations for each customer. For instance, if a customer is looking for Olive Oil and has previously bought Tiger All Natural Pure Olive Oil, we'll suggest it to them.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3419a73b-7165-480d-9f55-e8f93de716eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_customer_transaction = DataFrame(\"customer_transactions\")\n",
    "\n",
    "print(\"Data information: \\n\", tdf_customer_transaction.shape)\n",
    "tdf_customer_transaction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0871755-f468-45f5-839f-eae9cc629e2a",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b> 10.2 Based on customer behaviour recommended the products</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "102e20b6-b4be-4088-81d2-0146b1f1cca2",
   "metadata": {},
   "source": [
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>As we conclude our demonstration, we'll analyze the customer transaction records to ensure our recommendations align with their past purchases. If there's no match between historical purchases and current suggestions, we'll then provide tailored recommendations based on the preceding steps.</p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>In order to accomplish this, we filter out the product IDs from our recommendations and search for the same product IDs in the history table. When we find a match, we recommend those products instead of our previously suggested ones, and ultimately display these items. If there's no purchase history available or we don't find any previously purchased products, we'll provide recommendations based on our system's default suggestions.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba828edb-08f1-4a92-ad98-eedb667b5726",
   "metadata": {},
   "outputs": [],
   "source": [
    "# all the ids from recommendations\n",
    "recommended_product_id = df_recommendations.get_values()[:,2]\n",
    "\n",
    "# filter products from customer transcations based on recommended product ids\n",
    "# first get product ids from transaction data\n",
    "trans_product_ids = tdf_customer_transaction[tdf_customer_transaction['product_id'].isin(recommended_product_id.tolist())].select('product_id').get_values()[:].flatten()\n",
    "\n",
    "# filter products from all the recommended product based on transactions data.\n",
    "product_rec_based_trans = df_recommendations.loc[df_recommendations['_recommended_product_id'].isin(trans_product_ids.tolist())][['_search_product_name','_recommended_product_name']]\n",
    "\n",
    "# get product ids which are not found in customer transcation table\n",
    "missed_hist_rec_ids = np.setdiff1d(recommended_product_id, trans_product_ids)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e324096-9c75-4458-ae71-b0391b55d24f",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>10.3 Final recommendations of product</b></p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>First, we'll incorporate the products found in the customer transactions into our final recommendations. Then, we'll include any missed products that were recommended by our system but weren't found in the customer transactions.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f80322b7-1865-4cea-b033-31744215313d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# if products found in  customer transactions, then add those to final recommendations\n",
    "df_recommendations_final = product_rec_based_trans if len(trans_product_ids) > 0 else df_recommendations\n",
    "if len(missed_hist_rec_ids) > 0:\n",
    "    # add those products which are not found in customer transactions\n",
    "    missed_rec = df_recommendations.loc[df_recommendations['_recommended_product_id'].isin(missed_hist_rec_ids.tolist())][['_search_product_name','_recommended_product_name']]\n",
    "    df_recommendations_final = df_recommendations_final.concat(missed_rec)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eeef6050-13a0-481d-98a6-019c3da46972",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Based on the products we searched for, here we'll display the recommended products. We have created a response template which will help user to view recommendations within notebook in better way.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97021681-7f50-4a65-8a40-643096d2f2bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import display, Markdown\n",
    "\n",
    "def response_template(data):\n",
    "    view = \"\"\"<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>Product Recommendations</b></p>\"\"\"\n",
    "    products = {}\n",
    "\n",
    "    # Organize data into dictionary\n",
    "    for category, product in data:\n",
    "        if category in products:\n",
    "            products[category].append(product)\n",
    "        else:\n",
    "            products[category] = [product]\n",
    "            \n",
    "    # Print products and sub-products\n",
    "    for main_product, sub_products in products.items():\n",
    "        view = (\n",
    "            view\n",
    "            + f\"\"\" <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>  <li> Based on your search for  <strong>{main_product}</strong> here are some recommended products: <ul>\"\"\"\n",
    "        )\n",
    "        \n",
    "        view2 = \"\"\n",
    "        for sub_product in sub_products:\n",
    "            view2 = view2 + f\"\"\" <li>{sub_product}</li>\"\"\"\n",
    "        view = view + view2 + \"</ul></ul>\"\n",
    "    return view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60e6ce4a-5f48-4af3-8686-ef56dcb925dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "number_of_products_to_display = df_recommendations_final.assign(drop_columns=True, dist_search_product_name=df_recommendations_final._search_product_name.distinct().count()).get_values()[0][0]\n",
    "\n",
    "# get values\n",
    "df_recommendations_final_cols = (\n",
    "    df_recommendations_final.select([\"_search_product_name\", \"_recommended_product_name\"])\n",
    "    .groupby(\"_search_product_name\")\n",
    "    .sort(\"_search_product_name\")\n",
    "    .get_values()\n",
    ")\n",
    "\n",
    "display(Markdown(response_template(df_recommendations_final_cols)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d563d56-a768-410f-a948-b4ec6ef34e51",
   "metadata": {},
   "source": [
    "<div id='section10'></div>\n",
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>11. Cleanup</b>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>11.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": "b96a0c26-03e2-4115-b44f-d85531600b5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = [\n",
    "    \"customer_transactions\",\n",
    "    \"product_embeddings\",\n",
    "    \"KMeans_Model\",\n",
    "    \"search_product_embeddings\",\n",
    "    \"df_recommendations\",\n",
    "    \"product_embeddings_cluster_filtered\"\n",
    "]\n",
    "\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name=table)\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "defd724f-14dd-410e-a452-4fe6a2325dfe",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'> <b>11.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": "396e4803-5b5f-464b-8004-15df96c99d50",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_Grocery_Data');\"        # Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30521be8-1d18-48b2-a857-d5377ac0e519",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96cb092e-5f56-45af-a6b6-538933722f1d",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Required Materials</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let’s look at the elements we have available for reference for this notebook:</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Filters:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Industry:</b> ECommerce</li>\n",
    "    <li><b>Functionality:</b> Generative AI</li>\n",
    "    <li><b>Use Case:</b> Product Recommendation</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Related Resources:</b></p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><a href='https://www.teradata.com/Blogs/Fraud-Busting-AI'>Fraud-Busting-AI</a></li>\n",
    "    <li><a href='https://www.teradata.com/Industries/Financial-Services'>Financial Services</a></li>\n",
    "    <li><a href='https://www.teradata.com/Resources/Datasheets/Move-from-Detection-to-Prevention-and-Outsmart-Fraudsters'>Move from Detection to Prevention and Outsmart Tech-Savvy Fraudsters</a></li>\n",
    "</ul>\n",
    "\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Dataset:</b>\n",
    "\n",
    "- `product_id`: Unique row customer id\n",
    "- `product_name`: customer age (numeric)\n",
    "- `aisle_id` : Aisle id (numeric)\n",
    "- `department_id` : Department id (numeric)\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Dataset source: <a href=\"https://www.kaggle.com/competitions/instacart-market-basket-analysis/data\">kaggle</a></p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Teradataml Python reference: <a href = 'https://docs.teradata.com/search/all?query=Python+Package+User+Guide&content-lang=en-US'>here</a></li>\n",
    "    <li>OpenAI embeddings reference: <a href='https://platform.openai.com/docs/guides/embeddings'>here</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "624dca69-b83a-42bc-a5f7-9203760b846a",
   "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
}