05-xrefs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 05-xrefs\n",
    "Get xrefs from a variety of sources\n",
    "- Drugs: \n",
    "UMLS has mesh xrefs. From mesh, we can get UNII and CAS. From UNII_FDA, we can get inchikeys \n",
    "(lookup using cas or unii). From chembl, we can get chembl IDs from the inchikeys\n",
    "So: UMLS -> mesh -> unii/cas -> inchikey -> chembl\n",
    "insane, I know.\n",
    "- Anatomy: uberon has umls xrefs\n",
    "- disease: DO has umls, umls has NCI, ICD10PCS, SNOMEDCT_US, ICD10CM, OMIM\n",
    "- proteins: umls has uniprot xrefs\n",
    "- biological_process_or_activity/activity_and_behavior: umls has GO\n",
    "- gene: umls has HGNC and OMIM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "import os\n",
    "import pickle\n",
    "%matplotlib inline\n",
    "import pandas as pd\n",
    "from numpy import nan\n",
    "import seaborn as sns\n",
    "import shelve\n",
    "import re\n",
    "from collections import defaultdict, Counter\n",
    "from tqdm import tqdm\n",
    "from tqdm.notebook import tqdm as tqdm_notebook\n",
    "from itertools import chain\n",
    "from more_itertools import chunked\n",
    "from collections import Counter\n",
    "from pprint import pprint\n",
    "import requests\n",
    "from pyquery import PyQuery as pq\n",
    "from wikidataintegrator import wdi_helpers, wdi_core, wdi_login\n",
    "from semmed_biolink_environment import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "uri_to_curie = lambda s: s.split(\"/\")[-1].replace(\"_\", \":\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes = pd.read_csv(NODES_BIOLINK_TSV, sep='\\t', index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>LABEL</th>\n",
       "      <th>umls_type</th>\n",
       "      <th>umls_type_label</th>\n",
       "      <th>blm_category</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C0270951</th>\n",
       "      <td>Ocular muscular dystrophy</td>\n",
       "      <td>T047</td>\n",
       "      <td>Disease or Syndrome</td>\n",
       "      <td>DiseaseOrPhenotypicFeature</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C1706324</th>\n",
       "      <td>EREG wt Allele</td>\n",
       "      <td>T028</td>\n",
       "      <td>Gene or Genome</td>\n",
       "      <td>Gene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0075561</th>\n",
       "      <td>sulfochloranthine</td>\n",
       "      <td>T109|T121</td>\n",
       "      <td>Organic Chemical|Pharmacologic Substance</td>\n",
       "      <td>ChemicalSubstance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C3470895</th>\n",
       "      <td>LINC00607 gene</td>\n",
       "      <td>T028</td>\n",
       "      <td>Gene or Genome</td>\n",
       "      <td>Gene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0486433</th>\n",
       "      <td>Extended-spectrum beta lactamase</td>\n",
       "      <td>T126|T116</td>\n",
       "      <td>Amino Acid, Peptide, or Protein|Enzyme</td>\n",
       "      <td>Protein</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     LABEL  umls_type  \\\n",
       "ID                                                      \n",
       "C0270951         Ocular muscular dystrophy       T047   \n",
       "C1706324                    EREG wt Allele       T028   \n",
       "C0075561                 sulfochloranthine  T109|T121   \n",
       "C3470895                    LINC00607 gene       T028   \n",
       "C0486433  Extended-spectrum beta lactamase  T126|T116   \n",
       "\n",
       "                                   umls_type_label                blm_category  \n",
       "ID                                                                              \n",
       "C0270951                       Disease or Syndrome  DiseaseOrPhenotypicFeature  \n",
       "C1706324                            Gene or Genome                        Gene  \n",
       "C0075561  Organic Chemical|Pharmacologic Substance           ChemicalSubstance  \n",
       "C3470895                            Gene or Genome                        Gene  \n",
       "C0486433    Amino Acid, Peptide, or Protein|Enzyme                     Protein  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ChemicalSubstance              58700\n",
       "DiseaseOrPhenotypicFeature     36958\n",
       "Protein                        28019\n",
       "Gene                           23788\n",
       "GrossAnatomicalStructure        9086\n",
       "BiologicalProcessOrActivity     7665\n",
       "AnatomicalEntity                2757\n",
       "CellularComponent               1732\n",
       "Cell                            1255\n",
       "ActivityAndBehavior              881\n",
       "PhenotypicFeature                418\n",
       "GenomicEntity                    172\n",
       "Name: blm_category, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes.blm_category.value_counts()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "## used by xray\n",
    "AnatomicalEntity: uberon  # we can map through NCI\n",
    "BiologicalProcess: GO\n",
    "ChemicalSubstance: CHEMBL  # MSH RXNORM NDFRT DRUGBANK UNII\n",
    "Disease: DOID  # NCI, ICD10PCS, SNOMEDCT_US, ICD10CM, OMIM, \n",
    "MicroRNA: NCBIGene  # NCBI, HGNC\n",
    "Pathway: reactome  # map through go?\n",
    "PhenotypicFeature: HP  # HPO\n",
    "Protein: uniprot\n",
    "\n",
    "# we need:\n",
    "AnatomicalEntity:\n",
    "ChemicalSubstance\n",
    "Disease\n",
    "Genomic_entity # NCBI, HGNC\n",
    "biological_process\n",
    "\n",
    "# need to get externally:\n",
    "UBERON, CHEMBL, DOID, UNIPROT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## parse UMLS flat file to get all UMLS xrefs\n",
    "# see: https://www.ncbi.nlm.nih.gov/books/NBK9685/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/richard/opt/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3062: DtypeWarning: Columns (9,10,13) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    }
   ],
   "source": [
    "names = \"CUI,LAT,TS,LUI,STT,SUI,ISPREF,AUI,SAUI,SCUI,SDUI,SAB,TTY,CODE,STR,SRL,SUPPRESS,CVF,X\".split(\",\")\n",
    "umls = pd.read_csv(MRCONSO_ENG_ARCHIVE, delimiter=\"|\", names=names, index_col=None)\n",
    "# only get CUIs in our list of nodes\n",
    "umls = umls[umls.CUI.isin(nodes.index)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "umls['xref'] = umls.SAB + \":\" + umls.CODE.map(str)\n",
    "# easy fix to HGNC prefix duplication between SAB and CODE\n",
    "umls.xref = umls.xref.str.replace(\"HGNC:HGNC:\", \"HGNC:\")\n",
    "# fix this MSH MESH nonsense\n",
    "umls.xref = umls.xref.str.replace(\"MSH:\", \"MESH:\")\n",
    "# NCI_FDA is UNII\n",
    "umls.xref = umls.xref.str.replace(\"NCI_FDA:\", \"UNII:\")"
   ]
  },
  {
   "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>CUI</th>\n",
       "      <th>LAT</th>\n",
       "      <th>TS</th>\n",
       "      <th>LUI</th>\n",
       "      <th>STT</th>\n",
       "      <th>SUI</th>\n",
       "      <th>ISPREF</th>\n",
       "      <th>AUI</th>\n",
       "      <th>SAUI</th>\n",
       "      <th>SCUI</th>\n",
       "      <th>SDUI</th>\n",
       "      <th>SAB</th>\n",
       "      <th>TTY</th>\n",
       "      <th>CODE</th>\n",
       "      <th>STR</th>\n",
       "      <th>SRL</th>\n",
       "      <th>SUPPRESS</th>\n",
       "      <th>CVF</th>\n",
       "      <th>X</th>\n",
       "      <th>xref</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C0000039</td>\n",
       "      <td>ENG</td>\n",
       "      <td>P</td>\n",
       "      <td>L0000039</td>\n",
       "      <td>PF</td>\n",
       "      <td>S17175117</td>\n",
       "      <td>N</td>\n",
       "      <td>A28315139</td>\n",
       "      <td>9194921.0</td>\n",
       "      <td>1926948</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RXNORM</td>\n",
       "      <td>IN</td>\n",
       "      <td>1926948</td>\n",
       "      <td>1,2-dipalmitoylphosphatidylcholine</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>256.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RXNORM:1926948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>C0000039</td>\n",
       "      <td>ENG</td>\n",
       "      <td>P</td>\n",
       "      <td>L0000039</td>\n",
       "      <td>PF</td>\n",
       "      <td>S17175117</td>\n",
       "      <td>Y</td>\n",
       "      <td>A28572604</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MTH</td>\n",
       "      <td>PN</td>\n",
       "      <td>NOCODE</td>\n",
       "      <td>1,2-dipalmitoylphosphatidylcholine</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>256.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MTH:NOCODE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C0000039</td>\n",
       "      <td>ENG</td>\n",
       "      <td>P</td>\n",
       "      <td>L0000039</td>\n",
       "      <td>VC</td>\n",
       "      <td>S0007564</td>\n",
       "      <td>Y</td>\n",
       "      <td>A0016515</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M0023172</td>\n",
       "      <td>D015060</td>\n",
       "      <td>MSH</td>\n",
       "      <td>MH</td>\n",
       "      <td>D015060</td>\n",
       "      <td>1,2-Dipalmitoylphosphatidylcholine</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MESH:D015060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>C0000039</td>\n",
       "      <td>ENG</td>\n",
       "      <td>P</td>\n",
       "      <td>L0000039</td>\n",
       "      <td>VC</td>\n",
       "      <td>S1357296</td>\n",
       "      <td>Y</td>\n",
       "      <td>A1317708</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M0023172</td>\n",
       "      <td>D015060</td>\n",
       "      <td>MSH</td>\n",
       "      <td>PM</td>\n",
       "      <td>D015060</td>\n",
       "      <td>1,2 Dipalmitoylphosphatidylcholine</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MESH:D015060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>C0000039</td>\n",
       "      <td>ENG</td>\n",
       "      <td>S</td>\n",
       "      <td>L0000035</td>\n",
       "      <td>PF</td>\n",
       "      <td>S0007560</td>\n",
       "      <td>Y</td>\n",
       "      <td>A26674543</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M0023172</td>\n",
       "      <td>D015060</td>\n",
       "      <td>MSH</td>\n",
       "      <td>ET</td>\n",
       "      <td>D015060</td>\n",
       "      <td>1,2-Dihexadecyl-sn-Glycerophosphocholine</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MESH:D015060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>C0000039</td>\n",
       "      <td>ENG</td>\n",
       "      <td>S</td>\n",
       "      <td>L0000035</td>\n",
       "      <td>VO</td>\n",
       "      <td>S1357276</td>\n",
       "      <td>Y</td>\n",
       "      <td>A1317687</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M0023172</td>\n",
       "      <td>D015060</td>\n",
       "      <td>MSH</td>\n",
       "      <td>PM</td>\n",
       "      <td>D015060</td>\n",
       "      <td>1,2 Dihexadecyl sn Glycerophosphocholine</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MESH:D015060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>C0000039</td>\n",
       "      <td>ENG</td>\n",
       "      <td>S</td>\n",
       "      <td>L0000038</td>\n",
       "      <td>PF</td>\n",
       "      <td>S0007563</td>\n",
       "      <td>Y</td>\n",
       "      <td>A26661070</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M0023172</td>\n",
       "      <td>D015060</td>\n",
       "      <td>MSH</td>\n",
       "      <td>ET</td>\n",
       "      <td>D015060</td>\n",
       "      <td>1,2-Dipalmitoyl-Glycerophosphocholine</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MESH:D015060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>C0000039</td>\n",
       "      <td>ENG</td>\n",
       "      <td>S</td>\n",
       "      <td>L0000038</td>\n",
       "      <td>VO</td>\n",
       "      <td>S1357295</td>\n",
       "      <td>Y</td>\n",
       "      <td>A1317707</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M0023172</td>\n",
       "      <td>D015060</td>\n",
       "      <td>MSH</td>\n",
       "      <td>PM</td>\n",
       "      <td>D015060</td>\n",
       "      <td>1,2 Dipalmitoyl Glycerophosphocholine</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MESH:D015060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>C0000039</td>\n",
       "      <td>ENG</td>\n",
       "      <td>S</td>\n",
       "      <td>L0012507</td>\n",
       "      <td>PF</td>\n",
       "      <td>S0033298</td>\n",
       "      <td>N</td>\n",
       "      <td>A18399186</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LP15542-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LNC</td>\n",
       "      <td>LPN</td>\n",
       "      <td>LP15542-1</td>\n",
       "      <td>Dipalmitoylphosphatidylcholine</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>256.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LNC:LP15542-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>C0000039</td>\n",
       "      <td>ENG</td>\n",
       "      <td>S</td>\n",
       "      <td>L0012507</td>\n",
       "      <td>PF</td>\n",
       "      <td>S0033298</td>\n",
       "      <td>N</td>\n",
       "      <td>A22817493</td>\n",
       "      <td>166113012.0</td>\n",
       "      <td>102735002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SNOMEDCT_US</td>\n",
       "      <td>OAP</td>\n",
       "      <td>102735002</td>\n",
       "      <td>Dipalmitoylphosphatidylcholine</td>\n",
       "      <td>9</td>\n",
       "      <td>O</td>\n",
       "      <td>256.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SNOMEDCT_US:102735002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         CUI  LAT TS       LUI STT        SUI ISPREF        AUI         SAUI  \\\n",
       "2   C0000039  ENG  P  L0000039  PF  S17175117      N  A28315139    9194921.0   \n",
       "3   C0000039  ENG  P  L0000039  PF  S17175117      Y  A28572604          NaN   \n",
       "4   C0000039  ENG  P  L0000039  VC   S0007564      Y   A0016515          NaN   \n",
       "5   C0000039  ENG  P  L0000039  VC   S1357296      Y   A1317708          NaN   \n",
       "6   C0000039  ENG  S  L0000035  PF   S0007560      Y  A26674543          NaN   \n",
       "7   C0000039  ENG  S  L0000035  VO   S1357276      Y   A1317687          NaN   \n",
       "8   C0000039  ENG  S  L0000038  PF   S0007563      Y  A26661070          NaN   \n",
       "9   C0000039  ENG  S  L0000038  VO   S1357295      Y   A1317707          NaN   \n",
       "10  C0000039  ENG  S  L0012507  PF   S0033298      N  A18399186          NaN   \n",
       "11  C0000039  ENG  S  L0012507  PF   S0033298      N  A22817493  166113012.0   \n",
       "\n",
       "         SCUI     SDUI          SAB  TTY       CODE  \\\n",
       "2     1926948      NaN       RXNORM   IN    1926948   \n",
       "3         NaN      NaN          MTH   PN     NOCODE   \n",
       "4    M0023172  D015060          MSH   MH    D015060   \n",
       "5    M0023172  D015060          MSH   PM    D015060   \n",
       "6    M0023172  D015060          MSH   ET    D015060   \n",
       "7    M0023172  D015060          MSH   PM    D015060   \n",
       "8    M0023172  D015060          MSH   ET    D015060   \n",
       "9    M0023172  D015060          MSH   PM    D015060   \n",
       "10  LP15542-1      NaN          LNC  LPN  LP15542-1   \n",
       "11  102735002      NaN  SNOMEDCT_US  OAP  102735002   \n",
       "\n",
       "                                         STR  SRL SUPPRESS    CVF   X  \\\n",
       "2         1,2-dipalmitoylphosphatidylcholine    0        N  256.0 NaN   \n",
       "3         1,2-dipalmitoylphosphatidylcholine    0        N  256.0 NaN   \n",
       "4         1,2-Dipalmitoylphosphatidylcholine    0        N    NaN NaN   \n",
       "5         1,2 Dipalmitoylphosphatidylcholine    0        N    NaN NaN   \n",
       "6   1,2-Dihexadecyl-sn-Glycerophosphocholine    0        N    NaN NaN   \n",
       "7   1,2 Dihexadecyl sn Glycerophosphocholine    0        N    NaN NaN   \n",
       "8      1,2-Dipalmitoyl-Glycerophosphocholine    0        N    NaN NaN   \n",
       "9      1,2 Dipalmitoyl Glycerophosphocholine    0        N    NaN NaN   \n",
       "10            Dipalmitoylphosphatidylcholine    0        N  256.0 NaN   \n",
       "11            Dipalmitoylphosphatidylcholine    9        O  256.0 NaN   \n",
       "\n",
       "                     xref  \n",
       "2          RXNORM:1926948  \n",
       "3              MTH:NOCODE  \n",
       "4            MESH:D015060  \n",
       "5            MESH:D015060  \n",
       "6            MESH:D015060  \n",
       "7            MESH:D015060  \n",
       "8            MESH:D015060  \n",
       "9            MESH:D015060  \n",
       "10          LNC:LP15542-1  \n",
       "11  SNOMEDCT_US:102735002  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "umls.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'LNC:LP15542-1', 'MESH:D015060', 'RXNORM:1926948', 'LNC:MTHU010538', 'SNOMEDCT_US:102735002', 'MTH:NOCODE'}\n"
     ]
    }
   ],
   "source": [
    "XREF = dict(umls.groupby(\"CUI\")['xref'].apply(set))\n",
    "XREF = defaultdict(set, XREF)\n",
    "print(XREF['C0000039'])"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# all xrefs we get from umls\n",
    "# Counter(list(chain(*[list(map(lambda x:x.split(\":\",1)[0], y)) for y in XREF.values()])))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Chemicals and drugs"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "plan: UMLS has mesh xrefs. From mesh, we can get UNII and CAS. From UNII_FDA, we can get inchikeys \n",
    "(lookup using cas or unii). From chembl, we can get chembl IDs from the inchikeys\n",
    "So: UMLS -> mesh -> unii/cas -> inchikey -> chembl\n",
    "insane, I know."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "58700\n",
      "[('MESH', 51182),\n",
      " ('SNOMEDCT_US', 15435),\n",
      " ('NCI', 11047),\n",
      " ('RXNORM', 9143),\n",
      " ('CHV', 8331),\n",
      " ('MMSL', 8046),\n",
      " ('MTH', 7294),\n",
      " ('MTHSPL', 6649),\n",
      " ('UNII', 6573),\n",
      " ('NDDF', 6371),\n",
      " ('LNC', 5558),\n",
      " ('VANDF', 4040),\n",
      " ('DRUGBANK', 3496),\n",
      " ('GS', 3406),\n",
      " ('ATC', 3204),\n",
      " ('PDQ', 2965),\n",
      " ('MEDCIN', 2914),\n",
      " ('CSP', 2896),\n",
      " ('USP', 1887),\n",
      " ('LCH_NW', 1826),\n",
      " ('NCI_NCI-GLOSS', 1505),\n",
      " ('NCI_CTRP', 1007),\n",
      " ('USPMG', 939),\n",
      " ('NCI_DTP', 834),\n",
      " ('NCI_DCP', 734)]\n"
     ]
    }
   ],
   "source": [
    "# what xrefs are on chemicals?\n",
    "chem_umls = nodes[nodes.blm_category == \"ChemicalSubstance\"].index\n",
    "xref_chem = {k:v for k,v in XREF.items() if k in chem_umls}\n",
    "print(len(chem_umls))\n",
    "c = Counter(list(chain(*[list(map(lambda x:x.split(\":\",1)[0], y)) for y in xref_chem.values()])))\n",
    "pprint(c.most_common(25))\n",
    "# nearly all have a mesh ID. not much of anything else\n",
    "# neither mesh nor umls have inchikeys, or inchi, or smiles or anything useful for linking out\n",
    "# blech"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option(\"display.width\", 120)\n",
    "\n",
    "URL = \"http://id.nlm.nih.gov/mesh/sparql\"\n",
    "PREFIX = \"\"\"\n",
    "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>\n",
    "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
    "PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>\n",
    "PREFIX owl: <http://www.w3.org/2002/07/owl#>\n",
    "PREFIX meshv: <http://id.nlm.nih.gov/mesh/vocab#>\n",
    "PREFIX mesh: <http://id.nlm.nih.gov/mesh/>\n",
    "\"\"\"\n",
    "\n",
    "def sparql_query(query):\n",
    "    params = {'query': PREFIX + query, 'format': 'JSON', 'limit': 1000, 'offset': 0}\n",
    "    r = requests.get(URL, params=params)\n",
    "    res = [{k: v['value'] for k, v in x.items()} for x in r.json()['results']['bindings']]\n",
    "    t = tqdm()\n",
    "    while True:\n",
    "        t.update(1)\n",
    "        params['offset'] += 1000\n",
    "        r = requests.get(URL, params=params).json()['results']['bindings']\n",
    "        if not r:\n",
    "            break\n",
    "        res.extend([{k: v['value'] for k, v in x.items()} for x in r])\n",
    "    df = pd.DataFrame(res)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "218it [05:46,  1.84s/it]"
     ]
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT distinct ?mesh ?meshLabel ?r ?rr\n",
    "FROM <http://id.nlm.nih.gov/mesh> WHERE {\n",
    "  ?mesh meshv:active 1 .\n",
    "  ?mesh meshv:preferredMappedTo ?p .\n",
    "  ?p meshv:treeNumber ?treeNum .\n",
    "  FILTER(STRSTARTS(STR(?treeNum), \"http://id.nlm.nih.gov/mesh/D\")) .\n",
    "  ?mesh rdfs:label ?meshLabel .\n",
    "  ?mesh meshv:preferredConcept [meshv:registryNumber ?r] .\n",
    "  #OPTIONAL {?mesh meshv:preferredConcept [meshv:relatedRegistryNumber ?rr]}\n",
    "}\n",
    "\"\"\"\n",
    "df = sparql_query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.r = df.r.replace(\"0\", nan)\n",
    "df.dropna(subset=[\"r\"], inplace=True)\n",
    "df = df[~df.r.str.startswith(\"EC \")]\n",
    "df.mesh = df.mesh.str.replace(\"http://id.nlm.nih.gov/mesh/\", \"\")\n",
    "df.set_index(\"mesh\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>meshLabel</th>\n",
       "      <th>r</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mesh</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C000603935</th>\n",
       "      <td>aripiprazole lauroxil</td>\n",
       "      <td>B786J7A343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C487637</th>\n",
       "      <td>telavancin</td>\n",
       "      <td>XK134822Z0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C070576</th>\n",
       "      <td>PML protein, human</td>\n",
       "      <td>143220-95-5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C100708</th>\n",
       "      <td>oritavancin</td>\n",
       "      <td>PUG62FRZ2E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C000603533</th>\n",
       "      <td>3-methyl-3-buten-1-ol</td>\n",
       "      <td>KJ25C8CPFA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        meshLabel            r\n",
       "mesh                                          \n",
       "C000603935  aripiprazole lauroxil   B786J7A343\n",
       "C487637                telavancin   XK134822Z0\n",
       "C070576        PML protein, human  143220-95-5\n",
       "C100708               oritavancin   PUG62FRZ2E\n",
       "C000603533  3-methyl-3-buten-1-ol   KJ25C8CPFA"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_csv(MESH_XREFS_TSV, sep='\\t')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "39652"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mesh_xrefs = pd.read_csv(MESH_XREFS_TSV, sep='\\t', index_col=0)\n",
    "mesh_xrefs.r = mesh_xrefs.r.apply(lambda x: \"CAS:\" + x if \"-\" in x else \"UNII:\" + x)\n",
    "mesh_xrefs = mesh_xrefs.groupby(\"mesh\").r.apply(set).to_dict()\n",
    "mesh_xrefs = {\"MESH:\"+k:v for k,v in mesh_xrefs.items()}\n",
    "len(mesh_xrefs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "for k,v in xref_chem.items():\n",
    "    for vv in list(v):\n",
    "        if vv in mesh_xrefs:\n",
    "            v.update(mesh_xrefs[vv])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# download: 'http://fdasis.nlm.nih.gov/srs/download/srs/UNII_Data.zip'\n",
    "unii_df = pd.read_csv(UNII_RECORDS, dtype=str, sep='\\t', low_memory=False)\n",
    "unii_df.dropna(subset=['INCHIKEY'], inplace=True)"
   ]
  },
  {
   "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>UNII</th>\n",
       "      <th>PT</th>\n",
       "      <th>RN</th>\n",
       "      <th>EC</th>\n",
       "      <th>NCIT</th>\n",
       "      <th>RXCUI</th>\n",
       "      <th>PUBCHEM</th>\n",
       "      <th>ITIS</th>\n",
       "      <th>NCBI</th>\n",
       "      <th>PLANTS</th>\n",
       "      <th>GRIN</th>\n",
       "      <th>MPNS</th>\n",
       "      <th>INN_ID</th>\n",
       "      <th>MF</th>\n",
       "      <th>INCHIKEY</th>\n",
       "      <th>SMILES</th>\n",
       "      <th>INGREDIENT_TYPE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00174624E2</td>\n",
       "      <td>CHF-6333 CATION</td>\n",
       "      <td>1613620-10-2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76285164</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C27H28F3N6O3</td>\n",
       "      <td>IHTRPSMRGYWUIM-HSZRJFAPSA-O</td>\n",
       "      <td>COC(=O)C1=C(C)N(C2=NNC(=O)N2[C@@H]1C3=CC=C(C=C...</td>\n",
       "      <td>IONIC MOIETY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0129526470</td>\n",
       "      <td>5,8-DIMETHOXY(1,2,4)TRIAZOLO(1,5-C)PYRIMIDIN-2...</td>\n",
       "      <td>219715-62-5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11446888</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C7H9N5O2</td>\n",
       "      <td>DBJPBHJHAPAUQU-UHFFFAOYSA-N</td>\n",
       "      <td>COC1=CN=C(OC)N2N=C(N)N=C12</td>\n",
       "      <td>INGREDIENT SUBSTANCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0377415922</td>\n",
       "      <td>N-DESMETHYLVENLAFAXINE, (S)-</td>\n",
       "      <td>392332-59-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9860056</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C16H25NO2</td>\n",
       "      <td>MKAFOJAJJMUXLW-OAHLLOKOSA-N</td>\n",
       "      <td>CNC[C@H](C1=CC=C(OC)C=C1)C2(O)CCCCC2</td>\n",
       "      <td>INGREDIENT SUBSTANCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0480546720</td>\n",
       "      <td>HOMOCYCLOLEUCINE HYDROCHLORIDE</td>\n",
       "      <td>39692-17-6</td>\n",
       "      <td>254-594-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2724466</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C7H13NO2.ClH</td>\n",
       "      <td>GTKXSYHXQSKWNP-UHFFFAOYSA-N</td>\n",
       "      <td>Cl.NC1(CCCCC1)C(O)=O</td>\n",
       "      <td>INGREDIENT SUBSTANCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0503177591</td>\n",
       "      <td>SUCCINALDEHYDE</td>\n",
       "      <td>638-37-9</td>\n",
       "      <td>211-333-8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12524</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C4H6O2</td>\n",
       "      <td>PCSMJKASWLYICJ-UHFFFAOYSA-N</td>\n",
       "      <td>O=CCCC=O</td>\n",
       "      <td>INGREDIENT SUBSTANCE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         UNII                                                 PT            RN         EC NCIT RXCUI   PUBCHEM ITIS  \\\n",
       "0  00174624E2                                    CHF-6333 CATION  1613620-10-2        NaN  NaN   NaN  76285164  NaN   \n",
       "1  0129526470  5,8-DIMETHOXY(1,2,4)TRIAZOLO(1,5-C)PYRIMIDIN-2...   219715-62-5        NaN  NaN   NaN  11446888  NaN   \n",
       "3  0377415922                       N-DESMETHYLVENLAFAXINE, (S)-   392332-59-1        NaN  NaN   NaN   9860056  NaN   \n",
       "5  0480546720                     HOMOCYCLOLEUCINE HYDROCHLORIDE    39692-17-6  254-594-3  NaN   NaN   2724466  NaN   \n",
       "6  0503177591                                     SUCCINALDEHYDE      638-37-9  211-333-8  NaN   NaN     12524  NaN   \n",
       "\n",
       "  NCBI PLANTS GRIN MPNS INN_ID            MF                     INCHIKEY  \\\n",
       "0  NaN    NaN  NaN  NaN    NaN  C27H28F3N6O3  IHTRPSMRGYWUIM-HSZRJFAPSA-O   \n",
       "1  NaN    NaN  NaN  NaN    NaN      C7H9N5O2  DBJPBHJHAPAUQU-UHFFFAOYSA-N   \n",
       "3  NaN    NaN  NaN  NaN    NaN     C16H25NO2  MKAFOJAJJMUXLW-OAHLLOKOSA-N   \n",
       "5  NaN    NaN  NaN  NaN    NaN  C7H13NO2.ClH  GTKXSYHXQSKWNP-UHFFFAOYSA-N   \n",
       "6  NaN    NaN  NaN  NaN    NaN        C4H6O2  PCSMJKASWLYICJ-UHFFFAOYSA-N   \n",
       "\n",
       "                                              SMILES       INGREDIENT_TYPE  \n",
       "0  COC(=O)C1=C(C)N(C2=NNC(=O)N2[C@@H]1C3=CC=C(C=C...          IONIC MOIETY  \n",
       "1                         COC1=CN=C(OC)N2N=C(N)N=C12  INGREDIENT SUBSTANCE  \n",
       "3               CNC[C@H](C1=CC=C(OC)C=C1)C2(O)CCCCC2  INGREDIENT SUBSTANCE  \n",
       "5                               Cl.NC1(CCCCC1)C(O)=O  INGREDIENT SUBSTANCE  \n",
       "6                                           O=CCCC=O  INGREDIENT SUBSTANCE  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unii_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "cfa5204a2baf4e7d858343c688f8671a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=58700.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "n=0\n",
    "for k,v in tqdm_notebook(xref_chem.items()):\n",
    "    for vv in list(v):\n",
    "        if vv.startswith(\"UNII:\"):\n",
    "            xref = vv.replace(\"UNII:\", \"\")\n",
    "            s = unii_df.query(\"UNII == @xref\").INCHIKEY\n",
    "            if not s.empty:\n",
    "                n+=1\n",
    "                v.add(\"INCHIKEY:\" + list(s)[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10290\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('C0000248', 'CZIHNRWJTSTCEX-UHFFFAOYSA-N'),\n",
       " ('C0000294', 'XOGTZOOQQBDUSI-UHFFFAOYSA-M'),\n",
       " ('C0000378', 'QXWYKJLNLSIPIN-JGVFFNPUSA-N'),\n",
       " ('C0000399', 'CBOKZNLSFMZJJA-PEBGCTIMSA-N')]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xref_inchi = {k:v for k,v in xref_chem.items() if any(vv.startswith(\"INCHIKEY:\") for vv in v)}\n",
    "xref_inchi = {k:[vv for vv in v if vv.startswith(\"INCHIKEY:\")][0].replace(\"INCHIKEY:\", \"\") for k,v in xref_inchi.items()}\n",
    "print(len(xref_inchi))\n",
    "list(xref_inchi.items())[:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "  0%|          | 0/102.9 [00:00<?, ?it/s]\u001b[A\n",
      "  1%|          | 1/102.9 [00:07<12:53,  7.59s/it]\u001b[A\n",
      "  2%|▏         | 2/102.9 [00:11<10:58,  6.53s/it]\u001b[A\n",
      "  3%|▎         | 3/102.9 [00:15<09:44,  5.85s/it]\u001b[A\n",
      "  4%|▍         | 4/102.9 [00:19<08:26,  5.12s/it]\u001b[A\n",
      "  5%|▍         | 5/102.9 [00:23<07:46,  4.77s/it]\u001b[A\n",
      "  6%|▌         | 6/102.9 [00:26<07:00,  4.34s/it]\u001b[A\n",
      "  7%|▋         | 7/102.9 [00:29<06:24,  4.01s/it]\u001b[A\n",
      "  8%|▊         | 8/102.9 [00:33<05:56,  3.76s/it]\u001b[A\n",
      "  9%|▊         | 9/102.9 [00:36<05:40,  3.63s/it]\u001b[A\n",
      " 10%|▉         | 10/102.9 [00:39<05:35,  3.61s/it]\u001b[A\n",
      " 11%|█         | 11/102.9 [00:43<05:21,  3.50s/it]\u001b[A\n",
      " 12%|█▏        | 12/102.9 [00:46<05:16,  3.48s/it]\u001b[A\n",
      " 13%|█▎        | 13/102.9 [00:49<05:06,  3.41s/it]\u001b[A\n",
      " 14%|█▎        | 14/102.9 [00:55<06:06,  4.13s/it]\u001b[A\n",
      " 15%|█▍        | 15/102.9 [00:58<05:41,  3.88s/it]\u001b[A\n",
      " 16%|█▌        | 16/102.9 [01:02<05:19,  3.67s/it]\u001b[A\n",
      " 17%|█▋        | 17/102.9 [01:05<04:58,  3.48s/it]\u001b[A\n",
      " 17%|█▋        | 18/102.9 [01:08<04:47,  3.39s/it]\u001b[A\n",
      " 18%|█▊        | 19/102.9 [01:11<04:47,  3.42s/it]\u001b[A\n",
      " 19%|█▉        | 20/102.9 [01:14<04:29,  3.25s/it]\u001b[A\n",
      " 20%|██        | 21/102.9 [01:18<04:29,  3.29s/it]\u001b[A\n",
      " 21%|██▏       | 22/102.9 [01:23<05:06,  3.79s/it]\u001b[A\n",
      " 22%|██▏       | 23/102.9 [01:26<04:58,  3.74s/it]\u001b[A\n",
      " 23%|██▎       | 24/102.9 [01:30<04:52,  3.71s/it]\u001b[A\n",
      " 24%|██▍       | 25/102.9 [01:33<04:31,  3.48s/it]\u001b[A\n",
      " 25%|██▌       | 26/102.9 [01:36<04:13,  3.30s/it]\u001b[A\n",
      " 26%|██▌       | 27/102.9 [01:39<04:14,  3.35s/it]\u001b[A\n",
      " 27%|██▋       | 28/102.9 [01:42<04:05,  3.27s/it]\u001b[A\n",
      " 28%|██▊       | 29/102.9 [01:45<03:59,  3.24s/it]\u001b[A\n",
      " 29%|██▉       | 30/102.9 [01:49<03:54,  3.22s/it]\u001b[A\n",
      " 30%|███       | 31/102.9 [01:55<04:52,  4.07s/it]\u001b[A\n",
      " 31%|███       | 32/102.9 [01:58<04:39,  3.94s/it]\u001b[A\n",
      " 32%|███▏      | 33/102.9 [02:02<04:26,  3.81s/it]\u001b[A\n",
      " 33%|███▎      | 34/102.9 [02:05<04:08,  3.61s/it]\u001b[A\n",
      " 34%|███▍      | 35/102.9 [02:08<04:05,  3.61s/it]\u001b[A\n",
      " 35%|███▍      | 36/102.9 [02:12<03:57,  3.54s/it]\u001b[A\n",
      " 36%|███▌      | 37/102.9 [02:15<03:44,  3.41s/it]\u001b[A\n",
      " 37%|███▋      | 38/102.9 [02:18<03:39,  3.38s/it]\u001b[A\n",
      " 38%|███▊      | 39/102.9 [02:21<03:23,  3.18s/it]\u001b[A\n",
      " 39%|███▉      | 40/102.9 [02:24<03:17,  3.13s/it]\u001b[A\n",
      " 40%|███▉      | 41/102.9 [02:27<03:16,  3.17s/it]\u001b[A\n",
      " 41%|████      | 42/102.9 [02:31<03:16,  3.22s/it]\u001b[A\n",
      " 42%|████▏     | 43/102.9 [02:34<03:09,  3.16s/it]\u001b[A\n",
      " 43%|████▎     | 44/102.9 [02:37<03:06,  3.17s/it]\u001b[A\n",
      " 44%|████▎     | 45/102.9 [02:40<03:07,  3.24s/it]\u001b[A\n",
      " 45%|████▍     | 46/102.9 [02:43<02:54,  3.07s/it]\u001b[A\n",
      " 46%|████▌     | 47/102.9 [02:46<02:55,  3.14s/it]\u001b[A\n",
      " 47%|████▋     | 48/102.9 [02:49<02:48,  3.06s/it]\u001b[A\n",
      " 48%|████▊     | 49/102.9 [02:53<02:51,  3.18s/it]\u001b[A\n",
      " 49%|████▊     | 50/102.9 [02:55<02:44,  3.12s/it]\u001b[A\n",
      " 50%|████▉     | 51/102.9 [02:59<02:42,  3.13s/it]\u001b[A\n",
      " 51%|█████     | 52/102.9 [03:02<02:44,  3.24s/it]\u001b[A\n",
      " 52%|█████▏    | 53/102.9 [03:05<02:36,  3.14s/it]\u001b[A\n",
      " 52%|█████▏    | 54/102.9 [03:08<02:28,  3.04s/it]\u001b[A\n",
      " 53%|█████▎    | 55/102.9 [03:11<02:29,  3.13s/it]\u001b[A\n",
      " 54%|█████▍    | 56/102.9 [03:14<02:24,  3.09s/it]\u001b[A\n",
      " 55%|█████▌    | 57/102.9 [03:17<02:22,  3.10s/it]\u001b[A\n",
      " 56%|█████▋    | 58/102.9 [03:21<02:21,  3.16s/it]\u001b[A\n",
      " 57%|█████▋    | 59/102.9 [03:24<02:16,  3.11s/it]\u001b[A\n",
      " 58%|█████▊    | 60/102.9 [03:27<02:14,  3.14s/it]\u001b[A\n",
      " 59%|█████▉    | 61/102.9 [03:30<02:11,  3.13s/it]\u001b[A\n",
      " 60%|██████    | 62/102.9 [03:33<02:11,  3.23s/it]\u001b[A\n",
      " 61%|██████    | 63/102.9 [03:36<02:01,  3.04s/it]\u001b[A\n",
      " 62%|██████▏   | 64/102.9 [03:40<02:04,  3.19s/it]\u001b[A\n",
      " 63%|██████▎   | 65/102.9 [03:43<01:59,  3.16s/it]\u001b[A\n",
      " 64%|██████▍   | 66/102.9 [03:46<01:58,  3.21s/it]\u001b[A\n",
      " 65%|██████▌   | 67/102.9 [03:49<01:53,  3.17s/it]\u001b[A\n",
      " 66%|██████▌   | 68/102.9 [03:52<01:50,  3.16s/it]\u001b[A\n",
      " 67%|██████▋   | 69/102.9 [03:56<01:51,  3.28s/it]\u001b[A\n",
      " 68%|██████▊   | 70/102.9 [03:59<01:46,  3.24s/it]\u001b[A\n",
      " 69%|██████▉   | 71/102.9 [04:02<01:38,  3.07s/it]\u001b[A\n",
      " 70%|██████▉   | 72/102.9 [04:04<01:27,  2.84s/it]\u001b[A\n",
      " 71%|███████   | 73/102.9 [04:07<01:25,  2.87s/it]\u001b[A\n",
      " 72%|███████▏  | 74/102.9 [04:10<01:24,  2.93s/it]\u001b[A\n",
      " 73%|███████▎  | 75/102.9 [04:13<01:24,  3.01s/it]\u001b[A\n",
      " 74%|███████▍  | 76/102.9 [04:16<01:21,  3.03s/it]\u001b[A\n",
      " 75%|███████▍  | 77/102.9 [04:19<01:18,  3.03s/it]\u001b[A\n",
      " 76%|███████▌  | 78/102.9 [04:22<01:14,  2.98s/it]\u001b[A\n",
      " 77%|███████▋  | 79/102.9 [04:24<01:06,  2.77s/it]\u001b[A\n",
      " 78%|███████▊  | 80/102.9 [04:27<01:00,  2.62s/it]\u001b[A\n",
      " 79%|███████▊  | 81/102.9 [04:29<00:57,  2.64s/it]\u001b[A\n",
      " 80%|███████▉  | 82/102.9 [04:32<00:54,  2.61s/it]\u001b[A\n",
      " 81%|████████  | 83/102.9 [04:35<00:53,  2.68s/it]\u001b[A\n",
      " 82%|████████▏ | 84/102.9 [04:38<00:55,  2.96s/it]\u001b[A\n",
      " 83%|████████▎ | 85/102.9 [04:42<00:55,  3.09s/it]\u001b[A\n",
      " 84%|████████▎ | 86/102.9 [04:45<00:51,  3.06s/it]\u001b[A\n",
      " 85%|████████▍ | 87/102.9 [04:48<00:48,  3.07s/it]\u001b[A\n",
      " 86%|████████▌ | 88/102.9 [04:51<00:44,  3.01s/it]\u001b[A\n",
      " 86%|████████▋ | 89/102.9 [04:53<00:40,  2.89s/it]\u001b[A\n",
      " 87%|████████▋ | 90/102.9 [04:57<00:38,  3.01s/it]\u001b[A\n",
      " 88%|████████▊ | 91/102.9 [05:00<00:36,  3.10s/it]\u001b[A\n",
      " 89%|████████▉ | 92/102.9 [05:03<00:35,  3.23s/it]\u001b[A\n",
      " 90%|█████████ | 93/102.9 [05:09<00:38,  3.90s/it]\u001b[A\n",
      " 91%|█████████▏| 94/102.9 [05:12<00:33,  3.77s/it]\u001b[A\n",
      " 92%|█████████▏| 95/102.9 [05:15<00:28,  3.58s/it]\u001b[A\n",
      " 93%|█████████▎| 96/102.9 [05:19<00:24,  3.58s/it]\u001b[A\n",
      " 94%|█████████▍| 97/102.9 [05:23<00:21,  3.63s/it]\u001b[A\n",
      " 95%|█████████▌| 98/102.9 [05:26<00:17,  3.57s/it]\u001b[A\n",
      " 96%|█████████▌| 99/102.9 [05:32<00:16,  4.22s/it]\u001b[A\n",
      " 97%|█████████▋| 100/102.9 [05:36<00:11,  4.08s/it]\u001b[A\n",
      " 98%|█████████▊| 101/102.9 [05:39<00:07,  3.80s/it]\u001b[A\n",
      " 99%|█████████▉| 102/102.9 [05:42<00:03,  3.59s/it]\u001b[A\n",
      "100%|██████████| 103/102.9 [05:45<00:00,  3.36s/it]\u001b[A\n"
     ]
    }
   ],
   "source": [
    "url = \"https://www.ebi.ac.uk/chembl/api/data/molecule?molecule_structures__standard_inchi_key__in={}&format=json&limit=100\"\n",
    "for chunk in tqdm(chunked(xref_inchi.items(), 100), total=len(xref_inchi)/100):\n",
    "    chunk = dict(chunk)\n",
    "    chunk = {v:k for k,v in chunk.items()}\n",
    "    inchis = \",\".join(chunk)\n",
    "    mols = requests.get(url.format(inchis)).json()['molecules']\n",
    "    for m in mols:\n",
    "        chembl = m['molecule_chembl_id']\n",
    "        inchi = m['molecule_structures']['standard_inchi_key']\n",
    "        XREF[chunk[inchi]].add(\"CHEMBL:\" + chembl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8088"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len({k:v for k,v in XREF.items() if any(vv.startswith(\"CHEMBL:\") for vv in v)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(XREFS_SHELVE, 'wb') as f:\n",
    "    pickle.dump(XREF, f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## UBERON, uses uberon.csv generated from the uberon.owl file previously downloaded (see README)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>item</th>\n",
       "      <th>xref</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9755</th>\n",
       "      <td>UBERON:0007227</td>\n",
       "      <td>C0228757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11788</th>\n",
       "      <td>UBERON:0002965</td>\n",
       "      <td>C0175263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11853</th>\n",
       "      <td>UBERON:0001532</td>\n",
       "      <td>C0007276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11897</th>\n",
       "      <td>UBERON:0014636</td>\n",
       "      <td>C0228627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11904</th>\n",
       "      <td>UBERON:0002610</td>\n",
       "      <td>C0152411</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 item      xref\n",
       "9755   UBERON:0007227  C0228757\n",
       "11788  UBERON:0002965  C0175263\n",
       "11853  UBERON:0001532  C0007276\n",
       "11897  UBERON:0014636  C0228627\n",
       "11904  UBERON:0002610  C0152411"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(UBERON_CSV)\n",
    "df = df[df.xref.str.startswith(\"UMLS:\")]\n",
    "df.xref = df.xref.str.replace(\"UMLS:\", \"\")\n",
    "df.item = df.item.apply(uri_to_curie)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = df.groupby(\"xref\")['item'].apply(set)\n",
    "for umls, x in dict(s).items():\n",
    "    XREF[umls].update(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'MTH:NOCODE', 'SNOMEDCT_US:384765009', 'UBERON:0006472'}"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "XREF['C1272528']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DOID, uses doid.csv generated from the doi.owl file previously downloaded (see README)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>item</th>\n",
       "      <th>xref</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DOID:3864</td>\n",
       "      <td>C1332188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DOID:3864</td>\n",
       "      <td>C0278876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>DOID:699</td>\n",
       "      <td>C0162670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>DOID:0111831</td>\n",
       "      <td>C1844936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>DOID:0111618</td>\n",
       "      <td>C1853116</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            item      xref\n",
       "6      DOID:3864  C1332188\n",
       "7      DOID:3864  C0278876\n",
       "21      DOID:699  C0162670\n",
       "27  DOID:0111831  C1844936\n",
       "34  DOID:0111618  C1853116"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(DOID_CSV)\n",
    "df.dropna(inplace=True)\n",
    "df = df[df.xref.str.startswith(\"UMLS_CUI:\")]\n",
    "df.xref = df.xref.str.replace(\"UMLS_CUI:\", \"\")\n",
    "df.item = df.item.apply(uri_to_curie)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = df.groupby(\"xref\")['item'].apply(set)\n",
    "for umls, x in dict(s).items():\n",
    "    XREF[umls].update(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'DOID:1943',\n",
       " 'HPO:HP:0025470',\n",
       " 'ICD10:L65.0',\n",
       " 'ICD10AM:L65.0',\n",
       " 'ICD10CM:L65.0',\n",
       " 'ICD9CM:704.02',\n",
       " 'ICPC2ICD10ENG:MTHU025078',\n",
       " 'ICPC2ICD10ENG:MTHU073653',\n",
       " 'MDR:10043200',\n",
       " 'MEDCIN:37106',\n",
       " 'MTH:NOCODE',\n",
       " 'NCI:C112200',\n",
       " 'NCI_NICHD:C112200',\n",
       " 'SNOMEDCT_US:201147004',\n",
       " 'SNOMEDCT_US:39479004'}"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "XREF['C0263518']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CHV:0000041179', 'MESH:D000068298'}"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "XREF['C0591520']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## proteins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "  0%|          | 0/67.668372 [00:00<?, ?it/s]\u001b[A\n",
      "  1%|▏         | 1/67.668372 [00:03<03:23,  3.05s/it]\u001b[A/Users/richard/opt/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3254: DtypeWarning: Columns (1,2,5,7,10) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  if (await self.run_code(code, result,  async_=asy)):\n",
      "\n",
      "  3%|▎         | 2/67.668372 [00:06<03:20,  3.05s/it]\u001b[A\n",
      "  4%|▍         | 3/67.668372 [00:09<03:20,  3.10s/it]\u001b[A/Users/richard/opt/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3254: DtypeWarning: Columns (1,2,5,7) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  if (await self.run_code(code, result,  async_=asy)):\n",
      "\n",
      "  6%|▌         | 4/67.668372 [00:12<03:15,  3.07s/it]\u001b[A\n",
      "  7%|▋         | 5/67.668372 [00:15<03:15,  3.11s/it]\u001b[A\n",
      "  9%|▉         | 6/67.668372 [00:18<03:08,  3.06s/it]\u001b[A\n",
      " 10%|█         | 7/67.668372 [00:21<03:03,  3.03s/it]\u001b[A\n",
      " 12%|█▏        | 8/67.668372 [00:24<03:02,  3.05s/it]\u001b[A\n",
      " 13%|█▎        | 9/67.668372 [00:27<03:00,  3.08s/it]\u001b[A\n",
      " 15%|█▍        | 10/67.668372 [00:30<02:56,  3.06s/it]\u001b[A\n",
      " 16%|█▋        | 11/67.668372 [00:33<02:55,  3.10s/it]\u001b[A\n",
      " 18%|█▊        | 12/67.668372 [00:36<02:52,  3.09s/it]\u001b[A\n",
      " 19%|█▉        | 13/67.668372 [00:39<02:47,  3.06s/it]\u001b[A\n",
      " 21%|██        | 14/67.668372 [00:42<02:41,  3.00s/it]\u001b[A\n",
      " 22%|██▏       | 15/67.668372 [00:46<02:41,  3.06s/it]\u001b[A\n",
      " 24%|██▎       | 16/67.668372 [00:49<02:42,  3.15s/it]\u001b[A/Users/richard/opt/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3254: DtypeWarning: Columns (5) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  if (await self.run_code(code, result,  async_=asy)):\n",
      "\n",
      " 25%|██▌       | 17/67.668372 [00:52<02:41,  3.20s/it]\u001b[A\n",
      " 27%|██▋       | 18/67.668372 [00:55<02:39,  3.21s/it]\u001b[A\n",
      " 28%|██▊       | 19/67.668372 [00:59<02:37,  3.25s/it]\u001b[A\n",
      " 30%|██▉       | 20/67.668372 [01:02<02:32,  3.19s/it]\u001b[A\n",
      " 31%|███       | 21/67.668372 [01:05<02:33,  3.28s/it]\u001b[A\n",
      " 33%|███▎      | 22/67.668372 [01:09<02:29,  3.28s/it]\u001b[A\n",
      " 34%|███▍      | 23/67.668372 [01:12<02:30,  3.36s/it]\u001b[A\n",
      " 35%|███▌      | 24/67.668372 [01:16<02:27,  3.37s/it]\u001b[A\n",
      " 37%|███▋      | 25/67.668372 [01:19<02:30,  3.52s/it]\u001b[A\n",
      " 38%|███▊      | 26/67.668372 [01:22<02:19,  3.34s/it]\u001b[A\n",
      " 40%|███▉      | 27/67.668372 [01:25<02:13,  3.29s/it]\u001b[A\n",
      " 41%|████▏     | 28/67.668372 [01:29<02:09,  3.26s/it]\u001b[A\n",
      " 43%|████▎     | 29/67.668372 [01:32<02:07,  3.30s/it]\u001b[A\n",
      " 44%|████▍     | 30/67.668372 [01:35<02:01,  3.22s/it]\u001b[A\n",
      " 46%|████▌     | 31/67.668372 [01:38<01:58,  3.22s/it]\u001b[A\n",
      " 47%|████▋     | 32/67.668372 [01:42<01:59,  3.35s/it]\u001b[A/Users/richard/opt/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3254: DtypeWarning: Columns (5,7) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  if (await self.run_code(code, result,  async_=asy)):\n",
      "\n",
      " 49%|████▉     | 33/67.668372 [01:46<02:00,  3.49s/it]\u001b[A/Users/richard/opt/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3254: DtypeWarning: Columns (7) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  if (await self.run_code(code, result,  async_=asy)):\n",
      "\n",
      " 50%|█████     | 34/67.668372 [01:49<01:51,  3.32s/it]\u001b[A\n",
      " 52%|█████▏    | 35/67.668372 [01:52<01:44,  3.21s/it]\u001b[A\n",
      " 53%|█████▎    | 36/67.668372 [01:55<01:47,  3.38s/it]\u001b[A\n",
      " 55%|█████▍    | 37/67.668372 [02:00<01:49,  3.59s/it]\u001b[A\n",
      " 56%|█████▌    | 38/67.668372 [02:03<01:44,  3.51s/it]\u001b[A\n",
      " 58%|█████▊    | 39/67.668372 [02:06<01:40,  3.52s/it]\u001b[A\n",
      " 59%|█████▉    | 40/67.668372 [02:10<01:37,  3.54s/it]\u001b[A\n",
      " 61%|██████    | 41/67.668372 [02:14<01:34,  3.54s/it]\u001b[A\n",
      " 62%|██████▏   | 42/67.668372 [02:17<01:31,  3.58s/it]\u001b[A\n",
      " 64%|██████▎   | 43/67.668372 [02:21<01:31,  3.71s/it]\u001b[A\n",
      " 65%|██████▌   | 44/67.668372 [02:25<01:25,  3.62s/it]\u001b[A\n",
      " 67%|██████▋   | 45/67.668372 [02:28<01:18,  3.47s/it]\u001b[A\n",
      " 68%|██████▊   | 46/67.668372 [02:32<01:18,  3.63s/it]\u001b[A\n",
      " 69%|██████▉   | 47/67.668372 [02:35<01:12,  3.50s/it]\u001b[A\n",
      " 71%|███████   | 48/67.668372 [02:38<01:05,  3.35s/it]\u001b[A\n",
      " 72%|███████▏  | 49/67.668372 [02:41<01:03,  3.41s/it]\u001b[A/Users/richard/opt/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3254: DtypeWarning: Columns (1,2) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  if (await self.run_code(code, result,  async_=asy)):\n",
      "\n",
      " 74%|███████▍  | 50/67.668372 [02:45<00:58,  3.30s/it]\u001b[A\n",
      " 75%|███████▌  | 51/67.668372 [02:48<00:54,  3.29s/it]\u001b[A\n",
      " 77%|███████▋  | 52/67.668372 [02:51<00:53,  3.39s/it]\u001b[A\n",
      " 78%|███████▊  | 53/67.668372 [02:55<00:51,  3.48s/it]\u001b[A\n",
      " 80%|███████▉  | 54/67.668372 [02:59<00:47,  3.48s/it]\u001b[A\n",
      " 81%|████████▏ | 55/67.668372 [03:03<00:46,  3.63s/it]\u001b[A\n",
      " 83%|████████▎ | 56/67.668372 [03:06<00:42,  3.62s/it]\u001b[A\n",
      " 84%|████████▍ | 57/67.668372 [03:10<00:38,  3.64s/it]\u001b[A\n",
      " 86%|████████▌ | 58/67.668372 [03:13<00:33,  3.50s/it]\u001b[A\n",
      " 87%|████████▋ | 59/67.668372 [03:16<00:29,  3.35s/it]\u001b[A\n",
      " 89%|████████▊ | 60/67.668372 [03:19<00:24,  3.23s/it]\u001b[A\n",
      " 90%|█████████ | 61/67.668372 [03:22<00:21,  3.20s/it]\u001b[A\n",
      " 92%|█████████▏| 62/67.668372 [03:26<00:18,  3.27s/it]\u001b[A\n",
      " 93%|█████████▎| 63/67.668372 [03:29<00:15,  3.23s/it]\u001b[A\n",
      " 95%|█████████▍| 64/67.668372 [03:32<00:12,  3.33s/it]\u001b[A\n",
      " 96%|█████████▌| 65/67.668372 [03:35<00:08,  3.23s/it]\u001b[A\n",
      " 98%|█████████▊| 66/67.668372 [03:39<00:05,  3.32s/it]\u001b[A\n",
      " 99%|█████████▉| 67/67.668372 [03:42<00:02,  3.31s/it]\u001b[A\n",
      "/Users/richard/opt/anaconda3/lib/python3.8/site-packages/tqdm/std.py:480: TqdmWarning: clamping frac to range [0, 1]\n",
      "  full_bar = Bar(\n",
      "100%|██████████| 68/67.668372 [03:46<-1:59:59,  3.36s/it]\u001b[A\n",
      "69it [03:49,  3.41s/it]                                  \u001b[A\n",
      "70it [03:53,  3.46s/it]\u001b[A\n",
      "71it [03:56,  3.37s/it]\u001b[A\n",
      "72it [03:59,  3.28s/it]\u001b[A\n",
      "73it [04:02,  3.23s/it]\u001b[A\n",
      "74it [04:05,  3.18s/it]\u001b[A\n",
      "75it [04:08,  3.08s/it]\u001b[A\n",
      "76it [04:11,  3.13s/it]\u001b[A\n",
      "77it [04:14,  3.10s/it]\u001b[A\n",
      "78it [04:17,  3.01s/it]\u001b[A\n",
      "79it [04:20,  3.29s/it]\u001b[A\n"
     ]
    }
   ],
   "source": [
    "# See README regarding MRSAT_ARCHIVE\n",
    "names = list(\"abcdefghijklmn\")\n",
    "iter_csv = pd.read_csv(MRSAT_ARCHIVE, delimiter=\"|\", names=names, index_col=None, chunksize=1000000)\n",
    "chunks = []\n",
    "umls_uniprot = dict()\n",
    "for chunk in tqdm(iter_csv, total=67668372/1000000):\n",
    "    chunk.fillna(method='ffill', inplace=True)\n",
    "    chunk = chunk[chunk.i == \"SWISS_PROT\"]\n",
    "    d = dict(zip(chunk.a, chunk.k))\n",
    "    umls_uniprot.update(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4916"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(umls_uniprot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "for umls, uniprot in umls_uniprot.items():\n",
    "    XREF[umls].add(\"UNIPROT:\" + uniprot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'MESH:C081092',\n",
       " 'MTH:NOCODE',\n",
       " 'NCI:C127008',\n",
       " 'NCI_CTRP:C127008',\n",
       " 'UNIPROT:Q04756'}"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "XREF['C0215993']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(XREFS_SHELVE, 'wb') as f:\n",
    "    pickle.dump(XREF, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes['xrefs'] = nodes.index.map(lambda x: \";\".join(XREF.get(x,list())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>LABEL</th>\n",
       "      <th>umls_type</th>\n",
       "      <th>umls_type_label</th>\n",
       "      <th>blm_category</th>\n",
       "      <th>xrefs</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C0270951</th>\n",
       "      <td>Ocular muscular dystrophy</td>\n",
       "      <td>T047</td>\n",
       "      <td>Disease or Syndrome</td>\n",
       "      <td>DiseaseOrPhenotypicFeature</td>\n",
       "      <td>ICPC2ICD10ENG:MTHU051186;ICPC2ICD10ENG:MTHU053...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C1706324</th>\n",
       "      <td>EREG wt Allele</td>\n",
       "      <td>T028</td>\n",
       "      <td>Gene or Genome</td>\n",
       "      <td>Gene</td>\n",
       "      <td>MTH:NOCODE;NCI:C51422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0075561</th>\n",
       "      <td>sulfochloranthine</td>\n",
       "      <td>T109|T121</td>\n",
       "      <td>Organic Chemical|Pharmacologic Substance</td>\n",
       "      <td>ChemicalSubstance</td>\n",
       "      <td>CAS:55945-60-3;MESH:C022876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C3470895</th>\n",
       "      <td>LINC00607 gene</td>\n",
       "      <td>T028</td>\n",
       "      <td>Gene or Genome</td>\n",
       "      <td>Gene</td>\n",
       "      <td>MTH:NOCODE;HGNC:43944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0486433</th>\n",
       "      <td>Extended-spectrum beta lactamase</td>\n",
       "      <td>T126|T116</td>\n",
       "      <td>Amino Acid, Peptide, or Protein|Enzyme</td>\n",
       "      <td>Protein</td>\n",
       "      <td>LNC:LP17264-0;CHV:0000037238;SNOMEDCT_US:10276...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0393778</th>\n",
       "      <td>Congenital anosmia</td>\n",
       "      <td>T019</td>\n",
       "      <td>Congenital Abnormality</td>\n",
       "      <td>DiseaseOrPhenotypicFeature</td>\n",
       "      <td>OMIM:301700;SNOMEDCT_US:230502003;OMIM:107200;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0543506</th>\n",
       "      <td>clinical acidosis</td>\n",
       "      <td>T046</td>\n",
       "      <td>Pathologic Function</td>\n",
       "      <td>DiseaseOrPhenotypicFeature</td>\n",
       "      <td>CHV:0000038669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0596286</th>\n",
       "      <td>Cell Growth Regulation</td>\n",
       "      <td>T043</td>\n",
       "      <td>Cell Function</td>\n",
       "      <td>BiologicalProcessOrActivity</td>\n",
       "      <td>CSP:0602-2830;GO:GO:0001558;NCI:C18080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C3273836</th>\n",
       "      <td>MIA wt Allele</td>\n",
       "      <td>T028</td>\n",
       "      <td>Gene or Genome</td>\n",
       "      <td>Gene</td>\n",
       "      <td>MTH:NOCODE;NCI_CTRP:C98125;NCI:C98125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0214958</th>\n",
       "      <td>PD 140248</td>\n",
       "      <td>T109</td>\n",
       "      <td>Organic Chemical</td>\n",
       "      <td>ChemicalSubstance</td>\n",
       "      <td>MESH:C080527;CAS:147208-55-7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0387539</th>\n",
       "      <td>neoisoliquiritin, (E)-isomer</td>\n",
       "      <td>T109|T121</td>\n",
       "      <td>Organic Chemical|Pharmacologic Substance</td>\n",
       "      <td>ChemicalSubstance</td>\n",
       "      <td>INCHIKEY:YNWXJFQOCHMPCK-LXGDFETPSA-N;UNII:2Y34...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0439949</th>\n",
       "      <td>Tetrazine</td>\n",
       "      <td>T109</td>\n",
       "      <td>Organic Chemical</td>\n",
       "      <td>ChemicalSubstance</td>\n",
       "      <td>SNOMEDCT_US:255815005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C1281092</th>\n",
       "      <td>Entire culmen</td>\n",
       "      <td>T023</td>\n",
       "      <td>Body Part, Organ, or Organ Component</td>\n",
       "      <td>GrossAnatomicalStructure</td>\n",
       "      <td>MTH:NOCODE;SNOMEDCT_US:279384001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0151559</th>\n",
       "      <td>Central nervous system depression (disorder)</td>\n",
       "      <td>T047</td>\n",
       "      <td>Disease or Syndrome</td>\n",
       "      <td>DiseaseOrPhenotypicFeature</td>\n",
       "      <td>MDR:10009710;MDR:10007941;SNOMEDCT_US:41807200...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C1334538</th>\n",
       "      <td>MYH11 gene</td>\n",
       "      <td>T028</td>\n",
       "      <td>Gene or Genome</td>\n",
       "      <td>Gene</td>\n",
       "      <td>OMIM:160745;HGNC:7569;NCI_NCI-HGNC:7569;NCI_CT...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C1160610</th>\n",
       "      <td>collagen fibril organization</td>\n",
       "      <td>T042</td>\n",
       "      <td>Organ or Tissue Function</td>\n",
       "      <td>BiologicalProcessOrActivity</td>\n",
       "      <td>GO:GO:0030199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0771001</th>\n",
       "      <td>Acetomenaphthone</td>\n",
       "      <td>T109|T121</td>\n",
       "      <td>Organic Chemical|Pharmacologic Substance</td>\n",
       "      <td>ChemicalSubstance</td>\n",
       "      <td>CHEMBL:CHEMBL1479848;SNOMEDCT_US:714619003;UNI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0294261</th>\n",
       "      <td>procsanol P-268</td>\n",
       "      <td>T109|T121</td>\n",
       "      <td>Organic Chemical|Pharmacologic Substance</td>\n",
       "      <td>ChemicalSubstance</td>\n",
       "      <td>MESH:C092023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C0072299</th>\n",
       "      <td>prostaglandin-generating factor of anaphylaxis</td>\n",
       "      <td>T116</td>\n",
       "      <td>Amino Acid, Peptide, or Protein</td>\n",
       "      <td>Protein</td>\n",
       "      <td>MESH:C035516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C1416599</th>\n",
       "      <td>KCNK6 gene</td>\n",
       "      <td>T028</td>\n",
       "      <td>Gene or Genome</td>\n",
       "      <td>Gene</td>\n",
       "      <td>MTH:NOCODE;HGNC:6281;OMIM:603939</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   LABEL  umls_type                           umls_type_label  \\\n",
       "ID                                                                                                              \n",
       "C0270951                       Ocular muscular dystrophy       T047                       Disease or Syndrome   \n",
       "C1706324                                  EREG wt Allele       T028                            Gene or Genome   \n",
       "C0075561                               sulfochloranthine  T109|T121  Organic Chemical|Pharmacologic Substance   \n",
       "C3470895                                  LINC00607 gene       T028                            Gene or Genome   \n",
       "C0486433                Extended-spectrum beta lactamase  T126|T116    Amino Acid, Peptide, or Protein|Enzyme   \n",
       "C0393778                              Congenital anosmia       T019                    Congenital Abnormality   \n",
       "C0543506                               clinical acidosis       T046                       Pathologic Function   \n",
       "C0596286                          Cell Growth Regulation       T043                             Cell Function   \n",
       "C3273836                                   MIA wt Allele       T028                            Gene or Genome   \n",
       "C0214958                                       PD 140248       T109                          Organic Chemical   \n",
       "C0387539                    neoisoliquiritin, (E)-isomer  T109|T121  Organic Chemical|Pharmacologic Substance   \n",
       "C0439949                                       Tetrazine       T109                          Organic Chemical   \n",
       "C1281092                                   Entire culmen       T023      Body Part, Organ, or Organ Component   \n",
       "C0151559    Central nervous system depression (disorder)       T047                       Disease or Syndrome   \n",
       "C1334538                                      MYH11 gene       T028                            Gene or Genome   \n",
       "C1160610                    collagen fibril organization       T042                  Organ or Tissue Function   \n",
       "C0771001                                Acetomenaphthone  T109|T121  Organic Chemical|Pharmacologic Substance   \n",
       "C0294261                                 procsanol P-268  T109|T121  Organic Chemical|Pharmacologic Substance   \n",
       "C0072299  prostaglandin-generating factor of anaphylaxis       T116           Amino Acid, Peptide, or Protein   \n",
       "C1416599                                      KCNK6 gene       T028                            Gene or Genome   \n",
       "\n",
       "                         blm_category                                              xrefs  \n",
       "ID                                                                                        \n",
       "C0270951   DiseaseOrPhenotypicFeature  ICPC2ICD10ENG:MTHU051186;ICPC2ICD10ENG:MTHU053...  \n",
       "C1706324                         Gene                              MTH:NOCODE;NCI:C51422  \n",
       "C0075561            ChemicalSubstance                        CAS:55945-60-3;MESH:C022876  \n",
       "C3470895                         Gene                              MTH:NOCODE;HGNC:43944  \n",
       "C0486433                      Protein  LNC:LP17264-0;CHV:0000037238;SNOMEDCT_US:10276...  \n",
       "C0393778   DiseaseOrPhenotypicFeature  OMIM:301700;SNOMEDCT_US:230502003;OMIM:107200;...  \n",
       "C0543506   DiseaseOrPhenotypicFeature                                     CHV:0000038669  \n",
       "C0596286  BiologicalProcessOrActivity             CSP:0602-2830;GO:GO:0001558;NCI:C18080  \n",
       "C3273836                         Gene              MTH:NOCODE;NCI_CTRP:C98125;NCI:C98125  \n",
       "C0214958            ChemicalSubstance                       MESH:C080527;CAS:147208-55-7  \n",
       "C0387539            ChemicalSubstance  INCHIKEY:YNWXJFQOCHMPCK-LXGDFETPSA-N;UNII:2Y34...  \n",
       "C0439949            ChemicalSubstance                              SNOMEDCT_US:255815005  \n",
       "C1281092     GrossAnatomicalStructure                   MTH:NOCODE;SNOMEDCT_US:279384001  \n",
       "C0151559   DiseaseOrPhenotypicFeature  MDR:10009710;MDR:10007941;SNOMEDCT_US:41807200...  \n",
       "C1334538                         Gene  OMIM:160745;HGNC:7569;NCI_NCI-HGNC:7569;NCI_CT...  \n",
       "C1160610  BiologicalProcessOrActivity                                      GO:GO:0030199  \n",
       "C0771001            ChemicalSubstance  CHEMBL:CHEMBL1479848;SNOMEDCT_US:714619003;UNI...  \n",
       "C0294261            ChemicalSubstance                                       MESH:C092023  \n",
       "C0072299                      Protein                                       MESH:C035516  \n",
       "C1416599                         Gene                   MTH:NOCODE;HGNC:6281;OMIM:603939  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes.to_csv(NODES_XREF_TSV, sep='\\t')"
   ]
  },
  {
   "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.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}