2_ms2_annotation/0_fixing_GNPS_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=\"#Fixing-GNPS-annotation-output\" data-toc-modified-id=\"Fixing-GNPS-annotation-output-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Fixing GNPS annotation 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-GNPS-output-file\" data-toc-modified-id=\"Glob-GNPS-output-file-1.2\"><span class=\"toc-item-num\">1.2&nbsp;&nbsp;</span>Glob GNPS output file</a></span></li><li><span><a href=\"#Select-columns-of-interest\" data-toc-modified-id=\"Select-columns-of-interest-1.3\"><span class=\"toc-item-num\">1.3&nbsp;&nbsp;</span>Select columns of interest</a></span></li><li><span><a href=\"#Translate-InChIKey-to-xref-in-KEGG,-BioCyc,-HMDB,-etc.\" data-toc-modified-id=\"Translate-InChIKey-to-xref-in-KEGG,-BioCyc,-HMDB,-etc.-1.4\"><span class=\"toc-item-num\">1.4&nbsp;&nbsp;</span>Translate InChIKey to xref in KEGG, BioCyc, HMDB, etc.</a></span></li><li><span><a href=\"#Use-InChIKey-to-extract-Smiles,-Molecular-Formulas-from-PubChem\" data-toc-modified-id=\"Use-InChIKey-to-extract-Smiles,-Molecular-Formulas-from-PubChem-1.5\"><span class=\"toc-item-num\">1.5&nbsp;&nbsp;</span>Use InChIKey to extract Smiles, Molecular Formulas from PubChem</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></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Fixing GNPS annotation output\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\n",
    "import requests\n",
    "import time\n",
    "import json\n",
    "import io"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Glob GNPS output file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'gnps/ProteoSAFe-MOLECULAR-LIBRARYSEARCH-V2-d46f30ee-download_all_identifications/MOLECULAR-LIBRARYSEARCH-V2-d46f30ee-download_all_identifications-main.tsv'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gnps_file = glob.glob('gnps/ProteoSAFe-MOLECULAR-LIBRARYSEARCH-*/*.tsv')[0]\n",
    "gnps_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(35, 45)\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>index</th>\n",
       "      <th>SpectrumID</th>\n",
       "      <th>Compound_Name</th>\n",
       "      <th>Ion_Source</th>\n",
       "      <th>Instrument</th>\n",
       "      <th>Compound_Source</th>\n",
       "      <th>PI</th>\n",
       "      <th>Data_Collector</th>\n",
       "      <th>Adduct</th>\n",
       "      <th>Precursor_MZ</th>\n",
       "      <th>...</th>\n",
       "      <th>NumberHits</th>\n",
       "      <th>full_CCMS_path</th>\n",
       "      <th>tags</th>\n",
       "      <th>MoleculeExplorerDatasets</th>\n",
       "      <th>MoleculeExplorerFiles</th>\n",
       "      <th>InChIKey</th>\n",
       "      <th>InChIKey-Planar</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>10</td>\n",
       "      <td>CCMSLIB00003140214</td>\n",
       "      <td>Spectral Match to (-)-Catechin gallate from NI...</td>\n",
       "      <td>ESI</td>\n",
       "      <td>IT/ion trap</td>\n",
       "      <td>Isolated</td>\n",
       "      <td>Data from Norberto Peporine Lopes</td>\n",
       "      <td>Data deposited by faustocn</td>\n",
       "      <td>M-H</td>\n",
       "      <td>441.084</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>nathaliagg/URen/consensus/ms2spectra_consensus...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18</td>\n",
       "      <td>613</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>CCMSLIB00003107838</td>\n",
       "      <td>PE(14:0/0:0); [M-H]- C19H39N1O7P1</td>\n",
       "      <td>LC-ESI</td>\n",
       "      <td>CID; Velos</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Thomas Metz</td>\n",
       "      <td>Thomas Metz</td>\n",
       "      <td>M-H</td>\n",
       "      <td>425.254</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>nathaliagg/URen/consensus/ms2spectra_consensus...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17</td>\n",
       "      <td>CCMSLIB00003099089</td>\n",
       "      <td>PE(18:0/0:0); [M-H]- C23H47N1O7P1</td>\n",
       "      <td>LC-ESI</td>\n",
       "      <td>CID; Velos</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>Thomas Metz</td>\n",
       "      <td>Thomas Metz</td>\n",
       "      <td>M-H</td>\n",
       "      <td>481.317</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>nathaliagg/URen/consensus/ms2spectra_consensus...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57</td>\n",
       "      <td>3123</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>CCMSLIB00005740087</td>\n",
       "      <td>Massbank:PR305485 isorhamnetin-3-O-rutinoside</td>\n",
       "      <td>ESI</td>\n",
       "      <td>qTof</td>\n",
       "      <td>Isolated</td>\n",
       "      <td>Massbank</td>\n",
       "      <td>Massbank</td>\n",
       "      <td>M-H</td>\n",
       "      <td>623.162</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>nathaliagg/URen/consensus/ms2spectra_consensus...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>UIDGLYUNOUKLBM-UHFFFAOYSA-N</td>\n",
       "      <td>UIDGLYUNOUKLBM</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Flavonoids</td>\n",
       "      <td>Flavonoid glycosides</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23</td>\n",
       "      <td>CCMSLIB00005771063</td>\n",
       "      <td>Massbank:MT000020 Docosapentaenoic acid (22:5(...</td>\n",
       "      <td>ESI</td>\n",
       "      <td>Ion Trap</td>\n",
       "      <td>Isolated</td>\n",
       "      <td>Massbank</td>\n",
       "      <td>Massbank</td>\n",
       "      <td>M-H</td>\n",
       "      <td>329.400</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>nathaliagg/URen/consensus/ms2spectra_consensus...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>YUFFSWGQGVEMMI-UHFFFAOYSA-N</td>\n",
       "      <td>YUFFSWGQGVEMMI</td>\n",
       "      <td>Lipids and lipid-like molecules</td>\n",
       "      <td>Fatty Acyls</td>\n",
       "      <td>Fatty acids and conjugates</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 45 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index          SpectrumID  \\\n",
       "0     10  CCMSLIB00003140214   \n",
       "1     11  CCMSLIB00003107838   \n",
       "2     17  CCMSLIB00003099089   \n",
       "3     21  CCMSLIB00005740087   \n",
       "4     23  CCMSLIB00005771063   \n",
       "\n",
       "                                       Compound_Name Ion_Source   Instrument  \\\n",
       "0  Spectral Match to (-)-Catechin gallate from NI...        ESI  IT/ion trap   \n",
       "1                  PE(14:0/0:0); [M-H]- C19H39N1O7P1     LC-ESI   CID; Velos   \n",
       "2                  PE(18:0/0:0); [M-H]- C23H47N1O7P1     LC-ESI   CID; Velos   \n",
       "3      Massbank:PR305485 isorhamnetin-3-O-rutinoside        ESI         qTof   \n",
       "4  Massbank:MT000020 Docosapentaenoic acid (22:5(...        ESI     Ion Trap   \n",
       "\n",
       "  Compound_Source                                 PI  \\\n",
       "0        Isolated  Data from Norberto Peporine Lopes   \n",
       "1      Commercial                        Thomas Metz   \n",
       "2      Commercial                        Thomas Metz   \n",
       "3        Isolated                           Massbank   \n",
       "4        Isolated                           Massbank   \n",
       "\n",
       "               Data_Collector Adduct  Precursor_MZ  ...  NumberHits  \\\n",
       "0  Data deposited by faustocn    M-H       441.084  ...           1   \n",
       "1                 Thomas Metz    M-H       425.254  ...           1   \n",
       "2                 Thomas Metz    M-H       481.317  ...           1   \n",
       "3                    Massbank    M-H       623.162  ...           1   \n",
       "4                    Massbank    M-H       329.400  ...           1   \n",
       "\n",
       "                                      full_CCMS_path tags  \\\n",
       "0  nathaliagg/URen/consensus/ms2spectra_consensus...  NaN   \n",
       "1  nathaliagg/URen/consensus/ms2spectra_consensus...  NaN   \n",
       "2  nathaliagg/URen/consensus/ms2spectra_consensus...  NaN   \n",
       "3  nathaliagg/URen/consensus/ms2spectra_consensus...  NaN   \n",
       "4  nathaliagg/URen/consensus/ms2spectra_consensus...  NaN   \n",
       "\n",
       "  MoleculeExplorerDatasets MoleculeExplorerFiles                     InChIKey  \\\n",
       "0                       18                   613                          NaN   \n",
       "1                        0                     0                          NaN   \n",
       "2                       57                  3123                          NaN   \n",
       "3                        0                     0  UIDGLYUNOUKLBM-UHFFFAOYSA-N   \n",
       "4                        0                     0  YUFFSWGQGVEMMI-UHFFFAOYSA-N   \n",
       "\n",
       "  InChIKey-Planar                        superclass        class  \\\n",
       "0             NaN                               NaN          NaN   \n",
       "1             NaN                               NaN          NaN   \n",
       "2             NaN                               NaN          NaN   \n",
       "3  UIDGLYUNOUKLBM  Phenylpropanoids and polyketides   Flavonoids   \n",
       "4  YUFFSWGQGVEMMI   Lipids and lipid-like molecules  Fatty Acyls   \n",
       "\n",
       "                     subclass  \n",
       "0                         NaN  \n",
       "1                         NaN  \n",
       "2                         NaN  \n",
       "3        Flavonoid glycosides  \n",
       "4  Fatty acids and conjugates  \n",
       "\n",
       "[5 rows x 45 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import\n",
    "df = pd.read_csv(gnps_file, sep = '\\t')\n",
    "\n",
    "# remove positive hits\n",
    "df = df[df['IonMode']=='Negative'].reset_index()\n",
    "\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Select columns of interest\n",
    "\n",
    "These columns match the output by Sirius as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(19, 17)\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>Adduct</th>\n",
       "      <th>IonMode</th>\n",
       "      <th>InChIKey</th>\n",
       "      <th>superclass</th>\n",
       "      <th>class</th>\n",
       "      <th>subclass</th>\n",
       "      <th>MolecularFormula</th>\n",
       "      <th>ExactMass</th>\n",
       "      <th>InChI</th>\n",
       "      <th>CanonicalSmiles</th>\n",
       "      <th>PubChem_CID</th>\n",
       "      <th>KEGG</th>\n",
       "      <th>BioCyc</th>\n",
       "      <th>LIPIDMAPS</th>\n",
       "      <th>ChEBI</th>\n",
       "      <th>HMDB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FT2518</td>\n",
       "      <td>M-H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>UIDGLYUNOUKLBM-UHFFFAOYSA-N</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Flavonoids</td>\n",
       "      <td>Flavonoid glycosides</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></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FT0551</td>\n",
       "      <td>M-H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>YUFFSWGQGVEMMI-UHFFFAOYSA-N</td>\n",
       "      <td>Lipids and lipid-like molecules</td>\n",
       "      <td>Fatty Acyls</td>\n",
       "      <td>Fatty acids and conjugates</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></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FT2417</td>\n",
       "      <td>M+H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>YPZRWBKMTBYPTK-BJDJZHNGSA-N</td>\n",
       "      <td>Organic acids and derivatives</td>\n",
       "      <td>Carboxylic acids and derivatives</td>\n",
       "      <td>Amino acids, peptides, and analogues</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></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FT2103</td>\n",
       "      <td>M-H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>XFZJEEAOWLFHDH-NFJBMHMQSA-N</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Flavonoids</td>\n",
       "      <td>Biflavonoids and polyflavonoids</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></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FT1134</td>\n",
       "      <td>M-H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>JPUKWEQWGBDDQB-QSOFNFLRSA-N</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Flavonoids</td>\n",
       "      <td>Flavonoid glycosides</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></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Features Adduct   IonMode                     InChIKey  \\\n",
       "0   FT2518    M-H  Negative  UIDGLYUNOUKLBM-UHFFFAOYSA-N   \n",
       "1   FT0551    M-H  Negative  YUFFSWGQGVEMMI-UHFFFAOYSA-N   \n",
       "2   FT2417    M+H  Negative  YPZRWBKMTBYPTK-BJDJZHNGSA-N   \n",
       "3   FT2103    M-H  Negative  XFZJEEAOWLFHDH-NFJBMHMQSA-N   \n",
       "4   FT1134    M-H  Negative  JPUKWEQWGBDDQB-QSOFNFLRSA-N   \n",
       "\n",
       "                         superclass                             class  \\\n",
       "0  Phenylpropanoids and polyketides                        Flavonoids   \n",
       "1   Lipids and lipid-like molecules                       Fatty Acyls   \n",
       "2     Organic acids and derivatives  Carboxylic acids and derivatives   \n",
       "3  Phenylpropanoids and polyketides                        Flavonoids   \n",
       "4  Phenylpropanoids and polyketides                        Flavonoids   \n",
       "\n",
       "                               subclass MolecularFormula ExactMass InChI  \\\n",
       "0                  Flavonoid glycosides                                    \n",
       "1            Fatty acids and conjugates                                    \n",
       "2  Amino acids, peptides, and analogues                                    \n",
       "3       Biflavonoids and polyflavonoids                                    \n",
       "4                  Flavonoid glycosides                                    \n",
       "\n",
       "  CanonicalSmiles PubChem_CID KEGG BioCyc LIPIDMAPS ChEBI HMDB  \n",
       "0                                                               \n",
       "1                                                               \n",
       "2                                                               \n",
       "3                                                               \n",
       "4                                                               "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subset_df = df.copy()\n",
    "\n",
    "subset_df = subset_df.loc[:, [\n",
    "    'Adduct',\n",
    "    'IonMode',\n",
    "    'InChIKey',\n",
    "    '#Scan#',\n",
    "    'superclass',\n",
    "    'class',\n",
    "    'subclass',\n",
    "]]\n",
    "\n",
    "subset_df = subset_df[~subset_df['InChIKey'].isna()].reset_index(drop=True)\n",
    "subset_df['#Scan#'] = \"FT\"+subset_df['#Scan#'].apply(lambda x: '{0:0>4}'.format(x))\n",
    "subset_df.rename(columns={'#Scan#':'Features'}, inplace=True)\n",
    "subset_df = subset_df.set_index('Features')\n",
    "subset_df = subset_df.reset_index()\n",
    "list_columns = list(subset_df.columns)\n",
    "new_columns = ['MolecularFormula', 'ExactMass', 'InChI', 'CanonicalSmiles', \n",
    "               'PubChem_CID', 'KEGG', 'BioCyc', 'LIPIDMAPS', 'ChEBI', 'HMDB']\n",
    "\n",
    "list_columns.extend(new_columns)\n",
    "subset_df = subset_df.reindex(columns = list_columns, fill_value='')\n",
    "\n",
    "print(subset_df.shape)\n",
    "subset_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Translate InChIKey to xref in KEGG, BioCyc, HMDB, etc.\n",
    "\n",
    "I used the REST API by [Chemical Translation Service - CTS](https://cts.fiehnlab.ucdavis.edu/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done\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>Adduct</th>\n",
       "      <th>IonMode</th>\n",
       "      <th>InChIKey</th>\n",
       "      <th>superclass</th>\n",
       "      <th>class</th>\n",
       "      <th>subclass</th>\n",
       "      <th>MolecularFormula</th>\n",
       "      <th>ExactMass</th>\n",
       "      <th>InChI</th>\n",
       "      <th>CanonicalSmiles</th>\n",
       "      <th>PubChem_CID</th>\n",
       "      <th>KEGG</th>\n",
       "      <th>BioCyc</th>\n",
       "      <th>LIPIDMAPS</th>\n",
       "      <th>ChEBI</th>\n",
       "      <th>HMDB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FT2518</td>\n",
       "      <td>M-H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>UIDGLYUNOUKLBM-UHFFFAOYSA-N</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Flavonoids</td>\n",
       "      <td>Flavonoid glycosides</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>6223069</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>HMDB0037746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FT0551</td>\n",
       "      <td>M-H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>YUFFSWGQGVEMMI-UHFFFAOYSA-N</td>\n",
       "      <td>Lipids and lipid-like molecules</td>\n",
       "      <td>Fatty Acyls</td>\n",
       "      <td>Fatty acids and conjugates</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>3145</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FT2417</td>\n",
       "      <td>M+H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>YPZRWBKMTBYPTK-BJDJZHNGSA-N</td>\n",
       "      <td>Organic acids and derivatives</td>\n",
       "      <td>Carboxylic acids and derivatives</td>\n",
       "      <td>Amino acids, peptides, and analogues</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>11215652,65359</td>\n",
       "      <td>C00127,D00031</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>CHEBI:17858</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FT2103</td>\n",
       "      <td>M-H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>XFZJEEAOWLFHDH-NFJBMHMQSA-N</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Flavonoids</td>\n",
       "      <td>Biflavonoids and polyflavonoids</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>122738</td>\n",
       "      <td>C17639</td>\n",
       "      <td>CPD-1981</td>\n",
       "      <td>LMPK12030002</td>\n",
       "      <td>CHEBI:75632</td>\n",
       "      <td>HMDB0033973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FT1134</td>\n",
       "      <td>M-H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>JPUKWEQWGBDDQB-QSOFNFLRSA-N</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Flavonoids</td>\n",
       "      <td>Flavonoid glycosides</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>5282102</td>\n",
       "      <td>C12249</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>CHEBI:30200</td>\n",
       "      <td>HMDB0037429</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Features Adduct   IonMode                     InChIKey  \\\n",
       "0   FT2518    M-H  Negative  UIDGLYUNOUKLBM-UHFFFAOYSA-N   \n",
       "1   FT0551    M-H  Negative  YUFFSWGQGVEMMI-UHFFFAOYSA-N   \n",
       "2   FT2417    M+H  Negative  YPZRWBKMTBYPTK-BJDJZHNGSA-N   \n",
       "3   FT2103    M-H  Negative  XFZJEEAOWLFHDH-NFJBMHMQSA-N   \n",
       "4   FT1134    M-H  Negative  JPUKWEQWGBDDQB-QSOFNFLRSA-N   \n",
       "\n",
       "                         superclass                             class  \\\n",
       "0  Phenylpropanoids and polyketides                        Flavonoids   \n",
       "1   Lipids and lipid-like molecules                       Fatty Acyls   \n",
       "2     Organic acids and derivatives  Carboxylic acids and derivatives   \n",
       "3  Phenylpropanoids and polyketides                        Flavonoids   \n",
       "4  Phenylpropanoids and polyketides                        Flavonoids   \n",
       "\n",
       "                               subclass MolecularFormula ExactMass InChI  \\\n",
       "0                  Flavonoid glycosides                                    \n",
       "1            Fatty acids and conjugates                                    \n",
       "2  Amino acids, peptides, and analogues                                    \n",
       "3       Biflavonoids and polyflavonoids                                    \n",
       "4                  Flavonoid glycosides                                    \n",
       "\n",
       "  CanonicalSmiles     PubChem_CID           KEGG    BioCyc     LIPIDMAPS  \\\n",
       "0                         6223069                                          \n",
       "1                            3145                                          \n",
       "2                  11215652,65359  C00127,D00031                           \n",
       "3                          122738         C17639  CPD-1981  LMPK12030002   \n",
       "4                         5282102         C12249                           \n",
       "\n",
       "         ChEBI         HMDB  \n",
       "0               HMDB0037746  \n",
       "1                            \n",
       "2  CHEBI:17858               \n",
       "3  CHEBI:75632  HMDB0033973  \n",
       "4  CHEBI:30200  HMDB0037429  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for index, row in subset_df.iterrows():\n",
    "    #print(index, row['InChIKey'])\n",
    "    \n",
    "    query = row['InChIKey']\n",
    "    \n",
    "    ## translate inchikey using the Chemical Translation Service - CTS\n",
    "    s = requests.Session()\n",
    "    main_url = \"https://cts.fiehnlab.ucdavis.edu/rest/\"\n",
    "\n",
    "    list_dbs = ['KEGG', 'BioCyc', 'LIPIDMAPS', 'ChEBI', 'Human Metabolome Database', \"PubChem CID\"]\n",
    "    result_dict = {key:None for key in list_dbs}\n",
    "    result_dict['InChIKey'] = query\n",
    "\n",
    "    for db in list_dbs:\n",
    "\n",
    "        r = s.get(main_url+'convert/InChIKey/'+db+'/'+query)\n",
    "        r = json.loads(r.text)\n",
    "\n",
    "        result = dict()\n",
    "\n",
    "        if type(r) == dict: # error\n",
    "            pass\n",
    "        else:\n",
    "            hit_dict = r[0]\n",
    "            #print(hit_dict)\n",
    "            if 'results' in hit_dict:\n",
    "                if len(hit_dict['results']) == 1:\n",
    "                    result_dict[db] = hit_dict['results'][0]\n",
    "                elif len(hit_dict['results']) > 1:\n",
    "                    result_dict[db] = \",\".join(hit_dict['results'])\n",
    "                else:\n",
    "                    result_dict[db] = \"\"\n",
    "            else:\n",
    "                result_dict[db] = \"\"\n",
    "    \n",
    "    subset_df.loc[index,'KEGG'] = result_dict['KEGG']\n",
    "    subset_df.loc[index,'BioCyc'] = result_dict['BioCyc']\n",
    "    subset_df.loc[index,'LIPIDMAPS'] = result_dict['LIPIDMAPS']\n",
    "    subset_df.loc[index,'ChEBI'] = result_dict['ChEBI']\n",
    "    subset_df.loc[index,'HMDB'] = result_dict['Human Metabolome Database']\n",
    "    subset_df.loc[index,'PubChem_CID'] = result_dict['PubChem CID']\n",
    "    \n",
    "print('Done')\n",
    "subset_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Use InChIKey to extract Smiles, Molecular Formulas from PubChem\n",
    "\n",
    "GNPS does not provide these info in their output.\n",
    "\n",
    "I used the Pug REST Service by PubChem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done\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>Adduct</th>\n",
       "      <th>IonMode</th>\n",
       "      <th>InChIKey</th>\n",
       "      <th>superclass</th>\n",
       "      <th>class</th>\n",
       "      <th>subclass</th>\n",
       "      <th>MolecularFormula</th>\n",
       "      <th>ExactMass</th>\n",
       "      <th>InChI</th>\n",
       "      <th>CanonicalSmiles</th>\n",
       "      <th>PubChem_CID</th>\n",
       "      <th>KEGG</th>\n",
       "      <th>BioCyc</th>\n",
       "      <th>LIPIDMAPS</th>\n",
       "      <th>ChEBI</th>\n",
       "      <th>HMDB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FT2518</td>\n",
       "      <td>M-H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>UIDGLYUNOUKLBM-UHFFFAOYSA-N</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Flavonoids</td>\n",
       "      <td>Flavonoid glycosides</td>\n",
       "      <td>C28H32O16</td>\n",
       "      <td>624.169035</td>\n",
       "      <td>InChI=1S/C28H32O16/c1-9-18(32)21(35)23(37)27(4...</td>\n",
       "      <td>CC1C(C(C(C(O1)OCC2C(C(C(C(O2)OC3=C(OC4=CC(=CC(...</td>\n",
       "      <td>6223069</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>HMDB0037746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FT0551</td>\n",
       "      <td>M-H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>YUFFSWGQGVEMMI-UHFFFAOYSA-N</td>\n",
       "      <td>Lipids and lipid-like molecules</td>\n",
       "      <td>Fatty Acyls</td>\n",
       "      <td>Fatty acids and conjugates</td>\n",
       "      <td>C22H34O2</td>\n",
       "      <td>330.25588</td>\n",
       "      <td>InChI=1S/C22H34O2/c1-2-3-4-5-6-7-8-9-10-11-12-...</td>\n",
       "      <td>CCC=CCC=CCC=CCC=CCC=CCCCCCC(=O)O</td>\n",
       "      <td>3145</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FT2417</td>\n",
       "      <td>M+H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>YPZRWBKMTBYPTK-BJDJZHNGSA-N</td>\n",
       "      <td>Organic acids and derivatives</td>\n",
       "      <td>Carboxylic acids and derivatives</td>\n",
       "      <td>Amino acids, peptides, and analogues</td>\n",
       "      <td>C20H32N6O12S2</td>\n",
       "      <td>612.151963</td>\n",
       "      <td>InChI=1S/C20H32N6O12S2/c21-9(19(35)36)1-3-13(2...</td>\n",
       "      <td>C(CC(=O)NC(CSSCC(C(=O)NCC(=O)O)NC(=O)CCC(C(=O)...</td>\n",
       "      <td>11215652,65359</td>\n",
       "      <td>C00127,D00031</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>CHEBI:17858</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FT2103</td>\n",
       "      <td>M-H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>XFZJEEAOWLFHDH-NFJBMHMQSA-N</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Flavonoids</td>\n",
       "      <td>Biflavonoids and polyflavonoids</td>\n",
       "      <td>C30H26O12</td>\n",
       "      <td>578.142426</td>\n",
       "      <td>InChI=1S/C30H26O12/c31-13-7-20(37)24-23(8-13)4...</td>\n",
       "      <td>C1C(C(OC2=C1C(=CC(=C2C3C(C(OC4=CC(=CC(=C34)O)O...</td>\n",
       "      <td>122738</td>\n",
       "      <td>C17639</td>\n",
       "      <td>CPD-1981</td>\n",
       "      <td>LMPK12030002</td>\n",
       "      <td>CHEBI:75632</td>\n",
       "      <td>HMDB0033973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FT1134</td>\n",
       "      <td>M-H</td>\n",
       "      <td>Negative</td>\n",
       "      <td>JPUKWEQWGBDDQB-QSOFNFLRSA-N</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Flavonoids</td>\n",
       "      <td>Flavonoid glycosides</td>\n",
       "      <td>C21H20O11</td>\n",
       "      <td>448.100561</td>\n",
       "      <td>InChI=1S/C21H20O11/c22-7-13-15(26)17(28)18(29)...</td>\n",
       "      <td>C1=CC(=CC=C1C2=C(C(=O)C3=C(C=C(C=C3O2)O)O)OC4C...</td>\n",
       "      <td>5282102</td>\n",
       "      <td>C12249</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>CHEBI:30200</td>\n",
       "      <td>HMDB0037429</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Features Adduct   IonMode                     InChIKey  \\\n",
       "0   FT2518    M-H  Negative  UIDGLYUNOUKLBM-UHFFFAOYSA-N   \n",
       "1   FT0551    M-H  Negative  YUFFSWGQGVEMMI-UHFFFAOYSA-N   \n",
       "2   FT2417    M+H  Negative  YPZRWBKMTBYPTK-BJDJZHNGSA-N   \n",
       "3   FT2103    M-H  Negative  XFZJEEAOWLFHDH-NFJBMHMQSA-N   \n",
       "4   FT1134    M-H  Negative  JPUKWEQWGBDDQB-QSOFNFLRSA-N   \n",
       "\n",
       "                         superclass                             class  \\\n",
       "0  Phenylpropanoids and polyketides                        Flavonoids   \n",
       "1   Lipids and lipid-like molecules                       Fatty Acyls   \n",
       "2     Organic acids and derivatives  Carboxylic acids and derivatives   \n",
       "3  Phenylpropanoids and polyketides                        Flavonoids   \n",
       "4  Phenylpropanoids and polyketides                        Flavonoids   \n",
       "\n",
       "                               subclass MolecularFormula   ExactMass  \\\n",
       "0                  Flavonoid glycosides        C28H32O16  624.169035   \n",
       "1            Fatty acids and conjugates         C22H34O2   330.25588   \n",
       "2  Amino acids, peptides, and analogues    C20H32N6O12S2  612.151963   \n",
       "3       Biflavonoids and polyflavonoids        C30H26O12  578.142426   \n",
       "4                  Flavonoid glycosides        C21H20O11  448.100561   \n",
       "\n",
       "                                               InChI  \\\n",
       "0  InChI=1S/C28H32O16/c1-9-18(32)21(35)23(37)27(4...   \n",
       "1  InChI=1S/C22H34O2/c1-2-3-4-5-6-7-8-9-10-11-12-...   \n",
       "2  InChI=1S/C20H32N6O12S2/c21-9(19(35)36)1-3-13(2...   \n",
       "3  InChI=1S/C30H26O12/c31-13-7-20(37)24-23(8-13)4...   \n",
       "4  InChI=1S/C21H20O11/c22-7-13-15(26)17(28)18(29)...   \n",
       "\n",
       "                                     CanonicalSmiles     PubChem_CID  \\\n",
       "0  CC1C(C(C(C(O1)OCC2C(C(C(C(O2)OC3=C(OC4=CC(=CC(...         6223069   \n",
       "1                   CCC=CCC=CCC=CCC=CCC=CCCCCCC(=O)O            3145   \n",
       "2  C(CC(=O)NC(CSSCC(C(=O)NCC(=O)O)NC(=O)CCC(C(=O)...  11215652,65359   \n",
       "3  C1C(C(OC2=C1C(=CC(=C2C3C(C(OC4=CC(=CC(=C34)O)O...          122738   \n",
       "4  C1=CC(=CC=C1C2=C(C(=O)C3=C(C=C(C=C3O2)O)O)OC4C...         5282102   \n",
       "\n",
       "            KEGG    BioCyc     LIPIDMAPS        ChEBI         HMDB  \n",
       "0                                                      HMDB0037746  \n",
       "1                                                                   \n",
       "2  C00127,D00031                          CHEBI:17858               \n",
       "3         C17639  CPD-1981  LMPK12030002  CHEBI:75632  HMDB0033973  \n",
       "4         C12249                          CHEBI:30200  HMDB0037429  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for index, row in subset_df.iterrows():\n",
    "    #print(index, row['InChIKey'])\n",
    "    \n",
    "    query = row['InChIKey']\n",
    "    \n",
    "    ## search pubchem for molecular formula, exact mass, smiles and inchi using inchikey\n",
    "    ## 'MolecularFormula', 'ExactMass', 'InChI', 'CanonicalSmiles',\n",
    "    properties = 'MolecularFormula,MolecularWeight,ExactMass,CanonicalSMILES,InChI'\n",
    "\n",
    "    r = s.get('https://pubchem.ncbi.nlm.nih.gov/rest/pug/compound/inchikey/'+query+'/property/'+properties+'/CSV').content\n",
    "\n",
    "    result_df = pd.read_csv(io.StringIO(r.decode('utf-8')))\n",
    "    \n",
    "    if 'Status: 404' in result_df.columns:\n",
    "        pass\n",
    "    \n",
    "    elif not result_df.empty == True:\n",
    "        \n",
    "        mol_formula = \",\".join(list(result_df['MolecularFormula'].unique()))\n",
    "        subset_df.loc[index,'MolecularFormula'] = mol_formula\n",
    "        \n",
    "        ex_mass = \",\".join([str(x) for x in result_df['ExactMass'].unique()])\n",
    "        subset_df.loc[index,'ExactMass'] = ex_mass\n",
    "        \n",
    "        can_smiles = \",\".join(list(result_df['CanonicalSMILES'].unique()))\n",
    "        subset_df.loc[index,'CanonicalSmiles'] = can_smiles\n",
    "        \n",
    "        inchi = \",\".join(list(result_df['InChI'].unique()))\n",
    "        subset_df.loc[index,'InChI'] = inchi\n",
    "    \n",
    "    else:\n",
    "        pass\n",
    "    \n",
    "    \n",
    "    if index%5 == 0:\n",
    "        time.sleep(3)\n",
    "\n",
    "    #print(index, row['InChIKey'], 'Done searches')\n",
    "    \n",
    "print('Done')\n",
    "subset_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "subset_df.to_csv('summary_output_GNPS.csv', index=False)"
   ]
  },
  {
   "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
}