YT_Talk_to_CSV_&_Excel_Files_with_LangChain.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# Talking to CSV and Excel files with LangChain"
      ],
      "metadata": {
        "id": "QrR8xfz8gAXK"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "RRYSu48huSUW",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "061fa0de-4c63-4369-d068-622e4b248a80"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\u001b[?25l     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m0.0/426.3 KB\u001b[0m \u001b[31m?\u001b[0m eta \u001b[36m-:--:--\u001b[0m\r\u001b[2K     \u001b[91m━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[90m╺\u001b[0m\u001b[90m━━━━━━━━━━\u001b[0m \u001b[32m307.2/426.3 KB\u001b[0m \u001b[31m9.2 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m426.3/426.3 KB\u001b[0m \u001b[31m7.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m70.1/70.1 KB\u001b[0m \u001b[31m4.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.0/1.0 MB\u001b[0m \u001b[31m19.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m264.6/264.6 KB\u001b[0m \u001b[31m11.2 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m158.8/158.8 KB\u001b[0m \u001b[31m3.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m114.2/114.2 KB\u001b[0m \u001b[31m2.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m49.1/49.1 KB\u001b[0m \u001b[31m1.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h"
          ]
        }
      ],
      "source": [
        "!pip -q install langchain openai"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import os\n",
        "\n",
        "os.environ[\"OPENAI_API_KEY\"] = \"\""
      ],
      "metadata": {
        "id": "dNA4TsHpu6OM"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "!pip show langchain"
      ],
      "metadata": {
        "id": "J-KFB7J_u_3L",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "45900590-49bc-4e14-f693-559f09045393"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Name: langchain\n",
            "Version: 0.0.123\n",
            "Summary: Building applications with LLMs through composability\n",
            "Home-page: https://www.github.com/hwchase17/langchain\n",
            "Author: \n",
            "Author-email: \n",
            "License: MIT\n",
            "Location: /usr/local/lib/python3.9/dist-packages\n",
            "Requires: aiohttp, dataclasses-json, numpy, pydantic, PyYAML, requests, SQLAlchemy, tenacity\n",
            "Required-by: \n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Dataset - Black Friday Sales\n",
        "\n",
        "\n",
        "https://www.kaggle.com/datasets/sdolezel/black-friday"
      ],
      "metadata": {
        "id": "pSa-ywnnWSAR"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!wget -q https://www.dropbox.com/s/8y6a1zloiscuo5d/black_friday_sales.zip\n",
        "!unzip -q black_friday_sales.zip"
      ],
      "metadata": {
        "id": "nU35TyN8W0HG"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "\n",
        "df = pd.read_csv('/content/train.csv')"
      ],
      "metadata": {
        "id": "k-gEnmv-WyVB"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 340
        },
        "id": "PoBMknlZXY2j",
        "outputId": "418184b2-3a9c-43e9-979a-b8abe9353a56"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   User_ID Product_ID Gender   Age  Occupation City_Category  \\\n",
              "0  1000001  P00069042      F  0-17          10             A   \n",
              "1  1000001  P00248942      F  0-17          10             A   \n",
              "2  1000001  P00087842      F  0-17          10             A   \n",
              "3  1000001  P00085442      F  0-17          10             A   \n",
              "4  1000002  P00285442      M   55+          16             C   \n",
              "\n",
              "  Stay_In_Current_City_Years  Marital_Status  Product_Category_1  \\\n",
              "0                          2               0                   3   \n",
              "1                          2               0                   1   \n",
              "2                          2               0                  12   \n",
              "3                          2               0                  12   \n",
              "4                         4+               0                   8   \n",
              "\n",
              "   Product_Category_2  Product_Category_3  Purchase  \n",
              "0                 NaN                 NaN      8370  \n",
              "1                 6.0                14.0     15200  \n",
              "2                 NaN                 NaN      1422  \n",
              "3                14.0                 NaN      1057  \n",
              "4                 NaN                 NaN      7969  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-8e79c29d-39a0-4962-9a3d-d83500afa296\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>User_ID</th>\n",
              "      <th>Product_ID</th>\n",
              "      <th>Gender</th>\n",
              "      <th>Age</th>\n",
              "      <th>Occupation</th>\n",
              "      <th>City_Category</th>\n",
              "      <th>Stay_In_Current_City_Years</th>\n",
              "      <th>Marital_Status</th>\n",
              "      <th>Product_Category_1</th>\n",
              "      <th>Product_Category_2</th>\n",
              "      <th>Product_Category_3</th>\n",
              "      <th>Purchase</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1000001</td>\n",
              "      <td>P00069042</td>\n",
              "      <td>F</td>\n",
              "      <td>0-17</td>\n",
              "      <td>10</td>\n",
              "      <td>A</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>3</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>8370</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1000001</td>\n",
              "      <td>P00248942</td>\n",
              "      <td>F</td>\n",
              "      <td>0-17</td>\n",
              "      <td>10</td>\n",
              "      <td>A</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>6.0</td>\n",
              "      <td>14.0</td>\n",
              "      <td>15200</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1000001</td>\n",
              "      <td>P00087842</td>\n",
              "      <td>F</td>\n",
              "      <td>0-17</td>\n",
              "      <td>10</td>\n",
              "      <td>A</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1422</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1000001</td>\n",
              "      <td>P00085442</td>\n",
              "      <td>F</td>\n",
              "      <td>0-17</td>\n",
              "      <td>10</td>\n",
              "      <td>A</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>12</td>\n",
              "      <td>14.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1057</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1000002</td>\n",
              "      <td>P00285442</td>\n",
              "      <td>M</td>\n",
              "      <td>55+</td>\n",
              "      <td>16</td>\n",
              "      <td>C</td>\n",
              "      <td>4+</td>\n",
              "      <td>0</td>\n",
              "      <td>8</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7969</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8e79c29d-39a0-4962-9a3d-d83500afa296')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-8e79c29d-39a0-4962-9a3d-d83500afa296 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-8e79c29d-39a0-4962-9a3d-d83500afa296');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## CSV Agent\n",
        "\n",
        "NOTE: this agent calls the Pandas DataFrame agent under the hood, which in turn calls the Python agent, which executes LLM generated Python code - this can be bad if the LLM generated Python code is harmful. Use cautiously."
      ],
      "metadata": {
        "id": "P0LaHRvoVGKS"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain.agents import create_csv_agent\n",
        "from langchain.llms import OpenAI"
      ],
      "metadata": {
        "id": "GzQ6sbACUzSf"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "agent = create_csv_agent(OpenAI(temperature=0), \n",
        "                         '/content/train.csv', \n",
        "                         verbose=True)"
      ],
      "metadata": {
        "id": "wFjXiYDjUzZ8"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "agent"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0PMNQ-WDuHnh",
        "outputId": "4fc3f8e1-f798-4599-a1da-07dff2f87236"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "AgentExecutor(memory=None, callback_manager=<langchain.callbacks.shared.SharedCallbackManager object at 0x7f68343c37c0>, verbose=True, agent=ZeroShotAgent(llm_chain=LLMChain(memory=None, callback_manager=<langchain.callbacks.shared.SharedCallbackManager object at 0x7f68343c37c0>, verbose=False, prompt=PromptTemplate(input_variables=['input', 'agent_scratchpad'], output_parser=None, partial_variables={'df': '   User_ID Product_ID Gender   Age  Occupation City_Category  \\\\\\n0  1000001  P00069042      F  0-17          10             A   \\n1  1000001  P00248942      F  0-17          10             A   \\n2  1000001  P00087842      F  0-17          10             A   \\n3  1000001  P00085442      F  0-17          10             A   \\n4  1000002  P00285442      M   55+          16             C   \\n\\n  Stay_In_Current_City_Years  Marital_Status  Product_Category_1  \\\\\\n0                          2               0                   3   \\n1                          2               0                   1   \\n2                          2               0                  12   \\n3                          2               0                  12   \\n4                         4+               0                   8   \\n\\n   Product_Category_2  Product_Category_3  Purchase  \\n0                 NaN                 NaN      8370  \\n1                 6.0                14.0     15200  \\n2                 NaN                 NaN      1422  \\n3                14.0                 NaN      1057  \\n4                 NaN                 NaN      7969  '}, template='\\nYou are working with a pandas dataframe in Python. The name of the dataframe is `df`.\\nYou should use the tools below to answer the question posed of you:\\n\\npython_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\\nUse the following format:\\n\\nQuestion: the input question you must answer\\nThought: you should always think about what to do\\nAction: the action to take, should be one of [python_repl_ast]\\nAction Input: the input to the action\\nObservation: the result of the action\\n... (this Thought/Action/Action Input/Observation can repeat N times)\\nThought: I now know the final answer\\nFinal Answer: the final answer to the original input question\\n\\n\\nThis is the result of `print(df.head())`:\\n{df}\\n\\nBegin!\\nQuestion: {input}\\n{agent_scratchpad}', template_format='f-string', validate_template=True), llm=OpenAI(cache=None, verbose=False, callback_manager=<langchain.callbacks.shared.SharedCallbackManager object at 0x7f68343c37c0>, client=<class 'openai.api_resources.completion.Completion'>, model_name='text-davinci-003', temperature=0.0, max_tokens=256, top_p=1, frequency_penalty=0, presence_penalty=0, n=1, best_of=1, model_kwargs={}, openai_api_key=None, batch_size=20, request_timeout=None, logit_bias={}, max_retries=6, streaming=False), output_key='text'), allowed_tools=['python_repl_ast'], return_values=['output']), tools=[PythonAstREPLTool(name='python_repl_ast', description='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.', return_direct=False, verbose=False, callback_manager=<langchain.callbacks.shared.SharedCallbackManager object at 0x7f68343c37c0>, globals={}, locals={'df':         User_ID Product_ID Gender    Age  Occupation City_Category  \\\n",
              "0       1000001  P00069042      F   0-17          10             A   \n",
              "1       1000001  P00248942      F   0-17          10             A   \n",
              "2       1000001  P00087842      F   0-17          10             A   \n",
              "3       1000001  P00085442      F   0-17          10             A   \n",
              "4       1000002  P00285442      M    55+          16             C   \n",
              "...         ...        ...    ...    ...         ...           ...   \n",
              "550063  1006033  P00372445      M  51-55          13             B   \n",
              "550064  1006035  P00375436      F  26-35           1             C   \n",
              "550065  1006036  P00375436      F  26-35          15             B   \n",
              "550066  1006038  P00375436      F    55+           1             C   \n",
              "550067  1006039  P00371644      F  46-50           0             B   \n",
              "\n",
              "       Stay_In_Current_City_Years  Marital_Status  Product_Category_1  \\\n",
              "0                               2               0                   3   \n",
              "1                               2               0                   1   \n",
              "2                               2               0                  12   \n",
              "3                               2               0                  12   \n",
              "4                              4+               0                   8   \n",
              "...                           ...             ...                 ...   \n",
              "550063                          1               1                  20   \n",
              "550064                          3               0                  20   \n",
              "550065                         4+               1                  20   \n",
              "550066                          2               0                  20   \n",
              "550067                         4+               1                  20   \n",
              "\n",
              "        Product_Category_2  Product_Category_3  Purchase  \n",
              "0                      NaN                 NaN      8370  \n",
              "1                      6.0                14.0     15200  \n",
              "2                      NaN                 NaN      1422  \n",
              "3                     14.0                 NaN      1057  \n",
              "4                      NaN                 NaN      7969  \n",
              "...                    ...                 ...       ...  \n",
              "550063                 NaN                 NaN       368  \n",
              "550064                 NaN                 NaN       371  \n",
              "550065                 NaN                 NaN       137  \n",
              "550066                 NaN                 NaN       365  \n",
              "550067                 NaN                 NaN       490  \n",
              "\n",
              "[550068 rows x 12 columns]})], return_intermediate_steps=False, max_iterations=15, early_stopping_method='force')"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "agent.agent.llm_chain.prompt.template"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 174
        },
        "id": "rS-PPUZkvuS8",
        "outputId": "c4a9e37d-3928-43d3-fca4-654e57d732f9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'\\nYou are working with a pandas dataframe in Python. The name of the dataframe is `df`.\\nYou should use the tools below to answer the question posed of you:\\n\\npython_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\\nUse the following format:\\n\\nQuestion: the input question you must answer\\nThought: you should always think about what to do\\nAction: the action to take, should be one of [python_repl_ast]\\nAction Input: the input to the action\\nObservation: the result of the action\\n... (this Thought/Action/Action Input/Observation can repeat N times)\\nThought: I now know the final answer\\nFinal Answer: the final answer to the original input question\\n\\n\\nThis is the result of `print(df.head())`:\\n{df}\\n\\nBegin!\\nQuestion: {input}\\n{agent_scratchpad}'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "\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}\n",
        "\n",
        "Begin!\n",
        "Question: {input}\n",
        "{agent_scratchpad}"
      ],
      "metadata": {
        "id": "flo_FqmZwTPU"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "agent.run(\"how many rows are there?\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 226
        },
        "id": "inMx5EdoUzby",
        "outputId": "8c790fa2-7ce9-48cc-b092-0abdc421c75a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
            "\u001b[32;1m\u001b[1;3mThought: I need to count the number of rows\n",
            "Action: python_repl_ast\n",
            "Action Input: len(df)\u001b[0m\n",
            "Observation: \u001b[36;1m\u001b[1;3m550068\u001b[0m\n",
            "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
            "Final Answer: 550068\u001b[0m\n",
            "\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'550068'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "agent.run(\"how many people are female?\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 434
        },
        "id": "LJ7IjBuJUzdm",
        "outputId": "170d372b-0a80-4266-ead9-2e6f02f4d7ef"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
            "\u001b[32;1m\u001b[1;3mThought: I need to count the number of people who are female\n",
            "Action: python_repl_ast\n",
            "Action Input: df[df['Gender'] == 'F'].count()\u001b[0m\n",
            "Observation: \u001b[36;1m\u001b[1;3mUser_ID                       135809\n",
            "Product_ID                    135809\n",
            "Gender                        135809\n",
            "Age                           135809\n",
            "Occupation                    135809\n",
            "City_Category                 135809\n",
            "Stay_In_Current_City_Years    135809\n",
            "Marital_Status                135809\n",
            "Product_Category_1            135809\n",
            "Product_Category_2             91530\n",
            "Product_Category_3             37594\n",
            "Purchase                      135809\n",
            "dtype: int64\u001b[0m\n",
            "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
            "Final Answer: 135809\u001b[0m\n",
            "\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'135809'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "agent.run(\"how many people have stayed more than 3 years in the city?\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 226
        },
        "id": "CRV3rGBaUzfq",
        "outputId": "2fe97474-d076-46f0-8260-1d3590d136a0"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
            "\u001b[32;1m\u001b[1;3mThought: I need to filter the dataframe to find the people who have stayed more than 3 years in the city\n",
            "Action: python_repl_ast\n",
            "Action Input: df[df['Stay_In_Current_City_Years'] > '3'].shape[0]\u001b[0m\n",
            "Observation: \u001b[36;1m\u001b[1;3m84726\u001b[0m\n",
            "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
            "Final Answer: 84726 people have stayed more than 3 years in the city.\u001b[0m\n",
            "\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'84726 people have stayed more than 3 years in the city.'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "agent.run(\"how many people have stayed more than 3 years in the city and are female?\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 972
        },
        "id": "zuJ0AeKNYN4X",
        "outputId": "0994cb77-4983-4912-b19e-cfbac6785c3c"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
            "\u001b[32;1m\u001b[1;3mThought: I need to filter the dataframe to find the people who meet the criteria\n",
            "Action: python_repl_ast\n",
            "Action Input: df[(df['Stay_In_Current_City_Years'] > '3') & (df['Gender'] == 'F')]\u001b[0m\n",
            "Observation: \u001b[36;1m\u001b[1;3m        User_ID Product_ID Gender    Age  Occupation City_Category  \\\n",
            "29      1000010  P00085942      F  36-45           1             B   \n",
            "30      1000010  P00118742      F  36-45           1             B   \n",
            "31      1000010  P00297942      F  36-45           1             B   \n",
            "32      1000010  P00266842      F  36-45           1             B   \n",
            "33      1000010  P00058342      F  36-45           1             B   \n",
            "...         ...        ...    ...    ...         ...           ...   \n",
            "549972  1005901  P00372445      F  36-45           7             C   \n",
            "549981  1005911  P00372445      F  26-35          17             C   \n",
            "550011  1005952  P00371644      F  46-50           1             C   \n",
            "550065  1006036  P00375436      F  26-35          15             B   \n",
            "550067  1006039  P00371644      F  46-50           0             B   \n",
            "\n",
            "       Stay_In_Current_City_Years  Marital_Status  Product_Category_1  \\\n",
            "29                             4+               1                   2   \n",
            "30                             4+               1                   5   \n",
            "31                             4+               1                   8   \n",
            "32                             4+               1                   5   \n",
            "33                             4+               1                   3   \n",
            "...                           ...             ...                 ...   \n",
            "549972                         4+               0                  20   \n",
            "549981                         4+               1                  20   \n",
            "550011                         4+               1                  20   \n",
            "550065                         4+               1                  20   \n",
            "550067                         4+               1                  20   \n",
            "\n",
            "        Product_Category_2  Product_Category_3  Purchase  \n",
            "29                     4.0                 8.0     16352  \n",
            "30                    11.0                 NaN      8886  \n",
            "31                     NaN                 NaN      5875  \n",
            "32                     NaN                 NaN      8854  \n",
            "33                     4.0                 NaN     10946  \n",
            "...                    ...                 ...       ...  \n",
            "549972                 NaN                 NaN       603  \n",
            "549981                 NaN                 NaN       596  \n",
            "550011                 NaN                 NaN       594  \n",
            "550065                 NaN                 NaN       137  \n",
            "550067                 NaN                 NaN       490  \n",
            "\n",
            "[18596 rows x 12 columns]\u001b[0m\n",
            "Thought:\u001b[32;1m\u001b[1;3m I need to count the number of rows in the result\n",
            "Action: python_repl_ast\n",
            "Action Input: len(df[(df['Stay_In_Current_City_Years'] > '3') & (df['Gender'] == 'F')])\u001b[0m\n",
            "Observation: \u001b[36;1m\u001b[1;3m18596\u001b[0m\n",
            "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
            "Final Answer: 18596 people have stayed more than 3 years in the city and are female.\u001b[0m\n",
            "\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'18596 people have stayed more than 3 years in the city and are female.'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "agent.run(\"Are there more males or females?\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 261
        },
        "id": "PDM8TfbJv2Uy",
        "outputId": "1b951d94-ae8b-400c-a1e4-b5f86ef1a56e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
            "\u001b[32;1m\u001b[1;3mThought: I need to count the number of males and females\n",
            "Action: python_repl_ast\n",
            "Action Input: df['Gender'].value_counts()\u001b[0m\n",
            "Observation: \u001b[36;1m\u001b[1;3mM    414259\n",
            "F    135809\n",
            "Name: Gender, dtype: int64\u001b[0m\n",
            "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
            "Final Answer: There are more males than females.\u001b[0m\n",
            "\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'There are more males than females.'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## LangChain CSV Loader"
      ],
      "metadata": {
        "id": "HqwsGJDhvAQ5"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain.document_loaders.csv_loader import CSVLoader\n",
        "\n",
        "loader = CSVLoader(file_path='/content/train.csv')\n",
        "data = loader.load()"
      ],
      "metadata": {
        "id": "J-ilDbIBVC-H"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#custom chain etc"
      ],
      "metadata": {
        "id": "i46ck8EPcruR"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Convert XLS file to csv"
      ],
      "metadata": {
        "id": "t8de30eojHeD"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install openpyxl"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1QSii5AGaS90",
        "outputId": "6de6b7fb-74fe-41ff-c9f6-498f3d471d19"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Requirement already satisfied: openpyxl in /usr/local/lib/python3.9/dist-packages (3.0.10)\n",
            "Requirement already satisfied: et-xmlfile in /usr/local/lib/python3.9/dist-packages (from openpyxl) (1.1.0)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!wget -q https://www.dropbox.com/s/co5kwipba9hpumt/excel_files.zip\n",
        "!unzip -q excel_files.zip"
      ],
      "metadata": {
        "id": "iFquHDs4jpfy"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "xls_file = r'/content/excel_file_example.xlsx'\n",
        "output_csv = r'/content/excel_file_example.csv'\n",
        "\n",
        "# Read the XLS file using pandas and openpyxl as the engine\n",
        "data = pd.read_excel(xls_file, engine='openpyxl')\n",
        "\n",
        "# Save the data as a CSV file\n",
        "data.to_csv(output_csv, index=False)"
      ],
      "metadata": {
        "id": "S9lVOQk-mM-o"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "agent = create_csv_agent(OpenAI(temperature=0), \n",
        "                         '/content/excel_file_example.csv', \n",
        "                         verbose=True)"
      ],
      "metadata": {
        "id": "HgzQAEKPYN_b"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "agent.run(\"What are the column names?\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 261
        },
        "id": "cKULEHFeeu_0",
        "outputId": "323a114c-9aa7-4ef1-887c-bb333b440a72"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
            "\u001b[32;1m\u001b[1;3mThought: I need to find out what the column names are\n",
            "Action: python_repl_ast\n",
            "Action Input: df.columns\u001b[0m\n",
            "Observation: \u001b[36;1m\u001b[1;3mIndex(['0', 'First Name', 'Last Name', 'Gender', 'Country', 'Age', 'Date',\n",
            "       'Id'],\n",
            "      dtype='object')\u001b[0m\n",
            "Thought:\u001b[32;1m\u001b[1;3m I now know the column names\n",
            "Final Answer: 0, First Name, Last Name, Gender, Country, Age, Date, Id\u001b[0m\n",
            "\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'0, First Name, Last Name, Gender, Country, Age, Date, Id'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "agent.run(\"What is the average age?\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 226
        },
        "id": "DAnTdAlYevBn",
        "outputId": "5af4ba33-7f3e-4f49-f873-937f567f2b8c"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
            "\u001b[32;1m\u001b[1;3mThought: I need to calculate the average age of the people in the dataframe\n",
            "Action: python_repl_ast\n",
            "Action Input: df['Age'].mean()\u001b[0m\n",
            "Observation: \u001b[36;1m\u001b[1;3m33.26\u001b[0m\n",
            "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
            "Final Answer: The average age is 33.26.\u001b[0m\n",
            "\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'The average age is 33.26.'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "agent.run(\"Which country appears the most and how many times does it appear?\")"
      ],
      "metadata": {
        "id": "yxbY7aQ_evDa",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 278
        },
        "outputId": "1940c7a9-ace1-4ea0-cfc0-483661f81bd6"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
            "\u001b[32;1m\u001b[1;3mThought: I need to count the number of times each country appears in the dataframe.\n",
            "Action: python_repl_ast\n",
            "Action Input: df['Country'].value_counts()\u001b[0m\n",
            "Observation: \u001b[36;1m\u001b[1;3mUnited States    48\n",
            "Great Britain    28\n",
            "France           24\n",
            "Name: Country, dtype: int64\u001b[0m\n",
            "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
            "Final Answer: The country that appears the most is United States, appearing 48 times.\u001b[0m\n",
            "\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'The country that appears the most is United States, appearing 48 times.'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "agent.run(\"What is the ratio of males to females?\")"
      ],
      "metadata": {
        "id": "Kcf1mGHRyyMb",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 261
        },
        "outputId": "c3591842-6dbc-4396-ad3f-47cf6c9feaf3"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
            "\u001b[32;1m\u001b[1;3mThought: I need to count the number of males and females\n",
            "Action: python_repl_ast\n",
            "Action Input: df['Gender'].value_counts()\u001b[0m\n",
            "Observation: \u001b[36;1m\u001b[1;3mFemale    76\n",
            "Male      24\n",
            "Name: Gender, dtype: int64\u001b[0m\n",
            "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
            "Final Answer: 24:76\u001b[0m\n",
            "\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'24:76'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "BOE02tgSy7NX"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}