18_merge_metadata_and_text.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load the metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "\n",
    "metadata_mapping = pd.read_excel(os.path.join(os.getcwd(),'FAA DRS','DRS Document Types Metadata Mapping.xlsx')) # from https://drs.faa.gov/help/helpdetails and https://drs.faa.gov/help/helpdetails\n",
    "\n",
    "doc_types = pd.unique(metadata_mapping.loc[:, \"Document Type Name in API request\"])\n",
    "doc_types = [val for val in doc_types if isinstance(val, str)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### STC\n",
    "merge metadata, raw text and extracted text."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\victor\\anaconda3\\envs\\faa-nlp-drs\\Lib\\site-packages\\pyarrow\\pandas_compat.py:373: FutureWarning: is_sparse is deprecated and will be removed in a future version. Check `isinstance(dtype, pd.SparseDtype)` instead.\n",
      "  if _pandas_api.is_sparse(col):\n"
     ]
    }
   ],
   "source": [
    "import glob\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "df_stc_pdftext_googlevision = pd.read_parquet(r\"C:\\Users\\victor\\Documents\\DeepLearning\\FAA NLP Project\\database\\decoded_pdf_using_text-from-pdf-googlevision.parquet\")\n",
    "#df_stc_pdftext_pdfplumber = pd.read_parquet(r\"C:\\Users\\victor\\Documents\\DeepLearning\\FAA NLP Project\\database\\decoded_pdf_using_text-from-pdf-pdfplumber.parquet\")\n",
    "\n",
    "df_stc_metadata = pd.read_parquet(glob.glob(os.path.join(os.getcwd(), 'database', 'all drs', 'stc*.parquet'))[-1])\n",
    "df_stc_metadata['documentGuid'] = df_stc_metadata['documentGuid'].astype('string')\n",
    "\n",
    "df_stc_metadata_and_text = df_stc_metadata.join(df_stc_pdftext_googlevision.set_index('documentGuid'), on=\"documentGuid\", how=\"right\", lsuffix=\"_metadata\", rsuffix=\"_googlevision\")\n",
    "df_stc_metadata_and_text.to_parquet(os.path.join(os.getcwd(),'database','stc_metadata_and_text.parquet'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Any other DRS document\n",
    "merge metadata and raw text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "import pandas as pd\n",
    "import logging\n",
    "import pdfplumber\n",
    "\n",
    "logging.basicConfig(filename='18_merge_metadata_and_text.log', encoding='utf-8',format='%(asctime)s %(message)s', level=logging.INFO)\n",
    "\n",
    "# for all doc types\n",
    "\n",
    "for ocr_option in ['googlevision', 'pdfplumber']:\n",
    "    text_source = 'text-from-pdf-'+ocr_option\n",
    "\n",
    "    if ocr_option=='pdfplumber':\n",
    "        available_txts = glob.glob(os.path.join(os.getcwd(),'database','data','stc','pdf','*.pdf'))\n",
    "    elif ocr_option=='googlevision':\n",
    "        available_txts = glob.glob(os.path.join(os.getcwd(),'database','data','stc','text-from-pdf','*.txt'))\n",
    "\n",
    "    decoded_pdf = [[]]\n",
    "\n",
    "    for path_file in available_txts:\n",
    "        try:\n",
    "            if ocr_option=='pdfplumber':\n",
    "                with pdfplumber.open(path_file) as opened_pdf:    \n",
    "                    raw_text_pagesplit = [str(this_page.extract_text())+'\\n' for this_page in opened_pdf.pages] \n",
    "            elif ocr_option=='googlevision':\n",
    "                with open(path_file, 'r', encoding=\"utf8\") as f:\n",
    "                    raw_text = f.read()\n",
    "                    raw_text_pagesplit = raw_text.split(\"\\n\\n\")\n",
    "\n",
    "            decoded_pdf.append([path_file.split('__')[-1][:-4],\n",
    "                                list2text_frompdf(get_all_descriptions(raw_text_pagesplit)),\n",
    "                                list2text_frompdf(get_all_limitations(raw_text_pagesplit)),\n",
    "                                \"\\n\\n\".join(raw_text_pagesplit)\n",
    "                                ])\n",
    "\n",
    "        except Exception as e:\n",
    "            logging.info('Failed for '+path_file+' with Exception: '+str(e))\n",
    "            decoded_pdf.append([path_file.split('__')[-1][:-4],\n",
    "                                '',\n",
    "                                '',\n",
    "                                ''])\n",
    "\n",
    "    df_pdf_content = pd.DataFrame(decoded_pdf,\n",
    "                                columns=['documentGuid', \n",
    "                                        'descriptions', \n",
    "                                        'limitations',\n",
    "                                        'rawtext'])    \n",
    "    df_pdf_content = df_pdf_content.astype(\"string\")\n",
    "\n",
    "    df_pdf_content.to_parquet(os.path.join(os.getcwd(),'database','decoded_pdf_using_'+text_source+'.parquet'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "ename": "NoSuchWindowException",
     "evalue": "Message: Browsing context has been discarded\nStacktrace:\nRemoteError@chrome://remote/content/shared/RemoteError.sys.mjs:8:8\nWebDriverError@chrome://remote/content/shared/webdriver/Errors.sys.mjs:189:5\nNoSuchWindowError@chrome://remote/content/shared/webdriver/Errors.sys.mjs:630:5\nassert.that/<@chrome://remote/content/shared/webdriver/Assert.sys.mjs:485:13\nassert.open@chrome://remote/content/shared/webdriver/Assert.sys.mjs:147:4\nGeckoDriver.prototype.maximizeWindow@chrome://remote/content/marionette/driver.sys.mjs:2619:15\ndespatch@chrome://remote/content/marionette/server.sys.mjs:318:40\nexecute@chrome://remote/content/marionette/server.sys.mjs:289:16\nonPacket/<@chrome://remote/content/marionette/server.sys.mjs:262:20\nonPacket@chrome://remote/content/marionette/server.sys.mjs:263:9\n_onJSONObjectReady/<@chrome://remote/content/marionette/transport.sys.mjs:494:20\n",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNoSuchWindowException\u001b[0m                     Traceback (most recent call last)",
      "\u001b[1;32mc:\\Users\\victor\\Documents\\DeepLearning\\FAA NLP Project\\90_gendoc_collection.ipynb Cell 5\u001b[0m line \u001b[0;36m4\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/victor/Documents/DeepLearning/FAA%20NLP%20Project/90_gendoc_collection.ipynb#W4sZmlsZQ%3D%3D?line=38'>39</a>\u001b[0m df_drs \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39mread_parquet(glob\u001b[39m.\u001b[39mglob(os\u001b[39m.\u001b[39mpath\u001b[39m.\u001b[39mjoin(os\u001b[39m.\u001b[39mgetcwd(), \u001b[39m'\u001b[39m\u001b[39mdatabase\u001b[39m\u001b[39m'\u001b[39m, \u001b[39m'\u001b[39m\u001b[39mall drs\u001b[39m\u001b[39m'\u001b[39m, doc_type\u001b[39m+\u001b[39m\u001b[39m'\u001b[39m\u001b[39m*.parquet\u001b[39m\u001b[39m'\u001b[39m))[\u001b[39m-\u001b[39m\u001b[39m1\u001b[39m])\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/victor/Documents/DeepLearning/FAA%20NLP%20Project/90_gendoc_collection.ipynb#W4sZmlsZQ%3D%3D?line=40'>41</a>\u001b[0m driver \u001b[39m=\u001b[39m webdriver\u001b[39m.\u001b[39mFirefox(options\u001b[39m=\u001b[39moptions)\n\u001b[1;32m---> <a href='vscode-notebook-cell:/c%3A/Users/victor/Documents/DeepLearning/FAA%20NLP%20Project/90_gendoc_collection.ipynb#W4sZmlsZQ%3D%3D?line=41'>42</a>\u001b[0m driver\u001b[39m.\u001b[39mmaximize_window()\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/victor/Documents/DeepLearning/FAA%20NLP%20Project/90_gendoc_collection.ipynb#W4sZmlsZQ%3D%3D?line=43'>44</a>\u001b[0m \u001b[39mfor\u001b[39;00m index, row \u001b[39min\u001b[39;00m df_drs\u001b[39m.\u001b[39miterrows():\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/victor/Documents/DeepLearning/FAA%20NLP%20Project/90_gendoc_collection.ipynb#W4sZmlsZQ%3D%3D?line=44'>45</a>\u001b[0m     filename \u001b[39m=\u001b[39m row[\u001b[39m'\u001b[39m\u001b[39mdrs:status\u001b[39m\u001b[39m'\u001b[39m]\u001b[39m+\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m__\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m+\u001b[39mrow[\u001b[39m'\u001b[39m\u001b[39mdocumentGuid\u001b[39m\u001b[39m'\u001b[39m]\n",
      "File \u001b[1;32mc:\\Users\\victor\\anaconda3\\envs\\faa-nlp-drs\\Lib\\site-packages\\selenium\\webdriver\\remote\\webdriver.py:496\u001b[0m, in \u001b[0;36mWebDriver.maximize_window\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    494\u001b[0m \u001b[39m\u001b[39m\u001b[39m\"\"\"Maximizes the current window that webdriver is using.\"\"\"\u001b[39;00m\n\u001b[0;32m    495\u001b[0m command \u001b[39m=\u001b[39m Command\u001b[39m.\u001b[39mW3C_MAXIMIZE_WINDOW\n\u001b[1;32m--> 496\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mexecute(command, \u001b[39mNone\u001b[39;00m)\n",
      "File \u001b[1;32mc:\\Users\\victor\\anaconda3\\envs\\faa-nlp-drs\\Lib\\site-packages\\selenium\\webdriver\\remote\\webdriver.py:344\u001b[0m, in \u001b[0;36mWebDriver.execute\u001b[1;34m(self, driver_command, params)\u001b[0m\n\u001b[0;32m    342\u001b[0m response \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mcommand_executor\u001b[39m.\u001b[39mexecute(driver_command, params)\n\u001b[0;32m    343\u001b[0m \u001b[39mif\u001b[39;00m response:\n\u001b[1;32m--> 344\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39merror_handler\u001b[39m.\u001b[39mcheck_response(response)\n\u001b[0;32m    345\u001b[0m     response[\u001b[39m\"\u001b[39m\u001b[39mvalue\u001b[39m\u001b[39m\"\u001b[39m] \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_unwrap_value(response\u001b[39m.\u001b[39mget(\u001b[39m\"\u001b[39m\u001b[39mvalue\u001b[39m\u001b[39m\"\u001b[39m, \u001b[39mNone\u001b[39;00m))\n\u001b[0;32m    346\u001b[0m     \u001b[39mreturn\u001b[39;00m response\n",
      "File \u001b[1;32mc:\\Users\\victor\\anaconda3\\envs\\faa-nlp-drs\\Lib\\site-packages\\selenium\\webdriver\\remote\\errorhandler.py:229\u001b[0m, in \u001b[0;36mErrorHandler.check_response\u001b[1;34m(self, response)\u001b[0m\n\u001b[0;32m    227\u001b[0m         alert_text \u001b[39m=\u001b[39m value[\u001b[39m\"\u001b[39m\u001b[39malert\u001b[39m\u001b[39m\"\u001b[39m]\u001b[39m.\u001b[39mget(\u001b[39m\"\u001b[39m\u001b[39mtext\u001b[39m\u001b[39m\"\u001b[39m)\n\u001b[0;32m    228\u001b[0m     \u001b[39mraise\u001b[39;00m exception_class(message, screen, stacktrace, alert_text)  \u001b[39m# type: ignore[call-arg]  # mypy is not smart enough here\u001b[39;00m\n\u001b[1;32m--> 229\u001b[0m \u001b[39mraise\u001b[39;00m exception_class(message, screen, stacktrace)\n",
      "\u001b[1;31mNoSuchWindowException\u001b[0m: Message: Browsing context has been discarded\nStacktrace:\nRemoteError@chrome://remote/content/shared/RemoteError.sys.mjs:8:8\nWebDriverError@chrome://remote/content/shared/webdriver/Errors.sys.mjs:189:5\nNoSuchWindowError@chrome://remote/content/shared/webdriver/Errors.sys.mjs:630:5\nassert.that/<@chrome://remote/content/shared/webdriver/Assert.sys.mjs:485:13\nassert.open@chrome://remote/content/shared/webdriver/Assert.sys.mjs:147:4\nGeckoDriver.prototype.maximizeWindow@chrome://remote/content/marionette/driver.sys.mjs:2619:15\ndespatch@chrome://remote/content/marionette/server.sys.mjs:318:40\nexecute@chrome://remote/content/marionette/server.sys.mjs:289:16\nonPacket/<@chrome://remote/content/marionette/server.sys.mjs:262:20\nonPacket@chrome://remote/content/marionette/server.sys.mjs:263:9\n_onJSONObjectReady/<@chrome://remote/content/marionette/transport.sys.mjs:494:20\n"
     ]
    }
   ],
   "source": [
    "import glob\n",
    "\n",
    "#df_stc_pdftext_googlevision = pd.read_excel(os.path.join(os.getcwd(),'database','decoded_pdf_using_text-from-googlevision.parquet'))\n",
    "#df_stc_pdftext_pdfplumber = pd.read_excel(os.path.join(os.getcwd(),'database','decoded_pdf_using_text-from-pdfplumber.parquet'))\n",
    "\n",
    "df_stc_metadata = pd.read_parquet(glob.glob(os.path.join(os.getcwd(), 'database', 'all drs', 'stc*.parquet'))[-1])\n",
    "df_stc_metadata['documentGuid']=df_stc_metadata['documentGuid'].astype('str')\n",
    "\n",
    "df_stc_metadata_and_text = df_stc_metadata.join(df_stc_pdftext_googlevision.set_index('documentGuid'), on=\"documentGuid\", how=\"right\", lsuffix=\"_metadata\", rsuffix=\"_googlevision\")\n",
    "df_stc_metadata_and_text = df_stc_metadata.join(df_stc_pdftext_pdfplumber.set_index('documentGuid'), on=\"documentGuid\", how=\"right\", lsuffix=\"\", rsuffix=\"_pdfplumber\")\n",
    "\n",
    "df_stc_metadata_and_text.to_parquet(os.path.join(os.getcwd(),'database','stc_metadata_and_text.parquet'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "8c49a9e6d6b122d0bc46f085b0f9b84866f77188fdff3252b8a9eae176833897"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}