07-TabularDataQA.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "45b4b5a4-96b0-4935-b890-8eb1c3d678ad",
   "metadata": {},
   "source": [
    "# Skill 2 : Data Analysis from CSV file"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9697d091-a0fb-4aac-9761-36dbfbebae29",
   "metadata": {},
   "source": [
    "To really have a Smart Search Engine or Virtual assistant that can answer any question about your corporate documents, this \"engine\" must understand tabular data, aka, sources with tables, rows and columns with numbers. \n",
    "This is a different problem that simply looking for the top most similar results.  The concept of indexing, bringing top results, embedding, doing a cosine semantic search and summarize an answer, doesn't really apply to this problem.\n",
    "We are dealing now with sources with Tables in which each row and column are related to each other, and in order to answer a question, all of the data is needed, not just top results.\n",
    "\n",
    "In this notebook, the goal is to show how to deal with this kind of use cases. To continue with our Covid-19 theme, we will be using an open dataset called [\"Covid Tracking Project\"](https://covidtracking.com/data/download). The COVID Tracking Project dataset is a  CSV file that provides the latest numbers on tests, confirmed cases, hospitalizations, and patient outcomes from every US state and territory (they stopped tracking on March 7 2021).\n",
    "\n",
    "Imagine that many documents on a data lake are tabular data, or that your use case is to ask questions in natural language to a LLM model and this model needs to get the context from a CSV file or even a SQL Database in order to answer the question. A GPT Smart Search Engine, must understand how to deal with this sources, understand the data and answer acoordingly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4ccab2f5-f8d3-4eb5-b1a7-961388d33d3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from langchain_openai import AzureChatOpenAI\n",
    "from langchain.agents.agent_types import AgentType\n",
    "from langchain_experimental.agents.agent_toolkits import create_csv_agent, create_pandas_dataframe_agent\n",
    "\n",
    "from common.prompts import CSV_PROMPT_PREFIX\n",
    "\n",
    "from IPython.display import Markdown, HTML, display  \n",
    "\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv(\"credentials.env\")\n",
    "\n",
    "def printmd(string):\n",
    "    display(Markdown(string))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "81a497a8-d2f4-40ef-bdd2-389c44c41a2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set the ENV variables that Langchain needs to connect to Azure OpenAI\n",
    "os.environ[\"OPENAI_API_VERSION\"] = os.environ[\"AZURE_OPENAI_API_VERSION\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd23c284-a569-4e9f-9c77-62da216be92b",
   "metadata": {},
   "source": [
    "## Download the dataset and load it into Pandas Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "09035e45-419d-4870-a297-5b5afac18d6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "os.makedirs(\"data\",exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "73bc931d-59d1-4fa7-876f-ce597a1ca153",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2024-03-15 09:41:47--  https://covidtracking.com/data/download/all-states-history.csv\n",
      "Resolving covidtracking.com (covidtracking.com)... 104.21.64.114, 172.67.183.132, 2606:4700:3032::ac43:b784, ...\n",
      "Connecting to covidtracking.com (covidtracking.com)|104.21.64.114|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: unspecified [text/csv]\n",
      "Saving to: ‘./data/all-states-history.csv’\n",
      "\n",
      "all-states-history.     [ <=>                ]   2.61M  --.-KB/s    in 0.05s   \n",
      "\n",
      "2024-03-15 09:41:48 (49.1 MB/s) - ‘./data/all-states-history.csv’ saved [2738601]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "!wget https://covidtracking.com/data/download/all-states-history.csv -P ./data/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "54c0f7eb-0ec2-44aa-b02b-8dbe1b122b28",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rows and Columns: (20780, 41)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>state</th>\n",
       "      <th>death</th>\n",
       "      <th>deathConfirmed</th>\n",
       "      <th>deathIncrease</th>\n",
       "      <th>deathProbable</th>\n",
       "      <th>hospitalized</th>\n",
       "      <th>hospitalizedCumulative</th>\n",
       "      <th>hospitalizedCurrently</th>\n",
       "      <th>hospitalizedIncrease</th>\n",
       "      <th>...</th>\n",
       "      <th>totalTestResults</th>\n",
       "      <th>totalTestResultsIncrease</th>\n",
       "      <th>totalTestsAntibody</th>\n",
       "      <th>totalTestsAntigen</th>\n",
       "      <th>totalTestsPeopleAntibody</th>\n",
       "      <th>totalTestsPeopleAntigen</th>\n",
       "      <th>totalTestsPeopleViral</th>\n",
       "      <th>totalTestsPeopleViralIncrease</th>\n",
       "      <th>totalTestsViral</th>\n",
       "      <th>totalTestsViralIncrease</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-03-07</td>\n",
       "      <td>AK</td>\n",
       "      <td>305.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1293.0</td>\n",
       "      <td>1293.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1731628.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1731628.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-03-07</td>\n",
       "      <td>AL</td>\n",
       "      <td>10148.0</td>\n",
       "      <td>7963.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>2185.0</td>\n",
       "      <td>45976.0</td>\n",
       "      <td>45976.0</td>\n",
       "      <td>494.0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2323788.0</td>\n",
       "      <td>2347</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>119757.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2323788.0</td>\n",
       "      <td>2347</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-03-07</td>\n",
       "      <td>AR</td>\n",
       "      <td>5319.0</td>\n",
       "      <td>4308.0</td>\n",
       "      <td>22</td>\n",
       "      <td>1011.0</td>\n",
       "      <td>14926.0</td>\n",
       "      <td>14926.0</td>\n",
       "      <td>335.0</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>2736442.0</td>\n",
       "      <td>3380</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>481311.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2736442.0</td>\n",
       "      <td>3380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-03-07</td>\n",
       "      <td>AS</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2140.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2140.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-03-07</td>\n",
       "      <td>AZ</td>\n",
       "      <td>16328.0</td>\n",
       "      <td>14403.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1925.0</td>\n",
       "      <td>57907.0</td>\n",
       "      <td>57907.0</td>\n",
       "      <td>963.0</td>\n",
       "      <td>44</td>\n",
       "      <td>...</td>\n",
       "      <td>7908105.0</td>\n",
       "      <td>45110</td>\n",
       "      <td>580569.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>444089.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3842945.0</td>\n",
       "      <td>14856</td>\n",
       "      <td>7908105.0</td>\n",
       "      <td>45110</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         date state    death  deathConfirmed  deathIncrease  deathProbable  \\\n",
       "0  2021-03-07    AK    305.0             0.0              0            0.0   \n",
       "1  2021-03-07    AL  10148.0          7963.0             -1         2185.0   \n",
       "2  2021-03-07    AR   5319.0          4308.0             22         1011.0   \n",
       "3  2021-03-07    AS      0.0             0.0              0            0.0   \n",
       "4  2021-03-07    AZ  16328.0         14403.0              5         1925.0   \n",
       "\n",
       "   hospitalized  hospitalizedCumulative  hospitalizedCurrently  \\\n",
       "0        1293.0                  1293.0                   33.0   \n",
       "1       45976.0                 45976.0                  494.0   \n",
       "2       14926.0                 14926.0                  335.0   \n",
       "3           0.0                     0.0                    0.0   \n",
       "4       57907.0                 57907.0                  963.0   \n",
       "\n",
       "   hospitalizedIncrease  ...  totalTestResults  totalTestResultsIncrease  \\\n",
       "0                     0  ...         1731628.0                         0   \n",
       "1                     0  ...         2323788.0                      2347   \n",
       "2                    11  ...         2736442.0                      3380   \n",
       "3                     0  ...            2140.0                         0   \n",
       "4                    44  ...         7908105.0                     45110   \n",
       "\n",
       "   totalTestsAntibody  totalTestsAntigen  totalTestsPeopleAntibody  \\\n",
       "0                 0.0                0.0                       0.0   \n",
       "1                 0.0                0.0                  119757.0   \n",
       "2                 0.0                0.0                       0.0   \n",
       "3                 0.0                0.0                       0.0   \n",
       "4            580569.0                0.0                  444089.0   \n",
       "\n",
       "   totalTestsPeopleAntigen  totalTestsPeopleViral  \\\n",
       "0                      0.0                    0.0   \n",
       "1                      0.0              2323788.0   \n",
       "2                 481311.0                    0.0   \n",
       "3                      0.0                    0.0   \n",
       "4                      0.0              3842945.0   \n",
       "\n",
       "   totalTestsPeopleViralIncrease  totalTestsViral  totalTestsViralIncrease  \n",
       "0                              0        1731628.0                        0  \n",
       "1                           2347              0.0                        0  \n",
       "2                              0        2736442.0                     3380  \n",
       "3                              0           2140.0                        0  \n",
       "4                          14856        7908105.0                    45110  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Bad pipe message: %s [b'~z9\\xc7j\\x11F\\xa0xX\\x0f\\xc7\\x83\\xd7\\xbb9\\x0f\\x1e Ar\\xf0\\x18*\\xf2\\x89\\xd6\\xdb^\\n?a\\xe0\\x10(\\x99\\xd7~\\x8e\\x80U\\xb1j\\xb5\\xcdH+\\xfb\\x16\\x9bY\\x00\\x08\\x13\\x02\\x13\\x03\\x13\\x01\\x00\\xff\\x01\\x00\\x00\\x8f\\x00\\x00\\x00\\x0e\\x00\\x0c\\x00\\x00\\t127.0.0.1\\x00\\x0b\\x00']\n",
      "Bad pipe message: %s [b\"h\\x80\\xdb\\x00lL\\xcbt\\xb8e\\xb68;e\\xd9\\x8bj\\xa4\\x00\\x00|\\xc0,\\xc00\\x00\\xa3\\x00\\x9f\\xcc\\xa9\\xcc\\xa8\\xcc\\xaa\\xc0\\xaf\\xc0\\xad\\xc0\\xa3\\xc0\\x9f\\xc0]\\xc0a\\xc0W\\xc0S\\xc0+\\xc0/\\x00\\xa2\\x00\\x9e\\xc0\\xae\\xc0\\xac\\xc0\\xa2\\xc0\\x9e\\xc0\\\\\\xc0`\\xc0V\\xc0R\\xc0$\\xc0(\\x00k\\x00j\\xc0#\\xc0'\\x00g\\x00@\\xc0\\n\\xc0\\x14\\x009\\x008\\xc0\\t\\xc0\\x13\\x003\\x002\\x00\\x9d\\xc0\\xa1\\xc0\\x9d\\xc0Q\\x00\\x9c\\xc0\\xa0\\xc0\\x9c\\xc0P\\x00=\\x00<\\x005\\x00/\\x00\\x9a\\x00\\x99\\xc0\\x07\\xc0\\x11\\x00\\x96\\x00\\x05\\x00\\xff\\x01\\x00\\x00j\\x00\\x00\\x00\\x0e\\x00\\x0c\\x00\\x00\\t127.0.0.1\\x00\\x0b\\x00\\x04\\x03\\x00\"]\n",
      "Bad pipe message: %s [b'']\n",
      "Bad pipe message: %s [b'\\xd7%y\\xa0\\xc1\\xd2\\x87p\\xae\\x9bK\\x01D\\xe9\\xa7\\xa7\\x96\\xb5\\x00\\x00>\\xc0\\x14\\xc0\\n\\x009\\x008\\x007\\x006\\xc0\\x0f\\xc0\\x05\\x005\\xc0\\x13\\xc0\\t\\x003\\x002\\x001\\x000\\xc0\\x0e\\xc0\\x04\\x00/\\x00\\x9a\\x00\\x99\\x00\\x98\\x00\\x97\\x00\\x96\\x00\\x07\\xc0\\x11\\xc0\\x07\\xc0\\x0c\\xc0\\x02\\x00\\x05\\x00\\x04\\x00\\xff\\x02\\x01\\x00\\x00C\\x00\\x00\\x00\\x0e\\x00\\x0c\\x00\\x00\\t127.0.0.']\n",
      "Bad pipe message: %s [b'\\x0b\\x00\\x04\\x03\\x00\\x01\\x02\\x00\\n\\x00\\x1c\\x00\\x1a\\x00\\x17\\x00\\x19\\x00\\x1c\\x00\\x1b\\x00\\x18\\x00\\x1a\\x00\\x16\\x00\\x0e\\x00\\r\\x00\\x0b\\x00\\x0c\\x00\\t\\x00\\n\\x00#\\x00\\x00\\x00\\x0f\\x00\\x01\\x01']\n",
      "Bad pipe message: %s [b'u\\xab\\xc0\\xb9\\xba\\xbfB\\xbd\\xf1qH\\x9a=\\xd9DE\\xa8W\\x00\\x00\\xa2\\xc0\\x14\\xc0\\n\\x009\\x008\\x007\\x006\\x00\\x88\\x00\\x87\\x00\\x86\\x00\\x85\\xc0\\x19\\x00:\\x00\\x89\\xc0\\x0f\\xc0\\x05\\x005\\x00\\x84\\xc0\\x13\\xc0\\t\\x003\\x002\\x001\\x000\\x00\\x9a\\x00\\x99\\x00\\x98\\x00\\x97\\x00E\\x00D\\x00C\\x00B\\xc0\\x18\\x004\\x00\\x9b\\x00F\\xc0\\x0e\\xc0\\x04\\x00/\\x00\\x96\\x00A\\x00\\x07\\xc0\\x11\\xc0\\x07\\xc0\\x16\\x00\\x18\\xc0\\x0c\\xc0\\x02\\x00\\x05\\x00\\x04\\xc0\\x12\\xc0\\x08\\x00\\x16\\x00\\x13\\x00\\x10\\x00\\r\\xc0\\x17\\x00\\x1b\\xc0\\r\\xc0\\x03\\x00\\n\\x00\\x15\\x00\\x12\\x00\\x0f\\x00\\x0c\\x00', b'\\t\\x00\\x14\\x00\\x11\\x00\\x19\\x00\\x08\\x00\\x06\\x00\\x17\\x00\\x03\\xc0\\x10\\xc0\\x06\\xc0\\x15\\xc0\\x0b\\xc0\\x01']\n",
      "Bad pipe message: %s [b'\\xba\\x86D\\xb1\\x7fBR\\xd0\\xed1\\xd3\\xf21\\xc3\\xdc\\xdbl\\x8c\\x00\\x00\\xa2\\xc0\\x14\\xc0\\n\\x009\\x008\\x007\\x006\\x00\\x88\\x00\\x87\\x00\\x86\\x00\\x85\\xc0\\x19\\x00:\\x00\\x89\\xc0\\x0f\\xc0\\x05\\x005\\x00\\x84\\xc0\\x13\\xc0\\t\\x003\\x002\\x001\\x000\\x00\\x9a\\x00\\x99\\x00\\x98\\x00\\x97\\x00E\\x00D\\x00C\\x00B\\xc0\\x18\\x004\\x00\\x9b\\x00F\\xc0\\x0e\\xc0\\x04\\x00/\\x00\\x96\\x00A\\x00\\x07\\xc0\\x11\\xc0\\x07\\xc0\\x16\\x00\\x18\\xc0\\x0c\\xc0\\x02\\x00\\x05\\x00\\x04\\xc0\\x12\\xc0\\x08\\x00\\x16\\x00\\x13\\x00\\x10\\x00\\r\\xc0\\x17\\x00\\x1b\\xc0\\r\\xc0\\x03\\x00\\n\\x00\\x15\\x00\\x12\\x00\\x0f\\x00\\x0c\\x00\\x1a\\x00\\t\\x00\\x14\\x00\\x11\\x00\\x19\\x00\\x08\\x00', b'\\x17\\x00\\x03\\xc0\\x10']\n"
     ]
    }
   ],
   "source": [
    "file_url = \"./data/all-states-history.csv\"\n",
    "df = pd.read_csv(file_url).fillna(value = 0)\n",
    "print(\"Rows and Columns:\",df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d703e877-0a85-43c5-ab35-8ecbe72c44c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'state', 'death', 'deathConfirmed', 'deathIncrease',\n",
       "       'deathProbable', 'hospitalized', 'hospitalizedCumulative',\n",
       "       'hospitalizedCurrently', 'hospitalizedIncrease', 'inIcuCumulative',\n",
       "       'inIcuCurrently', 'negative', 'negativeIncrease',\n",
       "       'negativeTestsAntibody', 'negativeTestsPeopleAntibody',\n",
       "       'negativeTestsViral', 'onVentilatorCumulative', 'onVentilatorCurrently',\n",
       "       'positive', 'positiveCasesViral', 'positiveIncrease', 'positiveScore',\n",
       "       'positiveTestsAntibody', 'positiveTestsAntigen',\n",
       "       'positiveTestsPeopleAntibody', 'positiveTestsPeopleAntigen',\n",
       "       'positiveTestsViral', 'recovered', 'totalTestEncountersViral',\n",
       "       'totalTestEncountersViralIncrease', 'totalTestResults',\n",
       "       'totalTestResultsIncrease', 'totalTestsAntibody', 'totalTestsAntigen',\n",
       "       'totalTestsPeopleAntibody', 'totalTestsPeopleAntigen',\n",
       "       'totalTestsPeopleViral', 'totalTestsPeopleViralIncrease',\n",
       "       'totalTestsViral', 'totalTestsViralIncrease'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21f25d06-03c3-4f73-bb9a-5a43777d1bf5",
   "metadata": {},
   "source": [
    "## CSV/Pandas Agent"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87d4c5dd-8d4b-4a7b-a108-99486582530d",
   "metadata": {},
   "source": [
    "LLMs are great for building question-answering systems over various types of data sources. In this section we’ll go over how to build Q&A systems over data stored in a CSV file(s). Like we did in the last notebook, the key to working with tabular CSV files is to give an LLM access to tools/experts for querying and interacting with the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b86deb94-a500-4187-9638-55fc64ce0115",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's delve into a challenging question that demands a multi-step solution. \n",
    "# The path to solving it might not be immediately clear.\n",
    "# When examining the dataframe above, even a human might struggle to determine which columns are pertinent.\n",
    "\n",
    "QUESTION = \"\"\"\n",
    "How may patients were hospitalized during July 2020 in Texas. \n",
    "And nationwide as the total of all states? \n",
    "Use the hospitalizedIncrease column\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "46238c2e-2eb4-4fc3-8472-b894380a5063",
   "metadata": {},
   "outputs": [],
   "source": [
    "# First we load our LLM\n",
    "COMPLETION_TOKENS = 1000\n",
    "llm = AzureChatOpenAI(deployment_name=os.environ[\"GPT35_DEPLOYMENT_NAME\"], \n",
    "                      temperature=0.5, max_tokens=COMPLETION_TOKENS)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66a4d7d9-17c9-49cc-a98a-a5c7ace0480d",
   "metadata": {},
   "source": [
    "Now we need our agent and our expert/tool.  \n",
    "LangChain has created an out-of-the-box agents that we can use to solve our Q&A to CSV tabular data file problem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2927c9d0-1980-437e-9b06-7462bb6602a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "agent_executor = create_pandas_dataframe_agent(llm=llm,\n",
    "                                               df=df,\n",
    "                                               verbose=True,\n",
    "                                               agent_type=\"openai-tools\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d6eb9727-036f-4a43-a796-7702183fc57d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `python_repl_ast` with `{'query': \"df[(df['date'].str.startswith('2020-07')) & (df['state'] == 'TX')]['hospitalizedIncrease'].sum()\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m0\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `python_repl_ast` with `{'query': \"df[df['date'].str.startswith('2020-07')]['hospitalizedIncrease'].sum()\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m63105\u001b[0m\u001b[32;1m\u001b[1;3mIn July 2020, there were 0 patients hospitalized in Texas. Nationwide, the total number of patients hospitalized in July 2020 across all states was 63,105.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "In July 2020, there were 0 patients hospitalized in Texas. Nationwide, the total number of patients hospitalized in July 2020 across all states was 63,105."
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 196 ms, sys: 19.7 ms, total: 216 ms\n",
      "Wall time: 2.76 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "try:\n",
    "    display(Markdown(agent_executor.invoke(QUESTION)['output']))\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91425cc1-0fa5-43e4-925b-4bcc20311604",
   "metadata": {},
   "source": [
    "That was good pandas code, and pretty fast. Let's add some prompt engineering to improve answer."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a57e0b30-7368-45f7-bfee-0ab997b67e02",
   "metadata": {},
   "source": [
    "### Improving the Prompt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c23eeb6d-f3f2-4f10-8db2-9ba5afb16464",
   "metadata": {},
   "source": [
    "We can give it now an improved sager prompt. In `prompts.py` you can find `CSV_PROMPT_PREFIX`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0b8ce275-0326-4a3a-82cc-985561649d46",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n- First set the pandas display options to show all the columns, get the column names, then answer the question.\\n- **ALWAYS** before giving the Final Answer, try another method. Then reflect on the answers of the two methods you did and ask yourself if it answers correctly the original question. If you are not sure, try another method.\\n- \\n- If the methods tried do not give the same result, reflect and try again until you have two methods that have the same result. \\n- If you still cannot arrive to a consistent result, say that you are not sure of the answer.\\n- If you are sure of the correct answer, create a beautiful and thorough response using Markdown.\\n- **DO NOT MAKE UP AN ANSWER OR USE PRIOR KNOWLEDGE, ONLY USE THE RESULTS OF THE CALCULATIONS YOU HAVE DONE**. \\n- **ALWAYS**, as part of your \"Final Answer\", explain how you got to the answer on a section that starts with: \"\\n\\nExplanation:\\n\". In the explanation, mention the column names that you used to get to the final answer. \\n'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSV_PROMPT_PREFIX"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d8a6e99e-8bba-4810-9e00-e734bd928537",
   "metadata": {},
   "outputs": [],
   "source": [
    "agent_executor = create_pandas_dataframe_agent(llm=llm,\n",
    "                                               df=df,\n",
    "                                               prefix=CSV_PROMPT_PREFIX,\n",
    "                                               verbose=True,\n",
    "                                               agent_type=\"openai-tools\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c76e0a0c-b615-45d9-991d-035ddb28f09f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `python_repl_ast` with `{'query': 'df.columns'}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3mIndex(['date', 'state', 'death', 'deathConfirmed', 'deathIncrease',\n",
      "       'deathProbable', 'hospitalized', 'hospitalizedCumulative',\n",
      "       'hospitalizedCurrently', 'hospitalizedIncrease', 'inIcuCumulative',\n",
      "       'inIcuCurrently', 'negative', 'negativeIncrease',\n",
      "       'negativeTestsAntibody', 'negativeTestsPeopleAntibody',\n",
      "       'negativeTestsViral', 'onVentilatorCumulative', 'onVentilatorCurrently',\n",
      "       'positive', 'positiveCasesViral', 'positiveIncrease', 'positiveScore',\n",
      "       'positiveTestsAntibody', 'positiveTestsAntigen',\n",
      "       'positiveTestsPeopleAntibody', 'positiveTestsPeopleAntigen',\n",
      "       'positiveTestsViral', 'recovered', 'totalTestEncountersViral',\n",
      "       'totalTestEncountersViralIncrease', 'totalTestResults',\n",
      "       'totalTestResultsIncrease', 'totalTestsAntibody', 'totalTestsAntigen',\n",
      "       'totalTestsPeopleAntibody', 'totalTestsPeopleAntigen',\n",
      "       'totalTestsPeopleViral', 'totalTestsPeopleViralIncrease',\n",
      "       'totalTestsViral', 'totalTestsViralIncrease'],\n",
      "      dtype='object')\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `python_repl_ast` with `{'query': 'df.head()'}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m         date state    death  deathConfirmed  deathIncrease  deathProbable  \\\n",
      "0  2021-03-07    AK    305.0             0.0              0            0.0   \n",
      "1  2021-03-07    AL  10148.0          7963.0             -1         2185.0   \n",
      "2  2021-03-07    AR   5319.0          4308.0             22         1011.0   \n",
      "3  2021-03-07    AS      0.0             0.0              0            0.0   \n",
      "4  2021-03-07    AZ  16328.0         14403.0              5         1925.0   \n",
      "\n",
      "   hospitalized  hospitalizedCumulative  hospitalizedCurrently  \\\n",
      "0        1293.0                  1293.0                   33.0   \n",
      "1       45976.0                 45976.0                  494.0   \n",
      "2       14926.0                 14926.0                  335.0   \n",
      "3           0.0                     0.0                    0.0   \n",
      "4       57907.0                 57907.0                  963.0   \n",
      "\n",
      "   hospitalizedIncrease  inIcuCumulative  inIcuCurrently   negative  \\\n",
      "0                     0              0.0             0.0        0.0   \n",
      "1                     0           2676.0             0.0  1931711.0   \n",
      "2                    11              0.0           141.0  2480716.0   \n",
      "3                     0              0.0             0.0     2140.0   \n",
      "4                    44              0.0           273.0  3073010.0   \n",
      "\n",
      "   negativeIncrease  negativeTestsAntibody  negativeTestsPeopleAntibody  \\\n",
      "0                 0                    0.0                          0.0   \n",
      "1              2087                    0.0                          0.0   \n",
      "2              3267                    0.0                          0.0   \n",
      "3                 0                    0.0                          0.0   \n",
      "4             13678                    0.0                          0.0   \n",
      "\n",
      "   negativeTestsViral  onVentilatorCumulative  onVentilatorCurrently  \\\n",
      "0           1660758.0                     0.0                    2.0   \n",
      "1                 0.0                  1515.0                    0.0   \n",
      "2           2480716.0                  1533.0                   65.0   \n",
      "3                 0.0                     0.0                    0.0   \n",
      "4                 0.0                     0.0                  143.0   \n",
      "\n",
      "   positive  positiveCasesViral  positiveIncrease  positiveScore  \\\n",
      "0   56886.0                 0.0                 0              0   \n",
      "1  499819.0            392077.0               408              0   \n",
      "2  324818.0            255726.0               165              0   \n",
      "3       0.0                 0.0                 0              0   \n",
      "4  826454.0            769935.0              1335              0   \n",
      "\n",
      "   positiveTestsAntibody  positiveTestsAntigen  positiveTestsPeopleAntibody  \\\n",
      "0                    0.0                   0.0                          0.0   \n",
      "1                    0.0                   0.0                          0.0   \n",
      "2                    0.0                   0.0                          0.0   \n",
      "3                    0.0                   0.0                          0.0   \n",
      "4                    0.0                   0.0                          0.0   \n",
      "\n",
      "   positiveTestsPeopleAntigen  positiveTestsViral  recovered  \\\n",
      "0                         0.0             68693.0        0.0   \n",
      "1                         0.0                 0.0   295690.0   \n",
      "2                     81803.0                 0.0   315517.0   \n",
      "3                         0.0                 0.0        0.0   \n",
      "4                         0.0                 0.0        0.0   \n",
      "\n",
      "   totalTestEncountersViral  totalTestEncountersViralIncrease  \\\n",
      "0                       0.0                                 0   \n",
      "1                       0.0                                 0   \n",
      "2                       0.0                                 0   \n",
      "3                       0.0                                 0   \n",
      "4                       0.0                                 0   \n",
      "\n",
      "   totalTestResults  totalTestResultsIncrease  totalTestsAntibody  \\\n",
      "0         1731628.0                         0                 0.0   \n",
      "1         2323788.0                      2347                 0.0   \n",
      "2         2736442.0                      3380                 0.0   \n",
      "3            2140.0                         0                 0.0   \n",
      "4         7908105.0                     45110            580569.0   \n",
      "\n",
      "   totalTestsAntigen  totalTestsPeopleAntibody  totalTestsPeopleAntigen  \\\n",
      "0                0.0                       0.0                      0.0   \n",
      "1                0.0                  119757.0                      0.0   \n",
      "2                0.0                       0.0                 481311.0   \n",
      "3                0.0                       0.0                      0.0   \n",
      "4                0.0                  444089.0                      0.0   \n",
      "\n",
      "   totalTestsPeopleViral  totalTestsPeopleViralIncrease  totalTestsViral  \\\n",
      "0                    0.0                              0        1731628.0   \n",
      "1              2323788.0                           2347              0.0   \n",
      "2                    0.0                              0        2736442.0   \n",
      "3                    0.0                              0           2140.0   \n",
      "4              3842945.0                          14856        7908105.0   \n",
      "\n",
      "   totalTestsViralIncrease  \n",
      "0                        0  \n",
      "1                        0  \n",
      "2                     3380  \n",
      "3                        0  \n",
      "4                    45110  \u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `python_repl_ast` with `{'query': \"df.loc[(df['date'].str.contains('2020-07')) & (df['state'] == 'TX'), 'hospitalizedIncrease'].sum()\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m0\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `python_repl_ast` with `{'query': \"df.loc[df['date'].str.contains('2020-07'), 'hospitalizedIncrease'].sum()\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m63105\u001b[0m\u001b[32;1m\u001b[1;3mThe number of patients hospitalized during July 2020 in Texas is 0, and nationwide the total hospitalized patients for all states during July 2020 is 63,105.\n",
      "\n",
      "Explanation:\n",
      "- To find the number of hospitalized patients in Texas during July 2020, we filtered the dataset for rows where the date contains '2020-07' and the state is 'TX'. Then we summed the values in the 'hospitalizedIncrease' column for Texas, which resulted in 0 hospitalizations.\n",
      "- To find the nationwide total of hospitalized patients for all states during July 2020, we filtered the dataset for rows where the date contains '2020-07' and summed the values in the 'hospitalizedIncrease' column, resulting in 63,105 hospitalizations.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "The number of patients hospitalized during July 2020 in Texas is 0, and nationwide the total hospitalized patients for all states during July 2020 is 63,105.\n",
       "\n",
       "Explanation:\n",
       "- To find the number of hospitalized patients in Texas during July 2020, we filtered the dataset for rows where the date contains '2020-07' and the state is 'TX'. Then we summed the values in the 'hospitalizedIncrease' column for Texas, which resulted in 0 hospitalizations.\n",
       "- To find the nationwide total of hospitalized patients for all states during July 2020, we filtered the dataset for rows where the date contains '2020-07' and summed the values in the 'hospitalizedIncrease' column, resulting in 63,105 hospitalizations."
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 488 ms, sys: 4.79 ms, total: 492 ms\n",
      "Wall time: 5.31 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "try:\n",
    "    display(Markdown(agent_executor.invoke(QUESTION)['output']))\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f732d941-e206-445b-a52c-b454398afba4",
   "metadata": {},
   "source": [
    "## Evaluation\n",
    "Let's see if the answer is correct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "42209997-aa2a-4b97-b94b-a203bc4c6096",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df['date'] = pd.to_datetime(df['date'])\n",
    "july_2020 = df[(df['date'] >= '2020-07-01') & (df['date'] <= '2020-07-31')]\n",
    "texas_hospitalized_july_2020 = july_2020[july_2020['state'] == 'TX']['hospitalizedIncrease'].sum()\n",
    "nationwide_hospitalized_july_2020 = july_2020['hospitalizedIncrease'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "349c3020-3383-4ad3-83a4-07c1ead1207d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TX: 0 Nationwide: 63105\n"
     ]
    }
   ],
   "source": [
    "print( \"TX:\",texas_hospitalized_july_2020,\"Nationwide:\",nationwide_hospitalized_july_2020)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49988cb5-719c-4180-8ac5-0afa44018b50",
   "metadata": {},
   "source": [
    "It is Correct!\n",
    "\n",
    "**Note**: Obviously, there were hospitalizations in Texas in July 2020 (Try asking ChatGPT), but this particular File, for some reason has 0 on the column \"hospitalizedIncrease\" for Texas in July 2020. This proves though that the model is NOT making up information or using prior knowledge, but instead using only the results of its calculation on this CSV file. That's what we need!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "073913d5-321b-4c56-9c66-649266cf6280",
   "metadata": {},
   "source": [
    "# Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41108384-c132-45fc-92e4-31dc1bcf00c0",
   "metadata": {},
   "source": [
    "So, we just solved our problem on how to ask questions in natural language to our Tabular data hosted on a CSV File.\n",
    "With this approach you can see then that it is NOT necessary to make a dump of a database data into a CSV file and index that on a Search Engine, you don't even need to use the above approach and deal with a CSV data dump file. With the Agents framework, the best engineering decision is to interact directly with the data source API without the need to replicate the data in order to ask questions to it. Remember, LLMs can do SQL very well. \n",
    "\n",
    "\n",
    "**Note**: We don't recommend using a pandas agent to answer questions from tabular data. It is not fast and it makes too many parsing mistakes. We recommend using SQL (see next notebook)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88f769ab-db90-48f5-a6b9-60fc0a4a854f",
   "metadata": {},
   "source": [
    "# NEXT\n",
    "We can see that GPT-3.5, and even better GPT-4, is powerful and can translate a natural language question into the right steps in python in order to query a CSV data loaded into a pandas dataframe. \n",
    "That's pretty amazing. However the question remains: **Do I need then to dump all the data from my original sources (Databases, ERP Systems, CRM Systems) in order to be searchable by a Smart Search Engine?**\n",
    "\n",
    "The next Notebook answers this question by implementing a Question->SQL process and get the information from data in a SQL Database, eliminating the need to dump it out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f8007b1-9cf2-4a37-8183-da6e9bc8fef1",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10 - SDK v2",
   "language": "python",
   "name": "python310-sdkv2"
  },
  "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}