UMLS-Graph-Extracts.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## UMLS-Graph Extract Scripts\n",
    "#### Assumes accessible Oracle database of UMLS Metathesauras and Semantic Network\n",
    "#### Generates a set of CSV files for ingest into neo4j"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import cx_Oracle\n",
    "import sqlalchemy\n",
    "\n",
    "pd.set_option('display.max_colwidth', None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Establish a connection - hide password in file in same directory\n",
    "##### conn_string.txt file contains one line like: oracle+cx_oracle://user:pass@server-address:port/database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "_ = open('conn_string.txt', 'r'); conn_string = _.read().replace('\\n',''); _.close()\n",
    "engine = sqlalchemy.create_engine(conn_string, arraysize=100000, max_identifier_length=128, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Set the UMLSversion (in Pitt/Neptune implementation this is the Oracle Schema/User)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "UMLSversion = 'UMLS2021AA'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TUIs.csv"
   ]
  },
  {
   "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>TUI:ID</th>\n",
       "      <th>name</th>\n",
       "      <th>STN</th>\n",
       "      <th>DEF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>T072</td>\n",
       "      <td>Physical Object</td>\n",
       "      <td>A1</td>\n",
       "      <td>An object perceptible to the sense of vision or touch.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>T114</td>\n",
       "      <td>Nucleic Acid, Nucleoside, or Nucleotide</td>\n",
       "      <td>A1.4.1.2.1.5</td>\n",
       "      <td>A complex compound of high molecular weight occurring in living cells. These are basically of two types, ribonucleic (RNA) and deoxyribonucleic (DNA) acids. Nucleic acids are made of nucleotides (nitrogen-containing base, a 5-carbon sugar, and one or more phosphate group) linked together by a phosphodiester bond between the 5' and 3' carbon atoms. Nucleosides are compounds composed of a purine or pyrimidine base (usually adenine, cytosine, guanine, thymine, uracil) linked to either a ribose or a deoxyribose sugar.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>T171</td>\n",
       "      <td>Language</td>\n",
       "      <td>A2.5</td>\n",
       "      <td>The system of communication used by a particular nation or people.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>T195</td>\n",
       "      <td>Antibiotic</td>\n",
       "      <td>A1.4.1.1.1.1</td>\n",
       "      <td>A pharmacologically active compound produced by growing microorganisms which kill or inhibit growth of other microorganisms.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>T201</td>\n",
       "      <td>Clinical Attribute</td>\n",
       "      <td>A2.3.1</td>\n",
       "      <td>An observable or measurable property or state of an organism of clinical interest.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    TUI:ID                                     name           STN  \\\n",
       "122   T072                          Physical Object            A1   \n",
       "123   T114  Nucleic Acid, Nucleoside, or Nucleotide  A1.4.1.2.1.5   \n",
       "124   T171                                 Language          A2.5   \n",
       "125   T195                               Antibiotic  A1.4.1.1.1.1   \n",
       "126   T201                       Clinical Attribute        A2.3.1   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         DEF  \n",
       "122                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   An object perceptible to the sense of vision or touch.  \n",
       "123  A complex compound of high molecular weight occurring in living cells. These are basically of two types, ribonucleic (RNA) and deoxyribonucleic (DNA) acids. Nucleic acids are made of nucleotides (nitrogen-containing base, a 5-carbon sugar, and one or more phosphate group) linked together by a phosphodiester bond between the 5' and 3' carbon atoms. Nucleosides are compounds composed of a purine or pyrimidine base (usually adenine, cytosine, guanine, thymine, uracil) linked to either a ribose or a deoxyribose sugar.  \n",
       "124                                                                                                                                                                                                                                                                                                                                                                                                                                                                       The system of communication used by a particular nation or people.  \n",
       "125                                                                                                                                                                                                                                                                                                                                                                                                             A pharmacologically active compound produced by growing microorganisms which kill or inhibit growth of other microorganisms.  \n",
       "126                                                                                                                                                                                                                                                                                                                                                                                                                                                       An observable or measurable property or state of an organism of clinical interest.  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT DISTINCT UI, STY_RL, STN_RTN, DEF FROM {0}.SRDEF WHERE RT = 'STY'\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns =['TUI:ID', 'name', 'STN', 'DEF']\n",
    "df.to_csv(path_or_buf='UMLS-Graph-Extracts/TUIs.csv', header=True, index=False)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TUIrel.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>:END_ID</th>\n",
       "      <th>:START_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>459</th>\n",
       "      <td>T051</td>\n",
       "      <td>T191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>460</th>\n",
       "      <td>T071</td>\n",
       "      <td>T194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>461</th>\n",
       "      <td>T121</td>\n",
       "      <td>T195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>462</th>\n",
       "      <td>T032</td>\n",
       "      <td>T201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463</th>\n",
       "      <td>T071</td>\n",
       "      <td>T204</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    :END_ID :START_ID\n",
       "459    T051      T191\n",
       "460    T071      T194\n",
       "461    T121      T195\n",
       "462    T032      T201\n",
       "463    T071      T204"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"WITH Semantics as (SELECT DISTINCT UI from {0}.SRDEF WHERE RT = 'STY') SELECT DISTINCT UI3, UI1 FROM {0}.SRSTRE1 INNER JOIN Semantics ON {0}.SRSTRE1.UI1 = Semantics.UI WHERE UI2 = 'T186'\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns =[':END_ID', ':START_ID']\n",
    "df.to_csv(path_or_buf='UMLS-Graph-Extracts/TUIrel.csv', header=True, index=False)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CUIs.csv"
   ]
  },
  {
   "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>CUI:ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4391077</th>\n",
       "      <td>C5441358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4391078</th>\n",
       "      <td>C5441392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4391079</th>\n",
       "      <td>C5441417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4391080</th>\n",
       "      <td>C5441495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4391081</th>\n",
       "      <td>C5441500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           CUI:ID\n",
       "4391077  C5441358\n",
       "4391078  C5441392\n",
       "4391079  C5441417\n",
       "4391080  C5441495\n",
       "4391081  C5441500"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT DISTINCT CUI from {0}.MRCONSO where {0}.MRCONSO.ISPREF = 'Y' AND {0}.MRCONSO.STT = 'PF' AND {0}.MRCONSO.TS = 'P' and {0}.MRCONSO.LAT = 'ENG'\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns =['CUI:ID']\n",
    "df.to_csv(path_or_buf='UMLS-Graph-Extracts/CUIs.csv', header=True, index=False)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CUI-TUIs.csv"
   ]
  },
  {
   "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>:START_ID</th>\n",
       "      <th>:END_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4713812</th>\n",
       "      <td>C5441259</td>\n",
       "      <td>T058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4713813</th>\n",
       "      <td>C5441278</td>\n",
       "      <td>T170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4713814</th>\n",
       "      <td>C5441370</td>\n",
       "      <td>T061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4713815</th>\n",
       "      <td>C5441383</td>\n",
       "      <td>T060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4713816</th>\n",
       "      <td>C5441403</td>\n",
       "      <td>T061</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        :START_ID :END_ID\n",
       "4713812  C5441259    T058\n",
       "4713813  C5441278    T170\n",
       "4713814  C5441370    T061\n",
       "4713815  C5441383    T060\n",
       "4713816  C5441403    T061"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT DISTINCT CUI, TUI FROM {0}.MRSTY\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns =[':START_ID',':END_ID']\n",
    "df.to_csv(path_or_buf='UMLS-Graph-Extracts/CUI-TUIs.csv', header=True, index=False)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CUI-CUIs.csv"
   ]
  },
  {
   "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>:START_ID</th>\n",
       "      <th>:END_ID</th>\n",
       "      <th>:TYPE</th>\n",
       "      <th>SAB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24892781</th>\n",
       "      <td>C5141051</td>\n",
       "      <td>C5441502</td>\n",
       "      <td>has_default_outpatient_classification</td>\n",
       "      <td>CCSR_ICD10CM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24892782</th>\n",
       "      <td>C5384648</td>\n",
       "      <td>C5441502</td>\n",
       "      <td>has_default_outpatient_classification</td>\n",
       "      <td>CCSR_ICD10CM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24892783</th>\n",
       "      <td>C0231528</td>\n",
       "      <td>C5441503</td>\n",
       "      <td>classified_as</td>\n",
       "      <td>CCSR_ICD10CM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24892784</th>\n",
       "      <td>C2895051</td>\n",
       "      <td>C5441503</td>\n",
       "      <td>has_default_outpatient_classification</td>\n",
       "      <td>CCSR_ICD10CM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24892785</th>\n",
       "      <td>C2896461</td>\n",
       "      <td>C5441503</td>\n",
       "      <td>has_default_outpatient_classification</td>\n",
       "      <td>CCSR_ICD10CM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         :START_ID   :END_ID                                  :TYPE  \\\n",
       "24892781  C5141051  C5441502  has_default_outpatient_classification   \n",
       "24892782  C5384648  C5441502  has_default_outpatient_classification   \n",
       "24892783  C0231528  C5441503                          classified_as   \n",
       "24892784  C2895051  C5441503  has_default_outpatient_classification   \n",
       "24892785  C2896461  C5441503  has_default_outpatient_classification   \n",
       "\n",
       "                   SAB  \n",
       "24892781  CCSR_ICD10CM  \n",
       "24892782  CCSR_ICD10CM  \n",
       "24892783  CCSR_ICD10CM  \n",
       "24892784  CCSR_ICD10CM  \n",
       "24892785  CCSR_ICD10CM  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"WITH SABlist as (SELECT DISTINCT SAB from {0}.MRCONSO where {0}.MRCONSO.LAT = 'ENG') SELECT DISTINCT CUI2, CUI1, NVL(RELA, REL), {0}.MRREL.SAB from {0}.MRREL inner join SABlist on {0}.MRREL.SAB = SABlist.SAB where {0}.MRREL.SUPPRESS <> 'O' and CUI1 <> CUI2 and REL <> 'SIB'\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns =[':START_ID',':END_ID',':TYPE','SAB']\n",
    "df.to_csv(path_or_buf='UMLS-Graph-Extracts/CUI-CUIs.csv', header=True, index=False)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CODEs.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>CodeID:ID</th>\n",
       "      <th>SAB</th>\n",
       "      <th>CODE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5129141</th>\n",
       "      <td>CCSR_ICD10PCS SKB</td>\n",
       "      <td>CCSR_ICD10PCS</td>\n",
       "      <td>SKB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5129142</th>\n",
       "      <td>CCSR_ICD10PCS SKB006</td>\n",
       "      <td>CCSR_ICD10PCS</td>\n",
       "      <td>SKB006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5129143</th>\n",
       "      <td>CCSR_ICD10PCS MST</td>\n",
       "      <td>CCSR_ICD10PCS</td>\n",
       "      <td>MST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5129144</th>\n",
       "      <td>CCSR_ICD10PCS PGN</td>\n",
       "      <td>CCSR_ICD10PCS</td>\n",
       "      <td>PGN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5129145</th>\n",
       "      <td>CCSR_ICD10CM MTHU000002</td>\n",
       "      <td>CCSR_ICD10CM</td>\n",
       "      <td>MTHU000002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       CodeID:ID            SAB        CODE\n",
       "5129141        CCSR_ICD10PCS SKB  CCSR_ICD10PCS         SKB\n",
       "5129142     CCSR_ICD10PCS SKB006  CCSR_ICD10PCS      SKB006\n",
       "5129143        CCSR_ICD10PCS MST  CCSR_ICD10PCS         MST\n",
       "5129144        CCSR_ICD10PCS PGN  CCSR_ICD10PCS         PGN\n",
       "5129145  CCSR_ICD10CM MTHU000002   CCSR_ICD10CM  MTHU000002"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"With CUIlist as (SELECT DISTINCT CUI from {0}.MRCONSO where {0}.MRCONSO.ISPREF = 'Y' AND {0}.MRCONSO.STT = 'PF' AND {0}.MRCONSO.TS = 'P' and {0}.MRCONSO.LAT = 'ENG') SELECT DISTINCT ({0}.MRCONSO.SAB||' '||{0}.MRCONSO.CODE), {0}.MRCONSO.SAB, {0}.MRCONSO.CODE from {0}.MRCONSO inner join CUIlist on {0}.MRCONSO.CUI = CUIlist.CUI where {0}.MRCONSO.LAT = 'ENG' and SUPPRESS <> 'O'\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns =['CodeID:ID','SAB','CODE']\n",
    "df.to_csv(path_or_buf='UMLS-Graph-Extracts/CODEs.csv', header=True, index=False)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CUI-CODEs.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>:START_ID</th>\n",
       "      <th>:END_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5678407</th>\n",
       "      <td>C5441031</td>\n",
       "      <td>SNOMEDCT_US 464271000124109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5678408</th>\n",
       "      <td>C5441114</td>\n",
       "      <td>SNOMEDCT_US 465381000124103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5678409</th>\n",
       "      <td>C5441378</td>\n",
       "      <td>CCSR_ICD10PCS SKB006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5678410</th>\n",
       "      <td>C5441417</td>\n",
       "      <td>CCSR_ICD10PCS FRS011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5678411</th>\n",
       "      <td>C5441420</td>\n",
       "      <td>CCSR_ICD10PCS FRS014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        :START_ID                      :END_ID\n",
       "5678407  C5441031  SNOMEDCT_US 464271000124109\n",
       "5678408  C5441114  SNOMEDCT_US 465381000124103\n",
       "5678409  C5441378         CCSR_ICD10PCS SKB006\n",
       "5678410  C5441417         CCSR_ICD10PCS FRS011\n",
       "5678411  C5441420         CCSR_ICD10PCS FRS014"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT DISTINCT CUI, (SAB||' '||CODE) FROM {0}.MRCONSO WHERE LAT = 'ENG' AND SUPPRESS <> 'O'\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns =[':START_ID',':END_ID']\n",
    "df.to_csv(path_or_buf='UMLS-Graph-Extracts/CUI-CODEs.csv', header=True, index=False)\n",
    "\n",
    "# Keep a copy for use by NDCs later\n",
    "CUI_CODEs = df.copy()\n",
    "\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SUIs.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>SUI:ID</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9322334</th>\n",
       "      <td>S20333456</td>\n",
       "      <td>Img hd abnml neuro exam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9322335</th>\n",
       "      <td>S20333923</td>\n",
       "      <td>Sars-cov-2 antb quantitative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9322336</th>\n",
       "      <td>S20335906</td>\n",
       "      <td>True Comfort Pro 33G 4mm Pen Needle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9322337</th>\n",
       "      <td>S20336233</td>\n",
       "      <td>hydrogen peroxide Oral Paste Product</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9322338</th>\n",
       "      <td>S20334892</td>\n",
       "      <td>CATHETER,INTERM,MAGIC3 18FR ROCH#50618</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            SUI:ID                                    name\n",
       "9322334  S20333456                 Img hd abnml neuro exam\n",
       "9322335  S20333923            Sars-cov-2 antb quantitative\n",
       "9322336  S20335906     True Comfort Pro 33G 4mm Pen Needle\n",
       "9322337  S20336233    hydrogen peroxide Oral Paste Product\n",
       "9322338  S20334892  CATHETER,INTERM,MAGIC3 18FR ROCH#50618"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT DISTINCT {0}.MRCONSO.SUI, {0}.MRCONSO.STR FROM {0}.MRCONSO WHERE {0}.MRCONSO.LAT = 'ENG'\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns =['SUI:ID','name']\n",
    "df.to_csv(path_or_buf='UMLS-Graph-Extracts/SUIs.csv', header=True, index=False)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CODE-SUIs.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>:END_ID</th>\n",
       "      <th>:START_ID</th>\n",
       "      <th>:TYPE</th>\n",
       "      <th>CUI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9802005</th>\n",
       "      <td>S20347760</td>\n",
       "      <td>SNOMEDCT_US 464711000124105</td>\n",
       "      <td>PT</td>\n",
       "      <td>C5441073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9802006</th>\n",
       "      <td>S20347733</td>\n",
       "      <td>SNOMEDCT_US 465391000124100</td>\n",
       "      <td>FN</td>\n",
       "      <td>C5441115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9802007</th>\n",
       "      <td>S20347947</td>\n",
       "      <td>SNOMEDCT_US 466091000124105</td>\n",
       "      <td>SY</td>\n",
       "      <td>C5441152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9802008</th>\n",
       "      <td>S20348023</td>\n",
       "      <td>SNOMEDCT_US 466711000124102</td>\n",
       "      <td>PT</td>\n",
       "      <td>C5441185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9802009</th>\n",
       "      <td>S20348974</td>\n",
       "      <td>CCSR_ICD10PCS ADM017</td>\n",
       "      <td>SP</td>\n",
       "      <td>C5441450</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           :END_ID                    :START_ID :TYPE       CUI\n",
       "9802005  S20347760  SNOMEDCT_US 464711000124105    PT  C5441073\n",
       "9802006  S20347733  SNOMEDCT_US 465391000124100    FN  C5441115\n",
       "9802007  S20347947  SNOMEDCT_US 466091000124105    SY  C5441152\n",
       "9802008  S20348023  SNOMEDCT_US 466711000124102    PT  C5441185\n",
       "9802009  S20348974         CCSR_ICD10PCS ADM017    SP  C5441450"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT DISTINCT SUI, (SAB||' '||CODE), TTY, CUI FROM {0}.MRCONSO WHERE LAT = 'ENG' AND SUPPRESS <> 'O'\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns =[':END_ID',':START_ID',':TYPE','CUI']\n",
    "df.to_csv(path_or_buf='UMLS-Graph-Extracts/CODE-SUIs.csv', header=True, index=False)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CUI-SUIs.csv"
   ]
  },
  {
   "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>:START_ID</th>\n",
       "      <th>:END_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4391077</th>\n",
       "      <td>C5441310</td>\n",
       "      <td>S20349104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4391078</th>\n",
       "      <td>C5441369</td>\n",
       "      <td>S20349041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4391079</th>\n",
       "      <td>C5441410</td>\n",
       "      <td>S20349101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4391080</th>\n",
       "      <td>C5441415</td>\n",
       "      <td>S20349056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4391081</th>\n",
       "      <td>C5441461</td>\n",
       "      <td>S20349159</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        :START_ID    :END_ID\n",
       "4391077  C5441310  S20349104\n",
       "4391078  C5441369  S20349041\n",
       "4391079  C5441410  S20349101\n",
       "4391080  C5441415  S20349056\n",
       "4391081  C5441461  S20349159"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT DISTINCT CUI, SUI FROM {0}.MRCONSO WHERE {0}.MRCONSO.ISPREF = 'Y' AND {0}.MRCONSO.STT = 'PF' AND {0}.MRCONSO.TS = 'P' and {0}.MRCONSO.LAT = 'ENG'\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns =[':START_ID',':END_ID']\n",
    "df.to_csv(path_or_buf='UMLS-Graph-Extracts/CUI-SUIs.csv', header=True, index=False)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DEFs.csv"
   ]
  },
  {
   "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>ATUI:ID</th>\n",
       "      <th>SAB</th>\n",
       "      <th>DEF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>335442</th>\n",
       "      <td>AT263079807</td>\n",
       "      <td>UMD</td>\n",
       "      <td>Consulting services that assist organizations in the development and review of alarm management programs.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335443</th>\n",
       "      <td>AT263079607</td>\n",
       "      <td>UMD</td>\n",
       "      <td>Consulting services that assist organizations in the management of energy and utility services.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335444</th>\n",
       "      <td>AT263078367</td>\n",
       "      <td>UMD</td>\n",
       "      <td>Facility and environmental purchased services that provide specialized cleaning for cubicle curtains.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335445</th>\n",
       "      <td>AT263078428</td>\n",
       "      <td>UMD</td>\n",
       "      <td>Devices used to expand pneumatic cuffs using compressed air. Pneumatic cuff inflators usually include a display screen, controls, pressure regulator, and tubing to connect to the cuff. The inflators typically regulate inflation to a preselected target pressure, maintain that pressure, and subsequently deflate the cuff. These devices may also have a timer for automatic inflation and deflation.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335446</th>\n",
       "      <td>AT263078714</td>\n",
       "      <td>UMD</td>\n",
       "      <td>Handheld devices used to remove a gastrostomy feeding tube. These removers are often sized to be used with specific length and diameter feeding tubes.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ATUI:ID  SAB  \\\n",
       "335442  AT263079807  UMD   \n",
       "335443  AT263079607  UMD   \n",
       "335444  AT263078367  UMD   \n",
       "335445  AT263078428  UMD   \n",
       "335446  AT263078714  UMD   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                                DEF  \n",
       "335442                                                                                                                                                                                                                                                                                                    Consulting services that assist organizations in the development and review of alarm management programs.  \n",
       "335443                                                                                                                                                                                                                                                                                                              Consulting services that assist organizations in the management of energy and utility services.  \n",
       "335444                                                                                                                                                                                                                                                                                                        Facility and environmental purchased services that provide specialized cleaning for cubicle curtains.  \n",
       "335445  Devices used to expand pneumatic cuffs using compressed air. Pneumatic cuff inflators usually include a display screen, controls, pressure regulator, and tubing to connect to the cuff. The inflators typically regulate inflation to a preselected target pressure, maintain that pressure, and subsequently deflate the cuff. These devices may also have a timer for automatic inflation and deflation.  \n",
       "335446                                                                                                                                                                                                                                                       Handheld devices used to remove a gastrostomy feeding tube. These removers are often sized to be used with specific length and diameter feeding tubes.  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"With CUIlist as (SELECT DISTINCT CUI from {0}.MRCONSO where {0}.MRCONSO.ISPREF = 'Y' AND {0}.MRCONSO.STT = 'PF' AND {0}.MRCONSO.TS = 'P' and {0}.MRCONSO.LAT = 'ENG') SELECT DISTINCT {0}.MRDEF.ATUI, {0}.MRDEF.SAB, ASCIISTR({0}.MRDEF.DEF) FROM {0}.MRDEF inner join CUIlist on {0}.MRDEF.CUI = CUIlist.CUI where SUPPRESS <> 'O' AND NOT (SAB LIKE 'MSH%' AND SAB <> 'MSH') AND NOT (SAB LIKE 'MDR%' AND SAB <> 'MDR')\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns =['ATUI:ID','SAB','DEF']\n",
    "df.to_csv(path_or_buf='UMLS-Graph-Extracts/DEFs.csv', header=True, index=False)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DEFrel.csv"
   ]
  },
  {
   "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>:END_ID</th>\n",
       "      <th>:START_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>392357</th>\n",
       "      <td>AT264688859</td>\n",
       "      <td>C5441051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392358</th>\n",
       "      <td>AT264485487</td>\n",
       "      <td>C5441060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392359</th>\n",
       "      <td>AT265006601</td>\n",
       "      <td>C5441061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392360</th>\n",
       "      <td>AT265051755</td>\n",
       "      <td>C5441062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392361</th>\n",
       "      <td>AT265006602</td>\n",
       "      <td>C5441082</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            :END_ID :START_ID\n",
       "392357  AT264688859  C5441051\n",
       "392358  AT264485487  C5441060\n",
       "392359  AT265006601  C5441061\n",
       "392360  AT265051755  C5441062\n",
       "392361  AT265006602  C5441082"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT DISTINCT ATUI, CUI FROM {0}.MRDEF WHERE SUPPRESS <> 'O'\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns =[':END_ID',':START_ID']\n",
    "df.to_csv(path_or_buf='UMLS-Graph-Extracts/DEFrel.csv', header=True, index=False)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Append NDCs to CODEs.csv and to CUI-CODEs.csv after merge to CUI_CODEs RXNORM CUIs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>:START_ID</th>\n",
       "      <th>:END_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>247540</th>\n",
       "      <td>C5241808</td>\n",
       "      <td>NDC 54771267002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247541</th>\n",
       "      <td>C5242119</td>\n",
       "      <td>NDC 71699015030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247542</th>\n",
       "      <td>C5383902</td>\n",
       "      <td>NDC 59676070260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247543</th>\n",
       "      <td>C5390492</td>\n",
       "      <td>NDC 79129000210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247544</th>\n",
       "      <td>C5398813</td>\n",
       "      <td>NDC 73184016901</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       :START_ID          :END_ID\n",
       "247540  C5241808  NDC 54771267002\n",
       "247541  C5242119  NDC 71699015030\n",
       "247542  C5383902  NDC 59676070260\n",
       "247543  C5390492  NDC 79129000210\n",
       "247544  C5398813  NDC 73184016901"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT DISTINCT ATV, (SAB||' '||CODE) FROM {0}.MRSAT WHERE SAB = 'RXNORM' and ATN = 'NDC' and SUPPRESS <> 'O'\".format(UMLSversion)\n",
    "df = pd.read_sql_query(query, engine)\n",
    "df.columns = ['CODE',':END_ID']\n",
    "df['SAB'] = 'NDC'\n",
    "df['CodeID:ID'] = df['SAB'] + \" \" + df['CODE']\n",
    "df[['CodeID:ID','SAB','CODE']].to_csv('UMLS-Graph-Extracts/CODEs.csv', mode='a', header=False, index=False)\n",
    "\n",
    "df = df.merge(CUI_CODEs, how='inner', on=':END_ID')\n",
    "df = df[[':START_ID','CodeID:ID']].rename({'CodeID:ID':':END_ID'}, axis=1)\n",
    "df.to_csv('UMLS-Graph-Extracts/CUI-CODEs.csv', mode='a', header=False, index=False)\n",
    "\n",
    "df.tail()"
   ]
  }
 ],
 "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.5"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}