data_extraction_mimic.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Extraction from MIMIC."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Contents:\n",
    "\n",
    "* (A) Locating the required data\n",
    "* (B) Extracting the data\n",
    "* (C) Post-processing of data (involves combining with cohort information from 'cohort_selection_mimic.ipynb')\n",
    "\n",
    "We use two mysql libraries:\n",
    "\n",
    "* 'sql' is simple for scripting, with line/cell magic. \n",
    "* 'MySQLdb' is required for more complex operations (e.g. parameter binding with python lists, and storing results directly in graphlab/python dataframes).\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "import graphlab\n",
    "import numpy as np\n",
    "import pickle\n",
    "from collections import OrderedDict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/rusty/anaconda3/lib/python2.7/site-packages/IPython/config.py:13: ShimWarning: The `IPython.config` package has been deprecated since IPython 4.0. You should import from traitlets.config instead.\n",
      "  \"You should import from traitlets.config instead.\", ShimWarning)\n",
      "/home/rusty/anaconda3/lib/python2.7/site-packages/IPython/utils/traitlets.py:5: UserWarning: IPython.utils.traitlets has moved to a top-level traitlets package.\n",
      "  warn(\"IPython.utils.traitlets has moved to a top-level traitlets package.\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This non-commercial license of GraphLab Create for academic use is assigned to cm1788@bristol.ac.uk and will expire on October 04, 2019.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[INFO] graphlab.cython.cy_server: GraphLab Create v2.1 started. Logging: /tmp/graphlab_server_1553264788.log\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 rows affected.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/rusty/anaconda3/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:1569: Warning: '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead\n",
      "  cursor.execute('SELECT @@tx_isolation')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql mysql://root:mysql2016@localhost/MIMIC?unix_socket=/run/mysqld/mysqld.sock\n",
    "%sql USE MIMIC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (A) Locating the required data.\n",
    "\n",
    "A number of different variables are required to assess the nurse led discharge (NLD) criteria. Here we locate the ITEMIDs corresponding to these variables, which will be used to extract patient data from chartevents.\n",
    "\n",
    "For simplicity we focus on (dbsource=)'Metavision' only since data storage within 'Carevue' is less coherent. Because the study compares with a general intensive care unit (GICU) from the UK, we take only patients with (first_careunit=)'MICU' or 'SICU' since this patient subset approximately corresponds to the GICU population.\n",
    "\n",
    "The required variables are:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "> fio2: fractional inspired O2\n",
    "> resp: respiratory rate\n",
    "> po2/pco2: partial pressure of arterial O2/CO2\n",
    "> temp: temperature\n",
    "> hr: heart rate\n",
    "> bp: systolic blood pressure \n",
    "> k: potassium\n",
    "> na: sodium\n",
    "> hco3: bicarbonate\n",
    "> spo2: O2 saturation\n",
    "> bun: blood urea nitrogen (urea)\n",
    "> airway: patent airway (we determine by looking for presence of tracheostemy tube)\n",
    "> gcs: glasgow coma scale\n",
    "> creatinine: creatinine\n",
    "> pain: pain score\n",
    "> urine: urine output\n",
    "> haemo: haemoglobin    \n",
    "> peep: positive end expiratory pressure (determine mech vent)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### To locate these variables we proceed as follows: \n",
    "\n",
    "We define these search terms:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "> fio2: '%fraction%', '%fio2%'\n",
    "> resp: '%resp%', '%rate%'\n",
    "> po2/pco2: '%partial%', '%o2%', '%oxygen%'\n",
    "> temp: '%temp%'\n",
    "> hr: '%heart%', '%hr%'\n",
    "> bp: '%blood%', '%systolic%'\n",
    "> k: '%potassium%'\n",
    "> na: '%sodium%'\n",
    "> hco3: '%hc03%'\n",
    "> spo2: '%saturation%', '%o2%'\n",
    "> bun: '%urea%', '%bun%'\n",
    "> airway: '%ett%', '%trach%'\n",
    "> gcs: '%gcs%'\n",
    "> creatinine: '%creatinine%'\n",
    "> pain: '%pain%'\n",
    "> urine: '%urine%'\n",
    "> haemo: '%hemog%'\n",
    "> peep: '%peep%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And search for ITEMIDS with LABELS that match the search terms using the following query (replacing the search terms for each variable with those above):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\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",
       "        <th>MAPPING_DEEP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12804</td>\n",
       "        <td>223835</td>\n",
       "        <td>Inspired O2 Fraction</td>\n",
       "        <td>FiO2</td>\n",
       "        <td>metavision</td>\n",
       "        <td>chartevents</td>\n",
       "        <td>Respiratory</td>\n",
       "        <td>None</td>\n",
       "        <td>Numeric</td>\n",
       "        <td>None</td>\n",
       "        <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14083</td>\n",
       "        <td>225628</td>\n",
       "        <td>CK-MB fraction (%)</td>\n",
       "        <td>CK-MB fraction (%)</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",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14515</td>\n",
       "        <td>226754</td>\n",
       "        <td>FiO2ApacheIIValue</td>\n",
       "        <td>FiO2ApacheIIValue</td>\n",
       "        <td>metavision</td>\n",
       "        <td>chartevents</td>\n",
       "        <td>Scores - APACHE II</td>\n",
       "        <td>%</td>\n",
       "        <td>Numeric</td>\n",
       "        <td>None</td>\n",
       "        <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14685</td>\n",
       "        <td>227008</td>\n",
       "        <td>Ejection Fraction</td>\n",
       "        <td>Ejection Fraction</td>\n",
       "        <td>metavision</td>\n",
       "        <td>chartevents</td>\n",
       "        <td>Scores - APACHE IV (2)</td>\n",
       "        <td>%</td>\n",
       "        <td>Numeric</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14686</td>\n",
       "        <td>227009</td>\n",
       "        <td>FiO2_ApacheIV_old</td>\n",
       "        <td>FiO2_ApacheIV_old</td>\n",
       "        <td>metavision</td>\n",
       "        <td>chartevents</td>\n",
       "        <td>Scores - APACHE IV (2)</td>\n",
       "        <td>None</td>\n",
       "        <td>Numeric</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14687</td>\n",
       "        <td>227010</td>\n",
       "        <td>FiO2_ApacheIV</td>\n",
       "        <td>FiO2_ApacheIV</td>\n",
       "        <td>metavision</td>\n",
       "        <td>chartevents</td>\n",
       "        <td>Scores - APACHE IV (2)</td>\n",
       "        <td>%</td>\n",
       "        <td>Numeric</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(12804, 223835, 'Inspired O2 Fraction', 'FiO2', 'metavision', 'chartevents', 'Respiratory', 'None', 'Numeric', None, 26L),\n",
       " (14083, 225628, 'CK-MB fraction (%)', 'CK-MB fraction (%)', 'metavision', 'chartevents', 'Labs', 'None', 'Numeric', None, None),\n",
       " (14515, 226754, 'FiO2ApacheIIValue', 'FiO2ApacheIIValue', 'metavision', 'chartevents', 'Scores - APACHE II', '%', 'Numeric', None, 26L),\n",
       " (14685, 227008, 'Ejection Fraction', 'Ejection Fraction', 'metavision', 'chartevents', 'Scores - APACHE IV (2)', '%', 'Numeric', None, None),\n",
       " (14686, 227009, 'FiO2_ApacheIV_old', 'FiO2_ApacheIV_old', 'metavision', 'chartevents', 'Scores - APACHE IV (2)', 'None', 'Numeric', None, None),\n",
       " (14687, 227010, 'FiO2_ApacheIV', 'FiO2_ApacheIV', 'metavision', 'chartevents', 'Scores - APACHE IV (2)', '%', 'Numeric', None, None)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM D_ITEMS WHERE (LABEL LIKE '%fraction%' OR LABEL LIKE '%fio2%') AND DBSOURCE='metavision'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For each variable we visually inspect the search ouptut and select the ITEMS that appear relevant. \n",
    "\n",
    "*For example, for FiO2 we select 226754, 227009, 227010,223835 as candidates.*\n",
    "\n",
    "We then measure the approximate frequnecy of the candidate variables on a random subset of the data (i.e. how many ICUSTAYS have at least one recording of each ITEMID)..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sub_limit = 100  ## subset size: number of ICUSTAYS randomly selected  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ITEMID</th>\n",
       "        <th>LABEL</th>\n",
       "        <th>STAY_COUNT</th>\n",
       "        <th>FREQ</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>223835</td>\n",
       "        <td>Inspired O2 Fraction</td>\n",
       "        <td>47</td>\n",
       "        <td>0.4700</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(223835, 'Inspired O2 Fraction', 47L, Decimal('0.4700'))]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT D.ITEMID, D.LABEL, COUNT(DISTINCT(II.ICUSTAY_ID)) AS STAY_COUNT, (COUNT(DISTINCT(II.ICUSTAY_ID))/(:sub_limit)) AS FREQ \\\n",
    "FROM CHARTEVENTS C \\\n",
    "INNER JOIN ( \\\n",
    "    SELECT * FROM ( \\\n",
    "        SELECT * \\\n",
    "        FROM ICUSTAYS I \\\n",
    "        WHERE I.DBSOURCE='metavision' AND (I.FIRST_CAREUNIT='MICU' or I.FIRST_CAREUNIT='SICU') \\\n",
    "        ORDER BY RAND() \\\n",
    "        LIMIT :sub_limit) AS II_sub \\\n",
    "    ORDER BY II_sub.SUBJECT_ID, II_sub.HADM_ID, II_sub.ICUSTAY_ID \\\n",
    "            ) AS II \\\n",
    "ON (C.SUBJECT_ID=II.SUBJECT_ID \\\n",
    "    AND C.HADM_ID=II.HADM_ID \\\n",
    "    AND C.ICUSTAY_ID=II.ICUSTAY_ID) \\\n",
    "INNER JOIN D_ITEMS D \\\n",
    "ON C.ITEMID=D.ITEMID \\\n",
    "WHERE D.ITEMID IN (226754, 227009, 227010, 223835) \\\n",
    "GROUP BY D.ITEMID"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "..and manually inspect the first few instances of each ITEM to confirm it contains the required data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ROW_ID</th>\n",
       "        <th>SUBJECT_ID</th>\n",
       "        <th>HADM_ID</th>\n",
       "        <th>ICUSTAY_ID</th>\n",
       "        <th>ITEMID</th>\n",
       "        <th>CHARTTIME</th>\n",
       "        <th>STORETIME</th>\n",
       "        <th>CGID</th>\n",
       "        <th>VALUE</th>\n",
       "        <th>VALUENUM</th>\n",
       "        <th>VALUEUOM</th>\n",
       "        <th>WARNING</th>\n",
       "        <th>ERROR</th>\n",
       "        <th>RESULTSTATUS</th>\n",
       "        <th>STOPPED</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>335</td>\n",
       "        <td>34</td>\n",
       "        <td>144319</td>\n",
       "        <td>290505</td>\n",
       "        <td>223835</td>\n",
       "        <td>2191-02-23 07:31:00</td>\n",
       "        <td>2191-02-23 07:35:00</td>\n",
       "        <td>16924</td>\n",
       "        <td>60</td>\n",
       "        <td>60.0</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>430</td>\n",
       "        <td>34</td>\n",
       "        <td>144319</td>\n",
       "        <td>290505</td>\n",
       "        <td>223835</td>\n",
       "        <td>2191-02-23 11:00:00</td>\n",
       "        <td>2191-02-23 11:04:00</td>\n",
       "        <td>14913</td>\n",
       "        <td>60</td>\n",
       "        <td>60.0</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>706</td>\n",
       "        <td>36</td>\n",
       "        <td>165660</td>\n",
       "        <td>241249</td>\n",
       "        <td>223835</td>\n",
       "        <td>2134-05-12 07:09:00</td>\n",
       "        <td>2134-05-12 07:09:00</td>\n",
       "        <td>17525</td>\n",
       "        <td>100</td>\n",
       "        <td>100.0</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>789</td>\n",
       "        <td>36</td>\n",
       "        <td>165660</td>\n",
       "        <td>241249</td>\n",
       "        <td>223835</td>\n",
       "        <td>2134-05-12 12:00:00</td>\n",
       "        <td>2134-05-12 13:56:00</td>\n",
       "        <td>17525</td>\n",
       "        <td>100</td>\n",
       "        <td>100.0</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>877</td>\n",
       "        <td>36</td>\n",
       "        <td>165660</td>\n",
       "        <td>241249</td>\n",
       "        <td>223835</td>\n",
       "        <td>2134-05-12 16:13:00</td>\n",
       "        <td>2134-05-12 16:27:00</td>\n",
       "        <td>18428</td>\n",
       "        <td>100</td>\n",
       "        <td>100.0</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(335L, 34, 144319, 290505, 223835, datetime.datetime(2191, 2, 23, 7, 31), datetime.datetime(2191, 2, 23, 7, 35), 16924, '60', 60.0, None, 0, 0, None, None),\n",
       " (430L, 34, 144319, 290505, 223835, datetime.datetime(2191, 2, 23, 11, 0), datetime.datetime(2191, 2, 23, 11, 4), 14913, '60', 60.0, None, 0, 0, None, None),\n",
       " (706L, 36, 165660, 241249, 223835, datetime.datetime(2134, 5, 12, 7, 9), datetime.datetime(2134, 5, 12, 7, 9), 17525, '100', 100.0, None, 0, 0, None, None),\n",
       " (789L, 36, 165660, 241249, 223835, datetime.datetime(2134, 5, 12, 12, 0), datetime.datetime(2134, 5, 12, 13, 56), 17525, '100', 100.0, None, 0, 0, None, None),\n",
       " (877L, 36, 165660, 241249, 223835, datetime.datetime(2134, 5, 12, 16, 13), datetime.datetime(2134, 5, 12, 16, 27), 18428, '100', 100.0, None, 0, 0, None, None)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM CHARTEVENTS WHERE ITEMID=223835 LIMIT 5 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*For example ITEMID 223835 (Inspired O2 Fraction) contains numeric fractional data, and is measured with a reasonably high frequency (~0.4 to 0.6). We connclude that this is a relevant ITEM.*\t"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### By the above procedure we identify the following ITEMIDS corresponding to the required variables:\n",
    "\n",
    "* Some ITEMIDS may not be in (frequent) use in the database - we select them anyway for completeness.\n",
    "* Urine output may be located in either CHARTEVENTS or OUTPUTEVENTS (we check both)."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "> fio2: (226754, 227009, 227010,223835)\n",
    "> resp: (220210, 224688, 224689, 224690)\n",
    "> po2:  (226770,227039,227516,220224)  ## 227516 is venous - excluded in subsequent script.\n",
    "> pco2: (220235,226062,226063,227036)  ## 226062,226063 are venous - excluded in subsequent script.\n",
    "> temp: (223761, 223762, 224027) \n",
    "> hr: (220045)\n",
    "> bp: (220050, 220059, 220179, 224167, 225309, 227243, 226850, 226852, 228151)\n",
    "\n",
    "> k:  (220640, 227464, 227442, 226772, 226535)\n",
    "> na: (220645, 226534, 226776)\n",
    "> hco3: (224826, 226759, 227443)\n",
    "> spo2: (220227, 220277, 226860,226861,226862,226863,226865,228232)\n",
    "> bun:  (225624, 227000, 227001)\n",
    "> airway: (223838, 224832, 224391, 227810,223837, 224829)\n",
    "\n",
    "> gcs:  (220739, 223900, 223901, 226755, 226756, 226757, 226758, 227011, 227012, 227013, 227014,228112)\n",
    "> creatinine: (220615, 226752, 227005)\n",
    "> pain: (223791, 227881)\n",
    "\n",
    "? urine (chartevents): (227519, 227059)\n",
    "? urine (outputevents): (226566, 226627, 226631)\n",
    "\n",
    "> haemoglobin: (220228)\n",
    "> peep: (220339, 224699, 224700)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**As a single list this is** (220739, 223900, 223901, 226755, 226756, 226757, 226758, 227011, 227012, 227013, 227014,228112, 220615, 226752, 227005, 223791, 227881, 227519, 227059, 220640, 227464, 227442, 226772, 226535, 220645, 226534, 226776, 224826, 226759, 227443,220227, 220277, 226860,226861,226862,226863,226865,228232, 225624, 227000, 227001, 223838, 224832, 224391, 227810,223837, 224829, 226754, 227009, 227010,223835, 220210, 224688, 224689, 224690, 226770,227039,227516,220224,220235,226062,226063,227036, 223761, 223762, 224027, 220045, 220228, 220339, 224699, 224700, 220050, 220059, 220179, 224167, 225309, 227243, 226850, 226852, 228151) ** for CHARTEVENTS.**\n",
    "\n",
    "** And ** (226566, 226627, 226631) **for OUTPUTEVENTS (urine output only).**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-------------------------------------\n",
    "## (B) Extracting the data\n",
    "\n",
    "Having located the relevant ITEMIDs we now:\n",
    "\n",
    "* Calculate their usage frequency from a larger random sample of ICUSTAYS\n",
    "* Define a cuttoff frequency\n",
    "* Extract and save all measurements of the remaining variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sub_limit=1000  ## size of random sample"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As before we calculate how many ICUSTAYS have at least one measurement of each ITEM, and convert this to an occurence frequnecy (FREQ). This time we calculate for all ITEMIDs simultaneoulsy, and sort the results by ascending occurence frequency. For CHARTEVENTS:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "43 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ITEMID</th>\n",
       "        <th>LABEL</th>\n",
       "        <th>STAY_COUNT</th>\n",
       "        <th>FREQ</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>227243</td>\n",
       "        <td>Manual Blood Pressure Systolic Right</td>\n",
       "        <td>5</td>\n",
       "        <td>0.0050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220059</td>\n",
       "        <td>Pulmonary Artery Pressure systolic</td>\n",
       "        <td>8</td>\n",
       "        <td>0.0080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>224167</td>\n",
       "        <td>Manual Blood Pressure Systolic Left</td>\n",
       "        <td>11</td>\n",
       "        <td>0.0110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>228232</td>\n",
       "        <td>PAR-Oxygen saturation</td>\n",
       "        <td>22</td>\n",
       "        <td>0.0220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>223762</td>\n",
       "        <td>Temperature Celsius</td>\n",
       "        <td>40</td>\n",
       "        <td>0.0400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>227516</td>\n",
       "        <td>PO2 (Mixed Venous)</td>\n",
       "        <td>46</td>\n",
       "        <td>0.0460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>225309</td>\n",
       "        <td>ART BP Systolic</td>\n",
       "        <td>54</td>\n",
       "        <td>0.0540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>224829</td>\n",
       "        <td>Trach Tube Type</td>\n",
       "        <td>55</td>\n",
       "        <td>0.0550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>226062</td>\n",
       "        <td>Venous CO2 Pressure</td>\n",
       "        <td>120</td>\n",
       "        <td>0.1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>226063</td>\n",
       "        <td>Venous O2 Pressure</td>\n",
       "        <td>120</td>\n",
       "        <td>0.1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>227810</td>\n",
       "        <td>ETT Re-taped</td>\n",
       "        <td>134</td>\n",
       "        <td>0.1340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>226534</td>\n",
       "        <td>Sodium (whole blood)</td>\n",
       "        <td>152</td>\n",
       "        <td>0.1520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>224391</td>\n",
       "        <td>ETT Mark (location)</td>\n",
       "        <td>159</td>\n",
       "        <td>0.1590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220227</td>\n",
       "        <td>Arterial O2 Saturation</td>\n",
       "        <td>197</td>\n",
       "        <td>0.1970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>227464</td>\n",
       "        <td>Potassium (whole blood)</td>\n",
       "        <td>204</td>\n",
       "        <td>0.2040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>224700</td>\n",
       "        <td>Total PEEP Level</td>\n",
       "        <td>254</td>\n",
       "        <td>0.2540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220050</td>\n",
       "        <td>Arterial Blood Pressure systolic</td>\n",
       "        <td>319</td>\n",
       "        <td>0.3190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>223838</td>\n",
       "        <td>ETT Location</td>\n",
       "        <td>334</td>\n",
       "        <td>0.3340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>224832</td>\n",
       "        <td>ETT Type</td>\n",
       "        <td>334</td>\n",
       "        <td>0.3340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>223837</td>\n",
       "        <td>ETT Size (ID)</td>\n",
       "        <td>335</td>\n",
       "        <td>0.3350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>224688</td>\n",
       "        <td>Respiratory Rate (Set)</td>\n",
       "        <td>335</td>\n",
       "        <td>0.3350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>224690</td>\n",
       "        <td>Respiratory Rate (Total)</td>\n",
       "        <td>365</td>\n",
       "        <td>0.3650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220339</td>\n",
       "        <td>PEEP set</td>\n",
       "        <td>378</td>\n",
       "        <td>0.3780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>224689</td>\n",
       "        <td>Respiratory Rate (spontaneous)</td>\n",
       "        <td>378</td>\n",
       "        <td>0.3780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220224</td>\n",
       "        <td>Arterial O2 pressure</td>\n",
       "        <td>460</td>\n",
       "        <td>0.4600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220235</td>\n",
       "        <td>Arterial CO2 Pressure</td>\n",
       "        <td>460</td>\n",
       "        <td>0.4600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>223835</td>\n",
       "        <td>Inspired O2 Fraction</td>\n",
       "        <td>487</td>\n",
       "        <td>0.4870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>223791</td>\n",
       "        <td>Pain Level</td>\n",
       "        <td>894</td>\n",
       "        <td>0.8940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220228</td>\n",
       "        <td>Hemoglobin</td>\n",
       "        <td>965</td>\n",
       "        <td>0.9650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>227443</td>\n",
       "        <td>HCO3 (serum)</td>\n",
       "        <td>967</td>\n",
       "        <td>0.9670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220615</td>\n",
       "        <td>Creatinine</td>\n",
       "        <td>968</td>\n",
       "        <td>0.9680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220645</td>\n",
       "        <td>Sodium (serum)</td>\n",
       "        <td>968</td>\n",
       "        <td>0.9680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>225624</td>\n",
       "        <td>BUN</td>\n",
       "        <td>968</td>\n",
       "        <td>0.9680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>227442</td>\n",
       "        <td>Potassium (serum)</td>\n",
       "        <td>968</td>\n",
       "        <td>0.9680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220179</td>\n",
       "        <td>Non Invasive Blood Pressure systolic</td>\n",
       "        <td>979</td>\n",
       "        <td>0.9790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>224027</td>\n",
       "        <td>Skin Temperature</td>\n",
       "        <td>979</td>\n",
       "        <td>0.9790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>223761</td>\n",
       "        <td>Temperature Fahrenheit</td>\n",
       "        <td>985</td>\n",
       "        <td>0.9850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>223900</td>\n",
       "        <td>GCS - Verbal Response</td>\n",
       "        <td>985</td>\n",
       "        <td>0.9850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>223901</td>\n",
       "        <td>GCS - Motor Response</td>\n",
       "        <td>986</td>\n",
       "        <td>0.9860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220277</td>\n",
       "        <td>O2 saturation pulseoxymetry</td>\n",
       "        <td>987</td>\n",
       "        <td>0.9870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220739</td>\n",
       "        <td>GCS - Eye Opening</td>\n",
       "        <td>987</td>\n",
       "        <td>0.9870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220045</td>\n",
       "        <td>Heart Rate</td>\n",
       "        <td>988</td>\n",
       "        <td>0.9880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>220210</td>\n",
       "        <td>Respiratory Rate</td>\n",
       "        <td>988</td>\n",
       "        <td>0.9880</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(227243, 'Manual Blood Pressure Systolic Right', 5L, Decimal('0.0050')),\n",
       " (220059, 'Pulmonary Artery Pressure systolic', 8L, Decimal('0.0080')),\n",
       " (224167, 'Manual Blood Pressure Systolic Left', 11L, Decimal('0.0110')),\n",
       " (228232, 'PAR-Oxygen saturation', 22L, Decimal('0.0220')),\n",
       " (223762, 'Temperature Celsius', 40L, Decimal('0.0400')),\n",
       " (227516, 'PO2 (Mixed Venous)', 46L, Decimal('0.0460')),\n",
       " (225309, 'ART BP Systolic', 54L, Decimal('0.0540')),\n",
       " (224829, 'Trach Tube Type', 55L, Decimal('0.0550')),\n",
       " (226062, 'Venous CO2 Pressure', 120L, Decimal('0.1200')),\n",
       " (226063, 'Venous O2 Pressure', 120L, Decimal('0.1200')),\n",
       " (227810, 'ETT Re-taped', 134L, Decimal('0.1340')),\n",
       " (226534, 'Sodium (whole blood)', 152L, Decimal('0.1520')),\n",
       " (224391, 'ETT Mark (location)', 159L, Decimal('0.1590')),\n",
       " (220227, 'Arterial O2 Saturation', 197L, Decimal('0.1970')),\n",
       " (227464, 'Potassium (whole blood)', 204L, Decimal('0.2040')),\n",
       " (224700, 'Total PEEP Level', 254L, Decimal('0.2540')),\n",
       " (220050, 'Arterial Blood Pressure systolic', 319L, Decimal('0.3190')),\n",
       " (223838, 'ETT Location', 334L, Decimal('0.3340')),\n",
       " (224832, 'ETT Type', 334L, Decimal('0.3340')),\n",
       " (223837, 'ETT Size (ID)', 335L, Decimal('0.3350')),\n",
       " (224688, 'Respiratory Rate (Set)', 335L, Decimal('0.3350')),\n",
       " (224690, 'Respiratory Rate (Total)', 365L, Decimal('0.3650')),\n",
       " (220339, 'PEEP set', 378L, Decimal('0.3780')),\n",
       " (224689, 'Respiratory Rate (spontaneous)', 378L, Decimal('0.3780')),\n",
       " (220224, 'Arterial O2 pressure', 460L, Decimal('0.4600')),\n",
       " (220235, 'Arterial CO2 Pressure', 460L, Decimal('0.4600')),\n",
       " (223835, 'Inspired O2 Fraction', 487L, Decimal('0.4870')),\n",
       " (223791, 'Pain Level', 894L, Decimal('0.8940')),\n",
       " (220228, 'Hemoglobin', 965L, Decimal('0.9650')),\n",
       " (227443, 'HCO3 (serum)', 967L, Decimal('0.9670')),\n",
       " (220615, 'Creatinine', 968L, Decimal('0.9680')),\n",
       " (220645, 'Sodium (serum)', 968L, Decimal('0.9680')),\n",
       " (225624, 'BUN', 968L, Decimal('0.9680')),\n",
       " (227442, 'Potassium (serum)', 968L, Decimal('0.9680')),\n",
       " (220179, 'Non Invasive Blood Pressure systolic', 979L, Decimal('0.9790')),\n",
       " (224027, 'Skin Temperature', 979L, Decimal('0.9790')),\n",
       " (223761, 'Temperature Fahrenheit', 985L, Decimal('0.9850')),\n",
       " (223900, 'GCS - Verbal Response', 985L, Decimal('0.9850')),\n",
       " (223901, 'GCS - Motor Response', 986L, Decimal('0.9860')),\n",
       " (220277, 'O2 saturation pulseoxymetry', 987L, Decimal('0.9870')),\n",
       " (220739, 'GCS - Eye Opening', 987L, Decimal('0.9870')),\n",
       " (220045, 'Heart Rate', 988L, Decimal('0.9880')),\n",
       " (220210, 'Respiratory Rate', 988L, Decimal('0.9880'))]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT D.ITEMID, D.LABEL, COUNT(DISTINCT(II.ICUSTAY_ID)) AS STAY_COUNT, (COUNT(DISTINCT(II.ICUSTAY_ID))/(:sub_limit)) AS FREQ \\\n",
    "FROM CHARTEVENTS C \\\n",
    "INNER JOIN ( \\\n",
    "    SELECT * FROM ( \\\n",
    "        SELECT * \\\n",
    "        FROM ICUSTAYS I \\\n",
    "        WHERE I.DBSOURCE='metavision' AND (I.FIRST_CAREUNIT='MICU' or I.FIRST_CAREUNIT='SICU') \\\n",
    "        ORDER BY RAND() \\\n",
    "        LIMIT :sub_limit) AS II_sub \\\n",
    "    ORDER BY II_sub.SUBJECT_ID, II_sub.HADM_ID, II_sub.ICUSTAY_ID \\\n",
    "            ) AS II \\\n",
    "ON (C.SUBJECT_ID=II.SUBJECT_ID \\\n",
    "    AND C.HADM_ID=II.HADM_ID \\\n",
    "    AND C.ICUSTAY_ID=II.ICUSTAY_ID) \\\n",
    "INNER JOIN D_ITEMS D \\\n",
    "ON C.ITEMID=D.ITEMID \\\n",
    "WHERE D.ITEMID IN (220739, 223900, 223901, 226755, 226756, 226757, 226758, 227011, 227012, 227013, 227014,228112, 220615, 226752, 227005, 223791, 227881, 227519, 227059, 220640, 227464, 227442, 226772, 226535, 220645, 226534, 226776, 224826, 226759, 227443,220227, 220277, 226860,226861,226862,226863,226865,228232, 225624, 227000, 227001, 223838, 224832, 224391, 227810,223837, 224829, 226754, 227009, 227010,223835, 220210, 224688, 224689, 224690, 226770,227039,227516,220224,220235,226062,226063,227036, 223761, 223762, 224027, 220045, 220228, 220339, 224699, 224700, 220050, 220059, 220179, 224167, 225309, 227243, 226850, 226852, 228151) \\\n",
    "GROUP BY D.ITEMID \\\n",
    "ORDER BY FREQ"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "..and for OUTPUTEVENTS:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ITEMID</th>\n",
       "        <th>LABEL</th>\n",
       "        <th>STAY_COUNT</th>\n",
       "        <th>FREQ</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>226631</td>\n",
       "        <td>PACU Urine</td>\n",
       "        <td>15</td>\n",
       "        <td>0.0150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>226627</td>\n",
       "        <td>OR Urine</td>\n",
       "        <td>139</td>\n",
       "        <td>0.1390</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(226631, 'PACU Urine', 15L, Decimal('0.0150')),\n",
       " (226627, 'OR Urine', 139L, Decimal('0.1390'))]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT D.ITEMID, D.LABEL, COUNT(DISTINCT(II.ICUSTAY_ID)) AS STAY_COUNT, (COUNT(DISTINCT(II.ICUSTAY_ID))/(:sub_limit)) AS FREQ \\\n",
    "FROM OUTPUTEVENTS C \\\n",
    "INNER JOIN ( \\\n",
    "    SELECT * FROM ( \\\n",
    "        SELECT * \\\n",
    "        FROM ICUSTAYS I \\\n",
    "        WHERE I.DBSOURCE='metavision' AND (I.FIRST_CAREUNIT='MICU' or I.FIRST_CAREUNIT='SICU') \\\n",
    "        ORDER BY RAND() \\\n",
    "        LIMIT :sub_limit) AS II_sub \\\n",
    "    ORDER BY II_sub.SUBJECT_ID, II_sub.HADM_ID, II_sub.ICUSTAY_ID \\\n",
    "            ) AS II \\\n",
    "ON (C.SUBJECT_ID=II.SUBJECT_ID \\\n",
    "    AND C.HADM_ID=II.HADM_ID \\\n",
    "    AND C.ICUSTAY_ID=II.ICUSTAY_ID) \\\n",
    "INNER JOIN D_ITEMS D \\\n",
    "ON C.ITEMID=D.ITEMID \\\n",
    "WHERE D.ITEMID IN (226566, 226627, 226631) \\\n",
    "GROUP BY D.ITEMID \\\n",
    "ORDER BY FREQ"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note:** we find that the 'Urine Output' ITEMs that we identified are not in frequent use. The difficulty of identifying urine in MIMIC has been previoulsy noted by others. We accept that we canot use 'Urine Output' in our analysis and do not attempt further investigation.  \n",
    "\n",
    "We now store the CHARTEVENTS query results into a dataframe, and remove those that are only recorded infrequently. This is done to simplify the subsequent analysis (and reduce the required number of checks on data integrity), but we aknowledge that it will result in a small amount of missing data. We define a minimum usage frequency of 0.01 (1%)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "61 rows affected.\n",
      "+--------+---------------------+------------+-------+\n",
      "| ITEMID |        LABEL        | STAY_COUNT |  FREQ |\n",
      "+--------+---------------------+------------+-------+\n",
      "| 227000 |     BUN_ApacheIV    |     1      | 0.001 |\n",
      "| 227001 |  BunScore_ApacheIV  |     1      | 0.001 |\n",
      "| 227005 | Creatinine_ApacheIV |     1      | 0.001 |\n",
      "| 227010 |    FiO2_ApacheIV    |     1      | 0.001 |\n",
      "+--------+---------------------+------------+-------+\n",
      "[61 rows x 4 columns]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "freqs=%sql SELECT D.ITEMID, D.LABEL, COUNT(DISTINCT(II.ICUSTAY_ID)) AS STAY_COUNT, (COUNT(DISTINCT(II.ICUSTAY_ID))/(:sub_limit)) AS FREQ \\\n",
    "FROM CHARTEVENTS C \\\n",
    "INNER JOIN ( \\\n",
    "    SELECT * FROM ( \\\n",
    "        SELECT * \\\n",
    "        FROM ICUSTAYS I \\\n",
    "        WHERE I.DBSOURCE='metavision' AND (I.FIRST_CAREUNIT='MICU' or I.FIRST_CAREUNIT='SICU') \\\n",
    "        ORDER BY RAND() \\\n",
    "        LIMIT :sub_limit) AS II_sub \\\n",
    "    ORDER BY II_sub.SUBJECT_ID, II_sub.HADM_ID, II_sub.ICUSTAY_ID \\\n",
    "            ) AS II \\\n",
    "ON (C.SUBJECT_ID=II.SUBJECT_ID \\\n",
    "    AND C.HADM_ID=II.HADM_ID \\\n",
    "    AND C.ICUSTAY_ID=II.ICUSTAY_ID) \\\n",
    "INNER JOIN D_ITEMS D \\\n",
    "ON C.ITEMID=D.ITEMID \\\n",
    "WHERE D.ITEMID IN (220739, 223900, 223901, 226755, 226756, 226757, 226758, 227011, 227012, 227013, 227014,228112, 220615, 226752, 227005, 223791, 227881, 227519, 227059, 220640, 227464, 227442, 226772, 226535, 220645, 226534, 226776, 224826, 226759, 227443,220227, 220277, 226860,226861,226862,226863,226865,228232, 225624, 227000, 227001, 223838, 224832, 224391, 227810,223837, 224829, 226754, 227009, 227010,223835, 220210, 224688, 224689, 224690, 226770,227039,227516,220224,220235,226062,226063,227036, 223761, 223762, 224027, 220045, 220228, 220339, 224699, 224700, 220050, 220059, 220179, 224167, 225309, 227243, 226850, 226852, 228151) \\\n",
    "GROUP BY D.ITEMID \\\n",
    "ORDER BY FREQ\n",
    "\n",
    "freqs=graphlab.SFrame(freqs.DataFrame())\n",
    "freqs.print_rows(num_rows=4)\n",
    "ITEMIDS = freqs[freqs['FREQ']>=0.01]['ITEMID']  ## cutoff = 0.01\n",
    "ITEMIDS_TOREMOVE = freqs[freqs['FREQ']<0.01]['ITEMID']  ## remove these from variable mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 21 infrequent ITEMS to remove from the variable mapping.\n"
     ]
    }
   ],
   "source": [
    "print \"There are %d infrequent ITEMS to remove from the variable mapping.\" %len(ITEMIDS_TOREMOVE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'urine': [], 'pain': [223791], 'temp': [223761, 223762, 224027], 'hr': [220045], 'fio2': [227009, 223835], 'resp': [220210, 224688, 224689, 224690], 'airway': [223838, 224832, 224391, 227810, 223837, 224829], 'po2': [226770, 227039, 227516, 220224], 'hco3': [224826, 227443], 'peep': [220339, 224699, 224700], 'gcs': [220739, 223900, 223901, 227011, 227012, 227014, 228112], 'pco2': [220235, 226062, 226063], 'na': [220645, 226534], 'bun': [225624], 'bp': [220050, 220179, 225309, 226850, 226852, 228151], 'creatinine': [220615], 'k': [220640, 227464, 227442, 226535], 'spo2': [220227, 220277, 226860, 226861, 226862, 226863, 226865, 228232], 'haemoglobin': [220228]}\n"
     ]
    }
   ],
   "source": [
    "variable_mapping = dict()\n",
    "\n",
    "variable_mapping['fio2'] = [226754, 227009, 227010,223835]\n",
    "variable_mapping['resp'] = [220210, 224688, 224689, 224690]\n",
    "variable_mapping['po2'] = [226770,227039,227516,220224]  ## 227516 is venous - excluded in subsequent script.\n",
    "variable_mapping['pco2'] = [220235,226062,226063,227036]  ## 226062,226063 are venous - excluded in subsequent script.\n",
    "\n",
    "variable_mapping['temp'] = [223761, 223762, 224027] \n",
    "variable_mapping['hr'] = [220045]\n",
    "variable_mapping['bp'] = [220050, 220059, 220179, 224167, 225309, 227243, 226850, 226852, 228151] ## 228151 is diastolic - excluded in subsequent script.\n",
    "variable_mapping['k'] = [220640, 227464, 227442, 226772, 226535]\n",
    "variable_mapping['na'] = [220645, 226534, 226776]\n",
    "variable_mapping['hco3'] = [224826, 226759, 227443]\n",
    "variable_mapping['spo2'] = [220227, 220277, 226860,226861,226862,226863,226865,228232]\n",
    "variable_mapping['bun'] = [225624, 227000, 227001]\n",
    "variable_mapping['airway'] = [223838, 224832, 224391, 227810,223837, 224829]\n",
    "variable_mapping['gcs'] = [220739, 223900, 223901, 226755, 226756, 226757, 226758, 227011, 227012, 227013, 227014,228112]\n",
    "variable_mapping['creatinine'] = [220615, 226752, 227005]\n",
    "variable_mapping['pain'] = [223791, 227881]\n",
    "variable_mapping['urine'] = [227519, 227059]\n",
    "variable_mapping['haemoglobin'] = [220228]\n",
    "variable_mapping['peep'] = [220339, 224699, 224700]\n",
    "                        \n",
    "for var in variable_mapping:\n",
    "        variable_mapping[var] = [itd for itd in variable_mapping[var] if itd not in ITEMIDS_TOREMOVE]\n",
    "        \n",
    "print variable_mapping"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Finally we extract the data from CHARTEVENTS for all the selected ITEMIDS:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import MySQLdb\n",
    "import MySQLdb.cursors\n",
    "from contextlib import closing\n",
    "import shutil\n",
    "\n",
    "def sql_connect():\n",
    "    db='MIMIC'\n",
    "    user='root'\n",
    "    password='mysql2016'\n",
    "\n",
    "    connection = MySQLdb.connect(host=\"localhost\",\n",
    "                     user=user, \n",
    "                     passwd=password, \n",
    "                     db=db,\n",
    "                     unix_socket=\"/run/mysqld/mysqld.sock\",\n",
    "                     cursorclass = MySQLdb.cursors.SSCursor)  ## ensures correct behaviour for 'fetchone'\n",
    "    return connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(12710, 220799, 'ZSpecific Gravity (urine)', 'ZSpecific gravity (urine)', 'metavision', 'chartevents', 'Labs', 'None', 'Numeric', None, None)\n"
     ]
    }
   ],
   "source": [
    "## test the connection (should only print one row if 'fetchone' is working correctly):\n",
    "conn = sql_connect()\n",
    "with closing(conn.cursor()) as cur:\n",
    "    cur.execute('SELECT * FROM D_ITEMS WHERE LABEL LIKE \"%urine%\" AND DBSOURCE=\"metavision\"')\n",
    "    row = cur.fetchone()\n",
    "    print row\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "list_of_ids = ITEMIDS\n",
    "format_strings = ','.join(['%s'] * len(list_of_ids))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "partial_pull_query0 =\"\"\"SELECT C.SUBJECT_ID, C.HADM_ID, C.ICUSTAY_ID, C.ITEMID, C.CHARTTIME, C.VALUE, C.VALUENUM, C.VALUEUOM, II.INTIME, II.OUTTIME, II.LOS\n",
    "FROM CHARTEVENTS C \n",
    "INNER JOIN ICUSTAYS II\n",
    "ON (C.SUBJECT_ID=II.SUBJECT_ID \n",
    "    AND C.HADM_ID=II.HADM_ID \n",
    "    AND C.ICUSTAY_ID=II.ICUSTAY_ID)  \n",
    "WHERE II.DBSOURCE='metavision' \n",
    "AND (II.FIRST_CAREUNIT='MICU' or II.FIRST_CAREUNIT='SICU') \n",
    "AND II.ICUSTAY_ID=%d\"\"\"\n",
    "\n",
    "partial_pull_query1=\"\"\"\n",
    "   AND C.ITEMID in \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now extract the data. This is a big query. We use 'fetchone' one to grab the results row by row, and store them in a dictionary (keys=column names, values=list of column values).\n",
    "\n",
    "We use graphlab because is has out-of-memory storage for SFrames (no concern over size of result set). Still, we select the data patient by patient and build up the results frame incrementaly.\n",
    "\n",
    "Note the SFrame.from_sql() method with MysqlDB connection was found to be more robust than sqlalchemy and pandas (using read_sql_query)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 14595 icustays in MICU and SICU under metavision.\n"
     ]
    }
   ],
   "source": [
    "with closing(sql_connect()) as conn: \n",
    "    df = graphlab.SFrame.from_sql(conn, \"SELECT DISTINCT(ICUSTAY_ID) FROM ICUSTAYS WHERE DBSOURCE='metavision' AND (FIRST_CAREUNIT='MICU' OR FIRST_CAREUNIT='SICU')\", dbapi_module=MySQLdb, cursor_arraysize=1000)\n",
    "    icustays = list(df['ICUSTAY_ID'])\n",
    "    print \"There are %d icustays in MICU and SICU under metavision.\" %len(icustays)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "#### We proceed in batches of 3000 patients and then clean up at the end (this is probably not necessary):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## This should not be necessary in an un-corrupted database.\n",
    "## icustays = [i for i in icustays if i not in (249026, 254172)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Batch 0\n",
      "    Initialised batch results frame, proceeding with batch queries...\n",
      "0 241249\n",
      "500 280701\n",
      "1000 218331\n",
      "1500 278095\n",
      "2000 216040\n",
      "2500 239067\n",
      "    Batch queries complete. Saving batch results...\n",
      "    Batch 0 complete. Batch results contains 1821152 rows.\n",
      "Batch 1\n",
      "    Initialised batch results frame, proceeding with batch queries...\n",
      "0 246007\n",
      "500 277881\n",
      "1000 293316\n",
      "1500 244855\n",
      "2000 237177\n",
      "2500 292053\n",
      "    Batch queries complete. Saving batch results...\n",
      "    Batch 1 complete. Batch results contains 2549385 rows.\n",
      "Batch 2\n",
      "    Initialised batch results frame, proceeding with batch queries...\n",
      "0 283328\n",
      "500 292737\n",
      "1000 243405\n",
      "1500 298627\n",
      "2000 236772\n",
      "2500 235580\n",
      "    Batch queries complete. Saving batch results...\n",
      "    Batch 2 complete. Batch results contains 2342751 rows.\n",
      "Batch 3\n",
      "    Initialised batch results frame, proceeding with batch queries...\n",
      "0 266340\n",
      "500 274052\n",
      "1000 261558\n",
      "1500 248886\n",
      "2000 276657\n",
      "2500 234288\n",
      "    Batch queries complete. Saving batch results...\n",
      "    Batch 3 complete. Batch results contains 2161039 rows.\n",
      "Batch 4\n",
      "    Initialised batch results frame, proceeding with batch queries...\n",
      "0 249842\n",
      "500 245825\n",
      "1000 238410\n",
      "1500 258120\n",
      "2000 215402\n",
      "2500 285847\n",
      "    Batch queries complete. Saving batch results...\n",
      "    Batch 4 complete. Batch results contains 1938051 rows.\n"
     ]
    }
   ],
   "source": [
    "icustays_with_sql_errors = [] \n",
    "## this list of stays that cause sql errors helped me identify the two icustays that appear the be corrupted in the database. \n",
    "\n",
    "for batch in [0,1,2,3,4]:\n",
    "    \n",
    "    print \"Batch \" + str(batch)\n",
    "    first_icid_index = 0 + batch*3000\n",
    "    last_icid_index = min(3000 + batch*3000, len(icustays))\n",
    "    \n",
    "    with closing(sql_connect()) as conn:\n",
    "        pull_query = partial_pull_query0 %icustays[first_icid_index] + partial_pull_query1 + str(tuple(list_of_ids))\n",
    "        results = graphlab.SFrame.from_sql(conn, pull_query, dbapi_module=MySQLdb, cursor_arraysize=1000)\n",
    "\n",
    "    print \"    Initialised batch results frame, proceeding with batch queries...\"    \n",
    "    for i,icid in enumerate(icustays[first_icid_index+1 : last_icid_index]):\n",
    "\n",
    "        if i%500 == 0:\n",
    "            print \"%d %d\" %(i, icid) \n",
    "        try:\n",
    "            with closing(sql_connect()) as conn:\n",
    "                pull_query = partial_pull_query0 %icid + partial_pull_query1 + str(tuple(list_of_ids))\n",
    "                _df = graphlab.SFrame.from_sql(conn, pull_query, dbapi_module=MySQLdb, cursor_arraysize=1000)\n",
    "        except:\n",
    "            print \"Sql error on icustay : %d\" %icid\n",
    "            icustays_with_sql_errors.append(icid)\n",
    "\n",
    "        try:\n",
    "            if len(_df)>0:\n",
    "                results = results.append(graphlab.SFrame(_df))\n",
    "        except:\n",
    "            print \"Could not join df to results on:  %d\" %icid\n",
    "    \n",
    "    print \"    Batch queries complete. Saving batch results...\"\n",
    "    results.save('_batch_%d_query_data' %batch)\n",
    "    print \"    Batch %d complete. Batch results contains %d rows.\" %(batch,len(results))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------------------------------------------------------------------------------------------------------\n",
    "\n",
    "#### We now join the batch results and cleanup temporary files on the hardrive:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "results = graphlab.SFrame('_batch_0_query_data')\n",
    "\n",
    "for batch in [1,2,3,4]:\n",
    "    _df = graphlab.SFrame('_batch_%d_query_data' %batch)\n",
    "    results = results.append(_df)\n",
    "\n",
    "results.materialize()\n",
    "results.save('mimic_all_data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are a total of 10812378 rows in the data.\n"
     ]
    }
   ],
   "source": [
    "print \"There are a total of %d rows in the data.\" %len(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### If joining the batched results was successful we can safely remove the batch files:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for batch in [0,1,2,3,4]:\n",
    "    shutil.rmtree('_batch_%d_query_data' %batch)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We now need to extract D.Label and D.Unitname from D_Items and join them to the results table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "ditems_query = \"\"\"SELECT ITEMID, LABEL AS 'D.LABEL', UNITNAME AS 'D.UNITNAME' FROM D_ITEMS WHERE ITEMID IN \"\"\"\n",
    "\n",
    "with closing(sql_connect()) as conn:\n",
    "    item_info = graphlab.SFrame.from_sql(conn, ditems_query + str(tuple(list_of_ids)), dbapi_module=MySQLdb, cursor_arraysize=1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = graphlab.SFrame('mimic_all_data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = results.join(item_info, on='ITEMID', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "results.save('mimic_all_data')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------------------------------------------------------------------------------------------\n",
    "## (C) Post-processing of data.\n",
    "\n",
    "Having extracted the main data we perform some post-processing to facilitate analysis. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['SUBJECT_ID', 'HADM_ID', 'ICUSTAY_ID', 'ITEMID', 'CHARTTIME', 'VALUE', 'VALUENUM', 'VALUEUOM', 'INTIME', 'OUTTIME', 'LOS', 'D.LABEL', 'D.UNITNAME']\n"
     ]
    }
   ],
   "source": [
    "all_data = graphlab.SFrame('mimic_all_data') ## reload\n",
    "print all_data.column_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "all_data = all_data.rename({'SUBJECT_ID':'C.SUBJECT_ID',\n",
    "                            'HADM_ID':'C.HADM_ID',\n",
    "                            'ICUSTAY_ID':'C.ICUSTAY_ID',\n",
    "                            'ITEMID':'D.ITEMID',\n",
    "                            'CHARTTIME':'C.CHARTTIME',\n",
    "                            'VALUE':'C.VALUE',\n",
    "                            'VALUENUM':'C.VALUENUM',\n",
    "                            'VALUEUOM':'C.VALUEUOM',\n",
    "                            'INTIME':'II.INTIME',\n",
    "                            'OUTTIME':'II.OUTTIME',\n",
    "                            'LOS':'II.LOS',\n",
    "                            'D.LABEL':'D.LABEL',\n",
    "                            'D.UNITNAME':'D.UNITNAME'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 10812378 rows in the data. \n",
      "There are 14430 intensive care stays.\n"
     ]
    }
   ],
   "source": [
    "number_of_stays = len(all_data['C.ICUSTAY_ID'].unique())\n",
    "print \"There are %d rows in the data. \" %len(all_data)\n",
    "print \"There are %d intensive care stays.\" %number_of_stays"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We add the following columns to the data:\n",
    "\n",
    "* ['final_4hr'] : 1 if measurement is from final 4 hours of patients stay\n",
    "* ['final_24hr'] : 1 if measurement is from final 24 hours of patients stay\n",
    "* ['hrs_bd'] : float giving number of hours before discharge that measurement was taken (can filter on this column later)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "all_data['hrs_bd'] = (all_data['II.OUTTIME'] - all_data['C.CHARTTIME'])/float(60**2)\n",
    "all_data.save('mimic_all_data')  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "HR = 4  ## number of hours before end of ICUSTAY \n",
    "all_data['final_%dhr' %HR] = all_data.apply(lambda row: 1 if (row['II.OUTTIME'] - row['C.CHARTTIME']).total_seconds()/(60.**2) <= HR else 0)\n",
    "all_data.save('mimic_all_data')            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "HR = 24  ## number of hours before end of ICUSTAY \n",
    "all_data['final_%dhr' %HR] = all_data.apply(lambda row: 1 if (row['II.OUTTIME'] - row['C.CHARTTIME']).total_seconds()/(60.**2) <= HR else 0)\n",
    "all_data.save('mimic_all_data')            "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Merging data extraction with cohort..\n",
    "\n",
    "We now add columns for cohort identification, and filtering based on outcome. Main cohort consists of the first ICUSTAY of each hospital admission (should be >13000 stays).\n",
    "\n",
    "From this cohort we are interested in ICUSTAYS that have good (1) and bad (0) outcome.\n",
    "The are two types of bad outcome: (A) in-hospital death, (B) readmission to ICU (during same hospital admission).\n",
    "\n",
    "The above abstraction is not fool-proof. For example:\n",
    "* patients may die outside of hospital for related reasons\n",
    "* readmission may be linked but occur on different stay\n",
    "* in-hopistal death may be unrelated to ICU discharge\n",
    "* etc\n",
    "\n",
    "We add the following columns to the data frame: \n",
    "\n",
    "* ['cohort']: binary flag. 1 indicates that ICUSTAY is part of cohort (i.e. first or only stay of a hospital admission).\n",
    "* ['outcome']: binary flag. 1 indicates good outcome (survival with no readmission). 0 indicates bad outcome (death or readmission).\n",
    "* ['in_h_death']: binary flag. 1 for death. 0 for survival.\n",
    "* ['in_icu_death']: binary flag. 1 for death. 0 for survival.\n",
    "* ['readmit']: integer. Number of readmissions to icu during same hospital admission.\n",
    "\n",
    "*Note: there are fewer HADMs than in cohort_selection_mimic.ipynb, since not all patients have chartevents data.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "f = open('first_stays.pkl', 'rb')\n",
    "first_stays = pickle.load(f)\n",
    "f.close()\n",
    "\n",
    "f = open('moratlities.pkl', 'rb')\n",
    "mortalities = pickle.load(f)\n",
    "f.close()\n",
    "\n",
    "f = open('stay_counts.pkl', 'rb')\n",
    "stay_counts = pickle.load(f)\n",
    "f.close()\n",
    "\n",
    "f = open('icu_deaths.pkl', 'rb')\n",
    "ic_deaths = pickle.load(f)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Adding cohort column...\n",
      "Adding in hospital column...\n",
      "Adding in icu death column...\n",
      "Adding readmission column...\n",
      "Adding outcome column...\n",
      "finish adding new columns, saving data frame..\n"
     ]
    }
   ],
   "source": [
    "print \"Adding cohort column...\"\n",
    "all_data['cohort'] = all_data.apply(lambda row: 1 if first_stays[row['C.HADM_ID']]==row['C.ICUSTAY_ID'] else 0)\n",
    "\n",
    "print \"Adding in hospital column...\"\n",
    "all_data['in_h_death'] = all_data['C.HADM_ID'].apply(lambda hadmid: 1 if hadmid in mortalities else 0 )\n",
    "\n",
    "print \"Adding in icu death column...\"\n",
    "all_data['in_icu_death'] = all_data['C.ICUSTAY_ID'].apply(lambda icustay: 1 if icustay in ic_deaths else 0 )\n",
    "\n",
    "print \"Adding readmission column...\"\n",
    "all_data['readmit'] = all_data['C.HADM_ID'].apply(lambda hadmid: stay_counts[hadmid] - 1)\n",
    "\n",
    "print \"Adding outcome column...\"\n",
    "all_data['outcome'] = all_data['C.HADM_ID'].apply(lambda hadmid: 1 if (hadmid not in mortalities and stay_counts[hadmid]==1) else 0)\n",
    "\n",
    "print 'finish adding new columns, saving data frame..'\n",
    "all_data.save('mimic_all_data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 14430 unique ICU stays, of which\n",
      "13603 are in the cohort.\n"
     ]
    }
   ],
   "source": [
    "print \"There are %d unique ICU stays, of which\" %len(all_data['C.ICUSTAY_ID'].unique())\n",
    "print \"%d are in the cohort.\" %(len(all_data[all_data['cohort']==1]['C.ICUSTAY_ID'].unique()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: this cohort size will be reduced by further processing in subsequent scripts."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}