<jupyter_start><jupyter_text>Cohort Extraction NotebookNote: Notebook and SQL files have been heavily adapted from <NAME>, MD, MPH work on sequential severity prediction for critically ill patients (Source: https://github.com/cosgriffc/seq-severityscore)Notebook for extracting data from eICU-CRD Database for GI Bleed patients. Environment<jupyter_code>import pandas as pd
import numpy as np
import psycopg2
from sklearn.model_selection import train_test_split

dbname = 'eicu'
schema_name = 'eicu_crd'
query_schema = 'SET search_path TO ' + schema_name + ';'
con = psycopg2.connect(dbname=dbname)<jupyter_output><empty_output><jupyter_text>Materialized ViewsCreate materialized views for faster extraction of data from the eICU-CRD database in later queries<jupyter_code>def execute_query_safely(sql, con):
    cur = con.cursor()
    try:
        cur.execute(sql)
    except:
        cur.execute('rollback;')
        raise
    finally:
        cur.close()
    return

def generate_materialized_view(query_file, con, query_schema):
    with open(query_file) as fp:
        query = ''.join(fp.readlines())
    print('Generating materialized view using {} ...'.format(query_file), end=' ')
    execute_query_safely(query_schema + query, con)
    print('done.')<jupyter_output><empty_output><jupyter_text>Generate the materialized views:<jupyter_code>generate_materialized_view('./sql/vitalsfirstday.sql', con, query_schema)
generate_materialized_view('./sql/labsfirstday.sql', con, query_schema)
generate_materialized_view('./sql/treatmentfirstday.sql', con, query_schema)<jupyter_output>Generating materialized view using ./sql/vitalsfirstday.sql ... done.
Generating materialized view using ./sql/labsfirstday.sql ... done.
Generating materialized view using ./sql/treatmentfirstday.sql ... done.<jupyter_text>Load Features Extract patients with `GIBLEED` as their current diagnosis. Extract other relevant features from `patient`, `apachepredvar` and `apachepatientresult`, extract other comorbidities also from current `diagnosis`.<jupyter_code>base_cohort_query = query_schema + '''

-- Check diagnosis table for comorbidities, note: they might not be major or primary diagnosis
WITH comorbid AS (
    SELECT 
        d.patientunitstayid
        , MAX(CASE
            WHEN LOWER(d.diagnosisstring) LIKE '%copd%' THEN 1
            ELSE 0
            END) AS copd
        , MAX(CASE
            WHEN LOWER(d.diagnosisstring) LIKE '%coronary artery disease%' THEN 1
            ELSE 0
            END) AS cad
        , MAX(CASE
            WHEN LOWER(d.diagnosisstring) LIKE '%atrial fibrillation%' THEN 1
            ELSE 0
            END) AS afib
        , MAX(CASE
            WHEN LOWER(d.diagnosisstring) LIKE '%chronic kidney disease%' THEN 1
            ELSE 0
            END) AS ckd
    FROM diagnosis AS d
    GROUP BY d.patientunitstayid
)

SELECT 
       -- Patient characteristics
        p.patientunitstayid, p.age, p.gender, p.ethnicity, p.admissionheight AS height
       , p.admissionweight AS weight , p.unittype AS unit_type, p.unitadmitsource
       , p.unitdischargeoffset AS unit_los, p.hospitaldischargeoffset AS hospital_los
       -- GCS
       , a.day1meds AS gcs_meds, a.day1verbal AS gcs_verbal, a.day1motor AS gcs_motor, a.day1eyes AS gcs_eyes
       -- Admision diagnosis
       , a.admitdiagnosis AS admit_diagnosis
       -- Comorbid burden
       , a.hepaticFailure AS hepatic_failure, a.metastaticCancer AS metastatic_cancer
       , a.immunosuppression AS immunosuppression, a.cirrhosis AS cirrhosis, a.diabetes AS diabetes
       , c.copd AS copd, c.cad AS cad, c.afib AS afib, c.ckd AS ckd
       -- Readmission, MI in last 6 months
       , a.readmit AS apache_readmit, a.midur AS apache_midur
       -- Ventilation, Intubation
       , a.oOBVentDay1 AS apache_ventday1, a.oOBIntubDay1 AS apache_intubday1
       -- O2 stats
       , a.day1fio2 AS apache_fio2, a.day1pao2 AS apache_pao2, (a.day1pao2 / a.day1fio2) AS apache_o2ratio
       -- Apache predicted mortality, Apache score, Mortality
       , CAST(o.predictedhospitalmortality AS float) AS apache_prediction, o.apachescore AS apache_score, o.actualhospitalmortality AS hospital_expiration
FROM patient p
INNER JOIN apachepredvar a
ON p.patientunitstayid = a.patientunitstayid
INNER JOIN apachepatientresult o
ON p.patientunitstayid = o.patientunitstayid
LEFT JOIN comorbid c
ON p.patientunitstayid = c.patientunitstayid
WHERE a.admitdiagnosis LIKE ('%GIBLEED%')
    AND o.apacheversion LIKE 'IVa'
    AND o.apachescore > 0
ORDER BY patientunitstayid;
'''
base_cohort = pd.read_sql_query(base_cohort_query, con)
base_cohort.shape
# Types of GI Bleed
base_cohort['admit_diagnosis'].unique()<jupyter_output><empty_output><jupyter_text>Of the 200,859 patients in the database, 6,397 have admission diagnosis of GI Bleed. We will extract lab and vital features for these patients.<jupyter_code>feature_set_query = query_schema + '''

SELECT v.patientunitstayid, v.HR_Mean, v.SBP_periodic_Mean, v.DBP_periodic_Mean
    , v.MAP_periodic_Mean, v.SBP_aperiodic_Mean, v.DBP_aperiodic_Mean
    , v.MAP_aperiodic_Mean, v.RR_Mean, v.SpO2_Mean, v.TempC_Mean
    , ALBUMIN_min, ALBUMIN_max 
    , AMYLASE_min, AMYLASE_max
    , BICARBONATE_min, BICARBONATE_max
    , BUN_min, BUN_max
    , CPK_min, CPK_max
    , BILIRUBIN_min, BILIRUBIN_max
    , IONCALCIUM_min, IONCALCIUM_max
    , CREATININE_min, CREATININE_max
    , GLUCOSE_min, GLUCOSE_max
    , HEMATOCRIT_min, HEMATOCRIT_max
    , FIBRINOGEN_min, FIBRINOGEN_max
    , LIPASE_min, LIPASE_max
    , HEMOGLOBIN_min, HEMOGLOBIN_max
    , LACTATE_min, LACTATE_max
    , LYMPHS_min, LYMPHS_max
    , PLATELET_min, PLATELET_max
    , PMN_min, PMN_max
    , POTASSIUM_min, POTASSIUM_max
    , PTT_min, PTT_max
    , INR_min, INR_max
    , PT_min, PT_max
    , SODIUM_min, SODIUM_max
    , TROPI_min, TROPI_max
    , WBC_min, WBC_max
    , ALT_min, ALT_max
    , AST_min, AST_max
    , ALKPHOS_min, ALKPHOS_max
    , t.abx, t.vasopressor, t.antiarr, t.sedative, t.diuretic, t.blood_product_prbc,t.blood_product_other, t.antiinf
FROM vitalsfirstday v
LEFT JOIN labsfirstday l
ON v.patientunitstayid = l.patientunitstayid
LEFT OUTER JOIN treatmentfirstday t
ON v.patientunitstayid = t.patientunitstayid;
'''

feature_set = pd.read_sql_query(feature_set_query, con)
feature_set.shape<jupyter_output><empty_output><jupyter_text>Merge the two dataframes using _inner join_ to ensure that patients in cohort have vital sign recordings in `vitalsperiodic`.<jupyter_code>cohort = pd.merge(left=base_cohort, right=feature_set, how='inner', on='patientunitstayid')
cohort.shape<jupyter_output><empty_output><jupyter_text>Here we go from 6,397 to 6,351. The missing 46 did not have recorded vitals.  Inclusion and Exclusion Criteria __1. Age $\geq$ 18, and Not Missing__<jupyter_code># <NAME>, the median age for >89 pt in eICU is 93
cohort.loc[cohort.age == '> 89', 'age'] = 93.0
cohort = cohort.loc[cohort.age != '', :]
cohort.age = cohort.age.astype('float64')
cohort = cohort.loc[cohort.age >= 18., :]
cohort.shape<jupyter_output><empty_output><jupyter_text>__2. LoS $\geq$ 4hrs__<jupyter_code>cohort = cohort.loc[cohort.unit_los >= 240, :]
cohort.shape<jupyter_output><empty_output><jupyter_text>__3. Remove Surgery Related Bleed __<jupyter_code>cohort = cohort[~cohort.admit_diagnosis.isin(['S-UGIBLEED', 'S-LGIBLEED'])]
cohort.shape<jupyter_output><empty_output><jupyter_text>__4. Remove Outliers __ __a. Weight $\geq$ 50 and Weight $\leq$ 300 __<jupyter_code>cohort = cohort[(cohort.weight >= 50) & (cohort.weight <= 300)]
cohort.shape<jupyter_output><empty_output><jupyter_text>__b. Height $\geq$ 50 and Height $\leq$ 250__<jupyter_code>cohort = cohort[(cohort.height <= 250) & (cohort.height >= 50)]
cohort.shape
# Save cohort before cleaning and feature engineering
cohort.to_csv('./extraction/data/extracted_cohort_all.csv', index=False)<jupyter_output><empty_output><jupyter_text>Cleaning, Formatting and Feature EngineeringDrop variables from the pull since both min/max values are not needed, keep only the most _abnormal_ laboratory value in the first 24 hours of ICU stay. * The minimum value for: bicarbonate, platelets, hemoglobin, fibrinogen, and hematocrit* The maximum value for: creatinine, AST, ALT, alkalin phos., BUN, bilirubin, PT/INR, lactate, troponin I, amylase, lipase, creatinine phosphokinase, albumin and ioncalcium.* For sodium, the transformation is similar to Cosgriff et al. i.e. 'which aberrantly deviates bidirectionally, the most abnormal value was defined as the value with greatest deviation from the normal range boundaries'.    * This transformation can be applied to glucose and potassium as well.* For white blood cell and neutrophil counts, the most abnormal value was chosen.<jupyter_code># for the unidirectional abberations, just drop what isn't needed
lab_drop = ['bicarbonate_max', 'platelet_max', 
            'hemoglobin_max', 'fibrinogen_max', 'hematocrit_max', 'albumin_max', 'ioncalcium_max',
            'creatinine_min', 'alt_min','ast_min', 'alkphos_min',
            'bun_min', 'bilirubin_min', 'pt_min','ptt_min', 'inr_min', 'lactate_min', 
            'tropi_min', 'amylase_min', 'lipase_min', 'cpk_min']
cohort = cohort.drop(lab_drop, axis=1)

# sodium, deviates bidirectionally
sodium_check = abs(cohort.sodium_min - 135.) >= abs(cohort.sodium_max - 145.)
sodium = np.empty(len(cohort.index), dtype='float64')
sodium[sodium_check] = cohort.sodium_min[sodium_check]
sodium[~sodium_check] = cohort.sodium_max[~sodium_check]
cohort = cohort.assign(sodium=sodium)
cohort = cohort.drop(['sodium_min', 'sodium_max'], axis=1)

# potassium, deviates bidirectionally, same treatment then
potassium_check = abs(cohort.potassium_min - 3.5) >= abs(cohort.potassium_max - 5.0)
potassium = np.empty(len(cohort.index), dtype='float64')
potassium[potassium_check] = cohort.potassium_min[potassium_check]
potassium[~potassium_check] = cohort.potassium_max[~potassium_check]
cohort = cohort.assign(potassium=potassium)
cohort = cohort.drop(['potassium_min', 'potassium_max'], axis=1)

# wbc counts
wbc_check = cohort.wbc_min < 2
pmn_check = cohort.pmn_min < 45
lym_check = cohort.lymphs_min < 20

wbc = np.empty(len(cohort.index), dtype='float64')
wbc[wbc_check] = cohort.wbc_min[wbc_check]
wbc[~wbc_check] = cohort.wbc_max[~wbc_check]
cohort = cohort.assign(wbc=wbc)
cohort = cohort.drop(['wbc_min', 'wbc_max'], axis=1)

pmn = np.empty(len(cohort.index), dtype='float64')
pmn[pmn_check] = cohort.pmn_min[pmn_check]
pmn[~pmn_check] = cohort.pmn_max[~pmn_check]
cohort = cohort.assign(pmn=pmn)
cohort = cohort.drop(['pmn_min', 'pmn_max'], axis=1)

lym = np.empty(len(cohort.index), dtype='float64')
lym[lym_check] = cohort.lymphs_min[lym_check]
lym[~lym_check] = cohort.lymphs_max[~lym_check]
cohort = cohort.assign(lym=lym)
cohort = cohort.drop(['lymphs_min', 'lymphs_max'], axis=1)<jupyter_output><empty_output><jupyter_text>Drop variables which are redundant or unreliable, and in some cases one source is better than another:* Use aperiodic BP instead of periodic* Drop temperature; it is an unreliable signal when automatically captured* Drop PaO2/FiO2 from APACHE and instead just use the PaO2 values derived directly from laboratory data* Drop unit admit source and hospital LoS.* Relabel missing values with `np.nan` and not -1 as is present in some of the eICU tables (i.e. apache table)<jupyter_code>cohort = cohort.replace(-1, np.nan)
cohort = cohort.drop(['sbp_periodic_mean', 'dbp_periodic_mean', 'map_periodic_mean',
                      'tempc_mean', 'apache_pao2', 'apache_fio2', 'apache_o2ratio', 'hospital_los', 
                      'unitadmitsource'], axis=1)
# Before feature engineering save cohort
cohort.to_csv('./extraction/data/cohort.csv', index=False)<jupyter_output><empty_output><jupyter_text>Drop these variables since they either indecate severity or help peak in the future* Drop APACHE score* Drop unit LoS variables since they would let our models peek into the future<jupyter_code># Drop Apache score and unit LoS
cohort = cohort.drop(['apache_score','unit_los'], axis=1)<jupyter_output><empty_output><jupyter_text>Additional variables to drop, these variables are either:_1_ Found to be not statistically significant with hospital_expiration _2_ Weakly correlated with hospital_expiration _3_ Strongly correlated with another included feature_4_ Significant missing data **Category 1*** Drop Height, Weight, Gender, Ethnicity* Drop Unit Type* Drop Diabetes, COPD, CAD, CKD, AFIB, Cirrhosis, Hepatic failure* Drop Sedative, Diuretic, Antiinf* Drop Apache MI* Drop Sodium, Glucose (min)**Category 2*** Drop GCS Meds**Category 3*** Drop SBP, DBP (w/MAP)* Apache Ventilation (w/ Apache Intubation)* Drop ALT (w/ AST)* Drop Hematocrit (w/ Hemoglobin)* Drop Lym (w/PMN)**Category 4*** Drop Fibrinogen (91%)* Drop TropI (78%)* Drop Ion Calcium (83%)* Drop CPK (84%)* Drop Amylase (94%) * Drop Lipase (73%)<jupyter_code># Drop features
cohort = cohort.drop(['height', 'weight','gender','ethnicity',
                      'unit_type',
                      'diabetes', 'copd', 'cad', 'ckd', 'afib','cirrhosis', 'hepatic_failure',
                      'sedative', 'diuretic', 'antiinf',
                      'apache_midur', 
                      'sodium','glucose_min'
                     ], axis=1)

cohort = cohort.drop(['gcs_meds',
                      ], axis=1)

cohort = cohort.drop(['sbp_aperiodic_mean','dbp_aperiodic_mean',
                      'apache_ventday1',
                      'alt_max',
                      'hematocrit_min',
                      'lym',
                     ], axis=1)

cohort = cohort.drop(['tropi_max',
                      'fibrinogen_min',
                      'ioncalcium_min',
                      'cpk_max',
                      'amylase_max', 'lipase_max',
                     ], axis=1)<jupyter_output><empty_output><jupyter_text>Create the following features for modeling* Convert admission diagnosis to one-hot-encoded * Combine GCS sub-parts to one GCS score<jupyter_code>adx_dummies = pd.get_dummies(cohort.admit_diagnosis, 'adx', drop_first=True)
cohort = pd.concat([cohort, adx_dummies], axis=1)
cohort = cohort.drop('admit_diagnosis', axis=1)
# GCS
gcs_net = cohort['gcs_eyes'] + cohort['gcs_motor'] + cohort['gcs_verbal'] 
cohort['gcs'] = gcs_net
cohort = cohort.drop(['gcs_eyes','gcs_motor','gcs_verbal'], axis=1)<jupyter_output><empty_output><jupyter_text>Save Train/Test Split of Features and LabelSave the outcome variable (`hospital expiration`) and APACHE prediction<jupyter_code>label = (cohort.hospital_expiration == 'EXPIRED').astype('int')
apache_pred = cohort.apache_prediction
cohort = cohort.drop(['hospital_expiration', 'apache_prediction'], axis=1)<jupyter_output><empty_output><jupyter_text>Train and test split.<jupyter_code>train_X, test_X, train_y, test_y, train_apache, test_apache = train_test_split(cohort, label, apache_pred, test_size=0.20, stratify = label,random_state=42)<jupyter_output><empty_output><jupyter_text>Save to CSV file for model training purpose<jupyter_code>train_X.to_csv('./extraction/data/train_X.csv', index=False)
train_y.to_csv('./extraction/data/train_y.csv', index=False, header=True)
train_apache.to_csv('./extraction/data/train_apache.csv', index=False, header=True)

test_X.to_csv('./extraction/data/test_X.csv', index=False)
test_y.to_csv('./extraction/data/test_y.csv', index=False, header=True)
test_apache.to_csv('./extraction/data/test_apache.csv', index=False, header=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10702556507180115, 'fraction_numerical': 0.01114044690578917, 'mean_word_length': 4.489572287027218, 'pattern_counts': {'":': 0, '<': 83, '<?xml version=': 0, '>': 87, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '223726', 'license': 'MIT', 'n_tokens_mistral': 5823, 'n_tokens_neox': 5192, 'n_words': 1263, 'path': 'cohort_extraction.ipynb', 'repo_name': 'shamelmerchant/gi-bleed', 'size': '15551'}