<jupyter_start><jupyter_text>The variables are `age`, `hours_in_hosp_before_intubation`, `lactate_min`, `resp_rate_avg`,  `plateau_pressure_avg`, `parapleagia`<jupyter_code># age SELECT  FROM `physionet-data.eicu_crd_derived.basic_demographics` LIMIT 1000
# lactate SELECT lactate FROM `physionet-data.eicu_crd_derived.pivoted_lab` LIMIT 1000
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
''')
dataset_id = 'default' # change accordingly
view_id = f"{project_id}.{dataset_id}.vent_cohort_eicu_view" # this need to be created in advance
view_id
HOUR_GAP = 24
cols = ['patientunitstayid', 'vent_start', 'vent_end', 'vent_duration','over72h']
# lactate, resp

sql_features = f'''
with lab_ft as (
    SELECT 
    v.patientunitstayid, 
    lab.lactate
    FROM `{view_id}` v
    left join `physionet-data.eicu_crd_derived.pivoted_lab` lab
    on v.patientunitstayid=lab.patientunitstayid
    where lab.chartoffset <= v.vent_start + {HOUR_GAP}*60 and lab.chartoffset >= v.vent_start
)

, vs_ft as (
    SELECT 
    v.patientunitstayid, 
    # vt.heartrate, vt.temperature, vt.spo2,
    vt.respiratoryrate, 
    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.lactate) lactate_max
,min(b.lactate) lactate_min
,avg(b.lactate) lactate_avg

,max(s.respiratoryrate) resp_rate_max
,min(s.respiratoryrate) resp_rate_min
,avg(s.respiratoryrate) resp_rate_avg

# ,min(s.heartrate) hr_min
# ,max(s.heartrate) hr_max
# ,min(s.temperature) temp_min 
# ,max(s.temperature) temp_max

from `{view_id}` v
left join lab_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

'''
ft = run_query(sql_features)
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_pressure_max
    , avg(plateau) plateau_pressure_avg
    , min(plateau) plateau_pressure_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)

sql_age = """
SELECT patientunitstayid, age FROM `physionet-data.eicu_crd_derived.basic_demographics`
"""
age = run_query(sql_age)
ft.head(2)
plateau.head(2)
cols = ['patientunitstayid', 'vent_start', 'vent_end', 'vent_duration','over72h']

df = ft.merge(plateau, on=cols, how='inner')
print(len(df))
df.head(2)
df = df.merge(age, how='left')
print(len(df))
df.head(2)
df.to_csv('eicu_some_ft.csv', index=False)
#### ignore everything below<jupyter_output><empty_output><jupyter_text>ignore everything below apsiii, peep_min, resp_rate_min(Neuroblocker, vasopressin), ph_max, heightco2_total_min, co2_total_avg, temp_max, fio2_minplateau_pressure_max, peep_mingcs_score, acidbase_score resp min, ph max,<jupyter_code>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

'''
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
, avg(TotalCO2) co2_total_avg
, 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
'''
df_ft1 = run_query(sql_features)
df_ft_new = run_query(sql_ft_new)
df_aps = run_query(sql_apsiii)
df1 = df_ft1.merge(df_ft_new, 
                on=['patientunitstayid'], how='left').merge(df_aps, 
                                                      on=['patientunitstayid'], how='left')
df1<jupyter_output><empty_output><jupyter_text>peep_min, fio2_min, plateau_pressure_max<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)

df2 = peep.merge(fio2, on=cols).merge(plateau, on=cols)
len(df2)
df1.columns
df2.columns
ft1=['apsiii', 'resp_min', 'ph_max', 'temp_max', 'co2_total_avg', 'co2_total_min']
ft2=['fio2_min', 'plateau_pr_max', 'peep_min']
ndf1 = df1[cols+ft1].rename(columns={'resp_min':'resp_rate_min'})
ndf2 = df2[cols+ft2].rename(columns={'plateau_pr_max':'plateau_pressure_max'})
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)
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
'''
vaso = run_query(sql_vaso)
mdf = ndf1.merge(ndf2, on=cols)
mdf1 = mdf.merge(demo[['patientunitstayid', 'height']], how='left', on=['patientunitstayid'])
mdf2 = mdf1.merge(vaso, how='left', on=['patientunitstayid'])
mdf2.vasopressor.fillna(0, inplace=True)
l = mdf2.columns[5:]
print(', '.join(l))
len(l)
mdf2.to_csv('eicu_features.csv', index=False)
os.listdir('.')

from google.colab import files
files.download('eicu_features.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10966163880962088, 'fraction_numerical': 0.015654300856094577, 'mean_word_length': 5.316168898043254, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 28, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '178869', 'license': 'MIT', 'n_tokens_mistral': 5077, 'n_tokens_neox': 4778, 'n_words': 989, 'path': 'code/data extraction/additional_fts_eicu.ipynb', 'repo_name': 'vlada-rozova/ventilation-duration', 'size': '12265'}