eicu-code
eicu-code copied to clipboard
Published
20 hours ago •
MIT-LCP
MIT-LCP
physionet-data.eicu_derived.ventilation_events
The phsyionet-data.eicu_derived dataset on bigquery has a table called ventilation_events. Where does this come from? I'd like make a table similar to ventilator_durations from MIMIC and was hoping to avoid duplication of work.
I can try to upload the code in the next few weeks. Unfortunately defining ventilation across the 200 or so hospitals was fairly challenging and I never ended up with a viable solution. This table was from one of my attempts (though I can't remember which one).
I coded something up based on the MIMIC approach, which I think ended up in a project repo though I don't remember which one. Not sure whether this was used. Will see if I can help dig something out.
I ended up fashioning something like this:
WITH raw_vent_data AS (
SELECT DISTINCT
patientunitstayid
, respchartoffset
FROM
`physionet-data.eicu_crd.respiratorycharting`
WHERE
respchartvaluelabel IN
('PEEP', 'Total RR', 'Vent Rate',
'Tidal Volume (set)', 'TV/kg IBW', 'Mean Airway Pressure',
'Peak Insp. Pressure')
), raw_vent_events AS (
SELECT
patientunitstayid
, respchartoffset
, CASE
WHEN LAG(respchartoffset) OVER (PARTITION BY patientunitstayid ORDER BY respchartoffset) IS NULL THEN 1
WHEN respchartoffset - LAG(respchartoffset) OVER (PARTITION BY patientunitstayid ORDER BY respchartoffset) > 806 THEN 1
ELSE 0 END AS new_vent_event
FROM raw_vent_data
), labeled_vent_events AS (
SELECT
patientunitstayid
, respchartoffset
, SUM(new_vent_event) OVER (PARTITION BY patientunitstayid ORDER BY respchartoffset) AS vent_event_id
FROM raw_vent_events
I'd love feedback and I'm happy to supply a PR if desired.
I ended up fashioning something like this:
WITH raw_vent_data AS ( SELECT DISTINCT patientunitstayid , respchartoffset FROM `physionet-data.eicu_crd.respiratorycharting` WHERE respchartvaluelabel IN ('PEEP', 'Total RR', 'Vent Rate', 'Tidal Volume (set)', 'TV/kg IBW', 'Mean Airway Pressure', 'Peak Insp. Pressure') ), raw_vent_events AS ( SELECT patientunitstayid , respchartoffset , CASE WHEN LAG(respchartoffset) OVER (PARTITION BY patientunitstayid ORDER BY respchartoffset) IS NULL THEN 1 WHEN respchartoffset - LAG(respchartoffset) OVER (PARTITION BY patientunitstayid ORDER BY respchartoffset) > 806 THEN 1 ELSE 0 END AS new_vent_event FROM raw_vent_data ), labeled_vent_events AS ( SELECT patientunitstayid , respchartoffset , SUM(new_vent_event) OVER (PARTITION BY patientunitstayid ORDER BY respchartoffset) AS vent_event_id FROM raw_vent_eventsI'd love feedback and I'm happy to supply a PR if desired.
I generated the ventialtion_event table as per your script. But when I execute the sql script generating oasis table, error displayed as :
LINE 315: WHEN (event = 'mechvent start' OR event = 'mechvent ...
^
ERROR: column "event" does not exist
Anything wrong, please?
The SQL script generating oasis table reads as follow:
SET search_path TO eicu_derived, eicu_crd;
DROP TABLE IF EXISTS pivoted_oasis CASCADE;
CREATE TABLE pivoted_oasis as
-- ------------------------------------------------------------------
-- Title: Oxford Acute Severity of Illness Score (OASIS)
-- This query extracts the Oxford acute severity of illness score.
-- This score is a measure of severity of illness for patients in the ICU.
-- The score is calculated on the first day of each ICU patients' stay.
-- OASIS score was originally created for MIMIC
-- This script creates a pivoted table containing the OASIS score in eICU
-- ------------------------------------------------------------------
-- Authors:
-- Tristan Struja, MD, MSc, MPH (ORCID 0000-0003-0199-0184) and João Matos, MS (ORICD 0000-0002-0312-1647)
-- Reference for OASIS:
-- Johnson, Alistair EW, Andrew A. Kramer, and Gari D. Clifford.
-- "A new severity of illness scale using a subset of acute physiology and chronic health evaluation data elements shows comparable predictive accuracy*."
-- Critical care medicine 41, no. 7 (2013): 1711-1718.
-- https://alistairewj.github.io/project/oasis/
-- Variables used in OASIS (first 24h only):
-- Heart rate, MAP, Temperature, Respiratory rate
-- (sourced FROM `physionet-data.eicu_crd_derived.pivoted_vital`)
-- GCS
-- (sourced FROM `physionet-data.eicu_crd_derived.pivoted_vital` and `physionet-data.eicu_crd_derived.physicalexam`)
-- Urine output
-- (sourced FROM `physionet-data.eicu_crd_derived.pivoted_uo`)
-- Pre-ICU in-hospital length of stay
-- (sourced FROM `physionet-data.eicu_crd.patient`)
-- Age
-- (sourced FROM `physionet-data.eicu_crd.patient`)
-- Elective surgery
-- (sourced FROM `physionet-data.eicu_crd.patient` and `physionet-data.eicu_crd.apachepredvar`)
-- Ventilation status
-- (sourced FROM `physionet-data.eicu_crd_derived.ventilation_events`, `physionet-data.eicu_crd.apacheapsvar`,
-- `physionet-data.eicu_crd.apachepredvar`, and `physionet-data.eicu_crd.respiratorycare`)
-- Regarding missing values:
-- Elective stay: If there is no information on surgery in an elective stay, we assumed all cases to be -> "no elective surgery"
-- There are a lot of missing values, especially for urine output. Hence, we have created 2 OASIS summary scores:
-- 1) No imputation, values as is with missings. 2) Imputation in case of NULL values, with 0's (common approach for severity of illness scores)
-- Note:
-- The score is calculated for *all* ICU patients, with the assumption that the user will subselect appropriate patientunitstayid.
WITH
-- Pre-ICU stay LOS -> directly convert from minutes to hours
pre_icu_los_data AS (
SELECT patientunitstayid AS pid_LOS
,CASE
WHEN hospitaladmitoffset > (-0.17*60) THEN 5
WHEN hospitaladmitoffset BETWEEN (-4.94*60) AND (-0.17*60) THEN 3
WHEN hospitaladmitoffset BETWEEN (-24*60) AND (-4.94*60) THEN 0
WHEN hospitaladmitoffset BETWEEN (-311.80*60) AND (-24.0*60) THEN 2
WHEN hospitaladmitoffset < (-311.80*60) THEN 1
ELSE NULL
END AS pre_icu_los_oasis
FROM eicu_crd.patient
)
-- Age
-- Change age from string to integer
, age_numeric AS (
SELECT patientunitstayid
, CASE
WHEN age = '> 89' THEN 91
-- ELSE SAFE_CAST(age AS INT64)
ELSE CAST(age AS bigint)
END AS age_num
FROM eicu_crd.patient
)
-- Get the information itself in a second step
, age_oasis AS (
SELECT patientunitstayid AS pid_age
, CASE
WHEN MAX(age_num) < 24 THEN 0
WHEN MAX(age_num) BETWEEN 24 AND 53 THEN 3
WHEN MAX(age_num) BETWEEN 54 AND 77 THEN 6
WHEN MAX(age_num) BETWEEN 78 AND 89 THEN 9
WHEN MAX(age_num) > 89 THEN 7
ELSE NULL
END AS age_oasis
FROM age_numeric
GROUP BY pid_age
)
-- GCS, Glasgow Coma Scale
-- Merge information from two tables into one
, merged_gcs AS (
SELECT pat_gcs.patientunitstayid, physicalexam.gcs1, pivoted_gcs.gcs2
FROM eicu_crd.patient AS pat_gcs
LEFT JOIN(
SELECT patientunitstayid, MIN(CAST(physicalexamvalue AS NUMERIC)) AS gcs1
FROM eicu_crd.physicalexam
WHERE (
(physicalExamPath LIKE 'notes/Progress Notes/Physical Exam/Physical Exam/Neurologic/GCS/_' OR
physicalExamPath LIKE 'notes/Progress Notes/Physical Exam/Physical Exam/Neurologic/GCS/__')
AND (physicalexamoffset > 0 AND physicalexamoffset <= 1440) -- consider only first 24h
AND physicalexamvalue IS NOT NULL)
GROUP BY patientunitstayid
)
AS physicalexam
ON physicalexam.patientunitstayid = pat_gcs.patientunitstayid
LEFT JOIN(
SELECT pivoted_gcs.patientunitstayid, pivoted_gcs.gcs as gcs2
FROM eicu_derived.pivoted_gcs AS pivoted_gcs
WHERE (chartoffset > 0 AND chartoffset <= 1440) -- consider only first 24h
)
AS pivoted_gcs
ON pivoted_gcs.patientunitstayid = pat_gcs.patientunitstayid
)
-- Only keep minimal gcs from merged_gcs table
, minimal_gcs AS (
SELECT patientunitstayid, COALESCE(gcs1, gcs2) AS gcs_min
FROM merged_gcs
)
-- Call merged_gcs table in one go
, gcs_oasis AS (
SELECT patientunitstayid AS pid_gcs
, CASE
WHEN gcs_min < 8 THEN 10
WHEN gcs_min BETWEEN 8 AND 13 THEN 4
WHEN gcs_min = 14 THEN 3
WHEN gcs_min = 15 THEN 0
ELSE NULL
END AS gcs_oasis
FROM minimal_gcs
--WHERE (chartoffset > 0 AND chartoffset <= 1440) -- already considered in step above
)
-- Elective admission
-- Mapping
-- Assume emergency admission if patient came from
-- Emergency Department
-- Assume elective admission if patient from other place, e.g. operating room, floor, Direct Admit, Chest Pain Center, Other Hospital, Observation, etc.
, elective_surgery AS (
-- 1: pat table as base for patientunitstayid
SELECT pat.patientunitstayid, electivesurgery1
, CASE
WHEN unitAdmitSource LIKE 'Emergency Department' THEN 0
ELSE 1
END AS adm_elective1
FROM eicu_crd.patient AS pat
-- 2: apachepredvar table
LEFT JOIN (
SELECT apache.patientunitstayid, electivesurgery AS electivesurgery1
-- FROM `physionet-data.eicu_crd.apachepredvar` AS apache
FROM eicu_crd.apachepredvar AS apache
)
AS apache
ON pat.patientunitstayid = apache.patientunitstayid
)
, electivesurgery_oasis AS (
SELECT patientunitstayid AS pid_adm
, CASE
WHEN electivesurgery1 = 0 THEN 6
WHEN electivesurgery1 IS NULL THEN 6
WHEN adm_elective1 = 0 THEN 6
ELSE 0
END AS electivesurgery_oasis
FROM elective_surgery
)
-- Heart rate
, heartrate_oasis AS (
SELECT patientunitstayid AS pid_HR
, CASE
WHEN MIN(heartrate) < 33 THEN 4
WHEN MAX(heartrate) BETWEEN 33 AND 88 THEN 0
WHEN MAX(heartrate) BETWEEN 89 AND 106 THEN 1
WHEN MAX(heartrate) BETWEEN 107 AND 125 THEN 3
WHEN MAX(heartrate) > 125 THEN 6
ELSE NULL
END AS heartrate_oasis
FROM eicu_derived.pivoted_vital
WHERE (chartoffset > 0 AND chartoffset <= 1440) -- consider only first 24h
AND heartrate IS NOT NULL
GROUP BY pid_HR
)
-- Mean arterial pressure
, map_oasis AS (
SELECT patientunitstayid AS pid_MAP
, CASE
WHEN MIN(ibp_mean) < 20.65 THEN 4
WHEN MIN(ibp_mean) BETWEEN 20.65 AND 50.99 THEN 3
WHEN MIN(ibp_mean) BETWEEN 51 AND 61.32 THEN 2
WHEN MIN(ibp_mean) BETWEEN 61.33 AND 143.44 THEN 0
WHEN MAX(ibp_mean) >143.44 THEN 3
WHEN MIN(nibp_mean) < 20.65 THEN 4
WHEN MIN(nibp_mean) BETWEEN 20.65 AND 50.99 THEN 3
WHEN MIN(nibp_mean) BETWEEN 51 AND 61.32 THEN 2
WHEN MIN(nibp_mean) BETWEEN 61.33 AND 143.44 THEN 0
WHEN MAX(nibp_mean) >143.44 THEN 3
ELSE NULL
END AS map_oasis
FROM eicu_derived.pivoted_vital
WHERE (chartoffset > 0 AND chartoffset <= 1440) -- consider only first 24h
GROUP BY pid_MAP
)
-- Respiratory rate
, respiratoryrate_oasis AS (
SELECT patientunitstayid AS pid_RR
, CASE
WHEN MIN(respiratoryrate) < 6 THEN 10
WHEN MIN(respiratoryrate) BETWEEN 6 AND 12 THEN 1
WHEN MIN(respiratoryrate) BETWEEN 13 AND 22 THEN 0
WHEN MAX(respiratoryrate) BETWEEN 23 AND 30 THEN 1
WHEN MAX(respiratoryrate) BETWEEN 31 AND 44 THEN 6
WHEN MAX(respiratoryrate) > 44 THEN 9
ELSE NULL
END AS respiratoryrate_oasis
FROM eicu_derived.pivoted_vital
WHERE (chartoffset > 0 AND chartoffset <= 1440) -- consider only first 24h
AND respiratoryrate IS NOT NULL
GROUP BY pid_RR
)
-- Temperature
, temperature_oasis AS (
SELECT patientunitstayid AS pid_temp
, CASE
WHEN MIN(temperature) < 33.22 THEN 3
WHEN MIN(temperature) BETWEEN 33.22 AND 35.93 THEN 4
WHEN MAX(temperature) BETWEEN 33.22 AND 35.93 THEN 4
WHEN MIN(temperature) BETWEEN 35.94 AND 36.39 THEN 2
WHEN MAX(temperature) BETWEEN 36.40 AND 36.88 THEN 0
WHEN MAX(temperature) BETWEEN 36.89 AND 39.88 THEN 2
WHEN MAX(temperature) >39.88 THEN 6
ELSE NULL
END AS temperature_oasis
FROM eicu_derived.pivoted_vital
WHERE (chartoffset > 0 AND chartoffset <= 1440) -- consider only first 24h
AND temperature IS NOT NULL
GROUP BY pid_temp
)
-- Urine output
, merged_uo AS (
-- pat table as base for patientunitstayid
SELECT pat.patientunitstayid, COALESCE(pivoted_uo.urineoutput, apache_urine.urine) AS uo_comb -- consider pivoted_uo first, if missing -> apacheapsvar
FROM eicu_crd.patient AS pat
-- Join information from pivoted_uo table
LEFT JOIN(
SELECT patientunitstayid AS pid_uo, SUM(urineoutput) AS urineoutput
FROM eicu_derived.pivoted_uo
WHERE (chartoffset > 0 AND chartoffset <= 1440) -- consider only first 24h
AND urineoutput > 0 AND urineoutput IS NOT NULL -- ignore biologically implausible values <0
GROUP BY pid_uo
) AS pivoted_uo
ON pivoted_uo.pid_uo = pat.patientunitstayid
-- Join information from apacheapsvar table
LEFT JOIN(
SELECT patientunitstayid AS pid_auo, urine
FROM eicu_crd.apacheapsvar
WHERE urine > 0 AND urine IS NOT NULL -- ignore biologically implausible values <0
) AS apache_urine
ON apache_urine.pid_auo = pat.patientunitstayid
)
-- Call merged_uo table for score computation
, urineoutput_oasis AS (
SELECT merged_uo.patientunitstayid AS pid_urine, merged_uo.uo_comb
, CASE
WHEN uo_comb <671 THEN 10
WHEN uo_comb BETWEEN 671 AND 1426.99 THEN 5
WHEN uo_comb BETWEEN 1427 AND 2543.99 THEN 1
WHEN uo_comb BETWEEN 2544 AND 6896 THEN 0
WHEN uo_comb >6896 THEN 8
ELSE NULL
END AS urineoutput_oasis
FROM merged_uo
)
-- Ventiliation -> Note: This information is stored in 5 tables
-- Create unified vent_table first
, merged_vent AS (
-- 1: use patient table as base
SELECT pat.patientunitstayid, vent_1, vent_2, vent_3, vent_4
FROM eicu_crd.patient AS pat
-- 2: ventilation_events table
LEFT JOIN(
SELECT patientunitstayid,
-- MAX(CASE WHEN event = "mechvent start" OR event = "mechvent end") THEN 1
-- ELSE NULL
-- END as vent_1
MAX(CASE
WHEN (event = 'mechvent start' OR event = 'mechvent end') THEN 1
ELSE NULL
END) as vent_1
FROM eicu_derived.ventilation_events AS vent_events
GROUP BY patientunitstayid
)
AS vent_events
ON vent_events.patientunitstayid = pat.patientunitstayid
-- 3: apacheapsvar table
LEFT JOIN(
SELECT patientunitstayid, intubated as vent_2
FROM eicu_crd.apacheapsvar AS apacheapsvar
WHERE (intubated = 1)
)
AS apacheapsvar
ON apacheapsvar.patientunitstayid = pat.patientunitstayid
-- 4: apachepredvar table
LEFT JOIN(
SELECT patientunitstayid, oobintubday1 as vent_3
FROM eicu_crd.apachepredvar AS apachepredvar
WHERE (oobintubday1 = 1)
)
AS apachepredvar
ON apachepredvar.patientunitstayid = pat.patientunitstayid
-- 5: respiratory care table
LEFT JOIN(
SELECT patientunitstayid,
CASE
WHEN COUNT(airwaytype) >= 1 THEN 1
WHEN COUNT(airwaysize) >= 1 THEN 1
WHEN COUNT(airwayposition) >= 1 THEN 1
WHEN COUNT(cuffpressure) >= 1 THEN 1
WHEN COUNT(setapneatv) >= 1 THEN 1
ELSE NULL
END AS vent_4
FROM eicu_crd.respiratorycare AS resp_care
WHERE (respCareStatusOffset > 0 AND respCareStatusOffset <= 1440)
GROUP BY patientunitstayid
)
AS resp_care
ON resp_care.patientunitstayid = pat.patientunitstayid
)
-- Call merged vent table in one go
, vent_oasis AS (
SELECT patientunitstayid AS pid_vent
, CASE
WHEN vent_1 = 1 THEN 9
WHEN vent_2 = 1 THEN 9
WHEN vent_3 = 1 THEN 9
WHEN vent_4 = 1 THEN 9
ELSE 0
END AS vent_oasis
FROM merged_vent
--WHERE (chartoffset > 0 AND chartoffset <= 1440) -- already considered in step above
)
, cohort_oasis AS (
SELECT cohort.patientunitstayid,
pre_icu_los_data.pre_icu_los_oasis,
age_oasis.age_oasis,
gcs_oasis.gcs_oasis,
heartrate_oasis.heartrate_oasis,
map_oasis.map_oasis,
respiratoryrate_oasis.respiratoryrate_oasis,
temperature_oasis.temperature_oasis,
urineoutput_oasis.urineoutput_oasis,
vent_oasis.vent_oasis,
electivesurgery_oasis.electivesurgery_oasis
FROM eicu_crd.patient AS cohort
LEFT JOIN pre_icu_los_data
ON cohort.patientunitstayid = pre_icu_los_data.pid_LOS
LEFT JOIN age_oasis
ON cohort.patientunitstayid = age_oasis.pid_age
LEFT JOIN gcs_oasis
ON cohort.patientunitstayid = gcs_oasis.pid_gcs
LEFT JOIN heartrate_oasis
ON cohort.patientunitstayid = heartrate_oasis.pid_HR
LEFT JOIN map_oasis
ON cohort.patientunitstayid = map_oasis.pid_MAP
LEFT JOIN respiratoryrate_oasis
ON cohort.patientunitstayid = respiratoryrate_oasis.pid_RR
LEFT JOIN temperature_oasis
ON cohort.patientunitstayid = temperature_oasis.pid_temp
LEFT JOIN urineoutput_oasis
ON cohort.patientunitstayid = urineoutput_oasis.pid_urine
LEFT JOIN vent_oasis
ON cohort.patientunitstayid = vent_oasis.pid_vent
LEFT JOIN electivesurgery_oasis
ON cohort.patientunitstayid = electivesurgery_oasis.pid_adm
)
, score_impute AS (
SELECT cohort_oasis.*,
IFNULL(pre_icu_los_oasis, 0) AS pre_icu_los_oasis_imp,
IFNULL(age_oasis, 0) AS age_oasis_imp,
IFNULL(gcs_oasis, 0) AS gcs_oasis_imp,
IFNULL(heartrate_oasis, 0) AS heartrate_oasis_imp,
IFNULL(map_oasis, 0) AS map_oasis_imp,
IFNULL(respiratoryrate_oasis, 0) AS respiratoryrate_oasis_imp,
IFNULL(temperature_oasis, 0) AS temperature_oasis_imp,
IFNULL(urineoutput_oasis, 0) AS urineoutput_oasis_imp,
IFNULL(vent_oasis, 0) AS vent_oasis_imp,
IFNULL(electivesurgery_oasis, 0) AS electivesurgery_oasis_imp
FROM cohort_oasis
)
--Compute overall score
-- oasis_null -> only cases where all components have a Non-NULL value
-- oasis_imp -> Imputation in case of NULL values, with 0's (common approach for severity of illness scores)
, score AS (
SELECT patientunitstayid,
MAX(pre_icu_los_oasis) AS pre_icu_los_oasis,
MAX(age_oasis) AS age_oasis,
MAX(gcs_oasis) AS gcs_oasis,
MAX(heartrate_oasis) AS heartrate_oasis,
MAX(map_oasis) AS map_oasis,
MAX(respiratoryrate_oasis) AS respiratoryrate_oasis,
MAX(temperature_oasis) AS temperature_oasis,
MAX(urineoutput_oasis) AS urineoutput_oasis,
MAX(vent_oasis) AS vent_oasis,
MAX(electivesurgery_oasis) AS electivesurgery_oasis,
MAX(pre_icu_los_oasis +
age_oasis +
gcs_oasis +
heartrate_oasis +
map_oasis +
respiratoryrate_oasis +
temperature_oasis +
urineoutput_oasis +
vent_oasis +
electivesurgery_oasis) AS oasis_null,
MAX(pre_icu_los_oasis_imp) AS pre_icu_los_oasis_imp,
MAX(age_oasis_imp) AS age_oasis_imp,
MAX(gcs_oasis_imp) AS gcs_oasis_imp,
MAX(heartrate_oasis_imp) AS heartrate_oasis_imp,
MAX(map_oasis_imp) AS map_oasis_imp,
MAX(respiratoryrate_oasis_imp) AS respiratoryrate_oasis_imp,
MAX(temperature_oasis_imp) AS temperature_oasis_imp,
MAX(urineoutput_oasis_imp) AS urineoutput_oasis_imp,
MAX(vent_oasis_imp) AS vent_oasis_imp,
MAX(electivesurgery_oasis_imp) AS electivesurgery_oasis_imp,
MAX(pre_icu_los_oasis_imp +
age_oasis_imp +
gcs_oasis_imp +
heartrate_oasis_imp +
map_oasis_imp +
respiratoryrate_oasis_imp +
temperature_oasis_imp +
urineoutput_oasis_imp +
vent_oasis_imp +
electivesurgery_oasis_imp) AS oasis_imp
FROM score_impute
GROUP BY patientunitstayid
)
-- Final statement to generate view
-- Note: single components contain NULL values, but not final OASIS score (NULL's replaced by 0, see above)
-- Code for above columns is retrained as convienience for user wanting to modify the view for other puroposes
SELECT patientunitstayid,
pre_icu_los_oasis,
age_oasis,
gcs_oasis,
heartrate_oasis,
map_oasis,
respiratoryrate_oasis,
temperature_oasis,
urineoutput_oasis,
vent_oasis,
electivesurgery_oasis,
oasis_imp AS oasis
-- Calculate the probability of in-hospital mortality
, 1 / (1 + exp(- (-6.1746 + 0.1275*(oasis_imp) ))) AS oasis_prob
FROM score
;
