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": null,
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Update connection details to MIMIC-III\n",
    "conn = psycopg2.connect(\"dbname = mimic user= postgres password= postgres host=localhost port=5432\")\n",
    "\n",
    "# Update the path for data extraction here\n",
    "exportdir=\"D:\\\\research\\\\healthcare\\\\export_dir\\\\\""
   ]
  },
  {
   "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": null,
   "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": null,
   "metadata": {},
   "outputs": [],
   "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": [
    "sql_file = open('elixhauser-quan.sql','r')\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(sql_file.read())"
   ]
  },
  {
   "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, mimiciii.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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "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
}