get_reference_labels.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5fb9c644",
   "metadata": {},
   "outputs": [],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(purrr)\n",
    "library(jsonlite)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "63146fe2",
   "metadata": {},
   "outputs": [],
   "source": [
    "## connect to database\n",
    "con1 <- DBI::dbConnect(\n",
    "    RMySQL::MySQL(), \n",
    "    host = \"localhost\",\n",
    "    user = \"uog2000\",\n",
    "    port = 3307,\n",
    "    dbname = \"user_uog2000\",\n",
    "    password = read_file('/home/uog2000/mimir_setup/password.txt') %>% str_replace('\\n', '')\n",
    ")\n",
    "con <- DBI::dbConnect(\n",
    "    RMySQL::MySQL(), \n",
    "    host = \"localhost\",\n",
    "    user = \"uog2000\",\n",
    "    port = 3307,\n",
    "    dbname = \"clinical_merge_v5_2024q1\",\n",
    "    password = read_file('/home/uog2000/mimir_setup/password.txt') %>% str_replace('\\n', '')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "id": "57eba077",
   "metadata": {},
   "outputs": [],
   "source": [
    "referece_ades <- con1 %>% tbl('reference_ades') %>% as_tibble()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "bd576a77",
   "metadata": {},
   "outputs": [],
   "source": [
    "reference_rxnorm <- con1 %>% tbl('reference_ades_rxnorm') %>% as_tibble()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "bb4317d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 0 × 14</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>drug_concept_id</th><th scope=col>drug_name</th><th scope=col>concept_id_2</th><th scope=col>RXCUI</th><th scope=col>concept_id</th><th scope=col>concept_name</th><th scope=col>domain_id</th><th scope=col>vocabulary_id</th><th scope=col>concept_class_id</th><th scope=col>standard_concept</th><th scope=col>concept_code</th><th scope=col>valid_start_date</th><th scope=col>valid_end_date</th><th scope=col>invalid_reason</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 0 × 14\n",
       "\\begin{tabular}{llllllllllllll}\n",
       " drug\\_concept\\_id & drug\\_name & concept\\_id\\_2 & RXCUI & concept\\_id & concept\\_name & domain\\_id & vocabulary\\_id & concept\\_class\\_id & standard\\_concept & concept\\_code & valid\\_start\\_date & valid\\_end\\_date & invalid\\_reason\\\\\n",
       " <int> & <chr> & <int> & <int> & <int> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 0 × 14\n",
       "\n",
       "| drug_concept_id &lt;int&gt; | drug_name &lt;chr&gt; | concept_id_2 &lt;int&gt; | RXCUI &lt;int&gt; | concept_id &lt;int&gt; | concept_name &lt;chr&gt; | domain_id &lt;chr&gt; | vocabulary_id &lt;chr&gt; | concept_class_id &lt;chr&gt; | standard_concept &lt;chr&gt; | concept_code &lt;chr&gt; | valid_start_date &lt;chr&gt; | valid_end_date &lt;chr&gt; | invalid_reason &lt;chr&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
       "\n"
      ],
      "text/plain": [
       "     drug_concept_id drug_name concept_id_2 RXCUI concept_id concept_name\n",
       "     domain_id vocabulary_id concept_class_id standard_concept concept_code\n",
       "     valid_start_date valid_end_date invalid_reason"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "reference_rxnorm <- reference_rxnorm %>%\n",
    "                        mutate(RXCUI = as.integer(RXCUI))\n",
    "reference_rxnorm %>% filter(is.na(RXCUI))"
   ]
  },
  {
   "cell_type": "raw",
   "id": "aee9380f",
   "metadata": {},
   "source": [
    "paste0(unique(referece_ades$drug_name), collapse = '|')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "2be5ce4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "path <- \"../drug_labels/gold_xml/\"\n",
    "files <- dir(path, pattern = \"*.json\")\n",
    "gold <- files %>%\n",
    "       map_df(~fromJSON(file.path(path, .), flatten = TRUE))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "203b6e7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "path <- \"../drug_labels/train_xml/\"\n",
    "files <- dir(path, pattern = \"*.json\")\n",
    "train <- files %>%\n",
    "       map_df(~fromJSON(file.path(path, .), flatten = TRUE))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "abf5e409",
   "metadata": {},
   "outputs": [],
   "source": [
    "path <- \"../drug_labels/latest_labels_ingredients/\"\n",
    "files <- dir(path, pattern = \"*.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "08178c46",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 6 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>set_id</th><th scope=col>label_id</th><th scope=col>spl_version</th><th scope=col>title</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1612d931-d86e-4dba-bbd7-0aac3eaee131</td><td>B08BDAA0-28F1-6D59-3526-C965D2DB1A6F</td><td>1</td><td>HMS®(medrysone ophthalmic suspension) 1%          </td></tr>\n",
       "\t<tr><td>1612d931-d86e-4dba-bbd7-0aac3eaee131</td><td>B08BDAA0-28F1-6D59-3526-C965D2DB1A6F</td><td>1</td><td>HMS®(medrysone ophthalmic suspension) 1%          </td></tr>\n",
       "\t<tr><td>1612d931-d86e-4dba-bbd7-0aac3eaee131</td><td>B08BDAA0-28F1-6D59-3526-C965D2DB1A6F</td><td>1</td><td>HMS®(medrysone ophthalmic suspension) 1%          </td></tr>\n",
       "\t<tr><td>1612d931-d86e-4dba-bbd7-0aac3eaee131</td><td>B08BDAA0-28F1-6D59-3526-C965D2DB1A6F</td><td>1</td><td>HMS®(medrysone ophthalmic suspension) 1%          </td></tr>\n",
       "\t<tr><td>312042cf-df60-4dc5-90dd-4e77ebd4a781</td><td>312042cf-df60-4dc5-90dd-4e77ebd4a781</td><td>1</td><td>ENLON (edrophonium chloride injection, USP)Rx only</td></tr>\n",
       "\t<tr><td>312042cf-df60-4dc5-90dd-4e77ebd4a781</td><td>312042cf-df60-4dc5-90dd-4e77ebd4a781</td><td>1</td><td>ENLON (edrophonium chloride injection, USP)Rx only</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 6 × 4\n",
       "\\begin{tabular}{llll}\n",
       " set\\_id & label\\_id & spl\\_version & title\\\\\n",
       " <chr> & <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 1612d931-d86e-4dba-bbd7-0aac3eaee131 & B08BDAA0-28F1-6D59-3526-C965D2DB1A6F & 1 & HMS®(medrysone ophthalmic suspension) 1\\%          \\\\\n",
       "\t 1612d931-d86e-4dba-bbd7-0aac3eaee131 & B08BDAA0-28F1-6D59-3526-C965D2DB1A6F & 1 & HMS®(medrysone ophthalmic suspension) 1\\%          \\\\\n",
       "\t 1612d931-d86e-4dba-bbd7-0aac3eaee131 & B08BDAA0-28F1-6D59-3526-C965D2DB1A6F & 1 & HMS®(medrysone ophthalmic suspension) 1\\%          \\\\\n",
       "\t 1612d931-d86e-4dba-bbd7-0aac3eaee131 & B08BDAA0-28F1-6D59-3526-C965D2DB1A6F & 1 & HMS®(medrysone ophthalmic suspension) 1\\%          \\\\\n",
       "\t 312042cf-df60-4dc5-90dd-4e77ebd4a781 & 312042cf-df60-4dc5-90dd-4e77ebd4a781 & 1 & ENLON (edrophonium chloride injection, USP)Rx only\\\\\n",
       "\t 312042cf-df60-4dc5-90dd-4e77ebd4a781 & 312042cf-df60-4dc5-90dd-4e77ebd4a781 & 1 & ENLON (edrophonium chloride injection, USP)Rx only\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 6 × 4\n",
       "\n",
       "| set_id &lt;chr&gt; | label_id &lt;chr&gt; | spl_version &lt;chr&gt; | title &lt;chr&gt; |\n",
       "|---|---|---|---|\n",
       "| 1612d931-d86e-4dba-bbd7-0aac3eaee131 | B08BDAA0-28F1-6D59-3526-C965D2DB1A6F | 1 | HMS®(medrysone ophthalmic suspension) 1%           |\n",
       "| 1612d931-d86e-4dba-bbd7-0aac3eaee131 | B08BDAA0-28F1-6D59-3526-C965D2DB1A6F | 1 | HMS®(medrysone ophthalmic suspension) 1%           |\n",
       "| 1612d931-d86e-4dba-bbd7-0aac3eaee131 | B08BDAA0-28F1-6D59-3526-C965D2DB1A6F | 1 | HMS®(medrysone ophthalmic suspension) 1%           |\n",
       "| 1612d931-d86e-4dba-bbd7-0aac3eaee131 | B08BDAA0-28F1-6D59-3526-C965D2DB1A6F | 1 | HMS®(medrysone ophthalmic suspension) 1%           |\n",
       "| 312042cf-df60-4dc5-90dd-4e77ebd4a781 | 312042cf-df60-4dc5-90dd-4e77ebd4a781 | 1 | ENLON (edrophonium chloride injection, USP)Rx only |\n",
       "| 312042cf-df60-4dc5-90dd-4e77ebd4a781 | 312042cf-df60-4dc5-90dd-4e77ebd4a781 | 1 | ENLON (edrophonium chloride injection, USP)Rx only |\n",
       "\n"
      ],
      "text/plain": [
       "  set_id                               label_id                            \n",
       "1 1612d931-d86e-4dba-bbd7-0aac3eaee131 B08BDAA0-28F1-6D59-3526-C965D2DB1A6F\n",
       "2 1612d931-d86e-4dba-bbd7-0aac3eaee131 B08BDAA0-28F1-6D59-3526-C965D2DB1A6F\n",
       "3 1612d931-d86e-4dba-bbd7-0aac3eaee131 B08BDAA0-28F1-6D59-3526-C965D2DB1A6F\n",
       "4 1612d931-d86e-4dba-bbd7-0aac3eaee131 B08BDAA0-28F1-6D59-3526-C965D2DB1A6F\n",
       "5 312042cf-df60-4dc5-90dd-4e77ebd4a781 312042cf-df60-4dc5-90dd-4e77ebd4a781\n",
       "6 312042cf-df60-4dc5-90dd-4e77ebd4a781 312042cf-df60-4dc5-90dd-4e77ebd4a781\n",
       "  spl_version title                                             \n",
       "1 1           HMS®(medrysone ophthalmic suspension) 1%          \n",
       "2 1           HMS®(medrysone ophthalmic suspension) 1%          \n",
       "3 1           HMS®(medrysone ophthalmic suspension) 1%          \n",
       "4 1           HMS®(medrysone ophthalmic suspension) 1%          \n",
       "5 1           ENLON (edrophonium chloride injection, USP)Rx only\n",
       "6 1           ENLON (edrophonium chloride injection, USP)Rx only"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data <- files %>%\n",
    "       map_df(~fromJSON(file.path(path, .), flatten = TRUE))\n",
    "data %>% select(-sections) %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "f628a3d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>1551</li>\n",
       "\t<li>1</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 1551\n",
       "\\item 1\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 1551\n",
       "2. 1\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 1551    1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>1634</li>\n",
       "\t<li>1</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 1634\n",
       "\\item 1\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 1634\n",
       "2. 1\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 1634    1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data %>% distinct(set_id) %>% dim()\n",
    "rbind(data, train, gold) %>% distinct(set_id) %>% dim()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "1d4bcf70",
   "metadata": {},
   "outputs": [],
   "source": [
    "data <- rbind(data, train, gold)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "8fad2e16",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 5 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>set_id</th><th scope=col>label_id</th><th scope=col>spl_version</th><th scope=col>title</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1b5646a9-4724-44b8-8cd0-9c14f5e87f86</td><td>120fbd67-f644-aa50-e063-6394a90aae42</td><td>12</td><td>These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994</td></tr>\n",
       "\t<tr><td>1b5646a9-4724-44b8-8cd0-9c14f5e87f86</td><td>120fbd67-f644-aa50-e063-6394a90aae42</td><td>12</td><td>These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994</td></tr>\n",
       "\t<tr><td>1b5646a9-4724-44b8-8cd0-9c14f5e87f86</td><td>120fbd67-f644-aa50-e063-6394a90aae42</td><td>12</td><td>These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994</td></tr>\n",
       "\t<tr><td>1b5646a9-4724-44b8-8cd0-9c14f5e87f86</td><td>120fbd67-f644-aa50-e063-6394a90aae42</td><td>12</td><td>These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994</td></tr>\n",
       "\t<tr><td>1b5646a9-4724-44b8-8cd0-9c14f5e87f86</td><td>120fbd67-f644-aa50-e063-6394a90aae42</td><td>12</td><td>These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 5 × 4\n",
       "\\begin{tabular}{llll}\n",
       " set\\_id & label\\_id & spl\\_version & title\\\\\n",
       " <chr> & <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 & 120fbd67-f644-aa50-e063-6394a90aae42 & 12 & These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994\\\\\n",
       "\t 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 & 120fbd67-f644-aa50-e063-6394a90aae42 & 12 & These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994\\\\\n",
       "\t 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 & 120fbd67-f644-aa50-e063-6394a90aae42 & 12 & These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994\\\\\n",
       "\t 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 & 120fbd67-f644-aa50-e063-6394a90aae42 & 12 & These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994\\\\\n",
       "\t 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 & 120fbd67-f644-aa50-e063-6394a90aae42 & 12 & These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 5 × 4\n",
       "\n",
       "| set_id &lt;chr&gt; | label_id &lt;chr&gt; | spl_version &lt;chr&gt; | title &lt;chr&gt; |\n",
       "|---|---|---|---|\n",
       "| 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 | 120fbd67-f644-aa50-e063-6394a90aae42 | 12 | These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994 |\n",
       "| 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 | 120fbd67-f644-aa50-e063-6394a90aae42 | 12 | These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994 |\n",
       "| 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 | 120fbd67-f644-aa50-e063-6394a90aae42 | 12 | These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994 |\n",
       "| 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 | 120fbd67-f644-aa50-e063-6394a90aae42 | 12 | These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994 |\n",
       "| 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 | 120fbd67-f644-aa50-e063-6394a90aae42 | 12 | These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994 |\n",
       "\n"
      ],
      "text/plain": [
       "  set_id                               label_id                            \n",
       "1 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 120fbd67-f644-aa50-e063-6394a90aae42\n",
       "2 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 120fbd67-f644-aa50-e063-6394a90aae42\n",
       "3 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 120fbd67-f644-aa50-e063-6394a90aae42\n",
       "4 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 120fbd67-f644-aa50-e063-6394a90aae42\n",
       "5 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 120fbd67-f644-aa50-e063-6394a90aae42\n",
       "  spl_version\n",
       "1 12         \n",
       "2 12         \n",
       "3 12         \n",
       "4 12         \n",
       "5 12         \n",
       "  title                                                                                                                                                                                                                                                    \n",
       "1 These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\\n \\n\\n\\n\\nLAMOTRIGINE Tablets, for oral use\\n \\n\\n\\n\\nInitial U.S. Approval: 1994\n",
       "2 These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\\n \\n\\n\\n\\nLAMOTRIGINE Tablets, for oral use\\n \\n\\n\\n\\nInitial U.S. Approval: 1994\n",
       "3 These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\\n \\n\\n\\n\\nLAMOTRIGINE Tablets, for oral use\\n \\n\\n\\n\\nInitial U.S. Approval: 1994\n",
       "4 These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\\n \\n\\n\\n\\nLAMOTRIGINE Tablets, for oral use\\n \\n\\n\\n\\nInitial U.S. Approval: 1994\n",
       "5 These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\\n \\n\\n\\n\\nLAMOTRIGINE Tablets, for oral use\\n \\n\\n\\n\\nInitial U.S. Approval: 1994"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data %>% filter(set_id == '1b5646a9-4724-44b8-8cd0-9c14f5e87f86') %>% select(-sections)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "27f01e42",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.table: 6 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>SETID</th><th scope=col>SPL_VERSION</th><th scope=col>RXCUI</th><th scope=col>RXSTRING</th><th scope=col>RXTTY</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>000155a8-709c-44e5-a75f-cd890f3a7caf</td><td>27</td><td> 198014</td><td>naproxen 500 MG Oral Tablet                           </td><td>PSN</td></tr>\n",
       "\t<tr><td>000155a8-709c-44e5-a75f-cd890f3a7caf</td><td>27</td><td> 198014</td><td>naproxen 500 MG Oral Tablet                           </td><td>SCD</td></tr>\n",
       "\t<tr><td>0001d1cf-f727-4875-a44f-5a51f7b617a4</td><td> 2</td><td>1046593</td><td>benzalkonium chloride 0.13 % Medicated Liquid Soap    </td><td>PSN</td></tr>\n",
       "\t<tr><td>0001d1cf-f727-4875-a44f-5a51f7b617a4</td><td> 2</td><td>1046593</td><td>benzalkonium chloride 0.13 % Medicated Liquid Soap    </td><td>SY </td></tr>\n",
       "\t<tr><td>0001d1cf-f727-4875-a44f-5a51f7b617a4</td><td> 2</td><td>1046593</td><td>benzalkonium chloride 1.3 MG/ML Medicated Liquid Soap </td><td>SCD</td></tr>\n",
       "\t<tr><td>0001d1cf-f727-4875-a44f-5a51f7b617a4</td><td> 2</td><td>1046593</td><td>benzalkonium chloride 1.33 MG/ML Medicated Liquid Soap</td><td>SY </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.table: 6 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " SETID & SPL\\_VERSION & RXCUI & RXSTRING & RXTTY\\\\\n",
       " <chr> & <int> & <int> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 000155a8-709c-44e5-a75f-cd890f3a7caf & 27 &  198014 & naproxen 500 MG Oral Tablet                            & PSN\\\\\n",
       "\t 000155a8-709c-44e5-a75f-cd890f3a7caf & 27 &  198014 & naproxen 500 MG Oral Tablet                            & SCD\\\\\n",
       "\t 0001d1cf-f727-4875-a44f-5a51f7b617a4 &  2 & 1046593 & benzalkonium chloride 0.13 \\% Medicated Liquid Soap     & PSN\\\\\n",
       "\t 0001d1cf-f727-4875-a44f-5a51f7b617a4 &  2 & 1046593 & benzalkonium chloride 0.13 \\% Medicated Liquid Soap     & SY \\\\\n",
       "\t 0001d1cf-f727-4875-a44f-5a51f7b617a4 &  2 & 1046593 & benzalkonium chloride 1.3 MG/ML Medicated Liquid Soap  & SCD\\\\\n",
       "\t 0001d1cf-f727-4875-a44f-5a51f7b617a4 &  2 & 1046593 & benzalkonium chloride 1.33 MG/ML Medicated Liquid Soap & SY \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.table: 6 × 5\n",
       "\n",
       "| SETID &lt;chr&gt; | SPL_VERSION &lt;int&gt; | RXCUI &lt;int&gt; | RXSTRING &lt;chr&gt; | RXTTY &lt;chr&gt; |\n",
       "|---|---|---|---|---|\n",
       "| 000155a8-709c-44e5-a75f-cd890f3a7caf | 27 |  198014 | naproxen 500 MG Oral Tablet                            | PSN |\n",
       "| 000155a8-709c-44e5-a75f-cd890f3a7caf | 27 |  198014 | naproxen 500 MG Oral Tablet                            | SCD |\n",
       "| 0001d1cf-f727-4875-a44f-5a51f7b617a4 |  2 | 1046593 | benzalkonium chloride 0.13 % Medicated Liquid Soap     | PSN |\n",
       "| 0001d1cf-f727-4875-a44f-5a51f7b617a4 |  2 | 1046593 | benzalkonium chloride 0.13 % Medicated Liquid Soap     | SY  |\n",
       "| 0001d1cf-f727-4875-a44f-5a51f7b617a4 |  2 | 1046593 | benzalkonium chloride 1.3 MG/ML Medicated Liquid Soap  | SCD |\n",
       "| 0001d1cf-f727-4875-a44f-5a51f7b617a4 |  2 | 1046593 | benzalkonium chloride 1.33 MG/ML Medicated Liquid Soap | SY  |\n",
       "\n"
      ],
      "text/plain": [
       "  SETID                                SPL_VERSION RXCUI  \n",
       "1 000155a8-709c-44e5-a75f-cd890f3a7caf 27           198014\n",
       "2 000155a8-709c-44e5-a75f-cd890f3a7caf 27           198014\n",
       "3 0001d1cf-f727-4875-a44f-5a51f7b617a4  2          1046593\n",
       "4 0001d1cf-f727-4875-a44f-5a51f7b617a4  2          1046593\n",
       "5 0001d1cf-f727-4875-a44f-5a51f7b617a4  2          1046593\n",
       "6 0001d1cf-f727-4875-a44f-5a51f7b617a4  2          1046593\n",
       "  RXSTRING                                               RXTTY\n",
       "1 naproxen 500 MG Oral Tablet                            PSN  \n",
       "2 naproxen 500 MG Oral Tablet                            SCD  \n",
       "3 benzalkonium chloride 0.13 % Medicated Liquid Soap     PSN  \n",
       "4 benzalkonium chloride 0.13 % Medicated Liquid Soap     SY   \n",
       "5 benzalkonium chloride 1.3 MG/ML Medicated Liquid Soap  SCD  \n",
       "6 benzalkonium chloride 1.33 MG/ML Medicated Liquid Soap SY   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "rxnorm_mappings <- fread('../drug_labels/rxnorm_mappings_20240312.csv.gz')\n",
    "rxnorm_mappings %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "id": "352082b0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.table: 2 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>SETID</th><th scope=col>SPL_VERSION</th><th scope=col>RXCUI</th><th scope=col>RXSTRING</th><th scope=col>RXTTY</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1b5646a9-4724-44b8-8cd0-9c14f5e87f86</td><td>12</td><td>282401</td><td>lamoTRIgine 25 MG Oral Tablet</td><td>PSN</td></tr>\n",
       "\t<tr><td>1b5646a9-4724-44b8-8cd0-9c14f5e87f86</td><td>12</td><td>282401</td><td>lamotrigine 25 MG Oral Tablet</td><td>SCD</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.table: 2 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " SETID & SPL\\_VERSION & RXCUI & RXSTRING & RXTTY\\\\\n",
       " <chr> & <int> & <int> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 & 12 & 282401 & lamoTRIgine 25 MG Oral Tablet & PSN\\\\\n",
       "\t 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 & 12 & 282401 & lamotrigine 25 MG Oral Tablet & SCD\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.table: 2 × 5\n",
       "\n",
       "| SETID &lt;chr&gt; | SPL_VERSION &lt;int&gt; | RXCUI &lt;int&gt; | RXSTRING &lt;chr&gt; | RXTTY &lt;chr&gt; |\n",
       "|---|---|---|---|---|\n",
       "| 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 | 12 | 282401 | lamoTRIgine 25 MG Oral Tablet | PSN |\n",
       "| 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 | 12 | 282401 | lamotrigine 25 MG Oral Tablet | SCD |\n",
       "\n"
      ],
      "text/plain": [
       "  SETID                                SPL_VERSION RXCUI \n",
       "1 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 12          282401\n",
       "2 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 12          282401\n",
       "  RXSTRING                      RXTTY\n",
       "1 lamoTRIgine 25 MG Oral Tablet PSN  \n",
       "2 lamotrigine 25 MG Oral Tablet SCD  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "rxnorm_mappings %>% filter(SETID == '1b5646a9-4724-44b8-8cd0-9c14f5e87f86')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "id": "9fbdac20",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Warning message in inner_join(., reference_rxnorm, by = \"RXCUI\"):\n",
      "“\u001b[1m\u001b[22mEach row in `x` is expected to match at most 1 row in `y`.\n",
      "\u001b[36mℹ\u001b[39m Row 734 of `x` matches multiple rows.\n",
      "\u001b[36mℹ\u001b[39m If multiple matches are expected, set `multiple = \"all\"` to silence this\n",
      "  warning.”\n"
     ]
    }
   ],
   "source": [
    "mapped_drugs <- rxnorm_mappings %>%\n",
    "    inner_join(reference_rxnorm, by = 'RXCUI') %>%\n",
    "    distinct(drug_concept_id, drug_name, RXCUI)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "id": "18b22381",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Warning message in inner_join(., data, c(SETID = \"set_id\")):\n",
      "“\u001b[1m\u001b[22mEach row in `x` is expected to match at most 1 row in `y`.\n",
      "\u001b[36mℹ\u001b[39m Row 24 of `x` matches multiple rows.\n",
      "\u001b[36mℹ\u001b[39m If multiple matches are expected, set `multiple = \"all\"` to silence this\n",
      "  warning.”\n"
     ]
    }
   ],
   "source": [
    "labeled_drugs <- rxnorm_mappings %>%\n",
    "    inner_join(data, c('SETID' = 'set_id')) %>%\n",
    "    inner_join(reference_rxnorm, by = 'RXCUI') %>%\n",
    "    distinct(drug_concept_id, drug_name, RXCUI)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "id": "162622e0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>3</li>\n",
       "\t<li>2</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 3\n",
       "\\item 2\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 3\n",
       "2. 2\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 3 2"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "rxnorm_mappings %>%\n",
    "    inner_join(train, by = c('SETID' = 'set_id'), multiple = 'all') %>%\n",
    "    inner_join(reference_rxnorm, by = 'RXCUI') %>%\n",
    "    distinct(drug_concept_id, drug_name) %>% dim()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "id": "0543056e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>173</li>\n",
       "\t<li>2</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 173\n",
       "\\item 2\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 173\n",
       "2. 2\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 173   2"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>159</li>\n",
       "\t<li>2</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 159\n",
       "\\item 2\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 159\n",
       "2. 2\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 159   2"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>182</li>\n",
       "\t<li>2</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 182\n",
       "\\item 2\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 182\n",
       "2. 2\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 182   2"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "mapped_drugs %>% distinct(drug_concept_id, drug_name) %>% dim()\n",
    "labeled_drugs %>% distinct(drug_concept_id, drug_name) %>% dim()\n",
    "referece_ades %>% distinct(drug_concept_id, drug_name) %>% dim()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "id": "ca485d64",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining with `by = join_by(drug_concept_id, drug_name, RXCUI)`\n",
      "\u001b[1m\u001b[22m`summarise()` has grouped output by 'drug_concept_id'. You can override using the\n",
      "`.groups` argument.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A grouped_df: 8 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>drug_concept_id</th><th scope=col>drug_name</th><th scope=col>seen</th><th scope=col>mapped</th><th scope=col>label</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>  727835</td><td>Pemoline     </td><td>1</td><td>0</td><td>0</td></tr>\n",
       "\t<tr><td>  953391</td><td>Propantheline</td><td>1</td><td>0</td><td>0</td></tr>\n",
       "\t<tr><td> 1103374</td><td>valdecoxib   </td><td>1</td><td>0</td><td>0</td></tr>\n",
       "\t<tr><td> 1721543</td><td>Norfloxacin  </td><td>1</td><td>0</td><td>0</td></tr>\n",
       "\t<tr><td> 1724827</td><td>Zalcitabine  </td><td>1</td><td>0</td><td>0</td></tr>\n",
       "\t<tr><td>19008009</td><td>Cosyntropin  </td><td>1</td><td>0</td><td>0</td></tr>\n",
       "\t<tr><td>19011093</td><td>Tenofovir    </td><td>1</td><td>0</td><td>0</td></tr>\n",
       "\t<tr><td>19026710</td><td>Capreomycin  </td><td>1</td><td>0</td><td>0</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A grouped\\_df: 8 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " drug\\_concept\\_id & drug\\_name & seen & mapped & label\\\\\n",
       " <int> & <chr> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t   727835 & Pemoline      & 1 & 0 & 0\\\\\n",
       "\t   953391 & Propantheline & 1 & 0 & 0\\\\\n",
       "\t  1103374 & valdecoxib    & 1 & 0 & 0\\\\\n",
       "\t  1721543 & Norfloxacin   & 1 & 0 & 0\\\\\n",
       "\t  1724827 & Zalcitabine   & 1 & 0 & 0\\\\\n",
       "\t 19008009 & Cosyntropin   & 1 & 0 & 0\\\\\n",
       "\t 19011093 & Tenofovir     & 1 & 0 & 0\\\\\n",
       "\t 19026710 & Capreomycin   & 1 & 0 & 0\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A grouped_df: 8 × 5\n",
       "\n",
       "| drug_concept_id &lt;int&gt; | drug_name &lt;chr&gt; | seen &lt;dbl&gt; | mapped &lt;dbl&gt; | label &lt;dbl&gt; |\n",
       "|---|---|---|---|---|\n",
       "|   727835 | Pemoline      | 1 | 0 | 0 |\n",
       "|   953391 | Propantheline | 1 | 0 | 0 |\n",
       "|  1103374 | valdecoxib    | 1 | 0 | 0 |\n",
       "|  1721543 | Norfloxacin   | 1 | 0 | 0 |\n",
       "|  1724827 | Zalcitabine   | 1 | 0 | 0 |\n",
       "| 19008009 | Cosyntropin   | 1 | 0 | 0 |\n",
       "| 19011093 | Tenofovir     | 1 | 0 | 0 |\n",
       "| 19026710 | Capreomycin   | 1 | 0 | 0 |\n",
       "\n"
      ],
      "text/plain": [
       "  drug_concept_id drug_name     seen mapped label\n",
       "1   727835        Pemoline      1    0      0    \n",
       "2   953391        Propantheline 1    0      0    \n",
       "3  1103374        valdecoxib    1    0      0    \n",
       "4  1721543        Norfloxacin   1    0      0    \n",
       "5  1724827        Zalcitabine   1    0      0    \n",
       "6 19008009        Cosyntropin   1    0      0    \n",
       "7 19011093        Tenofovir     1    0      0    \n",
       "8 19026710        Capreomycin   1    0      0    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "reference_ades %>% as_tibble() %>% mutate(RXCUI = as.integer(RXCUI)) %>%\n",
    "    left_join(mapped_drugs %>% mutate(mapped = 1)) %>%\n",
    "    distinct(drug_concept_id, drug_name, mapped) %>% \n",
    "    left_join(labeled_drugs %>% distinct(drug_concept_id, drug_name) %>%\n",
    "              mutate(label = 1), by = c('drug_concept_id', 'drug_name')) %>%\n",
    "distinct(drug_concept_id, drug_name, mapped, label) %>%\n",
    "    replace_na(list('mapped' = 0, 'label' = 0)) %>%\n",
    "    group_by(drug_concept_id, drug_name) %>%\n",
    "    summarise(seen = 1,\n",
    "              mapped = max(mapped, na.rm = T),\n",
    "              label = max(label, na.rm = T)) %>%\n",
    "filter(mapped == 0, label == 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "id": "666b11a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 5 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>set_id</th><th scope=col>label_id</th><th scope=col>spl_version</th><th scope=col>title</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1b5646a9-4724-44b8-8cd0-9c14f5e87f86</td><td>120fbd67-f644-aa50-e063-6394a90aae42</td><td>12</td><td>These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994</td></tr>\n",
       "\t<tr><td>1b5646a9-4724-44b8-8cd0-9c14f5e87f86</td><td>120fbd67-f644-aa50-e063-6394a90aae42</td><td>12</td><td>These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994</td></tr>\n",
       "\t<tr><td>1b5646a9-4724-44b8-8cd0-9c14f5e87f86</td><td>120fbd67-f644-aa50-e063-6394a90aae42</td><td>12</td><td>These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994</td></tr>\n",
       "\t<tr><td>1b5646a9-4724-44b8-8cd0-9c14f5e87f86</td><td>120fbd67-f644-aa50-e063-6394a90aae42</td><td>12</td><td>These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994</td></tr>\n",
       "\t<tr><td>1b5646a9-4724-44b8-8cd0-9c14f5e87f86</td><td>120fbd67-f644-aa50-e063-6394a90aae42</td><td>12</td><td>These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 5 × 4\n",
       "\\begin{tabular}{llll}\n",
       " set\\_id & label\\_id & spl\\_version & title\\\\\n",
       " <chr> & <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 & 120fbd67-f644-aa50-e063-6394a90aae42 & 12 & These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994\\\\\n",
       "\t 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 & 120fbd67-f644-aa50-e063-6394a90aae42 & 12 & These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994\\\\\n",
       "\t 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 & 120fbd67-f644-aa50-e063-6394a90aae42 & 12 & These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994\\\\\n",
       "\t 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 & 120fbd67-f644-aa50-e063-6394a90aae42 & 12 & These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994\\\\\n",
       "\t 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 & 120fbd67-f644-aa50-e063-6394a90aae42 & 12 & These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 5 × 4\n",
       "\n",
       "| set_id &lt;chr&gt; | label_id &lt;chr&gt; | spl_version &lt;chr&gt; | title &lt;chr&gt; |\n",
       "|---|---|---|---|\n",
       "| 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 | 120fbd67-f644-aa50-e063-6394a90aae42 | 12 | These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994 |\n",
       "| 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 | 120fbd67-f644-aa50-e063-6394a90aae42 | 12 | These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994 |\n",
       "| 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 | 120fbd67-f644-aa50-e063-6394a90aae42 | 12 | These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994 |\n",
       "| 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 | 120fbd67-f644-aa50-e063-6394a90aae42 | 12 | These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994 |\n",
       "| 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 | 120fbd67-f644-aa50-e063-6394a90aae42 | 12 | These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "LAMOTRIGINE Tablets, for oral use\n",
       " \n",
       "\n",
       "\n",
       "\n",
       "Initial U.S. Approval: 1994 |\n",
       "\n"
      ],
      "text/plain": [
       "  set_id                               label_id                            \n",
       "1 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 120fbd67-f644-aa50-e063-6394a90aae42\n",
       "2 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 120fbd67-f644-aa50-e063-6394a90aae42\n",
       "3 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 120fbd67-f644-aa50-e063-6394a90aae42\n",
       "4 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 120fbd67-f644-aa50-e063-6394a90aae42\n",
       "5 1b5646a9-4724-44b8-8cd0-9c14f5e87f86 120fbd67-f644-aa50-e063-6394a90aae42\n",
       "  spl_version\n",
       "1 12         \n",
       "2 12         \n",
       "3 12         \n",
       "4 12         \n",
       "5 12         \n",
       "  title                                                                                                                                                                                                                                                    \n",
       "1 These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\\n \\n\\n\\n\\nLAMOTRIGINE Tablets, for oral use\\n \\n\\n\\n\\nInitial U.S. Approval: 1994\n",
       "2 These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\\n \\n\\n\\n\\nLAMOTRIGINE Tablets, for oral use\\n \\n\\n\\n\\nInitial U.S. Approval: 1994\n",
       "3 These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\\n \\n\\n\\n\\nLAMOTRIGINE Tablets, for oral use\\n \\n\\n\\n\\nInitial U.S. Approval: 1994\n",
       "4 These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\\n \\n\\n\\n\\nLAMOTRIGINE Tablets, for oral use\\n \\n\\n\\n\\nInitial U.S. Approval: 1994\n",
       "5 These highlights do not include all the information needed to use LAMOTRIGINE TABLETS safely and effectively. See full prescribing information for LAMOTRIGINE TABLETS.\\n \\n\\n\\n\\nLAMOTRIGINE Tablets, for oral use\\n \\n\\n\\n\\nInitial U.S. Approval: 1994"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data %>% filter(set_id == '1b5646a9-4724-44b8-8cd0-9c14f5e87f86') %>% select(-sections)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "id": "ea243e54",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Warning message in inner_join(., data %>% select(-sections), by = c(SETID = \"set_id\")):\n",
      "“\u001b[1m\u001b[22mEach row in `x` is expected to match at most 1 row in `y`.\n",
      "\u001b[36mℹ\u001b[39m Row 24 of `x` matches multiple rows.\n",
      "\u001b[36mℹ\u001b[39m If multiple matches are expected, set `multiple = \"all\"` to silence this\n",
      "  warning.”\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>1604</li>\n",
       "\t<li>1</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 1604\n",
       "\\item 1\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 1604\n",
       "2. 1\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 1604    1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "rxnorm_mappings %>%\n",
    "    inner_join(data %>% select(-sections), by = c('SETID' = 'set_id')) %>%\n",
    "    distinct(SETID) %>% dim()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "id": "fb4624ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>159</li>\n",
       "\t<li>1</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 159\n",
       "\\item 1\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 159\n",
       "2. 1\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 159   1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "reference_labels %>% ungroup() %>% distinct(drug_concept_id) %>% dim()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "id": "097d23ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22m`summarise()` has grouped output by 'cohort_id', 'condition_name',\n",
      "'drug_concept_id', 'drug_name', 'affect', 'set_id', 'label_id', 'spl_version'. You\n",
      "can override using the `.groups` argument.\n"
     ]
    }
   ],
   "source": [
    "reference_labels <- rxnorm_mappings %>%\n",
    "    inner_join(reference_rxnorm, by = 'RXCUI', multiple = \"all\") %>%\n",
    "    inner_join(data, by = c('SETID'='set_id'), multiple = \"all\") %>%\n",
    "    inner_join(referece_ades, by = c('drug_concept_id', 'drug_name'), multiple = \"all\") %>% \n",
    "    group_by(cohort_id, condition_name, drug_concept_id, drug_name, affect,\n",
    "             set_id=SETID, label_id, spl_version, title) %>%\n",
    "    summarise(sections = paste0(sections, collapse = '\\n')) %>%\n",
    "    select('cohort_id','condition_name','drug_concept_id','drug_name',\n",
    "           'affect','set_id','label_id','spl_version','title','sections') %>%\n",
    "# write_csv('reference_set_labels.csv')"
   ]
  },
  {
   "cell_type": "raw",
   "id": "91729a95",
   "metadata": {},
   "source": [
    "cohort_id,condition_name,drug_concept_id,drug_name,affect,set_id,label_id,spl_version,title,sections"
   ]
  },
  {
   "cell_type": "raw",
   "id": "6222bcf9",
   "metadata": {},
   "source": [
    "reference_labels <- rxnorm_mappings %>%\n",
    "    inner_join(reference_rxnorm, by = 'RXCUI', multiple = \"all\") %>%\n",
    "    inner_join(data, by = c('SETID'='set_id'), multiple = \"all\") %>%\n",
    "    inner_join(referece_ades, by = c('drug_concept_id', 'drug_name'), multiple = \"all\") %>% \n",
    "    group_by(cohort_id, condition_name, drug_concept_id, drug_name, affect,\n",
    "             set_id=SETID, label_id, spl_version, title) %>%\n",
    "    summarise(sections = paste0(sections, collapse = '\\n')) %>%\n",
    "    select('cohort_id','condition_name','drug_concept_id','drug_name',\n",
    "           'affect','set_id','label_id','spl_version','title','sections')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "id": "f2f02dcb",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining with `by = join_by(condition_name, drug_concept_id)`\n",
      "Warning message in left_join(., reference_labels %>% ungroup() %>% mutate(retrieved = 1)):\n",
      "“\u001b[1m\u001b[22mEach row in `x` is expected to match at most 1 row in `y`.\n",
      "\u001b[36mℹ\u001b[39m Row 17 of `x` matches multiple rows.\n",
      "\u001b[36mℹ\u001b[39m If multiple matches are expected, set `multiple = \"all\"` to silence this\n",
      "  warning.”\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 4 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>condition_name</th><th scope=col>n_drugs</th><th scope=col>n_retrieved</th><th scope=col>prop</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Acute kidney injury        </td><td> 88</td><td> 76</td><td>86.36</td></tr>\n",
       "\t<tr><td>Acute liver injury         </td><td>118</td><td>101</td><td>85.59</td></tr>\n",
       "\t<tr><td>Acute myocardial infarction</td><td>102</td><td> 89</td><td>87.25</td></tr>\n",
       "\t<tr><td>GI bleed                   </td><td> 91</td><td> 78</td><td>85.71</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 4 × 4\n",
       "\\begin{tabular}{llll}\n",
       " condition\\_name & n\\_drugs & n\\_retrieved & prop\\\\\n",
       " <chr> & <int> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Acute kidney injury         &  88 &  76 & 86.36\\\\\n",
       "\t Acute liver injury          & 118 & 101 & 85.59\\\\\n",
       "\t Acute myocardial infarction & 102 &  89 & 87.25\\\\\n",
       "\t GI bleed                    &  91 &  78 & 85.71\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 4 × 4\n",
       "\n",
       "| condition_name &lt;chr&gt; | n_drugs &lt;int&gt; | n_retrieved &lt;dbl&gt; | prop &lt;dbl&gt; |\n",
       "|---|---|---|---|\n",
       "| Acute kidney injury         |  88 |  76 | 86.36 |\n",
       "| Acute liver injury          | 118 | 101 | 85.59 |\n",
       "| Acute myocardial infarction | 102 |  89 | 87.25 |\n",
       "| GI bleed                    |  91 |  78 | 85.71 |\n",
       "\n"
      ],
      "text/plain": [
       "  condition_name              n_drugs n_retrieved prop \n",
       "1 Acute kidney injury          88      76         86.36\n",
       "2 Acute liver injury          118     101         85.59\n",
       "3 Acute myocardial infarction 102      89         87.25\n",
       "4 GI bleed                     91      78         85.71"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "referece_ades %>% distinct(condition_name, drug_concept_id) %>%\n",
    "    left_join(reference_labels %>% ungroup() %>% mutate(retrieved = 1)) %>%\n",
    "    replace_na(list('retrieved' = 0)) %>%\n",
    "    distinct(condition_name, drug_concept_id, retrieved) %>%\n",
    "    group_by(condition_name) %>%\n",
    "    summarise(n_drugs = n_distinct(drug_concept_id),\n",
    "              n_retrieved = sum(retrieved),\n",
    "              prop = round(n_retrieved/n_drugs * 100, digits = 2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "id": "b2ca0cfe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>159</li>\n",
       "\t<li>1</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 159\n",
       "\\item 1\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 159\n",
       "2. 1\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 159   1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "reference_labels %>% ungroup() %>% distinct(drug_concept_id) %>% dim()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "id": "a7cc6a88",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>182</li>\n",
       "\t<li>1</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 182\n",
       "\\item 1\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 182\n",
       "2. 1\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 182   1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "referece_ades %>% ungroup() %>% distinct(drug_concept_id) %>% dim()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "id": "c714a0d2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.table: 6 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>SETID</th><th scope=col>SPL_VERSION</th><th scope=col>RXCUI</th><th scope=col>RXSTRING</th><th scope=col>RXTTY</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>000155a8-709c-44e5-a75f-cd890f3a7caf</td><td>27</td><td> 198014</td><td>naproxen 500 MG Oral Tablet                           </td><td>PSN</td></tr>\n",
       "\t<tr><td>000155a8-709c-44e5-a75f-cd890f3a7caf</td><td>27</td><td> 198014</td><td>naproxen 500 MG Oral Tablet                           </td><td>SCD</td></tr>\n",
       "\t<tr><td>0001d1cf-f727-4875-a44f-5a51f7b617a4</td><td> 2</td><td>1046593</td><td>benzalkonium chloride 0.13 % Medicated Liquid Soap    </td><td>PSN</td></tr>\n",
       "\t<tr><td>0001d1cf-f727-4875-a44f-5a51f7b617a4</td><td> 2</td><td>1046593</td><td>benzalkonium chloride 0.13 % Medicated Liquid Soap    </td><td>SY </td></tr>\n",
       "\t<tr><td>0001d1cf-f727-4875-a44f-5a51f7b617a4</td><td> 2</td><td>1046593</td><td>benzalkonium chloride 1.3 MG/ML Medicated Liquid Soap </td><td>SCD</td></tr>\n",
       "\t<tr><td>0001d1cf-f727-4875-a44f-5a51f7b617a4</td><td> 2</td><td>1046593</td><td>benzalkonium chloride 1.33 MG/ML Medicated Liquid Soap</td><td>SY </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.table: 6 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " SETID & SPL\\_VERSION & RXCUI & RXSTRING & RXTTY\\\\\n",
       " <chr> & <int> & <int> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 000155a8-709c-44e5-a75f-cd890f3a7caf & 27 &  198014 & naproxen 500 MG Oral Tablet                            & PSN\\\\\n",
       "\t 000155a8-709c-44e5-a75f-cd890f3a7caf & 27 &  198014 & naproxen 500 MG Oral Tablet                            & SCD\\\\\n",
       "\t 0001d1cf-f727-4875-a44f-5a51f7b617a4 &  2 & 1046593 & benzalkonium chloride 0.13 \\% Medicated Liquid Soap     & PSN\\\\\n",
       "\t 0001d1cf-f727-4875-a44f-5a51f7b617a4 &  2 & 1046593 & benzalkonium chloride 0.13 \\% Medicated Liquid Soap     & SY \\\\\n",
       "\t 0001d1cf-f727-4875-a44f-5a51f7b617a4 &  2 & 1046593 & benzalkonium chloride 1.3 MG/ML Medicated Liquid Soap  & SCD\\\\\n",
       "\t 0001d1cf-f727-4875-a44f-5a51f7b617a4 &  2 & 1046593 & benzalkonium chloride 1.33 MG/ML Medicated Liquid Soap & SY \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.table: 6 × 5\n",
       "\n",
       "| SETID &lt;chr&gt; | SPL_VERSION &lt;int&gt; | RXCUI &lt;int&gt; | RXSTRING &lt;chr&gt; | RXTTY &lt;chr&gt; |\n",
       "|---|---|---|---|---|\n",
       "| 000155a8-709c-44e5-a75f-cd890f3a7caf | 27 |  198014 | naproxen 500 MG Oral Tablet                            | PSN |\n",
       "| 000155a8-709c-44e5-a75f-cd890f3a7caf | 27 |  198014 | naproxen 500 MG Oral Tablet                            | SCD |\n",
       "| 0001d1cf-f727-4875-a44f-5a51f7b617a4 |  2 | 1046593 | benzalkonium chloride 0.13 % Medicated Liquid Soap     | PSN |\n",
       "| 0001d1cf-f727-4875-a44f-5a51f7b617a4 |  2 | 1046593 | benzalkonium chloride 0.13 % Medicated Liquid Soap     | SY  |\n",
       "| 0001d1cf-f727-4875-a44f-5a51f7b617a4 |  2 | 1046593 | benzalkonium chloride 1.3 MG/ML Medicated Liquid Soap  | SCD |\n",
       "| 0001d1cf-f727-4875-a44f-5a51f7b617a4 |  2 | 1046593 | benzalkonium chloride 1.33 MG/ML Medicated Liquid Soap | SY  |\n",
       "\n"
      ],
      "text/plain": [
       "  SETID                                SPL_VERSION RXCUI  \n",
       "1 000155a8-709c-44e5-a75f-cd890f3a7caf 27           198014\n",
       "2 000155a8-709c-44e5-a75f-cd890f3a7caf 27           198014\n",
       "3 0001d1cf-f727-4875-a44f-5a51f7b617a4  2          1046593\n",
       "4 0001d1cf-f727-4875-a44f-5a51f7b617a4  2          1046593\n",
       "5 0001d1cf-f727-4875-a44f-5a51f7b617a4  2          1046593\n",
       "6 0001d1cf-f727-4875-a44f-5a51f7b617a4  2          1046593\n",
       "  RXSTRING                                               RXTTY\n",
       "1 naproxen 500 MG Oral Tablet                            PSN  \n",
       "2 naproxen 500 MG Oral Tablet                            SCD  \n",
       "3 benzalkonium chloride 0.13 % Medicated Liquid Soap     PSN  \n",
       "4 benzalkonium chloride 0.13 % Medicated Liquid Soap     SY   \n",
       "5 benzalkonium chloride 1.3 MG/ML Medicated Liquid Soap  SCD  \n",
       "6 benzalkonium chloride 1.33 MG/ML Medicated Liquid Soap SY   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "rxnorm_mappings %>% head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "id": "3692e8d5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining with `by = join_by(drug_concept_id, drug_name)`\n"
     ]
    }
   ],
   "source": [
    "referece_ades %>% \n",
    "    distinct(drug_concept_id, drug_name) %>%\n",
    "    anti_join(reference_labels) %>%\n",
    "write_csv('missing_labels.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "id": "d08b8cbf",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining with `by = join_by(drug_concept_id, drug_name)`\n"
     ]
    }
   ],
   "source": [
    "temp <- referece_ades %>% \n",
    "    distinct(drug_concept_id, drug_name) %>%\n",
    "    anti_join(reference_labels)\n",
    "missing <- paste0(unique(temp$drug_name), collapse = '|')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "id": "106a95f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 0 × 6</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>set_id</th><th scope=col>label_id</th><th scope=col>spl_version</th><th scope=col>sections</th><th scope=col>title</th><th scope=col>drug_name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;named list&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 0 × 6\n",
       "\\begin{tabular}{llllll}\n",
       " set\\_id & label\\_id & spl\\_version & sections & title & drug\\_name\\\\\n",
       " <chr> & <chr> & <chr> & <named list> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 0 × 6\n",
       "\n",
       "| set_id &lt;chr&gt; | label_id &lt;chr&gt; | spl_version &lt;chr&gt; | sections &lt;named list&gt; | title &lt;chr&gt; | drug_name &lt;chr&gt; |\n",
       "|---|---|---|---|---|---|\n",
       "\n"
      ],
      "text/plain": [
       "     set_id label_id spl_version sections title drug_name"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data %>% rowwise() %>%\n",
    "    mutate(title = title %>% tolower()) %>%\n",
    "    filter(grepl(pattern = missing, x = title)) %>%\n",
    "    mutate(drug_name = str_match(title, missing)[1]) %>% ungroup()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "id": "a92cc262",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining with `by = join_by(drug_concept_id, drug_name)`\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 1 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>cohort_id</th><th scope=col>condition_name</th><th scope=col>drug_concept_id</th><th scope=col>drug_name</th><th scope=col>affect</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>500000401</td><td>Acute kidney injury</td><td>1312742</td><td>Olmesartan medoxomil</td><td>1</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 1 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " cohort\\_id & condition\\_name & drug\\_concept\\_id & drug\\_name & affect\\\\\n",
       " <int> & <chr> & <int> & <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t 500000401 & Acute kidney injury & 1312742 & Olmesartan medoxomil & 1\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 1 × 5\n",
       "\n",
       "| cohort_id &lt;int&gt; | condition_name &lt;chr&gt; | drug_concept_id &lt;int&gt; | drug_name &lt;chr&gt; | affect &lt;int&gt; |\n",
       "|---|---|---|---|---|\n",
       "| 500000401 | Acute kidney injury | 1312742 | Olmesartan medoxomil | 1 |\n",
       "\n"
      ],
      "text/plain": [
       "  cohort_id condition_name      drug_concept_id drug_name            affect\n",
       "1 500000401 Acute kidney injury 1312742         Olmesartan medoxomil 1     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "referece_ades %>% as_tibble() %>%\n",
    "    anti_join(reference_ades %>% as_tibble())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2615f121",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R [conda env:geno] *",
   "language": "R",
   "name": "conda-env-geno-r"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}