<jupyter_start><jupyter_text>eICU Collaborative Research DatabaseThis notebook generates the tables and figures from the eICU Collaborative Research Database paper.<jupyter_code>from __future__ import print_function

# Import libraries
import pandas as pd
import numpy as np
import psycopg2
import matplotlib.pyplot as plt
import os
import tableone

# print out pretty pandas dataframes
from IPython.display import display, HTML

from collections import OrderedDict
# Plot settings
%matplotlib inline
plt.style.use('ggplot')
fontsize = 20 # size for x and y ticks
plt.rcParams['legend.fontsize'] = fontsize
plt.rcParams.update({'font.size': fontsize})
# Database config
sqluser = 'postgres'
dbname = 'eicu'
schema_name = 'eicu_crd'
sqlhost = 'localhost'
sqlport = 5647
# Connect to the database
con = psycopg2.connect(dbname=dbname, user=sqluser, host=sqlhost, port=sqlport)
query_schema = 'SET search_path to public,' + schema_name + ';'<jupyter_output><empty_output><jupyter_text>Display list of tables<jupyter_code>query = \
"""
SELECT tablename 
FROM pg_catalog.pg_tables 
WHERE schemaname like 'eicu_crd'
ORDER BY tablename;
"""

list_of_tables = pd.read_sql_query(query,con)
list_of_tables<jupyter_output><empty_output><jupyter_text>Demographics<jupyter_code>query = query_schema + \
"""
select
    pt.PATIENTUNITSTAYID
  , pt.patienthealthsystemstayid
  , pt.uniquepid
  , pt.hospitalid
  , pt.wardid

  -- **************************** --
  -- ******* DEMOGRAPHICS ******* --
  -- **************************** --
  
  -- patient features
  , pt.gender
  , pt.age
  , case
      when pt.age = '> 89' then 90
      when pt.age = '' then NULL
    else cast(pt.age as NUMERIC)
    end as age_numeric
  , pt.ethnicity

  -- hospital level features
  , hp.numbedscategory as hospital_size
  , hp.teachingstatus as hospital_teaching_status
  , hp.region as hospital_region
  
  -- patient hospital stay features
  , (hospitaldischargeoffset - hospitaladmitoffset) / 60.0 / 24.0 as hospital_los
  , pt.hospitaldischargelocation
  , pt.hospitaldischargestatus -- 'Expired', 'Alive', ''
  , apv.diedinhospital
  
  , pt.hospitaldischargeyear

  -- ICU length of stay in days
  , pt.unitdischargeoffset / 60.0 / 24.0 as unit_los
  , pt.unittype
  , pt.unitadmitsource
  , pt.unitdischargelocation
  , pt.unitdischargestatus

  -- APACHE covariates for discharge location, etc
  , apv.admitsource
  , apv.dischargelocation
  , apv.bedcount
  , apv.readmit

  -- APACHE IVa
  , ag.apachedxgroup
  , pt.apacheadmissiondx

  -- apache comorbidities
  , aav.dialysis
  , apv.aids
  , apv.hepaticfailure
  , apv.cirrhosis
  , apv.diabetes
  , apv.immunosuppression
  , apv.leukemia
  , apv.lymphoma
  , apv.metastaticcancer
  , apv.thrombolytics

  , pt.admissionheight -- height in cm
  , pt.admissionweight -- weight in kg

  -- ********************** --
  -- *** APACHE APS VAR *** --
  -- ********************** --

  , aav.eyes
  , aav.motor
  , aav.verbal
  , aav.eyes + aav.motor + aav.verbal as GCS
  , aav.meds as UnableGCS
  , aav.urine
  , aav.pao2
  , aav.fio2
  , case when aav.pao2 = -1 or aav.fio2 = -1
      then -1
    else
      aav.pao2 / aav.fio2 * 100
    end as pao2fio2_apache


  -- APACHE vital signs
  , aav.temperature
  , aav.respiratoryrate
  , aav.heartrate
  , aav.meanbp

  -- APACHE labs
  -- Noteably, APACHE does not include lactate, platelets, INR, WBC, or PTT
  , aav.albumin
  , aav.bilirubin
  , aav.bun
  , aav.creatinine
  , aav.glucose
  , aav.hematocrit
  , aav.sodium
  , aav.pco2
  , aav.ph
  , aav.intubated
  , aav.wbc

  -- *********************** --
  -- *** APACHE PRED VAR *** --
  -- *********************** --

  -- Mechanical ventilation start and stop times are interfaced into
  -- or documented directly into the respiratory flow sheet
  -- or by updating the care plan in eCareManager.
  -- however, we decided not to use flow sheet, but to use APACHE vent variable.

  , apv.oobIntubDay1 as oobIntubDay1
  , apv.oobVentDay1 as oobVentDay1
  , apv.VENTDAY1 as VentDay1

  -- APACHE PATIENT RESULT --
  , apr.physicianSpeciality as physicianSpeciality
  , apr.acutePhysiologyScore as acutePhysiologyScore
  , apr.apacheScore as apacheScore
  , apr.predictedICUMortality as predictedICUMortality
  , apr.predictedICULOS as predictedICULOS
  , apr.predictedHospitalMortality as predictedHospitalMortality
  , apr.predictedHospitalLOS as predictedHospitalLOS
  , apr.preopMI as preopMI
  , apr.preopCardiacCath as preopCardiacCath
  , apr.PTCAwithin24h as PTCAwithin24h
  , apv.graftCount

from patient pt
left join hospital hp
  on pt.hospitalid = hp.hospitalid
left join apachepredvar apv
  on pt.patientunitstayid = apv.patientunitstayid
left join apacheapsvar aav
  on pt.patientunitstayid = aav.patientunitstayid
left join apachepatientresult apr
  on pt.patientunitstayid = apr.patientunitstayid
  and apr.apacheversion = 'IVa'
left join apache_groups ag
  on pt.patientunitstayid = ag.patientunitstayid
"""

df = pd.read_sql_query(query,con)
# patient counts
N_STAYS = df['patientunitstayid'].nunique()
# note that the number of unique patientunitstayid == df.shape[0]
print('{:6d} unit stays.'.format(N_STAYS))
print('{:6d} hospital stays.'.format(df['patienthealthsystemstayid'].nunique()))
print('{:6d} patients.'.format(df['uniquepid'].nunique()))
print('{:6d} hospitals.'.format(df['hospitalid'].nunique()))
print('{:6d} units (wards).'.format(df['wardid'].nunique()))
# collapse some categories
df['gender_grouped'] = df['gender']
df.loc[ df['gender_grouped'] == 'Other', 'gender_grouped'] = 'Other or Unknown'
df.loc[ df['gender_grouped'] == 'Unknown', 'gender_grouped'] = 'Other or Unknown'
categorical = [u'gender_grouped',
               u'ethnicity', u'hospital_size', u'hospital_teaching_status',
               u'hospital_region', 
               u'hospitaldischargeyear',
               u'unittype',
               # don't include admit/disch locations
               # u'unitadmitsource', u'unitdischargelocation',
               # u'hospitaldischargelocation',
               
               #  below are from apache table
               # u'admitsource', u'dischargelocation',
               u'unitdischargestatus', 
               u'hospitaldischargestatus'
               #u'readmit', 
               # too many categories
               # u'apacheadmissiondx', u'physicianspeciality',
               # below are infrequently documented
               #u'preopmi', u'preopcardiaccath', u'ptcawithin24h', u'graftcount'
              ]

# columns we want to include
columns = ['age_numeric'
           , u'unit_los', u'hospital_los'
           , u'diedinhospital'
           , u'admissionheight', u'admissionweight'
          ]
# output these demographics to a markdown file
t1 = tableone.TableOne(df, columns=columns + categorical, categorical=categorical,
                       nonnormal=['age_numeric', 'unit_los', 'hospital_los'])
print(t1.to_latex())<jupyter_output>/home/alistairewj/.virtualenvs/eicu/lib/python3.5/site-packages/modality.py:930: RuntimeWarning: divide by zero encountered in double_scalars
  k_start = (yU[1]-yU[0])/(xU[1]-xU[0])<jupyter_text>Table of diagnoses<jupyter_code># diagnoses
idxKeep = (~df['predictedhospitalmortality'].isnull()) & (df['predictedhospitalmortality'] != '-1')

t1dx = tableone.TableOne(df.loc[idxKeep,['apacheadmissiondx']],
                         categorical=['apacheadmissiondx'],
                        limit=10)
print(t1dx.tableone.loc['apacheadmissiondx', 'overall'].to_latex())<jupyter_output>\begin{tabular}{ll}
\toprule
{} &      overall \\
level                                              &              \\
\midrule
Sepsis, pulmonary                                  &  6823 (5.01) \\
Infarction, acute myocardial (MI)                  &  5919 (4.34) \\
CVA, cerebrovascular accident/stroke               &  5284 (3.88) \\
CHF, congestive heart failure                      &  4840 (3.55) \\
Sepsis, renal/UTI (including bladder)              &  4284 (3.14) \\
Diabetic ketoacidosis                              &  4001 (2.94) \\
CABG alone, coronary artery bypass grafting        &  3635 (2.67) \\
Rhythm disturbance (atrial, supraventricular)      &  3474 (2.55) \\
Cardiac arrest (with or without respiratory arr... &  3377 (2.48) \\
Emphysema/bronchitis                               &  3304 (2.43) \\
\bottomrule
\end{tabular}<jupyter_text>Table of APACHE diagnoses grouped by clinical meaning<jupyter_code>pretty_names = {'Sepsis': 'Sepsis'
, 'CVA': 'Cerebrovascular accident'
, 'CardiacArrest': 'Cardiac Arrest'
, 'ACS': 'Acute Coronary Syndrome'
, 'RespMedOther': 'Respiratory medicine'
, 'GIBleed': 'Gastrointestinal Bleed'
, 'Trauma': 'Congestive Heart Failure'
, 'CHF': 'Trauma'
, 'CABG': 'Coronary Artery Bypass Graft'
, 'Neuro': 'Neurological'
, 'PNA': 'Pneumonia'
, 'DKA': 'Diabetic Ketoacidosis'
, 'Overdose': 'Overdose'
, 'Asthma-Emphys': 'Asthma/Emphysema'
, 'CVOther': 'Other cardiovascular disease'
, 'ValveDz': 'Valvular disorders'
, 'Coma': 'Coma'
, 'ARF': 'Acute renal failure'
, 'GIObstruction': 'Gastrointestinal obstruction'
, 'ChestPain': 'Chest Pain (unknown cause)'}

# diagnoses for only patients with APACHE predictions
idxKeep = (~df['predictedhospitalmortality'].isnull()) & (df['predictedhospitalmortality'] != '-1')

df_adx = df.loc[idxKeep, ['patientunitstayid','apachedxgroup']].copy()
df_adx['apachedxgroup'] = df_adx['apachedxgroup'].map(pretty_names)
df_adx = df_adx.groupby('apachedxgroup').count()
df_adx['%'] = np.round(df_adx['patientunitstayid']*100.0/(df_adx['patientunitstayid'].sum()),2)

for i, row in df_adx.sort_values('%', ascending=False).iterrows():
    print('{:40s} & {:5g} ({:3.2f}) \\\\'.format(row.name, row['patientunitstayid'], np.round(row['%'],2)))
# diagnoses for only patients with APACHE predictions
idxKeep = (~df['predictedhospitalmortality'].isnull()) & (df['predictedhospitalmortality'] != '-1')
t1dx = tableone.TableOne(df.loc[idxKeep,:], columns=['apachedxgroup'], categorical=['apachedxgroup'])
print('{} - {} = {} missing a diagnosis'.format(df.shape[0], np.sum(idxKeep), df.shape[0]-np.sum(idxKeep)))
t1dx.head()<jupyter_output>200859 - 136236 = 64623 missing a diagnosis<jupyter_text>Table of hospital stats<jupyter_code>query = query_schema + \
"""
-- hospital level features
select
    hp.hospitalid
  , hp.numbedscategory as hospital_size
  , hp.teachingstatus as hospital_teaching_status
  , hp.region as hospital_region
  , count(distinct pt.patientunitstayid) as n_patients
from hospital hp
inner join patient pt
  on hp.hospitalid = pt.hospitalid
group by hp.hospitalid, hp.numbedscategory, hp.teachingstatus, hp.region
"""

df_hosp = pd.read_sql_query(query,con)

# set null to unknown category so tableone uses desired format
df_hosp['hospital_size'].fillna('Unknown',inplace=True)
df_hosp['hospital_teaching_status'].fillna('Unknown',inplace=True)
df_hosp['hospital_region'].fillna('Unknown',inplace=True)
for h in ['hospital_size','hospital_teaching_status','hospital_region']:
    grp = df_hosp.groupby(h)[['hospitalid','n_patients']].agg({'hospitalid': 'count', 'n_patients': 'sum'})
    grp['hospitalid_%'] = np.round(grp['hospitalid'] / grp['hospitalid'].sum() * 100.0,2)
    grp['n_patients_%'] = np.round(grp['n_patients'] / grp['n_patients'].sum() * 100.0,2)
    display(HTML(grp[['hospitalid', 'hospitalid_%', 'n_patients','n_patients_%']].to_html()))<jupyter_output><empty_output><jupyter_text>Information on top diagnoses/treatments<jupyter_code># diagnoses
query = query_schema + \
"""
select diagnosisstring
, count(distinct patientunitstayid) as numobs
from diagnosis
group by diagnosisstring
order by numobs desc
"""
df_misc = pd.read_sql_query(query,con)

df_misc['frac_pat'] = df_misc['numobs']/200859*100.0
print('{} unique diagnoses.'.format(df_misc.shape))
display(df_misc.head())
# treatments
query = query_schema + \
"""
select treatmentstring
, count(distinct patientunitstayid) as numobs
from treatment
group by treatmentstring
order by numobs desc
"""
df_treat = pd.read_sql_query(query,con)

df_treat['frac_pat'] = df_treat['numobs']/200859*100.0
print('{} unique treatments.'.format(df_treat.shape))

for i, row in df_treat.iterrows():
    print('{:6g} ({:5.2f}) - {}'.format(
            row['numobs'], row['frac_pat'], row['treatmentstring']))
    if i>=5:
        break<jupyter_output>(2711, 3) unique treatments.
 34059 (16.96) - pulmonary|ventilation and oxygenation|mechanical ventilation
 17660 ( 8.79) - pulmonary|radiologic procedures / bronchoscopy|chest x-ray
 15200 ( 7.57) - cardiovascular|intravenous fluid|normal saline administration
 13926 ( 6.93) - pulmonary|ventilation and oxygenation|oxygen therapy (< 40%)|nasal cannula
 12424 ( 6.19) - infectious diseases|medications|therapeutic antibacterials
 12383 ( 6.17) - pulmonary|ventilation and oxygenation|non-invasive ventilation<jupyter_text>Table of top diagnosis groups<jupyter_code># diagnosis groups
query = query_schema + \
"""
with t1 as
(
select substring(diagnosisstring,1,position('|' in diagnosisstring)-1) as dx
, patientunitstayid
from diagnosis
)
select dx, count(distinct patientunitstayid) as numpat
from t1
group by dx
order by numpat desc
"""
df_dxgrp = pd.read_sql_query(query,con)

df_dxgrp['frac_pat'] = df_misc['numobs']/200859*100.0
for i, row in df_dxgrp.sort_values('numpat', ascending=False).iterrows():
    print('{:40s} & {:5,d} ({:3.2f}\%) \\\\'.format(row['dx'].capitalize(),
                                                 row['numpat'],
                                                 row['frac_pat']))<jupyter_output>Cardiovascular                           & 104,264 (11.15\%) \\
Pulmonary                                & 64,222 (8.15\%) \\
Neurologic                               & 51,609 (7.28\%) \\
Renal                                    & 43,009 (6.38\%) \\
Endocrine                                & 35,519 (6.15\%) \\
Gastrointestinal                         & 35,223 (6.10\%) \\
Infectious diseases                      & 20,316 (6.01\%) \\
Hematology                               & 19,611 (5.32\%) \\
Burns/trauma                             & 9,208 (5.13\%) \\
Oncology                                 & 7,954 (4.72\%) \\
Toxicology                               & 7,185 (4.47\%) \\
Surgery                                  & 5,723 (3.97\%) \\
General                                  & 1,698 (3.91\%) \\
Transplant                               &   770 (3.75\%) \\
Obstetrics/gynecology                    &    46 (3.52\%) \\
Genitourinary                            &    26 (3.18\%) \\
Musculoskeleta[...]<jupyter_text>Number of observations for vital tables<jupyter_code>query = query_schema + \
"""
with t1 as
(
select patientunitstayid
, count(heartrate) as heartrate
, count(respiration) as respiration
, count(sao2) as sao2
, count(temperature) as temperature
, count(cvp) as cvp
, count(etco2) as etco2
, count(systemicsystolic) as systemicsystolic
, count(systemicdiastolic) as systemicdiastolic
, count(systemicmean) as systemicmean
, count(pasystolic) as pasystolic
, count(padiastolic) as padiastolic
, count(pamean) as pamean
, count(st1) as st1
, count(st2) as st2
, count(st3) as st3
, count(icp) as icp
from vitalperiodic
group by patientunitstayid
)
select
  'patient count' as data_type
, sum(case when heartrate > 0 then 1 else 0 end) as heartrate
, sum(case when respiration > 0 then 1 else 0 end) as respiration
, sum(case when sao2 > 0 then 1 else 0 end) as sao2
, sum(case when temperature > 0 then 1 else 0 end) as temperature
, sum(case when cvp > 0 then 1 else 0 end) as cvp
, sum(case when etco2 > 0 then 1 else 0 end) as etco2
, sum(case when systemicsystolic > 0 then 1 else 0 end) as systemicsystolic
, sum(case when systemicdiastolic > 0 then 1 else 0 end) as systemicdiastolic
, sum(case when systemicmean > 0 then 1 else 0 end) as systemicmean
, sum(case when pasystolic > 0 then 1 else 0 end) as pasystolic
, sum(case when padiastolic > 0 then 1 else 0 end) as padiastolic
, sum(case when pamean > 0 then 1 else 0 end) as pamean
, sum(case when st1 > 0 then 1 else 0 end) as st1
, sum(case when st2 > 0 then 1 else 0 end) as st2
, sum(case when st3 > 0 then 1 else 0 end) as st3
, sum(case when icp > 0 then 1 else 0 end) as icp
from t1
UNION
select
  'observation count' as data_type
, sum(heartrate)::numeric / sum(case when heartrate > 0 then 1 else 0 end) as heartrate
, sum(respiration)::numeric / sum(case when respiration > 0 then 1 else 0 end) as respiration
, sum(sao2)::numeric / sum(case when sao2 > 0 then 1 else 0 end) as sao2
, sum(temperature)::numeric / sum(case when temperature > 0 then 1 else 0 end) as temperature
, sum(cvp)::numeric / sum(case when cvp > 0 then 1 else 0 end) as cvp
, sum(etco2)::numeric / sum(case when etco2 > 0 then 1 else 0 end) as etco2
, sum(systemicsystolic)::numeric / sum(case when systemicsystolic > 0 then 1 else 0 end) as systemicsystolic
, sum(systemicdiastolic)::numeric / sum(case when systemicdiastolic > 0 then 1 else 0 end) as systemicdiastolic
, sum(systemicmean)::numeric / sum(case when systemicmean > 0 then 1 else 0 end) as systemicmean
, sum(pasystolic)::numeric / sum(case when pasystolic > 0 then 1 else 0 end) as pasystolic
, sum(padiastolic)::numeric / sum(case when padiastolic > 0 then 1 else 0 end) as padiastolic
, sum(pamean)::numeric / sum(case when pamean > 0 then 1 else 0 end) as pamean
, sum(st1)::numeric / sum(case when st1 > 0 then 1 else 0 end) as st1
, sum(st2)::numeric / sum(case when st2 > 0 then 1 else 0 end) as st2
, sum(st3)::numeric / sum(case when st3 > 0 then 1 else 0 end) as st3
, sum(icp)::numeric / sum(case when icp > 0 then 1 else 0 end) as icp
from t1;
"""
df_vp = pd.read_sql_query(query,con)
df_vp.set_index('data_type',inplace=True)
df_vp.T
pretty_labels = {'heartrate': 'Heart rate'
, 'respiration': 'Respiration rate'
, 'sao2': 'Peripheral oxygen saturation'
, 'temperature': 'Temperature'
, 'cvp': 'Central venous pressure'
, 'etco2': 'End tidal carbon dioxide concentration'
, 'systemicsystolic': 'Invasive systolic blood pressure'
, 'systemicdiastolic': 'Invasive diastolic blood pressure'
, 'systemicmean': 'Invasive mean blood pressure'
, 'pasystolic': 'Systolic pulmonary artery pressure'
, 'padiastolic': 'Diastolic pulmonary artery pressure'
, 'pamean': 'Mean pulmonary artery pressure'
, 'st1': 'ST level'
, 'st2': 'ST level'
, 'st3': 'ST level'
, 'icp': 'Intracranial pressure'}

df_vp.sort_values('patient count',axis=1,ascending=False,inplace=True)

for c in df_vp.columns:
    if c != 'data_type':
        print('{:40s}'.format(pretty_labels[c]), end=' & ')
        print('\\colname{:20s}'.format('{' + c + '}'),end=' & ')
        print('{:6,d} ({:3.2f}\%)'.format(
                df_vp.loc['patient count',c].astype(int),
                df_vp.loc['patient count',c].astype(float)/200859*100.0),
              end=' & ')
        print('{:,d} ({:3.1f})'.format((df_vp.loc['observation count',c]*df_vp.loc['patient count',c]).astype(int),
                               df_vp.loc['observation count',c]),
              end=' \\\\\n')
# vital aperiodic
query = query_schema + \
"""
with t1 as
(
select patientunitstayid
, count(noninvasivesystolic) as noninvasivesystolic
, count(noninvasivediastolic) as noninvasivediastolic
, count(noninvasivemean) as noninvasivemean
, count(paop) as paop
, count(cardiacoutput) as cardiacoutput
, count(cardiacinput) as cardiacinput
, count(svr) as svr
, count(svri) as svri
, count(pvr) as pvr
, count(pvri) as pvri
from vitalaperiodic
group by patientunitstayid
)
select
  sum(case when noninvasivesystolic > 0 then 1 else 0 end) as noninvasivesystolic
, sum(case when noninvasivediastolic > 0 then 1 else 0 end) as noninvasivediastolic
, sum(case when noninvasivemean > 0 then 1 else 0 end) as noninvasivemean
, sum(case when paop > 0 then 1 else 0 end) as paop
, sum(case when cardiacoutput > 0 then 1 else 0 end) as cardiacoutput
, sum(case when cardiacinput > 0 then 1 else 0 end) as cardiacinput
, sum(case when svr > 0 then 1 else 0 end) as svr
, sum(case when svri > 0 then 1 else 0 end) as svri
, sum(case when pvr > 0 then 1 else 0 end) as pvr
, sum(case when pvri > 0 then 1 else 0 end) as pvri
from t1;
"""
df_vap = pd.read_sql_query(query,con)
df_vap.T
for c in df_vap.columns:
    print('{:6g} ({:5.2f}%) - {}'.format(
            df_vap.loc[0,c],
            df_vap.loc[0,c].astype(float)/200859*100.0,
            c))<jupyter_output>189620 (94.40%) - noninvasivesystolic
189626 (94.41%) - noninvasivediastolic
189663 (94.43%) - noninvasivemean
  2072 ( 1.03%) - paop
  5591 ( 2.78%) - cardiacoutput
  4203 ( 2.09%) - cardiacinput
  5022 ( 2.50%) - svr
  4156 ( 2.07%) - svri
  1898 ( 0.94%) - pvr
  1875 ( 0.93%) - pvri<jupyter_text>Number of distinct laboratory measurements per patient<jupyter_code># query to load data from the patient table
query = query_schema + \
"""
SELECT patientunitstayid
, COUNT(DISTINCT labname) AS num_distinct_labs
FROM lab
GROUP BY patientunitstayid
"""

df_lab = pd.read_sql_query(query, con)

plt.figure(figsize=[12,8])
plt.hist(df_lab['num_distinct_labs'], bins=np.linspace(0,160,33))
plt.show()<jupyter_output><empty_output><jupyter_text>Funnel plot<jupyter_code>query = query_schema + \
"""
SELECT pt.patientunitstayid, pt.hospitalid
, apr.predictedhospitalmortality as pred
, CASE WHEN pt.hospitaldischargestatus = 'Expired' then 1
       WHEN pt.hospitaldischargestatus = 'Alive' then 0
    else NULL end as target
FROM patient pt
INNER JOIN apachepatientresult apr
ON pt.patientunitstayid = apr.patientunitstayid
AND apr.apacheversion = 'IVa'
WHERE apr.predictedhospitalmortality != ''
AND apr.predictedhospitalmortality IS NOT NULL
AND apr.predictedhospitalmortality != '-1'
AND pt.hospitaldischargestatus in ('Alive','Expired')
"""

df = pd.read_sql_query(query,con)
df['pred'] = pd.to_numeric(df['pred'])
# count patients per hospital
idx = df.groupby('hospitalid')['patientunitstayid'].count()
# isolate to only those with count >= 100
idx = idx.index[idx>=100]
# create index for df
idx = np.in1d(df['hospitalid'],idx)

grp = df.loc[idx,:].groupby('hospitalid')

plt.figure(figsize=[12,8])
plt.plot(grp['patientunitstayid'].count(),
         grp['pred'].mean() / grp['target'].mean(),
        'o', markersize=8)

plt.plot([0,5000],
         np.ones(2) * np.mean(grp['pred'].mean() / grp['target'].mean()),
        'k--', linewidth=2)
plt.show()<jupyter_output><empty_output><jupyter_text>Data completion for patientunitstayidBelow query returns whether a `patientUnitStayId` is in each table in the database.<jupyter_code># query to load data from the patient table
query = query_schema + \
"""
SELECT
  pt.patientunitstayid, pt.hospitalid
  , CASE WHEN adx.patientunitstayid  IS NOT NULL then 1 else 0 end as admissionDx
  , CASE WHEN adr.patientunitstayid  IS NOT NULL then 1 else 0 end as admissionDrug
  , CASE WHEN al.patientunitstayid   IS NOT NULL THEN 1 ELSE 0 END AS allergy
  , CASE WHEN aav.patientunitstayid  IS NOT NULL then 1 else 0 end as apacheApsVar
  , CASE WHEN apv.patientunitstayid  IS NOT NULL then 1 else 0 end as apachePredVar
  , CASE WHEN apr.patientunitstayid  IS NOT NULL then 1 else 0 end as apachePatientResult
  , CASE WHEN cpp.patientunitstayid  IS NOT NULL then 1 else 0 end as carePlanCareProvider
  , CASE WHEN cpe.patientunitstayid  IS NOT NULL then 1 else 0 end as carePlanEOL
  , CASE WHEN cpg.patientunitstayid  IS NOT NULL then 1 else 0 end as carePlanGeneral
  , CASE WHEN cpgl.patientunitstayid IS NOT NULL then 1 else 0 end as carePlanGoal
  , CASE WHEN cpi.patientunitstayid  IS NOT NULL then 1 else 0 end as carePlanInfectiousDisease
  , CASE WHEN cl.patientunitstayid   IS NOT NULL THEN 1 ELSE 0 END AS customLab
  , CASE WHEN di.patientunitstayid   IS NOT NULL then 1 else 0 end as diagnosis
  , CASE WHEN id.patientunitstayid   IS NOT NULL then 1 else 0 end as infusionDrug
  , CASE WHEN io.patientunitstayid   IS NOT NULL THEN 1 ELSE 0 END AS intakeOutput
  , CASE WHEN la.patientunitstayid   IS NOT NULL then 1 else 0 end as lab
  , CASE WHEN me.patientunitstayid   IS NOT NULL then 1 else 0 end as medication
  , CASE WHEN mi.patientunitstayid   IS NOT NULL THEN 1 ELSE 0 END AS microLab
  , CASE WHEN no.patientunitstayid   IS NOT NULL THEN 1 ELSE 0 END AS note
  , CASE WHEN na.patientunitstayid   IS NOT NULL THEN 1 ELSE 0 END AS nurseAssessment
  , CASE WHEN nca.patientunitstayid  IS NOT NULL THEN 1 ELSE 0 END AS nurseCare
  , CASE WHEN nc.patientunitstayid   IS NOT NULL THEN 1 ELSE 0 END AS nurseCharting
  , CASE WHEN pa.patientunitstayid   IS NOT NULL then 1 else 0 end as pastHistory
  , CASE WHEN pe.patientunitstayid   IS NOT NULL THEN 1 ELSE 0 END AS physicalExam
  , CASE WHEN rca.patientunitstayid  IS NOT NULL THEN 1 ELSE 0 END AS respiratoryCare
  , CASE WHEN rc.patientunitstayid   IS NOT NULL THEN 1 ELSE 0 END AS respiratoryCharting
  , CASE WHEN tr.patientunitstayid   IS NOT NULL then 1 else 0 end as treatment
  , CASE WHEN va.patientunitstayid   IS NOT NULL then 1 else 0 end as vitalAperiodic
  , CASE WHEN vp.patientunitstayid   IS NOT NULL then 1 else 0 end as vitalPeriodic
FROM patient pt
LEFT JOIN (select distinct patientunitstayid from admissiondx) adx
    ON pt.patientunitstayid = adx.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from admissionDrug) adr
    ON pt.patientunitstayid = adr.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from allergy) al
    ON pt.patientunitstayid = al.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from apacheapsvar) aav
    ON pt.patientunitstayid = aav.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from apachepredvar) apv
    ON pt.patientunitstayid = apv.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from apachepatientresult) apr
    ON pt.patientunitstayid = apr.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from careplancareprovider) cpp
    ON pt.patientunitstayid = cpp.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from careplaneol) cpe
    ON pt.patientunitstayid = cpe.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from careplangeneral) cpg
    ON pt.patientunitstayid = cpg.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from careplangoal) cpgl
    ON pt.patientunitstayid = cpgl.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from careplaninfectiousdisease) cpi
    ON pt.patientunitstayid = cpi.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from customlab) cl
    ON pt.patientunitstayid = cl.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from diagnosis) di
    ON pt.patientunitstayid = di.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from infusionDrug) id
    ON pt.patientunitstayid = id.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from intakeoutput) io
    ON pt.patientunitstayid = io.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from lab) la
    ON pt.patientunitstayid = la.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from medication) me
    ON pt.patientunitstayid = me.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from microlab) mi
    ON pt.patientunitstayid = mi.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from note) no
    ON pt.patientunitstayid = no.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from nurseassessment) na
    ON pt.patientunitstayid = na.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from nursecare) nca
    ON pt.patientunitstayid = nca.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from nursecharting) nc
    ON pt.patientunitstayid = nc.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from pasthistory) pa
    ON pt.patientunitstayid = pa.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from physicalexam) pe
    ON pt.patientunitstayid = pe.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from respiratorycare) rca
    ON pt.patientunitstayid = rca.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from respiratorycharting) rc
    ON pt.patientunitstayid = rc.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from treatment) tr
    ON pt.patientunitstayid = tr.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from vitalaperiodic) va
    ON pt.patientunitstayid = va.patientunitstayid
LEFT JOIN (select distinct patientunitstayid from vitalperiodic) vp
    ON pt.patientunitstayid = vp.patientunitstayid;
"""

df_dc = pd.read_sql_query(query,con)
# group by hospital - percent data completion for each table
grp = df_dc.groupby('hospitalid')

# group into 5 categories:
# 0%, 0-20%, 20-60%, 60-80%, 80-100%
lower_range = [ -1,  0, 20, 60,  80]
upper_range = [  0, 20, 60, 80, 100]
grp = grp.mean()*100.0
num_hosp = grp.shape[0]

df_count = pd.DataFrame(columns=df_dc.columns)
df_count.drop(['patientunitstayid', 'hospitalid'], axis=1, inplace=True)
for i in range(len(upper_range)):
    df_count.loc[i,:] = None
    for c in df_count.columns:
        num_hosp_in_range=np.sum(
            (grp[c] > lower_range[i]) & (grp[c] <= upper_range[i])
            )
        frac_hosp = float(num_hosp_in_range)/num_hosp*100.0
        df_count.loc[i, c] = frac_hosp
# print out header
print('& \\multicolumn{5}{c}{Coverage} \\\\ \\hline')
print('Table Name &')
print('No data (0\%) &')
print('Low coverage (0-20\%) &')
print('Medium coverage (20-60\%) &')
print('High coverage (60-80\%) &')
print('Excellent coverage (80-100\%) \\\\ \\hline')

# print each row
for i, row in df_count.T.iterrows():
    print('{:40s}'.format( '\\tblname{' + row.name + '}'), end=' ')
    for r in row:
        print('& {:5.2f}'.format(r), end=' ')
    print('\\\\')
df_heatmap = df_count.copy()
df_heatmap = (df_heatmap*256/100).astype(int)
df_heatmap

plt.figure(figsize=[12,8])
plt.plot()
plt.imshow(df_heatmap.T, interpolation='none', aspect='auto')
plt.yticks(np.arange(df_heatmap.shape[1]), df_heatmap.columns)
plt.xticks(np.arange(5), ['0%', '0-20%','20-60%','60-80%','80-100%'])
# disable grid
plt.grid()
plt.show()<jupyter_output><empty_output><jupyter_text>Figure for a single patient<jupyter_code># select a single ICU stay
patientunitstayid = 142493
query_schema = 'set search_path to eicu_crd_demo;'
# print APACHE diagnosis for patient
query = query_schema + \
"""
SELECT patientunitstayid
, apacheadmissiondx as dx
FROM patient
WHERE patientunitstayid = """ + str(patientunitstayid) + """
"""

dx = pd.read_sql_query(query,con)
print('Diagnosis: {}'.format(dx.loc[0,'dx']))
# pull lab data
query = query_schema + \
"""
SELECT patientunitstayid
, labresultoffset as "offset"
, labname as "label"
, labresult as "value"
FROM lab
WHERE patientunitstayid = """ + str(patientunitstayid) + """
ORDER BY "offset"
"""

lab = pd.read_sql_query(query,con)
lab.head()
# pull meds data
query = query_schema + \
"""
SELECT patientunitstayid
, drugstartoffset as "offset"
, drugname as "label"
, dosage as "value"
FROM medication
WHERE patientunitstayid = """ + str(patientunitstayid) + """
AND drugname IS NOT NULL
AND dosage IS NOT NULL
ORDER BY "offset"
"""

med = pd.read_sql_query(query,con)
med.head()
# pull meds data
query = query_schema + \
"""
SELECT patientunitstayid
, intakeoutputoffset as "offset"
, celllabel as "label"
, cellvaluenumeric as "value"
FROM intakeoutput
WHERE patientunitstayid = """ + str(patientunitstayid) + """
AND celllabel IS NOT NULL
AND cellvaluenumeric IS NOT NULL
ORDER BY "offset"
"""

io = pd.read_sql_query(query,con)
io.head()
# pull infusion drug data
query = query_schema + \
"""
SELECT patientunitstayid
, infusionoffset as "offset"
, drugname as "label"
, coalesce(drugrate,infusionrate) as "value"
FROM infusiondrug
WHERE patientunitstayid = """ + str(patientunitstayid) + """
AND coalesce(drugrate, infusionrate) IS NOT NULL
AND drugname IS NOT NULL
ORDER BY "offset"
"""

inf = pd.read_sql_query(query,con)
inf.head()
# pull vitalaperiodic data
query = query_schema + \
"""
SELECT patientunitstayid
, observationoffset as "offset"
, noninvasivesystolic sysbp
, noninvasivediastolic diasbp
, noninvasivemean meanbp
FROM vitalaperiodic
WHERE patientunitstayid = """ + str(patientunitstayid) + """
AND noninvasivemean IS NOT NULL
ORDER BY "offset"
"""
vap = pd.read_sql_query(query,con)
vap.head()
# pull vitalperiodic data
query = query_schema + \
"""
SELECT patientunitstayid
, observationoffset as "offset"
, systemicsystolic sysbp
, systemicdiastolic diasbp
, systemicmean meanbp
, heartrate
, sao2 as spo2
, respiration
FROM vitalperiodic
WHERE patientunitstayid = """ + str(patientunitstayid) + """
ORDER BY "offset"
"""

vp = pd.read_sql_query(query,con)
vp.head()
# nursecharting data
query = query_schema + \
"""
SELECT patientunitstayid
, nursingchartoffset as "offset"
, avg((case when nursingchartcelltypevallabel = 'Heart Rate' AND nursingchartcelltypevalname = 'Heart Rate' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as HeartRate                 
, avg((case when nursingchartcelltypevallabel = 'Respiratory Rate' AND nursingchartcelltypevalname = 'Respiratory Rate' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as RespiratoryRate           
, avg((case when nursingchartcelltypevallabel = 'Invasive BP' AND nursingchartcelltypevalname = 'Invasive BP Systolic' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as InvasiveBPSystolic       
, avg((case when nursingchartcelltypevallabel = 'Invasive BP' AND nursingchartcelltypevalname = 'Invasive BP Diastolic' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as InvasiveBPDiastolic       
, avg((case when nursingchartcelltypevallabel = 'Arterial Line MAP (mmHg)' AND nursingchartcelltypevalname = 'Value' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as InvasiveBPMean    
, avg((case when nursingchartcelltypevallabel = 'O2 Saturation' AND nursingchartcelltypevalname = 'O2 Saturation' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as spo2
, avg((case when nursingchartcelltypevallabel = 'Non-Invasive BP' AND nursingchartcelltypevalname = 'Non-Invasive BP Diastolic' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as NonInvasiveBPDiastolic  
, avg((case when nursingchartcelltypevallabel = 'Non-Invasive BP' AND nursingchartcelltypevalname = 'Non-Invasive BP Systolic' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as NonInvasiveBPSystolic
, avg((case when nursingchartcelltypevallabel = 'Non-Invasive BP' AND nursingchartcelltypevalname = 'Non-Invasive BP Mean' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as NonInvasiveBPMean
, avg((case when nursingchartcelltypevallabel = 'Temperature' AND nursingchartcelltypevalname = 'Temperature (C)' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as Temperature
, avg((case when nursingchartcelltypevallabel = 'Patient s Comfort/Function (Pain) GOAL At Rest' AND nursingchartcelltypevalname = 'Value' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as paingoal                      
, avg((case when nursingchartcelltypevallabel = 'Pain Score/Goal' AND nursingchartcelltypevalname = 'Pain Score' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as PainScore
, avg((case when nursingchartcelltypevallabel = 'Verbal Response' AND nursingchartcelltypevalname = 'Value' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as verbal
, avg((case when nursingchartcelltypevallabel = 'Eye Opening' AND nursingchartcelltypevalname = 'Value' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as eyes
, avg((case when nursingchartcelltypevallabel = 'Glasgow coma score' AND nursingchartcelltypevalname = 'GCS Total' THEN nursingchartvalue ELSE NULL END)::NUMERIC) as gcs
FROM nursecharting
WHERE patientunitstayid = """ + str(patientunitstayid) + """
GROUP BY patientunitstayid, nursingchartoffset
ORDER BY "offset"
"""

nc = pd.read_sql_query(query,con)
nc.head()
# plot the data

# convert from minutes to our time unit here
TIME_FACTOR = 1.0/60.0
TIME_UNIT = 'hours'

plt.figure(figsize=[12,10])
p_hr = plt.plot(vp['offset']*TIME_FACTOR, vp['heartrate'], '.--', label='_no_legend_')
p_nibp = plt.plot(vap['offset']*TIME_FACTOR, vap['meanbp'], '.--', label='_no_legend_')
p_ibp = plt.plot(vp['offset']*TIME_FACTOR, vp['meanbp'], '.--', label='_no_legend_')
p_o2 = plt.plot(vp['offset']*TIME_FACTOR, vp['spo2'], '.--', label='_no_legend_')
p_rr = plt.plot(vp['offset']*TIME_FACTOR, vp['respiration'], '.--', label='_no_legend_')

# nurse validated
plt.plot(nc['offset']*TIME_FACTOR, nc['heartrate'], '^',
         markersize=12, markerfacecolor=None, color=p_hr[0].get_color(),
         label='Heart rate, beats/minute')
plt.plot(nc['offset']*TIME_FACTOR, nc['invasivebpmean'], 's',
         markersize=12, markerfacecolor=None, color=p_ibp[0].get_color(), label='Mean blood pressure (non-invasive), mmHg')
plt.plot(nc['offset']*TIME_FACTOR, nc['noninvasivebpmean'], 'o',
         markersize=12, markerfacecolor=None, color=p_nibp[0].get_color(), label='Mean blood pressure (invasive), mmHg')
plt.plot(nc['offset']*TIME_FACTOR, nc['spo2'], 'd',
         markersize=12, markerfacecolor=None, color=p_o2[0].get_color(), label='Oxygen saturation, %')
plt.plot(nc['offset']*TIME_FACTOR, nc['respiratoryrate'], 'v',
         markersize=12, markerfacecolor=None, color=p_rr[0].get_color(), label='Respiration rate, breaths/min')
"""
# Plot infusions as a scaled line
infdrg_dict = OrderedDict([
        ['Norepinephrine, mcg/min', [145, 'Norepinephrine (mcg/min)', 0, 10 ]]
        #['Normal Saline (ml/hr)', [155, 'NS (ml/hr)', 0, 500]]
    ])

xloc = 0
for item in infdrg_dict:
    yloc = infdrg_dict[item][0]
    label = infdrg_dict[item][1]
    plot_lo = infdrg_dict[item][2]
    plot_hi = infdrg_dict[item][3]
    
    scale_data = 10.0/(plot_hi-plot_lo)
    plt.text(xloc, yloc, item, fontsize=18, horizontalalignment='right')
    # plot the trend over time
    df_item = inf.loc[inf['label']==label,:]
    # limit to first 2 days
    df_item = df_item.loc[df_item['offset']<=(1440*2),:]
    scaled_data = df_item['value'].astype(float)
    # shift low to 0
    scaled_data = scaled_data - plot_lo
    # shift high to 1
    scaled_data = scaled_data / plot_hi
    # move up to [yloc, yloc+5]
    scaled_data = (scaled_data)*5.0 + yloc
    
    plt.plot( df_item['offset']*TIME_FACTOR, scaled_data, 'o--', label='_nolegend_' )
    
# Plot intake output as a single marker indicating start time
med_dict = OrderedDict([
        ['Normal Saline (ml/hr)', [155, 'NS (ml/hr)', 0, 500]]
    ])

# plot a single marker for each instance - regardless of the value of the instance
xloc = 0
for item in med_dict:
    yloc = med_dict[item][0]
    label = med_dict[item][1]
    plt.text(xloc, yloc, item, fontsize=18, horizontalalignment='right')
    
    df_item = med.loc[med['label']==label,:]
    
    # limit to first 2 days
    df_item = df_item.loc[(df_item['offset']>0) & (df_item['offset']<=(1440*2)),:]
    
    plt.plot( df_item['offset']*TIME_FACTOR, (yloc+2.0)*np.ones(df_item.shape[0]), 'o',
             label='_nolegend_' )
"""

# Plot intake output as a single marker indicating start time
io_dict = OrderedDict([
        ['Red blood cell transfusion', [125, 'Volume-Transfuse red blood cells']]
        #['Normal Saline (ml/hr)', [155, 'NS (ml/hr)', 0, 500]]
    ])

# plot a single marker for each instance - regardless of the value of the instance
xloc = 0
for item in io_dict:
    yloc = io_dict[item][0]
    label = io_dict[item][1]
    plt.text(xloc, yloc, item, fontsize=18, horizontalalignment='right', color='#555555')
    
    df_item = io.loc[io['label']==label,:]
    
    # limit to first 2 days
    df_item = df_item.loc[(df_item['offset']>0) & (df_item['offset']<=(1440*2)),:]
    
    plt.plot( df_item['offset']*TIME_FACTOR, (yloc+2.0)*np.ones(df_item.shape[0]), '+',
             markersize=20, mew=8,
             label='_nolegend_' )

# Plot labs as text on the plot
labs = OrderedDict([
        #['Creatinine, mg/dL', [195, 'creatinine']],
        #['White blood cell, K/uL', [ 185, 'WBC x 1000' ]],
        #['Troponin, g/dL', [175, 'troponin - I']],
        #['Lactate, mmol/L', [175, 'lactate']],
        ['PaO2, mmHg', [155, 'paO2']],
        ['Hemoglobin, g/dL', [145, 'Hgb']],
        ['Hematocrit, %', [135, 'Hct']]
    ])

xloc = 0
for l in labs:
    yloc = labs[l][0]
    label = labs[l][1]
    
    # plot the trend over time
    df_item = lab.loc[lab['label']==label,:]
    # limit to first 2 days
    df_item = df_item.loc[(df_item['offset']<=(1440*2)) & (df_item['offset']>0),:]
    
    plt.text(xloc, yloc, l, fontsize=18, horizontalalignment='right', color='#555555')
    for i, row in df_item.iterrows():
        # put text for pao2 without a decimal
        if l=='PaO2, mmHg':
            v = int(row['value'])
        else:
            v = row['value']
            
        plt.text(row['offset']*TIME_FACTOR, yloc, v,
                 fontsize=16, color='#555555', label='_nolegend_') 

plt.yticks(np.arange(0, 120, 20))
plt.xticks(np.arange(0, 12.01, 4))
plt.xlim([0,12])
plt.ylim([0,170])
plt.xlabel('Time since unit admission (' + TIME_UNIT + ')')
plt.ylabel('Measurement value')
# manually position the y-label
ax = plt.gca()
ax.yaxis.set_label_coords(-0.1, 0.3)
leg = plt.legend(loc='lower right')
plt.setp(leg.get_texts(), color='#555555')
plt.savefig('ExamplePatient.pdf', bbox_inches='tight')
plt.savefig('ExamplePatient.png', bbox_inches='tight')
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12419089943000676, 'fraction_numerical': 0.026229349821273305, 'mean_word_length': 4.011498426531106, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 97, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 21, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '412901', 'license': 'MIT', 'n_tokens_mistral': 15383, 'n_tokens_neox': 13794, 'n_words': 3696, 'path': 'eicu-data-paper.ipynb', 'repo_name': 'vishwas1234567/eicu-data-paper', 'size': '41404'}