12.map-idc-compounds.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Map IDC Compounds to DrugBank\n",
    "\n",
    "[MicroSource International Drug Collection](http://www.msdiscovery.com/intdrug.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import logging\n",
    "\n",
    "import pandas\n",
    "import requests\n",
    "import requests_cache"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "requests_cache.install_cache(cache_name=\"requests-cache\", allowable_codes=[200, 404])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read IDC compounds from tamai screen spreadsheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>idc_idnumber</th>\n",
       "      <th>idc_plate</th>\n",
       "      <th>idc_position</th>\n",
       "      <th>idc_name</th>\n",
       "      <th>idc_structure</th>\n",
       "      <th>idc_formula</th>\n",
       "      <th>idc_moecularl_weight</th>\n",
       "      <th>idc_saltdata</th>\n",
       "      <th>idc_medical_use</th>\n",
       "      <th>idc_reference</th>\n",
       "      <th>idc_cas</th>\n",
       "      <th>idc_status</th>\n",
       "      <th>idc_etc</th>\n",
       "      <th>pharmakon_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1506107</td>\n",
       "      <td>151126-01</td>\n",
       "      <td>A02</td>\n",
       "      <td>SULFACARBAMIDE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C7H9N3O3S</td>\n",
       "      <td>215.23208</td>\n",
       "      <td>NaN</td>\n",
       "      <td>antibacterial</td>\n",
       "      <td>Antimicrob Agents Chemother 19:82 (1981)</td>\n",
       "      <td>547-44-4</td>\n",
       "      <td>synthetic</td>\n",
       "      <td>INN, BAN</td>\n",
       "      <td>Pharmakon1600-01506107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>210369</td>\n",
       "      <td>151126-01</td>\n",
       "      <td>A03</td>\n",
       "      <td>GALLIC ACID</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C7H6O5</td>\n",
       "      <td>170.12287</td>\n",
       "      <td>NaN</td>\n",
       "      <td>antineoplastic, astringent, antibacterial</td>\n",
       "      <td>J Chem Soc 1961:1854; Agric Biol Chem 30:617 (...</td>\n",
       "      <td>149-91-7</td>\n",
       "      <td>insect galls</td>\n",
       "      <td>INN, NF-VII</td>\n",
       "      <td>Pharmakon1600-00210369</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   idc_idnumber  idc_plate idc_position        idc_name  idc_structure  \\\n",
       "0       1506107  151126-01          A02  SULFACARBAMIDE            NaN   \n",
       "1        210369  151126-01          A03     GALLIC ACID            NaN   \n",
       "\n",
       "  idc_formula  idc_moecularl_weight  idc_saltdata  \\\n",
       "0   C7H9N3O3S             215.23208           NaN   \n",
       "1      C7H6O5             170.12287           NaN   \n",
       "\n",
       "                              idc_medical_use  \\\n",
       "0                               antibacterial   \n",
       "1  antineoplastic, astringent, antibacterial    \n",
       "\n",
       "                                       idc_reference   idc_cas    idc_status  \\\n",
       "0           Antimicrob Agents Chemother 19:82 (1981)  547-44-4     synthetic   \n",
       "1  J Chem Soc 1961:1854; Agric Biol Chem 30:617 (...  149-91-7  insect galls   \n",
       "\n",
       "       idc_etc            pharmakon_id  \n",
       "0     INN, BAN  Pharmakon1600-01506107  \n",
       "1  INN, NF-VII  Pharmakon1600-00210369  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idc_df = pandas.read_excel(\n",
    "    io=\"data/tamai-screen/IDC_Period_24-120.xlsx\",\n",
    "    sheet_name=\"MicroSource Discovery社311品目\",\n",
    ").add_prefix(\"idc_\")\n",
    "# fix messy column names\n",
    "idc_df.columns = idc_df.columns.str.strip().str.lower().str.replace(\" \", \"_\")\n",
    "# http://www.msdiscovery.com/pharmakon.html\n",
    "idc_df[\"pharmakon_id\"] = idc_df.idc_idnumber.map(\"Pharmakon1600-{:08d}\".format)\n",
    "idc_df.head(2)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Map IDC compounds to PubChem and Drugbank\n",
    "\n",
    "Uses the `Pharmakon1600-` style IDs for the [MicroSource Pharmakon Collection](http://www.msdiscovery.com/pharmakon.html), which includes the IDC. If this fails, fallback to plain name search.\n",
    "\n",
    "[PubChem](https://pubchempy.readthedocs.io/en/latest/guide/substance.html#retrieving-substances) has the Pharmakon identifiers deposited as substances. For example, [search PubChem](https://pubchem.ncbi.nlm.nih.gov/#query=Pharmakon1600-00300607) for `Pharmakon1600-00300607`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from utils.pubchem import query_pubchem_for_drugbank"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:root:pharmakon_id and idc_name lookup failed for BUTYLATED HYDROXYANISOLE\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 849 ms, sys: 80.4 ms, total: 929 ms\n",
      "Wall time: 928 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "310"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "rows = []\n",
    "for idc_compound in idc_df.itertuples():\n",
    "    for compound_name in [idc_compound.pharmakon_id, idc_compound.idc_name]:\n",
    "        row = query_pubchem_for_drugbank(compound_name)\n",
    "        if row:\n",
    "            break\n",
    "    else:\n",
    "        logging.warning(f\"pharmakon_id and idc_name lookup failed for {idc_compound.idc_name}\")\n",
    "        continue\n",
    "    row[\"idc_name\"] = idc_compound.idc_name\n",
    "    row[\"pharmakon_id\"] = idc_compound.pharmakon_id\n",
    "    rows.append(row)\n",
    "len(rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>pharmakon_id</th>\n",
       "      <th>idc_name</th>\n",
       "      <th>idc_plate</th>\n",
       "      <th>idc_position</th>\n",
       "      <th>query_compound</th>\n",
       "      <th>pubchem_cid</th>\n",
       "      <th>drugbank_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Pharmakon1600-01506107</td>\n",
       "      <td>SULFACARBAMIDE</td>\n",
       "      <td>151126-01</td>\n",
       "      <td>A02</td>\n",
       "      <td>Pharmakon1600-01506107</td>\n",
       "      <td>11033</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Pharmakon1600-00210369</td>\n",
       "      <td>GALLIC ACID</td>\n",
       "      <td>151126-01</td>\n",
       "      <td>A03</td>\n",
       "      <td>Pharmakon1600-00210369</td>\n",
       "      <td>370</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Pharmakon1600-00210866</td>\n",
       "      <td>KHELLIN</td>\n",
       "      <td>151126-01</td>\n",
       "      <td>A04</td>\n",
       "      <td>Pharmakon1600-00210866</td>\n",
       "      <td>3828</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Pharmakon1600-01503127</td>\n",
       "      <td>DEQUALINIUM CHLORIDE</td>\n",
       "      <td>151126-01</td>\n",
       "      <td>A05</td>\n",
       "      <td>Pharmakon1600-01503127</td>\n",
       "      <td>5351247</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pharmakon1600-00212064</td>\n",
       "      <td>HYDROXYTOLUIC ACID</td>\n",
       "      <td>151126-01</td>\n",
       "      <td>A06</td>\n",
       "      <td>Pharmakon1600-00212064</td>\n",
       "      <td>6738</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             pharmakon_id              idc_name  idc_plate idc_position  \\\n",
       "0  Pharmakon1600-01506107        SULFACARBAMIDE  151126-01          A02   \n",
       "1  Pharmakon1600-00210369           GALLIC ACID  151126-01          A03   \n",
       "2  Pharmakon1600-00210866               KHELLIN  151126-01          A04   \n",
       "3  Pharmakon1600-01503127  DEQUALINIUM CHLORIDE  151126-01          A05   \n",
       "4  Pharmakon1600-00212064    HYDROXYTOLUIC ACID  151126-01          A06   \n",
       "\n",
       "           query_compound  pubchem_cid drugbank_id  \n",
       "0  Pharmakon1600-01506107        11033        <NA>  \n",
       "1  Pharmakon1600-00210369          370        <NA>  \n",
       "2  Pharmakon1600-00210866         3828        <NA>  \n",
       "3  Pharmakon1600-01503127      5351247        <NA>  \n",
       "4  Pharmakon1600-00212064         6738        <NA>  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idc_map_df = (\n",
    "    idc_df\n",
    "    [[\"pharmakon_id\", \"idc_name\", \"idc_plate\", \"idc_position\"]]\n",
    "    .merge(pandas.DataFrame(rows), how=\"left\")\n",
    "    .convert_dtypes()\n",
    ")\n",
    "idc_map_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>pharmakon_id</th>\n",
       "      <th>idc_name</th>\n",
       "      <th>idc_plate</th>\n",
       "      <th>idc_position</th>\n",
       "      <th>query_compound</th>\n",
       "      <th>pubchem_cid</th>\n",
       "      <th>drugbank_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Pharmakon1600-00212151</td>\n",
       "      <td>CHLORQUINALDOL</td>\n",
       "      <td>151126-01</td>\n",
       "      <td>A07</td>\n",
       "      <td>Pharmakon1600-00212151</td>\n",
       "      <td>6301</td>\n",
       "      <td>DB13306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Pharmakon1600-00300546</td>\n",
       "      <td>BERGAPTEN</td>\n",
       "      <td>151126-01</td>\n",
       "      <td>A09</td>\n",
       "      <td>Pharmakon1600-00300546</td>\n",
       "      <td>2355</td>\n",
       "      <td>DB12216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Pharmakon1600-01502024</td>\n",
       "      <td>PIPEMIDIC ACID</td>\n",
       "      <td>151126-01</td>\n",
       "      <td>B03</td>\n",
       "      <td>Pharmakon1600-01502024</td>\n",
       "      <td>4831</td>\n",
       "      <td>DB13823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Pharmakon1600-00330012</td>\n",
       "      <td>COUMOPHOS</td>\n",
       "      <td>151126-01</td>\n",
       "      <td>B06</td>\n",
       "      <td>Pharmakon1600-00330012</td>\n",
       "      <td>2871</td>\n",
       "      <td>DB11390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Pharmakon1600-01506157</td>\n",
       "      <td>NIKETHAMIDE</td>\n",
       "      <td>151126-01</td>\n",
       "      <td>B07</td>\n",
       "      <td>Pharmakon1600-01506157</td>\n",
       "      <td>5497</td>\n",
       "      <td>DB13655</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              pharmakon_id        idc_name  idc_plate idc_position  \\\n",
       "5   Pharmakon1600-00212151  CHLORQUINALDOL  151126-01          A07   \n",
       "7   Pharmakon1600-00300546       BERGAPTEN  151126-01          A09   \n",
       "11  Pharmakon1600-01502024  PIPEMIDIC ACID  151126-01          B03   \n",
       "14  Pharmakon1600-00330012       COUMOPHOS  151126-01          B06   \n",
       "15  Pharmakon1600-01506157     NIKETHAMIDE  151126-01          B07   \n",
       "\n",
       "            query_compound  pubchem_cid drugbank_id  \n",
       "5   Pharmakon1600-00212151         6301     DB13306  \n",
       "7   Pharmakon1600-00300546         2355     DB12216  \n",
       "11  Pharmakon1600-01502024         4831     DB13823  \n",
       "14  Pharmakon1600-00330012         2871     DB11390  \n",
       "15  Pharmakon1600-01506157         5497     DB13655  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show only records that have drugbank_id\n",
    "idc_map_df.dropna(subset=[\"drugbank_id\"]).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>pharmakon_id</th>\n",
       "      <th>idc_name</th>\n",
       "      <th>idc_plate</th>\n",
       "      <th>idc_position</th>\n",
       "      <th>query_compound</th>\n",
       "      <th>pubchem_cid</th>\n",
       "      <th>drugbank_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>Pharmakon1600-01503740</td>\n",
       "      <td>BUTYLATED HYDROXYANISOLE</td>\n",
       "      <td>151126-03</td>\n",
       "      <td>B05</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               pharmakon_id                  idc_name  idc_plate idc_position  \\\n",
       "172  Pharmakon1600-01503740  BUTYLATED HYDROXYANISOLE  151126-03          B05   \n",
       "\n",
       "    query_compound  pubchem_cid drugbank_id  \n",
       "172           <NA>         <NA>        <NA>  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# IDC compounds not mapped to PubChem\n",
    "idc_map_df[idc_map_df.pubchem_cid.isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "273 compounds mapped to pubchem via pharmakon_id\n",
      "37 compounds mapped to pubchem via idc_name\n",
      "1 compounds failed to map to pubchem\n",
      "\n",
      "120 compounds mapped to DrugBank\n"
     ]
    }
   ],
   "source": [
    "print(f\"\"\"\n",
    "{(idc_map_df.query_compound == idc_map_df.pharmakon_id).sum():,} compounds mapped to pubchem via pharmakon_id\n",
    "{(idc_map_df.query_compound == idc_map_df.idc_name).sum():,} compounds mapped to pubchem via idc_name\n",
    "{sum(idc_map_df.pubchem_cid.isna()):,} compounds failed to map to pubchem\n",
    "\n",
    "{sum(idc_map_df.drugbank_id.notna()):,} compounds mapped to DrugBank\n",
    "\"\"\".strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "idc_map_df.to_csv(\"data/compounds/idc-to-pubchem.tsv\", sep=\"\\t\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Map compounds to DrugBank using MyChem.info\n",
    "\n",
    "Uses https://mychem.info/\n",
    "\n",
    "Alternative to the PubChem method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "from utils.mychem import query_mychem"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          SULFACARBAMIDE\n",
       "1             GALLIC ACID\n",
       "2                 KHELLIN\n",
       "3    DEQUALINIUM CHLORIDE\n",
       "4      HYDROXYTOLUIC ACID\n",
       "Name: idc_name, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idc_df.idc_name[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>_id</th>\n",
       "      <th>_score</th>\n",
       "      <th>query_compound</th>\n",
       "      <th>drugbank._license</th>\n",
       "      <th>drugbank.formula</th>\n",
       "      <th>drugbank.id</th>\n",
       "      <th>drugbank.name</th>\n",
       "      <th>drugbank.synonyms</th>\n",
       "      <th>drugbank.weight.average</th>\n",
       "      <th>drugbank.weight.monoisotopic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>JAONZGLTYYUPCT-UHFFFAOYSA-K</td>\n",
       "      <td>8.772476</td>\n",
       "      <td>GALLIC ACID</td>\n",
       "      <td>http://bit.ly/2PSfZTD</td>\n",
       "      <td>C7H5BiO6</td>\n",
       "      <td>DB13909</td>\n",
       "      <td>Bismuth subgallate</td>\n",
       "      <td>[basic bismuth 3,4,5-trihydroxybenzoate, basis...</td>\n",
       "      <td>394.091</td>\n",
       "      <td>393.989010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GPTXWRGISTZRIO-UHFFFAOYSA-N</td>\n",
       "      <td>14.978941</td>\n",
       "      <td>CHLORQUINALDOL</td>\n",
       "      <td>http://bit.ly/2PSfZTD</td>\n",
       "      <td>C10H7Cl2NO</td>\n",
       "      <td>DB13306</td>\n",
       "      <td>Chlorquinaldol</td>\n",
       "      <td>[2-methyl-5,7-dichloro-8-hydroxyquinoline, 5,7...</td>\n",
       "      <td>228.070</td>\n",
       "      <td>226.990469</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           _id     _score  query_compound  \\\n",
       "0  JAONZGLTYYUPCT-UHFFFAOYSA-K   8.772476     GALLIC ACID   \n",
       "1  GPTXWRGISTZRIO-UHFFFAOYSA-N  14.978941  CHLORQUINALDOL   \n",
       "\n",
       "       drugbank._license drugbank.formula drugbank.id       drugbank.name  \\\n",
       "0  http://bit.ly/2PSfZTD         C7H5BiO6     DB13909  Bismuth subgallate   \n",
       "1  http://bit.ly/2PSfZTD       C10H7Cl2NO     DB13306      Chlorquinaldol   \n",
       "\n",
       "                                   drugbank.synonyms  drugbank.weight.average  \\\n",
       "0  [basic bismuth 3,4,5-trihydroxybenzoate, basis...                  394.091   \n",
       "1  [2-methyl-5,7-dichloro-8-hydroxyquinoline, 5,7...                  228.070   \n",
       "\n",
       "   drugbank.weight.monoisotopic  \n",
       "0                    393.989010  \n",
       "1                    226.990469  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows = []\n",
    "for name in idc_df.idc_name:\n",
    "    response = query_mychem(name)\n",
    "    for hit in response.json()[\"hits\"]:\n",
    "        hit[\"query_compound\"] = name\n",
    "        rows.append(hit)\n",
    "query_df = pandas.json_normalize(rows)\n",
    "query_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "query_df = query_df.reindex(columns=[\"query_compound\", \"drugbank.id\", \"drugbank.name\", \"drugbank.formula\", \"drugbank.weight.average\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>idc_idnumber</th>\n",
       "      <th>idc_name</th>\n",
       "      <th>idc_formula</th>\n",
       "      <th>idc_moecularl_weight</th>\n",
       "      <th>query_compound</th>\n",
       "      <th>drugbank.id</th>\n",
       "      <th>drugbank.name</th>\n",
       "      <th>drugbank.formula</th>\n",
       "      <th>drugbank.weight.average</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>210369</td>\n",
       "      <td>GALLIC ACID</td>\n",
       "      <td>C7H6O5</td>\n",
       "      <td>170.12287</td>\n",
       "      <td>GALLIC ACID</td>\n",
       "      <td>DB13909</td>\n",
       "      <td>Bismuth subgallate</td>\n",
       "      <td>C7H5BiO6</td>\n",
       "      <td>394.091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>212151</td>\n",
       "      <td>CHLORQUINALDOL</td>\n",
       "      <td>C10H7Cl2NO</td>\n",
       "      <td>228.07939</td>\n",
       "      <td>CHLORQUINALDOL</td>\n",
       "      <td>DB13306</td>\n",
       "      <td>Chlorquinaldol</td>\n",
       "      <td>C10H7Cl2NO</td>\n",
       "      <td>228.070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>300546</td>\n",
       "      <td>BERGAPTEN</td>\n",
       "      <td>C12H8O4</td>\n",
       "      <td>216.19516</td>\n",
       "      <td>BERGAPTEN</td>\n",
       "      <td>DB12216</td>\n",
       "      <td>Bergapten</td>\n",
       "      <td>C12H8O4</td>\n",
       "      <td>216.192</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   idc_idnumber        idc_name idc_formula  idc_moecularl_weight  \\\n",
       "0        210369     GALLIC ACID      C7H6O5             170.12287   \n",
       "1        212151  CHLORQUINALDOL  C10H7Cl2NO             228.07939   \n",
       "2        300546       BERGAPTEN     C12H8O4             216.19516   \n",
       "\n",
       "   query_compound drugbank.id       drugbank.name drugbank.formula  \\\n",
       "0     GALLIC ACID     DB13909  Bismuth subgallate         C7H5BiO6   \n",
       "1  CHLORQUINALDOL     DB13306      Chlorquinaldol       C10H7Cl2NO   \n",
       "2       BERGAPTEN     DB12216           Bergapten          C12H8O4   \n",
       "\n",
       "   drugbank.weight.average  \n",
       "0                  394.091  \n",
       "1                  228.070  \n",
       "2                  216.192  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df = (\n",
    "    idc_df\n",
    "    .reindex(columns=[\"idc_idnumber\", \"idc_name\", \"idc_formula\", \"idc_moecularl_weight\"])\n",
    "    .merge(query_df, left_on=\"idc_name\", right_on=\"query_compound\")\n",
    ")\n",
    "merged_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merged_df.to_csv(\"data/compounds/idc-to-drugbank.tsv\", sep=\"\\t\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>idc_idnumber</th>\n",
       "      <th>idc_name</th>\n",
       "      <th>idc_formula</th>\n",
       "      <th>idc_moecularl_weight</th>\n",
       "      <th>query_compound</th>\n",
       "      <th>drugbank.id</th>\n",
       "      <th>drugbank.name</th>\n",
       "      <th>drugbank.formula</th>\n",
       "      <th>drugbank.weight.average</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>210369</td>\n",
       "      <td>GALLIC ACID</td>\n",
       "      <td>C7H6O5</td>\n",
       "      <td>170.12287</td>\n",
       "      <td>GALLIC ACID</td>\n",
       "      <td>DB13909</td>\n",
       "      <td>Bismuth subgallate</td>\n",
       "      <td>C7H5BiO6</td>\n",
       "      <td>394.0910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1500621</td>\n",
       "      <td>AMINOPYRINE</td>\n",
       "      <td>C13H17N3O</td>\n",
       "      <td>231.29994</td>\n",
       "      <td>AMINOPYRINE</td>\n",
       "      <td>DB01424</td>\n",
       "      <td>Aminophenazone</td>\n",
       "      <td>C13H17N3O</td>\n",
       "      <td>231.2936</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    idc_idnumber     idc_name idc_formula  idc_moecularl_weight  \\\n",
       "0         210369  GALLIC ACID      C7H6O5             170.12287   \n",
       "11       1500621  AMINOPYRINE   C13H17N3O             231.29994   \n",
       "\n",
       "   query_compound drugbank.id       drugbank.name drugbank.formula  \\\n",
       "0     GALLIC ACID     DB13909  Bismuth subgallate         C7H5BiO6   \n",
       "11    AMINOPYRINE     DB01424      Aminophenazone        C13H17N3O   \n",
       "\n",
       "    drugbank.weight.average  \n",
       "0                  394.0910  \n",
       "11                 231.2936  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "name_mismatch_df = merged_df[merged_df.idc_name.str.lower() != merged_df[\"drugbank.name\"].str.lower()]\n",
    "name_mismatch_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>idc_idnumber</th>\n",
       "      <th>idc_name</th>\n",
       "      <th>idc_formula</th>\n",
       "      <th>idc_moecularl_weight</th>\n",
       "      <th>query_compound</th>\n",
       "      <th>drugbank.id</th>\n",
       "      <th>drugbank.name</th>\n",
       "      <th>drugbank.formula</th>\n",
       "      <th>drugbank.weight.average</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>210369</td>\n",
       "      <td>GALLIC ACID</td>\n",
       "      <td>C7H6O5</td>\n",
       "      <td>170.12287</td>\n",
       "      <td>GALLIC ACID</td>\n",
       "      <td>DB13909</td>\n",
       "      <td>Bismuth subgallate</td>\n",
       "      <td>C7H5BiO6</td>\n",
       "      <td>394.0910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1501111</td>\n",
       "      <td>PROTOPORPHYRIN IX</td>\n",
       "      <td>C34H34N4O4</td>\n",
       "      <td>562.67448</td>\n",
       "      <td>PROTOPORPHYRIN IX</td>\n",
       "      <td>DB02949</td>\n",
       "      <td>2-Acetyl-Protoporphyrin Ix</td>\n",
       "      <td>C34H34FeN4O5</td>\n",
       "      <td>634.5030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1501173</td>\n",
       "      <td>ACETANILIDE</td>\n",
       "      <td>C8H9NO</td>\n",
       "      <td>135.16703</td>\n",
       "      <td>ACETANILIDE</td>\n",
       "      <td>DB01297</td>\n",
       "      <td>Practolol</td>\n",
       "      <td>C14H22N2O3</td>\n",
       "      <td>266.3361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>1506163</td>\n",
       "      <td>SELENOMETHIONINE</td>\n",
       "      <td>C5H11NO2Se</td>\n",
       "      <td>196.10892</td>\n",
       "      <td>SELENOMETHIONINE</td>\n",
       "      <td>DB09400</td>\n",
       "      <td>Selenomethionine Se-75</td>\n",
       "      <td>C5H11NO2Se</td>\n",
       "      <td>192.0710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>1503921</td>\n",
       "      <td>CYPROTERONE</td>\n",
       "      <td>C22H27ClO3</td>\n",
       "      <td>374.91169</td>\n",
       "      <td>CYPROTERONE</td>\n",
       "      <td>DB04839</td>\n",
       "      <td>Cyproterone acetate</td>\n",
       "      <td>C24H29ClO4</td>\n",
       "      <td>416.9380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>1503941</td>\n",
       "      <td>THIOCTIC ACID</td>\n",
       "      <td>C8H14O2S2</td>\n",
       "      <td>206.32758</td>\n",
       "      <td>THIOCTIC ACID</td>\n",
       "      <td>DB06253</td>\n",
       "      <td>Thioctic acid tromethamine</td>\n",
       "      <td>C12H25NO5S2</td>\n",
       "      <td>327.4500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>1502248</td>\n",
       "      <td>GLUTATHIONE</td>\n",
       "      <td>C10H17N3O6S</td>\n",
       "      <td>307.32749</td>\n",
       "      <td>GLUTATHIONE</td>\n",
       "      <td>DB03310</td>\n",
       "      <td>Glutathione disulfide</td>\n",
       "      <td>C20H32N6O12S2</td>\n",
       "      <td>612.6310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>1701028</td>\n",
       "      <td>CORTISONE</td>\n",
       "      <td>C21H28O5</td>\n",
       "      <td>360.45431</td>\n",
       "      <td>CORTISONE</td>\n",
       "      <td>DB01380</td>\n",
       "      <td>Cortisone acetate</td>\n",
       "      <td>C23H30O6</td>\n",
       "      <td>402.4807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>1503973</td>\n",
       "      <td>2-THIOURACIL</td>\n",
       "      <td>C4H4N2OS</td>\n",
       "      <td>128.15328</td>\n",
       "      <td>2-THIOURACIL</td>\n",
       "      <td>DB00550</td>\n",
       "      <td>Propylthiouracil</td>\n",
       "      <td>C7H10N2OS</td>\n",
       "      <td>170.2320</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     idc_idnumber           idc_name  idc_formula  idc_moecularl_weight  \\\n",
       "0          210369        GALLIC ACID       C7H6O5             170.12287   \n",
       "26        1501111  PROTOPORPHYRIN IX   C34H34N4O4             562.67448   \n",
       "32        1501173        ACETANILIDE       C8H9NO             135.16703   \n",
       "55        1506163   SELENOMETHIONINE   C5H11NO2Se             196.10892   \n",
       "84        1503921        CYPROTERONE   C22H27ClO3             374.91169   \n",
       "85        1503941      THIOCTIC ACID    C8H14O2S2             206.32758   \n",
       "100       1502248        GLUTATHIONE  C10H17N3O6S             307.32749   \n",
       "131       1701028          CORTISONE     C21H28O5             360.45431   \n",
       "138       1503973       2-THIOURACIL     C4H4N2OS             128.15328   \n",
       "\n",
       "        query_compound drugbank.id               drugbank.name  \\\n",
       "0          GALLIC ACID     DB13909          Bismuth subgallate   \n",
       "26   PROTOPORPHYRIN IX     DB02949  2-Acetyl-Protoporphyrin Ix   \n",
       "32         ACETANILIDE     DB01297                   Practolol   \n",
       "55    SELENOMETHIONINE     DB09400      Selenomethionine Se-75   \n",
       "84         CYPROTERONE     DB04839         Cyproterone acetate   \n",
       "85       THIOCTIC ACID     DB06253  Thioctic acid tromethamine   \n",
       "100        GLUTATHIONE     DB03310       Glutathione disulfide   \n",
       "131          CORTISONE     DB01380           Cortisone acetate   \n",
       "138       2-THIOURACIL     DB00550            Propylthiouracil   \n",
       "\n",
       "    drugbank.formula  drugbank.weight.average  \n",
       "0           C7H5BiO6                 394.0910  \n",
       "26      C34H34FeN4O5                 634.5030  \n",
       "32        C14H22N2O3                 266.3361  \n",
       "55        C5H11NO2Se                 192.0710  \n",
       "84        C24H29ClO4                 416.9380  \n",
       "85       C12H25NO5S2                 327.4500  \n",
       "100    C20H32N6O12S2                 612.6310  \n",
       "131         C23H30O6                 402.4807  \n",
       "138        C7H10N2OS                 170.2320  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# mismatched names with different weights\n",
    "name_mismatch_df[(name_mismatch_df[\"idc_moecularl_weight\"] - name_mismatch_df[\"drugbank.weight.average\"]).abs() > 1.0]"
   ]
  }
 ],
 "metadata": {
  "file_extension": ".py",
  "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.8.2"
  },
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "pygments_lexer": "ipython3",
  "version": 3
 },
 "nbformat": 4,
 "nbformat_minor": 4
}