06-TabularDataQA.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "45b4b5a4-96b0-4935-b890-8eb1c3d678ad",
   "metadata": {},
   "source": [
    "# Q&A against Tabular Data from a CSV file  (experimental)"
   ]
  },
  {
   "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.chat_models import AzureChatOpenAI\n",
    "from langchain_experimental.agents.agent_toolkits import create_pandas_dataframe_agent\n",
    "from langchain_experimental.agents.agent_toolkits import create_csv_agent\n",
    "\n",
    "from common.prompts import CSV_PROMPT_PREFIX, CSV_PROMPT_SUFFIX\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": [
      "--2023-12-08 05:28:27--  https://covidtracking.com/data/download/all-states-history.csv\n",
      "Resolving covidtracking.com (covidtracking.com)... 172.67.183.132, 104.21.64.114, 2606:4700:3032::ac43:b784, ...\n",
      "Connecting to covidtracking.com (covidtracking.com)|172.67.183.132|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: unspecified [text/csv]\n",
      "Saving to: ‘./data/all-states-history.csv.1’\n",
      "\n",
      "all-states-history.     [ <=>                ]   2.61M  --.-KB/s    in 0.07s   \n",
      "\n",
      "2023-12-08 05:28:27 (37.8 MB/s) - ‘./data/all-states-history.csv.1’ 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"
    }
   ],
   "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": [
    "## Introducing: Agents"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87d4c5dd-8d4b-4a7b-a108-99486582530d",
   "metadata": {},
   "source": [
    "The implementation of Agents is inspired by two papers: the [MRKL Systems](https://arxiv.org/abs/2205.00445) paper (pronounced ‘miracle’ 😉) and the [ReAct](https://arxiv.org/abs/2210.03629) paper.\n",
    "\n",
    "Agents are a way to leverage the ability of LLMs to understand and act on prompts. In essence, an Agent is an LLM that has been given a very clever initial prompt. The prompt tells the LLM to break down the process of answering a complex query into a sequence of steps that are resolved one at a time.\n",
    "\n",
    "Agents become really cool when we combine them with ‘experts’, introduced in the MRKL paper. Simple example: an Agent might not have the inherent capability to reliably perform mathematical calculations by itself. However, we can introduce an expert - in this case a calculator, an expert at mathematical calculations. Now, when we need to perform a calculation, the Agent can call in the expert rather than trying to predict the result itself. This is actually the concept behind [ChatGPT Pluggins](https://openai.com/blog/chatgpt-plugins).\n",
    "\n",
    "In our case, in order to solve the problem \"How do I ask questions to a tabular CSV file\", we need this REACT/MRKL approach, in which we need to instruct the LLM that it needs to use an 'expert/tool' in order to read/load/understand/interact with a CSV tabular file.\n",
    "\n",
    "OpenAI opened the world to a whole new concept. Libraries are being created fast and furious. We will be using [LangChain](https://docs.langchain.com/docs/) as our library to solve this problem, however there are others that we recommend: [HayStack](https://haystack.deepset.ai/) and [Semantic Kernel](https://learn.microsoft.com/en-us/semantic-kernel/whatissk)."
   ]
  },
  {
   "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. 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 = \"How may patients were hospitalized during July 2020 in Texas, and nationwide as the total of all states? Use the hospitalizedIncrease column\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "46238c2e-2eb4-4fc3-8472-b894380a5063",
   "metadata": {},
   "outputs": [],
   "source": [
    "# First we load our LLM: GPT-4 (you are welcome to try GPT-3.5-Turbo. You will see that GPT-3.5 \n",
    "# does not have the cognitive capabilities to solve a complex question and will make mistakes)\n",
    "llm = AzureChatOpenAI(deployment_name=\"gpt-4\", temperature=0, max_tokens=500)"
   ]
  },
  {
   "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. For more informatio about tje **CSV Agent** click [HERE](https://python.langchain.com/en/latest/modules/agents/toolkits/examples/csv.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2927c9d0-1980-437e-9b06-7462bb6602a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "agent_executor = create_pandas_dataframe_agent(llm=llm,df=df,verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "44a7b5bf-7601-4b4c-b76f-a8a64dda7c39",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['python_repl_ast']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agent_executor.agent.allowed_tools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "904e0276-78a2-4555-96ce-ece5a99e4db1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "\n",
       "You are working with a pandas dataframe in Python. The name of the dataframe is `df`.\n",
       "You should use the tools below to answer the question posed of you:\n",
       "\n",
       "python_repl_ast: A Python shell. Use this to execute python commands. Input should be a valid python command. When using this tool, sometimes output is abbreviated - make sure it does not look abbreviated before using it in your answer.\n",
       "\n",
       "Use the following format:\n",
       "\n",
       "Question: the input question you must answer\n",
       "Thought: you should always think about what to do\n",
       "Action: the action to take, should be one of [python_repl_ast]\n",
       "Action Input: the input to the action\n",
       "Observation: the result of the action\n",
       "... (this Thought/Action/Action Input/Observation can repeat N times)\n",
       "Thought: I now know the final answer\n",
       "Final Answer: the final answer to the original input question\n",
       "\n",
       "\n",
       "This is the result of `print(df.head())`:\n",
       "{df_head}\n",
       "\n",
       "Begin!\n",
       "Question: {input}\n",
       "{agent_scratchpad}"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "printmd(agent_executor.agent.llm_chain.prompt.template)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d0220e2-9b3f-467e-9843-7a27a09fd39b",
   "metadata": {},
   "source": [
    "## Enjoy the response and the power of GPT-4 + REACT/MKRL approach"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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;3mThought: \n",
      "To answer this question, I need to filter the dataframe for the month of July 2020 and then sum the 'hospitalizedIncrease' column for the state of Texas and for all states. \n",
      "\n",
      "Action: python_repl_ast\n",
      "Action Input: \n",
      "```python\n",
      "# Import necessary libraries\n",
      "import pandas as pd\n",
      "\n",
      "# Set pandas display options\n",
      "pd.set_option('display.max_columns', None)\n",
      "\n",
      "# Print column names\n",
      "print(df.columns)\n",
      "```\u001b[0m\n",
      "Observation: \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')\n",
      "\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe 'hospitalizedIncrease' column is present in the dataframe. Now, I need to filter the dataframe for the month of July 2020 and then sum the 'hospitalizedIncrease' column for the state of Texas and for all states.\n",
      "\n",
      "Action: python_repl_ast\n",
      "Action Input: \n",
      "```python\n",
      "# Convert 'date' column to datetime\n",
      "df['date'] = pd.to_datetime(df['date'])\n",
      "\n",
      "# Filter for July 2020\n",
      "df_july_2020 = df[(df['date'] >= '2020-07-01') & (df['date'] <= '2020-07-31')]\n",
      "\n",
      "# Calculate the sum of 'hospitalizedIncrease' for Texas\n",
      "tx_hospitalized = df_july_2020[df_july_2020['state'] == 'TX']['hospitalizedIncrease'].sum()\n",
      "\n",
      "# Calculate the sum of 'hospitalizedIncrease' for all states\n",
      "all_states_hospitalized = df_july_2020['hospitalizedIncrease'].sum()\n",
      "\n",
      "tx_hospitalized, all_states_hospitalized\n",
      "```\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m(0, 63105)\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe number of patients hospitalized in Texas during July 2020 is 0 and the total number of patients hospitalized nationwide during the same period is 63105. \n",
      "\n",
      "To confirm these results, I will try another method. I will group the dataframe by state and then sum the 'hospitalizedIncrease' column. \n",
      "\n",
      "Action: python_repl_ast\n",
      "Action Input: \n",
      "```python\n",
      "# Group by state and sum 'hospitalizedIncrease'\n",
      "statewise_hospitalized = df_july_2020.groupby('state')['hospitalizedIncrease'].sum()\n",
      "\n",
      "# Get the sum for Texas and for all states\n",
      "tx_hospitalized_2 = statewise_hospitalized['TX']\n",
      "all_states_hospitalized_2 = statewise_hospitalized.sum()\n",
      "\n",
      "tx_hospitalized_2, all_states_hospitalized_2\n",
      "```\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m(0, 63105)\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mThe results from the second method match the results from the first method. Therefore, I am confident that the number of patients hospitalized in Texas during July 2020 is 0 and the total number of patients hospitalized nationwide during the same period is 63105.\n",
      "\n",
      "Final Answer: The number of patients hospitalized in Texas during July 2020 is 0 and the total number of patients hospitalized nationwide during the same period is 63105.\n",
      "\n",
      "Explanation:\n",
      "I used the 'hospitalizedIncrease' column to calculate the number of patients hospitalized. I first filtered the dataframe for the month of July 2020. Then, I summed the 'hospitalizedIncrease' column for the state of Texas and for all states. I confirmed these results by grouping the dataframe by state and summing the 'hospitalizedIncrease' column. The results from both methods matched, confirming the final answer.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "The number of patients hospitalized in Texas during July 2020 is 0 and the total number of patients hospitalized nationwide during the same period is 63105.\n",
      "\n",
      "Explanation:\n",
      "I used the 'hospitalizedIncrease' column to calculate the number of patients hospitalized. I first filtered the dataframe for the month of July 2020. Then, I summed the 'hospitalizedIncrease' column for the state of Texas and for all states. I confirmed these results by grouping the dataframe by state and summing the 'hospitalizedIncrease' column. The results from both methods matched, confirming the final answer.\n"
     ]
    }
   ],
   "source": [
    "# We are doing a for loop to retry N times. This is because: \n",
    "# 1) GPT-4 is still in preview and the API is being very throttled and \n",
    "# 2) Because the LLM not always gives the answer on the exact format the agent needs and hence cannot be parsed\n",
    "\n",
    "for i in range(5):\n",
    "    try:\n",
    "        response = agent_executor.run(CSV_PROMPT_PREFIX + QUESTION + CSV_PROMPT_SUFFIX) \n",
    "        break\n",
    "    except:\n",
    "        response = \"Error too many failed retries\"\n",
    "        continue\n",
    "        \n",
    "print(response)"
   ]
  },
  {
   "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": 13,
   "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": 14,
   "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!\n",
    "\n",
    "**Note 2**: You will also notice that if you run the above cell multiple times, not always you will get the same result. Sometimes it will even fail an error out. Why? \n",
    "1) This is still a very new field and LLMs and libraries still has a lot room to grow\n",
    "2) Because for complex questions that require multiple steps to solve it, even humans make mistakes\n",
    "3) Because if the column names are not clear, or ambiguous, or the data is not clean, it will make mistakes, just as humans would."
   ]
  },
  {
   "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, GPT-4 can do SQL very well. \n",
    "\n",
    "Just think about this: if GPT-4 can do the above, imagine what GPT-5/6/7/8 will be able to do.\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": "69e074a0-4f46-40c7-9567-7058ba103f5b",
   "metadata": {},
   "source": [
    "# Reference\n",
    "\n",
    "- https://haystack.deepset.ai/blog/introducing-haystack-agents\n",
    "- https://python.langchain.com/en/latest/modules/agents/agents.html\n",
    "- https://tsmatz.wordpress.com/2023/03/07/react-with-openai-gpt-and-langchain/\n",
    "- https://medium.com/@meghanheintz/intro-to-langchain-llm-templates-and-agents-8793f30f1837"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88f769ab-db90-48f5-a6b9-60fc0a4a854f",
   "metadata": {},
   "source": [
    "# NEXT\n",
    "We can see that 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."
   ]
  }
 ],
 "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
}