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-07-10 04:28:44--  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:3034::6815:4072, ...\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.3’\n",
      "\n",
      "all-states-history.     [ <=>                ]   2.61M  --.-KB/s    in 0.03s   \n",
      "\n",
      "2024-07-10 04:28:44 (93.4 MB/s) - ‘./data/all-states-history.csv.3’ 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": [
    "## 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[\"GPT4o_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": 10,
   "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\",\n",
    "                                               allow_dangerous_code=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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['state'] == 'TX') & (df['date'] >= '2020-07-01') & (df['date'] <= '2020-07-31')]['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'] >= '2020-07-01') & (df['date'] <= '2020-07-31')]['hospitalizedIncrease'].sum()\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m63105\u001b[0m\u001b[32;1m\u001b[1;3mDuring July 2020, there were 0 patients hospitalized in Texas according to the `hospitalizedIncrease` column. \n",
      "\n",
      "Nationwide, the total number of patients hospitalized during the same period was 63,105.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "During July 2020, there were 0 patients hospitalized in Texas according to the `hospitalizedIncrease` column. \n",
       "\n",
       "Nationwide, the total number of patients hospitalized during the same period was 63,105."
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 188 ms, sys: 8.42 ms, total: 196 ms\n",
      "Wall time: 7.97 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": 12,
   "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- 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": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSV_PROMPT_PREFIX"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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\",\n",
    "                                               allow_dangerous_code=True)"
   ]
  },
  {
   "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': \"import pandas as pd\\npd.set_option('display.max_columns', None)\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m\u001b[0m\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': \"# First, let's filter the dataframe for the month of July 2020 and state of Texas\\ntx_july_2020 = df[(df['state'] == 'TX') & (df['date'] >= '2020-07-01') & (df['date'] <= '2020-07-31')]\\n\\n# Sum the hospitalizedIncrease for Texas in July 2020\\nhospitalized_tx_july_2020 = tx_july_2020['hospitalizedIncrease'].sum()\\n\\nhospitalized_tx_july_2020\"}`\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': \"# Now, let's filter the dataframe for the month of July 2020 nationwide\\njuly_2020 = df[(df['date'] >= '2020-07-01') & (df['date'] <= '2020-07-31')]\\n\\n# Sum the hospitalizedIncrease nationwide in July 2020\\nhospitalized_nationwide_july_2020 = july_2020['hospitalizedIncrease'].sum()\\n\\nhospitalized_nationwide_july_2020\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m63105\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `python_repl_ast` with `{'query': \"# Let's try another method to verify the results\\n# First, filter for Texas in July 2020 again\\ntx_july_2020 = df[(df['state'] == 'TX') & (df['date'] >= '2020-07-01') & (df['date'] <= '2020-07-31')]\\n\\n# Check the hospitalizedIncrease column to ensure it's consistent\\ntx_july_2020['hospitalizedIncrease']\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m12311    0\n",
      "12367    0\n",
      "12423    0\n",
      "12479    0\n",
      "12535    0\n",
      "12591    0\n",
      "12647    0\n",
      "12703    0\n",
      "12759    0\n",
      "12815    0\n",
      "12871    0\n",
      "12927    0\n",
      "12983    0\n",
      "13039    0\n",
      "13095    0\n",
      "13151    0\n",
      "13207    0\n",
      "13263    0\n",
      "13319    0\n",
      "13375    0\n",
      "13431    0\n",
      "13487    0\n",
      "13543    0\n",
      "13599    0\n",
      "13655    0\n",
      "13711    0\n",
      "13767    0\n",
      "13823    0\n",
      "13879    0\n",
      "13935    0\n",
      "13991    0\n",
      "Name: hospitalizedIncrease, dtype: int64\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `python_repl_ast` with `{'query': \"# Now, filter for nationwide in July 2020 again\\njuly_2020 = df[(df['date'] >= '2020-07-01') & (df['date'] <= '2020-07-31')]\\n\\n# Check the hospitalizedIncrease column to ensure it's consistent\\njuly_2020['hospitalizedIncrease']\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m12264      4\n",
      "12265    451\n",
      "12266    105\n",
      "12267      0\n",
      "12268     88\n",
      "        ... \n",
      "13995      0\n",
      "13996     38\n",
      "13997     36\n",
      "13998      0\n",
      "13999      0\n",
      "Name: hospitalizedIncrease, Length: 1736, dtype: int64\u001b[0m\u001b[32;1m\u001b[1;3m### Number of Patients Hospitalized in Texas and Nationwide during July 2020\n",
      "\n",
      "During the month of July 2020, the number of patients hospitalized in Texas and nationwide can be determined using the `hospitalizedIncrease` column from the dataset.\n",
      "\n",
      "#### Texas\n",
      "After filtering the data for Texas (`state == 'TX'`) and the date range for July 2020, the sum of the `hospitalizedIncrease` column is 0. This means that there were no new hospitalizations reported in Texas during this period according to the given data.\n",
      "\n",
      "#### Nationwide\n",
      "For the nationwide data, after filtering for the date range of July 2020, the sum of the `hospitalizedIncrease` column is 63,105. This represents the total number of new hospitalizations across all states during July 2020.\n",
      "\n",
      "### Final Answer:\n",
      "- **Texas (July 2020):** 0 new hospitalizations\n",
      "- **Nationwide (July 2020):** 63,105 new hospitalizations\n",
      "\n",
      "#### Explanation:\n",
      "To arrive at these results, I performed the following steps:\n",
      "1. **Set display options:** Ensured all columns are visible.\n",
      "2. **Filtered Data:**\n",
      "   - For Texas, I filtered the dataframe for rows where `state == 'TX'` and the date range was between `2020-07-01` and `2020-07-31`.\n",
      "   - For nationwide data, I filtered the dataframe for rows where the date range was between `2020-07-01` and `2020-07-31`.\n",
      "3. **Summed the `hospitalizedIncrease` column:** \n",
      "   - For Texas, the sum was 0.\n",
      "   - For nationwide, the sum was 63,105.\n",
      "\n",
      "Both methods consistently showed that Texas had no new hospitalizations reported for July 2020, while the nationwide total was 63,105.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "### Number of Patients Hospitalized in Texas and Nationwide during July 2020\n",
       "\n",
       "During the month of July 2020, the number of patients hospitalized in Texas and nationwide can be determined using the `hospitalizedIncrease` column from the dataset.\n",
       "\n",
       "#### Texas\n",
       "After filtering the data for Texas (`state == 'TX'`) and the date range for July 2020, the sum of the `hospitalizedIncrease` column is 0. This means that there were no new hospitalizations reported in Texas during this period according to the given data.\n",
       "\n",
       "#### Nationwide\n",
       "For the nationwide data, after filtering for the date range of July 2020, the sum of the `hospitalizedIncrease` column is 63,105. This represents the total number of new hospitalizations across all states during July 2020.\n",
       "\n",
       "### Final Answer:\n",
       "- **Texas (July 2020):** 0 new hospitalizations\n",
       "- **Nationwide (July 2020):** 63,105 new hospitalizations\n",
       "\n",
       "#### Explanation:\n",
       "To arrive at these results, I performed the following steps:\n",
       "1. **Set display options:** Ensured all columns are visible.\n",
       "2. **Filtered Data:**\n",
       "   - For Texas, I filtered the dataframe for rows where `state == 'TX'` and the date range was between `2020-07-01` and `2020-07-31`.\n",
       "   - For nationwide data, I filtered the dataframe for rows where the date range was between `2020-07-01` and `2020-07-31`.\n",
       "3. **Summed the `hospitalizedIncrease` column:** \n",
       "   - For Texas, the sum was 0.\n",
       "   - For nationwide, the sum was 63,105.\n",
       "\n",
       "Both methods consistently showed that Texas had no new hospitalizations reported for July 2020, while the nationwide total was 63,105."
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.07 s, sys: 10.4 ms, total: 1.08 s\n",
      "Wall time: 34.3 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
}