MIT-LCP · alistairewj · Dec 15, 2021 · Dec 8, 2021 · Dec 8, 2021 · Dec 8, 2021
diff --git a/mimic-iv/concepts/make_concepts.sh b/mimic-iv/concepts/make_concepts.sh
@@ -16,23 +16,32 @@ do
             # table name is file name minus extension
             tbl=`echo $fn | rev | cut -d. -f2- | rev`
 
-            # skip first_day_sofa as it depends on other firstday queries
-            if [[ "${tbl}" == "first_day_sofa" ]]; then
-                continue
-            # kdigo_stages needs to be run after creat/uo
-            elif [[ "${tbl}" == "kdigo_stages" ]]; then
-                continue
+            # skip certain tables where order matters - generated at the end of the script
+            skip=0
+            for skip_table in first_day_sofa kdigo_stages vasoactive_agent norepinephrine_eqivalent_dose
+            do
+              if [[ "${tbl}" == "${skip_table}" ]]; then
+                skip=1
+                break
+              fi
+            done;
+            if [[ "${skip}" == "1" ]]; then
+              continue
             fi
+
+            # not skipping - so generate the table on bigquery
             echo "Generating ${TARGET_DATASET}.${tbl}"
             bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.${tbl} < ${d}/${fn}
         fi
     done
 done
 
-# generate first_day_sofa table last
-echo "Generating ${TARGET_DATASET}.first_day_sofa"
-bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.first_day_sofa < firstday/first_day_sofa.sql
+echo "Now generating tables which were skipped due to depending on other tables."
+# generate tables after the above, and in a specific order to ensure dependencies are met
+for table_path in firstday/first_day_sofa organfailure/kdigo_stages medication/vasoactive_agent medication/norepinephrine_equivalent_dose;
+do
+  table=`echo $table_path | rev | cut -d/ -f1 | rev`
 
-# generate first_day_sofa table last
-echo "Generating ${TARGET_DATASET}.kdigo_stages"
-bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.kdigo_stages < organfailure/kdigo_stages.sql
+  echo "Generating ${TARGET_DATASET}.${table}"
+  bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.${table} < ${table_path}.sql
+done
diff --git a/mimic-iv/concepts/medication/dobutamine.sql b/mimic-iv/concepts/medication/dobutamine.sql
@@ -1,6 +1,7 @@
 -- This query extracts dose+durations of dopamine administration
 select
 stay_id, linkorderid
+-- all rows in mcg/kg/min
 , rate as vaso_rate
 , amount as vaso_amount
 , starttime

diff --git a/mimic-iv/concepts/medication/dopamine.sql b/mimic-iv/concepts/medication/dopamine.sql
@@ -1,6 +1,7 @@
 -- This query extracts dose+durations of dopamine administration
 select
 stay_id, linkorderid
+-- all rows in mcg/kg/min
 , rate as vaso_rate
 , amount as vaso_amount
 , starttime

diff --git a/mimic-iv/concepts/medication/epinephrine.sql b/mimic-iv/concepts/medication/epinephrine.sql
@@ -1,6 +1,7 @@
 -- This query extracts dose+durations of epinephrine administration
 select
 stay_id, linkorderid
+-- all rows in mcg/kg/min
 , rate as vaso_rate
 , amount as vaso_amount
 , starttime

diff --git a/mimic-iv/concepts/medication/milrinone.sql b/mimic-iv/concepts/medication/milrinone.sql
@@ -0,0 +1,10 @@
+-- This query extracts dose+durations of milrinone administration
+select
+stay_id, linkorderid
+-- all rows in mcg/kg/min
+, rate as vaso_rate
+, amount as vaso_amount
+, starttime
+, endtime
+from `physionet-data.mimic_icu.inputevents`
+where itemid = 221986 -- milrinone
diff --git a/mimic-iv/concepts/medication/norepinephrine.sql b/mimic-iv/concepts/medication/norepinephrine.sql
@@ -1,6 +1,12 @@
 -- This query extracts dose+durations of norepinephrine administration
 select
   stay_id, linkorderid
+  -- two rows in mg/kg/min... rest in mcg/kg/min
+  -- the rows in mg/kg/min are documented incorrectly
+  , CASE WHEN rateuom = 'mg/kg/min' AND patientweight = 1 THEN rate
+  -- below row is written for completion, but doesn't impact rows
+  WHEN rateuom = 'mg/kg/min' THEN rate * 1000.0
+  ELSE rate END AS vaso_rate
   , rate as vaso_rate
   , amount as vaso_amount
   , starttime

diff --git a/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql b/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql
@@ -0,0 +1,23 @@
+-- This query calculates norepinephrine equivalent dose for vasopressors.
+-- Based on "Vasopressor dose equivalence: A scoping review and suggested formula"
+-- by Goradia et al. 2020.
+SELECT stay_id, starttime, endtime
+-- calculate the dose
+, ROUND(COALESCE(norepinephrine, 0)
+  + COALESCE(epinephrine, 0)
+  + COALESCE(phenylephrine/10, 0)
+  + COALESCE(dopamine/100, 0)
+  -- + metaraminol/8 -- metaraminol not used in BIDMC
+  + COALESCE(vasopressin*2.5, 0)
+  -- angotensin_ii*10 -- angitensin ii rarely used, currently not incorporated
+  -- (it could be included due to norepinephrine sparing effects)
+  , 4) AS norepinephrine_equivalent_dose
+  -- angotensin_ii*10 -- angitensin ii rarely used, currently not incorporated
+  -- (it could be included due to norepinephrine sparing effects)
+  AS norepinephrine_equivalent_dose
+FROM mimic_derived.vasoactive_agent
+WHERE norepinephrine IS NOT NULL
+OR epinephrine IS NOT NULL
+OR phenylephrine IS NOT NULL
+OR dopamine IS NOT NULL
+OR vasopressin IS NOT NULL;
diff --git a/mimic-iv/concepts/medication/phenylephrine.sql b/mimic-iv/concepts/medication/phenylephrine.sql
@@ -1,7 +1,9 @@
 -- This query extracts dose+durations of phenylephrine administration
 select
   stay_id, linkorderid
-  , rate as vaso_rate
+  -- one row in mcg/min, the rest in mcg/kg/min
+  , CASE WHEN rateuom = 'mcg/min' THEN rate / patientweight
+  ELSE rate END as vaso_rate
   , amount as vaso_amount
   , starttime
   , endtime

diff --git a/mimic-iv/concepts/medication/vasoactive_agent.sql b/mimic-iv/concepts/medication/vasoactive_agent.sql
@@ -0,0 +1,95 @@
+-- This query creates a single table with ongoing doses of vasoactive agents.
+-- TBD: rarely angiotensin II, methylene blue, and isoprenaline/isoproterenol are used.
+-- these are not in the query currently (they don't appear to be documented in MetaVision).
+
+-- collect all vasopressor administration times
+-- create a single table with these as start/stop times
+WITH tm AS
+(
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.dobutamine
+    UNION DISTINCT
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.dopamine
+    UNION DISTINCT
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.epinephrine
+    UNION DISTINCT
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.norepinephrine
+    UNION DISTINCT
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.phenylephrine
+    UNION DISTINCT
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.vasopressin
+    UNION DISTINCT
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.milrinone
+    UNION DISTINCT
+    -- combine end times from the same tables
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.dobutamine
+    UNION DISTINCT
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.dopamine
+    UNION DISTINCT
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.epinephrine
+    UNION DISTINCT
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.norepinephrine
+    UNION DISTINCT
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.phenylephrine
+    UNION DISTINCT
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.vasopressin
+    UNION DISTINCT
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.milrinone
+)
+-- create starttime/endtime from all possible times collected
+, tm_lag AS
+(
+    SELECT stay_id
+    , vasotime AS starttime
+    -- note: the last row for each partition (stay_id) will have a NULL endtime
+    -- we can drop this row later, as we know that no vasopressor will start at this time
+    -- (otherwise, we would have a later end time, which would mean it's not the last row!)
+    -- QED? :)
+    , LEAD(vasotime, 1) OVER (PARTITION BY stay_id ORDER BY vasotime) AS endtime
+    FROM tm
+)
+-- left join to raw data tables to combine doses
+SELECT t.stay_id, t.starttime, t.endtime
+-- inopressors/vasopressors
+, dop.vaso_rate AS dopamine
+, epi.vaso_rate AS epinephrine
+, nor.vaso_rate AS norepinephrine
+, phe.vaso_rate AS phenylephrine
+, vas.vaso_rate AS vasopressin
+-- inodialators
+, dob.vaso_rate AS dobutamine
+, mil.vaso_rate AS milrinone
+-- isoproterenol is used in CCU/CVICU but not in metavision
+-- other drugs not included here but (rarely) used in the BIDMC:
+-- angiotensin II, methylene blue
+FROM tm_lag t
+LEFT JOIN mimic_derived.dobutamine dob
+    ON t.stay_id = dob.stay_id
+    AND t.starttime >= dob.starttime
+    AND t.endtime <= dob.endtime
+LEFT JOIN mimic_derived.dopamine dop
+    ON t.stay_id = dop.stay_id
+    AND t.starttime >= dop.starttime
+    AND t.endtime <= dop.endtime
+LEFT JOIN mimic_derived.epinephrine epi
+    ON t.stay_id = epi.stay_id
+    AND t.starttime >= epi.starttime
+    AND t.endtime <= epi.endtime
+LEFT JOIN mimic_derived.norepinephrine nor
+    ON t.stay_id = nor.stay_id
+    AND t.starttime >= nor.starttime
+    AND t.endtime <= nor.endtime
+LEFT JOIN mimic_derived.phenylephrine phe
+    ON t.stay_id = phe.stay_id
+    AND t.starttime >= phe.starttime
+    AND t.endtime <= phe.endtime
+LEFT JOIN mimic_derived.vasopressin vas
+    ON t.stay_id = vas.stay_id
+    AND t.starttime >= vas.starttime
+    AND t.endtime <= vas.endtime
+LEFT JOIN mimic_derived.milrinone mil
+    ON t.stay_id = mil.stay_id
+    AND t.starttime >= mil.starttime
+    AND t.endtime <= mil.endtime
+-- remove the final row for each stay_id
+-- it will not have any infusions associated with it
+WHERE t.endtime IS NOT NULL;
diff --git a/mimic-iv/concepts/medication/vasopressin.sql b/mimic-iv/concepts/medication/vasopressin.sql
@@ -1,7 +1,10 @@
 -- This query extracts dose+durations of vasopressin administration
 select
   stay_id, linkorderid
-  , rate as vaso_rate
+  -- three rows in units/min, rest in units/hour
+  -- the three rows in units/min look reasonable and fit with the patient course
+  , CASE WHEN rateuom = 'units/min' THEN rate * 60.0
+    ELSE rate END AS vaso_rate
   , amount as vaso_amount
   , starttime
   , endtime

diff --git a/mimic-iv/tests/test_medication.py b/mimic-iv/tests/test_medication.py
@@ -0,0 +1,109 @@
+import pandas as pd
+from pandas.io import gbq
+import logging
+
+_LOGGER = logging.getLogger(__name__)
+
+def test_vasopressor_units(dataset, project_id):
+    # verify vasopressors in expected units
+    units = {
+        'milrinone': 'mcg/kg/min',
+        'dobutamine': 'mcg/kg/min',
+        'dopamine': 'mcg/kg/min',
+        'epinephrine': 'mcg/kg/min',
+        'norepinephrine': 'mcg/kg/min',
+        'phenylephrine': 'mcg/kg/min',
+        'vasopressin': 'units/hour',
+    }
+
+    itemids = {
+        'milrinone': 221986,
+        'dobutamine': 221653,
+        'dopamine': 221662,
+        'epinephrine': 221289,
+        'norepinephrine': 221906,
+        'phenylephrine': 221749,
+        'vasopressin': 222315,
+    }
+
+    hadm_id = {
+        'norepinephrine': [21898267],
+        'phenylephrine': [26809360],
+        'vasopressin': [26272149]
+    }
+
+    # verify we always have a unit of measure for the rate
+    query = f"""
+    select itemid, COUNT(*) AS n
+    FROM mimic_icu.inputevents
+    WHERE itemid IN ({", ".join([str(x) for x in itemids.values()])})
+    AND rateuom IS NULL
+    GROUP BY itemid
+    """
+    df = gbq.read_gbq(query, project_id=project_id, dialect="standard")
+    assert df.shape[0] == 0, 'found vasopressors with null units'
+
+    # norepinephrine has two rows in mg/kg/min
+    #   these are actually supposed to be mcg/kg/min - and the patient weight has been set to 1 to make it work
+    # phenylephrine has one row in mcg/min - looks fine, within expected dose
+    # vasopressin three rows in units/min - these look OK
+
+    for drug, hadm_id_list in hadm_id.items():
+        query = f"""
+        select hadm_id, rate, rateuom
+        FROM mimic_icu.inputevents
+        WHERE itemid = {itemids[drug]}
+        AND rateuom != '{units[drug]}'
+        LIMIT 10
+        """
+        df = gbq.read_gbq(query, project_id=project_id, dialect="standard")
+        # if we find new uninspected rows, raise a warning. this will only happen when mimic-iv is updated.
+        if (~df['hadm_id'].contains(hadm_id_list)).any():
+            _LOGGER.warn(f"""New data found with non-standard unit. Inspect the data with this query:
+
+            select *
+            from `physionet-data.mimic_icu.inputevents`
+            where itemid = {itemids['vasopressin']}
+            and stay_id in (
+                select stay_id from `physionet-data.mimic_icu.inputevents`
+                where itemid = {itemids['vasopressin']}
+                and rateuom != '{units['vasopressin']}'
+            )
+            order by starttime
+            """)
+        assert df.shape[0] != 10, f'many rows found with non-standard unit for {drug}'
+
+def test_vasopressor_doses(dataset, project_id):
+    # verify vasopressors have reasonable doses
+    # based on uptodate graphic 99963 version 19.0
+    # double the maximum dose used in refractory shock is the upper limit used
+    itemids = {
+        'milrinone': 221986,
+        'dobutamine': 221653,
+        'dopamine': 221662,
+        'epinephrine': 221289,
+        'norepinephrine': 221906,
+        'phenylephrine': 221749,
+        'vasopressin': 222315,
+    }
+    max_dose = {
+        'milrinone': 1.5,
+        'dobutamine': 40,
+        'dopamine': 40,
+        'epinephrine': 4,
+        'norepinephrine': 6.6,
+        'phenylephrine': 18.2,
+        'vasopressin': 0.08,
+    }
+
+    for vaso, dose in max_dose.items():
+        query = f"""
+        select COUNT(vaso_rate) AS n_above_rate
+        FROM mimic_derived.{vaso}
+        WHERE vaso_rate >= {dose}
+        """
+        df = gbq.read_gbq(query, project_id=project_id, dialect="standard")
+        n_above_rate = df.loc[0, 'n_above_rate']
+        assert n_above_rate == 0, f'found {vaso} rows with dose above {dose}, potentially incorrect'
+
+