Experiments/data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "dca11949",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from langchain_groq import ChatGroq\n",
    "from langchain_experimental.tools import PythonAstREPLTool\n",
    "from langchain.agents import AgentExecutor, create_tool_calling_agent\n",
    "from langchain_core.prompts import ChatPromptTemplate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "9914d633",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"Bakery sales.csv\")\n",
    "llm = ChatGroq(temperature=0, model_name=\"openai/gpt-oss-120b\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "fcedb05d",
   "metadata": {},
   "outputs": [],
   "source": [
    "tool = PythonAstREPLTool(locals={\"df\": df})\n",
    "tools = [tool]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ac006097",
   "metadata": {},
   "outputs": [],
   "source": [
    "prompt = ChatPromptTemplate.from_messages(\n",
    "    [\n",
    "        (\n",
    "            \"system\",\n",
    "            \"You are a helpful assistant that has access to a pandas dataframe, `df`.\"\n",
    "            \"You have access to a tool to execute python code.\"\n",
    "        ),\n",
    "        (\"placeholder\", \"{chat_history}\"),\n",
    "        (\"human\", \"{input}\"),\n",
    "        (\"placeholder\", \"{agent_scratchpad}\"),\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "43de4086",
   "metadata": {},
   "outputs": [],
   "source": [
    "agent = create_tool_calling_agent(llm, tools, prompt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "5a30966c",
   "metadata": {},
   "outputs": [],
   "source": [
    "agent_executor = AgentExecutor(\n",
    "    agent=agent,\n",
    "    tools=tools,\n",
    "    verbose=True,\n",
    "    handle_parsing_errors=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ce2065ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from langchain_groq import ChatGroq\n",
    "from langchain.agents import AgentExecutor, create_tool_calling_agent\n",
    "from langchain_core.prompts import ChatPromptTemplate\n",
    "from langchain_core.tools import tool\n",
    "from pydantic import BaseModel, Field\n",
    "import sys\n",
    "from io import StringIO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "63efc773",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Load data and initialize LLM\n",
    "df = pd.read_csv(\"Bakery sales.csv\")\n",
    "llm = ChatGroq(temperature=0, model_name=\"openai/gpt-oss-120b\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "35931f94",
   "metadata": {},
   "outputs": [],
   "source": [
    "class PythonInputs(BaseModel):\n",
    "    query: str = Field(description=\"A valid python command to run.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "de3d1367",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3. Create the tool using the @tool decorator. This is the gold standard.\n",
    "@tool(args_schema=PythonInputs)\n",
    "def python_repl_ast(query: str) -> str:\n",
    "    \"\"\"\n",
    "    Runs a Python command and returns the result.\n",
    "    The command has access to a pandas DataFrame named `df`.\n",
    "    \"\"\"\n",
    "    # Create a restricted namespace for the execution to see the DataFrame\n",
    "    local_namespace = {\"df\": df}\n",
    "    global_namespace = {}\n",
    "\n",
    "    # Capture stdout to return print() statements\n",
    "    old_stdout = sys.stdout\n",
    "    sys.stdout = captured_output = StringIO()\n",
    "\n",
    "    try:\n",
    "        # Execute the code\n",
    "        exec(query, global_namespace, local_namespace)\n",
    "        \n",
    "        # Get the output\n",
    "        output = captured_output.getvalue()\n",
    "        if output:\n",
    "            return output\n",
    "        else:\n",
    "            # If nothing was printed, try to eval the last line for a result\n",
    "            try:\n",
    "                # Split the query into lines and eval the last non-empty one\n",
    "                lines = [line for line in query.strip().split('\\n') if line.strip()]\n",
    "                if lines:\n",
    "                    last_line_result = eval(lines[-1], global_namespace, local_namespace)\n",
    "                    return str(last_line_result)\n",
    "                else:\n",
    "                    return \"Executed successfully, but no output was produced.\"\n",
    "            except Exception:\n",
    "                 return \"Executed successfully, but no output was produced.\"\n",
    "\n",
    "    except Exception as e:\n",
    "        return f\"Execution failed with error: {e}\"\n",
    "    finally:\n",
    "        # Restore stdout\n",
    "        sys.stdout = old_stdout"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a6326681",
   "metadata": {},
   "outputs": [],
   "source": [
    "tools = [python_repl_ast]\n",
    "\n",
    "# 5. Create the Agent Prompt (same as before)\n",
    "prompt = ChatPromptTemplate.from_messages(\n",
    "    [\n",
    "        (\n",
    "            \"system\",\n",
    "            \"You are a helpful assistant that has access to a pandas dataframe, `df`.\"\n",
    "            \"You have access to a tool to execute python code.\"\n",
    "        ),\n",
    "        (\"placeholder\", \"{chat_history}\"),\n",
    "        (\"human\", \"{input}\"),\n",
    "        (\"placeholder\", \"{agent_scratchpad}\"),\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "53cbe910",
   "metadata": {},
   "outputs": [],
   "source": [
    "agent = create_tool_calling_agent(llm, tools, prompt)\n",
    "\n",
    "# 7. Create the Agent Executor (same as before)\n",
    "agent_executor = AgentExecutor(\n",
    "    agent=agent,\n",
    "    tools=tools,\n",
    "    verbose=True,\n",
    "    handle_parsing_errors=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5c04f415",
   "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, json, sys\\nprint('Shape:', df.shape)\\nprint('Columns:', df.columns.tolist())\\nprint(df.head())\\nprint(df.dtypes)\\n\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3mShape: (234005, 7)\n",
      "Columns: ['Unnamed: 0', 'date', 'time', 'ticket_number', 'article', 'Quantity', 'unit_price']\n",
      "   Unnamed: 0        date   time  ticket_number               article  \\\n",
      "0           0  2021-01-02  08:38       150040.0              BAGUETTE   \n",
      "1           1  2021-01-02  08:38       150040.0      PAIN AU CHOCOLAT   \n",
      "2           4  2021-01-02  09:14       150041.0      PAIN AU CHOCOLAT   \n",
      "3           5  2021-01-02  09:14       150041.0                  PAIN   \n",
      "4           8  2021-01-02  09:25       150042.0  TRADITIONAL BAGUETTE   \n",
      "\n",
      "   Quantity unit_price  \n",
      "0       1.0     0,90 €  \n",
      "1       3.0     1,20 €  \n",
      "2       2.0     1,20 €  \n",
      "3       1.0     1,15 €  \n",
      "4       5.0     1,20 €  \n",
      "Unnamed: 0         int64\n",
      "date              object\n",
      "time              object\n",
      "ticket_number    float64\n",
      "article           object\n",
      "Quantity         float64\n",
      "unit_price        object\n",
      "dtype: object\n",
      "\u001b[0m\u001b[32;1m\u001b[1;3m**What the DataFrame Represents**\n",
      "\n",
      "| Column | Meaning (as inferred) |\n",
      "|--------|-----------------------|\n",
      "| `Unnamed: 0` | An auto‑generated row index that was saved when the CSV/Excel file was exported. It does **not** carry any business information. |\n",
      "| `date` | The calendar day of the sale (e.g., `2021‑01‑02`). |\n",
      "| `time` | The clock‑time of the sale (e.g., `08:38`). |\n",
      "| `ticket_number` | The identifier of the receipt / transaction. All rows that share the same ticket number belong to the same purchase. |\n",
      "| `article` | The name of the product sold (e.g., *BAGUETTE*, *PAIN AU CHOCOLAT*). |\n",
      "| `Quantity` | Number of units of that article bought in the transaction. |\n",
      "| `unit_price` | The price of a single unit, stored as a string with a comma as decimal separator and the euro sign (e.g., `0,90 €`). |\n",
      "\n",
      "The DataFrame therefore contains **point‑of‑sale (POS) transaction data** for a bakery or a small food shop. Each row is a line‑item on a receipt, and the dataset spans many days (≈ 234 k rows, 7 columns).\n",
      "\n",
      "---\n",
      "\n",
      "## Step‑by‑Step Pre‑processing Checklist\n",
      "\n",
      "Below is a ready‑to‑copy Python script (using pandas) that will clean the data and put it in a tidy, analysis‑ready form.\n",
      "\n",
      "```python\n",
      "import pandas as pd\n",
      "import numpy as np\n",
      "\n",
      "# -------------------------------------------------\n",
      "# 1️⃣  Drop the useless index column\n",
      "# -------------------------------------------------\n",
      "if 'Unnamed: 0' in df.columns:\n",
      "    df = df.drop(columns=['Unnamed: 0'])\n",
      "\n",
      "# -------------------------------------------------\n",
      "# 2️⃣  Clean column names (lower‑case, no spaces)\n",
      "# -------------------------------------------------\n",
      "df = df.rename(columns=lambda x: x.strip().lower().replace(' ', '_'))\n",
      "\n",
      "# -------------------------------------------------\n",
      "# 3️⃣  Parse dates & times → single datetime column\n",
      "# -------------------------------------------------\n",
      "#   a) Ensure date column is a proper date\n",
      "df['date'] = pd.to_datetime(df['date'], errors='coerce', format='%Y-%m-%d')\n",
      "\n",
      "#   b) Clean the time column (some rows may have stray spaces)\n",
      "df['time'] = df['time'].str.strip()\n",
      "\n",
      "#   c) Combine date + time into a datetime column\n",
      "df['datetime'] = pd.to_datetime(df['date'].dt.strftime('%Y-%m-%d') + ' ' + df['time'],\n",
      "                                errors='coerce',\n",
      "                                format='%Y-%m-%d %H:%M')\n",
      "\n",
      "#   d) Drop the original separate columns if you don’t need them\n",
      "df = df.drop(columns=['date', 'time'])\n",
      "\n",
      "# -------------------------------------------------\n",
      "# 4️⃣  Convert ticket_number to an integer (it’s a float because of NaNs)\n",
      "# -------------------------------------------------\n",
      "df['ticket_number'] = df['ticket_number'].astype('Int64')   # pandas nullable integer\n",
      "\n",
      "# -------------------------------------------------\n",
      "# 5️⃣  Clean the monetary column (unit_price)\n",
      "# -------------------------------------------------\n",
      "#   a) Remove the euro sign and any whitespace\n",
      "df['unit_price'] = df['unit_price'].str.replace('€', '', regex=False).str.strip()\n",
      "\n",
      "#   b) Replace the French decimal comma with a dot\n",
      "df['unit_price'] = df['unit_price'].str.replace(',', '.', regex=False)\n",
      "\n",
      "#   c) Convert to numeric (float). Invalid parsing → NaN\n",
      "df['unit_price'] = pd.to_numeric(df['unit_price'], errors='coerce')\n",
      "\n",
      "# -------------------------------------------------\n",
      "# 6️⃣  Ensure Quantity is numeric (it already is float, but enforce int where possible)\n",
      "# -------------------------------------------------\n",
      "df['quantity'] = df['quantity'].astype('Int64')   # keep nullable int\n",
      "\n",
      "# -------------------------------------------------\n",
      "# 7️⃣  Create a total_price column (quantity × unit_price)\n",
      "# -------------------------------------------------\n",
      "df['total_price'] = df['quantity'] * df['unit_price']\n",
      "\n",
      "# -------------------------------------------------\n",
      "# 8️⃣  Handle missing / erroneous rows\n",
      "# -------------------------------------------------\n",
      "#   a) Identify rows with critical missing data\n",
      "critical_missing = df[['datetime', 'ticket_number', 'article', 'quantity', 'unit_price']].isnull().any(axis=1)\n",
      "\n",
      "#   b) Decide what to do – here we drop them, but you could also impute or flag them\n",
      "df_clean = df[~critical_missing].copy()\n",
      "\n",
      "# -------------------------------------------------\n",
      "# 9️⃣  Optional: sort the data for easier inspection\n",
      "# -------------------------------------------------\n",
      "df_clean = df_clean.sort_values(['datetime', 'ticket_number']).reset_index(drop=True)\n",
      "\n",
      "# -------------------------------------------------\n",
      "# 10️⃣  Final sanity checks\n",
      "# -------------------------------------------------\n",
      "print(\"Rows after cleaning :\", df_clean.shape[0])\n",
      "print(\"Sample rows:\")\n",
      "print(df_clean.head())\n",
      "print(\"\\nData types:\")\n",
      "print(df_clean.dtypes)\n",
      "```\n",
      "\n",
      "### What each step accomplishes\n",
      "\n",
      "| # | Action | Why it matters |\n",
      "|---|--------|----------------|\n",
      "| 1 | Drop `Unnamed: 0` | Removes a meaningless auto‑generated index that would otherwise clutter the dataset. |\n",
      "| 2 | Normalise column names | Guarantees consistent, Python‑friendly identifiers (`snake_case`). |\n",
      "| 3 | Build a proper `datetime` column | Merges separate date & time strings into a true timestamp, enabling time‑series analysis, resampling, and sorting. |\n",
      "| 4 | Cast `ticket_number` to integer | Ticket numbers are identifiers, not continuous floats; using an integer type avoids accidental arithmetic. |\n",
      "| 5 | Clean `unit_price` | Strips currency symbols, converts French decimal commas to dots, and casts to numeric so you can compute totals, averages, etc. |\n",
      "| 6 | Cast `quantity` to integer | Quantities are discrete counts; using an integer type prevents floating‑point artefacts. |\n",
      "| 7 | Compute `total_price` | Gives the monetary value of each line‑item, a key metric for revenue analysis. |\n",
      "| 8 | Deal with missing data | Removes rows that lack essential information (date, ticket, article, price, quantity) – you can also choose to flag or impute them. |\n",
      "| 9 | Sort & reset index | Makes the DataFrame easier to explore and ensures a clean, contiguous index. |\n",
      "|10 | Verify | Quick sanity‑check that the cleaning succeeded and the data types are now appropriate for analysis. |\n",
      "\n",
      "---\n",
      "\n",
      "### After preprocessing you can safely start analysis such as:\n",
      "\n",
      "* **Revenue per day / week / month** (`df_clean.groupby(df_clean.datetime.dt.date)['total_price'].sum()`)\n",
      "* **Top‑selling articles** (`df_clean.groupby('article')['quantity'].sum().sort_values(ascending=False)`)\n",
      "* **Average basket size** (`df_clean.groupby('ticket_number')['total_price'].sum().mean()`)\n",
      "* **Time‑of‑day patterns** (hourly sales, peak hours, etc.)\n",
      "* **Customer segmentation** (if you later add customer IDs)\n",
      "\n",
      "Feel free to adapt any of the steps (e.g., keep the original `date`/`time` columns, use a different missing‑value strategy, or add more derived columns). The script above gives you a clean, analysis‑ready foundation.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "response = agent_executor.invoke({\n",
    "    \"input\": \"Find what is the given dataframe is about and give a clear summary of the df and a clear step by step prompt to perform any preprocessing steps before we can do any analysis on it.\"\n",
    "})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4a00bd4f",
   "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.head()'}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m   Unnamed: 0        date   time  ticket_number               article  \\\n",
      "0           0  2021-01-02  08:38       150040.0              BAGUETTE   \n",
      "1           1  2021-01-02  08:38       150040.0      PAIN AU CHOCOLAT   \n",
      "2           4  2021-01-02  09:14       150041.0      PAIN AU CHOCOLAT   \n",
      "3           5  2021-01-02  09:14       150041.0                  PAIN   \n",
      "4           8  2021-01-02  09:25       150042.0  TRADITIONAL BAGUETTE   \n",
      "\n",
      "   Quantity unit_price  \n",
      "0       1.0     0,90 €  \n",
      "1       3.0     1,20 €  \n",
      "2       2.0     1,20 €  \n",
      "3       1.0     1,15 €  \n",
      "4       5.0     1,20 €  \u001b[0m\u001b[32;1m\u001b[1;3mThe dataframe records bakery sales with columns for an index, date, time, ticket number, article name, quantity sold and unit price formatted with a comma and euro symbol. First drop the unnamed index column. Then combine the date and time columns into a single datetime column and convert it to a proper datetime type. Next clean the unit_price column by removing the euro symbol and comma, replace the comma with a dot and convert the result to a numeric type. Then ensure the quantity column is numeric and handle any missing or non‑numeric entries. After that set appropriate data types for ticket number and article as categorical or string as needed. Finally sort the data by the new datetime column and reset the index for further analysis.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "response = agent_executor.invoke({\n",
    "    \"input\": \"Find what is the given dataframe is about and give a clear summary of the df and a clear step by step prompt to perform any preprocessing steps before we can do any analysis on it.(STRICT RULES:ONLY CONTEXT IN PLAIN ENGLISH NO CODE NO MARKDOWN NO BULLETS NO NUMBERS NO EXTRA WORDS NO APOLOGIES NO EXPLANATIONS NO PREAMBLE NO POSTAMBLE)\"\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "02d20d20",
   "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': \"\\nimport pandas as pd, numpy as np\\n# Step 1: drop Unnamed: 0 if exists\\nif 'Unnamed: 0' in df.columns:\\n    df = df.drop(columns=['Unnamed: 0'])\\n# Step 2: combine date and time into datetime\\n# Assume date and time are strings\\n# Strip whitespace\\ndf['date'] = df['date'].astype(str).str.strip()\\ndf['time'] = df['time'].astype(str).str.strip()\\n# Combine\\ndf['datetime'] = pd.to_datetime(df['date'] + ' ' + df['time'], errors='coerce')\\n# Drop original date and time if desired\\n# df = df.drop(columns=['date','time'])\\n# Step 3: cast ticket_number to integer\\n# First coerce to numeric\\ndf['ticket_number'] = pd.to_numeric(df['ticket_number'], errors='coerce').astype('Int64')\\n# Step 4: clean unit_price\\n# Remove euro sign and spaces, replace comma with dot\\nif df['unit_price'].dtype == object:\\n    df['unit_price'] = df['unit_price'].astype(str).str.replace('€','').str.replace(',','.', regex=False).str.strip()\\n# Convert to float\\ndf['unit_price'] = pd.to_numeric(df['unit_price'], errors='coerce')\\n# Step 5: ensure Quantity numeric\\nif df['Quantity'].dtype == object:\\n    df['Quantity'] = df['Quantity'].astype(str).str.replace(',','.', regex=False).str.strip()\\ndf['Quantity'] = pd.to_numeric(df['Quantity'], errors='coerce')\\n# Step 6: line_total\\ndf['line_total'] = df['Quantity'] * df['unit_price']\\n# Step 7: handle missing/invalid rows\\n# Define critical fields: datetime, ticket_number, article, Quantity, unit_price\\ncritical = ['datetime','ticket_number','article','Quantity','unit_price']\\n# Drop rows where any critical is missing\\ndf_clean = df.dropna(subset=critical)\\n# Step 8: standardize article names\\ndf_clean['article'] = df_clean['article'].astype(str).str.strip().str.title()\\n# Step 9: dtype checks\\n# Ensure final dtypes\\nfinal_dtypes = df_clean.dtypes\\n# Return head and dtypes summary\\nresult = {\\n    'head': df_clean.head().to_dict(orient='records'),\\n    'dtypes': final_dtypes.apply(lambda x: str(x)).to_dict(),\\n    'shape': df_clean.shape\\n}\\nresult\"}`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m{'head': [{'date': '2021-01-02', 'time': '08:38', 'ticket_number': 150040, 'article': 'Baguette', 'Quantity': 1.0, 'unit_price': 0.9, 'datetime': Timestamp('2021-01-02 08:38:00'), 'line_total': 0.9}, {'date': '2021-01-02', 'time': '08:38', 'ticket_number': 150040, 'article': 'Pain Au Chocolat', 'Quantity': 3.0, 'unit_price': 1.2, 'datetime': Timestamp('2021-01-02 08:38:00'), 'line_total': 3.5999999999999996}, {'date': '2021-01-02', 'time': '09:14', 'ticket_number': 150041, 'article': 'Pain Au Chocolat', 'Quantity': 2.0, 'unit_price': 1.2, 'datetime': Timestamp('2021-01-02 09:14:00'), 'line_total': 2.4}, {'date': '2021-01-02', 'time': '09:14', 'ticket_number': 150041, 'article': 'Pain', 'Quantity': 1.0, 'unit_price': 1.15, 'datetime': Timestamp('2021-01-02 09:14:00'), 'line_total': 1.15}, {'date': '2021-01-02', 'time': '09:25', 'ticket_number': 150042, 'article': 'Traditional Baguette', 'Quantity': 5.0, 'unit_price': 1.2, 'datetime': Timestamp('2021-01-02 09:25:00'), 'line_total': 6.0}], 'dtypes': {'date': 'object', 'time': 'object', 'ticket_number': 'Int64', 'article': 'object', 'Quantity': 'float64', 'unit_price': 'float64', 'datetime': 'datetime64[ns]', 'line_total': 'float64'}, 'shape': (234005, 8)}\u001b[0m\u001b[32;1m\u001b[1;3mHere is the cleaned POS‑sales dataframe (`df_clean`). All preprocessing steps have been applied and the data is ready for exploratory analysis.\n",
      "\n",
      "**Key transformations**\n",
      "\n",
      "| Step | Action |\n",
      "|------|--------|\n",
      "| 1️⃣  | Dropped the useless column **`Unnamed: 0`** (if it existed). |\n",
      "| 2️⃣  | Combined **`date`** and **`time`** into a proper **`datetime`** column (`datetime`). |\n",
      "| 3️⃣  | Cast **`ticket_number`** to integer (`Int64`). |\n",
      "| 4️⃣  | Cleaned **`unit_price`** – removed “€”, replaced commas with dots, and converted to `float`. |\n",
      "| 5️⃣  | Ensured **`Quantity`** is numeric (`float64`). |\n",
      "| 6️⃣  | Added **`line_total = Quantity * unit_price`**. |\n",
      "| 7️⃣  | Dropped rows with missing/invalid values in any critical field (`datetime`, `ticket_number`, `article`, `Quantity`, `unit_price`). |\n",
      "| 8️⃣  | Standardized **`article`** names – stripped whitespace and applied title‑case. |\n",
      "| 9️⃣  | Final dtype sanity check – all columns now have appropriate types. |\n",
      "\n",
      "**Resulting schema**\n",
      "\n",
      "| Column | dtype |\n",
      "|--------|-------|\n",
      "| `date` | `object` (original string, kept for reference) |\n",
      "| `time` | `object` (original string, kept for reference) |\n",
      "| `ticket_number` | `Int64` |\n",
      "| `article` | `object` |\n",
      "| `Quantity` | `float64` |\n",
      "| `unit_price` | `float64` |\n",
      "| `datetime` | `datetime64[ns]` |\n",
      "| `line_total` | `float64` |\n",
      "\n",
      "**Shape**\n",
      "\n",
      "- **Rows:** 234 005  \n",
      "- **Columns:** 8  \n",
      "\n",
      "**Sample of the cleaned data**\n",
      "\n",
      "| date       | time  | ticket_number | article            | Quantity | unit_price | datetime            | line_total |\n",
      "|------------|-------|---------------|--------------------|----------|------------|---------------------|------------|\n",
      "| 2021‑01‑02 | 08:38 | 150040        | Baguette           | 1.0      | 0.90       | 2021‑01‑02 08:38:00 | 0.90       |\n",
      "| 2021‑01‑02 | 08:38 | 150040        | Pain Au Chocolat   | 3.0      | 1.20       | 2021‑01‑02 08:38:00 | 3.60       |\n",
      "| 2021‑01‑02 | 09:14 | 150041        | Pain Au Chocolat   | 2.0      | 1.20       | 2021‑01‑02 09:14:00 | 2.40       |\n",
      "| 2021‑01‑02 | 09:14 | 150041        | Pain               | 1.0      | 1.15       | 2021‑01‑02 09:14:00 | 1.15       |\n",
      "| 2021‑01‑02 | 09:25 | 150042        | Traditional Baguette| 5.0     | 1.20       | 2021‑01‑02 09:25:00 | 6.00       |\n",
      "\n",
      "You can now continue with any EDA, aggregations, visualisations, etc., using `df_clean`. If you need further transformations or analyses, just let me know!\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "response = agent_executor.invoke({\n",
    "    \"input\": \"\"\"The dataframe is a POS sales log with columns: date, time, ticket_number, article, Quantity, unit_price.  \n",
    "Preprocess it step by step:  \n",
    "1) Drop useless column `Unnamed: 0`.  \n",
    "2) Convert `date` and `time` into proper datetime (create combined `datetime`).  \n",
    "3) Cast `ticket_number` to integer.  \n",
    "4) Clean `unit_price` (\"0,90 €\" → 0.90 float).  \n",
    "5) Ensure `Quantity` is numeric.  \n",
    "6) Add new column `line_total = Quantity * unit_price`.  \n",
    "7) Handle missing/invalid rows (drop or impute critical fields).  \n",
    "8) Standardize `article` names (strip, title case).  \n",
    "9) Run final dtype/row sanity checks.  \n",
    "Return clean dataframe ready for EDA.\"\"\"\n",
    "})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7d42959f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'input': 'The dataframe is a POS sales log with columns: date, time, ticket_number, article, Quantity, unit_price.  \\nPreprocess it step by step:  \\n1) Drop useless column `Unnamed: 0`.  \\n2) Convert `date` and `time` into proper datetime (create combined `datetime`).  \\n3) Cast `ticket_number` to integer.  \\n4) Clean `unit_price` (\"0,90 €\" → 0.90 float).  \\n5) Ensure `Quantity` is numeric.  \\n6) Add new column `line_total = Quantity * unit_price`.  \\n7) Handle missing/invalid rows (drop or impute critical fields).  \\n8) Standardize `article` names (strip, title case).  \\n9) Run final dtype/row sanity checks.  \\nReturn clean dataframe ready for EDA.', 'output': 'Here is the cleaned POS‑sales dataframe (`df_clean`). All preprocessing steps have been applied and the data is ready for exploratory analysis.\\n\\n**Key transformations**\\n\\n| Step | Action |\\n|------|--------|\\n| 1️⃣  | Dropped the useless column **`Unnamed: 0`** (if it existed). |\\n| 2️⃣  | Combined **`date`** and **`time`** into a proper **`datetime`** column (`datetime`). |\\n| 3️⃣  | Cast **`ticket_number`** to integer (`Int64`). |\\n| 4️⃣  | Cleaned **`unit_price`** – removed “€”, replaced commas with dots, and converted to `float`. |\\n| 5️⃣  | Ensured **`Quantity`** is numeric (`float64`). |\\n| 6️⃣  | Added **`line_total = Quantity * unit_price`**. |\\n| 7️⃣  | Dropped rows with missing/invalid values in any critical field (`datetime`, `ticket_number`, `article`, `Quantity`, `unit_price`). |\\n| 8️⃣  | Standardized **`article`** names – stripped whitespace and applied title‑case. |\\n| 9️⃣  | Final dtype sanity check – all columns now have appropriate types. |\\n\\n**Resulting schema**\\n\\n| Column | dtype |\\n|--------|-------|\\n| `date` | `object` (original string, kept for reference) |\\n| `time` | `object` (original string, kept for reference) |\\n| `ticket_number` | `Int64` |\\n| `article` | `object` |\\n| `Quantity` | `float64` |\\n| `unit_price` | `float64` |\\n| `datetime` | `datetime64[ns]` |\\n| `line_total` | `float64` |\\n\\n**Shape**\\n\\n- **Rows:** 234\\u202f005  \\n- **Columns:** 8  \\n\\n**Sample of the cleaned data**\\n\\n| date       | time  | ticket_number | article            | Quantity | unit_price | datetime            | line_total |\\n|------------|-------|---------------|--------------------|----------|------------|---------------------|------------|\\n| 2021‑01‑02 | 08:38 | 150040        | Baguette           | 1.0      | 0.90       | 2021‑01‑02 08:38:00 | 0.90       |\\n| 2021‑01‑02 | 08:38 | 150040        | Pain Au Chocolat   | 3.0      | 1.20       | 2021‑01‑02 08:38:00 | 3.60       |\\n| 2021‑01‑02 | 09:14 | 150041        | Pain Au Chocolat   | 2.0      | 1.20       | 2021‑01‑02 09:14:00 | 2.40       |\\n| 2021‑01‑02 | 09:14 | 150041        | Pain               | 1.0      | 1.15       | 2021‑01‑02 09:14:00 | 1.15       |\\n| 2021‑01‑02 | 09:25 | 150042        | Traditional Baguette| 5.0     | 1.20       | 2021‑01‑02 09:25:00 | 6.00       |\\n\\nYou can now continue with any EDA, aggregations, visualisations, etc., using `df_clean`. If you need further transformations or analyses, just let me know!'}\n"
     ]
    }
   ],
   "source": [
    "print(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5faaa486",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}