What Kind of Aggregation Should Be Used When Working with the Sepsis Dataset?

The Sepsis dataset (https://physionet.org/content/challenge-2019/1.0.0/) contains data for 40,336 patients, with hourly ICU readings of vital signs, lab values and clinical observations. When working on identifying KPIs, gathering insights and establishing correlations between the biomarkers, I did not pay much attention to the types of aggregations that should have been used i.e., average, minimum and maximum.

At the beginning of the analysis, I had assumed that calculating the average of lab values for each patient across their ICU hours would suffice for gathering meaningful insights as this approach seemed reasonable when identifying trends and establishing correlation between biomarkers. However, when working on calculating clinical scores such as the Sequential Organ Failure Assessment (SOFA) score and the Multiple Organ Dysfunction Score (MODS), the choice of aggregation became important as I should have no longer used average as the default methodology if I wanted my insights to portray the correct picture. 

The MODS ranks the severity of dysfunction across multiple organ systems in patients during their length of stay in the ICU. Similarly, SOFA score is a clinical scoring system used to track the patient status and assess the extent of organ dysfunction. Both scores are used to assess the disease severity and predict mortality risk in hospitals/ICU. In addition, both scoring systems assign a score from 0 to 4 based on the severity of organ dysfunction where, 0 indicates low to no severity and 4 indicates severe organ dysfunction.

At the beginning of the project, I calculated these scores using the average lab values for each patient. However, in reality during ICU hours, a patient's condition can deteriorate over a short span of time. Since the dataset contains hourly ICU readings, a patient may have normal lab readings for the majority part of their stay but experience a brief time of severe organ dysfunction. When lab readings are averaged across ICU hours, these critical health events become less valuable and visible in the analysis. 

Let's take a simple example for the above statement, Patient 1 has lab readings as 8, 8, 8, 8, 8 and Patient 2 has lab readings as 2, 2, 16, 16, 4 wherein both the patients have an average reading of 8. However, the average of lab readings in the example is failing to capture the extreme spike in lab readings of Patient 2. Both MODS and SOFA score are designed to assess the extreme severity of a patients’ health. Therefore, they rely on extreme lab readings rather than average values, thereby predicting the outcome of mortality risk. 

Based on the organ system being assessed, I calculated the MODS and SOFA score using either the minimum or maximum lab value readings during the patients’ ICU hours. 

The table below gives an outline of the type of aggregation method required for each organ system to calculate MODS along with its corresponding calculated fields in tableau.

Organ System	Biomarker	Type of Clinical Aggregation	MODS Formula
Cardiovascular	Mean Arterial Pressure (MAP)	Minimum	{ FIXED [Patient ID]: IF MIN([MAP]) >= 70 THEN 0 ELSEIF MIN([MAP]) >= 60 THEN 1 ELSEIF MIN([MAP]) >= 50 THEN 2 ELSEIF MIN([MAP]) >= 40 THEN 3 ELSE 4 END }
Coagulation	Platelets	Minimum	{ FIXED [Patient ID] : IF MIN([Platelets]) >= 150 THEN 0 ELSEIF MIN([Platelets]) >= 100 THEN 1 ELSEIF MIN([Platelets]) >= 50 THEN 2 ELSEIF MIN([Platelets]) >= 20 THEN 3 ELSE 4 END }
Kidney	Creatinine	Maximum	{ FIXED [Patient ID]: IF MAX([Creatinine]) < 1.2 THEN 0 ELSEIF MAX([Creatinine]) < 2 THEN 1 ELSEIF MAX([Creatinine]) < 3.5 THEN 2 ELSEIF MAX([Creatinine]) < 5 THEN 3 ELSE 4 END }
Liver	Bilirubin	Maximum	{ FIXED [Patient ID]: IF MAX([Bilirubin total]) < 1.2 THEN 0 ELSEIF MAX([Bilirubin total]) < 2 THEN 1 ELSEIF MAX([Bilirubin total]) < 6 THEN 2 ELSEIF MAX([Bilirubin total]) < 12 THEN 3 ELSE 4 END }
Metabolic	Lactate	Maximum	{ FIXED [Patient ID] : IF MAX([Lactate]) < 2 THEN 0 ELSEIF MAX([Lactate]) < 4 THEN 1 ELSEIF MAX([Lactate]) < 6 THEN 2 ELSEIF MAX([Lactate]) < 10 THEN 3 ELSE 4 END }
Respiratory	SF ratio Note:  SF_Ratio = IF [FiO2] > 0 THEN [SaO2] / ([FiO2]) ELSE NULL END	Minimum	{ FIXED [Patient ID]: IF MIN([SF_Ratio]) >= 400 THEN 0 ELSEIF MIN([SF_Ratio]) >= 300 THEN 1 ELSEIF MIN([SF_Ratio]) >= 200 THEN 2 ELSEIF MIN([SF_Ratio]) >= 100 THEN 3 ELSE 4 END }
Total MODS			[Cardiovascular]+[Coagulation]+[Kidney]+[Liver]+[Metabolic]+[Respiratory]

To analyze the overall MODS, I categorized the scores into the following buckets as mentioned in the formula below -

IF ISNULL([MOD_Score]) THEN "Unknown"

ELSEIF [MOD_Score] <= 2 THEN "Mild Dysfunction"

ELSEIF [MOD_Score] <= 5 THEN "Moderate Organ Dysfunction"

ELSEIF [MOD_Score] <= 9 THEN "Significant Multi-Organ Dysfunction"

ELSE "Severe Multi-Organ Failure"

END

In the calculated fields mentioned above, since each patient had lab values recorded on an hourly basis, it is necessary to aggregate at the patient level before calculating organ specific scores. Hence, level of detail (LOD) in tableau is a useful formula for this purpose because it allows to calculate corresponding patient level minimum and maximum lab values.

SOFA score uses a similar methodology to the MODS scoring system wherein individual organ systems are assessed and assigned scores based on the organ severity. Similar to MODS, the overall SOFA score is calculated by adding the scores across all organ systems.

While averaging values may be technically correct for identifying trends, extracting overall insights and understanding correlations, it can still produce results that are misleading if the methodology is not properly understood. Working on this dataset has helped me understand the importance of not only using the visualization tool i.e., tableau. In addition, it has also helped me gain domain knowledge insights and the underlying logic behind important clinical metrics.