mapping_metabolomics_compound_to_SPOKE.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "c5deea7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from neo4j import GraphDatabase, basic_auth\n",
    "from paths import *\n",
    "import pandas as pd\n",
    "import os\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36ff8c0c",
   "metadata": {},
   "source": [
    "## Mapping short chain fatty acids to SPOKE nodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "492e38f9",
   "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>name</th>\n",
       "      <th>spoke_identifer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2-Methylbutyric acid</td>\n",
       "      <td>inchikey:WLAMNBDJUVNPJU-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Acetic acid</td>\n",
       "      <td>inchikey:QTBSBXVTEAMEQO-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Butyric acid</td>\n",
       "      <td>inchikey:FERIUCNNQQJTOY-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Hexanoic acid</td>\n",
       "      <td>inchikey:FUZZWVXGSFPDMH-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Isobutyric acid</td>\n",
       "      <td>inchikey:KQNPFQTWMSNSAP-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Isovaleric acid</td>\n",
       "      <td>inchikey:GWYFCOCPABKNJV-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Propionic acid</td>\n",
       "      <td>inchikey:XBDQKXXYIPTUBI-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Valeric acid</td>\n",
       "      <td>inchikey:NQPDZGIKBAWPEJ-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name                       spoke_identifer\n",
       "0  2-Methylbutyric acid  inchikey:WLAMNBDJUVNPJU-UHFFFAOYSA-N\n",
       "1           Acetic acid  inchikey:QTBSBXVTEAMEQO-UHFFFAOYSA-N\n",
       "2          Butyric acid  inchikey:FERIUCNNQQJTOY-UHFFFAOYSA-N\n",
       "3         Hexanoic acid  inchikey:FUZZWVXGSFPDMH-UHFFFAOYSA-N\n",
       "4       Isobutyric acid  inchikey:KQNPFQTWMSNSAP-UHFFFAOYSA-N\n",
       "5       Isovaleric acid  inchikey:GWYFCOCPABKNJV-UHFFFAOYSA-N\n",
       "6        Propionic acid  inchikey:XBDQKXXYIPTUBI-UHFFFAOYSA-N\n",
       "7          Valeric acid  inchikey:NQPDZGIKBAWPEJ-UHFFFAOYSA-N"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "short_chain_spoke_map = [\n",
    "    {\n",
    "        \"name\" : \"2-Methylbutyric acid\",\n",
    "        \"identifier\": \"inchikey:WLAMNBDJUVNPJU-UHFFFAOYSA-N\"\n",
    "    },\n",
    "    {\n",
    "        \"name\" : \"Acetic acid\",\n",
    "        \"identifier\" : \"inchikey:QTBSBXVTEAMEQO-UHFFFAOYSA-N\"\n",
    "    },\n",
    "    {\n",
    "        \"name\" : \"Butyric acid\",\n",
    "        \"identifier\" : \"inchikey:FERIUCNNQQJTOY-UHFFFAOYSA-N\"\n",
    "    },\n",
    "    {\n",
    "        \"name\" : \"Hexanoic acid\",\n",
    "        \"identifier\" : \"inchikey:FUZZWVXGSFPDMH-UHFFFAOYSA-N\"\n",
    "    },\n",
    "    {\n",
    "        \"name\" : \"Isobutyric acid\",\n",
    "        \"identifier\" : \"inchikey:KQNPFQTWMSNSAP-UHFFFAOYSA-N\"\n",
    "    },\n",
    "    {\n",
    "        \"name\" : \"Isovaleric acid\",\n",
    "        \"identifier\" : \"inchikey:GWYFCOCPABKNJV-UHFFFAOYSA-N\"\n",
    "    },\n",
    "    {\n",
    "        \"name\" : \"Propionic acid\",\n",
    "        \"identifier\": \"inchikey:XBDQKXXYIPTUBI-UHFFFAOYSA-N\"\n",
    "    },\n",
    "    {\n",
    "        \"name\" : \"Valeric acid\",\n",
    "        \"identifier\" : \"inchikey:NQPDZGIKBAWPEJ-UHFFFAOYSA-N\"\n",
    "    }    \n",
    "]\n",
    " \n",
    "short_chain_spoke_map_df = pd.DataFrame(short_chain_spoke_map)\n",
    "short_chain_spoke_map_df = short_chain_spoke_map_df.rename(columns={\"identifier\":\"spoke_identifer\"})\n",
    "short_chain_spoke_map_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5ce1b31",
   "metadata": {},
   "source": [
    "## Comparing names of short chain fatty acids from iMSMS table and SPOKE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "b52a8eaf",
   "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>spoke_identifer</th>\n",
       "      <th>metabolon_name</th>\n",
       "      <th>spoke_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>inchikey:WLAMNBDJUVNPJU-UHFFFAOYSA-N</td>\n",
       "      <td>2-Methylbutyric acid</td>\n",
       "      <td>2-Methylbutanoic acid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>inchikey:QTBSBXVTEAMEQO-UHFFFAOYSA-N</td>\n",
       "      <td>Acetic acid</td>\n",
       "      <td>Acetic Acid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>inchikey:FERIUCNNQQJTOY-UHFFFAOYSA-N</td>\n",
       "      <td>Butyric acid</td>\n",
       "      <td>Butyric Acid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>inchikey:FUZZWVXGSFPDMH-UHFFFAOYSA-N</td>\n",
       "      <td>Hexanoic acid</td>\n",
       "      <td>Hexanoic acid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>inchikey:KQNPFQTWMSNSAP-UHFFFAOYSA-N</td>\n",
       "      <td>Isobutyric acid</td>\n",
       "      <td>Isobutyric acid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>inchikey:GWYFCOCPABKNJV-UHFFFAOYSA-N</td>\n",
       "      <td>Isovaleric acid</td>\n",
       "      <td>Isovaleric acid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>inchikey:XBDQKXXYIPTUBI-UHFFFAOYSA-N</td>\n",
       "      <td>Propionic acid</td>\n",
       "      <td>Propionic Acid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>inchikey:NQPDZGIKBAWPEJ-UHFFFAOYSA-N</td>\n",
       "      <td>Valeric acid</td>\n",
       "      <td>Valeric acid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        spoke_identifer        metabolon_name  \\\n",
       "0  inchikey:WLAMNBDJUVNPJU-UHFFFAOYSA-N  2-Methylbutyric acid   \n",
       "1  inchikey:QTBSBXVTEAMEQO-UHFFFAOYSA-N           Acetic acid   \n",
       "2  inchikey:FERIUCNNQQJTOY-UHFFFAOYSA-N          Butyric acid   \n",
       "3  inchikey:FUZZWVXGSFPDMH-UHFFFAOYSA-N         Hexanoic acid   \n",
       "4  inchikey:KQNPFQTWMSNSAP-UHFFFAOYSA-N       Isobutyric acid   \n",
       "5  inchikey:GWYFCOCPABKNJV-UHFFFAOYSA-N       Isovaleric acid   \n",
       "6  inchikey:XBDQKXXYIPTUBI-UHFFFAOYSA-N        Propionic acid   \n",
       "7  inchikey:NQPDZGIKBAWPEJ-UHFFFAOYSA-N          Valeric acid   \n",
       "\n",
       "              spoke_name  \n",
       "0  2-Methylbutanoic acid  \n",
       "1            Acetic Acid  \n",
       "2           Butyric Acid  \n",
       "3          Hexanoic acid  \n",
       "4        Isobutyric acid  \n",
       "5        Isovaleric acid  \n",
       "6         Propionic Acid  \n",
       "7           Valeric acid  "
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"MATCH(n:Compound) WHERE n.identifier = '{}' RETURN n.name as n_name\"\n",
    "\n",
    "auth = basic_auth(SPOKE_USER, SPOKE_PASSWORD)\n",
    "sdb = GraphDatabase.driver(URI, auth=auth)\n",
    "node_list = []\n",
    "with sdb.session() as session:\n",
    "    with session.begin_transaction() as tx:\n",
    "        for index,row in short_chain_spoke_map_df.iterrows():\n",
    "            result = tx.run(query.format(row[\"spoke_identifer\"]))\n",
    "            for row_ in result:\n",
    "                node_list.append((row[\"spoke_identifer\"], row[\"name\"], row_[\"n_name\"]))\n",
    "sdb.close()\n",
    "node_df = pd.DataFrame(node_list, columns = [\"spoke_identifer\", \"metabolon_name\", \"spoke_name\"])\n",
    "node_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3e06c62",
   "metadata": {},
   "source": [
    "## Mapping Compounds in the Global metabolomics files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef8f358e",
   "metadata": {},
   "outputs": [],
   "source": [
    "filename = GLOBAL_SERUM_DATA_FILENAME\n",
    "\n",
    "file_path = os.path.join(DATA_ROOT_PATH, filename)\n",
    "\n",
    "sheet_name = [\"Chemical Annotation\", \"Sample Meta Data\", \"Log Transformed Data\"]\n",
    "\n",
    "data = pd.read_excel(file_path, engine='openpyxl', sheet_name=sheet_name[0])\n",
    "\n",
    "# Selecting only rows whose TYPE is NAMED\n",
    "data = data[data[\"TYPE\"] == \"NAMED\"]\n",
    "\n",
    "# Selecting rows with INCHIKEY\n",
    "data_INCHI = data.dropna(subset=[\"INCHIKEY\"])\n",
    "data_INCHI[\"INCHIKEY\"] = \"inchikey:\"+data_INCHI[\"INCHIKEY\"]\n",
    "\n",
    "# Selecting rows without INCHIKEY\n",
    "data_ = data[data['INCHIKEY'].isna()]\n",
    "\n",
    "# From the without INCHI dataframe, select rows with KEGG id\n",
    "data_ = data_.dropna(subset=[\"KEGG\"])\n",
    "data_[\"KEGG\"] = data_[\"KEGG\"].apply(lambda x:x.split(\",\"))\n",
    "data_ = data_.explode(\"KEGG\")\n",
    "data_[\"KEGG\"] = \"kegg.compound:\" + data_[\"KEGG\"]\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdf40a70",
   "metadata": {},
   "source": [
    "## Finding INCHIKEY for those compounds with only KEGG ids in iMSMS data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "452a66b7",
   "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>KEGG</th>\n",
       "      <th>identifer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>kegg.compound:C02341</td>\n",
       "      <td>inchikey:GTZCVFVGUGFEME-HNQUOIGGSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>kegg.compound:C00803</td>\n",
       "      <td>inchikey:NQPDZGIKBAWPEJ-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   KEGG                             identifer\n",
       "0  kegg.compound:C02341  inchikey:GTZCVFVGUGFEME-HNQUOIGGSA-N\n",
       "1  kegg.compound:C00803  inchikey:NQPDZGIKBAWPEJ-UHFFFAOYSA-N"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "auth = basic_auth(SPOKE_USER, SPOKE_PASSWORD)\n",
    "sdb = GraphDatabase.driver(URI, auth=auth)\n",
    "query = \"MATCH(n:Compound) WHERE '{}' IN n.xrefs RETURN n.identifier AS n_id\"\n",
    "sdb = GraphDatabase.driver(URI, auth=auth)\n",
    "node_list = []\n",
    "with sdb.session() as session:\n",
    "    with session.begin_transaction() as tx:\n",
    "        for index,row in data_.iterrows():\n",
    "            result = tx.run(query.format(row[\"KEGG\"]))\n",
    "            for row_ in result:\n",
    "                node_list.append((row[\"KEGG\"], row_[\"n_id\"]))\n",
    "sdb.close()\n",
    "node_df = pd.DataFrame(node_list, columns=[\"KEGG\", \"identifer\"])\n",
    "node_df\n",
    "        \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8c37ffb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_KEGG_merge = pd.merge(data_, node_df, on=\"KEGG\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b72cb4b",
   "metadata": {},
   "source": [
    "## Selecting only relevant columns for mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "af21d55f",
   "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>CHEM_ID</th>\n",
       "      <th>name</th>\n",
       "      <th>spoke_identifer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35</td>\n",
       "      <td>S-1-pyrroline-5-carboxylate</td>\n",
       "      <td>inchikey:DWAKNKKXGALPNW-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50</td>\n",
       "      <td>spermidine</td>\n",
       "      <td>inchikey:ATHGHQPFGPMSJY-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>55</td>\n",
       "      <td>1-methylnicotinamide</td>\n",
       "      <td>inchikey:LDHMAVIPBRSVRG-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62</td>\n",
       "      <td>12,13-DiHOME</td>\n",
       "      <td>inchikey:CQSLTKIXAJTQGA-FLIBITNWSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>93</td>\n",
       "      <td>alpha-ketoglutarate</td>\n",
       "      <td>inchikey:KPGXRSRHYNQIFN-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1201</th>\n",
       "      <td>100022127</td>\n",
       "      <td>tetrahydrocortisone glucuronide (5)</td>\n",
       "      <td>inchikey:QUOCEDQXFGCYTL-QMELEVSMSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1202</th>\n",
       "      <td>100022172</td>\n",
       "      <td>perfluorohexanesulfonate (PFHxS)</td>\n",
       "      <td>inchikey:QZHDEAJFRJCDMF-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1203</th>\n",
       "      <td>100022475</td>\n",
       "      <td>menthol glucuronide</td>\n",
       "      <td>inchikey:CLJGMBYGTHRUNF-PJQJKGEDSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1204</th>\n",
       "      <td>100001359</td>\n",
       "      <td>aconitate [cis or trans]</td>\n",
       "      <td>inchikey:GTZCVFVGUGFEME-HNQUOIGGSA-N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1205</th>\n",
       "      <td>100021467</td>\n",
       "      <td>pentose acid*</td>\n",
       "      <td>inchikey:NQPDZGIKBAWPEJ-UHFFFAOYSA-N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1206 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        CHEM_ID                                 name  \\\n",
       "0            35          S-1-pyrroline-5-carboxylate   \n",
       "1            50                           spermidine   \n",
       "2            55                 1-methylnicotinamide   \n",
       "3            62                         12,13-DiHOME   \n",
       "4            93                  alpha-ketoglutarate   \n",
       "...         ...                                  ...   \n",
       "1201  100022127  tetrahydrocortisone glucuronide (5)   \n",
       "1202  100022172     perfluorohexanesulfonate (PFHxS)   \n",
       "1203  100022475                  menthol glucuronide   \n",
       "1204  100001359             aconitate [cis or trans]   \n",
       "1205  100021467                        pentose acid*   \n",
       "\n",
       "                           spoke_identifer  \n",
       "0     inchikey:DWAKNKKXGALPNW-UHFFFAOYSA-N  \n",
       "1     inchikey:ATHGHQPFGPMSJY-UHFFFAOYSA-N  \n",
       "2     inchikey:LDHMAVIPBRSVRG-UHFFFAOYSA-N  \n",
       "3     inchikey:CQSLTKIXAJTQGA-FLIBITNWSA-N  \n",
       "4     inchikey:KPGXRSRHYNQIFN-UHFFFAOYSA-N  \n",
       "...                                    ...  \n",
       "1201  inchikey:QUOCEDQXFGCYTL-QMELEVSMSA-N  \n",
       "1202  inchikey:QZHDEAJFRJCDMF-UHFFFAOYSA-N  \n",
       "1203  inchikey:CLJGMBYGTHRUNF-PJQJKGEDSA-N  \n",
       "1204  inchikey:GTZCVFVGUGFEME-HNQUOIGGSA-N  \n",
       "1205  inchikey:NQPDZGIKBAWPEJ-UHFFFAOYSA-N  \n",
       "\n",
       "[1206 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_INCHI_map = data_INCHI[[\"CHEM_ID\", \"CHEMICAL_NAME\", \"INCHIKEY\"]] \n",
    "data_KEGG_merge_map = data_KEGG_merge[[\"CHEM_ID\", \"CHEMICAL_NAME\", \"identifer\"]] \n",
    "data_KEGG_merge_map = data_KEGG_merge_map.rename(columns={\"identifer\":\"INCHIKEY\"})\n",
    "data_final_map = pd.concat([data_INCHI_map, data_KEGG_merge_map], ignore_index=True)\n",
    "data_final_map = data_final_map.rename(columns={\"CHEMICAL_NAME\": \"name\", \"INCHIKEY\": \"spoke_identifer\"})\n",
    "data_final_map"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d87b1e29",
   "metadata": {},
   "source": [
    "## Saving the mapping files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76c6cd2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "short_chain_spoke_map_df.to_csv(os.path.join(OUTPUT_PATH, \"short_chain_fatty_acid_spoke_map.csv\"), index=False, header=True)\n",
    "data_final_map.to_csv(os.path.join(OUTPUT_PATH, \"global_metabolomics_compound_spoke_map.csv\"), index=False, header=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f7971a4",
   "metadata": {},
   "source": [
    "## Checking iMSMS global compounds whose inchikeys do not match with any SPOKE nodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "6b81ac56",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 439 iMSMS global compounds whose inchikeys do not map to any SPOKE nodes\n"
     ]
    }
   ],
   "source": [
    "iMSMS_compounds_not_in_SPOKE_df = pd.read_csv(os.path.join(OUTPUT_PATH, \"iMSMS_compounds_not_in_SPOKE.csv\"))\n",
    "iMSMS_compounds_not_in_SPOKE_with_names_df = data_final_map[data_final_map.spoke_identifer.isin(iMSMS_compounds_not_in_SPOKE_df.CompoundIdentifier)]\n",
    "print(\"There are {} iMSMS global compounds whose inchikeys do not map to any SPOKE nodes\".format(iMSMS_compounds_not_in_SPOKE_with_names_df.CHEM_ID.unique().shape[0]))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d94c93b",
   "metadata": {},
   "source": [
    "## Mapping to SPOKE based on xrefs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0023a7d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "auth = basic_auth(SPOKE_USER, SPOKE_PASSWORD)\n",
    "sdb = GraphDatabase.driver(URI, auth=auth)\n",
    "\n",
    "query = \"\"\"\n",
    "            MATCH (n:Compound)\n",
    "            WHERE ANY(xref IN n.xrefs WHERE xref IN {})\n",
    "            RETURN n.identifier as n_id;\n",
    "        \"\"\"\n",
    "\n",
    "iMSMS_compounds_not_in_SPOKE_with_names_xref_df = data_INCHI[data_INCHI.INCHIKEY.isin(iMSMS_compounds_not_in_SPOKE_with_names_df.spoke_identifer)]\n",
    "iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE = []\n",
    "iMSMS_compounds_not_in_SPOKE_with_names_xref_df[\"KEGG\"] = \"kegg.compound:\"+iMSMS_compounds_not_in_SPOKE_with_names_xref_df[\"KEGG\"]\n",
    "iMSMS_compounds_not_in_SPOKE_with_names_xref_df[\"PUBCHEM\"] = \"pubchem.compound:\"+iMSMS_compounds_not_in_SPOKE_with_names_xref_df[\"PUBCHEM\"]\n",
    "with sdb.session() as session:\n",
    "    with session.begin_transaction() as tx:\n",
    "        for index, row in iMSMS_compounds_not_in_SPOKE_with_names_xref_df.iterrows():\n",
    "            id_list = list(row[[\"KEGG\", \"PUBCHEM\"]])\n",
    "            result = tx.run(query.format(id_list))\n",
    "            for row_ in result:\n",
    "                iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE.append((row[\"CHEM_ID\"], row[\"CHEMICAL_NAME\"], row[\"INCHIKEY\"], row_[\"n_id\"]))\n",
    "\n",
    "sdb.close()\n",
    "iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE_df = pd.DataFrame(iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE, columns=[\"CHEM_ID\", \"name\", \"spoke_identifer\", \"spoke_identifer_newly_mapped\"])\n",
    "\n",
    "iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE_df.to_csv(os.path.join(OUTPUT_PATH, \"iMSMS_compounds_KEGG_PUBCHEM_mapped_to_SPOKE.csv\"), index=False, header=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6839901",
   "metadata": {},
   "source": [
    "## Mapping to SPOKE based on synonyms"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0c43d3f",
   "metadata": {},
   "source": [
    "#### Note: We are considering the mapping based on xrefs, since that is an ID based mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "16a466ef",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 735 ms, sys: 63.8 ms, total: 798 ms\n",
      "Wall time: 49min 54s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "auth = basic_auth(SPOKE_USER, SPOKE_PASSWORD)\n",
    "sdb = GraphDatabase.driver(URI, auth=auth)\n",
    "\n",
    "query = \"\"\"\n",
    "            MATCH (n:Compound)\n",
    "            WHERE ANY(synonym IN n.synonyms WHERE toLower(synonym) = toLower(\"{}\"))\n",
    "            RETURN n.identifier as n_id\n",
    "        \"\"\"\n",
    "\n",
    "iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE = []\n",
    "with sdb.session() as session:\n",
    "    with session.begin_transaction() as tx:\n",
    "        for index, row in iMSMS_compounds_not_in_SPOKE_with_names_df.iterrows():\n",
    "            result = tx.run(query.format(row[\"name\"]))\n",
    "            for row_ in result:\n",
    "                iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE.append((row[\"CHEM_ID\"], row[\"name\"], row[\"spoke_identifer\"], row_[\"n_id\"]))\n",
    "\n",
    "sdb.close()\n",
    "iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE_df = pd.DataFrame(iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE, columns=[\"CHEM_ID\", \"name\", \"spoke_identifer\", \"spoke_identifer_newly_mapped\"])\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e530a06",
   "metadata": {},
   "source": [
    "## Amending the initial global_metabolomics_compound_spoke_map "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "1fdbd525",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_final_map_inchimap_group = data_final_map[~data_final_map.spoke_identifer.isin(iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE_df.spoke_identifer)]\n",
    "iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE_df_ = iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE_df.drop(\"spoke_identifer\", axis=1)\n",
    "iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE_df_ = iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE_df_.rename(columns={\"spoke_identifer_newly_mapped\":\"spoke_identifer\"})\n",
    "data_final_map_ = pd.concat([data_final_map_inchimap_group, iMSMS_compounds_not_in_SPOKE_mapped_to_SPOKE_df_], ignore_index=True)\n",
    "data_final_map_.to_csv(os.path.join(OUTPUT_PATH, \"global_metabolomics_compound_spoke_map.csv\"), index=False, header=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d9f1918",
   "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.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}