2. Create SUBRELOBJ from ENTDOI.csv.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "80de9054",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import random \n",
    "import matplotlib.pyplot as plt\n",
    "from fuzzywuzzy import fuzz\n",
    "from collections import defaultdict\n",
    "from rdflib import Graph, Literal, Namespace, RDF, URIRef, BNode\n",
    "import openai\n",
    "import ast\n",
    "import sqlite3\n",
    "from tqdm import tqdm\n",
    "from urllib.parse import quote"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a257b2e",
   "metadata": {},
   "source": [
    "# Load the file ENTPTNERDOI.csv.tar.gz from https://zenodo.org/records/10022727"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0784736b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify the path to the .tar.gz file\n",
    "tar_gz_path = 'ENTPTNERDOI.csv.tar.gz'\n",
    "\n",
    "# Specify the directory where you want to extract the file\n",
    "extract_dir = 'extracted_files'\n",
    "\n",
    "# Extract the tar.gz file\n",
    "with tarfile.open(tar_gz_path, 'r:gz') as file:\n",
    "    file.extractall(path=extract_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "3bfd0a43",
   "metadata": {},
   "outputs": [],
   "source": [
    "ENTDOI = pd.read_csv('ENTPTNERDOI.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8bf0386a",
   "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>Entity</th>\n",
       "      <th>Part_of_text</th>\n",
       "      <th>NER_Tag</th>\n",
       "      <th>DOI</th>\n",
       "      <th>Preferred_Entity</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>electrospray ionization mass spectrometry</td>\n",
       "      <td>Abs</td>\n",
       "      <td>APL</td>\n",
       "      <td>10.1016/s0925-4005(02)00161-2</td>\n",
       "      <td>Electrospray Ionization Mass Spectrometry</td>\n",
       "      <td>2002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>wsoc</td>\n",
       "      <td>Abs</td>\n",
       "      <td>APL</td>\n",
       "      <td>10.1016/j.atmosenv.2007.06.034</td>\n",
       "      <td>WSOC</td>\n",
       "      <td>2007.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>pyridinium</td>\n",
       "      <td>Abs</td>\n",
       "      <td>APL</td>\n",
       "      <td>10.1021/jo0712401</td>\n",
       "      <td>Pyridinium</td>\n",
       "      <td>2007.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>navy blue</td>\n",
       "      <td>Abs</td>\n",
       "      <td>APL</td>\n",
       "      <td>10.1016/j.ecoleng.2013.07.005</td>\n",
       "      <td>Navy Blue</td>\n",
       "      <td>2013.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>gantt chart</td>\n",
       "      <td>Abs</td>\n",
       "      <td>APL</td>\n",
       "      <td>10.1021/ie970286p</td>\n",
       "      <td>Gantt Chart</td>\n",
       "      <td>1997.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Entity Part_of_text NER_Tag  \\\n",
       "0  electrospray ionization mass spectrometry          Abs     APL   \n",
       "1                                       wsoc          Abs     APL   \n",
       "2                                 pyridinium          Abs     APL   \n",
       "3                                  navy blue          Abs     APL   \n",
       "4                                gantt chart          Abs     APL   \n",
       "\n",
       "                              DOI                           Preferred_Entity  \\\n",
       "0   10.1016/s0925-4005(02)00161-2  Electrospray Ionization Mass Spectrometry   \n",
       "1  10.1016/j.atmosenv.2007.06.034                                       WSOC   \n",
       "2               10.1021/jo0712401                                 Pyridinium   \n",
       "3   10.1016/j.ecoleng.2013.07.005                                  Navy Blue   \n",
       "4               10.1021/ie970286p                                Gantt Chart   \n",
       "\n",
       "     Year  \n",
       "0  2002.0  \n",
       "1  2007.0  \n",
       "2  2007.0  \n",
       "3  2013.0  \n",
       "4  1997.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ENTDOI.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7be558f2",
   "metadata": {},
   "source": [
    "### Perform SQLite Query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f04d5c2",
   "metadata": {},
   "source": [
    "Given the size of the ENTDOI.csv file, a pandas based aggregation and grouping can consume a lot of RAM \\\n",
    "Therefore, the query is performed using sqllite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "311e6c62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "84659831"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Assuming ENT_DF is your existing DataFrame\n",
    "# Create a new SQLite database in memory\n",
    "conn = sqlite3.connect(\":memory:\")\n",
    "\n",
    "# Write ENT_DF to the SQLite database\n",
    "ENTDOI.to_sql('ENT_DF', conn, index=False, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "281dc6d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# SQL query to generate the new SRO_DF\n",
    "sql_query = \"\"\"\n",
    "SELECT \n",
    "    E1.Preferred_Entity AS Subject, \n",
    "    E2.Preferred_Entity AS Object, \n",
    "    E1.NER_Tag || '-' || E2.NER_Tag AS Rel,\n",
    "    COUNT(DISTINCT E1.DOI) AS Count\n",
    "FROM \n",
    "    ENT_DF AS E1\n",
    "JOIN \n",
    "    ENT_DF AS E2 ON E1.DOI = E2.DOI AND E1.Part_of_text = E2.Part_of_text\n",
    "WHERE \n",
    "    E1.Preferred_Entity != E2.Preferred_Entity\n",
    "GROUP BY \n",
    "    Subject, Object, Rel\n",
    "HAVING \n",
    "    Count >= 30\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3db23d9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Execute the SQL query and read the results into a new DataFrame\n",
    "SRO_DF = pd.read_sql_query(sql_query, conn)\n",
    "SRO_DF.reset_index(inplace = True, drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bc96311",
   "metadata": {},
   "source": [
    "### Saving the results as SUBRELOBJ.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cc510c6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "SRO_DF.to_csv('SUBRELOBJ.csv', index = None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "836c5c14",
   "metadata": {},
   "source": [
    "To ensure that the URIs for the preferred_entities are the same in ENTNERPRDOI and SUBRELOBJ,\\\n",
    "we save all the URIs to an entity_uri_dict and load the values into the SUBRELOBJ.nt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "840120ce",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 84659831/84659831 [02:57<00:00, 477442.16it/s]\n"
     ]
    }
   ],
   "source": [
    "# Dictionary to keep track of entity URI mappings\n",
    "entity_uri_mapping = {}\n",
    "ner_dict           = {}\n",
    "NER, pref_entity = ENTDOI[['NER_Tag', 'Preferred_Entity']].values.T\n",
    "for ner, pref_ent in tqdm(zip(NER, pref_entity), total = len(NER)):\n",
    "    if pref_ent:\n",
    "\n",
    "        # Add Preferred_Entity, creating a URI based on NER tag\n",
    "        preferred_entity_uri = URIRef(\"http://example.com/{}/{}\".format(ner, quote(pref_ent)))\n",
    "\n",
    "        entity_uri_mapping[pref_ent] = preferred_entity_uri\n",
    "        ner_dict[pref_ent]           = ner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d09a37d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "pickle.dump(entity_uri_mapping, open('entity_uri_mapping.pickle', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "9f0bea02",
   "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>0</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PRO</td>\n",
       "      <td>37265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CHM</td>\n",
       "      <td>31182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>APL</td>\n",
       "      <td>27134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SMT</td>\n",
       "      <td>21027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CMT</td>\n",
       "      <td>16797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SPL</td>\n",
       "      <td>8656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DSC</td>\n",
       "      <td>6909</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0  count\n",
       "4  PRO  37265\n",
       "1  CHM  31182\n",
       "0  APL  27134\n",
       "5  SMT  21027\n",
       "2  CMT  16797\n",
       "6  SPL   8656\n",
       "3  DSC   6909"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "NERDF = pd.DataFrame.from_dict(ner_dict, orient = 'index')\n",
    "NERDF.groupby(0).size().reset_index(name='count').sort_values('count', ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "437a1115",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique entities : 70002\n"
     ]
    }
   ],
   "source": [
    "unique_entities = list(set(SRO_DF['Subject'].tolist() + SRO_DF['Object'].tolist()))\n",
    "print(\"Number of unique entities : {}\".format(len(unique_entities)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "218e082f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "70002"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reduced_ner_dict ={key: ner_dict[key] for key in unique_entities}\n",
    "len(reduced_ner_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "95bb0e68",
   "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>0</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PRO</td>\n",
       "      <td>37265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CHM</td>\n",
       "      <td>31182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>APL</td>\n",
       "      <td>27134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SMT</td>\n",
       "      <td>21027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CMT</td>\n",
       "      <td>16797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SPL</td>\n",
       "      <td>8656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DSC</td>\n",
       "      <td>6909</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0  count\n",
       "4  PRO  37265\n",
       "1  CHM  31182\n",
       "0  APL  27134\n",
       "5  SMT  21027\n",
       "2  CMT  16797\n",
       "6  SPL   8656\n",
       "3  DSC   6909"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RNERDF = pd.DataFrame.from_dict(ner_dict, orient = 'index')\n",
    "RNERDF.groupby(0).size().reset_index(name='count').sort_values('count', ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "de02940b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 5411478/5411478 [09:58<00:00, 9042.54it/s]  \n",
      "/home/synthesisproject/anaconda3/envs/vineeth_10/lib/python3.10/site-packages/rdflib/plugins/serializers/nt.py:40: UserWarning: NTSerializer always uses UTF-8 encoding. Given encoding was: None\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<Graph identifier=Nc8fcafd346764f54a963323d12bd0f4b (<class 'rdflib.graph.Graph'>)>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g = Graph()\n",
    "# Assuming you have a DataFrame df with 'subject', 'object', 'relationship', 'count' columns\n",
    "for row_num, row in tqdm(SRO_DF.iterrows(), total=len(SRO_DF)):\n",
    "    subject, object_, relationship, count = row\n",
    "    index = row_num  # Assuming row_num serves as the index\n",
    "\n",
    "    # Get URIs from the dictionaries\n",
    "    subject_uri = entity_uri_mapping.get(subject)\n",
    "    object_uri = entity_uri_mapping.get(object_)\n",
    "    relationship_uri = URIRef(f\"http://example.org/{relationship}\")\n",
    "\n",
    "    # Create a blank node or URI for the index\n",
    "    index_node = BNode(f\"index{index}\")\n",
    "\n",
    "    # Add the triples to the graph\n",
    "    g.add((index_node, URIRef(\"http://example.org/hasSubject\"), URIRef(subject_uri)))\n",
    "    g.add((index_node, URIRef(\"http://example.org/hasObject\"), URIRef(object_uri)))\n",
    "    g.add((index_node, URIRef(\"http://example.org/hasRelationship\"), URIRef(relationship_uri)))\n",
    "    g.add((index_node, URIRef(\"http://example.org/hasCount\"), Literal(count)))\n",
    "    \n",
    "g.serialize(destination='SUBRELOBJ.nt', format='nt')  "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "vineeth_10",
   "language": "python",
   "name": "vineeth_10"
  },
  "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}