3. SUBRELOBJ CSV Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "a588b38a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import random \n",
    "import matplotlib.pyplot as plt\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "269b640c",
   "metadata": {},
   "source": [
    "### Read the CSV file"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9a83df8",
   "metadata": {},
   "source": [
    "## Load the file SUBRELOBJ.csv from https://zenodo.org/records/10022727"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cc353de0",
   "metadata": {},
   "outputs": [],
   "source": [
    "SUBRELOBJ = pd.read_csv('SUBRELOBJ.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77a5dcb4",
   "metadata": {},
   "source": [
    "Analyzing the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1d1c7490",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows: (5411478, 4)\n",
      "Columns : ['Subject', 'Object', 'Rel', 'Count']\n"
     ]
    }
   ],
   "source": [
    "print(\"Number of rows: {}\".format(SUBRELOBJ.shape))\n",
    "print(\"Columns : {}\".format(SUBRELOBJ.columns.tolist()))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3166c225",
   "metadata": {},
   "source": [
    "Total unique entities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f26751d8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique entities : 70002\n"
     ]
    }
   ],
   "source": [
    "unique_entities = list(set(SUBRELOBJ['Subject'].tolist() + SUBRELOBJ['Object'].tolist()))\n",
    "print(\"Number of unique entities : {}\".format(len(unique_entities)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75591fd3",
   "metadata": {},
   "source": [
    "Let's see a couple of random samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "143916e1",
   "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>Subject</th>\n",
       "      <th>Object</th>\n",
       "      <th>Rel</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4567549</th>\n",
       "      <td>Sr2+</td>\n",
       "      <td>HRTEM</td>\n",
       "      <td>CHM-CMT</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1693259</th>\n",
       "      <td>Electrostatic Interaction</td>\n",
       "      <td>Amino Acid</td>\n",
       "      <td>PRO-CHM</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1234587</th>\n",
       "      <td>Current Density</td>\n",
       "      <td>D2O</td>\n",
       "      <td>PRO-CHM</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607970</th>\n",
       "      <td>Electrochemical Impedance</td>\n",
       "      <td>Discharge Charge Profile</td>\n",
       "      <td>PRO-PRO</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2409907</th>\n",
       "      <td>Hydrogen Electrode</td>\n",
       "      <td>Energy Density</td>\n",
       "      <td>APL-PRO</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           Subject                    Object      Rel  Count\n",
       "4567549                       Sr2+                     HRTEM  CHM-CMT    107\n",
       "1693259  Electrostatic Interaction                Amino Acid  PRO-CHM    111\n",
       "1234587            Current Density                       D2O  PRO-CHM     35\n",
       "1607970  Electrochemical Impedance  Discharge Charge Profile  PRO-PRO     30\n",
       "2409907         Hydrogen Electrode            Energy Density  APL-PRO     54"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SUBRELOBJ.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39eaf11f",
   "metadata": {},
   "source": [
    "### Plotting the distribution of relationships"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c12b2e2f",
   "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>Rel</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>PRO-PRO</td>\n",
       "      <td>513780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>CHM-CHM</td>\n",
       "      <td>502596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>PRO-CHM</td>\n",
       "      <td>338940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>CHM-PRO</td>\n",
       "      <td>338940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>DSC-PRO</td>\n",
       "      <td>223370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>PRO-DSC</td>\n",
       "      <td>223370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>CMT-PRO</td>\n",
       "      <td>211489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>PRO-CMT</td>\n",
       "      <td>211489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CHM-CMT</td>\n",
       "      <td>209937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>CMT-CHM</td>\n",
       "      <td>209937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>CMT-CMT</td>\n",
       "      <td>192658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>CHM-DSC</td>\n",
       "      <td>167883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>DSC-CHM</td>\n",
       "      <td>167883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>DSC-DSC</td>\n",
       "      <td>136428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>PRO-APL</td>\n",
       "      <td>120372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>APL-PRO</td>\n",
       "      <td>120372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>CMT-DSC</td>\n",
       "      <td>116373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>DSC-CMT</td>\n",
       "      <td>116373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>APL-CHM</td>\n",
       "      <td>107848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CHM-APL</td>\n",
       "      <td>107848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>APL-APL</td>\n",
       "      <td>92374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>SMT-CHM</td>\n",
       "      <td>82768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CHM-SMT</td>\n",
       "      <td>82768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>SMT-PRO</td>\n",
       "      <td>75296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>PRO-SMT</td>\n",
       "      <td>75296</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Rel   count\n",
       "32  PRO-PRO  513780\n",
       "8   CHM-CHM  502596\n",
       "29  PRO-CHM  338940\n",
       "11  CHM-PRO  338940\n",
       "25  DSC-PRO  223370\n",
       "31  PRO-DSC  223370\n",
       "18  CMT-PRO  211489\n",
       "30  PRO-CMT  211489\n",
       "9   CHM-CMT  209937\n",
       "15  CMT-CHM  209937\n",
       "16  CMT-CMT  192658\n",
       "10  CHM-DSC  167883\n",
       "22  DSC-CHM  167883\n",
       "24  DSC-DSC  136428\n",
       "28  PRO-APL  120372\n",
       "4   APL-PRO  120372\n",
       "17  CMT-DSC  116373\n",
       "23  DSC-CMT  116373\n",
       "1   APL-CHM  107848\n",
       "7   CHM-APL  107848\n",
       "0   APL-APL   92374\n",
       "36  SMT-CHM   82768\n",
       "12  CHM-SMT   82768\n",
       "39  SMT-PRO   75296\n",
       "33  PRO-SMT   75296"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rel_count = SUBRELOBJ.groupby('Rel').size().reset_index(name = 'count').sort_values('count', ascending = False)\n",
    "rel_count.head(25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "fa8ec6cd",
   "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>Rel</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>SPL-SPL</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>SMT-SPL</td>\n",
       "      <td>4271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>SPL-SMT</td>\n",
       "      <td>4271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>APL-SPL</td>\n",
       "      <td>3005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>SPL-APL</td>\n",
       "      <td>3005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Rel  count\n",
       "48  SPL-SPL   5002\n",
       "41  SMT-SPL   4271\n",
       "47  SPL-SMT   4271\n",
       "6   APL-SPL   3005\n",
       "42  SPL-APL   3005"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rel_count.tail(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccb6935d",
   "metadata": {},
   "source": [
    "### Querying CSV File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "bf445164",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def query_subrelobj(query, rel, k=10):\n",
    "    \n",
    "    def get_relevant_entities(query):\n",
    "        entities = []\n",
    "        for ent in unique_entities:\n",
    "            if query.lower() in ent.lower():\n",
    "                entities.append(ent)\n",
    "        return entities\n",
    "    \n",
    "    def query_df(entities, rel):\n",
    "        filtered_df = SUBRELOBJ[(SUBRELOBJ['Subject'].isin(entities)) & (SUBRELOBJ['Rel'] == rel)]\n",
    "        grouped_df = filtered_df.groupby('Object')['Count'].sum().reset_index()\n",
    "        sorted_df = grouped_df.sort_values(by='Count', ascending=False)\n",
    "        objects_and_counts = sorted_df.set_index('Object')['Count']\n",
    "        num = min(k, len(objects_and_counts))\n",
    "        return objects_and_counts[:num]\n",
    "    \n",
    "    relevant_entities = get_relevant_entities(query)\n",
    "    \n",
    "    if len(relevant_entities)<1:\n",
    "        print(\"No relevant query results\")\n",
    "        return None\n",
    "    \n",
    "    results = query_df(relevant_entities, rel)\n",
    "    return results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "900023cd",
   "metadata": {},
   "source": [
    "#### Finding all applications of a TiO2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "60143acf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Object\n",
       "Catalyst                      14533\n",
       "Electrode                      8492\n",
       "Photocatalyst                  7286\n",
       "Photocatalysts                 7013\n",
       "Electrodes                     6626\n",
       "Coatings                       5558\n",
       "Solar Cell                     4795\n",
       "Electrolyte                    4328\n",
       "Photocatalytic Degradation     4257\n",
       "Photoanode                     3935\n",
       "Name: Count, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = query_subrelobj('TiO2', 'CHM-APL')\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "830aa0bf",
   "metadata": {},
   "source": [
    "#### Finding all Symmetry Phase Labels of TiO2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "8823e675",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Object\n",
       "Anatase        17657\n",
       "Rutile         10694\n",
       "Perovskites     3635\n",
       "Cubic           1086\n",
       "Hexagonal        997\n",
       "Brookite         890\n",
       "Tetragonal       864\n",
       "T                830\n",
       "Spinels          807\n",
       "Garnet           602\n",
       "Name: Count, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = query_subrelobj('TiO2', 'CHM-SPL')\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6eace786",
   "metadata": {},
   "source": [
    "#### Finding all applications of CdTe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "c862c8b6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Object\n",
       "Solar Cell              1099\n",
       "Electrode                334\n",
       "Cells                    305\n",
       "Cell                     294\n",
       "Detector                 262\n",
       "Thin Film Solar Cell     237\n",
       "Electrodes               207\n",
       "Back Contact             197\n",
       "Sensor                   180\n",
       "Contacts                 166\n",
       "Name: Count, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = query_subrelobj('CdTe', 'CHM-APL')\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6527768a",
   "metadata": {},
   "source": [
    "#### Finding all Properties of CdTe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "8f9891b8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Object\n",
       "Semiconductor         758\n",
       "Efficiency            528\n",
       "Structure             472\n",
       "Compositions          419\n",
       "Band Gap              366\n",
       "Optical Properties    344\n",
       "Fluorescence          330\n",
       "Stability             309\n",
       "Morphology            301\n",
       "Bandgap               280\n",
       "Name: Count, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = query_subrelobj('CdTe', 'CHM-PRO')\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fde68342",
   "metadata": {},
   "source": [
    "#### Creating a NetworkX Graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "934173ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "import networkx as nx\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "e793912e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an empty directed graph\n",
    "G = nx.DiGraph()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "0d427d33",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 5411478/5411478 [03:56<00:00, 22849.17it/s]\n"
     ]
    }
   ],
   "source": [
    "# Add edges to the graph\n",
    "for index, row in tqdm(SUBRELOBJ.iterrows(), total = SUBRELOBJ.shape[0]):\n",
    "    G.add_edge(row['Subject'], row['Object'], relationship=row['Rel'], weight=row['Count'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "c5d1ce6a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average Degree: 140.3048484329019\n"
     ]
    }
   ],
   "source": [
    "# Calculate average degree\n",
    "average_degree = sum(dict(G.degree()).values()) / float(len(G))\n",
    "print(f\"Average Degree: {average_degree}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "6154eaac",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Weighted Average Degree: 8997.688151767092\n"
     ]
    }
   ],
   "source": [
    "# Corrected calculation of weighted average degree\n",
    "weighted_degree = sum(attr['weight'] for _, _, attr in G.edges(data=True)) / float(len(G))\n",
    "\n",
    "print(f\"Weighted Average Degree: {weighted_degree}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a0cd7989",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}