2_ms2_annotation/1_fixing_SIRIUS_output.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Now,-let's-deal-with-SIRIUS-output\" data-toc-modified-id=\"Now,-let's-deal-with-SIRIUS-output-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Now, let's deal with SIRIUS output</a></span><ul class=\"toc-item\"><li><span><a href=\"#Import-libraries\" data-toc-modified-id=\"Import-libraries-1.1\"><span class=\"toc-item-num\">1.1&nbsp;&nbsp;</span>Import libraries</a></span></li><li><span><a href=\"#Glob-compound-identifications\" data-toc-modified-id=\"Glob-compound-identifications-1.2\"><span class=\"toc-item-num\">1.2&nbsp;&nbsp;</span>Glob compound identifications</a></span></li><li><span><a href=\"#Glob-compound-classification-output-(CANOPUS)\" data-toc-modified-id=\"Glob-compound-classification-output-(CANOPUS)-1.3\"><span class=\"toc-item-num\">1.3&nbsp;&nbsp;</span>Glob compound classification output (CANOPUS)</a></span></li><li><span><a href=\"#Open-these-files-as-dataframes-and-merge-them\" data-toc-modified-id=\"Open-these-files-as-dataframes-and-merge-them-1.4\"><span class=\"toc-item-num\">1.4&nbsp;&nbsp;</span>Open these files as dataframes and merge them</a></span></li><li><span><a href=\"#Let's-deal-with-the-annotations...\" data-toc-modified-id=\"Let's-deal-with-the-annotations...-1.5\"><span class=\"toc-item-num\">1.5&nbsp;&nbsp;</span>Let's deal with the annotations...</a></span></li><li><span><a href=\"#Save\" data-toc-modified-id=\"Save-1.6\"><span class=\"toc-item-num\">1.6&nbsp;&nbsp;</span>Save</a></span></li></ul></li><li><span><a href=\"#For-MAGI\" data-toc-modified-id=\"For-MAGI-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>For MAGI</a></span><ul class=\"toc-item\"><li><span><a href=\"#PUGREST\" data-toc-modified-id=\"PUGREST-2.1\"><span class=\"toc-item-num\">2.1&nbsp;&nbsp;</span>PUGREST</a></span></li><li><span><a href=\"#Merge-the-exploded-dataframe-with-the-dataframe-of-pubchem-hits\" data-toc-modified-id=\"Merge-the-exploded-dataframe-with-the-dataframe-of-pubchem-hits-2.2\"><span class=\"toc-item-num\">2.2&nbsp;&nbsp;</span>Merge the exploded dataframe with the dataframe of pubchem hits</a></span></li></ul></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Now, let's deal with SIRIUS output\n",
    "\n",
    "... which is TOTALLY different than GNPS!\n",
    "\n",
    "## Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import glob,os,re,sys\n",
    "from natsort import natsorted\n",
    "import requests\n",
    "import time\n",
    "import json\n",
    "import io\n",
    "import pprint\n",
    "\n",
    "s = requests.Session()\n",
    "main_url = \"https://cts.fiehnlab.ucdavis.edu/rest/\"\n",
    "\n",
    "\n",
    "def split(a, n):\n",
    "    \"\"\"\n",
    "    Function to split list a in n groups\n",
    "    \"\"\"\n",
    "    k, m = divmod(len(a), n)\n",
    "    return (a[i * k + min(i, m):(i + 1) * k + min(i + 1, m)] for i in range(n))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Glob compound identifications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "305\n",
      "['sirius/SIRIUS_output/group_1/compound_identifications.tsv',\n",
      " 'sirius/SIRIUS_output/group_2/compound_identifications.tsv']\n"
     ]
    }
   ],
   "source": [
    "str_candidates_files = natsorted(glob.glob('sirius/SIRIUS_output/group_*/compound_identifications.tsv'))\n",
    "print(len(str_candidates_files))\n",
    "pprint.pprint(str_candidates_files[:2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Glob compound classification output (CANOPUS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "313\n",
      "['sirius/SIRIUS_output/group_1/canopus_summary.tsv',\n",
      " 'sirius/SIRIUS_output/group_2/canopus_summary.tsv']\n"
     ]
    }
   ],
   "source": [
    "canopus_files = natsorted(glob.glob('sirius/SIRIUS_output/group_*/canopus_summary.tsv'))\n",
    "print(len(canopus_files))\n",
    "pprint.pprint(canopus_files[:2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Open these files as dataframes and merge them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "code_folding": [
     21
    ],
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(916, 6)\n",
      "(916, 10)\n"
     ]
    },
    {
     "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>Features</th>\n",
       "      <th>molecularFormula</th>\n",
       "      <th>adduct</th>\n",
       "      <th>InChI</th>\n",
       "      <th>smiles</th>\n",
       "      <th>links</th>\n",
       "      <th>all classifications</th>\n",
       "      <th>superclass</th>\n",
       "      <th>class</th>\n",
       "      <th>subclass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FT0158</td>\n",
       "      <td>C6H9N4O5P</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C6H9N4O5P/c1-14-16(13,15-2)6-4(10(11)...</td>\n",
       "      <td>COP(=O)(C1=NC=NC(=C1[N+](=O)[O-])N)OC</td>\n",
       "      <td>PubChem:(3055774);PubChem class - safety and t...</td>\n",
       "      <td>Organic compounds; Organoheterocyclic compound...</td>\n",
       "      <td>Organoheterocyclic compounds</td>\n",
       "      <td>Heteroaromatic compounds</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FT0216</td>\n",
       "      <td>C17H12OS</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C17H12OS/c1-10-4-12-6-15-9-17-11(2-3-...</td>\n",
       "      <td>CC1=CC2=CC3=C(C=C4C=CSC4=C3)C=C2C=C1O</td>\n",
       "      <td>PubChem:(122448813)</td>\n",
       "      <td>Organic compounds; Organosulfur compounds; Phe...</td>\n",
       "      <td>Benzenoids</td>\n",
       "      <td>Phenols</td>\n",
       "      <td>1-hydroxy-2-unsubstituted benzenoids</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FT0221</td>\n",
       "      <td>C15H18BN3O</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C15H18BN3O/c1-9(2)19(10(3)4)13-8-17-7...</td>\n",
       "      <td>[B]C(=O)C1=NC2=C(C=NC=C2C=C1)N(C(C)C)C(C)C</td>\n",
       "      <td>PubChem:(58495757)</td>\n",
       "      <td>Organic compounds; Organic acids and derivativ...</td>\n",
       "      <td>Benzenoids</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FT0199</td>\n",
       "      <td>C10H28N8</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C10H28N8/c11-1-2-13-3-4-14-5-6-15-7-8...</td>\n",
       "      <td>C(CNCCNCCNCCNCCN=NN)N</td>\n",
       "      <td>PubChem:(118629314)</td>\n",
       "      <td>Organic compounds; Organoheterocyclic compound...</td>\n",
       "      <td>Organoheterocyclic compounds</td>\n",
       "      <td>Azacyclic compounds</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FT0227</td>\n",
       "      <td>C10H12N4O5</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C10H12N4O5/c15-2-5-4(16)1-6(19-5)14-3...</td>\n",
       "      <td>C1C(C(OC1N2C=NC3=C2NC(=O)NC3=O)CO)O</td>\n",
       "      <td>PubChem:(65372 14282796 54124565 57169553 5864...</td>\n",
       "      <td>Organic compounds; Halobenzenes; Hydrazines an...</td>\n",
       "      <td>Benzenoids</td>\n",
       "      <td>Benzene and substituted derivatives</td>\n",
       "      <td>Halobenzenes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Features molecularFormula    adduct  \\\n",
       "0   FT0158        C6H9N4O5P  [M - H]-   \n",
       "1   FT0216         C17H12OS  [M - H]-   \n",
       "2   FT0221       C15H18BN3O  [M - H]-   \n",
       "3   FT0199         C10H28N8  [M - H]-   \n",
       "4   FT0227       C10H12N4O5  [M - H]-   \n",
       "\n",
       "                                               InChI  \\\n",
       "0  InChI=1S/C6H9N4O5P/c1-14-16(13,15-2)6-4(10(11)...   \n",
       "1  InChI=1S/C17H12OS/c1-10-4-12-6-15-9-17-11(2-3-...   \n",
       "2  InChI=1S/C15H18BN3O/c1-9(2)19(10(3)4)13-8-17-7...   \n",
       "3  InChI=1S/C10H28N8/c11-1-2-13-3-4-14-5-6-15-7-8...   \n",
       "4  InChI=1S/C10H12N4O5/c15-2-5-4(16)1-6(19-5)14-3...   \n",
       "\n",
       "                                       smiles  \\\n",
       "0       COP(=O)(C1=NC=NC(=C1[N+](=O)[O-])N)OC   \n",
       "1       CC1=CC2=CC3=C(C=C4C=CSC4=C3)C=C2C=C1O   \n",
       "2  [B]C(=O)C1=NC2=C(C=NC=C2C=C1)N(C(C)C)C(C)C   \n",
       "3                       C(CNCCNCCNCCNCCN=NN)N   \n",
       "4         C1C(C(OC1N2C=NC3=C2NC(=O)NC3=O)CO)O   \n",
       "\n",
       "                                               links  \\\n",
       "0  PubChem:(3055774);PubChem class - safety and t...   \n",
       "1                                PubChem:(122448813)   \n",
       "2                                 PubChem:(58495757)   \n",
       "3                                PubChem:(118629314)   \n",
       "4  PubChem:(65372 14282796 54124565 57169553 5864...   \n",
       "\n",
       "                                 all classifications  \\\n",
       "0  Organic compounds; Organoheterocyclic compound...   \n",
       "1  Organic compounds; Organosulfur compounds; Phe...   \n",
       "2  Organic compounds; Organic acids and derivativ...   \n",
       "3  Organic compounds; Organoheterocyclic compound...   \n",
       "4  Organic compounds; Halobenzenes; Hydrazines an...   \n",
       "\n",
       "                     superclass                                class  \\\n",
       "0  Organoheterocyclic compounds             Heteroaromatic compounds   \n",
       "1                    Benzenoids                              Phenols   \n",
       "2                    Benzenoids                                  NaN   \n",
       "3  Organoheterocyclic compounds                  Azacyclic compounds   \n",
       "4                    Benzenoids  Benzene and substituted derivatives   \n",
       "\n",
       "                               subclass  \n",
       "0                                   NaN  \n",
       "1  1-hydroxy-2-unsubstituted benzenoids  \n",
       "2                                   NaN  \n",
       "3                                   NaN  \n",
       "4                          Halobenzenes  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summary_df = pd.DataFrame()\n",
    "\n",
    "for file in str_candidates_files:\n",
    "\n",
    "    intermediate_df = pd.read_csv(file, sep='\\t')\n",
    "\n",
    "    intermediate_df['Features'] = intermediate_df['id'].str.split('_').str[-1]\n",
    "\n",
    "    col_list = [\n",
    "        'Features', 'molecularFormula',\n",
    "        'adduct', 'InChI', 'smiles', 'links',\n",
    "    ]\n",
    "    intermediate_df = intermediate_df.loc[:, col_list]\n",
    "\n",
    "    summary_df = pd.concat([summary_df, intermediate_df], axis=0)\n",
    "\n",
    "\n",
    "print(summary_df.shape)\n",
    "\n",
    "canopus_df = pd.DataFrame()\n",
    "\n",
    "for file in canopus_files:\n",
    "\n",
    "    intermediate_df = pd.read_csv(file, sep='\\t', index_col=False)\n",
    "\n",
    "    if not intermediate_df.empty:\n",
    "\n",
    "        intermediate_df['Features'] = intermediate_df['name'].str.split(\n",
    "            '_').str[-1]\n",
    "        intermediate_df = intermediate_df.loc[:, [\n",
    "            'Features', 'all classifications', 'superclass', 'class',\n",
    "            'subclass'\n",
    "        ]]\n",
    "\n",
    "        canopus_df = pd.concat([canopus_df, intermediate_df],\n",
    "                            axis=0).reset_index(drop=True)\n",
    "\n",
    "    else:\n",
    "        continue\n",
    "\n",
    "summary_df = summary_df.merge(canopus_df, on=\"Features\", how='left')\n",
    "\n",
    "print(summary_df.shape)\n",
    "\n",
    "summary_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's deal with the annotations...\n",
    "\n",
    "All possible annotations are in the `links` column. Extract the annotation identifiers using `re`.\n",
    "\n",
    "I am not extracting entries from all databases. I am only extracting the most important ones like HMDB, YMDB, BioCyc, KEGG, PubChem, CHEBI, and COCONUT because this database name is great lol."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "code_folding": [],
    "scrolled": true
   },
   "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>Features</th>\n",
       "      <th>molecularFormula</th>\n",
       "      <th>adduct</th>\n",
       "      <th>InChI</th>\n",
       "      <th>smiles</th>\n",
       "      <th>links</th>\n",
       "      <th>all classifications</th>\n",
       "      <th>superclass</th>\n",
       "      <th>class</th>\n",
       "      <th>subclass</th>\n",
       "      <th>HMDB</th>\n",
       "      <th>YMDB</th>\n",
       "      <th>KNApSAcK</th>\n",
       "      <th>CHEBI</th>\n",
       "      <th>PlantCyc</th>\n",
       "      <th>BioCyc</th>\n",
       "      <th>KEGG</th>\n",
       "      <th>COCONUT</th>\n",
       "      <th>PubChem_CID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FT0158</td>\n",
       "      <td>C6H9N4O5P</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C6H9N4O5P/c1-14-16(13,15-2)6-4(10(11)...</td>\n",
       "      <td>COP(=O)(C1=NC=NC(=C1[N+](=O)[O-])N)OC</td>\n",
       "      <td>PubChem:(3055774);PubChem class - safety and t...</td>\n",
       "      <td>Organic compounds; Organoheterocyclic compound...</td>\n",
       "      <td>Organoheterocyclic compounds</td>\n",
       "      <td>Heteroaromatic compounds</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>3055774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FT0216</td>\n",
       "      <td>C17H12OS</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C17H12OS/c1-10-4-12-6-15-9-17-11(2-3-...</td>\n",
       "      <td>CC1=CC2=CC3=C(C=C4C=CSC4=C3)C=C2C=C1O</td>\n",
       "      <td>PubChem:(122448813)</td>\n",
       "      <td>Organic compounds; Organosulfur compounds; Phe...</td>\n",
       "      <td>Benzenoids</td>\n",
       "      <td>Phenols</td>\n",
       "      <td>1-hydroxy-2-unsubstituted benzenoids</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>122448813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FT0221</td>\n",
       "      <td>C15H18BN3O</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C15H18BN3O/c1-9(2)19(10(3)4)13-8-17-7...</td>\n",
       "      <td>[B]C(=O)C1=NC2=C(C=NC=C2C=C1)N(C(C)C)C(C)C</td>\n",
       "      <td>PubChem:(58495757)</td>\n",
       "      <td>Organic compounds; Organic acids and derivativ...</td>\n",
       "      <td>Benzenoids</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>58495757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FT0199</td>\n",
       "      <td>C10H28N8</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C10H28N8/c11-1-2-13-3-4-14-5-6-15-7-8...</td>\n",
       "      <td>C(CNCCNCCNCCNCCN=NN)N</td>\n",
       "      <td>PubChem:(118629314)</td>\n",
       "      <td>Organic compounds; Organoheterocyclic compound...</td>\n",
       "      <td>Organoheterocyclic compounds</td>\n",
       "      <td>Azacyclic compounds</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>118629314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FT0227</td>\n",
       "      <td>C10H12N4O5</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C10H12N4O5/c15-2-5-4(16)1-6(19-5)14-3...</td>\n",
       "      <td>C1C(C(OC1N2C=NC3=C2NC(=O)NC3=O)CO)O</td>\n",
       "      <td>PubChem:(65372 14282796 54124565 57169553 5864...</td>\n",
       "      <td>Organic compounds; Halobenzenes; Hydrazines an...</td>\n",
       "      <td>Benzenoids</td>\n",
       "      <td>Benzene and substituted derivatives</td>\n",
       "      <td>Halobenzenes</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>65372 14282796 54124565 57169553 58648961 6962...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Features molecularFormula    adduct  \\\n",
       "0   FT0158        C6H9N4O5P  [M - H]-   \n",
       "1   FT0216         C17H12OS  [M - H]-   \n",
       "2   FT0221       C15H18BN3O  [M - H]-   \n",
       "3   FT0199         C10H28N8  [M - H]-   \n",
       "4   FT0227       C10H12N4O5  [M - H]-   \n",
       "\n",
       "                                               InChI  \\\n",
       "0  InChI=1S/C6H9N4O5P/c1-14-16(13,15-2)6-4(10(11)...   \n",
       "1  InChI=1S/C17H12OS/c1-10-4-12-6-15-9-17-11(2-3-...   \n",
       "2  InChI=1S/C15H18BN3O/c1-9(2)19(10(3)4)13-8-17-7...   \n",
       "3  InChI=1S/C10H28N8/c11-1-2-13-3-4-14-5-6-15-7-8...   \n",
       "4  InChI=1S/C10H12N4O5/c15-2-5-4(16)1-6(19-5)14-3...   \n",
       "\n",
       "                                       smiles  \\\n",
       "0       COP(=O)(C1=NC=NC(=C1[N+](=O)[O-])N)OC   \n",
       "1       CC1=CC2=CC3=C(C=C4C=CSC4=C3)C=C2C=C1O   \n",
       "2  [B]C(=O)C1=NC2=C(C=NC=C2C=C1)N(C(C)C)C(C)C   \n",
       "3                       C(CNCCNCCNCCNCCN=NN)N   \n",
       "4         C1C(C(OC1N2C=NC3=C2NC(=O)NC3=O)CO)O   \n",
       "\n",
       "                                               links  \\\n",
       "0  PubChem:(3055774);PubChem class - safety and t...   \n",
       "1                                PubChem:(122448813)   \n",
       "2                                 PubChem:(58495757)   \n",
       "3                                PubChem:(118629314)   \n",
       "4  PubChem:(65372 14282796 54124565 57169553 5864...   \n",
       "\n",
       "                                 all classifications  \\\n",
       "0  Organic compounds; Organoheterocyclic compound...   \n",
       "1  Organic compounds; Organosulfur compounds; Phe...   \n",
       "2  Organic compounds; Organic acids and derivativ...   \n",
       "3  Organic compounds; Organoheterocyclic compound...   \n",
       "4  Organic compounds; Halobenzenes; Hydrazines an...   \n",
       "\n",
       "                     superclass                                class  \\\n",
       "0  Organoheterocyclic compounds             Heteroaromatic compounds   \n",
       "1                    Benzenoids                              Phenols   \n",
       "2                    Benzenoids                                  NaN   \n",
       "3  Organoheterocyclic compounds                  Azacyclic compounds   \n",
       "4                    Benzenoids  Benzene and substituted derivatives   \n",
       "\n",
       "                               subclass HMDB YMDB KNApSAcK CHEBI PlantCyc  \\\n",
       "0                                   NaN                                     \n",
       "1  1-hydroxy-2-unsubstituted benzenoids                                     \n",
       "2                                   NaN                                     \n",
       "3                                   NaN                                     \n",
       "4                          Halobenzenes                                     \n",
       "\n",
       "  BioCyc KEGG COCONUT                                        PubChem_CID  \n",
       "0                                                                3055774  \n",
       "1                                                              122448813  \n",
       "2                                                               58495757  \n",
       "3                                                              118629314  \n",
       "4                      65372 14282796 54124565 57169553 58648961 6962...  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annotation_df = summary_df.copy()\n",
    "\n",
    "## Extract HMDB, YMDB and KNApSAcK hits\n",
    "## it will match to everything inside two parenthesis ()\n",
    "## preceded by the name of the database like HMDB(*)\n",
    "## but pay attention that in some instances, there is more than one entry/database\n",
    "reg_expr = '\\(([^)]+)\\)'\n",
    "to_extract = [('HMDB', 'HMDB:'+reg_expr),\n",
    "              ('YMDB', 'YMDB:'+reg_expr), \n",
    "              ('KNApSAcK', 'KNApSAcK:'+reg_expr),\n",
    "              ('CHEBI', 'CHEBI:'+reg_expr),\n",
    "              ('PlantCyc', 'Plantcyc:'+reg_expr),\n",
    "              ('BioCyc', 'Biocyc:'+reg_expr), \n",
    "              ('KEGG', 'KEGG:'+reg_expr),\n",
    "              ('COCONUT', 'COCONUT:'+reg_expr),\n",
    "              ('PubChem_CID', 'PubChem:'+reg_expr)]\n",
    "\n",
    "for t in to_extract:\n",
    "    annotation_df[t[0]] = annotation_df['links'].str.extract(t[1]).fillna('')\n",
    "\n",
    "## Fix HMDB, YMDB and KNApSAcK entries!!\n",
    "to_fix = [('HMDB',  'HMDB{0:0>7}'),\n",
    "          ('YMDB',  'YMDB{0:0>5}'), \n",
    "          ('KNApSAcK', 'C{0:0>8}'),\n",
    "          ('CHEBI', 'CHEBI:{}')]\n",
    "\n",
    "for t in to_fix:\n",
    "    # print(t)\n",
    "    for index, row in annotation_df.iterrows():\n",
    "\n",
    "        if row[t[0]] == \"\":\n",
    "            continue\n",
    "        elif \" \" in row[t[0]]:\n",
    "            item_list = [t[1].format(x) for x in row[t[0]].split(' ')]\n",
    "            item_str = \",\".join(item_list)\n",
    "            # print(t[0], index, row[t[0]], item_str)\n",
    "            annotation_df.loc[index, t[0]] = item_str\n",
    "        else:\n",
    "            item_str = t[1].format(row[t[0]])\n",
    "            # print(t[0], index, row[t[0]], item_str)\n",
    "            annotation_df.loc[index, t[0]] = item_str\n",
    "\n",
    "\n",
    "annotation_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "annotation_df.to_csv('summary_output_SIRIUS.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# For MAGI\n",
    "\n",
    "MAGI is the genome-metabolite integration tool I'll use next, and it requires the chemical structure of compounds (the freaking InChIKey). \n",
    "\n",
    "SIRIUS does **not** resolve the full 3D structure because it's *in silico*, so the output is just a partial or 2D structure, which does not work with MAGI. **Luckily**, SIRIUS xreferences to PubChem, and I can retrieve the 3D InChIKey from PubChem using the PUGRest. \n",
    "\n",
    "Some of these Sirius predictions have more than one PubChem hit, which could be different isomers. For MAGI, it's important to include these isomers, so I'll use the function `explode()` to expand rows from features that have more than one PubChem hit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "exploded_df = annotation_df.copy()\n",
    "\n",
    "exploded_df['PubChem_CID'] = exploded_df['PubChem_CID'].replace(\"\", 'NA')\n",
    "\n",
    "exploded_df['PubChem_CID'] = exploded_df['PubChem_CID'].str.split()\n",
    "\n",
    "## explode!!\n",
    "exploded_df = exploded_df.explode('PubChem_CID')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PUGREST\n",
    "\n",
    "PUGREST allows the search of multiple compounds at once, but still doesn't like too many requests. So I am dividing the PubChem ids in lists of 10 ids, and searching 10 compounds at once. I am also setting a 20 s sleep time to give some time in between requests."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": false
   },
   "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>CID</th>\n",
       "      <th>MolecularFormula</th>\n",
       "      <th>ExactMass</th>\n",
       "      <th>InChIKey</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3055774</td>\n",
       "      <td>C6H9N4O5P</td>\n",
       "      <td>248.031056</td>\n",
       "      <td>BSDBYSKGESWCFF-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>122448813</td>\n",
       "      <td>C17H12OS</td>\n",
       "      <td>264.060886</td>\n",
       "      <td>IDBSUEYMOGXINU-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>58495757</td>\n",
       "      <td>C15H18BN3O</td>\n",
       "      <td>267.154292</td>\n",
       "      <td>RJKKLMSSEUZHKT-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>118629314</td>\n",
       "      <td>C10H28N8</td>\n",
       "      <td>260.243693</td>\n",
       "      <td>BEWWACPMZXTGDS-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>65372</td>\n",
       "      <td>C10H12N4O5</td>\n",
       "      <td>268.080769</td>\n",
       "      <td>NQAZHXBSLFDVKM-KVQBGUIXSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>10486436</td>\n",
       "      <td>C60H85N11O12</td>\n",
       "      <td>1151.637917</td>\n",
       "      <td>HYTNEVIAWLBLPM-IPHVNIDGSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>11665321</td>\n",
       "      <td>C56H81N15O11S</td>\n",
       "      <td>1171.596070</td>\n",
       "      <td>XYLCCDVVSFIDAE-CLQPFZBRSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>90872839</td>\n",
       "      <td>C48H56N2O33</td>\n",
       "      <td>1188.276532</td>\n",
       "      <td>SAIFNDSBLPJMTH-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>23428107</td>\n",
       "      <td>C59H98O24</td>\n",
       "      <td>1190.644804</td>\n",
       "      <td>SISUGQHDWWHKGJ-ZYHIYBPQSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>44584612</td>\n",
       "      <td>C59H98O24</td>\n",
       "      <td>1190.644804</td>\n",
       "      <td>SISUGQHDWWHKGJ-CVLVKPHKSA-N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1345 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           CID MolecularFormula    ExactMass                     InChIKey\n",
       "0      3055774        C6H9N4O5P   248.031056  BSDBYSKGESWCFF-UHFFFAOYSA-N\n",
       "1    122448813         C17H12OS   264.060886  IDBSUEYMOGXINU-UHFFFAOYSA-N\n",
       "2     58495757       C15H18BN3O   267.154292  RJKKLMSSEUZHKT-UHFFFAOYSA-N\n",
       "3    118629314         C10H28N8   260.243693  BEWWACPMZXTGDS-UHFFFAOYSA-N\n",
       "4        65372       C10H12N4O5   268.080769  NQAZHXBSLFDVKM-KVQBGUIXSA-N\n",
       "..         ...              ...          ...                          ...\n",
       "129   10486436     C60H85N11O12  1151.637917  HYTNEVIAWLBLPM-IPHVNIDGSA-N\n",
       "130   11665321    C56H81N15O11S  1171.596070  XYLCCDVVSFIDAE-CLQPFZBRSA-N\n",
       "131   90872839      C48H56N2O33  1188.276532  SAIFNDSBLPJMTH-UHFFFAOYSA-N\n",
       "132   23428107        C59H98O24  1190.644804  SISUGQHDWWHKGJ-ZYHIYBPQSA-N\n",
       "133   44584612        C59H98O24  1190.644804  SISUGQHDWWHKGJ-CVLVKPHKSA-N\n",
       "\n",
       "[1345 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## list of pubchem ids \n",
    "list_cid = [x for x in exploded_df['PubChem_CID'].values if x != 'NA' ]\n",
    "\n",
    "## split the list every 10 items\n",
    "query_list = list(split(list_cid, 10))\n",
    "\n",
    "## merge items in sublists for search in pugrest\n",
    "query_string = [\",\".join(x) for x in query_list]\n",
    "\n",
    "properties = 'MolecularFormula,ExactMass,InChIKey'\n",
    "\n",
    "all_pubchem_hits = pd.DataFrame()\n",
    "\n",
    "for query in query_string:\n",
    "\n",
    "    r = s.get('https://pubchem.ncbi.nlm.nih.gov/rest/pug/compound/cid/' +\n",
    "              query + '/property/' + properties + '/CSV').content\n",
    "\n",
    "    result_df = pd.read_csv(io.StringIO(r.decode('utf-8')))\n",
    "    \n",
    "    all_pubchem_hits = pd.concat([all_pubchem_hits, result_df], axis=0)\n",
    "    \n",
    "    time.sleep(20)\n",
    "    \n",
    "    \n",
    "all_pubchem_hits  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge the exploded dataframe with the dataframe of pubchem hits\n",
    "\n",
    "and save!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1449, 22)\n"
     ]
    },
    {
     "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>Features</th>\n",
       "      <th>molecularFormula</th>\n",
       "      <th>adduct</th>\n",
       "      <th>InChI</th>\n",
       "      <th>smiles</th>\n",
       "      <th>links</th>\n",
       "      <th>all classifications</th>\n",
       "      <th>superclass</th>\n",
       "      <th>class</th>\n",
       "      <th>subclass</th>\n",
       "      <th>...</th>\n",
       "      <th>KNApSAcK</th>\n",
       "      <th>CHEBI</th>\n",
       "      <th>PlantCyc</th>\n",
       "      <th>BioCyc</th>\n",
       "      <th>KEGG</th>\n",
       "      <th>COCONUT</th>\n",
       "      <th>PubChem_CID</th>\n",
       "      <th>MolecularFormula</th>\n",
       "      <th>ExactMass</th>\n",
       "      <th>InChIKey</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FT0158</td>\n",
       "      <td>C6H9N4O5P</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C6H9N4O5P/c1-14-16(13,15-2)6-4(10(11)...</td>\n",
       "      <td>COP(=O)(C1=NC=NC(=C1[N+](=O)[O-])N)OC</td>\n",
       "      <td>PubChem:(3055774);PubChem class - safety and t...</td>\n",
       "      <td>Organic compounds; Organoheterocyclic compound...</td>\n",
       "      <td>Organoheterocyclic compounds</td>\n",
       "      <td>Heteroaromatic compounds</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>3055774</td>\n",
       "      <td>C6H9N4O5P</td>\n",
       "      <td>248.031056</td>\n",
       "      <td>BSDBYSKGESWCFF-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FT0216</td>\n",
       "      <td>C17H12OS</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C17H12OS/c1-10-4-12-6-15-9-17-11(2-3-...</td>\n",
       "      <td>CC1=CC2=CC3=C(C=C4C=CSC4=C3)C=C2C=C1O</td>\n",
       "      <td>PubChem:(122448813)</td>\n",
       "      <td>Organic compounds; Organosulfur compounds; Phe...</td>\n",
       "      <td>Benzenoids</td>\n",
       "      <td>Phenols</td>\n",
       "      <td>1-hydroxy-2-unsubstituted benzenoids</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>122448813</td>\n",
       "      <td>C17H12OS</td>\n",
       "      <td>264.060886</td>\n",
       "      <td>IDBSUEYMOGXINU-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FT0221</td>\n",
       "      <td>C15H18BN3O</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C15H18BN3O/c1-9(2)19(10(3)4)13-8-17-7...</td>\n",
       "      <td>[B]C(=O)C1=NC2=C(C=NC=C2C=C1)N(C(C)C)C(C)C</td>\n",
       "      <td>PubChem:(58495757)</td>\n",
       "      <td>Organic compounds; Organic acids and derivativ...</td>\n",
       "      <td>Benzenoids</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>58495757</td>\n",
       "      <td>C15H18BN3O</td>\n",
       "      <td>267.154292</td>\n",
       "      <td>RJKKLMSSEUZHKT-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FT0199</td>\n",
       "      <td>C10H28N8</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C10H28N8/c11-1-2-13-3-4-14-5-6-15-7-8...</td>\n",
       "      <td>C(CNCCNCCNCCNCCN=NN)N</td>\n",
       "      <td>PubChem:(118629314)</td>\n",
       "      <td>Organic compounds; Organoheterocyclic compound...</td>\n",
       "      <td>Organoheterocyclic compounds</td>\n",
       "      <td>Azacyclic compounds</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>118629314</td>\n",
       "      <td>C10H28N8</td>\n",
       "      <td>260.243693</td>\n",
       "      <td>BEWWACPMZXTGDS-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FT0227</td>\n",
       "      <td>C10H12N4O5</td>\n",
       "      <td>[M - H]-</td>\n",
       "      <td>InChI=1S/C10H12N4O5/c15-2-5-4(16)1-6(19-5)14-3...</td>\n",
       "      <td>C1C(C(OC1N2C=NC3=C2NC(=O)NC3=O)CO)O</td>\n",
       "      <td>PubChem:(65372 14282796 54124565 57169553 5864...</td>\n",
       "      <td>Organic compounds; Halobenzenes; Hydrazines an...</td>\n",
       "      <td>Benzenoids</td>\n",
       "      <td>Benzene and substituted derivatives</td>\n",
       "      <td>Halobenzenes</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>65372</td>\n",
       "      <td>C10H12N4O5</td>\n",
       "      <td>268.080769</td>\n",
       "      <td>NQAZHXBSLFDVKM-KVQBGUIXSA-N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Features molecularFormula    adduct  \\\n",
       "0   FT0158        C6H9N4O5P  [M - H]-   \n",
       "1   FT0216         C17H12OS  [M - H]-   \n",
       "2   FT0221       C15H18BN3O  [M - H]-   \n",
       "3   FT0199         C10H28N8  [M - H]-   \n",
       "4   FT0227       C10H12N4O5  [M - H]-   \n",
       "\n",
       "                                               InChI  \\\n",
       "0  InChI=1S/C6H9N4O5P/c1-14-16(13,15-2)6-4(10(11)...   \n",
       "1  InChI=1S/C17H12OS/c1-10-4-12-6-15-9-17-11(2-3-...   \n",
       "2  InChI=1S/C15H18BN3O/c1-9(2)19(10(3)4)13-8-17-7...   \n",
       "3  InChI=1S/C10H28N8/c11-1-2-13-3-4-14-5-6-15-7-8...   \n",
       "4  InChI=1S/C10H12N4O5/c15-2-5-4(16)1-6(19-5)14-3...   \n",
       "\n",
       "                                       smiles  \\\n",
       "0       COP(=O)(C1=NC=NC(=C1[N+](=O)[O-])N)OC   \n",
       "1       CC1=CC2=CC3=C(C=C4C=CSC4=C3)C=C2C=C1O   \n",
       "2  [B]C(=O)C1=NC2=C(C=NC=C2C=C1)N(C(C)C)C(C)C   \n",
       "3                       C(CNCCNCCNCCNCCN=NN)N   \n",
       "4         C1C(C(OC1N2C=NC3=C2NC(=O)NC3=O)CO)O   \n",
       "\n",
       "                                               links  \\\n",
       "0  PubChem:(3055774);PubChem class - safety and t...   \n",
       "1                                PubChem:(122448813)   \n",
       "2                                 PubChem:(58495757)   \n",
       "3                                PubChem:(118629314)   \n",
       "4  PubChem:(65372 14282796 54124565 57169553 5864...   \n",
       "\n",
       "                                 all classifications  \\\n",
       "0  Organic compounds; Organoheterocyclic compound...   \n",
       "1  Organic compounds; Organosulfur compounds; Phe...   \n",
       "2  Organic compounds; Organic acids and derivativ...   \n",
       "3  Organic compounds; Organoheterocyclic compound...   \n",
       "4  Organic compounds; Halobenzenes; Hydrazines an...   \n",
       "\n",
       "                     superclass                                class  \\\n",
       "0  Organoheterocyclic compounds             Heteroaromatic compounds   \n",
       "1                    Benzenoids                              Phenols   \n",
       "2                    Benzenoids                                  NaN   \n",
       "3  Organoheterocyclic compounds                  Azacyclic compounds   \n",
       "4                    Benzenoids  Benzene and substituted derivatives   \n",
       "\n",
       "                               subclass  ... KNApSAcK CHEBI PlantCyc BioCyc  \\\n",
       "0                                   NaN  ...                                  \n",
       "1  1-hydroxy-2-unsubstituted benzenoids  ...                                  \n",
       "2                                   NaN  ...                                  \n",
       "3                                   NaN  ...                                  \n",
       "4                          Halobenzenes  ...                                  \n",
       "\n",
       "  KEGG COCONUT PubChem_CID MolecularFormula   ExactMass  \\\n",
       "0                  3055774        C6H9N4O5P  248.031056   \n",
       "1                122448813         C17H12OS  264.060886   \n",
       "2                 58495757       C15H18BN3O  267.154292   \n",
       "3                118629314         C10H28N8  260.243693   \n",
       "4                    65372       C10H12N4O5  268.080769   \n",
       "\n",
       "                      InChIKey  \n",
       "0  BSDBYSKGESWCFF-UHFFFAOYSA-N  \n",
       "1  IDBSUEYMOGXINU-UHFFFAOYSA-N  \n",
       "2  RJKKLMSSEUZHKT-UHFFFAOYSA-N  \n",
       "3  BEWWACPMZXTGDS-UHFFFAOYSA-N  \n",
       "4  NQAZHXBSLFDVKM-KVQBGUIXSA-N  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pubchem_hits = all_pubchem_hits.copy()\n",
    "\n",
    "pubchem_hits['CID'] = pubchem_hits['CID'].astype(str)\n",
    "\n",
    "merged = exploded_df.merge(pubchem_hits, left_on = \"PubChem_CID\", right_on = \"CID\", how = \"left\")\n",
    "\n",
    "merged['PubChem_CID'] = merged['PubChem_CID'].replace('NA', '')\n",
    "\n",
    "merged = merged.drop('CID', axis=1)\n",
    "\n",
    "print(merged.shape)\n",
    "\n",
    "merged.to_csv('summary_output_SIRIUS_exploded.csv', index=False)\n",
    "\n",
    "merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.9.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}