In [1]:
# Import libraries
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import psycopg2
# used to calculate AUROC/accuracy
from sklearn import metrics
%matplotlib inline
In [2]:
# create a database connection
sqluser = 'postgres'
dbname = 'mimic'
schema_name = 'mimiciii'
# Connect to local postgres version of mimic
con = psycopg2.connect(dbname=dbname, user=sqluser)
cur = con.cursor()
In [3]:
cur.execute('SET search_path to ' + schema_name)
query = \
"""
select ie.icustay_id, ie.hadm_id
, adm.admittime
, ie.intime
, min(transfertime) as FirstService
, min(transfertime) - adm.admittime as DeltaAdmit
, min(transfertime) - ie.intime as DeltaIn
from icustays ie
inner join admissions adm
on ie.hadm_id = adm.hadm_id
left join services se
on ie.hadm_id = se.hadm_id
group by ie.icustay_id, ie.hadm_id, ie.intime, adm.admittime
having (min(transfertime)) > intime;
"""
data = pd.read_sql_query(query,con)
data
Out[3]:
Looks like we have 5 cases where the first service occurs between 1-6 days after the first ICU admission.
In [4]:
# extract surgery definitions using the three methods
cur.execute('SET search_path to ' + schema_name)
query = \
"""
WITH serv as
(
-- note, 80% of patients only ever have one service
-- also note, it is possible a patient's trajectory is like this:
-- 1. elective admission
-- 2. medical service
-- 3. ICU admission
-- 4. change to surgical service
-- we do *not* treat this as an elective surgery
select ie.icustay_id
, se.curr_service
, case when lower(curr_service) like '%surg%' then 1 else 0 end as surgical
, ROW_NUMBER() over
(
PARTITION BY ICUSTAY_ID
ORDER BY TRANSFERTIME
) as serviceOrder
from icustays ie
left join services se
on ie.hadm_id = se.hadm_id
)
, cpt as
(
select cpt.hadm_id
, max(case when surgeryflag = 2 then 1 else 0 end) as surgical
from cptevents cpt
left join surgeryflag_cpt cptflg
on cpt.cpt_number between cptflg.cptstart and cptflg.cptstop
group by cpt.hadm_id
)
, icd as
(
select icd.hadm_id
, max(case when surgeryflag = 2 then 1 else 0 end) as surgical
from procedures_icd icd
left join surgeryflag_icd9 flg
on icd.icd9_code = flg.icd9_code
group by icd.hadm_id
)
select
ie.subject_id, ie.hadm_id, ie.icustay_id
, adm.ADMISSION_TYPE
, cpt.surgical as surgical_cpt
, icd.surgical as surgical_icd
, serv.surgical as surgical_service
from icustays ie
-- it's useful to get admission type
inner join admissions adm
on ie.hadm_id = adm.hadm_id
-- get CPT codes associated with surgery
left join cpt
on ie.hadm_id = cpt.hadm_id
-- get ICD codes associated with surgery
left join icd
on ie.hadm_id = icd.hadm_id
-- get first service
left join serv
on ie.icustay_id = serv.icustay_id and serv.serviceOrder = 1
"""
data = pd.read_sql_query(query,con)
data.head().T
Out[4]:
In [5]:
cur.close()
con.close()
In [6]:
data.shape
Out[6]:
In [7]:
print('% surgical status: {:2.2f}% - CPT.').format(100*data.surgical_cpt.mean())
print('% surgical status: {:2.2f}% - ICD.').format(100*data.surgical_icd.mean())
print('% surgical status: {:2.2f}% - SERVICE.').format(100*data.surgical_service.mean())