4 Table extraction with fine-tuned GPT 3.5.ipynb.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8431c4cb",
   "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>Prompt</th>\n",
       "      <th>Winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Oxides weight percentage content in EAF slag, ...</td>\n",
       "      <td>Material Name, SiO2, Al2O3, Fe2O3, CaO, MgO, S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Properties of cement and fly ashes used \\n [['...</td>\n",
       "      <td>Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chemical composition of SFS. \\n [['Constituent...</td>\n",
       "      <td>Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chemical compositions of cement, silica fume a...</td>\n",
       "      <td>Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cement composition oxide wt.% \\n [['SiO2', 'Al...</td>\n",
       "      <td>Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              Prompt  \\\n",
       "0  Oxides weight percentage content in EAF slag, ...   \n",
       "1  Properties of cement and fly ashes used \\n [['...   \n",
       "2  Chemical composition of SFS. \\n [['Constituent...   \n",
       "3  Chemical compositions of cement, silica fume a...   \n",
       "4  Cement composition oxide wt.% \\n [['SiO2', 'Al...   \n",
       "\n",
       "                                              Winner  \n",
       "0  Material Name, SiO2, Al2O3, Fe2O3, CaO, MgO, S...  \n",
       "1  Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO...  \n",
       "2  Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO...  \n",
       "3  Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO...  \n",
       "4  Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gt= pd.read_csv('Table dataset.csv')\n",
    "gt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "5fde2888",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "200"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset_c = []\n",
    "for _, (Table, Winner) in gt.iterrows():\n",
    "    Prompt = f\"\"\"CSV Format Requirements: Ensure the CSV includes columns for Material Name, SiO2, Al2O3, Fe2O3, CaO, MgO, SO3, TiO2, MnO, K2O, Na2O, LOI.\n",
    "    Handling Missing Data: Use 'null' for any missing data in these columns.\n",
    "    Non-relevant Data: If the table's content is not related to the specified chemicals, respond with 'NR' only.\n",
    "\n",
    "    Unstructured HTML table: {Table}\"\"\"\n",
    "\n",
    "\n",
    "    dataset_c.append({\"Prompt\": Prompt, \"Winner\": str(Winner).replace(\"NaN\",\"null\")})\n",
    "    \n",
    "len(dataset_c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "53c8a271",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO2,MnO,K2O,Na2O,LOI\\nPC,20.2,5.9,3.3,63.3,1.8,2.9,NaN,NaN,0.8,0.5,0.8\\nHL1,43.3,25.1,4.9,20.3,0.8,2.5,NaN,NaN,1.3,0.2,1.19\\nHL2,47.1,17.4,8.3,14.0,1.9,4.6,NaN,NaN,1.8,2.4,1.7\\nLL1,58.6,21.9,9.3,4.4,1.4,0.4,NaN,NaN,1.8,0.3,1.4\\nLL2,59.4,25.8,5.8,2.0,0.6,0.1,NaN,NaN,3.8,0.4,1.6'"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gt.loc[1].Winner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "eb10ae75",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reformatting for fine-tuning\n",
    "fine_tuning_dataset = []\n",
    "for item in dataset_c:\n",
    "    fine_tuning_item = {\n",
    "        \"messages\": [\n",
    "            {\"role\": \"system\", \"content\": \"You are an assistant trained to parse unstructured HTML tables into CSV format.\"},\n",
    "            {\"role\": \"user\", \"content\": json.dumps(item[\"Prompt\"])},\n",
    "            {\"role\": \"assistant\", \"content\": json.dumps(item[\"Winner\"])}\n",
    "        ]\n",
    "    }\n",
    "    fine_tuning_dataset.append(fine_tuning_item)\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "4a5bdeea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "200"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(fine_tuning_dataset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "208bd34f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'messages': [{'role': 'system',\n",
       "   'content': 'You are an assistant trained to parse unstructured HTML tables into CSV format.'},\n",
       "  {'role': 'user',\n",
       "   'content': '\"CSV Format Requirements: Ensure the CSV includes columns for Material Name, SiO2, Al2O3, Fe2O3, CaO, MgO, SO3, TiO2, MnO, K2O, Na2O, LOI.\\\\n    Handling Missing Data: Use \\'null\\' for any missing data in these columns.\\\\n    Non-relevant Data: If the table\\'s content is not related to the specified chemicals, respond with \\'NR\\' only.\\\\n\\\\n    Unstructured HTML table: Properties of cement and fly ashes used \\\\n [[\\'Chemical composition\\', \\'Chemical composition\\', \\'Chemical composition\\', \\'Chemical composition\\', \\'Chemical composition\\', \\'\\'], [\\'Oxide (%)\\', \\'PC\\', \\'HL1\\', \\'HL2\\', \\'LL1\\', \\'LL2\\'], [\\'CaO\\', \\'63.3\\', \\'20.3\\', \\'14.0\\', \\'4.4\\', \\'2.0\\'], [\\'SiO2\\', \\'20.2\\', \\'43.3\\', \\'47.1\\', \\'58.6\\', \\'59.4\\'], [\\'Al2O3\\', \\'5.9\\', \\'25.1\\', \\'17.4\\', \\'21.9\\', \\'25.8\\'], [\\'Fe2O3\\', \\'3.3\\', \\'4.9\\', \\'8.3\\', \\'9.3\\', \\'5.8\\'], [\\'MgO\\', \\'1.8\\', \\'0.8\\', \\'1.9\\', \\'1.4\\', \\'0.6\\'], [\\'SO3\\', \\'2.9\\', \\'2.5\\', \\'4.6\\', \\'0.4\\', \\'0.1\\'], [\\'K2O\\', \\'0.8\\', \\'1.3\\', \\'1.8\\', \\'1.8\\', \\'3.8\\'], [\\'Na2O\\', \\'0.5\\', \\'0.2\\', \\'2.4\\', \\'0.3\\', \\'0.4\\'], [\\'LOI\\', \\'0.8\\', \\'1.19\\', \\'1.7\\', \\'1.4\\', \\'1.6\\'], [\\'IR\\', \\'0.4\\', \\'nd\\', \\'nd\\', \\'nd\\', \\'nd\\'], [\\'Physical and mechanical properties\\', \\'Physical and mechanical properties\\', \\'Physical and mechanical properties\\', \\'Physical and mechanical properties\\', \\'Physical and mechanical properties\\', \\'Physical and mechanical properties\\'], [\\'Compressive strength (MPa)\\', \\'\\', \\'\\', \\'\\', \\'\\', \\'\\'], [\\'7 days\\', \\'20.7\\', \\'\\', \\'\\', \\'\\', \\'\\'], [\\'28 days\\', \\'31.6\\', \\'-\\', \\'-\\', \\'-\\', \\'-\\'], [\\'90 days\\', \\'42.9\\', \\'\\', \\'\\', \\'\\', \\'\\'], [\\'Density (g/cm3)\\', \\'3.12\\', \\'2.25\\', \\'2.28\\', \\'2.08\\', \\'2.02\\'], [\\'Blaine fineness (m2/kg)\\', \\'292\\', \\'455\\', \\'275\\', \\'302\\', \\'290\\'], [\\'PAI with PC (%)\\', \\'-\\', \\'84.5\\', \\'61.0\\', \\'66.3\\', \\'54.7\\'], [\\'PAI with lime (MPa)\\', \\'-\\', \\'32.0\\', \\'17.3\\', \\'14.7\\', \\'9.3\\'], [\\'nd = not determined. PAI = Pozzolanic activity index.\\', \\'nd = not determined. PAI = Pozzolanic activity index.\\', \\'nd = not determined. PAI = Pozzolanic activity index.\\', \\'nd = not determined. PAI = Pozzolanic activity index.\\', \\'nd = not determined. PAI = Pozzolanic activity index.\\', \\'nd = not determined. PAI = Pozzolanic activity index.\\']]\"'},\n",
       "  {'role': 'assistant',\n",
       "   'content': '\"Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO2,MnO,K2O,Na2O,LOI\\\\nPC,20.2,5.9,3.3,63.3,1.8,2.9,null,null,0.8,0.5,0.8\\\\nHL1,43.3,25.1,4.9,20.3,0.8,2.5,null,null,1.3,0.2,1.19\\\\nHL2,47.1,17.4,8.3,14.0,1.9,4.6,null,null,1.8,2.4,1.7\\\\nLL1,58.6,21.9,9.3,4.4,1.4,0.4,null,null,1.8,0.3,1.4\\\\nLL2,59.4,25.8,5.8,2.0,0.6,0.1,null,null,3.8,0.4,1.6\"'}]}"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fine_tuning_dataset[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "8db8f7ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "train_data, test_data = train_test_split(fine_tuning_dataset, test_size=0.1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "4fb89457",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "with open('fine_tuning_TE_CC.jsonl', 'w') as file:\n",
    "    for item in fine_tuning_dataset:\n",
    "        json.dump(item, file)\n",
    "        file.write('\\n')\n",
    "        \n",
    "\n",
    "with open('fine_tuning_TRAIN_TE_CC.jsonl', 'w') as file:\n",
    "    for item in train_data:\n",
    "        json.dump(item, file)\n",
    "        file.write('\\n')\n",
    "        \n",
    "\n",
    "with open('fine_tuning_TEST_TE_CC.jsonl', 'w') as file:\n",
    "    for item in test_data:\n",
    "        json.dump(item, file)\n",
    "        file.write('\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "9089fc2a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "200"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(fine_tuning_dataset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a3369d9-e0ee-44eb-aa92-8826a7085dbb",
   "metadata": {},
   "outputs": [],
   "source": [
    "api_key=api_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "cb9f720a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\r\n",
      "  \"object\": \"file\",\r\n",
      "  \"id\": \"file-R9fcrmV8QimJm5bRkdnWMeyn\",\r\n",
      "  \"purpose\": \"fine-tune\",\r\n",
      "  \"filename\": \"fine_tuning_TE_CC.jsonl\",\r\n",
      "  \"bytes\": 260979,\r\n",
      "  \"created_at\": 1705548780,\r\n",
      "  \"status\": \"processed\",\r\n",
      "  \"status_details\": null\r\n",
      "}\r\n"
     ]
    }
   ],
   "source": [
    "!curl https://api.openai.com/v1/files \\\n",
    "  -H \"Authorization: Bearer ${api_key}\" \\\n",
    "  -F \"purpose=fine-tune\" \\\n",
    "  -F \"file=@fine_tuning_TE_CC.jsonl\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "f3e70ca8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\r\n",
      "  \"object\": \"file\",\r\n",
      "  \"id\": \"file-1beLBpaYIuSKWNXVLXs3gM42\",\r\n",
      "  \"purpose\": \"fine-tune\",\r\n",
      "  \"filename\": \"fine_tuning_TRAIN_TE_CC.jsonl\",\r\n",
      "  \"bytes\": 238224,\r\n",
      "  \"created_at\": 1705548782,\r\n",
      "  \"status\": \"processed\",\r\n",
      "  \"status_details\": null\r\n",
      "}\r\n"
     ]
    }
   ],
   "source": [
    "!curl https://api.openai.com/v1/files \\\n",
    "  -H r\"Authorization: Bearer ${api_key}\" \\\n",
    "  -F \"purpose=fine-tune\" \\\n",
    "  -F \"file=@fine_tuning_TRAIN_TE_CC.jsonl\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "a6f19185",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\r\n",
      "  \"object\": \"file\",\r\n",
      "  \"id\": \"file-YtcQNMpcbEOsGbbGAA8EtkyX\",\r\n",
      "  \"purpose\": \"fine-tune\",\r\n",
      "  \"filename\": \"fine_tuning_TEST_TE_CC.jsonl\",\r\n",
      "  \"bytes\": 22755,\r\n",
      "  \"created_at\": 1705548782,\r\n",
      "  \"status\": \"processed\",\r\n",
      "  \"status_details\": null\r\n",
      "}\r\n"
     ]
    }
   ],
   "source": [
    "!curl https://api.openai.com/v1/files \\\n",
    "  -H \"Authorization: Bearer ${api_key} \\\n",
    "  -F \"purpose=fine-tune\" \\\n",
    "  -F \"file=@fine_tuning_TEST_TE_CC.jsonl\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "38881a9b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FineTuningJob(id='ftjob-wgyS1iM7UHyiSxPS6epXJ4Df', created_at=1705548783, error=None, fine_tuned_model=None, finished_at=None, hyperparameters=Hyperparameters(n_epochs='auto', batch_size='auto', learning_rate_multiplier='auto'), model='gpt-3.5-turbo-1106', object='fine_tuning.job', organization_id='org-CGIKMf8W0RLVflPNXCgE6SzT', result_files=[], status='validating_files', trained_tokens=None, training_file='file-q0vjmL1s9EIrud1SsSO1vLT7', validation_file='file-XMf29I1HR1H2O7FrQeO4WzmN')"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from openai import OpenAI\n",
    "client = OpenAI(api_key=api_key)\n",
    "\n",
    "client.fine_tuning.jobs.create(\n",
    "  training_file=\"file-q0vjmL1s9EIrud1SsSO1vLT7\", \n",
    "  validation_file=\"file-XMf29I1HR1H2O7FrQeO4WzmN\", \n",
    "  model=\"gpt-3.5-turbo-1106\", \n",
    "  hyperparameters={\n",
    "    \"n_epochs\":\"auto\"\n",
    "  }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "09de3091",
   "metadata": {},
   "outputs": [],
   "source": [
    "#client.fine_tuning.jobs.cancel(\"ftjob-dKkESY7D9zgJxlIczb1bR82E\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "a5414e4f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\r\n",
      "  \"object\": \"fine_tuning.job\",\r\n",
      "  \"id\": \"ftjob-Y5A5vvGYACRcuHtRf4tWLRW1\",\r\n",
      "  \"model\": \"gpt-3.5-turbo-1106\",\r\n",
      "  \"created_at\": 1704920850,\r\n",
      "  \"finished_at\": 1704923268,\r\n",
      "  \"fine_tuned_model\": \"ft:gpt-3.5-turbo-1106:personal::8faiLnzJ\",\r\n",
      "  \"organization_id\": \"org-CGIKMf8W0RLVflPNXCgE6SzT\",\r\n",
      "  \"result_files\": [\r\n",
      "    \"file-iZZ17sdaLGNJpKUemqmO0LQv\"\r\n",
      "  ],\r\n",
      "  \"status\": \"succeeded\",\r\n",
      "  \"validation_file\": \"file-XMf29I1HR1H2O7FrQeO4WzmN\",\r\n",
      "  \"training_file\": \"file-q0vjmL1s9EIrud1SsSO1vLT7\",\r\n",
      "  \"hyperparameters\": {\r\n",
      "    \"n_epochs\": 3,\r\n",
      "    \"batch_size\": 1,\r\n",
      "    \"learning_rate_multiplier\": 2\r\n",
      "  },\r\n",
      "  \"trained_tokens\": 269460,\r\n",
      "  \"error\": null\r\n",
      "}"
     ]
    }
   ],
   "source": [
    "!curl https://api.openai.com/v1/fine_tuning/jobs/ftjob-Y5A5vvGYACRcuHtRf4tWLRW1 \\\n",
    "  -H \"Authorization: Bearer ${api_key}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "c79acde1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current\n",
      "                                 Dload  Upload   Total   Spent    Left  Speed\n",
      "100 10724    0 10724    0     0  17409      0 --:--:-- --:--:-- --:--:-- 17380\n"
     ]
    }
   ],
   "source": [
    "!curl https://api.openai.com/v1/files/file-iZZ17sdaLGNJpKUemqmO0LQv/content \\\n",
    "  -H \"Authorization: Bearer ${api_key}\" > file.jsonl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "a0d0e9da",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Initialize embeddings and Chroma DB\n",
    "embeddings = SentenceTransformerEmbeddings(model_name=\"all-MiniLM-L6-v2\")\n",
    "persist_directory = 'data/chroma/etsminilm'\n",
    "\n",
    "tvdb = Chroma(persist_directory=persist_directory, embedding_function=embeddings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "a84b7b20",
   "metadata": {},
   "outputs": [],
   "source": [
    "question = \"major oxides or chemical compositions of materials including Al2O3, Fe2O3, CaO, MgO, So3, Tio2, Mno, K2o, Na2O, LOI, loss of ignition, ignition loss\"\n",
    "docs = tvdb.similarity_search(\n",
    "    question,\n",
    "    k=100000)\n",
    "#del docs[:70000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "a1a48bfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_responses(responses):\n",
    "    # Implement the logic to save 'responses'\n",
    "    # For example, saving to a file:\n",
    "    filename = f'responses_some.csv'\n",
    "    # Convert responses to DataFrame and save as CSV\n",
    "    pd.DataFrame(responses, columns=['Response', 'Caption', 'DOI']).to_csv(filename, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a9602f6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "c15849f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "from openai import OpenAI\n",
    "client = OpenAI(api_key=api_key)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "a0a2d511",
   "metadata": {},
   "outputs": [],
   "source": [
    "error_tables = [] \n",
    "responses = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c696f309-e2cc-4cde-9a5e-1a0e41c53023",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "for i, doc in enumerate(tqdm(docs)):\n",
    "\n",
    "    try: \n",
    "        Table = doc.page_content  # The caption of the table\n",
    "        caption = Table.split('/n')[0]\n",
    "        doi = doc.metadata['doi']  # The DOI of the table\n",
    "\n",
    "        AA = json.dumps(f\"\"\"CSV Format Requirements: Ensure the CSV includes columns for Material Name, SiO2, Al2O3, Fe2O3, CaO, MgO, SO3, TiO2, MnO, K2O, Na2O, LOI.\n",
    "        Handling Missing Data: Use 'null' for any missing data in these columns.\n",
    "        Non-relevant Data: If the table's content is not related to the specified chemicals, respond with 'NR' only.\n",
    "\n",
    "        Unstructured HTML table: {Table}\"\"\")\n",
    "\n",
    "        # Check if this caption and DOI already exist in the existing DataFrame\n",
    "        response = client.chat.completions.create(\n",
    "        model=\"ft:gpt-3.5-turbo-1106:personal::8faiLnzJ\",messages=[\n",
    "        {\"role\": \"system\", \"content\": \"You are an assistant trained to parse unstructured HTML tables into CSV format.\"},\n",
    "        {\"role\": \"user\", \"content\": f\"\"\"CSV Format Requirements: Ensure the CSV includes columns for Material Name, SiO2, Al2O3, Fe2O3, CaO, MgO, SO3, TiO2, MnO, K2O, Na2O, LOI.\n",
    "        Handling Missing Data: Use 'null' for any missing data in these columns.\n",
    "        Non-relevant Data: If the table's content is not related to the specified chemicals, respond with 'NR' only.\n",
    "\n",
    "        Unstructured HTML table: {Table}\"\"\"}],\n",
    "        temperature=0.0,\n",
    "        max_tokens=800,\n",
    "        top_p=1,)\n",
    "\n",
    "        RR=response.choices[0].message.content\n",
    "        responses.append((RR, caption, doi))\n",
    "\n",
    "        # Check if it's time to save the responses\n",
    "        if (i + 1) % 300 == 0:\n",
    "            save_responses(responses)\n",
    "\n",
    "\n",
    "    except Exception as e:\n",
    "        error_tables.append((Table, e))  # Append the table and the error for later review"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "dd8f9d64",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 30001/30001 [01:09<00:00, 434.79it/s] \n"
     ]
    }
   ],
   "source": [
    "keywords = {\"al2o3\", \"sio2\", \"fe2o3\", \"cao\", \"mgo\", \"so3\", \"tio2\", \"mno\", \"k2o\", \"na2o\", \"loi\", \"loss of ignition\", \"ignition loss\"}\n",
    "\n",
    "for i, doc in enumerate(tqdm(docs)):\n",
    "    if any(keyword in str(doc).lower() for keyword in keywords):\n",
    "        try: \n",
    "            Table = doc.page_content  # The caption of the table\n",
    "            caption = Table.split('/n')[0]\n",
    "            doi = doc.metadata['doi']  # The DOI of the table\n",
    "\n",
    "            AA = json.dumps(f\"\"\"CSV Format Requirements: Ensure the CSV includes columns for Material Name, SiO2, Al2O3, Fe2O3, CaO, MgO, SO3, TiO2, MnO, K2O, Na2O, LOI.\n",
    "            Handling Missing Data: Use 'null' for any missing data in these columns.\n",
    "            Non-relevant Data: If the table's content is not related to the specified chemicals, respond with 'NR' only.\n",
    "\n",
    "            Unstructured HTML table: {Table}\"\"\")\n",
    "\n",
    "            # Check if this caption and DOI already exist in the existing DataFrame\n",
    "            response = client.chat.completions.create(\n",
    "            model=\"ft:gpt-3.5-turbo-1106:personal::8faiLnzJ\",messages=[\n",
    "            {\"role\": \"system\", \"content\": \"You are an assistant trained to parse unstructured HTML tables into CSV format.\"},\n",
    "            {\"role\": \"user\", \"content\": f\"\"\"CSV Format Requirements: Ensure the CSV includes columns for Material Name, SiO2, Al2O3, Fe2O3, CaO, MgO, SO3, TiO2, MnO, K2O, Na2O, LOI.\n",
    "            Handling Missing Data: Use 'null' for any missing data in these columns.\n",
    "            Non-relevant Data: If the table's content is not related to the specified chemicals, respond with 'NR' only.\n",
    "\n",
    "            Unstructured HTML table: {Table}\"\"\"}],\n",
    "            temperature=0.0,\n",
    "            max_tokens=800,\n",
    "            top_p=1,)\n",
    "\n",
    "            RR=response.choices[0].message.content\n",
    "            responses.append((RR, caption, doi))\n",
    "\n",
    "            # Check if it's time to save the responses\n",
    "            if (i + 1) % 300 == 0:\n",
    "                save_responses(responses)\n",
    "\n",
    "\n",
    "        except Exception as e:\n",
    "            error_tables.append((Table, e))  # Append the table and the error for later review"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "6e394806",
   "metadata": {},
   "outputs": [],
   "source": [
    "save_responses(responses)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "07b58aae",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import io\n",
    "\n",
    "def is_header(row, num_columns):\n",
    "    \"\"\"\n",
    "    Check if a row can be considered as a header based on its format.\n",
    "    \"\"\"\n",
    "    columns = row.split(\",\")\n",
    "    if len(columns) != num_columns and column[0]=='Material Name':\n",
    "        return False\n",
    "    \n",
    "def parse_response_to_df(response, expected_columns, caption, doi):\n",
    "    # Clean the response string\n",
    "    cleaned_response = str(response).replace(\"null\",\"NaN\")\n",
    "    cleaned_response = ''.join(map(str, cleaned_response))\n",
    "    cleaned_response = cleaned_response.strip('[').strip(']').strip('\"\"').strip('''''').strip('\\'\"').replace('\\\\n', '\\n').replace('\\\\', '')\n",
    "    # Convert expected columns to lower case\n",
    "    expected_columns_lower = [col.lower() for col in expected_columns]\n",
    "    \n",
    "    if cleaned_response == ['NR']: return pd.DataFrame(columns=expected_columns) , []\n",
    "    \n",
    "    # Process each row\n",
    "    conf_tables = []\n",
    "    \n",
    "    # Initialize a new DataFrame with lowercased expected columns\n",
    "    new_df = pd.DataFrame(columns=expected_columns_lower)\n",
    "\n",
    "    try:\n",
    "        # Convert the cleaned response string to a DataFrame\n",
    "        df = pd.read_csv(io.StringIO(cleaned_response))\n",
    "        # Convert all column names in df to lower case for comparison\n",
    "        df.columns = [col.lower() for col in df.columns]\n",
    "        df.columns = df.columns.str.strip()\n",
    "\n",
    "        # Copy data from original DataFrame or fill with NaN\n",
    "        for exp_col in expected_columns_lower:\n",
    "            if exp_col in df.columns:\n",
    "                new_df[exp_col] = df[exp_col]\n",
    "            else:\n",
    "                new_df[exp_col] = pd.NA\n",
    "\n",
    "        return new_df, conf_tables\n",
    "\n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        print(cleaned_response)\n",
    "\n",
    "        return pd.DataFrame(columns=expected_columns_lower), conf_tables\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "e2587c84",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error tokenizing data. C error: Expected 12 fields in line 3, saw 13\n",
      "\n",
      "Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO2,MnO,K2O,Na2O,LOI\n",
      "Normal cement,20.7,4.96,3.33,65.2,0.57,2.49,NaN,NaN,0.32,0.10,NaN\n",
      "Cement with glass addition, second test,21.3,5.37,3.37,65.1,0.61,2.47,NaN,NaN,0.31,0.20,NaN\n",
      "Error tokenizing data. C error: Expected 12 fields in line 9, saw 13\n",
      "\n",
      "Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO2,MnO,K2O,Na2O,LOI\n",
      "Glass 1,80.5,2.2,NaN,NaN,NaN,NaN,NaN,NaN,0.4,3.8,NaN\n",
      "Glass 2,73.0,NaN,NaN,NaN,NaN,NaN,NaN,NaN,4.5,NaN,NaN\n",
      "Glass 3,77.9,0.8,NaN,0.8,NaN,NaN,NaN,NaN,2.3,4.1,NaN\n",
      "Glass 4,71-73.5,NaN,0.01,5-6,3.5-4.5,NaN,NaN,NaN,0-1.5,14-17,NaN\n",
      "Glass 5,61.5,NaN,0.8,NaN,NaN,NaN,NaN,NaN,5.3,8.8,NaN\n",
      "Glass 6,72.68,NaN,NaN,12.76,0.26,NaN,NaN,NaN,NaN,13.25,NaN\n",
      "Glass 7,63.0,NaN,NaN,0.3,0.2,NaN,NaN,NaN,6.0,7.6,NaN\n",
      "Glass 8,72.0,5.0,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,11.0,NaN\n",
      "Glass 9,57.3,3.2,NaN,4.7,8.7,NaN,NaN,NaN,0.5,1.9,NaN\n",
      "Glass 10,75.3,6.2,NaN,1.1,NaN,NaN,NaN,NaN,0.8,5.7,NaN\n",
      "Glass 11,68.0,3.0,3.0,NaN,NaN,NaN,NaN,NaN,NaN,22.0,NaN\n",
      "Error tokenizing data. C error: Expected 12 fields in line 9, saw 13\n",
      "\n",
      "Material Name,SiO2,Al2O3,Fe2O3,CaO,MgO,SO3,TiO2,MnO,K2O,Na2O,LOI\n",
      "Glass 1,80.5,2.2,NaN,NaN,NaN,NaN,NaN,NaN,0.4,3.8,NaN\n",
      "Glass 2,73.0,NaN,NaN,NaN,NaN,NaN,NaN,NaN,4.5,NaN,NaN\n",
      "Glass 3,77.9,0.8,NaN,0.8,NaN,NaN,NaN,NaN,2.3,4.1,NaN\n",
      "Glass 4,71-73.5,NaN,0.01,5-6,3.5-4.5,NaN,NaN,NaN,0-1.5,14-17,NaN\n",
      "Glass 5,61.5,NaN,0.8,NaN,NaN,NaN,NaN,NaN,5.3,8.8,NaN\n",
      "Glass 6,72.68,NaN,NaN,12.76,0.26,NaN,NaN,NaN,NaN,13.25,NaN\n",
      "Glass 7,63.0,NaN,NaN,0.3,0.2,NaN,NaN,NaN,6.0,7.6,NaN\n",
      "Glass 8,72.0,5.0,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,11.0,NaN\n",
      "Glass 9,57.3,3.2,NaN,4.7,8.7,NaN,NaN,NaN,0.5,1.9,NaN\n",
      "Glass 10,75.3,6.2,NaN,1.1,NaN,NaN,NaN,NaN,0.8,5.7,NaN\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "# Expected columns\n",
    "expected_columns = ['Material Name', 'SiO2', 'Al2O3', 'Fe2O3', 'CaO', 'MgO', 'SO3', 'TiO2', 'MnO', 'K2O', 'Na2O', 'LOI']\n",
    "\n",
    "# Create a DataFrame for each response\n",
    "dfs = []\n",
    "conf_tables = []\n",
    "for response_data, caption, doi in responses:\n",
    "    df , conf_table = parse_response_to_df(response_data, expected_columns, caption, doi)\n",
    "\n",
    "    # Add 'caption' and 'doi' columns to the DataFrame\n",
    "    df['caption'] = caption\n",
    "    df['doi'] = doi\n",
    "    \n",
    "    dfs.append(df)\n",
    "    conf_tables.append(conf_table)\n",
    "\n",
    "# Concatenate all DataFrames into one\n",
    "final_df = pd.concat(dfs, ignore_index=True) if dfs else pd.DataFrame(columns=expected_columns + ['caption', 'doi'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "41565cd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the DataFrame as a CSV file\n",
    "final_df.to_csv('all_materials_extracted_fgpt.csv', index=False)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "soroush1",
   "language": "python",
   "name": "soroush1"
  },
  "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.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}