index-drugs.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(6358, 6)\n"
     ]
    },
    {
     "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>Class ID</th>\n",
       "      <th>Preferred Label</th>\n",
       "      <th>Synonyms</th>\n",
       "      <th>CUI</th>\n",
       "      <th>Parents</th>\n",
       "      <th>ATC LEVEL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://purl.bioontology.org/ontology/UATC/A03AX13</td>\n",
       "      <td>silicones</td>\n",
       "      <td></td>\n",
       "      <td>C0037114</td>\n",
       "      <td>http://purl.bioontology.org/ontology/UATC/A03AX</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://purl.bioontology.org/ontology/UATC/J01DB07</td>\n",
       "      <td>cefatrizine</td>\n",
       "      <td></td>\n",
       "      <td>C0007545</td>\n",
       "      <td>http://purl.bioontology.org/ontology/UATC/J01DB</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://purl.bioontology.org/ontology/UATC/V09AA</td>\n",
       "      <td>Technetium 99m compounds, central nervous syst...</td>\n",
       "      <td>Technetium (99mTc) compounds</td>\n",
       "      <td>C3541402</td>\n",
       "      <td>http://purl.bioontology.org/ontology/UATC/V09A</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://purl.bioontology.org/ontology/UATC/G03GA04</td>\n",
       "      <td>urofollitropin</td>\n",
       "      <td></td>\n",
       "      <td>C0524639</td>\n",
       "      <td>http://purl.bioontology.org/ontology/UATC/G03GA</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://purl.bioontology.org/ontology/UATC/D04AA10</td>\n",
       "      <td>promethazine</td>\n",
       "      <td></td>\n",
       "      <td>C0033405</td>\n",
       "      <td>http://purl.bioontology.org/ontology/UATC/D04AA</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            Class ID  \\\n",
       "0  http://purl.bioontology.org/ontology/UATC/A03AX13   \n",
       "1  http://purl.bioontology.org/ontology/UATC/J01DB07   \n",
       "2    http://purl.bioontology.org/ontology/UATC/V09AA   \n",
       "3  http://purl.bioontology.org/ontology/UATC/G03GA04   \n",
       "4  http://purl.bioontology.org/ontology/UATC/D04AA10   \n",
       "\n",
       "                                     Preferred Label  \\\n",
       "0                                          silicones   \n",
       "1                                        cefatrizine   \n",
       "2  Technetium 99m compounds, central nervous syst...   \n",
       "3                                     urofollitropin   \n",
       "4                                       promethazine   \n",
       "\n",
       "                       Synonyms       CUI  \\\n",
       "0                                C0037114   \n",
       "1                                C0007545   \n",
       "2  Technetium (99mTc) compounds  C3541402   \n",
       "3                                C0524639   \n",
       "4                                C0033405   \n",
       "\n",
       "                                           Parents ATC LEVEL  \n",
       "0  http://purl.bioontology.org/ontology/UATC/A03AX         5  \n",
       "1  http://purl.bioontology.org/ontology/UATC/J01DB         5  \n",
       "2   http://purl.bioontology.org/ontology/UATC/V09A         4  \n",
       "3  http://purl.bioontology.org/ontology/UATC/G03GA         5  \n",
       "4  http://purl.bioontology.org/ontology/UATC/D04AA         5  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd \n",
    "\n",
    "atc_data = pd.read_csv(\n",
    "    \"data/atc-codes.csv\",      # relative python path to subdirectory\n",
    "    sep=',',\n",
    "    na_filter=False,\n",
    "    quotechar=\"\\\"\",        # single quote allowed as quote character\n",
    "    usecols=['Class ID','Preferred Label','Synonyms','CUI','Parents','ATC LEVEL']\n",
    ")\n",
    "\n",
    "print(atc_data.shape)\n",
    "atc_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100 drugs added\n",
      "200 drugs added\n",
      "300 drugs added\n",
      "400 drugs added\n",
      "500 drugs added\n",
      "600 drugs added\n",
      "700 drugs added\n",
      "800 drugs added\n",
      "900 drugs added\n",
      "1000 drugs added\n",
      "1100 drugs added\n",
      "1200 drugs added\n",
      "1300 drugs added\n",
      "1400 drugs added\n",
      "1500 drugs added\n",
      "1600 drugs added\n",
      "1700 drugs added\n",
      "1800 drugs added\n",
      "1900 drugs added\n",
      "2000 drugs added\n",
      "2100 drugs added\n",
      "2200 drugs added\n",
      "2300 drugs added\n",
      "2400 drugs added\n",
      "2500 drugs added\n",
      "2600 drugs added\n",
      "2700 drugs added\n",
      "2800 drugs added\n",
      "2900 drugs added\n",
      "3000 drugs added\n",
      "3100 drugs added\n",
      "3200 drugs added\n",
      "3300 drugs added\n",
      "3400 drugs added\n",
      "3500 drugs added\n",
      "3600 drugs added\n",
      "3700 drugs added\n",
      "3800 drugs added\n",
      "3900 drugs added\n",
      "4000 drugs added\n",
      "4100 drugs added\n",
      "4200 drugs added\n",
      "4300 drugs added\n",
      "4400 drugs added\n",
      "4500 drugs added\n",
      "4600 drugs added\n",
      "4700 drugs added\n",
      "4800 drugs added\n",
      "4900 drugs added\n",
      "5000 drugs added\n",
      "5100 drugs added\n",
      "5200 drugs added\n",
      "5300 drugs added\n",
      "5400 drugs added\n",
      "5500 drugs added\n",
      "5600 drugs added\n",
      "5700 drugs added\n",
      "5800 drugs added\n",
      "5900 drugs added\n",
      "6000 drugs added\n",
      "6100 drugs added\n",
      "6200 drugs added\n",
      "6300 drugs added\n"
     ]
    }
   ],
   "source": [
    "import pysolr\n",
    "import math\n",
    "\n",
    "solr = pysolr.Solr('http://librairy.linkeddata.es/data/atc', timeout=2)\n",
    "\n",
    "documents = []\n",
    "num = 0;\n",
    "for i in atc_data.itertuples(index=False):\n",
    "    atc_document = {}\n",
    "    if (len(i[0]) > 0 ):\n",
    "        atc_document['id']= i[0].split(\"/\")[-1]\n",
    "    if (len(i[1]) > 0 ):\n",
    "        atc_document['label_t']= i[1].lower()\n",
    "    synonyms = []\n",
    "    if (len(i[2]) > 0 ):\n",
    "        synonyms.append(i[2].lower())\n",
    "    atc_document['synonyms']= synonyms\n",
    "    if (len(i[3]) > 0 ):\n",
    "        atc_document['cui_s']= i[3]\n",
    "    if (len(i[4]) > 0 ):\n",
    "        atc_document['parent_s']= i[4].split(\"/\")[-1]\n",
    "    if (len(i[5]) > 0 ):\n",
    "        atc_document['level_i']= i[5]    \n",
    "    documents.append(atc_document) \n",
    "    num+=1\n",
    "    if (len(documents) > 0 and len(documents) %100 == 0):\n",
    "        solr.add(documents)\n",
    "        solr.commit()\n",
    "        documents = []\n",
    "        print(num,\"drugs added\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Adding CIMA Codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(23407, 3)\n"
     ]
    },
    {
     "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>Nº Registro</th>\n",
       "      <th>Medicamento</th>\n",
       "      <th>Cód. ATC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>40537</td>\n",
       "      <td>INACID 25 mg CAPSULAS DURAS</td>\n",
       "      <td>M01AB01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7235</td>\n",
       "      <td>PASSIFLORINE SOLUCION ORAL</td>\n",
       "      <td>N05CM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18329</td>\n",
       "      <td>TIOBARBITAL BRAUN 1 G</td>\n",
       "      <td>N01AF03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33561</td>\n",
       "      <td>DIHYDERGOT COMPRIMIDOS</td>\n",
       "      <td>N02CA01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34369</td>\n",
       "      <td>GLUCOSADA GRIFOLS 5% SOLUCION PARA PERFUSION</td>\n",
       "      <td>B05BA03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Nº Registro                                   Medicamento Cód. ATC\n",
       "0       40537                   INACID 25 mg CAPSULAS DURAS  M01AB01\n",
       "1        7235                    PASSIFLORINE SOLUCION ORAL    N05CM\n",
       "2       18329                         TIOBARBITAL BRAUN 1 G  N01AF03\n",
       "3       33561                        DIHYDERGOT COMPRIMIDOS  N02CA01\n",
       "4       34369  GLUCOSADA GRIFOLS 5% SOLUCION PARA PERFUSION  B05BA03"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drugs_data = pd.read_csv(\n",
    "    \"data/drugs.csv\",      # relative python path to subdirectory\n",
    "    sep=';',           # Tab-separated value file.\n",
    "    quotechar=\"\\\"\",        # single quote allowed as quote character\n",
    "    usecols=['Nº Registro', 'Medicamento', 'Cód. ATC']\n",
    ")\n",
    "\n",
    "print(drugs_data.shape) \n",
    "drugs_data.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     Cód. ATC                                       Nº Registro\n",
      "0     A01AA01                                    [57745, 57746]\n",
      "1       A01AB  [38791, 34484, 35528, 12912, 45563, 9957, 29898]\n",
      "2     A01AB03                                    [84343, 80642]\n",
      "3     A01AB09                                    [55969, 55962]\n",
      "4     A01AB19                                           [39783]\n",
      "...       ...                                               ...\n",
      "2227    V10XX                              [05322001, 05322002]\n",
      "2228  V10XX02                                        [03264001]\n",
      "2229  V10XX03                                       [113873001]\n",
      "2230  V10XX04                                      [1171226001]\n",
      "2231   XXXXXX               [75668, 75670, 75659, 75661, 75663]\n",
      "\n",
      "[2232 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "atc_regs = drugs_data.groupby('Cód. ATC')['Nº Registro'].apply(list).reset_index()\n",
    "print(atc_regs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100 drugs updated\n",
      "200 drugs updated\n",
      "300 drugs updated\n",
      "400 drugs updated\n",
      "500 drugs updated\n",
      "600 drugs updated\n",
      "700 drugs updated\n",
      "800 drugs updated\n",
      "900 drugs updated\n",
      "1000 drugs updated\n",
      "1100 drugs updated\n",
      "1200 drugs updated\n",
      "1300 drugs updated\n",
      "1400 drugs updated\n",
      "1500 drugs updated\n",
      "1600 drugs updated\n",
      "1700 drugs updated\n",
      "1800 drugs updated\n",
      "1900 drugs updated\n",
      "2000 drugs updated\n",
      "2100 drugs updated\n",
      "2200 drugs updated\n"
     ]
    }
   ],
   "source": [
    "import pysolr\n",
    "\n",
    "solr = pysolr.Solr('http://librairy.linkeddata.es/data/atc', timeout=2)\n",
    "\n",
    "def add_num_regs(atc_code,num_regs):\n",
    "    solr_query = \"id:\"+str(atc_code)\n",
    "    for result in solr.search(q=solr_query,rows=1):\n",
    "        result['cima_codes']=num_regs\n",
    "        del (result['_version_'])\n",
    "        return result\n",
    "    return {}\n",
    "\n",
    "sample_doc = {\n",
    "  \"id\": \"1212121212112121212\",\n",
    "  \"cima_codes\":[\"a\",\"b\",\"c\"]\n",
    "}\n",
    "solr.add([sample_doc])\n",
    "solr.commit()\n",
    "solr.delete(id=sample_doc['id'])\n",
    "solr.commit()\n",
    "updated_drugs = []\n",
    "num = 0;\n",
    "for i in atc_regs.itertuples():\n",
    "    atc_code = i[1]\n",
    "    updated_drugs.append(add_num_regs(atc_code,[str(code) for code in i[2]])) \n",
    "    num+=1\n",
    "    if (len(updated_drugs) == 0):\n",
    "        solr.add(updated_drugs)\n",
    "        solr.commit()\n",
    "        updated_drugs = []\n",
    "        print(num,\"drugs updated\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Adding medical names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Category</th>\n",
       "      <th>Medicine name</th>\n",
       "      <th>Therapeutic area</th>\n",
       "      <th>International non-proprietary name (INN) / common name</th>\n",
       "      <th>Active substance</th>\n",
       "      <th>Product number</th>\n",
       "      <th>Patient safety</th>\n",
       "      <th>Authorisation status</th>\n",
       "      <th>ATC code</th>\n",
       "      <th>Additional monitoring</th>\n",
       "      <th>...</th>\n",
       "      <th>Vet pharmacotherapeutic group</th>\n",
       "      <th>Date of opinion</th>\n",
       "      <th>Decision date</th>\n",
       "      <th>Revision number</th>\n",
       "      <th>Condition / indication</th>\n",
       "      <th>Species</th>\n",
       "      <th>ATCvet code</th>\n",
       "      <th>First published</th>\n",
       "      <th>Revision date</th>\n",
       "      <th>URL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Human</td>\n",
       "      <td>Eliquis</td>\n",
       "      <td>Arthroplasty, Venous Thromboembolism</td>\n",
       "      <td>apixaban</td>\n",
       "      <td>Apixaban</td>\n",
       "      <td>EMEA/H/C/002148</td>\n",
       "      <td>yes</td>\n",
       "      <td>Authorised</td>\n",
       "      <td>B01AF02</td>\n",
       "      <td>no</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2020-03-26 01:00:00</td>\n",
       "      <td>20</td>\n",
       "      <td>For Eliquis 2.5 mg film-coated tablets:Prevent...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2018-08-03 16:27:00</td>\n",
       "      <td>2020-04-22 17:05:00</td>\n",
       "      <td>https://www.ema.europa.eu/en/medicines/human/E...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Human</td>\n",
       "      <td>Temozolomide Sun</td>\n",
       "      <td>Glioma, Glioblastoma</td>\n",
       "      <td>temozolomide</td>\n",
       "      <td>temozolomide</td>\n",
       "      <td>EMEA/H/C/002198</td>\n",
       "      <td>no</td>\n",
       "      <td>Authorised</td>\n",
       "      <td>L01AX03</td>\n",
       "      <td>no</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2020-03-11 01:00:00</td>\n",
       "      <td>16</td>\n",
       "      <td>Temozolomide Sun is indicated for the treatmen...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2018-08-03 00:00:00</td>\n",
       "      <td>2020-04-22 16:34:00</td>\n",
       "      <td>https://www.ema.europa.eu/en/medicines/human/E...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Human</td>\n",
       "      <td>Atazanavir Mylan</td>\n",
       "      <td>HIV Infections</td>\n",
       "      <td>atazanavir</td>\n",
       "      <td>atazanavir sulfate</td>\n",
       "      <td>EMEA/H/C/004048</td>\n",
       "      <td>no</td>\n",
       "      <td>Authorised</td>\n",
       "      <td>J05AE08</td>\n",
       "      <td>no</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2020-03-12 01:00:00</td>\n",
       "      <td>8</td>\n",
       "      <td>Atazanavir Mylan, co-administered with low dos...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2018-05-03 17:20:00</td>\n",
       "      <td>2020-04-22 16:00:00</td>\n",
       "      <td>https://www.ema.europa.eu/en/medicines/human/E...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Human</td>\n",
       "      <td>Vivanza</td>\n",
       "      <td>Erectile Dysfunction</td>\n",
       "      <td>vardenafil</td>\n",
       "      <td>vardenafil</td>\n",
       "      <td>EMEA/H/C/000488</td>\n",
       "      <td>no</td>\n",
       "      <td>Authorised</td>\n",
       "      <td>G04BE09</td>\n",
       "      <td>no</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2020-03-19 01:00:00</td>\n",
       "      <td>27</td>\n",
       "      <td>Treatment of erectile dysfunction in adult men...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2018-04-19 00:00:00</td>\n",
       "      <td>2020-04-22 16:00:00</td>\n",
       "      <td>https://www.ema.europa.eu/en/medicines/human/E...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Human</td>\n",
       "      <td>Juluca</td>\n",
       "      <td>HIV Infections</td>\n",
       "      <td>dolutegravir / rilpivirine</td>\n",
       "      <td>dolutegravir sodium, rilpivirine hydrochloride</td>\n",
       "      <td>EMEA/H/C/004427</td>\n",
       "      <td>yes</td>\n",
       "      <td>Authorised</td>\n",
       "      <td>J05AR</td>\n",
       "      <td>yes</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>2018-03-22 01:00:00</td>\n",
       "      <td>2020-04-01 00:00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>Juluca is indicated for the treatment of human...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2018-05-16 12:26:00</td>\n",
       "      <td>2020-04-22 15:44:00</td>\n",
       "      <td>https://www.ema.europa.eu/en/medicines/human/E...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Category     Medicine name                      Therapeutic area  \\\n",
       "0    Human           Eliquis  Arthroplasty, Venous Thromboembolism   \n",
       "1    Human  Temozolomide Sun                  Glioma, Glioblastoma   \n",
       "2    Human  Atazanavir Mylan                        HIV Infections   \n",
       "3    Human           Vivanza                  Erectile Dysfunction   \n",
       "4    Human            Juluca                        HIV Infections   \n",
       "\n",
       "  International non-proprietary name (INN) / common name  \\\n",
       "0                                           apixaban       \n",
       "1                                       temozolomide       \n",
       "2                                         atazanavir       \n",
       "3                                         vardenafil       \n",
       "4                         dolutegravir / rilpivirine       \n",
       "\n",
       "                                 Active substance   Product number  \\\n",
       "0                                        Apixaban  EMEA/H/C/002148   \n",
       "1                                    temozolomide  EMEA/H/C/002198   \n",
       "2                              atazanavir sulfate  EMEA/H/C/004048   \n",
       "3                                      vardenafil  EMEA/H/C/000488   \n",
       "4  dolutegravir sodium, rilpivirine hydrochloride  EMEA/H/C/004427   \n",
       "\n",
       "  Patient safety Authorisation status ATC code Additional monitoring  ...  \\\n",
       "0            yes           Authorised  B01AF02                    no  ...   \n",
       "1             no           Authorised  L01AX03                    no  ...   \n",
       "2             no           Authorised  J05AE08                    no  ...   \n",
       "3             no           Authorised  G04BE09                    no  ...   \n",
       "4            yes           Authorised    J05AR                   yes  ...   \n",
       "\n",
       "  Vet pharmacotherapeutic group      Date of opinion       Decision date  \\\n",
       "0                                                    2020-03-26 01:00:00   \n",
       "1                                                    2020-03-11 01:00:00   \n",
       "2                                                    2020-03-12 01:00:00   \n",
       "3                                                    2020-03-19 01:00:00   \n",
       "4                                2018-03-22 01:00:00 2020-04-01 00:00:00   \n",
       "\n",
       "  Revision number                             Condition / indication Species  \\\n",
       "0              20  For Eliquis 2.5 mg film-coated tablets:Prevent...           \n",
       "1              16  Temozolomide Sun is indicated for the treatmen...           \n",
       "2               8  Atazanavir Mylan, co-administered with low dos...           \n",
       "3              27  Treatment of erectile dysfunction in adult men...           \n",
       "4               2  Juluca is indicated for the treatment of human...           \n",
       "\n",
       "  ATCvet code     First published       Revision date  \\\n",
       "0             2018-08-03 16:27:00 2020-04-22 17:05:00   \n",
       "1             2018-08-03 00:00:00 2020-04-22 16:34:00   \n",
       "2             2018-05-03 17:20:00 2020-04-22 16:00:00   \n",
       "3             2018-04-19 00:00:00 2020-04-22 16:00:00   \n",
       "4             2018-05-16 12:26:00 2020-04-22 15:44:00   \n",
       "\n",
       "                                                 URL  \n",
       "0  https://www.ema.europa.eu/en/medicines/human/E...  \n",
       "1  https://www.ema.europa.eu/en/medicines/human/E...  \n",
       "2  https://www.ema.europa.eu/en/medicines/human/E...  \n",
       "3  https://www.ema.europa.eu/en/medicines/human/E...  \n",
       "4  https://www.ema.europa.eu/en/medicines/human/E...  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "md_df = pd.read_excel('data/Medicines_output_european_public_assessment_reports.xlsx', skiprows=8, keep_default_na=False)\n",
    "md_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100 drugs updated\n",
      "200 drugs updated\n",
      "300 drugs updated\n",
      "400 drugs updated\n",
      "500 drugs updated\n",
      "600 drugs updated\n",
      "700 drugs updated\n",
      "800 drugs updated\n",
      "900 drugs updated\n",
      "1000 drugs updated\n",
      "1100 drugs updated\n",
      "1200 drugs updated\n",
      "1300 drugs updated\n"
     ]
    }
   ],
   "source": [
    "import pysolr\n",
    "import html2text\n",
    "import urllib.request\n",
    "\n",
    "solr = pysolr.Solr('http://librairy.linkeddata.es/data/atc', timeout=20)\n",
    "\n",
    "h = html2text.HTML2Text()\n",
    "h.ignore_links = True\n",
    "\n",
    "def add_field_in_list(resource,field,value):\n",
    "    if (len(value) > 0 ):\n",
    "        if (not field in resource):\n",
    "            resource[field] = []\n",
    "        resource[field].append(value)  \n",
    "\n",
    "def get_txt(url):\n",
    "    fp = urllib.request.urlopen(url)\n",
    "    mybytes = fp.read()\n",
    "    mystr = mybytes.decode(\"utf8\")\n",
    "    fp.close()\n",
    "    return h.handle(mystr)\n",
    "\n",
    "documents = []\n",
    "i = 1\n",
    "for row in md_df.itertuples():\n",
    "    atc_code = row[9]\n",
    "    if (len(atc_code) > 1):\n",
    "        for drug in solr.search(\"id:\"+atc_code):\n",
    "            add_field_in_list(drug,'category',row[1])\n",
    "            add_field_in_list(drug,'medicines',row[2])\n",
    "            add_field_in_list(drug,'therapeutic_area',row[3])\n",
    "            add_field_in_list(drug,'emea_codes',row[6])\n",
    "            add_field_in_list(drug,'overviews',row[30])\n",
    "            solr.add([drug])\n",
    "            solr.commit()\n",
    "            #print(drug)\n",
    "            if (i % 100 == 0):\n",
    "                print(i,\"drugs updated\")\n",
    "            i+=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}