notebooks/vancomycin-dosing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Investigating dosing of vancomycin\n",
    "\n",
    "This notebook aims to investigate dosing of vancomycin in MIMIC-III."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "from __future__ import print_function\n",
    "\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import psycopg2\n",
    "import socket\n",
    "import sys\n",
    "import os\n",
    "import getpass\n",
    "\n",
    "from collections import OrderedDict\n",
    "\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# colours for prettier plots\n",
    "import colorsys\n",
    "def gg_color_hue(n):\n",
    "    hues = np.linspace(15, 375, n)\n",
    "    hsv_tuples = [(x*1.0/360.0, 0.5, 0.8) for x in hues]\n",
    "    rgb_tuples = map(lambda x: colorsys.hsv_to_rgb(*x), hsv_tuples)\n",
    "    return rgb_tuples\n",
    "\n",
    "marker = ['v','o','d','^','s','o','+']\n",
    "ls = ['-','-','-','-','-','s','--','--']\n",
    "\n",
    "from IPython.display import display, HTML\n",
    "\n",
    "# plot settings\n",
    "%matplotlib inline\n",
    "plt.style.use('ggplot')\n",
    "font = {'size'   : 20}\n",
    "matplotlib.rc('font', **font)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using username alistairewj\n",
      "Connected to postgres 10.0.1!\n"
     ]
    }
   ],
   "source": [
    "sqluser = getpass.getuser()\n",
    "print('Using username {}'.format(sqluser))\n",
    "\n",
    "dbname = 'mimic'\n",
    "schema_name = 'mimiciii'\n",
    "query_schema = 'SET search_path to public,' + schema_name + ';'\n",
    "\n",
    "# Connect to local postgres version of mimic\n",
    "con = psycopg2.connect(dbname=dbname, user=sqluser)\n",
    "\n",
    "print('Connected to postgres {}.{}.{}!'.format(int(con.server_version/10000),\n",
    "                                              int((con.server_version - int(con.server_version/10000)*10000)/100),\n",
    "                                              int(con.server_version - int(con.server_version/100)*100)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Determine vancomycin drug names in prescriptions table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>drug_name_generic</th>\n",
       "      <th>drug_name_poe</th>\n",
       "      <th>drug</th>\n",
       "      <th>numobs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vancocin</td>\n",
       "      <td>Vancocin</td>\n",
       "      <td>Vancocin</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Vancomycin 25mg/mL Ophth Soln</td>\n",
       "      <td>Vancomycin 25mg/mL Ophth Soln</td>\n",
       "      <td>Vancomycin 25mg/mL Ophth Soln</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Vancomycin Desensitization</td>\n",
       "      <td>Vancomycin Desensitization</td>\n",
       "      <td>Vancomycin Desensitization</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Vancomycin Enema</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Vancomycin Enema</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Vancomycin Enema</td>\n",
       "      <td>Vancomycin Enema</td>\n",
       "      <td>Vancomycin Enema</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>vancoMYCIN for inhalation</td>\n",
       "      <td>vancoMYCIN for inhalation</td>\n",
       "      <td>vancoMYCIN for inhalation</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>vancoMYCIN for nasal inhalation</td>\n",
       "      <td>vancoMYCIN for nasal inhalation</td>\n",
       "      <td>vancoMYCIN for nasal inhalation</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Vancomycin fortified opthalmic</td>\n",
       "      <td>Vancomycin fortified opthalmic</td>\n",
       "      <td>Vancomycin fortified opthalmic</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Vancomycin HCl</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Vancomycin HCl</td>\n",
       "      <td>Vancomycin HCl</td>\n",
       "      <td>Vancomycin HCl</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Vancomycin HCl</td>\n",
       "      <td>None</td>\n",
       "      <td>Vancomycin HCl</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Vancomycin ophthalmic 50mg/ml</td>\n",
       "      <td>Vancomycin ophthalmic 50mg/ml</td>\n",
       "      <td>Vancomycin ophthalmic 50mg/ml</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Vancomycin ophthalmic solution</td>\n",
       "      <td>Vancomycin ophthalmic solution</td>\n",
       "      <td>Vancomycin ophthalmic solution</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Vancomycin Oral Liquid</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Vancomycin Oral Liquid</td>\n",
       "      <td>Vancomycin Oral Liquid</td>\n",
       "      <td>Vancomycin Oral Liquid</td>\n",
       "      <td>2613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NEO*IV*Vancomycin</td>\n",
       "      <td>898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>42618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Vancomycin Antibiotic Lock</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Vancomycin Desensitization</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Vancomycin Enema</td>\n",
       "      <td>208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Vancomycin HCl</td>\n",
       "      <td>22287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Vancomycin Intrathecal</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Vancomycin Intraventricular</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  drug_name_generic                    drug_name_poe  \\\n",
       "0                          Vancocin                         Vancocin   \n",
       "1                        Vancomycin                       Vancomycin   \n",
       "2     Vancomycin 25mg/mL Ophth Soln    Vancomycin 25mg/mL Ophth Soln   \n",
       "3        Vancomycin Desensitization       Vancomycin Desensitization   \n",
       "4                  Vancomycin Enema                       Vancomycin   \n",
       "5                  Vancomycin Enema                      Vancomycin    \n",
       "6                  Vancomycin Enema                 Vancomycin Enema   \n",
       "7         vancoMYCIN for inhalation        vancoMYCIN for inhalation   \n",
       "8   vancoMYCIN for nasal inhalation  vancoMYCIN for nasal inhalation   \n",
       "9    Vancomycin fortified opthalmic   Vancomycin fortified opthalmic   \n",
       "10                   Vancomycin HCl                      Vancomycin    \n",
       "11                   Vancomycin HCl                   Vancomycin HCl   \n",
       "12                   Vancomycin HCl                             None   \n",
       "13    Vancomycin ophthalmic 50mg/ml    Vancomycin ophthalmic 50mg/ml   \n",
       "14   Vancomycin ophthalmic solution   Vancomycin ophthalmic solution   \n",
       "15           Vancomycin Oral Liquid                       Vancomycin   \n",
       "16           Vancomycin Oral Liquid           Vancomycin Oral Liquid   \n",
       "17                             None                             None   \n",
       "18                             None                             None   \n",
       "19                             None                             None   \n",
       "20                             None                             None   \n",
       "21                             None                             None   \n",
       "22                             None                             None   \n",
       "23                             None                             None   \n",
       "24                             None                             None   \n",
       "\n",
       "                               drug  numobs  \n",
       "0                          Vancocin       2  \n",
       "1                        Vancomycin       7  \n",
       "2     Vancomycin 25mg/mL Ophth Soln      24  \n",
       "3        Vancomycin Desensitization       7  \n",
       "4                        Vancomycin       2  \n",
       "5                       Vancomycin        1  \n",
       "6                  Vancomycin Enema      92  \n",
       "7         vancoMYCIN for inhalation       1  \n",
       "8   vancoMYCIN for nasal inhalation       1  \n",
       "9    Vancomycin fortified opthalmic       2  \n",
       "10                      Vancomycin        1  \n",
       "11                   Vancomycin HCl       2  \n",
       "12                   Vancomycin HCl       2  \n",
       "13    Vancomycin ophthalmic 50mg/ml       1  \n",
       "14   Vancomycin ophthalmic solution       1  \n",
       "15                       Vancomycin       7  \n",
       "16           Vancomycin Oral Liquid    2613  \n",
       "17                NEO*IV*Vancomycin     898  \n",
       "18                       Vancomycin   42618  \n",
       "19       Vancomycin Antibiotic Lock      41  \n",
       "20       Vancomycin Desensitization      31  \n",
       "21                 Vancomycin Enema     208  \n",
       "22                   Vancomycin HCl   22287  \n",
       "23           Vancomycin Intrathecal      24  \n",
       "24      Vancomycin Intraventricular       4  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT drug_name_generic, drug_name_poe, drug, count(*) as numobs\n",
    "FROM prescriptions\n",
    "WHERE lower(drug) LIKE '%vanco%'\n",
    "OR lower(drug_name_generic) LIKE '%vanco%'\n",
    "OR lower(drug_name_poe) LIKE '%vanco%'\n",
    "GROUP BY drug, drug_name_generic, drug_name_poe\n",
    "ORDER BY drug_name_generic, drug_name_poe, drug;\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following will be deleted as we are only concerned with vancomycin administered intravenously:\n",
    " - 'Vancomycin 25mg/mL Ophth Soln', - eye use\n",
    " - 'Vancomycin Enema' - rectal use\n",
    " - 'Vancomycin Intrathecal', - cerebral spinal fluid injection\n",
    " - 'Vancomycin Intraventricular', - cerebral shunt injection\n",
    " - 'Vancomycin Oral Liquid', - oral use\n",
    " - 'Vancomycin fortified opthalmic', - eye use\n",
    " - 'Vancomycin ophthalmic 50mg/ml', - eye use\n",
    " - 'Vancomycin ophthalmic solution', - eye use\n",
    " - 'vancoMYCIN for inhalation', - nasal use\n",
    " - 'vancoMYCIN for nasal inhalation' - nasal use\n",
    "\n",
    "From above we keep ...\n",
    "\n",
    "```sql\n",
    "drug in \n",
    "(\n",
    "     'NEO*IV*Vancomycin', 'Vancocin', 'Vancomycin',\n",
    "     'Vancomycin ', 'Vancomycin Antibiotic Lock',\n",
    "     'Vancomycin Desensitization', 'Vancomycin HCl'\n",
    ")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we determine which itemids from labevents to keep."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>row_id</th>\n",
       "      <th>itemid</th>\n",
       "      <th>label</th>\n",
       "      <th>fluid</th>\n",
       "      <th>category</th>\n",
       "      <th>loinc_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>209</td>\n",
       "      <td>51009</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>Blood</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>20578-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_id  itemid       label  fluid   category loinc_code\n",
       "0     209   51009  Vancomycin  Blood  Chemistry    20578-1"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT *\n",
    "FROM d_labitems\n",
    "WHERE lower(label) like '%vanco%'\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From above, we keep everything!\n",
    "\n",
    "Next, we check which itemids from chartevents to keep. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>row_id</th>\n",
       "      <th>itemid</th>\n",
       "      <th>label</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>dbsource</th>\n",
       "      <th>linksto</th>\n",
       "      <th>category</th>\n",
       "      <th>unitname</th>\n",
       "      <th>param_type</th>\n",
       "      <th>conceptid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14767</td>\n",
       "      <td>227453</td>\n",
       "      <td>Vancomycin (Peak)</td>\n",
       "      <td>Vancomycin (Peak)</td>\n",
       "      <td>metavision</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Labs</td>\n",
       "      <td>None</td>\n",
       "      <td>Numeric with tag</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14768</td>\n",
       "      <td>227454</td>\n",
       "      <td>Vancomycin (Random)</td>\n",
       "      <td>Vancomycin (Random)</td>\n",
       "      <td>metavision</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Labs</td>\n",
       "      <td>None</td>\n",
       "      <td>Numeric with tag</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14769</td>\n",
       "      <td>227455</td>\n",
       "      <td>Vancomycin (Trough)</td>\n",
       "      <td>Vancomycin (Trough)</td>\n",
       "      <td>metavision</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Labs</td>\n",
       "      <td>None</td>\n",
       "      <td>Numeric with tag</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13982</td>\n",
       "      <td>225798</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>metavision</td>\n",
       "      <td>inputevents_mv</td>\n",
       "      <td>Antibiotics</td>\n",
       "      <td>dose</td>\n",
       "      <td>Solution</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14134</td>\n",
       "      <td>225697</td>\n",
       "      <td>ZVancomycin (Peak)</td>\n",
       "      <td>ZVancomycin (Peak)</td>\n",
       "      <td>metavision</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Labs</td>\n",
       "      <td>None</td>\n",
       "      <td>Numeric</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>14346</td>\n",
       "      <td>226064</td>\n",
       "      <td>ZVancomycin (Trough)</td>\n",
       "      <td>ZVancomycin (Trough)</td>\n",
       "      <td>metavision</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Labs</td>\n",
       "      <td>None</td>\n",
       "      <td>Numeric</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>14347</td>\n",
       "      <td>226065</td>\n",
       "      <td>ZVancomycin (Random)</td>\n",
       "      <td>ZVancomycin (Random)</td>\n",
       "      <td>metavision</td>\n",
       "      <td>chartevents</td>\n",
       "      <td>Labs</td>\n",
       "      <td>None</td>\n",
       "      <td>Numeric</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_id  itemid                 label          abbreviation    dbsource  \\\n",
       "0   14767  227453     Vancomycin (Peak)     Vancomycin (Peak)  metavision   \n",
       "1   14768  227454   Vancomycin (Random)   Vancomycin (Random)  metavision   \n",
       "2   14769  227455   Vancomycin (Trough)   Vancomycin (Trough)  metavision   \n",
       "3   13982  225798            Vancomycin            Vancomycin  metavision   \n",
       "4   14134  225697    ZVancomycin (Peak)    ZVancomycin (Peak)  metavision   \n",
       "5   14346  226064  ZVancomycin (Trough)  ZVancomycin (Trough)  metavision   \n",
       "6   14347  226065  ZVancomycin (Random)  ZVancomycin (Random)  metavision   \n",
       "\n",
       "          linksto     category unitname        param_type conceptid  \n",
       "0     chartevents         Labs     None  Numeric with tag      None  \n",
       "1     chartevents         Labs     None  Numeric with tag      None  \n",
       "2     chartevents         Labs     None  Numeric with tag      None  \n",
       "3  inputevents_mv  Antibiotics     dose          Solution      None  \n",
       "4     chartevents         Labs     None           Numeric      None  \n",
       "5     chartevents         Labs     None           Numeric      None  \n",
       "6     chartevents         Labs     None           Numeric      None  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT *\n",
    "FROM d_items\n",
    "WHERE lower(label) like '%vanco%'\n",
    "AND dbsource = 'metavision'\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In carevue, Alistair said they don't record vancomycin administration time so we will only consider data from metavision. \n",
    "\n",
    "We also only care about trough values and vancomycin administration.\n",
    "So from above we keep ...\n",
    "\n",
    "```sql\n",
    "itemid in \n",
    "(\n",
    "     227455, 225798, 226064\n",
    ")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define helper functions\n",
    "\n",
    "The first function gets data from each table for a given `subject_id`. It outputs the data as a tuple of dataframes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_data_for_subject(subject_id, query_schema, con):\n",
    "    # lab data\n",
    "    query = query_schema + \"\"\"\n",
    "    select\n",
    "          subject_id, hadm_id, di.itemid, di.label\n",
    "        , charttime, value, valuenum, valueuom, flag\n",
    "    from labevents le\n",
    "    inner join d_labitems di\n",
    "      on le.itemid = di.itemid\n",
    "    where subject_id = {}\n",
    "    and di.itemid = 51009 order by charttime;\n",
    "    \"\"\".format(subject_id)\n",
    "    lab = pd.read_sql_query(query, con)\n",
    "\n",
    "    # charted data\n",
    "    query = query_schema + \"\"\"\n",
    "    select \n",
    "          subject_id, hadm_id, icustay_id\n",
    "        , di.itemid, di.label\n",
    "        , charttime, storetime\n",
    "        , value, valuenum, valueuom\n",
    "        , error, resultstatus\n",
    "    from chartevents ce\n",
    "    inner join d_items di\n",
    "      on ce.itemid = di.itemid\n",
    "    where subject_id = {}\n",
    "    and di.itemid in\n",
    "    (\n",
    "      854, 855, 856, 1354, 5873, 3827, 3828, 2273, 3679, 6261\n",
    "      , 227453, 227454, 227455, 225697, 226064, 226065\n",
    "    )\n",
    "    order by charttime;\n",
    "    \"\"\".format(subject_id)\n",
    "    ce = pd.read_sql_query(query, con)\n",
    "\n",
    "\n",
    "    # input data (mv)\n",
    "    query = query_schema + \"\"\"\n",
    "    select \n",
    "          subject_id, hadm_id, icustay_id\n",
    "        , di.itemid, di.label\n",
    "        , starttime, endtime, storetime\n",
    "        , rate, rateuom\n",
    "        , amount, amountuom\n",
    "        , statusdescription\n",
    "    from inputevents_mv mv\n",
    "    inner join d_items di\n",
    "      on mv.itemid = di.itemid\n",
    "    where subject_id = {} \n",
    "    and di.itemid = 225798\n",
    "    order by starttime;\n",
    "    \"\"\".format(subject_id)\n",
    "    imv = pd.read_sql_query(query, con)\n",
    "\n",
    "    # no vanco data in CV\n",
    "\n",
    "    # prescriptions\n",
    "    query = query_schema + \"\"\"\n",
    "    select *\n",
    "    from prescriptions\n",
    "    where subject_id = {} \n",
    "    and drug in \n",
    "    (\n",
    "          'Vancomycin HCl', 'Vancomycin Oral Liquid', 'NEO*IV*Vancomycin'\n",
    "        , 'Vancomycin', 'Vancomycin Antibiotic Lock' , 'Vancomycin Desensitization'\n",
    "        , 'Vancomycin HCl', 'Vancomycin Intrathecal', '    Vancomycin Intraventricular'\n",
    "        , 'Vancocin'\n",
    "    )\n",
    "    order by startdate;\n",
    "    \"\"\".format(subject_id)\n",
    "    pr = pd.read_sql_query(query, con)\n",
    "\n",
    "    # transfers info\n",
    "    query = query_schema + \"\"\"\n",
    "    select * from transfers\n",
    "    where subject_id = {};\n",
    "    \"\"\".format(subject_id)\n",
    "    tr = pd.read_sql_query(query, con)\n",
    "    \n",
    "    return lab, ce, imv, pr, tr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The second function combines the individual dataframes into a single dataframe. It does this by combining columns together - so while data in a single column no longer consistently represents the same concept, it is easier to read."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# union the above dataframes together by renaming columns etc\n",
    "# column names are no longer 100% correct but it's easier to interpret the data this way\n",
    "def combine_into_single_dataframe(lab, ce, imv, pr, tr):\n",
    "    cols = ['subject_id', 'hadm_id', 'source', 'label', 'charttime', 'endtime', 'value', 'valuenum']\n",
    "\n",
    "    lab['endtime'] = None\n",
    "    lab['source'] = 'lab'\n",
    "\n",
    "    ce['endtime'] = None\n",
    "    ce['source'] = 'chart'\n",
    "    \n",
    "    imv['charttime'] = imv['starttime']\n",
    "    imv['value'] = imv['amount'].astype(str)\n",
    "    imv['valuenum'] = imv['rate']\n",
    "    imv['source'] = 'inputs'\n",
    "\n",
    "    pr['charttime'] = pr['startdate']\n",
    "    pr['endtime'] = pr['enddate']\n",
    "    pr['value'] = pr['route']\n",
    "    pr['label'] = pr['drug']\n",
    "    pr['valuenum'] = pr['dose_val_rx']\n",
    "    pr['source'] = 'poe'\n",
    "\n",
    "    # add in the start/end time of ICU\n",
    "    icu_admit = tr.loc[ (~tr['curr_careunit'].isnull()), :].copy()\n",
    "    icu_disch = tr.loc[ (~tr['curr_careunit'].isnull()), :].copy()\n",
    "\n",
    "    icu_admit['source'] = 'icu admit'\n",
    "    icu_disch['source'] = 'icu disch'\n",
    "    icu_admit['endtime'] = None\n",
    "    icu_disch['endtime'] = None\n",
    "    icu_admit['valuenum'] = None\n",
    "    icu_disch['valuenum'] = None\n",
    "    \n",
    "    icu_rename_dict = {'icustay_id': 'label', 'curr_careunit': 'value'}\n",
    "    icu_admit.rename( icu_rename_dict, axis=1, inplace=True )\n",
    "    icu_disch.rename( icu_rename_dict, axis=1, inplace=True )\n",
    "    \n",
    "    # set charttine - for admission, its intime, for discharge, its outtime\n",
    "    icu_admit.rename({'intime': 'charttime'}, axis=1, inplace=True)\n",
    "    icu_disch.rename({'outtime': 'charttime'}, axis=1, inplace=True)\n",
    "\n",
    "    df = pd.concat([ lab[cols], ce[cols], imv[cols], pr[cols], icu_admit[cols], icu_disch[cols] ],\n",
    "                   axis=0, ignore_index=True )\n",
    "\n",
    "    df.sort_values('charttime', ascending=True, inplace=True)\n",
    "    df.reset_index(drop=True, inplace=True)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source</th>\n",
       "      <th>label</th>\n",
       "      <th>charttime</th>\n",
       "      <th>endtime</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-18 00:00:00</td>\n",
       "      <td>2168-06-19 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>icu admit</td>\n",
       "      <td>240678</td>\n",
       "      <td>2168-06-18 01:08:37</td>\n",
       "      <td></td>\n",
       "      <td>TSICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-18 09:04:00</td>\n",
       "      <td>2168-06-18 09:05:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-18 20:48:00</td>\n",
       "      <td>2168-06-18 20:49:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-19 00:00:00</td>\n",
       "      <td>2168-06-21 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-19 04:25:00</td>\n",
       "      <td></td>\n",
       "      <td>6.1</td>\n",
       "      <td>6.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin (Random)</td>\n",
       "      <td>2168-06-19 05:25:00</td>\n",
       "      <td></td>\n",
       "      <td>6.1</td>\n",
       "      <td>6.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-19 08:29:00</td>\n",
       "      <td>2168-06-19 08:30:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-19 16:24:00</td>\n",
       "      <td>2168-06-19 16:25:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>icu disch</td>\n",
       "      <td>240678</td>\n",
       "      <td>2168-06-19 22:20:01</td>\n",
       "      <td></td>\n",
       "      <td>TSICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-20 15:25:00</td>\n",
       "      <td></td>\n",
       "      <td>9.2</td>\n",
       "      <td>9.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-21 00:00:00</td>\n",
       "      <td>2168-06-24 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-22 18:30:00</td>\n",
       "      <td></td>\n",
       "      <td>10.6</td>\n",
       "      <td>10.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-24 04:58:00</td>\n",
       "      <td></td>\n",
       "      <td>57.4</td>\n",
       "      <td>57.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-25 04:14:00</td>\n",
       "      <td></td>\n",
       "      <td>35.4</td>\n",
       "      <td>35.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-28 00:00:00</td>\n",
       "      <td>2168-06-28 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2168-06-28 04:51:00</td>\n",
       "      <td></td>\n",
       "      <td>8.4</td>\n",
       "      <td>8.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# pick the subject\n",
    "subject_id = 97920\n",
    "\n",
    "# get data for this subject\n",
    "lab, ce, imv, pr, tr = get_data_for_subject(subject_id, query_schema, con)\n",
    "# combine dataframes together\n",
    "df = combine_into_single_dataframe(lab, ce, imv, pr, tr)\n",
    "\n",
    "# drop subject/hadm_id\n",
    "df.drop(['subject_id', 'hadm_id'], axis=1, inplace=True)\n",
    "\n",
    "# display dataframe\n",
    "display(HTML(df.to_html().replace('None','')))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above makes sense - summarizing the flow:\n",
    "\n",
    "* Prescription on the 18th\n",
    "* Admitted to the ICU on the 18th (1am)\n",
    "* IV infusion on the 18th (9am, 8pm)\n",
    "* Prescription on the 19th-21st\n",
    "* Measured vanco on 19th\n",
    "* IV on the 19th (8am, 4pm)\n",
    "* Discharged from the ICU\n",
    "* POE/lab measurements continue from 20th onward\n",
    "\n",
    "We can try another subject."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source</th>\n",
       "      <th>label</th>\n",
       "      <th>charttime</th>\n",
       "      <th>endtime</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>icu admit</td>\n",
       "      <td>288166</td>\n",
       "      <td>2179-12-10 20:15:49</td>\n",
       "      <td></td>\n",
       "      <td>MICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-11 00:00:00</td>\n",
       "      <td>2179-12-12 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-11 00:00:00</td>\n",
       "      <td>2179-12-12 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-11 01:20:00</td>\n",
       "      <td>2179-12-11 01:21:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-11 03:44:00</td>\n",
       "      <td></td>\n",
       "      <td>43.9</td>\n",
       "      <td>43.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin (Random)</td>\n",
       "      <td>2179-12-11 03:44:00</td>\n",
       "      <td></td>\n",
       "      <td>43.9</td>\n",
       "      <td>43.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-12 00:00:00</td>\n",
       "      <td>2179-12-17 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-12 03:29:00</td>\n",
       "      <td></td>\n",
       "      <td>20.6</td>\n",
       "      <td>20.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin (Random)</td>\n",
       "      <td>2179-12-12 03:29:00</td>\n",
       "      <td></td>\n",
       "      <td>20.6</td>\n",
       "      <td>20.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-13 06:00:00</td>\n",
       "      <td></td>\n",
       "      <td>14.3</td>\n",
       "      <td>14.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin (Random)</td>\n",
       "      <td>2179-12-13 06:00:00</td>\n",
       "      <td></td>\n",
       "      <td>14.3</td>\n",
       "      <td>14.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-13 08:00:00</td>\n",
       "      <td>2179-12-13 08:01:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-14 05:41:00</td>\n",
       "      <td></td>\n",
       "      <td>15.3</td>\n",
       "      <td>15.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin (Random)</td>\n",
       "      <td>2179-12-14 05:41:00</td>\n",
       "      <td></td>\n",
       "      <td>15.3</td>\n",
       "      <td>15.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-14 08:30:00</td>\n",
       "      <td>2179-12-14 08:31:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin (Random)</td>\n",
       "      <td>2179-12-15 05:54:00</td>\n",
       "      <td></td>\n",
       "      <td>15.7</td>\n",
       "      <td>15.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-15 05:54:00</td>\n",
       "      <td></td>\n",
       "      <td>15.7</td>\n",
       "      <td>15.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin (Trough)</td>\n",
       "      <td>2179-12-16 06:07:00</td>\n",
       "      <td></td>\n",
       "      <td>16.8</td>\n",
       "      <td>16.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-16 06:07:00</td>\n",
       "      <td></td>\n",
       "      <td>16.8</td>\n",
       "      <td>16.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-16 08:00:00</td>\n",
       "      <td>2179-12-16 08:01:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-17 00:00:00</td>\n",
       "      <td>2179-12-20 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-17 06:01:00</td>\n",
       "      <td></td>\n",
       "      <td>24.7</td>\n",
       "      <td>24.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin (Trough)</td>\n",
       "      <td>2179-12-17 06:01:00</td>\n",
       "      <td></td>\n",
       "      <td>24.7</td>\n",
       "      <td>24.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin (Random)</td>\n",
       "      <td>2179-12-18 05:43:00</td>\n",
       "      <td></td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-18 05:43:00</td>\n",
       "      <td></td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>icu disch</td>\n",
       "      <td>288166</td>\n",
       "      <td>2179-12-18 20:51:40</td>\n",
       "      <td></td>\n",
       "      <td>MICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2179-12-20 11:00:00</td>\n",
       "      <td></td>\n",
       "      <td>27.3</td>\n",
       "      <td>27.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# pick the subject\n",
    "subject_id = 94864\n",
    "\n",
    "# get data for this subject\n",
    "lab, ce, imv, pr, tr = get_data_for_subject(subject_id, query_schema, con)\n",
    "# combine dataframes together\n",
    "df = combine_into_single_dataframe(lab, ce, imv, pr, tr)\n",
    "\n",
    "# drop subject/hadm_id\n",
    "df.drop(['subject_id', 'hadm_id'], axis=1, inplace=True)\n",
    "\n",
    "# display dataframe\n",
    "display(HTML(df.to_html().replace('None','')))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above is mostly sensible - except perhaps for rows 16/17. Here, we can an increase in vancomycin levels, but we don't have an associated dose to explain it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source</th>\n",
       "      <th>label</th>\n",
       "      <th>charttime</th>\n",
       "      <th>endtime</th>\n",
       "      <th>value</th>\n",
       "      <th>valuenum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>icu admit</td>\n",
       "      <td>228283</td>\n",
       "      <td>2197-12-06 07:13:48</td>\n",
       "      <td></td>\n",
       "      <td>MICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin HCl</td>\n",
       "      <td>2197-12-11 00:00:00</td>\n",
       "      <td>2197-12-15 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin/Random</td>\n",
       "      <td>2197-12-12 07:31:00</td>\n",
       "      <td></td>\n",
       "      <td>10.4</td>\n",
       "      <td>10.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2197-12-12 07:31:00</td>\n",
       "      <td></td>\n",
       "      <td>10.4</td>\n",
       "      <td>10.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin HCl</td>\n",
       "      <td>2197-12-14 00:00:00</td>\n",
       "      <td>2197-12-15 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>icu disch</td>\n",
       "      <td>228283</td>\n",
       "      <td>2197-12-26 15:26:37</td>\n",
       "      <td></td>\n",
       "      <td>MICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>icu admit</td>\n",
       "      <td>248896</td>\n",
       "      <td>2198-08-02 04:50:02</td>\n",
       "      <td></td>\n",
       "      <td>MICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>icu disch</td>\n",
       "      <td>248896</td>\n",
       "      <td>2198-08-04 01:55:49</td>\n",
       "      <td></td>\n",
       "      <td>MICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>icu admit</td>\n",
       "      <td>248896</td>\n",
       "      <td>2198-08-04 13:34:15</td>\n",
       "      <td></td>\n",
       "      <td>SICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2198-08-07 00:00:00</td>\n",
       "      <td>2198-08-10 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin/Random</td>\n",
       "      <td>2198-08-08 17:46:00</td>\n",
       "      <td></td>\n",
       "      <td>16.3</td>\n",
       "      <td>16.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2198-08-08 17:46:00</td>\n",
       "      <td></td>\n",
       "      <td>16.3</td>\n",
       "      <td>16.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2198-08-10 00:00:00</td>\n",
       "      <td>2198-08-14 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2198-08-11 05:00:00</td>\n",
       "      <td></td>\n",
       "      <td>22.0</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin/Random</td>\n",
       "      <td>2198-08-11 05:00:00</td>\n",
       "      <td></td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin/Random</td>\n",
       "      <td>2198-08-12 06:59:00</td>\n",
       "      <td></td>\n",
       "      <td>11.5</td>\n",
       "      <td>11.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2198-08-12 06:59:00</td>\n",
       "      <td></td>\n",
       "      <td>11.5</td>\n",
       "      <td>11.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2198-08-13 05:33:00</td>\n",
       "      <td></td>\n",
       "      <td>11.6</td>\n",
       "      <td>11.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin/Random</td>\n",
       "      <td>2198-08-13 05:33:00</td>\n",
       "      <td></td>\n",
       "      <td>11.6</td>\n",
       "      <td>11.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2198-08-14 00:00:00</td>\n",
       "      <td>2198-08-21 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin/Random</td>\n",
       "      <td>2198-08-14 00:46:00</td>\n",
       "      <td></td>\n",
       "      <td>12.1</td>\n",
       "      <td>12.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2198-08-14 00:46:00</td>\n",
       "      <td></td>\n",
       "      <td>12.1</td>\n",
       "      <td>12.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2198-08-15 19:00:00</td>\n",
       "      <td></td>\n",
       "      <td>17.8</td>\n",
       "      <td>17.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>chart</td>\n",
       "      <td>Vancomycin/Random</td>\n",
       "      <td>2198-08-15 19:00:00</td>\n",
       "      <td></td>\n",
       "      <td>17.8</td>\n",
       "      <td>17.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>icu disch</td>\n",
       "      <td>248896</td>\n",
       "      <td>2198-09-20 16:55:55</td>\n",
       "      <td></td>\n",
       "      <td>SICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2198-09-24 00:00:00</td>\n",
       "      <td>2198-10-15 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>lab</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2198-09-26 05:15:00</td>\n",
       "      <td></td>\n",
       "      <td>15.3</td>\n",
       "      <td>15.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>icu admit</td>\n",
       "      <td>273842</td>\n",
       "      <td>2198-11-01 22:39:10</td>\n",
       "      <td></td>\n",
       "      <td>MICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>icu disch</td>\n",
       "      <td>273842</td>\n",
       "      <td>2198-11-12 00:07:12</td>\n",
       "      <td></td>\n",
       "      <td>MICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2199-10-20 00:00:00</td>\n",
       "      <td>2199-10-23 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>icu admit</td>\n",
       "      <td>259107</td>\n",
       "      <td>2199-10-20 12:06:08</td>\n",
       "      <td></td>\n",
       "      <td>CCU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2199-10-20 22:33:00</td>\n",
       "      <td>2199-10-20 22:34:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>icu disch</td>\n",
       "      <td>259107</td>\n",
       "      <td>2199-10-21 10:18:24</td>\n",
       "      <td></td>\n",
       "      <td>CCU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2199-12-22 00:00:00</td>\n",
       "      <td>2199-12-23 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>icu admit</td>\n",
       "      <td>235292</td>\n",
       "      <td>2199-12-24 19:52:27</td>\n",
       "      <td></td>\n",
       "      <td>SICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>poe</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2199-12-25 00:00:00</td>\n",
       "      <td>2199-12-26 00:00:00</td>\n",
       "      <td>IV</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2199-12-25 01:00:00</td>\n",
       "      <td>2199-12-25 01:01:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2199-12-25 08:00:00</td>\n",
       "      <td>2199-12-25 08:01:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2199-12-25 20:00:00</td>\n",
       "      <td>2199-12-25 20:01:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>inputs</td>\n",
       "      <td>Vancomycin</td>\n",
       "      <td>2199-12-26 08:00:00</td>\n",
       "      <td>2199-12-26 08:01:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>icu disch</td>\n",
       "      <td>235292</td>\n",
       "      <td>2200-01-07 21:24:42</td>\n",
       "      <td></td>\n",
       "      <td>SICU</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# pick the subject\n",
    "subject_id = 357\n",
    "\n",
    "# get data for this subject\n",
    "lab, ce, imv, pr, tr = get_data_for_subject(subject_id, query_schema, con)\n",
    "# combine dataframes together\n",
    "df = combine_into_single_dataframe(lab, ce, imv, pr, tr)\n",
    "\n",
    "# drop subject/hadm_id\n",
    "df.drop(['subject_id', 'hadm_id'], axis=1, inplace=True)\n",
    "\n",
    "# display dataframe\n",
    "\n",
    "display(HTML(df.to_html().replace('None','')))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This patient is admitted under the carevue system, and unfortunately we do not have documentation of IV antibiotics for these patients. However, we note that the prescriptions ('poe') appear to match the charted data.\n",
    "\n",
    "For the last few rows, it is odd that there is no measurement of vancomycin between the 25th-26th."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conclusion\n",
    "\n",
    "Care should be taken when comparing dosing of vancomycin given with the value measured. Reducing the time resolution required for a study (e.g. to the day) may help smooth over slight inconsistencies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "con.close()"
   ]
  }
 ],
 "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}