data_preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datasets import load_dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = load_dataset(\"gretelai/synthetic_text_to_sql\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatasetDict({\n",
       "    train: Dataset({\n",
       "        features: ['id', 'domain', 'domain_description', 'sql_complexity', 'sql_complexity_description', 'sql_task_type', 'sql_task_type_description', 'sql_prompt', 'sql_context', 'sql', 'sql_explanation'],\n",
       "        num_rows: 100000\n",
       "    })\n",
       "    test: Dataset({\n",
       "        features: ['id', 'domain', 'domain_description', 'sql_complexity', 'sql_complexity_description', 'sql_task_type', 'sql_task_type_description', 'sql_prompt', 'sql_context', 'sql', 'sql_explanation'],\n",
       "        num_rows: 5851\n",
       "    })\n",
       "})"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset # we will work with['sql_prompt', 'sql_context', 'sql']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Dataset Stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dataset = dataset['train'].to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "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>id</th>\n",
       "      <th>domain</th>\n",
       "      <th>domain_description</th>\n",
       "      <th>sql_complexity</th>\n",
       "      <th>sql_complexity_description</th>\n",
       "      <th>sql_task_type</th>\n",
       "      <th>sql_task_type_description</th>\n",
       "      <th>sql_prompt</th>\n",
       "      <th>sql_context</th>\n",
       "      <th>sql</th>\n",
       "      <th>sql_explanation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>aerospace</td>\n",
       "      <td>Aircraft manufacturing data, satellite deploym...</td>\n",
       "      <td>aggregation</td>\n",
       "      <td>aggregation functions (COUNT, SUM, AVG, MIN, M...</td>\n",
       "      <td>analytics and reporting</td>\n",
       "      <td>generating reports, dashboards, and analytical...</td>\n",
       "      <td>What are the top 5 suppliers by total value of...</td>\n",
       "      <td>CREATE TABLE Suppliers (supplier_id INT, suppl...</td>\n",
       "      <td>SELECT supplier_name, SUM(total_value) as tota...</td>\n",
       "      <td>Filters the Suppliers table based on year 2020...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>aerospace</td>\n",
       "      <td>Aircraft manufacturing data, satellite deploym...</td>\n",
       "      <td>basic SQL</td>\n",
       "      <td>basic SQL with a simple select statement</td>\n",
       "      <td>analytics and reporting</td>\n",
       "      <td>generating reports, dashboards, and analytical...</td>\n",
       "      <td>Count the number of aircraft produced by compa...</td>\n",
       "      <td>CREATE TABLE aircraft (id INT, model VARCHAR(5...</td>\n",
       "      <td>SELECT COUNT(*) FROM aircraft WHERE manufactur...</td>\n",
       "      <td>This query counts the number of records in the...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>defense industry</td>\n",
       "      <td>Defense contract data, military equipment main...</td>\n",
       "      <td>basic SQL</td>\n",
       "      <td>basic SQL with a simple select statement</td>\n",
       "      <td>analytics and reporting</td>\n",
       "      <td>generating reports, dashboards, and analytical...</td>\n",
       "      <td>Get the average monthly maintenance cost of na...</td>\n",
       "      <td>CREATE TABLE NavalVessels (id INT, name VARCHA...</td>\n",
       "      <td>SELECT AVG(cost) FROM NavalVessels WHERE EXTRA...</td>\n",
       "      <td>This query calculates the average monthly main...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id            domain                                 domain_description  \\\n",
       "0   1         aerospace  Aircraft manufacturing data, satellite deploym...   \n",
       "1   2         aerospace  Aircraft manufacturing data, satellite deploym...   \n",
       "2   3  defense industry  Defense contract data, military equipment main...   \n",
       "\n",
       "  sql_complexity                         sql_complexity_description  \\\n",
       "0    aggregation  aggregation functions (COUNT, SUM, AVG, MIN, M...   \n",
       "1      basic SQL           basic SQL with a simple select statement   \n",
       "2      basic SQL           basic SQL with a simple select statement   \n",
       "\n",
       "             sql_task_type                          sql_task_type_description  \\\n",
       "0  analytics and reporting  generating reports, dashboards, and analytical...   \n",
       "1  analytics and reporting  generating reports, dashboards, and analytical...   \n",
       "2  analytics and reporting  generating reports, dashboards, and analytical...   \n",
       "\n",
       "                                          sql_prompt  \\\n",
       "0  What are the top 5 suppliers by total value of...   \n",
       "1  Count the number of aircraft produced by compa...   \n",
       "2  Get the average monthly maintenance cost of na...   \n",
       "\n",
       "                                         sql_context  \\\n",
       "0  CREATE TABLE Suppliers (supplier_id INT, suppl...   \n",
       "1  CREATE TABLE aircraft (id INT, model VARCHAR(5...   \n",
       "2  CREATE TABLE NavalVessels (id INT, name VARCHA...   \n",
       "\n",
       "                                                 sql  \\\n",
       "0  SELECT supplier_name, SUM(total_value) as tota...   \n",
       "1  SELECT COUNT(*) FROM aircraft WHERE manufactur...   \n",
       "2  SELECT AVG(cost) FROM NavalVessels WHERE EXTRA...   \n",
       "\n",
       "                                     sql_explanation  \n",
       "0  Filters the Suppliers table based on year 2020...  \n",
       "1  This query counts the number of records in the...  \n",
       "2  This query calculates the average monthly main...  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dataset.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 100000 entries, 0 to 99999\n",
      "Data columns (total 11 columns):\n",
      " #   Column                      Non-Null Count   Dtype \n",
      "---  ------                      --------------   ----- \n",
      " 0   id                          100000 non-null  int32 \n",
      " 1   domain                      100000 non-null  object\n",
      " 2   domain_description          100000 non-null  object\n",
      " 3   sql_complexity              100000 non-null  object\n",
      " 4   sql_complexity_description  100000 non-null  object\n",
      " 5   sql_task_type               100000 non-null  object\n",
      " 6   sql_task_type_description   100000 non-null  object\n",
      " 7   sql_prompt                  100000 non-null  object\n",
      " 8   sql_context                 100000 non-null  object\n",
      " 9   sql                         100000 non-null  object\n",
      " 10  sql_explanation             100000 non-null  object\n",
      "dtypes: int32(1), object(10)\n",
      "memory usage: 8.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df_dataset.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Saving the dataset (1/1 shards): 100%|██████████| 100000/100000 [00:00<00:00, 632252.17 examples/s]\n",
      "Saving the dataset (1/1 shards): 100%|██████████| 5851/5851 [00:00<00:00, 221956.77 examples/s]\n"
     ]
    }
   ],
   "source": [
    "dataset.save_to_disk('text_to_sql_data') # save dataset to disk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dataset_filtered = df_dataset[['sql_prompt', 'sql_context', 'sql']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check Average Encoder and Decoder Input Length to determine appx hyperparameter for training"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total =  1000\n",
      "prompt_total average =  13.717\n",
      "context_total average =  33.156\n",
      "input_total average =  46.873\n",
      "sql_total average =  15.786\n",
      "\n",
      "\n",
      "total =  2000\n",
      "prompt_total average =  13.759\n",
      "context_total average =  33.341\n",
      "input_total average =  47.1\n",
      "sql_total average =  15.699\n",
      "\n",
      "\n",
      "total =  3000\n",
      "prompt_total average =  13.830333333333334\n",
      "context_total average =  33.57966666666667\n",
      "input_total average =  47.41\n",
      "sql_total average =  15.75\n",
      "\n",
      "\n",
      "total =  4000\n",
      "prompt_total average =  13.8275\n",
      "context_total average =  33.659\n",
      "input_total average =  47.4865\n",
      "sql_total average =  15.7595\n",
      "\n",
      "\n",
      "total =  5000\n",
      "prompt_total average =  13.8652\n",
      "context_total average =  33.6044\n",
      "input_total average =  47.4696\n",
      "sql_total average =  15.799\n",
      "\n",
      "\n",
      "total =  6000\n",
      "prompt_total average =  13.860666666666667\n",
      "context_total average =  33.384166666666665\n",
      "input_total average =  47.24483333333333\n",
      "sql_total average =  15.726333333333333\n",
      "\n",
      "\n",
      "total =  7000\n",
      "prompt_total average =  13.866428571428571\n",
      "context_total average =  33.09385714285714\n",
      "input_total average =  46.96028571428572\n",
      "sql_total average =  15.655142857142858\n",
      "\n",
      "\n",
      "total =  8000\n",
      "prompt_total average =  13.883\n",
      "context_total average =  32.95975\n",
      "input_total average =  46.84275\n",
      "sql_total average =  15.687875\n",
      "\n",
      "\n",
      "total =  9000\n",
      "prompt_total average =  13.858888888888888\n",
      "context_total average =  32.84388888888889\n",
      "input_total average =  46.702777777777776\n",
      "sql_total average =  15.631\n",
      "\n",
      "\n",
      "total =  10000\n",
      "prompt_total average =  13.8701\n",
      "context_total average =  32.8384\n",
      "input_total average =  46.7085\n",
      "sql_total average =  15.6468\n",
      "\n",
      "\n",
      "total =  11000\n",
      "prompt_total average =  13.884636363636364\n",
      "context_total average =  32.819\n",
      "input_total average =  46.70363636363636\n",
      "sql_total average =  15.681545454545455\n",
      "\n",
      "\n",
      "total =  12000\n",
      "prompt_total average =  13.900166666666667\n",
      "context_total average =  32.790166666666664\n",
      "input_total average =  46.690333333333335\n",
      "sql_total average =  15.688\n",
      "\n",
      "\n",
      "total =  13000\n",
      "prompt_total average =  13.89576923076923\n",
      "context_total average =  32.82923076923077\n",
      "input_total average =  46.725\n",
      "sql_total average =  15.686076923076923\n",
      "\n",
      "\n",
      "total =  14000\n",
      "prompt_total average =  13.914\n",
      "context_total average =  32.8765\n",
      "input_total average =  46.7905\n",
      "sql_total average =  15.704285714285714\n",
      "\n",
      "\n",
      "total =  15000\n",
      "prompt_total average =  13.926666666666666\n",
      "context_total average =  32.86253333333333\n",
      "input_total average =  46.7892\n",
      "sql_total average =  15.707733333333334\n",
      "\n",
      "\n",
      "total =  16000\n",
      "prompt_total average =  13.9239375\n",
      "context_total average =  32.8035625\n",
      "input_total average =  46.7275\n",
      "sql_total average =  15.69125\n",
      "\n",
      "\n",
      "total =  17000\n",
      "prompt_total average =  13.910470588235293\n",
      "context_total average =  32.87158823529412\n",
      "input_total average =  46.78205882352941\n",
      "sql_total average =  15.680352941176471\n",
      "\n",
      "\n",
      "total =  18000\n",
      "prompt_total average =  13.90911111111111\n",
      "context_total average =  32.86494444444445\n",
      "input_total average =  46.774055555555556\n",
      "sql_total average =  15.691277777777778\n",
      "\n",
      "\n",
      "total =  19000\n",
      "prompt_total average =  13.90278947368421\n",
      "context_total average =  32.889631578947366\n",
      "input_total average =  46.79242105263158\n",
      "sql_total average =  15.672842105263157\n",
      "\n",
      "\n",
      "total =  20000\n",
      "prompt_total average =  13.90205\n",
      "context_total average =  32.8693\n",
      "input_total average =  46.77135\n",
      "sql_total average =  15.689\n",
      "\n",
      "\n",
      "total =  21000\n",
      "prompt_total average =  13.896\n",
      "context_total average =  32.87980952380953\n",
      "input_total average =  46.77580952380952\n",
      "sql_total average =  15.691428571428572\n",
      "\n",
      "\n",
      "total =  22000\n",
      "prompt_total average =  13.893454545454546\n",
      "context_total average =  32.917272727272724\n",
      "input_total average =  46.81072727272727\n",
      "sql_total average =  15.700909090909091\n",
      "\n",
      "\n",
      "total =  23000\n",
      "prompt_total average =  13.885826086956522\n",
      "context_total average =  32.91495652173913\n",
      "input_total average =  46.800782608695656\n",
      "sql_total average =  15.693173913043479\n",
      "\n",
      "\n",
      "total =  24000\n",
      "prompt_total average =  13.879041666666666\n",
      "context_total average =  32.90495833333333\n",
      "input_total average =  46.784\n",
      "sql_total average =  15.688583333333334\n",
      "\n",
      "\n",
      "total =  25000\n",
      "prompt_total average =  13.88392\n",
      "context_total average =  32.87168\n",
      "input_total average =  46.7556\n",
      "sql_total average =  15.67152\n",
      "\n",
      "\n",
      "total =  26000\n",
      "prompt_total average =  13.883538461538462\n",
      "context_total average =  32.882576923076925\n",
      "input_total average =  46.76611538461538\n",
      "sql_total average =  15.661846153846154\n",
      "\n",
      "\n",
      "total =  27000\n",
      "prompt_total average =  13.88774074074074\n",
      "context_total average =  32.90940740740741\n",
      "input_total average =  46.797148148148146\n",
      "sql_total average =  15.652074074074074\n",
      "\n",
      "\n",
      "total =  28000\n",
      "prompt_total average =  13.885\n",
      "context_total average =  32.89635714285714\n",
      "input_total average =  46.78135714285714\n",
      "sql_total average =  15.649892857142857\n",
      "\n",
      "\n",
      "total =  29000\n",
      "prompt_total average =  13.89003448275862\n",
      "context_total average =  32.87289655172414\n",
      "input_total average =  46.76293103448276\n",
      "sql_total average =  15.65196551724138\n",
      "\n",
      "\n",
      "total =  30000\n",
      "prompt_total average =  13.888933333333334\n",
      "context_total average =  32.87826666666667\n",
      "input_total average =  46.7672\n",
      "sql_total average =  15.652066666666666\n",
      "\n",
      "\n",
      "total =  31000\n",
      "prompt_total average =  13.889483870967743\n",
      "context_total average =  32.87725806451613\n",
      "input_total average =  46.76674193548387\n",
      "sql_total average =  15.652516129032257\n",
      "\n",
      "\n",
      "total =  32000\n",
      "prompt_total average =  13.88978125\n",
      "context_total average =  32.9073125\n",
      "input_total average =  46.79709375\n",
      "sql_total average =  15.65840625\n",
      "\n",
      "\n",
      "total =  33000\n",
      "prompt_total average =  13.887\n",
      "context_total average =  32.91657575757576\n",
      "input_total average =  46.80357575757576\n",
      "sql_total average =  15.664333333333333\n",
      "\n",
      "\n",
      "total =  34000\n",
      "prompt_total average =  13.884764705882352\n",
      "context_total average =  32.943970588235295\n",
      "input_total average =  46.82873529411765\n",
      "sql_total average =  15.67320588235294\n",
      "\n",
      "\n",
      "total =  35000\n",
      "prompt_total average =  13.885342857142858\n",
      "context_total average =  32.920857142857145\n",
      "input_total average =  46.8062\n",
      "sql_total average =  15.6788\n",
      "\n",
      "\n",
      "total =  36000\n",
      "prompt_total average =  13.885277777777778\n",
      "context_total average =  32.91875\n",
      "input_total average =  46.804027777777776\n",
      "sql_total average =  15.678472222222222\n",
      "\n",
      "\n",
      "total =  37000\n",
      "prompt_total average =  13.887\n",
      "context_total average =  32.94005405405405\n",
      "input_total average =  46.82705405405405\n",
      "sql_total average =  15.678513513513513\n",
      "\n",
      "\n",
      "total =  38000\n",
      "prompt_total average =  13.887973684210527\n",
      "context_total average =  32.947526315789474\n",
      "input_total average =  46.8355\n",
      "sql_total average =  15.684657894736842\n",
      "\n",
      "\n",
      "total =  39000\n",
      "prompt_total average =  13.89374358974359\n",
      "context_total average =  32.95225641025641\n",
      "input_total average =  46.846\n",
      "sql_total average =  15.68746153846154\n",
      "\n",
      "\n",
      "total =  40000\n",
      "prompt_total average =  13.890925\n",
      "context_total average =  32.948875\n",
      "input_total average =  46.8398\n",
      "sql_total average =  15.674325\n",
      "\n",
      "\n",
      "total =  41000\n",
      "prompt_total average =  13.890634146341464\n",
      "context_total average =  32.939926829268295\n",
      "input_total average =  46.83056097560976\n",
      "sql_total average =  15.67\n",
      "\n",
      "\n",
      "total =  42000\n",
      "prompt_total average =  13.888452380952382\n",
      "context_total average =  32.95473809523809\n",
      "input_total average =  46.84319047619048\n",
      "sql_total average =  15.66804761904762\n",
      "\n",
      "\n",
      "total =  43000\n",
      "prompt_total average =  13.888906976744186\n",
      "context_total average =  32.94218604651163\n",
      "input_total average =  46.83109302325581\n",
      "sql_total average =  15.67132558139535\n",
      "\n",
      "\n",
      "total =  44000\n",
      "prompt_total average =  13.886681818181819\n",
      "context_total average =  32.91493181818182\n",
      "input_total average =  46.80161363636363\n",
      "sql_total average =  15.665568181818182\n",
      "\n",
      "\n",
      "total =  45000\n",
      "prompt_total average =  13.8918\n",
      "context_total average =  32.92297777777778\n",
      "input_total average =  46.81477777777778\n",
      "sql_total average =  15.671466666666667\n",
      "\n",
      "\n",
      "total =  46000\n",
      "prompt_total average =  13.894565217391305\n",
      "context_total average =  32.916304347826085\n",
      "input_total average =  46.81086956521739\n",
      "sql_total average =  15.675521739130435\n",
      "\n",
      "\n",
      "total =  47000\n",
      "prompt_total average =  13.896765957446808\n",
      "context_total average =  32.928063829787234\n",
      "input_total average =  46.824829787234044\n",
      "sql_total average =  15.674851063829788\n",
      "\n",
      "\n",
      "total =  48000\n",
      "prompt_total average =  13.892645833333333\n",
      "context_total average =  32.912708333333335\n",
      "input_total average =  46.80535416666667\n",
      "sql_total average =  15.664854166666666\n",
      "\n",
      "\n",
      "total =  49000\n",
      "prompt_total average =  13.893571428571429\n",
      "context_total average =  32.91377551020408\n",
      "input_total average =  46.80734693877551\n",
      "sql_total average =  15.672326530612246\n",
      "\n",
      "\n",
      "total =  50000\n",
      "prompt_total average =  13.89252\n",
      "context_total average =  32.91348\n",
      "input_total average =  46.806\n",
      "sql_total average =  15.66992\n",
      "\n",
      "\n",
      "total =  51000\n",
      "prompt_total average =  13.892392156862746\n",
      "context_total average =  32.91658823529412\n",
      "input_total average =  46.80898039215686\n",
      "sql_total average =  15.667803921568627\n",
      "\n",
      "\n",
      "total =  52000\n",
      "prompt_total average =  13.88901923076923\n",
      "context_total average =  32.93803846153846\n",
      "input_total average =  46.82705769230769\n",
      "sql_total average =  15.66823076923077\n",
      "\n",
      "\n",
      "total =  53000\n",
      "prompt_total average =  13.889622641509433\n",
      "context_total average =  32.94430188679245\n",
      "input_total average =  46.833924528301885\n",
      "sql_total average =  15.663339622641509\n",
      "\n",
      "\n",
      "total =  54000\n",
      "prompt_total average =  13.889351851851853\n",
      "context_total average =  32.931592592592594\n",
      "input_total average =  46.82094444444444\n",
      "sql_total average =  15.660425925925926\n",
      "\n",
      "\n",
      "total =  55000\n",
      "prompt_total average =  13.886618181818182\n",
      "context_total average =  32.92372727272727\n",
      "input_total average =  46.810345454545455\n",
      "sql_total average =  15.660618181818181\n",
      "\n",
      "\n",
      "total =  56000\n",
      "prompt_total average =  13.887571428571428\n",
      "context_total average =  32.92232142857143\n",
      "input_total average =  46.809892857142856\n",
      "sql_total average =  15.663285714285715\n",
      "\n",
      "\n",
      "total =  57000\n",
      "prompt_total average =  13.885877192982456\n",
      "context_total average =  32.92389473684211\n",
      "input_total average =  46.809771929824564\n",
      "sql_total average =  15.665526315789474\n",
      "\n",
      "\n",
      "total =  58000\n",
      "prompt_total average =  13.885706896551724\n",
      "context_total average =  32.94127586206896\n",
      "input_total average =  46.82698275862069\n",
      "sql_total average =  15.664620689655173\n",
      "\n",
      "\n",
      "total =  59000\n",
      "prompt_total average =  13.887186440677967\n",
      "context_total average =  32.935610169491525\n",
      "input_total average =  46.82279661016949\n",
      "sql_total average =  15.67479661016949\n",
      "\n",
      "\n",
      "total =  60000\n",
      "prompt_total average =  13.888366666666666\n",
      "context_total average =  32.942083333333336\n",
      "input_total average =  46.83045\n",
      "sql_total average =  15.6764\n",
      "\n",
      "\n",
      "total =  61000\n",
      "prompt_total average =  13.892163934426229\n",
      "context_total average =  32.93480327868853\n",
      "input_total average =  46.826967213114756\n",
      "sql_total average =  15.677639344262294\n",
      "\n",
      "\n",
      "total =  62000\n",
      "prompt_total average =  13.894387096774194\n",
      "context_total average =  32.92529032258064\n",
      "input_total average =  46.81967741935484\n",
      "sql_total average =  15.685854838709677\n",
      "\n",
      "\n",
      "total =  63000\n",
      "prompt_total average =  13.89468253968254\n",
      "context_total average =  32.93590476190476\n",
      "input_total average =  46.8305873015873\n",
      "sql_total average =  15.68468253968254\n",
      "\n",
      "\n",
      "total =  64000\n",
      "prompt_total average =  13.89578125\n",
      "context_total average =  32.936375\n",
      "input_total average =  46.83215625\n",
      "sql_total average =  15.68578125\n",
      "\n",
      "\n",
      "total =  65000\n",
      "prompt_total average =  13.894646153846153\n",
      "context_total average =  32.93793846153846\n",
      "input_total average =  46.83258461538462\n",
      "sql_total average =  15.684553846153847\n",
      "\n",
      "\n",
      "total =  66000\n",
      "prompt_total average =  13.892924242424243\n",
      "context_total average =  32.95216666666666\n",
      "input_total average =  46.84509090909091\n",
      "sql_total average =  15.681318181818181\n",
      "\n",
      "\n",
      "total =  67000\n",
      "prompt_total average =  13.892179104477611\n",
      "context_total average =  32.96411940298508\n",
      "input_total average =  46.85629850746269\n",
      "sql_total average =  15.687492537313434\n",
      "\n",
      "\n",
      "total =  68000\n",
      "prompt_total average =  13.891691176470589\n",
      "context_total average =  32.95995588235294\n",
      "input_total average =  46.85164705882353\n",
      "sql_total average =  15.683235294117647\n",
      "\n",
      "\n",
      "total =  69000\n",
      "prompt_total average =  13.88940579710145\n",
      "context_total average =  32.956014492753624\n",
      "input_total average =  46.84542028985507\n",
      "sql_total average =  15.68627536231884\n",
      "\n",
      "\n",
      "total =  70000\n",
      "prompt_total average =  13.888757142857143\n",
      "context_total average =  32.94068571428571\n",
      "input_total average =  46.82944285714286\n",
      "sql_total average =  15.688071428571428\n",
      "\n",
      "\n",
      "total =  71000\n",
      "prompt_total average =  13.888323943661971\n",
      "context_total average =  32.95102816901409\n",
      "input_total average =  46.83935211267605\n",
      "sql_total average =  15.686140845070423\n",
      "\n",
      "\n",
      "total =  72000\n",
      "prompt_total average =  13.891791666666666\n",
      "context_total average =  32.95379166666667\n",
      "input_total average =  46.84558333333333\n",
      "sql_total average =  15.6915\n",
      "\n",
      "\n",
      "total =  73000\n",
      "prompt_total average =  13.889383561643836\n",
      "context_total average =  32.95017808219178\n",
      "input_total average =  46.839561643835616\n",
      "sql_total average =  15.6913698630137\n",
      "\n",
      "\n",
      "total =  74000\n",
      "prompt_total average =  13.891594594594595\n",
      "context_total average =  32.953851351351354\n",
      "input_total average =  46.84544594594595\n",
      "sql_total average =  15.696567567567568\n",
      "\n",
      "\n",
      "total =  75000\n",
      "prompt_total average =  13.89012\n",
      "context_total average =  32.95728\n",
      "input_total average =  46.8474\n",
      "sql_total average =  15.696093333333334\n",
      "\n",
      "\n",
      "total =  76000\n",
      "prompt_total average =  13.892434210526316\n",
      "context_total average =  32.957881578947365\n",
      "input_total average =  46.85031578947368\n",
      "sql_total average =  15.694973684210526\n",
      "\n",
      "\n",
      "total =  77000\n",
      "prompt_total average =  13.894194805194806\n",
      "context_total average =  32.962\n",
      "input_total average =  46.856194805194804\n",
      "sql_total average =  15.697987012987014\n",
      "\n",
      "\n",
      "total =  78000\n",
      "prompt_total average =  13.894871794871795\n",
      "context_total average =  32.963884615384615\n",
      "input_total average =  46.85875641025641\n",
      "sql_total average =  15.698358974358975\n",
      "\n",
      "\n",
      "total =  79000\n",
      "prompt_total average =  13.894620253164558\n",
      "context_total average =  32.960354430379745\n",
      "input_total average =  46.854974683544306\n",
      "sql_total average =  15.697569620253164\n",
      "\n",
      "\n",
      "total =  80000\n",
      "prompt_total average =  13.8934125\n",
      "context_total average =  32.954625\n",
      "input_total average =  46.8480375\n",
      "sql_total average =  15.69355\n",
      "\n",
      "\n",
      "total =  81000\n",
      "prompt_total average =  13.892913580246914\n",
      "context_total average =  32.952123456790126\n",
      "input_total average =  46.84503703703704\n",
      "sql_total average =  15.692666666666666\n",
      "\n",
      "\n",
      "total =  82000\n",
      "prompt_total average =  13.893341463414634\n",
      "context_total average =  32.9554756097561\n",
      "input_total average =  46.84881707317073\n",
      "sql_total average =  15.694426829268293\n",
      "\n",
      "\n",
      "total =  83000\n",
      "prompt_total average =  13.894590361445783\n",
      "context_total average =  32.94089156626506\n",
      "input_total average =  46.835481927710845\n",
      "sql_total average =  15.69232530120482\n",
      "\n",
      "\n",
      "total =  84000\n",
      "prompt_total average =  13.897190476190476\n",
      "context_total average =  32.95171428571429\n",
      "input_total average =  46.84890476190476\n",
      "sql_total average =  15.697559523809524\n",
      "\n",
      "\n",
      "total =  85000\n",
      "prompt_total average =  13.898235294117647\n",
      "context_total average =  32.949176470588235\n",
      "input_total average =  46.84741176470588\n",
      "sql_total average =  15.696964705882353\n",
      "\n",
      "\n",
      "total =  86000\n",
      "prompt_total average =  13.898046511627907\n",
      "context_total average =  32.94888372093023\n",
      "input_total average =  46.84693023255814\n",
      "sql_total average =  15.698116279069767\n",
      "\n",
      "\n",
      "total =  87000\n",
      "prompt_total average =  13.898816091954023\n",
      "context_total average =  32.95116091954023\n",
      "input_total average =  46.84997701149425\n",
      "sql_total average =  15.701528735632184\n",
      "\n",
      "\n",
      "total =  88000\n",
      "prompt_total average =  13.898647727272728\n",
      "context_total average =  32.958090909090906\n",
      "input_total average =  46.85673863636364\n",
      "sql_total average =  15.701193181818182\n",
      "\n",
      "\n",
      "total =  89000\n",
      "prompt_total average =  13.897426966292135\n",
      "context_total average =  32.950168539325844\n",
      "input_total average =  46.847595505617974\n",
      "sql_total average =  15.696898876404495\n",
      "\n",
      "\n",
      "total =  90000\n",
      "prompt_total average =  13.895177777777779\n",
      "context_total average =  32.95825555555555\n",
      "input_total average =  46.853433333333335\n",
      "sql_total average =  15.695933333333333\n",
      "\n",
      "\n",
      "total =  91000\n",
      "prompt_total average =  13.89367032967033\n",
      "context_total average =  32.952835164835165\n",
      "input_total average =  46.84650549450549\n",
      "sql_total average =  15.690329670329671\n",
      "\n",
      "\n",
      "total =  92000\n",
      "prompt_total average =  13.895184782608696\n",
      "context_total average =  32.96208695652174\n",
      "input_total average =  46.85727173913043\n",
      "sql_total average =  15.688717391304348\n",
      "\n",
      "\n",
      "total =  93000\n",
      "prompt_total average =  13.894602150537635\n",
      "context_total average =  32.96737634408602\n",
      "input_total average =  46.861978494623656\n",
      "sql_total average =  15.692494623655914\n",
      "\n",
      "\n",
      "total =  94000\n",
      "prompt_total average =  13.896510638297872\n",
      "context_total average =  32.96841489361702\n",
      "input_total average =  46.86492553191489\n",
      "sql_total average =  15.692702127659574\n",
      "\n",
      "\n",
      "total =  95000\n",
      "prompt_total average =  13.895642105263159\n",
      "context_total average =  32.96395789473684\n",
      "input_total average =  46.8596\n",
      "sql_total average =  15.697757894736842\n",
      "\n",
      "\n",
      "total =  96000\n",
      "prompt_total average =  13.897427083333334\n",
      "context_total average =  32.96163541666667\n",
      "input_total average =  46.8590625\n",
      "sql_total average =  15.701260416666667\n",
      "\n",
      "\n",
      "total =  97000\n",
      "prompt_total average =  13.898123711340206\n",
      "context_total average =  32.95405154639175\n",
      "input_total average =  46.85217525773196\n",
      "sql_total average =  15.70060824742268\n",
      "\n",
      "\n",
      "total =  98000\n",
      "prompt_total average =  13.896530612244899\n",
      "context_total average =  32.948112244897956\n",
      "input_total average =  46.84464285714286\n",
      "sql_total average =  15.701061224489797\n",
      "\n",
      "\n",
      "total =  99000\n",
      "prompt_total average =  13.897171717171718\n",
      "context_total average =  32.948040404040405\n",
      "input_total average =  46.84521212121212\n",
      "sql_total average =  15.704222222222223\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "prompt_total = 0\n",
    "context_total = 0\n",
    "sql_total = 0\n",
    "for idx in df_dataset.index:\n",
    "    prompt = df_dataset_filtered['sql_prompt'][idx]\n",
    "    context = df_dataset_filtered['sql_context'][idx]\n",
    "    sql = df_dataset_filtered['sql'][idx]\n",
    "\n",
    "    prompt_count = len(prompt.split())\n",
    "    context_count = len(context.split())\n",
    "    sql_count = len(sql.split())\n",
    "\n",
    "    prompt_total+=prompt_count\n",
    "    context_total+=context_count\n",
    "    sql_total+=sql_count\n",
    "\n",
    "    if idx%1000==0:\n",
    "        if idx>1:\n",
    "            print(\"total = \",idx)\n",
    "            print(\"prompt_total average = \",prompt_total/idx)\n",
    "            print(\"context_total average = \",context_total/idx)\n",
    "            print(\"input_total average = \",(prompt_total+context_total)/idx)\n",
    "            print(\"sql_total average = \",sql_total/idx)\n",
    "            print()\n",
    "            print()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "nl2sql_project_env",
   "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}