langchain-csv-agent.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this attempt I am using a csv agent. It translates the query to pandas query format. It works pretty well but it costs a lot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "queries = [\"The youngest.\",\"how many germans?\", \"Tell me the driver name with oldest birthdate.\", \"driver uses the number 14\",\"german and less than 25 years old\",\"uses the number 14 and less than 20 years\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "text-davinci-003\n",
      "\n",
      "\n",
      "\u001b[1m> Entering new  chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mThought: I need to find the driver with the youngest birthdate.\n",
      "Action: python_repl_ast\n",
      "Action Input: df.sort_values('birthdate').head(1)\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m     driverId       name  number code  forename    surname   birthdate  \\\n",
      "741       741  etancelin     NaN  NaN  Philippe  Étancelin  1896-12-28   \n",
      "\n",
      "    nationality  \n",
      "741      French  \u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer.\n",
      "Final Answer: Philippe Étancelin is the youngest driver.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "\n",
      "\n",
      "\u001b[1m> Entering new  chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mThought: I need to filter the dataframe to only include rows with a nationality of German\n",
      "Action: python_repl_ast\n",
      "Action Input: df[df['nationality'] == 'German'].shape[0]\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m50\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
      "Final Answer: 50\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "\n",
      "\n",
      "\u001b[1m> Entering new  chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mThought: I need to sort the dataframe by birthdate.\n",
      "Action: python_repl_ast\n",
      "Action Input: df.sort_values(by='birthdate', ascending=True)\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m     driverId       name  number code  forename    surname   birthdate  \\\n",
      "741       741  etancelin     NaN  NaN  Philippe  Étancelin  1896-12-28   \n",
      "786       786    fagioli     NaN  NaN     Luigi    Fagioli  1898-06-09   \n",
      "791       791  biondetti     NaN  NaN  Clemente  Biondetti  1898-08-18   \n",
      "703       703      legat     NaN  NaN    Arthur      Legat  1898-11-01   \n",
      "589       589     chiron     NaN  NaN     Louis     Chiron  1899-08-03   \n",
      "..        ...        ...     ...  ...       ...        ...         ...   \n",
      "853       855       zhou    24.0  ZHO    Guanyu       Zhou  1999-05-30   \n",
      "844       846     norris     4.0  NOR     Lando     Norris  1999-11-13   \n",
      "850       852    tsunoda    22.0  TSU      Yuki    Tsunoda  2000-05-11   \n",
      "856       858   sargeant     2.0  SAR     Logan   Sargeant  2000-12-31   \n",
      "855       857    piastri    81.0  PIA     Oscar    Piastri  2001-04-06   \n",
      "\n",
      "    nationality  \n",
      "741      French  \n",
      "786     Italian  \n",
      "791     Italian  \n",
      "703     Belgian  \n",
      "589  Monegasque  \n",
      "..          ...  \n",
      "853     Chinese  \n",
      "844     British  \n",
      "850    Japanese  \n",
      "856    American  \n",
      "855  Australian  \n",
      "\n",
      "[857 rows x 8 columns]\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I now know the oldest driver.\n",
      "Final Answer: Philippe Étancelin\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "\n",
      "\n",
      "\u001b[1m> Entering new  chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mThought: I need to find the driver with the number 14\n",
      "Action: python_repl_ast\n",
      "Action Input: df[df['number'] == 14]\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m   driverId    name  number code  forename surname   birthdate nationality\n",
      "3         4  alonso    14.0  ALO  Fernando  Alonso  1981-07-29     Spanish\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
      "Final Answer: The driver with the number 14 is Fernando Alonso.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "\n",
      "\n",
      "\u001b[1m> Entering new  chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mThought: I need to filter the dataframe to get the drivers who are German and less than 25 years old\n",
      "Action: python_repl_ast\n",
      "Action Input: df[(df['nationality'] == 'German') & (pd.to_datetime('today') - df['birthdate']).dt.days/365 < 25]\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3mNameError: name 'pd' is not defined\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I need to import pandas\n",
      "Action: python_repl_ast\n",
      "Action Input: import pandas as pd\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m Now I can filter the dataframe\n",
      "Action: python_repl_ast\n",
      "Action Input: df[(df['nationality'] == 'German') & (pd.to_datetime('today') - df['birthdate']).dt.days/365 < 25]\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3mTypeError: unsupported operand type(s) for -: 'Timestamp' and 'str'\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I need to convert the birthdate column to a datetime object\n",
      "Action: python_repl_ast\n",
      "Action Input: df['birthdate'] = pd.to_datetime(df['birthdate'])\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m Now I can filter the dataframe\n",
      "Action: python_repl_ast\n",
      "Action Input: df[(df['nationality'] == 'German') & (pd.to_datetime('today') - df['birthdate']).dt.days/365 < 25]\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3m     driverId             name  number code  forename     surname  birthdate  \\\n",
      "0           1         hamilton    44.0  HAM     Lewis    Hamilton 1985-01-07   \n",
      "1           2         heidfeld     NaN  HEI      Nick    Heidfeld 1977-05-10   \n",
      "2           3          rosberg     6.0  ROS      Nico     Rosberg 1985-06-27   \n",
      "3           4           alonso    14.0  ALO  Fernando      Alonso 1981-07-29   \n",
      "4           5       kovalainen     NaN  KOV    Heikki  Kovalainen 1981-10-19   \n",
      "..        ...              ...     ...  ...       ...         ...        ...   \n",
      "852       854  mick_schumacher    47.0  MSC      Mick  Schumacher 1999-03-22   \n",
      "853       855             zhou    24.0  ZHO    Guanyu        Zhou 1999-05-30   \n",
      "854       856         de_vries    45.0  DEV      Nyck    de Vries 1995-02-06   \n",
      "855       857          piastri    81.0  PIA     Oscar     Piastri 2001-04-06   \n",
      "856       858         sargeant     2.0  SAR     Logan    Sargeant 2000-12-31   \n",
      "\n",
      "    nationality  \n",
      "0       British  \n",
      "1        German  \n",
      "2        German  \n",
      "3       Spanish  \n",
      "4       Finnish  \n",
      "..          ...  \n",
      "852      German  \n",
      "853     Chinese  \n",
      "854       Dutch  \n",
      "855  Australian  \n",
      "856    American  \n",
      "\n",
      "[857 rows x 8 columns]\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
      "Final Answer: 857 drivers who are German and less than 25 years old.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "\n",
      "\n",
      "\u001b[1m> Entering new  chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mThought: I need to find a driver with the number 14 and a birthdate less than 20 years ago\n",
      "Action: python_repl_ast\n",
      "Action Input: df[(df['number'] == 14) & (df['birthdate'] > '2000-01-01')]\u001b[0m\n",
      "Observation: \u001b[36;1m\u001b[1;3mEmpty DataFrame\n",
      "Columns: [driverId, name, number, code, forename, surname, birthdate, nationality]\n",
      "Index: []\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
      "Final Answer: There is no driver with the number 14 and a birthdate less than 20 years ago.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "Tokens Used: 10327\n",
      "\tPrompt Tokens: 9730\n",
      "\tCompletion Tokens: 597\n",
      "Successful Requests: 16\n",
      "Total Cost (USD): $0.20654\n"
     ]
    }
   ],
   "source": [
    "from langchain.agents import create_csv_agent\n",
    "from langchain.llms import OpenAI\n",
    "from langchain.callbacks import get_openai_callback\n",
    "\n",
    "with get_openai_callback() as cb:\n",
    "\n",
    "    open_ai = OpenAI(temperature=0)\n",
    "\n",
    "    print(open_ai.model_name)\n",
    "\n",
    "    agent = create_csv_agent(open_ai, 'drivers_full.csv', verbose=True)\n",
    "\n",
    "    for query in queries: \n",
    "        agent.run(query)\n",
    "\n",
    "    print(cb)\n",
    "\n",
    "    "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.2"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}