<jupyter_start><jupyter_text>Setup<jupyter_code>import os 
import pandas as pd

project_id="upbeat-legacy-282508" # need to specify accordingly
os.environ["GOOGLE_CLOUD_PROJECT"]=project_id
from google.colab import auth
auth.authenticate_user()
print('Authenticated')
def run_query(query):
    df = pd.io.gbq.read_gbq(query, project_id=project_id, verbose=False, configuration={'query':{'useLegacySql': False}})
    print(len(df), 'rows')
    print(df.head())
    return df

# an example
df = run_query('''
SELECT
  subject_id,
  hadm_id,
  stay_id
FROM
  `physionet-data.mimic_icu.icustays`
LIMIT 10
''')<jupyter_output>10 rows
   subject_id   hadm_id   stay_id
0    10122297  25825366  36349608
1    17168310  21560534  33376903
2    15703353  29272306  35111434
3    10308232  21297383  30153687
4    13307171  21128752  38043905<jupyter_text>Cohort extraction The cohort extraction in eICU is more complicated than mimiv-iv as eICU does not provide explicitly an event on invasive mechanical ventilation. Furthermore, all events in eICU have only a chart time instead of duration, making it challenging to decide when a continuous event actually ends. So there are two main tasks to tackle in order to construct the prolonged MV cohort with eICU. First, identify charted events/items that indicate invasive MV; and second, decide start and end time of the MV. Below is the SQL modified based on https://github.com/nus-mornin-lab/oxygenation_kc/blob/master/data-extraction/eICU/eicu_oxygen_therapy.sql to address the two tasks and extract the eICU cohort. Again, only first ventilation in an ICU with duration longer than 24h is kept, with an over-72h flag.<jupyter_code>sql_vent_cohort = '''



WITH respchart AS (
    SELECT *
    FROM `physionet-data.eicu_crd.respiratorycharting`
)

, nursechart AS (
    SELECT *
    FROM `physionet-data.eicu_crd.nursecharting`
)

, pat AS (
    SELECT *
    FROM `physionet-data.eicu_crd.patient`
)

-- paterns modified based on 
-- https://github.com/nus-mornin-lab/oxygenation_kc/blob/master/data-extraction/eICU/eicu_oxygen_therapy.sql

-- The categories are invasive ventilation,
-- noninvasive ventilation, and either/or.
, ventsettings0 AS (
    SELECT patientunitstayid AS icustay_id
        , charttime
        , CASE

            -- Invasive ventilation
            WHEN
                string IN (
                    'plateau pressure',
                    'postion at lip',
                    'position at lip',
                    'pressure control'
                )
                OR string LIKE '%set vt%'
                OR string LIKE '%sputum%'
                OR string LIKE '%rsbi%'
                OR string LIKE '%tube%'
                OR string LIKE '%ett%'
                OR string LIKE '%endotracheal%'
                OR string LIKE '%tracheal suctioning%'
                OR string LIKE '%tracheostomy%'
                OR string LIKE '%reintubation%' # re?
                OR string LIKE '%intubation%' # remove re?
                OR string LIKE '%assist controlled%'
                OR string LIKE '%volume controlled%'
                OR string LIKE '%pressure controlled%'
                OR string LIKE '%trach collar%'
            THEN 0

            -- Noninvasive ventilation
            WHEN
                string IN (
                    'bi-pap',
                    'ambubag'
                )
                OR string LIKE '%ipap%'
                OR string LIKE '%niv%'
                OR string LIKE '%epap%'
                OR string LIKE '%mask leak%'
                OR string LIKE '%volume assured%'
                OR string LIKE '%non-invasive ventilation%'
                OR string LIKE '%cpap%'
            THEN 1

            -- Either invasive or noninvasive ventilation:
            WHEN
                string IN (
                    'flowtrigger',
                    'peep',
                    'tv/kg ibw',
                    'mean airway pressure',
                    'peak insp. pressure',
                    'exhaled mv',
                    'exhaled tv (machine)',
                    'exhaled tv (patient)',
                    'flow sensitivity',
                    'peak flow',
                    'f total',
                    'pressure to trigger ps',
                    'adult con setting set rr',
                    'adult con setting set vt',
                    'vti',
                    'exhaled vt',
                    'adult con alarms hi press alarm',
                    'mve',
                    'respiratory phase',
                    'inspiratory pressure, set',
                    'a1: high exhaled vt',
                    'set fraction of inspired oxygen (fio2)',
                    'insp flow (l/min)',
                    'adult con setting spont exp vt',
                    'spont tv',
                    'pulse ox results vt',
                    'vt spontaneous (ml)',
                    'peak pressure',
                    'ltv1200',
                    'tc'
                )
                OR (
                    string LIKE '%vent%'
                    AND NOT string LIKE '%hyperventilat%'
                )
                OR string LIKE '%tidal%'
                OR string LIKE '%flow rate%'
                OR string LIKE '%minute volume%'
                OR string LIKE '%leak%'
                OR string LIKE '%pressure support%'
                OR string LIKE '%peep%'
                OR string LIKE '%tidal volume%'
            THEN 2

            ELSE NULL

        END AS vent_type
        , activeUponDischarge
    FROM (

        SELECT patientunitstayid
            , nursingChartOffset AS charttime
            , LOWER(nursingchartvalue) AS string
            , NULL AS activeUponDischarge
        FROM nursechart

        UNION ALL

        SELECT patientunitstayid
            , respchartoffset AS charttime
            , LOWER(respchartvaluelabel) AS string
            , NULL AS activeUponDischarge
        FROM respchart

        UNION ALL

        -- Oxygen device from respchart
        SELECT patientunitstayid
            , respchartoffset AS charttime
            , LOWER(respchartvalue) AS string
            , NULL AS activeUponDischarge
        FROM respchart
        WHERE LOWER(respchartvaluelabel) IN (
            'o2 device',
            'respiratory device',
            'ventilator type',
            'oxygen delivery method'
        )

        UNION ALL

        -- The treatment table also contains info on oxygen therapy.
        SELECT patientunitstayid
            , treatmentoffset AS charttime
            , LOWER(treatmentstring) AS string
            , activeUponDischarge
        FROM `physionet-data.eicu_crd.treatment`
    )
    WHERE charttime >= -60

)
-- 
-- Ensure charttime is unique
, ventsettings AS (
    SELECT icustay_id
        , charttime
        -- , MIN(vent_type) AS vent_type
        , MIN(vent_type) AS vent_type
        , MAX(activeUponDischarge) AS activeUponDischarge
    FROM ventsettings0
    WHERE vent_type IS NOT NULL
    GROUP BY icustay_id, charttime #, vent_type
)
-- , vent_ordered as (
-- select * from ventsettings
-- order by icustay_id, charttime, vent_type
-- )
, vd0 as
(
  select
    *
    -- this carries over the previous charttime which had an event
    , LAG(CHARTTIME, 1) OVER (partition by icustay_id order by charttime)
    as charttime_lag
    -- similarly, this is one for vent_type, to later identify first vent_type=0
    , LAG(vent_type, 1) OVER (partition by icustay_id order by charttime)
    as vent_type_lag
  from ventsettings
)


, vd1 as
(
  select
      icustay_id
      , charttime
      , vent_type
      , vent_type_lag
      , activeUponDischarge
    -- If the time since the last oxygen therapy event is more than 24 hours,
    -- we consider that ventilation had ended in between.
    -- That is, the next ventilation record corresponds to a new ventilation session.
      , CASE
        WHEN charttime - charttime_lag > 24*60 THEN 1
        WHEN charttime_lag IS NULL THEN 1 -- No lag can be computed for the very first record
        ELSE 0
      END AS newvent
      
  -- use the staging table with only oxygen therapy records from chart events
  FROM vd0
)
-- select * from vd1 # 3677741 rows
, vd2 as
(
  select vd1.*
  -- create a cumulative sum of the instances of new ventilation
  -- this results in a monotonic integer assigned to each instance of ventilation
  , SUM( newvent )
      OVER ( partition by icustay_id order by charttime )
    as ventnum
  from vd1
)


-- -- now we convert the charttime of the ventilator setting into durations
-- -- here we focus on invasive ventilation, which has vent_type=0
-- -- meanwhile, chartevents with vent_type=2 can be either invasive or non-invasive ventilation
-- -- so here we pick the stays with an invasive chart event as long as vent_type=0 is in the stay
-- -- and pick the earliest charttime (equals either 0 or 2) as the beginning time 
-- -- and find the end time as following

,vd3 AS (
  select icustay_id, charttime, vent_type, ventnum, activeUponDischarge
      , CASE
        WHEN vent_type - vent_type_lag = -2 THEN 0 #'non2in'
        WHEN vent_type - vent_type_lag = 2 THEN 1 #'in2non'
        WHEN vent_type - vent_type_lag = 0 THEN 2 #'non2non'
        WHEN vent_type_lag IS NULL THEN 2 #'non2non' -- No lag can be computed for the very first record
        ELSE 3
      END AS vent_type_transit
  from vd2
  where (vent_type=0) or (vent_type=2)
)


, vd_endtime AS 
(
    SELECT icustay_id
        , ventnum
        , CASE 
            WHEN (
                MAX(activeUponDischarge)
                -- vent_end cannot be later than the unit discharge time.
                -- However, unitdischargeoffset often seems too low.
                -- So, we only use it if it yields and extension of the
                -- ventilation time from ventsettings.
                AND MAX(charttime)+60 < MAX(pat.unitdischargeoffset)

            ) THEN MAX(pat.unitdischargeoffset)

            -- End time is currently a charting time
            -- Since these are usually recorded hourly, ventilation is actually longer.
            -- We therefore add 60 minutes to the last time.
            ELSE MAX(charttime)+60
        END AS vent_end
    FROM vd3
        LEFT JOIN pat 
        ON vd3.icustay_id = pat.patientunitstayid
    GROUP BY icustay_id, ventnum
)



, vd_starttime AS
(
    SELECT icustay_id
        , ventnum
        , MIN(charttime) AS vent_start
    FROM vd3
    WHERE vent_type_transit = 0 # 116765 -> final 22372
    # otherwise final count is 40749 (ventnum=1 and dur>24 and start>=0)
    GROUP BY icustay_id, ventnum
)

-- select *
-- from vd_starttime
-- where icustay_id =1609092

, vd_final AS 
(
    select e.icustay_id, e.ventnum, s.vent_start, e.vent_end
    from vd_endtime e
    left join vd_starttime s on e.icustay_id = s.icustay_id and e.ventnum = s.ventnum
)
, vd_dur AS
(
    select *
        , (vent_end - vent_start) / 60 AS vent_duration
    from vd_final
--     order by icustay_id 
)
select icustay_id patientunitstayid, vent_start, vent_end, vent_duration
, case 
    when vent_duration > 72 then 1
    else 0
  end over72h
from vd_dur 
where ventnum=1 and vent_duration>=24 and vent_start>=0
-- order by patientunitstayid 


'''
# create a view
dataset_id = 'default' # change accordingly
view_id = f"{project_id}.{dataset_id}.vent_cohort_eicu_view"
# need to run the following to create the view for the first time


from google.cloud import bigquery

client = bigquery.Client()

view = bigquery.Table(view_id)
view.view_query = sql_vent_cohort

view = client.create_table(view)
print('Created view')<jupyter_output>Created view<jupyter_text>Ft SQL - set 1 8 features: hr min/max, resp min/max, temp min/max,  ph max, spo2 min (peep max excluded)<jupyter_code>HOUR_GAP = 24
sql_features = f'''
with bg_ft as (
    SELECT 
    v.patientunitstayid, 
    bg.pH #, peep
    FROM `{view_id}` v
    left join `physionet-data.eicu_crd_derived.pivoted_bg` bg
    on v.patientunitstayid=bg.patientunitstayid
    where bg.chartoffset <= v.vent_start + {HOUR_GAP}*60 and bg.chartoffset >= v.vent_start
)

, vs_ft as (
    SELECT 
    v.patientunitstayid, 
    vt.heartrate, vt.temperature, vt.respiratoryrate, vt.spo2
    FROM `{view_id}` v
    left join `physionet-data.eicu_crd_derived.pivoted_vital` vt
    on v.patientunitstayid = vt.patientunitstayid
    where vt.chartoffset <= v.vent_start + {HOUR_GAP}*60 and vt.chartoffset >= v.vent_start
)
select 
v.*
# v.patientunitstayid
,max(b.pH) ph_max
, min(s.spo2) spo2_min

,min(s.heartrate) hr_min
,max(s.heartrate) hr_max
,min(s.respiratoryrate) resp_min
,max(s.respiratoryrate) resp_max
,min(s.temperature) temp_min 
,max(s.temperature) temp_max

from `{view_id}` v
left join bg_ft b on b.patientunitstayid=v.patientunitstayid
left join vs_ft s on s.patientunitstayid=v.patientunitstayid
group by patientunitstayid, vent_start, vent_end, vent_duration, over72h

'''<jupyter_output><empty_output><jupyter_text>Ft SQL - set 2glucose max/min, apsiii, co2_total max/min, mbp_arterial_max, mbp_ni_min<jupyter_code>sql_ft_new = f'''
with lab as (
  SELECT 
  v.patientunitstayid 
  ,glucose ,TotalCO2 
  
  FROM `{view_id}` v
  left join `physionet-data.eicu_crd_derived.pivoted_lab` l
  on v.patientunitstayid =l.patientunitstayid 
  where l.chartoffset <= v.vent_start + {HOUR_GAP}*60 and l.chartoffset >=v.vent_start 
), bp as (
  SELECT
  v.patientunitstayid 
  ,nibp_mean 
  
  , case 
    when nibp_mean is null and ibp_mean is null then null
    when nibp_mean is null or ibp_mean is null then coalesce(nibp_mean, ibp_mean)
    else (nibp_mean+ibp_mean)/2

    end as bp_mean
  FROM `{view_id}` v
  left join `physionet-data.eicu_crd_derived.pivoted_vital` b
  on v.patientunitstayid =b.patientunitstayid 
  where b.chartoffset <= v.vent_start + {HOUR_GAP}*60 and b.chartoffset >=v.vent_start 
)
select 
v.patientunitstayid
, max(glucose) glucose_max
, min(glucose) glucose_min
, max(TotalCO2) co2_total_max
, min(TotalCO2) co2_total_min

, max(bp_mean) mbp_arterial_max
, min(nibp_mean) mbp_ni_min

FROM `{view_id}` v
left join lab l on v.patientunitstayid =l.patientunitstayid 
left join bp b on v.patientunitstayid =b.patientunitstayid 
group by patientunitstayid, vent_start, vent_end, vent_duration, over72h

'''
sql_apsiii = f'''
select
v.patientunitstayid
,avg(acutephysiologyscore) apsiii

FROM `{view_id}` v
left join `physionet-data.eicu_crd.apachepatientresult` a
on a.patientunitstayid =v.patientunitstayid 
group by patientunitstayid, vent_start, vent_end, vent_duration, over72h
'''<jupyter_output><empty_output><jupyter_text>Extraction & download<jupyter_code>sql_cohort = f'''
select *
from `{view_id}` v
'''

cohort_df = run_query(sql_cohort)
cols = ['patientunitstayid', 'vent_start', 'vent_end', 'vent_duration','over72h']
df_ft1 = run_query(sql_features)
df_ft_new = run_query(sql_ft_new)
df_aps = run_query(sql_apsiii)
df_peep = peep.drop(columns=['peep_avg'])
df_peep
len(cohort_df), len(df_ft1), len(df_ft_new), len(df_peep)
outdf = cohort_df.merge(df_ft1, 
                        on=cols, how='left').merge(df_ft_new, 
                                   on=['patientunitstayid'], how='left').merge(df_aps, 
                                                                         on=['patientunitstayid'], how='left').merge(df_peep, on=cols, how='left')
outdf                                                                        
outdf.columns
outdf.to_csv('ft17_eicu_new.csv', index=False)
os.listdir('.')

from google.colab import files
files.download('ft17_eicu_new.csv') 

len(cohort_df), len(df_ft1), len(df_ft_new)
final_df=cohort_df.merge(df_ft1, on=cols, how='left').merge(df_ft_new, on=['patientunitstayid'], how='left').merge(df_aps, on=['patientunitstayid'], how='left')
final_df
final_df.to_csv('ft15_invasive_cohort_eicu.csv', index=False)
os.listdir('.')

from google.colab import files
files.download('ft15_invasive_cohort_eicu.csv')<jupyter_output><empty_output><jupyter_text>Pt Characteristics<jupyter_code><jupyter_output><empty_output><jupyter_text>stay ids, hospital ids, unit/hosp disch, disch statusgender, age, weight, height, bmi, elective surgeryapsiii, sofavaso, rrtpeep, fio2, plateau<jupyter_code># demographic and discharge status
sql_demo = '''
select 
patientunitstayid, 
uniquepid patient_id,
patienthealthsystemstayid hospital_stay_id, 
hospitalid hospital_id, 
unitdischargeoffset,
hospitaldischargeoffset,
unitdischargestatus, 
hospitaldischargestatus, 
gender, age,
admissionheight height
 from `physionet-data.eicu_crd.patient` 
'''
# where patientunitstayid in ({})
# '''.format(','.join(icustay_ids))

demo = run_query(sql_demo)
df = cohort_df[['patientunitstayid']].merge(demo, on=['patientunitstayid'])
df
sql_weight = '''
SELECT * FROM `physionet-data.eicu_crd_derived.pivoted_weight`
'''
weight = run_query(sql_weight)
adm_w = weight[weight.weight_type=='admit'][['patientunitstayid', 'weight']]

tmp = df.merge(adm_w, on='patientunitstayid', how='left')
# impute missing adm weight with daily weight
for pid in tmp[tmp.weight.isna()]['patientunitstayid']:
    try:
        record = weight[weight.patientunitstayid==pid][['patientunitstayid', 'weight']].iloc[0]
#         ls.append(record)
        row = tmp[tmp['patientunitstayid']==pid].index[0]
        tmp.loc[row, 'weight'] = record['weight']
    except:
        pass
tmp
df = tmp.copy()

# clean unit cm
df.height = df.height.apply(lambda x: x*100 if x>0 and x<10 else x)
def bmi_func(w, h):
    if h==0 or w==0:
        return None
    if h>500:
        return None
    
    bmi = w/ (h/100)**2
    if bmi <100 and bmi>1:
        return bmi
    else:
        return None

df['bmi'] = df.apply(lambda r: bmi_func(r['weight'], r['height']) if r[['weight', 'height']].notnull().all() else None , axis=1)


df
sql_elect = '''
SELECT patientunitstayid, electivesurgery FROM `physionet-data.eicu_crd.apachepredvar`
'''

sql_diag = '''
SELECT patientunitstayid
, case when apachedxgroup = 'PNA' then 1 else 0 end pneumonia
, case when apachedxgroup = 'Sepsis' then 1 else 0 end sepsis
FROM `physionet-data.eicu_crd_derived.apache_groups` 
'''

elect = run_query(sql_elect)
diag = run_query(sql_diag)
elect = elect.fillna(0)
df = df.merge(elect, how='left').merge(diag, how='left')

df.electivesurgery.fillna(0, inplace=True)
df
sql_vaso = '''
with tmp as (
SELECT
  patientunitstayid,
  chartoffset,
  vasopressor # has only 1 value
FROM
  `physionet-data.eicu_crd_derived.pivoted_treatment_vasopressor`
) 
select patientunitstayid, max(vasopressor) as vasopressor
from tmp group by patientunitstayid
'''

sql_rrt = '''
with tmp as (
SELECT
   patientunitstayid, treatmentoffset, 
   case when LOWER(treatmentstring) LIKE 'renal|dialysis%'
  OR LOWER(treatmentstring) LIKE 'renal|pro%dialysis%' then 1 else 0 end dialysis
FROM
  `physionet-data.eicu_crd.treatment`
)
select 
  patientunitstayid, max(dialysis) as dialysis
from tmp group by patientunitstayid
'''
vaso = run_query(sql_vaso)
rrt = run_query(sql_rrt)
eicu = df.merge(vaso[['patientunitstayid', 'vasopressor']], how='left').merge(rrt, how='left')

eicu.vasopressor.fillna(0, inplace=True)
eicu.dialysis.fillna(0, inplace=True)

eicu<jupyter_output><empty_output><jupyter_text>SOFA<jupyter_code>sql_sofa = '''
--https://github.com/nus-mornin-lab/oxygenation_kc/blob/master/data-extraction/eICU/eicu_sofa_results.sql

--IMPORTANT: Please see around l277 for some possible issues.
--
--Based on code by <NAME>, with changes to allow it to be used 
-- on BQ

WITH 

pat AS (
SELECT * FROM `physionet-data.eicu_crd.patient`),

lab AS (
SELECT * FROM `physionet-data.eicu_crd.lab`),

vitalperiodic AS (
SELECT * FROM `physionet-data.eicu_crd.vitalperiodic`),

vitalaperiodic AS (
SELECT * FROM `physionet-data.eicu_crd.vitalaperiodic`),

infusiondrug AS (
SELECT * FROM `physionet-data.eicu_crd.infusiondrug`),

respiratorycare AS (
SELECT * FROM `physionet-data.eicu_crd.respiratorycare`),

treatment AS (
SELECT * FROM `physionet-data.eicu_crd.treatment`),

careplangeneral AS (
SELECT * FROM `physionet-data.eicu_crd.careplangeneral`),

physicalexam AS (
SELECT * FROM `physionet-data.eicu_crd.physicalexam`),

diag AS (
SELECT * FROM `physionet-data.eicu_crd.diagnosis`),

chart AS (
SELECT * FROM `physionet-data.eicu_crd.nursecharting`),

apsiii_raw AS (
SELECT * FROM `physionet-data.eicu_crd.apachepatientresult`),

intakeoutput AS (
SELECT * FROM `physionet-data.eicu_crd.intakeoutput`),

respchart AS (
SELECT * FROM `physionet-data.eicu_crd.respiratorycharting`),


cohort1 AS (
SELECT * FROM `physionet-data.eicu_crd.patient`),


t1 as -- MAP
(
WITH tt1 as
(
select patientunitstayid,
min( case when noninvasivemean is not null then noninvasivemean else null end) as map
from vitalaperiodic
where observationoffset between -1440 and 1440
group by patientunitstayid
), 

tt2 as
(
select patientunitstayid,
min( case when systemicmean is not null then systemicmean else null end) as map
from vitalperiodic
where observationoffset between -1440 and 1440
group by patientunitstayid
)


select pt.patientunitstayid, case when tt1.map is not null then tt1.map
when tt2.map is not null then tt2.map
else null end as map
from pat pt
left outer join tt1
on tt1.patientunitstayid=pt.patientunitstayid
left outer join tt2
on tt2.patientunitstayid=pt.patientunitstayid
order by pt.patientunitstayid
),

t2 as --DOPAMINE
(
select distinct  patientunitstayid, max(
case when lower(drugname) like '%(ml/hr)%' then round(cast(drugrate as numeric)/3,3) -- rate in ml/h * 1600 mcg/ml / 80 kg / 60 min, to convert in mcg/kg/min
when lower(drugname) like '%(mcg/kg/min)%' then cast(drugrate as numeric)
else null end ) as dopa
from infusiondrug id
where lower(drugname) like '%dopamine%' and infusionoffset between -120 and 1440 and REGEXP_CONTAINS(drugrate, '^[0-9]{0,5}$') and drugrate<>'' and drugrate<>'.'
group by patientunitstayid
order by patientunitstayid


), 

t3 as  --NOREPI
(
select distinct patientunitstayid, max(case when lower(drugname) like '%(ml/hr)%' and drugrate<>''  and drugrate<>'.' then round(cast(drugrate as numeric)/300,3) -- rate in ml/h * 16 mcg/ml / 80 kg / 60 min, to convert in mcg/kg/min
when lower(drugname) like '%(mcg/min)%' and drugrate<>'' and drugrate<>'.'  then round(cast(drugrate as numeric)/80 ,3)-- divide by 80 kg
when lower(drugname) like '%(mcg/kg/min)%' and drugrate<>'' and drugrate<>'.' then cast(drugrate as numeric)
else null end ) as norepi


from infusiondrug id
where lower(drugname) like '%norepinephrine%'  and infusionoffset between -120 and 1440  and REGEXP_CONTAINS(drugrate, '^[0-9]{0,5}$') and drugrate<>'' and drugrate<>'.'
group by patientunitstayid
order by patientunitstayid


), 

t4 as  --DOBUTAMINE
(
select distinct patientunitstayid, 1 as dobu
from infusiondrug id
where lower(drugname) like '%dobutamin%' and drugrate <>'' and drugrate<>'.' and drugrate <>'0' and REGEXP_CONTAINS(drugrate, '^[0-9]{0,5}$') and infusionoffset between -120 and 1440
order by patientunitstayid
),

sofacv as
(
select pt.patientunitstayid, t1.map, t2.dopa, t3.norepi, t4.dobu,
(case when dopa>=15 or norepi>0.1 then 4
when dopa>5 or (norepi>0 and norepi <=0.1) then 3
when dopa<=5 or dobu > 0 then 2
when map <70 then 1
else 0 end) as SOFA_cv --COMPUTE SOFA CV
from cohort1 pt
left outer join t1
on t1.patientunitstayid=pt.patientunitstayid
left outer join t2
on t2.patientunitstayid=pt.patientunitstayid
left outer join t3
on t3.patientunitstayid=pt.patientunitstayid
left outer join t4
on t4.patientunitstayid=pt.patientunitstayid
order by pt.patientunitstayid
),


-- SOFA-RESPI


sofarespi as
(
with tempo2 as 
(
with tempo1 as
(
with t1 as --FIO2 from respchart
(
select *
from
(
select distinct patientunitstayid, max(cast(respchartvalue as numeric)) as rcfio2
-- , max(case when respchartvaluelabel = 'FiO2' then respchartvalue else null end) as fiO2
from respchart
where respchartoffset between -120 and 1440 and respchartvalue <> '' and REGEXP_CONTAINS(respchartvalue, '^[0-9]{0,2}$')
group by patientunitstayid
) as tempo
where rcfio2 >20 -- many values are liters per minute!
order by patientunitstayid


), 

t2 as --FIO2 from nursecharting
(
select distinct patientunitstayid, max(cast(nursingchartvalue as numeric)) as ncfio2
from chart nc
where lower(nursingchartcelltypevallabel) like '%fio2%' and REGEXP_CONTAINS(nursingchartvalue, '^[0-9]{0,2}$') and nursingchartentryoffset between -120 and 1440
group by patientunitstayid


), 

t3 as --sao2 from vitalperiodic
(
select patientunitstayid,
min( case when sao2 is not null then sao2 else null end) as sao2
from vitalperiodic
where observationoffset between -1440 and 1440
group by patientunitstayid


), 

t4 as --pao2 from lab
(
select patientunitstayid,
min(case when lower(labname) like 'pao2%' then labresult else null end) as pao2
from lab
where labresultoffset between -1440 and 1440
group by patientunitstayid


), 

t5 as --airway type combining 3 sources (1=invasive)
(


with t1 as --airway type from respcare (1=invasive) (by resp therapist!!)
(
select distinct patientunitstayid,
max(case when airwaytype in ('Oral ETT','Nasal ETT','Tracheostomy') then 1 else NULL end) as airway  -- either invasive airway or NULL
from respiratorycare
where respcarestatusoffset between -1440 and 1440


group by patientunitstayid-- , respcarestatusoffset
-- order by patientunitstayid-- , respcarestatusoffset
),


t2 as --airway type from respcharting (1=invasive)
(
select distinct patientunitstayid, 1 as ventilator
from respchart rc
where respchartvalue like '%ventilator%'
or respchartvalue like '%vent%'
or respchartvalue like '%bipap%'
or respchartvalue like '%840%'
or respchartvalue like '%cpap%'
or respchartvalue like '%drager%'
or respchartvalue like 'mv%'
or respchartvalue like '%servo%'
or respchartvalue like '%peep%'
and respchartoffset between -1440 and 1440
group by patientunitstayid
-- order by patientunitstayid
),


t3 as --airway type from treatment (1=invasive)


(
select distinct patientunitstayid, max(case when treatmentstring in
('pulmonary|ventilation and oxygenation|mechanical ventilation',
'pulmonary|ventilation and oxygenation|tracheal suctioning',
'pulmonary|ventilation and oxygenation|ventilator weaning',
'pulmonary|ventilation and oxygenation|mechanical ventilation|assist controlled',
'pulmonary|radiologic procedures / bronchoscopy|endotracheal tube',
'pulmonary|ventilation and oxygenation|oxygen therapy (> 60%)',
'pulmonary|ventilation and oxygenation|mechanical ventilation|tidal volume 6-10 ml/kg',
'pulmonary|ventilation and oxygenation|mechanical ventilation|volume controlled',
'surgery|pulmonary therapies|mechanical ventilation',
'pulmonary|surgery / incision and drainage of thorax|tracheostomy',
'pulmonary|ventilation and oxygenation|mechanical ventilation|synchronized intermittent',
'pulmonary|surgery / incision and drainage of thorax|tracheostomy|performed during current admission for ventilatory support',
'pulmonary|ventilation and oxygenation|ventilator weaning|active',
'pulmonary|ventilation and oxygenation|mechanical ventilation|pressure controlled',
'pulmonary|ventilation and oxygenation|mechanical ventilation|pressure support',
'pulmonary|ventilation and oxygenation|ventilator weaning|slow',
'surgery|pulmonary therapies|ventilator weaning',
'surgery|pulmonary therapies|tracheal suctioning',
'pulmonary|radiologic procedures / bronchoscopy|reintubation',
'pulmonary|ventilation and oxygenation|lung recruitment maneuver',
'pulmonary|surgery / incision and drainage of thorax|tracheostomy|planned',
'surgery|pulmonary therapies|ventilator weaning|rapid',
'pulmonary|ventilation and oxygenation|prone position',
'pulmonary|surgery / incision and drainage of thorax|tracheostomy|conventional',
'pulmonary|ventilation and oxygenation|mechanical ventilation|permissive hypercapnea',
'surgery|pulmonary therapies|mechanical ventilation|synchronized intermittent',
'pulmonary|medications|neuromuscular blocking agent',
'surgery|pulmonary therapies|mechanical ventilation|assist controlled',
'pulmonary|ventilation and oxygenation|mechanical ventilation|volume assured',
'surgery|pulmonary therapies|mechanical ventilation|tidal volume 6-10 ml/kg',
'surgery|pulmonary therapies|mechanical ventilation|pressure support',
'pulmonary|ventilation and oxygenation|non-invasive ventilation',
'pulmonary|ventilation and oxygenation|non-invasive ventilation|face mask',
'pulmonary|ventilation and oxygenation|non-invasive ventilation|nasal mask',
'pulmonary|ventilation and oxygenation|mechanical ventilation|non-invasive ventilation',
'pulmonary|ventilation and oxygenation|mechanical ventilation|non-invasive ventilation|face mask',
'surgery|pulmonary therapies|non-invasive ventilation',
'surgery|pulmonary therapies|non-invasive ventilation|face mask',
'pulmonary|ventilation and oxygenation|mechanical ventilation|non-invasive ventilation|nasal mask',
'surgery|pulmonary therapies|non-invasive ventilation|nasal mask',
'surgery|pulmonary therapies|mechanical ventilation|non-invasive ventilation',
'surgery|pulmonary therapies|mechanical ventilation|non-invasive ventilation|face mask'
) then 1  else NULL end) as interface   -- either ETT/NiV or NULL
from treatment
where treatmentoffset between -1440 and 1440
group by patientunitstayid-- , treatmentoffset, interface
order by patientunitstayid-- , treatmentoffset
),

t4 as
(
select distinct patientunitstayid,
max(case when cplitemvalue like '%Intubated%' then 1 else NULL end) as airway  -- either invasive airway or NULL
from careplangeneral
where cplitemoffset between -1440 and 1440
group by patientunitstayid -- , respcarestatusoffset


)

--Note from Michael
--
--Previously the below line was "case when t1.airway is not null or t2.ventilator is not null or t3.interface is not null or t4.interface is not null then 1 else null end as mechvent
--
--t4 doesn't have interface, removing

select pt.patientunitstayid,
case when t1.airway is not null or t2.ventilator is not null or t3.interface is not null then 1 else null end as mechvent --summarize
from cohort1 pt
left outer join t1
on t1.patientunitstayid=pt.patientunitstayid
left outer join t2
on t2.patientunitstayid=pt.patientunitstayid
left outer join t3
on t3.patientunitstayid=pt.patientunitstayid
left outer join t4
on t4.patientunitstayid=pt.patientunitstayid

--Note from Michael
--
--Previously the last line was "on t4.patientunitstayid=pt.patientunitstayidorder by pt.patientunitstayid"
--
--No idea what this is. "patientunitstayidorder site:eicu-crd.mit.edu" has no hits.


)


select pt.patientunitstayid, t3.sao2, t4.pao2, 
(case when t1.rcfio2>20 then t1.rcfio2 when t2.ncfio2 >20 then t2.ncfio2  when t1.rcfio2=1 or t2.ncfio2=1 then 100 else null end) as fio2, t5.mechvent
from cohort1 pt
left outer join t1
on t1.patientunitstayid=pt.patientunitstayid
left outer join t2
on t2.patientunitstayid=pt.patientunitstayid
left outer join t3
on t3.patientunitstayid=pt.patientunitstayid
left outer join t4
on t4.patientunitstayid=pt.patientunitstayid
left outer join t5
on t5.patientunitstayid=pt.patientunitstayid
-- order by pt.patientunitstayid
)


select *, -- coalesce(fio2,nullif(fio2,0),21) as fn, nullif(fio2,0) as nullifzero, coalesce(coalesce(nullif(fio2,0),21),fio2,21) as ifzero21 ,
coalesce(pao2,100)/coalesce(coalesce(nullif(fio2,0),21),fio2,21) as pf, coalesce(sao2,100)/coalesce(coalesce(nullif(fio2,0),21),fio2,21) as sf
from tempo1
)


select patientunitstayid, 
(case when pf <1 or sf <0.67 then 4  --COMPUTE SOFA RESPI
when pf between 1 and 2 or sf between 0.67 and 1.41 then 3
when pf between 2 and 3 or sf between 1.42 and 2.2 then 2
when pf between 3 and 4 or sf between 2.21 and 3.01 then 1
when pf > 4 or sf> 3.01 then 0 else 0 end ) as SOFA_respi
from tempo2
order by patientunitstayid
),


-- SOFA-RENAL


sofarenal as
(
with t1 as --CREATININE
(
select pt.patientunitstayid,
max(case when lower(labname) like 'creatin%' then labresult else null end) as creat
from pat pt
left outer join lab
on pt.patientunitstayid=lab.patientunitstayid
where labresultoffset between -1440 and 1440
group by pt.patientunitstayid


),

t2 as --UO
(


with uotemp as
(
select patientunitstayid,
case when dayz=1 then sum(outputtotal) else null end as uod1
from
(


select distinct patientunitstayid, intakeoutputoffset, outputtotal,
(CASE
WHEN  (intakeoutputoffset) between -120 and 1440 THEN 1
else null
end) as dayz
from intakeoutput
where intakeoutputoffset between 0 and 5760
order by patientunitstayid, intakeoutputoffset


) as temp
group by patientunitstayid, temp.dayz
)


select pt.patientunitstayid,
max(case when uod1 is not null then uod1 else null end) as UO
from pat pt
left outer join uotemp
on uotemp.patientunitstayid=pt.patientunitstayid
group by pt.patientunitstayid


)


select pt.patientunitstayid, -- t1.creat, t2.uo,
(case --COMPUTE SOFA RENAL
when uo <200 or creat>5 then 4
when uo <500 or creat >3.5 then 3
when creat between 2 and 3.5 then 2
when creat between 1.2 and 2 then 1
else 0
end) as sofarenal
from cohort1 pt
left outer join t1
on t1.patientunitstayid=pt.patientunitstayid
left outer join t2
on t2.patientunitstayid=pt.patientunitstayid
order by pt.patientunitstayid
-- group by pt.patientunitstayid, t1.creat, t2.uo


),


-- SOFA- GCS, liver, platelets


sofa3others as
(
with t1 as --GCS
(
select patientunitstayid, sum(cast(physicalexamvalue as numeric)) as gcs
from physicalexam pe
where (lower(physicalexampath) like '%gcs/eyes%'
or lower(physicalexampath) like '%gcs/verbal%'
or lower(physicalexampath) like '%gcs/motor%')
and physicalexamoffset between -1440 and 1440
group by patientunitstayid--, physicalexamoffset
), t2 as
(
select pt.patientunitstayid,
max(case when lower(labname) like 'total bili%' then labresult else null end) as bili, --BILI
min(case when lower(labname) like 'platelet%' then labresult else null end) as plt --PLATELETS
from pat pt
left outer join lab
on pt.patientunitstayid=lab.patientunitstayid
where labresultoffset between -1440 and 1440
group by pt.patientunitstayid
)


select distinct pt.patientunitstayid, min(t1.gcs) as gcs, max(t2.bili) as bili, min(t2.plt) as plt,
max(case when plt<20 then 4
when plt<50 then 3
when plt<100 then 2
when plt<150 then 1
else 0 end) as sofacoag,
max(case when bili>12 then 4
when bili>6 then 3
when bili>2 then 2
when bili>1.2 then 1
else 0 end) as sofaliver,
max(case when gcs=15 then 0
when gcs>=13 then 1
when gcs>=10 then 2
when gcs>=6 then 3
when gcs>=3 then 4
else 0 end) as sofacns
from cohort1 pt
left outer join t1
on t1.patientunitstayid=pt.patientunitstayid
left outer join t2
on t2.patientunitstayid=pt.patientunitstayid
group by pt.patientunitstayid, t1.gcs, t2.bili, t2.plt
order by pt.patientunitstayid
)


-- SOFA: COMBINE ALL SUBSCORES 


Select pt.patientunitstayid, --  sofacv.sofa_cv, sofarespi.sofa_respi,sofarenal.sofarenal,sofa3others.sofacoag,sofa3others.sofaliver,sofa3others.sofacns, 
sofacv.sofa_cv+sofarespi.sofa_respi+ sofarenal.sofarenal+sofa3others.sofacoag+ sofa3others.sofaliver+sofa3others.sofacns as sofatotal
From cohort1 pt
Left outer join sofacv
On pt.patientunitstayid=sofacv.Patientunitstayid
Left outer join sofarespi
On pt.patientunitstayid= sofarespi.Patientunitstayid
Left outer join sofarenal
On pt.patientunitstayid= sofarenal.Patientunitstayid
Left outer join sofa3others
On pt.patientunitstayid= sofa3others.Patientunitstayid


'''
sofa = run_query(sql_sofa)
aps = run_query(sql_apsiii)
almost_done_df = eicu.merge(aps).merge(sofa)
almost_done_df<jupyter_output><empty_output><jupyter_text>peep, fio2, plateau<jupyter_code>sql_eicu_peep = f'''

with 
resp as (
SELECT patientunitstayid,
respchartoffset chartoffset
,respchartvalue peep
FROM `physionet-data.eicu_crd.respiratorycharting` 
where lower(respchartvaluelabel) like 'peep'
)
, peep_ft1 as (
  select 
  v.patientunitstayid, safe_cast(peep as float64) peep
  from `{view_id}` v
  left join resp p on v.patientunitstayid=p.patientunitstayid
  where p.chartoffset <= v.vent_start+{HOUR_GAP}*60 and p.chartoffset >= v.vent_start and safe_cast(peep as float64) < 100
)
, peep_ft2 as (
    SELECT 
    v.patientunitstayid, peep
    FROM `{view_id}` v
    left join `physionet-data.eicu_crd_derived.pivoted_bg` bg
    on v.patientunitstayid=bg.patientunitstayid
    where bg.chartoffset <= v.vent_start+{HOUR_GAP}*60 and bg.chartoffset >= v.vent_start
)
, peep_union as (
  select * from peep_ft1 
  
  union all

  select * from peep_ft2
)

select 
v.*
, max(peep) peep_max
, avg(peep) peep_avg
, min(peep) peep_min

from `{view_id}` v
left join peep_union p on p.patientunitstayid=v.patientunitstayid
group by patientunitstayid, vent_start, vent_end, vent_duration, over72h

'''
peep = run_query(sql_eicu_peep)
sql_eicu_fio2=f'''

with tmp as (
SELECT 
patientunitstayid ,
respchartoffset chartoffset, 
respchartvaluelabel
, safe_cast(respchartvalue as float64) v
FROM `physionet-data.eicu_crd.respiratorycharting` where lower(respchartvaluelabel) like 'fio2%' # Fio2, Fio2 (%)  
), 

tmp2 as ( select
patientunitstayid, chartoffset
,case when v>=0.2 and v<=1 then v*100 else null end value1
,case when v>=20 and v<=100 then v else null end value2
from tmp
-- where safe_cast(v as float64) >=0 and safe_cast(v as float64) <2
) 

, fio2_1 as (
select
patientunitstayid , chartoffset, value1 fio2
from tmp2 where value1 is not null)
, fio2_2 as ( 
select
patientunitstayid , chartoffset, value1 fio2
from tmp2 where value2 is not null)

, fio2_tmp_union as (
  select * from fio2_1 
  union all
  select * from fio2_2
)

,fio2_resp as (
select v.patientunitstayid, chartoffset, fio2
from `{view_id}` v
left join fio2_tmp_union f on v.patientunitstayid = f.patientunitstayid 
where f.chartoffset <= v.vent_start+{HOUR_GAP}*60 and f.chartoffset >= v.vent_start) 

,fio2_lab as (
select v.patientunitstayid, chartoffset, fio2 * 100 fio2 
from `{view_id}` v
left join `physionet-data.eicu_crd_derived.pivoted_bg` f on v.patientunitstayid = f.patientunitstayid  
where f.chartoffset <= v.vent_start+{HOUR_GAP}*60 and f.chartoffset >= v.vent_start )

, fio2_union as (
select * from fio2_resp 
union all
select * from fio2_lab
)
select 
v.*
, max(fio2) fio2_max
, avg(fio2) fio2_mean
, min(fio2) fio2_min

from `{view_id}` v
left join fio2_union f on f.patientunitstayid=v.patientunitstayid
group by patientunitstayid, vent_start, vent_end, vent_duration, over72h
'''

fio2 = run_query(sql_eicu_fio2)
sql_eicu_plateau=f'''
with tmp as (
SELECT 
patientunitstayid, 
respchartentryoffset chartoffset,
respchartvaluelabel, safe_cast(respchartvalue as float64) plateau
-- distinct respchartvaluelabel
FROM `physionet-data.eicu_crd.respiratorycharting` where lower(respchartvaluelabel) like '%plateau%'
)
, plt as (
select v.patientunitstayid , plateau
FROM `{view_id}` v
left join tmp t on t.patientunitstayid = v.patientunitstayid 
where t.chartoffset <= v.vent_start+{HOUR_GAP}*60 and t.chartoffset >= v.vent_start
)
select 
v.*
, max(plateau) plateau_pr_max
, avg(plateau) plateau_pr_mean
, min(plateau) plateau_pr_min

from `{view_id}` v
left join plt p on p.patientunitstayid=v.patientunitstayid
group by patientunitstayid, vent_start, vent_end, vent_duration, over72h


'''

plateau = run_query(sql_eicu_plateau)

cols = ['patientunitstayid', 'vent_start', 'vent_end', 'vent_duration', 'over72h']
fts = peep.merge(fio2, on=cols).merge(plateau, on=cols)
len(fts)
pt_char_df = almost_done_df.merge(fts.drop(columns=['vent_start', 'vent_end', 'vent_duration', 'over72h']))
pt_char_df
def handle89(x):
    try:
        return int(x)
    except:
        return 91

pt_char_df.age = pt_char_df.age.apply(lambda x: handle89(x))
pt_char_df.to_csv('pt_char_eicu.csv', index=False)
os.listdir('.')

from google.colab import files
files.download('pt_char_eicu.csv') 
pt_char_df.columns<jupyter_output><empty_output><jupyter_text>Archive<jupyter_code>cols = ['patientunitstayid', 'vent_start', 'vent_end', 'vent_duration', 'over72h']
cohort_ft = pd.merge(df_ft1, df_ft2, on=cols, how='left')
len(cohort_ft)
cohort_ft.head()
cohort_ft.to_csv('ft9_invasive_cohort_eicu.csv', index=False)
os.listdir('.')
from google.colab import files
files.download('ft9_invasive_cohort_eicu.csv') 
cohort_demo.to_csv('ft9_invasive_cohort_eicu_demo_mort.csv', index=False)
files.download('ft9_invasive_cohort_eicu_demo_mort.csv') 

sql_peep = f'''
with 
resp as (
SELECT patientunitstayid,
respchartoffset
,lower(respchartvaluelabel) label
,respchartvalue
FROM `physionet-data.eicu_crd.respiratorycharting` 
)
, peep_tmp as (
select 
patientunitstayid
,respchartoffset chartoffset
,respchartvalue peep
from resp
where label like 'peep'
)
, peep_ft1 as (
  select 
  v.patientunitstayid, safe_cast(peep as float64) peep
  from `{view_id}` v
  left join peep_tmp p on v.patientunitstayid=p.patientunitstayid
  where p.chartoffset <= v.vent_start + {HOUR_GAP}*60 and p.chartoffset >= v.vent_start and safe_cast(peep as float64) < 100
)
, peep_ft2 as (
    SELECT 
    v.patientunitstayid, peep
    FROM `{view_id}` v
    left join `physionet-data.eicu_crd_derived.pivoted_bg` bg
    on v.patientunitstayid=bg.patientunitstayid
    where bg.chartoffset <= v.vent_start + {HOUR_GAP}*60 and bg.chartoffset >= v.vent_start
)
, peep_union as (
  select * from peep_ft1 
  
  union all

  select * from peep_ft2
)

select 
v.*
, max(peep) peep_max

from `{view_id}` v
left join peep_union p on p.patientunitstayid=v.patientunitstayid
group by patientunitstayid, vent_start, vent_end, vent_duration, over72h

'''<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09098352759849726, 'fraction_numerical': 0.023359984587226663, 'mean_word_length': 3.7675086107921927, 'pattern_counts': {'":': 0, '<': 84, '<?xml version=': 0, '>': 100, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 13, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '426171', 'license': 'MIT', 'n_tokens_mistral': 15365, 'n_tokens_neox': 13932, 'n_words': 3969, 'path': 'code/data extraction/pmv_data_extraction_eicu.ipynb', 'repo_name': 'vlada-rozova/ventilation-duration', 'size': '41537'}