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": {
    "tags": []
   },
   "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": {
    "tags": []
   },
   "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": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "os.makedirs(\"data\",exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "73bc931d-59d1-4fa7-876f-ce597a1ca153",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2024-10-04 04:04:44--  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.06s   \n",
      "\n",
      "2024-10-04 04:04:44 (41.9 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": {
    "tags": []
   },
   "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": {
    "tags": []
   },
   "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": {
    "tags": []
   },
   "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": 9,
   "id": "46238c2e-2eb4-4fc3-8472-b894380a5063",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# First we load our LLM\n",
    "COMPLETION_TOKENS = 1000\n",
    "llm = AzureChatOpenAI(deployment_name=os.environ[\"GPT4oMINI_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": {
    "tags": []
   },
   "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": {
    "tags": []
   },
   "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': \"# First, we will filter the dataframe for Texas and for July 2020\\ntexas_july_hospitalized = df[(df['state'] == 'TX') & (df['date'] >= '2020-07-01') & (df['date'] <= '2020-07-31')]\\n# Summing the hospitalizedIncrease for Texas\\ntexas_hospitalized_total = texas_july_hospitalized['hospitalizedIncrease'].sum()\\ntexas_hospitalized_total\"}`\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, we will filter the dataframe for all states for July 2020\\nnationwide_july_hospitalized = df[(df['date'] >= '2020-07-01') & (df['date'] <= '2020-07-31')]\\n# Summing the hospitalizedIncrease for all states\\nnationwide_hospitalized_total = nationwide_july_hospitalized['hospitalizedIncrease'].sum()\\nnationwide_hospitalized_total\"}`\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, while the total number of hospitalized patients nationwide 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, while the total number of hospitalized patients nationwide 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 219 ms, sys: 4.28 ms, total: 223 ms\n",
      "Wall time: 2.38 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": {
    "tags": []
   },
   "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": {
    "tags": []
   },
   "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": {
    "tags": []
   },
   "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\\n\\n# Assuming df is already defined and contains the data\\n# Filter the data for Texas and July 2020\\ntexas_hospitalized_july = df[(df['state'] == 'TX') & (df['date'].str.startswith('2020-07'))]['hospitalizedIncrease'].sum()\\n\\n# Filter the data for all states and July 2020\\nnationwide_hospitalized_july = df[df['date'].str.startswith('2020-07')]['hospitalizedIncrease'].sum()\\n\\ntexas_hospitalized_july, nationwide_hospitalized_july\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m(0, 63105)\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `python_repl_ast` with `{'query': '# Check the column names to ensure they are correct\\ncolumn_names = df.columns.tolist()\\ncolumn_names'}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m['date', 'state', 'death', 'deathConfirmed', 'deathIncrease', 'deathProbable', 'hospitalized', 'hospitalizedCumulative', 'hospitalizedCurrently', 'hospitalizedIncrease', 'inIcuCumulative', 'inIcuCurrently', 'negative', 'negativeIncrease', 'negativeTestsAntibody', 'negativeTestsPeopleAntibody', 'negativeTestsViral', 'onVentilatorCumulative', 'onVentilatorCurrently', 'positive', 'positiveCasesViral', 'positiveIncrease', 'positiveScore', 'positiveTestsAntibody', 'positiveTestsAntigen', 'positiveTestsPeopleAntibody', 'positiveTestsPeopleAntigen', 'positiveTestsViral', 'recovered', 'totalTestEncountersViral', 'totalTestEncountersViralIncrease', 'totalTestResults', 'totalTestResultsIncrease', 'totalTestsAntibody', 'totalTestsAntigen', 'totalTestsPeopleAntibody', 'totalTestsPeopleAntigen', 'totalTestsPeopleViral', 'totalTestsPeopleViralIncrease', 'totalTestsViral', 'totalTestsViralIncrease']\u001b[0m\u001b[32;1m\u001b[1;3m### Results\n",
      "\n",
      "- **Hospitalized patients in Texas during July 2020:** 0\n",
      "- **Total hospitalized patients nationwide during July 2020:** 63,105\n",
      "\n",
      "### Explanation:\n",
      "To arrive at these results, I utilized the `hospitalizedIncrease` column from the DataFrame. \n",
      "\n",
      "1. **Texas Data:** I filtered the DataFrame for entries where the `state` was 'TX' and the `date` started with '2020-07'. I then summed the values in the `hospitalizedIncrease` column, which resulted in **0** hospitalized patients for Texas.\n",
      "\n",
      "2. **Nationwide Data:** I filtered the DataFrame for all states where the `date` started with '2020-07' and summed the `hospitalizedIncrease` column. This gave a total of **63,105** hospitalized patients across all states for that month.\n",
      "\n",
      "Both methods were consistent in their approach, leading to a clear understanding of the hospitalization data for July 2020.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "### Results\n",
       "\n",
       "- **Hospitalized patients in Texas during July 2020:** 0\n",
       "- **Total hospitalized patients nationwide during July 2020:** 63,105\n",
       "\n",
       "### Explanation:\n",
       "To arrive at these results, I utilized the `hospitalizedIncrease` column from the DataFrame. \n",
       "\n",
       "1. **Texas Data:** I filtered the DataFrame for entries where the `state` was 'TX' and the `date` started with '2020-07'. I then summed the values in the `hospitalizedIncrease` column, which resulted in **0** hospitalized patients for Texas.\n",
       "\n",
       "2. **Nationwide Data:** I filtered the DataFrame for all states where the `date` started with '2020-07' and summed the `hospitalizedIncrease` column. This gave a total of **63,105** hospitalized patients across all states for that month.\n",
       "\n",
       "Both methods were consistent in their approach, leading to a clear understanding of the hospitalization data for July 2020."
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 278 ms, sys: 2.36 ms, total: 280 ms\n",
      "Wall time: 3.07 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": {
    "tags": []
   },
   "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": {
    "tags": []
   },
   "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.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}