AIClinician_Data_extract_MIMIC3_140219.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DATA EXTRACTION FROM MIMIC-III FOR THE AI-CLINICIAN\n",
    "\n",
    "This notebook contains SQL queries required to recreate dataset for the publication: https://www.nature.com/articles/s41591-018-0213-5\n",
    "\n",
    "\n",
    "Recreating the dataset is a 3-step process:\n",
    "- Data extraction done here in SQL, sub-tables exported as csv files\n",
    "- Data imported in Matlab for:\n",
    "    - Sepsis-3 cohort definition (suspected infection + SOFA score)\n",
    "    - Construction of the final dataset\n",
    "    \n",
    "It uses some queries from the MIMIC-III repo: https://github.com/MIT-LCP/mimic-code\n",
    "\n",
    "Dr Matthieu Komorowski, Imperial College London 2019.",
    "\n",
    "\n",
    "If you use any of this code, please reference appropriately.",
    "\n",
    "\n",
    "This code is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY, without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import psycopg2\n",
    "import pprint\n",
    "import shelve"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Update connection details to MIMIC-III\n",
    "conn = psycopg2.connect(\"dbname = dbname_here user= user password=password host=IP_address_here port=port_number_here\")\n",
    "\n",
    "# Update the path for data extraction here\n",
    "exportdir='D:/exportdir/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# EXTRACTION OF SUB-TABLES\n",
    "\n",
    "## 'culture' items\n",
    "\n",
    "These correspond to blood/urine/CSF/sputum cultures etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "select subject_id, hadm_id, icustay_id,  extract(epoch from charttime) as charttime, itemid\n",
    "from mimiciii.chartevents\n",
    "where itemid in (6035,3333,938,941,942,4855,6043,2929,225401,225437,225444,225451,225454,225814,225816,225817,225818,225722,225723,225724,225725,225726,225727,225728,225729,225730,225731,225732,225733,227726,70006,70011,70012,70013,70014,70016,70024,70037,70041,225734,225735,225736,225768,70055,70057,70060,70063,70075,70083,226131,80220)\n",
    "order by subject_id, hadm_id, charttime\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'culture.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Microbiologyevents\n",
    "\n",
    "Extracts date & time of all microbiologyevents (regardless of whether they are positive or negative)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "select subject_id, hadm_id, extract(epoch from charttime) as charttime, extract(epoch from chartdate) as chartdate \n",
    "from mimiciii.microbiologyevents\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'microbio.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Antibiotics administration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>startdate</th>\n",
       "      <th>enddate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100006</td>\n",
       "      <td>291788.0</td>\n",
       "      <td>4.363632e+09</td>\n",
       "      <td>4.363978e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100006</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.363978e+09</td>\n",
       "      <td>4.364150e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.530810e+09</td>\n",
       "      <td>5.530810e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100009</td>\n",
       "      <td>253656.0</td>\n",
       "      <td>6.070982e+09</td>\n",
       "      <td>6.071069e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100009</td>\n",
       "      <td>253656.0</td>\n",
       "      <td>6.070896e+09</td>\n",
       "      <td>6.070982e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hadm_id  icustay_id     startdate       enddate\n",
       "0   100006    291788.0  4.363632e+09  4.363978e+09\n",
       "1   100006         NaN  4.363978e+09  4.364150e+09\n",
       "2   100007         NaN  5.530810e+09  5.530810e+09\n",
       "3   100009    253656.0  6.070982e+09  6.071069e+09\n",
       "4   100009    253656.0  6.070896e+09  6.070982e+09"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select hadm_id, icustay_id, extract(epoch from startdate) as startdate, extract(epoch from enddate) as enddate\n",
    "from mimiciii.prescriptions\n",
    "where gsn in ('002542','002543','007371','008873','008877','008879','008880','008935','008941','008942','008943','008944','008983','008984','008990','008991','008992','008995','008996','008998','009043','009046','009065','009066','009136','009137','009162','009164','009165','009171','009182','009189','009213','009214','009218','009219','009221','009226','009227','009235','009242','009263','009273','009284','009298','009299','009310','009322','009323','009326','009327','009339','009346','009351','009354','009362','009394','009395','009396','009509','009510','009511','009544','009585','009591','009592','009630','013023','013645','013723','013724','013725','014182','014500','015979','016368','016373','016408','016931','016932','016949','018636','018637','018766','019283','021187','021205','021735','021871','023372','023989','024095','024194','024668','025080','026721','027252','027465','027470','029325','029927','029928','037042','039551','039806','040819','041798','043350','043879','044143','045131','045132','046771','047797','048077','048262','048266','048292','049835','050442','050443','051932','052050','060365','066295','067471')\n",
    "order by hadm_id, icustay_id\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'abx.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Demographics\n",
    "\n",
    "Requires building **public table** *Elixhauser_Quan* first: https://github.com/MIT-LCP/mimic-code/blob/master/concepts/comorbidity/elixhauser-quan.sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "select ad.subject_id, ad.hadm_id, i.icustay_id ,extract(epoch from ad.admittime) as admittime, extract(epoch from ad.dischtime) as dischtime, ROW_NUMBER() over (partition by ad.subject_id order by i.intime asc) as adm_order, case when i.first_careunit='NICU' then 5 when i.first_careunit='SICU' then 2 when i.first_careunit='CSRU' then 4 when i.first_careunit='CCU' then 6 when i.first_careunit='MICU' then 1 when i.first_careunit='TSICU' then 3 end as unit,  extract(epoch from i.intime) as intime, extract(epoch from i.outtime) as outtime, i.los,\n",
    " EXTRACT(EPOCH FROM (i.intime-p.dob)::INTERVAL)/86400 as age, extract(epoch from p.dob) as dob, extract(epoch from p.dod) as dod,\n",
    " p.expire_flag,  case when p.gender='M' then 1 when p.gender='F' then 2 end as gender,\n",
    " CAST(extract(epoch from age(p.dod,ad.dischtime))<=24*3600  as int )as morta_hosp,  --died in hosp if recorded DOD is close to hosp discharge\n",
    " CAST(extract(epoch from age(p.dod,i.intime))<=90*24*3600  as int )as morta_90,\n",
    " congestive_heart_failure+cardiac_arrhythmias+valvular_disease+pulmonary_circulation+peripheral_vascular+hypertension+paralysis+other_neurological+chronic_pulmonary+diabetes_uncomplicated+diabetes_complicated+hypothyroidism+renal_failure+liver_disease+peptic_ulcer+aids+lymphoma+metastatic_cancer+solid_tumor+rheumatoid_arthritis+coagulopathy+obesity\t+weight_loss+fluid_electrolyte+blood_loss_anemia+\tdeficiency_anemias+alcohol_abuse+drug_abuse+psychoses+depression as elixhauser\n",
    "from mimiciii.admissions ad, mimiciii.icustays i, mimiciii.patients p, public.elixhauser_quan elix\n",
    "where ad.hadm_id=i.hadm_id and p.subject_id=i.subject_id and elix.hadm_id=ad.hadm_id\n",
    "order by subject_id asc, intime asc\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'demog.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Vitals from Chartevents\n",
    "\n",
    "Divided into 10 chunks for speed. Each chunk is around 170 MB."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20000\n",
      "30000\n",
      "40000\n",
      "50000\n",
      "60000\n",
      "70000\n",
      "80000\n",
      "90000\n"
     ]
    }
   ],
   "source": [
    "for i in range(0,100000,10000):\n",
    "        print(i)\n",
    "        query= \"select distinct icustay_id, extract(epoch from charttime) as charttime, itemid, case when value = 'None' then '0' when value = 'Ventilator' then '1' when value='Cannula' then '2' when value = 'Nasal Cannula' then '2' when value = 'Face Tent' then '3' when value = 'Aerosol-Cool' then '4' when value = 'Trach Mask' then '5' when value = 'Hi Flow Neb' then '6' when value = 'Non-Rebreather' then '7' when value = '' then '8'  when value = 'Venti Mask' then '9' when value = 'Medium Conc Mask' then '10' else valuenum end as valuenum from mimiciii.chartevents where icustay_id>=\"+str(200000+i)+\" and icustay_id< \" + str(210000+i) + \" and value is not null and itemid in  (467, 470,471,223834,227287,194,224691,226707,226730\t,581,\t580,\t224639\t,226512,198,228096\t,211,220045,220179,225309,6701,\t6\t,227243,\t224167,\t51,\t455, 220181,\t220052,\t225312,\t224322,\t6702,\t443\t,52,\t456,8368\t,8441,\t225310\t,8555\t,8440,220210\t,3337\t,224422\t,618,\t3603,\t615,220277,\t646,\t834,3655,\t223762\t,223761,\t678,220074\t,113,492,491,8448,116,\t1372\t,1366\t,228368\t,228177,626,223835,3420,160,\t727,190,220339\t,506\t,505,\t224700,224686,224684,684,\t224421,224687,\t450\t,448\t,445,224697,444,224695,\t535,224696\t,543,3083,\t2566\t,654\t,3050,681,\t2311)  order by icustay_id, charttime \"        \n",
    "        d=pd.read_sql_query(query,conn)\n",
    "        d.to_csv(exportdir+'ce' + str(i)+str(i+10000) +'.csv',index=False,sep='|')\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Labs from Chartevents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>icustay_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>itemid</th>\n",
       "      <th>valuenum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200001</td>\n",
       "      <td>6.686998e+09</td>\n",
       "      <td>224828</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200001</td>\n",
       "      <td>6.686998e+09</td>\n",
       "      <td>225667</td>\n",
       "      <td>1.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200001</td>\n",
       "      <td>6.686998e+09</td>\n",
       "      <td>225668</td>\n",
       "      <td>1.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200001</td>\n",
       "      <td>6.686998e+09</td>\n",
       "      <td>227464</td>\n",
       "      <td>4.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200001</td>\n",
       "      <td>6.687007e+09</td>\n",
       "      <td>225664</td>\n",
       "      <td>118.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   icustay_id     charttime  itemid  valuenum\n",
       "0      200001  6.686998e+09  224828      1.00\n",
       "1      200001  6.686998e+09  225667      1.13\n",
       "2      200001  6.686998e+09  225668      1.40\n",
       "3      200001  6.686998e+09  227464      4.60\n",
       "4      200001  6.687007e+09  225664    118.00"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select icustay_id, extract(epoch from charttime) as charttime, itemid, valuenum\n",
    "from mimiciii.chartevents\n",
    "where valuenum is not null and icustay_id is not null and itemid in  (829,\t1535,\t227442,\t227464,\t4195\t,3726\t,3792,837,\t220645,\t4194,\t3725,\t3803\t,226534,\t1536,\t4195,\t3726,788,\t220602,\t1523,\t4193,\t3724\t,226536,\t3747,225664,\t807,\t811,\t1529,\t220621,\t226537,\t3744,781,\t1162,\t225624,\t3737,791,\t1525,\t220615,\t3750,821,\t1532,\t220635,786,\t225625,\t1522,\t3746,816,\t225667,\t3766,777,\t787,770,\t3801,769,\t3802,1538,\t848,\t225690,\t803,\t1527,\t225651,\t3807,\t1539,\t849,\t772,\t1521,\t227456,\t3727,\t227429,\t851,227444,\t814,\t220228,\t813,\t220545,\t3761,\t226540,\t4197,\t3799\t,1127,\t1542,\t220546,\t4200,\t3834,\t828,\t227457,\t3789,825,\t1533,\t227466,\t3796,824,\t1286,1671,\t1520,\t768,220507\t,815,\t1530,\t227467,\t780,\t1126,\t3839,\t4753,779,\t490,\t3785,\t3838,\t3837,778,\t3784,\t3836,\t3835,776,\t224828,\t3736,\t4196,\t3740,\t74,225668,1531,227443,1817,\t228640,823,\t227686)\n",
    "order by icustay_id, charttime, itemid\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'labs_ce.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Labs from Labevents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>icustay_id</th>\n",
       "      <th>timestp</th>\n",
       "      <th>itemid</th>\n",
       "      <th>valuenum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>275225</td>\n",
       "      <td>4.660795e+09</td>\n",
       "      <td>50861</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>275225</td>\n",
       "      <td>4.660795e+09</td>\n",
       "      <td>50878</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>275225</td>\n",
       "      <td>4.660795e+09</td>\n",
       "      <td>50882</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>275225</td>\n",
       "      <td>4.660795e+09</td>\n",
       "      <td>50885</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>275225</td>\n",
       "      <td>4.660795e+09</td>\n",
       "      <td>50902</td>\n",
       "      <td>101.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   icustay_id       timestp  itemid  valuenum\n",
       "0      275225  4.660795e+09   50861      10.0\n",
       "1      275225  4.660795e+09   50878      16.0\n",
       "2      275225  4.660795e+09   50882      11.0\n",
       "3      275225  4.660795e+09   50885       0.5\n",
       "4      275225  4.660795e+09   50902     101.0"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select xx.icustay_id, extract(epoch from f.charttime) as timestp, f.itemid, f.valuenum\n",
    "from(\n",
    "select subject_id, hadm_id, icustay_id, intime, outtime\n",
    "from mimiciii.icustays\n",
    "group by subject_id, hadm_id, icustay_id, intime, outtime\n",
    ") as xx inner join  mimiciii.labevents as f on f.hadm_id=xx.hadm_id and f.charttime>=xx.intime-interval '1 day' and f.charttime<=xx.outtime+interval '1 day'  and f.itemid in  (50971,50822,50824,50806,50931,51081,50885,51003,51222,50810,51301,50983,50902,50809,51006,50912,50960,50893,50808,50804,50878,50861,51464,50883,50976,50862,51002,50889,50811,51221,51279,51300,51265,51275,51274,51237,50820,50821,50818,50802,50813,50882,50803) and valuenum is not null\n",
    "order by f.hadm_id, timestp, f.itemid\n",
    "\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'labs_le.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Real-time UO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>icustay_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>itemid</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200001</td>\n",
       "      <td>6.687007e+09</td>\n",
       "      <td>226560</td>\n",
       "      <td>250.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200001</td>\n",
       "      <td>6.687089e+09</td>\n",
       "      <td>226560</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200001</td>\n",
       "      <td>6.687222e+09</td>\n",
       "      <td>226560</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200003</td>\n",
       "      <td>7.245058e+09</td>\n",
       "      <td>40069</td>\n",
       "      <td>230.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200003</td>\n",
       "      <td>7.245065e+09</td>\n",
       "      <td>40069</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   icustay_id     charttime  itemid  value\n",
       "0      200001  6.687007e+09  226560  250.0\n",
       "1      200001  6.687089e+09  226560   60.0\n",
       "2      200001  6.687222e+09  226560   50.0\n",
       "3      200003  7.245058e+09   40069  230.0\n",
       "4      200003  7.245065e+09   40069    0.0"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select icustay_id, extract(epoch from charttime) as charttime, itemid, value\n",
    "from mimiciii.outputevents\n",
    "where icustay_id is not null and value is not null and itemid in (40055\t,43175\t,40069,\t40094\t,40715\t,40473\t,40085,\t40057,\t40056\t,40405\t,40428,\t40096,\t40651,226559\t,226560\t,227510\t,226561\t,227489\t,226584,\t226563\t,226564\t,226565\t,226557\t,226558)\n",
    "order by icustay_id, charttime, itemid\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'uo.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pre-admission UO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>icustay_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>itemid</th>\n",
       "      <th>value</th>\n",
       "      <th>datediff_minutes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200006</td>\n",
       "      <td>5.985493e+09</td>\n",
       "      <td>40060</td>\n",
       "      <td>950.0</td>\n",
       "      <td>-91.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200019</td>\n",
       "      <td>6.580174e+09</td>\n",
       "      <td>40060</td>\n",
       "      <td>600.0</td>\n",
       "      <td>-56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200019</td>\n",
       "      <td>6.580184e+09</td>\n",
       "      <td>40060</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-236.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200030</td>\n",
       "      <td>5.707637e+09</td>\n",
       "      <td>40060</td>\n",
       "      <td>240.0</td>\n",
       "      <td>-36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200035</td>\n",
       "      <td>5.894001e+09</td>\n",
       "      <td>226633</td>\n",
       "      <td>400.0</td>\n",
       "      <td>-34.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   icustay_id     charttime  itemid  value  datediff_minutes\n",
       "0      200006  5.985493e+09   40060  950.0             -91.0\n",
       "1      200019  6.580174e+09   40060  600.0             -56.0\n",
       "2      200019  6.580184e+09   40060    0.0            -236.0\n",
       "3      200030  5.707637e+09   40060  240.0             -36.0\n",
       "4      200035  5.894001e+09  226633  400.0             -34.0"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select distinct oe.icustay_id, extract(epoch from oe.charttime) as charttime, oe.itemid, oe.value , 60*24*date_part('day',ic.intime-oe.charttime)  + 60*date_part('hour',ic.intime-oe.charttime) + date_part('min',ic.intime-oe.charttime) as datediff_minutes\n",
    "from mimiciii.outputevents oe, mimiciii.icustays ic\n",
    "where oe.icustay_id=ic.icustay_id and itemid in (\t40060,\t226633)\t\n",
    "order by icustay_id, charttime, itemid\n",
    "\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'preadm_uo.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Real-time input from metavision\n",
    "\n",
    "- Records with no rate = STAT\n",
    "- Records with rate = INFUSION\n",
    "- fluids corrected for tonicity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>icustay_id</th>\n",
       "      <th>starttime</th>\n",
       "      <th>endtime</th>\n",
       "      <th>itemid</th>\n",
       "      <th>amount</th>\n",
       "      <th>rate</th>\n",
       "      <th>tev</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200001</td>\n",
       "      <td>6.687010e+09</td>\n",
       "      <td>6.687010e+09</td>\n",
       "      <td>225943</td>\n",
       "      <td>50.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200001</td>\n",
       "      <td>6.687014e+09</td>\n",
       "      <td>6.687158e+09</td>\n",
       "      <td>225158</td>\n",
       "      <td>200.000</td>\n",
       "      <td>5.0</td>\n",
       "      <td>200.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200001</td>\n",
       "      <td>6.687185e+09</td>\n",
       "      <td>6.687185e+09</td>\n",
       "      <td>225158</td>\n",
       "      <td>250.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>250.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200010</td>\n",
       "      <td>5.130953e+09</td>\n",
       "      <td>5.130958e+09</td>\n",
       "      <td>225158</td>\n",
       "      <td>17.708</td>\n",
       "      <td>12.5</td>\n",
       "      <td>17.708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200010</td>\n",
       "      <td>5.130956e+09</td>\n",
       "      <td>5.130956e+09</td>\n",
       "      <td>225158</td>\n",
       "      <td>1000.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   icustay_id     starttime       endtime  itemid    amount  rate       tev\n",
       "0      200001  6.687010e+09  6.687010e+09  225943    50.000   NaN    50.000\n",
       "1      200001  6.687014e+09  6.687158e+09  225158   200.000   5.0   200.000\n",
       "2      200001  6.687185e+09  6.687185e+09  225158   250.000   NaN   250.000\n",
       "3      200010  5.130953e+09  5.130958e+09  225158    17.708  12.5    17.708\n",
       "4      200010  5.130956e+09  5.130956e+09  225158  1000.000   NaN  1000.000"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "\n",
    "\n",
    "with t1 as\n",
    "(\n",
    "select icustay_id, extract(epoch from starttime) as starttime, extract(epoch from endtime) as endtime, itemid, amount, rate,\n",
    "case when itemid in (30176,30315) then amount *0.25\n",
    "when itemid in (30161) then amount *0.3\n",
    "when itemid in (30020,30015,225823,30321,30186,30211, 30353,42742,42244,225159) then amount *0.5 --\n",
    "when itemid in (227531) then amount *2.75\n",
    "when itemid in (30143,225161) then amount *3\n",
    "when itemid in (30009,220862) then amount *5\n",
    "when itemid in (30030,220995,227533) then amount *6.66\n",
    "when itemid in (228341) then amount *8\n",
    "else amount end as tev -- total equivalent volume\n",
    "from mimiciii.inputevents_mv\n",
    "-- only real time items !!\n",
    "where icustay_id is not null and amount is not null and itemid in (225158,225943,226089,225168,225828,225823,220862,220970,220864,225159,220995,225170,225825,227533,225161,227531,225171,225827,225941,225823,225825,225941,225825,228341,225827,30018,30021,30015,30296,30020,30066,30001,30030,30060,30005,30321,3000630061,30009,30179,30190,30143,30160,30008,30168,30186,30211,30353,30159,30007,30185,30063,30094,30352,30014,30011,30210,46493,45399,46516,40850,30176,30161,30381,30315,42742,30180,46087,41491,30004,42698,42244)\n",
    ")\n",
    "\n",
    "\n",
    "select icustay_id, starttime, endtime, itemid, round(cast(amount as numeric),3) as amount,round(cast(rate as numeric),3) as rate,round(cast(tev as numeric),3) as tev -- total equiv volume\n",
    "from t1\n",
    "order by icustay_id, starttime, itemid\n",
    "\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'fluid_mv.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Real-time input from carevue\n",
    "\n",
    "- In CAREVUE, all records are considered STAT doses!!\n",
    "- fluids corrected for tonicity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>icustay_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>itemid</th>\n",
       "      <th>amount</th>\n",
       "      <th>tev</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>7.245058e+09</td>\n",
       "      <td>30015</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>7.245061e+09</td>\n",
       "      <td>30015</td>\n",
       "      <td>100.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>7.245061e+09</td>\n",
       "      <td>30018</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>7.245065e+09</td>\n",
       "      <td>30015</td>\n",
       "      <td>100.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>7.245065e+09</td>\n",
       "      <td>30018</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   icustay_id     charttime  itemid  amount     tev\n",
       "0    200003.0  7.245058e+09   30015     0.0     0.0\n",
       "1    200003.0  7.245061e+09   30015   100.0    50.0\n",
       "2    200003.0  7.245061e+09   30018  1000.0  1000.0\n",
       "3    200003.0  7.245065e+09   30015   100.0    50.0\n",
       "4    200003.0  7.245065e+09   30018  1000.0  1000.0"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "with t1 as\n",
    "(\n",
    "select icustay_id, extract(epoch from charttime) as charttime, itemid, amount,\n",
    "case when itemid in (30176,30315) then amount *0.25\n",
    "when itemid in (30161) then amount *0.3\n",
    "when itemid in (30020,30321, 30015,225823,30186,30211,30353,42742,42244,225159,225159,225159) then amount *0.5\n",
    "when itemid in (227531) then amount *2.75\n",
    "when itemid in (30143,225161) then amount *3\n",
    "when itemid in (30009,220862) then amount *5\n",
    "when itemid in (30030,220995,227533) then amount *6.66\n",
    "when itemid in (228341) then amount *8\n",
    "else amount end as tev -- total equivalent volume\n",
    "from mimiciii.inputevents_cv\n",
    "-- only RT itemids\n",
    "where amount is not null and itemid in (225158,225943,226089,225168,225828,225823,220862,220970,220864,225159,220995,225170,225825,227533,225161,227531,225171,225827,225941,225823,225825,225941,225825,228341,225827,30018,30021,30015,30296,30020,30066,30001,30030,30060,30005,30321,3000630061,30009,30179,30190,30143,30160,30008,30168,30186,30211,30353,30159,30007,30185,30063,30094,30352,30014,30011,30210,46493,45399,46516,40850,30176,30161,30381,30315,42742,30180,46087,41491,30004,42698,42244)\n",
    "order by icustay_id, charttime, itemid\n",
    ")\n",
    "\n",
    "\n",
    "select icustay_id, charttime, itemid, round(cast(amount as numeric),3) as amount, round(cast(tev as numeric),3) as tev -- total equivalent volume\n",
    "from t1\n",
    "\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'fluid_cv.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pre-admission fluid intake"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>icustay_id</th>\n",
       "      <th>inputpreadm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200001</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200003</td>\n",
       "      <td>1400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200006</td>\n",
       "      <td>2000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200007</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200009</td>\n",
       "      <td>8900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   icustay_id  inputpreadm\n",
       "0      200001          NaN\n",
       "1      200003       1400.0\n",
       "2      200006       2000.0\n",
       "3      200007          NaN\n",
       "4      200009       8900.0"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "\n",
    "\n",
    "with mv as\n",
    "(\n",
    "select ie.icustay_id, sum(ie.amount) as sum\n",
    "from mimiciii.inputevents_mv ie, mimiciii.d_items ci\n",
    "where ie.itemid=ci.itemid and ie.itemid in (30054,30055,30101,30102,30103,30104,30105,30108,226361,226363,226364,226365,226367,226368,226369,226370,226371,226372,226375,226376,227070,227071,227072)\n",
    "group by icustay_id\n",
    "), cv as\n",
    "(\n",
    "select ie.icustay_id, sum(ie.amount) as sum\n",
    "from mimiciii.inputevents_cv ie, mimiciii.d_items ci\n",
    "where ie.itemid=ci.itemid and ie.itemid in (30054,30055,30101,30102,30103,30104,30105,30108,226361,226363,226364,226365,226367,226368,226369,226370,226371,226372,226375,226376,227070,227071,227072)\n",
    "group by icustay_id\n",
    ")\n",
    "\n",
    "\n",
    "select pt.icustay_id,\n",
    "case when mv.sum is not null then mv.sum\n",
    "when cv.sum is not null then cv.sum\n",
    "else null end as inputpreadm\n",
    "from mimiciii.icustays pt\n",
    "left outer join mv\n",
    "on mv.icustay_id=pt.icustay_id\n",
    "left outer join cv\n",
    "on cv.icustay_id=pt.icustay_id\n",
    "order by icustay_id\n",
    "\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'preadm_fluid.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Vasopressors from metavision\n",
    "\n",
    "- Drugs converted in noradrenaline-equivalent\n",
    "- Body weight assumed 80 kg when missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>icustay_id</th>\n",
       "      <th>itemid</th>\n",
       "      <th>starttime</th>\n",
       "      <th>endtime</th>\n",
       "      <th>rate_std</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200024.0</td>\n",
       "      <td>221662</td>\n",
       "      <td>4.959764e+09</td>\n",
       "      <td>4.959780e+09</td>\n",
       "      <td>0.200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200024.0</td>\n",
       "      <td>221906</td>\n",
       "      <td>4.959764e+09</td>\n",
       "      <td>4.959766e+09</td>\n",
       "      <td>0.301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200024.0</td>\n",
       "      <td>221906</td>\n",
       "      <td>4.959766e+09</td>\n",
       "      <td>4.959768e+09</td>\n",
       "      <td>0.200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200024.0</td>\n",
       "      <td>221906</td>\n",
       "      <td>4.959768e+09</td>\n",
       "      <td>4.959779e+09</td>\n",
       "      <td>0.502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200024.0</td>\n",
       "      <td>222315</td>\n",
       "      <td>4.959764e+09</td>\n",
       "      <td>4.959780e+09</td>\n",
       "      <td>0.200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   icustay_id  itemid     starttime       endtime  rate_std\n",
       "0    200024.0  221662  4.959764e+09  4.959780e+09     0.200\n",
       "1    200024.0  221906  4.959764e+09  4.959766e+09     0.301\n",
       "2    200024.0  221906  4.959766e+09  4.959768e+09     0.200\n",
       "3    200024.0  221906  4.959768e+09  4.959779e+09     0.502\n",
       "4    200024.0  222315  4.959764e+09  4.959780e+09     0.200"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select icustay_id, itemid, extract(epoch from starttime) as starttime, extract(epoch from endtime) as endtime, -- rate, -- ,rateuom,\n",
    "case when itemid in (30120,221906,30047) and rateuom='mcg/kg/min' then round(cast(rate as numeric),3)  -- norad\n",
    "when itemid in (30120,221906,30047) and rateuom='mcg/min' then round(cast(rate/80 as numeric),3)  -- norad\n",
    "when itemid in (30119,221289) and rateuom='mcg/kg/min' then round(cast(rate as numeric),3) -- epi\n",
    "when itemid in (30119,221289) and rateuom='mcg/min' then round(cast(rate/80 as numeric),3) -- epi\n",
    "when itemid in (30051,222315) and rate > 0.2 then round(cast(rate*5/60  as numeric),3) -- vasopressin, in U/h\n",
    "when itemid in (30051,222315) and rateuom='units/min' then round(cast(rate*5 as numeric),3) -- vasopressin\n",
    "when itemid in (30051,222315) and rateuom='units/hour' then round(cast(rate*5/60 as numeric),3) -- vasopressin\n",
    "when itemid in (30128,221749,30127) and rateuom='mcg/kg/min' then round(cast(rate*0.45 as numeric),3) -- phenyl\n",
    "when itemid in (30128,221749,30127) and rateuom='mcg/min' then round(cast(rate*0.45 / 80 as numeric),3) -- phenyl\n",
    "when itemid in (221662,30043,30307) and rateuom='mcg/kg/min' then round(cast(rate*0.01 as numeric),3)  -- dopa\n",
    "when itemid in (221662,30043,30307) and rateuom='mcg/min' then round(cast(rate*0.01/80 as numeric),3) else null end as rate_std-- dopa\n",
    "from mimiciii.inputevents_mv\n",
    "where itemid in (30128,30120,30051,221749,221906,30119,30047,30127,221289,222315,221662,30043,30307) and rate is not null and statusdescription <> 'Rewritten'\n",
    "order by icustay_id, itemid, starttime\n",
    "\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'vaso_mv.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Vasopressors from carevue\n",
    "\n",
    "- Same comments as above"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>icustay_id</th>\n",
       "      <th>itemid</th>\n",
       "      <th>charttime</th>\n",
       "      <th>rate_std</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>30128</td>\n",
       "      <td>7.245140e+09</td>\n",
       "      <td>0.225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>30128</td>\n",
       "      <td>7.245140e+09</td>\n",
       "      <td>0.225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>30128</td>\n",
       "      <td>7.245143e+09</td>\n",
       "      <td>0.225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>30128</td>\n",
       "      <td>7.245143e+09</td>\n",
       "      <td>0.225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>30128</td>\n",
       "      <td>7.245143e+09</td>\n",
       "      <td>0.225</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   icustay_id  itemid     charttime  rate_std\n",
       "0    200003.0   30128  7.245140e+09     0.225\n",
       "1    200003.0   30128  7.245140e+09     0.225\n",
       "2    200003.0   30128  7.245143e+09     0.225\n",
       "3    200003.0   30128  7.245143e+09     0.225\n",
       "4    200003.0   30128  7.245143e+09     0.225"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select icustay_id,  itemid, extract(epoch from charttime) as charttime, -- rate, -- rateuom,\n",
    "case when itemid in (30120,221906,30047) and rateuom='mcgkgmin' then round(cast(rate as numeric),3) -- norad\n",
    "when itemid in (30120,221906,30047) and rateuom='mcgmin' then round(cast(rate/80 as numeric),3)  -- norad\n",
    "when itemid in (30119,221289) and rateuom='mcgkgmin' then round(cast(rate as numeric),3) -- epi\n",
    "when itemid in (30119,221289) and rateuom='mcgmin' then round(cast(rate/80 as numeric),3) -- epi\n",
    "when itemid in (30051,222315) and rate > 0.2 then round(cast(rate*5/60  as numeric),3) -- vasopressin, in U/h\n",
    "when itemid in (30051,222315) and rateuom='Umin' and rate < 0.2 then round(cast(rate*5  as numeric),3) -- vasopressin\n",
    "when itemid in (30051,222315) and rateuom='Uhr' then round(cast(rate*5/60  as numeric),3) -- vasopressin\n",
    "when itemid in (30128,221749,30127) and rateuom='mcgkgmin' then round(cast(rate*0.45  as numeric),3) -- phenyl\n",
    "when itemid in (30128,221749,30127) and rateuom='mcgmin' then round(cast(rate*0.45 / 80  as numeric),3) -- phenyl\n",
    "when itemid in (221662,30043,30307) and rateuom='mcgkgmin' then round(cast(rate*0.01   as numeric),3) -- dopa\n",
    "when itemid in (221662,30043,30307) and rateuom='mcgmin' then round(cast(rate*0.01/80  as numeric),3) else null end as rate_std-- dopa\n",
    "-- case when rateuom='mcgkgmin' then 1 when rateuom='mcgmin' then 2 end as uom\n",
    "from mimiciii.inputevents_cv\n",
    "where itemid in (30128,30120,30051,221749,221906,30119,30047,30127,221289,222315,221662,30043,30307) and rate is not null\n",
    "order by icustay_id, itemid, charttime\n",
    "\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'vaso_cv.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mechanical ventilation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>icustay_id</th>\n",
       "      <th>charttime</th>\n",
       "      <th>mechvent</th>\n",
       "      <th>extubated</th>\n",
       "      <th>selfextubated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>7.245058e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>7.245061e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>7.245065e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>7.245068e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200003.0</td>\n",
       "      <td>7.245070e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   icustay_id     charttime  mechvent  extubated  selfextubated\n",
       "0    200003.0  7.245058e+09         0          0              0\n",
       "1    200003.0  7.245061e+09         0          0              0\n",
       "2    200003.0  7.245065e+09         0          0              0\n",
       "3    200003.0  7.245068e+09         0          0              0\n",
       "4    200003.0  7.245070e+09         0          0              0"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "\n",
    "\n",
    "select\n",
    "    icustay_id, extract(epoch from charttime) as charttime    -- case statement determining whether it is an instance of mech vent\n",
    "    , max(\n",
    "      case\n",
    "        when itemid is null or value is null then 0 -- can't have null values\n",
    "        when itemid = 720 and value != 'Other/Remarks' THEN 1  -- VentTypeRecorded\n",
    "        when itemid = 467 and value = 'Ventilator' THEN 1 -- O2 delivery device == ventilator\n",
    "        when itemid in\n",
    "          (\n",
    "          445, 448, 449, 450, 1340, 1486, 1600, 224687 -- minute volume\n",
    "          , 639, 654, 681, 682, 683, 684,224685,224684,224686 -- tidal volume\n",
    "          , 218,436,535,444,459,224697,224695,224696,224746,224747 -- High/Low/Peak/Mean/Neg insp force (\"RespPressure\")\n",
    "          , 221,1,1211,1655,2000,226873,224738,224419,224750,227187 -- Insp pressure\n",
    "          , 543 -- PlateauPressure\n",
    "          , 5865,5866,224707,224709,224705,224706 -- APRV pressure\n",
    "          , 60,437,505,506,686,220339,224700 -- PEEP\n",
    "          , 3459 -- high pressure relief\n",
    "          , 501,502,503,224702 -- PCV\n",
    "          , 223,667,668,669,670,671,672 -- TCPCV\n",
    "          , 157,158,1852,3398,3399,3400,3401,3402,3403,3404,8382,227809,227810 -- ETT\n",
    "          , 224701 -- PSVlevel\n",
    "          )\n",
    "          THEN 1\n",
    "        else 0\n",
    "      end\n",
    "      ) as MechVent\n",
    "      , max(\n",
    "        case when itemid is null or value is null then 0\n",
    "          when itemid = 640 and value = 'Extubated' then 1\n",
    "          when itemid = 640 and value = 'Self Extubation' then 1\n",
    "        else 0\n",
    "        end\n",
    "        )\n",
    "        as Extubated\n",
    "      , max(\n",
    "        case when itemid is null or value is null then 0\n",
    "          when itemid = 640 and value = 'Self Extubation' then 1\n",
    "        else 0\n",
    "        end\n",
    "        )\n",
    "        as SelfExtubated\n",
    "\n",
    "\n",
    "  from mimiciii.chartevents ce\n",
    "  where value is not null\n",
    "  and itemid in\n",
    "  (\n",
    "      640 -- extubated\n",
    "      , 720 -- vent type\n",
    "      , 467 -- O2 delivery device\n",
    "      , 445, 448, 449, 450, 1340, 1486, 1600, 224687 -- minute volume\n",
    "      , 639, 654, 681, 682, 683, 684,224685,224684,224686 -- tidal volume\n",
    "      , 218,436,535,444,459,224697,224695,224696,224746,224747 -- High/Low/Peak/Mean/Neg insp force (\"RespPressure\")\n",
    "      , 221,1,1211,1655,2000,226873,224738,224419,224750,227187 -- Insp pressure\n",
    "      , 543 -- PlateauPressure\n",
    "      , 5865,5866,224707,224709,224705,224706 -- APRV pressure\n",
    "      , 60,437,505,506,686,220339,224700 -- PEEP\n",
    "      , 3459 -- high pressure relief\n",
    "      , 501,502,503,224702 -- PCV\n",
    "      , 223,667,668,669,670,671,672 -- TCPCV\n",
    "      , 157,158,1852,3398,3399,3400,3401,3402,3403,3404,8382,227809,227810 -- ETT\n",
    "      , 224701 -- PSVlevel\n",
    "  )\n",
    "  group by icustay_id, charttime\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "d = pd.read_sql_query(query,conn)\n",
    "d.to_csv(exportdir+'mechvent.csv',index=False,sep='|')\n",
    "d.head()"
   ]
  }
 ],
 "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.14"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "position": {
    "height": "213px",
    "left": "1578px",
    "right": "20px",
    "top": "120px",
    "width": "322px"
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}