MHIA 6160: Value & Quality in Healthcare

Week 3 R-Laboratory: Working with DAEDALUS Explore

Author

Dr. Cameron B. Chiarot

Laboratory Overview

Welcome to the Week 3 R-Laboratory. In our Week 3 R-Lecture, we discussed the necessity of moving beyond single-objective optimization (e.g., only looking at clinical outcomes) and using tools like DAEDALUS Explore to navigate the trade-off frontier between health system resilience and macroeconomic survival.

In this session, we will ingest a DAEDALUS Explore simulated dataset into R, introduce time-series plotting (geom_line), and construct the dual-lens visualizations required for your upcoming Week 4 R-Visualization Assessment and the Capstone (i.e., Written Brief & Summit Pitch).

Today’s session is divided into four progressive parts:

  1. Part I: Data Acquisition, Housekeeping & Preprocessing
  2. Part II: The Clinical Ceiling (System Lens)
  3. Part III: The Macroeconomic Cost (Industry Lens)
  4. Part IV: The Capstone Synthesis

Part I: Data Acquisition, Housekeeping & Preprocessing

Before we can visualize the trade-off frontier, we must acquire, inspect, and clean our raw data. In real-world health administration, clinical and macroeconomic data are rarely stored in the same neat table. We will need to audit our file architecture, isolate our metrics from different tabs of the DAEDALUS export, and preprocess into analysis-ready dataframes.

Step 1: Data Acquisition (DAEDALUS Explore)

  1. Navigate to the DAEDALUS Explore Web Dashboard.
  2. Set up your baseline scenario parameters:
    • Country: Canada
    • Pathogen: SARS 2004
    • Closures: None
    • Global vaccine investment: Low
    • Change in public behaviour: Low
    • Hospital surge capacity: 8000
  3. After selecting the parameters, click the Run button to run the simulation.
  4. When the simulation is complete, click the Export button to download the resulting .xlsx workbook to your local computer.

Step 2: Project Housekeeping

Organization is critical for reproducible data science. Please follow these three steps to ensure proper file and folder architecture:

  1. Open your RStudio Project for this course.
  2. Ensure you have a subfolder named data within your main project directory.
  3. Move the downloaded .xlsx file from your computer’s Downloads folder into this data folder.
Code 
# Load necessary packages
library(tidyverse)
library(readxl)
library(here)

# Define the file path dynamically to prevent working-directory errors
file_path <- here("data", "daedalus_CAN_low_sars_cov_1_none_low_8000.xlsx")

Step 3: Data Familiarization (The Data Audit)

Excel workbooks exported from external software often contain multiple tabs. We need to audit the file architecture to find exactly where our clinical and economic variables are housed.

Code 
# View the tab names in the workbook
excel_sheets(file_path)

# This is the output that you should see in your R console:

## [1] "Parameters"                "Costs"                     "Capacities"               
## [4] "Interventions"             "Time series"               "Value of Statistical Life"

We can see that we will need the epidemiological data from the "Time series" tab and the financial data from the "Costs" tab. Let’s pull both sheets into R and inspect their column headers before we attempt to do any type of analysis.

Code 
# HEALTH DATA
# Load and inspect the Time Series tab
health_data <- read_excel(file_path, sheet = "Time series")

glimpse(health_data)

# This is the output that you should see in your R console:

## Rows: 601
## Columns: 9
## $ day              <dbl> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16...
## $ prevalence       <dbl> 3.7742, 3.1498, 2.9579, 3.0054, 3.1934, 3.4724, 3.8188...
## $ hospitalised     <dbl> 0.0000, 0.2105, 0.3519, 0.4566, 0.5431, 0.6221, 0.6999...
## $ dead             <dbl> 0.0000, 0.0014, 0.0051, 0.0102, 0.0166, 0.0239, 0.0323...
## $ vaccinated       <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0...
## $ new_infected     <dbl> 0.0000, 1.9789, 1.6607, 1.5601, 1.5828, 1.6796, 1.8254...
## $ new_hospitalised <dbl> 0.0000, 0.2157, 0.1544, 0.1232, 0.1093, 0.1055, 0.1079...
## $ new_dead         <dbl> 0.0000, 0.0014, 0.0036, 0.0052, 0.0063, 0.0074, 0.0083...
## $ new_vaccinated   <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0...

# ECONOMIC DATA
# Load and inspect the Costs tab
econ_data <- read_excel(file_path, sheet = "Costs")

glimpse(econ_data)

# This is the output that you should see in our R console:

## Rows: 17                                                                     
## Columns: 3
## $ costId <chr> "total", "gdp", "gdp_closures", "gdp_absences", "education", "education_clos...
## $ metric <chr> "usd_millions", "usd_millions", "usd_millions", "usd_millions", "usd_million...
## $ value  <dbl> 6.541006e+06, 1.025408e+05, 0.000000e+00, 1.025408e+05, 6.648123e+03, 0.0000...

Step 4: Data Preprocessing & Merging

ImportantPedagogical Note: The Reality of Data Granularity

In an ideal analytical scenario, we always prefer to join our datasets into a single, unified dataframe. A perfectly symmetrical dataset (e.g., where one row equals one day for both health and economic metrics) allows for seamless plotting and statistical modeling.

However, real-world health administration data is rarely perfectly synchronized. In our DAEDALUS export, we are encountering a fundamental mismatch in data granularity:

  • The Health Data: Longitudinal (a daily time-series spanning 601 rows).
  • The Economic Data: Categorical (a static, final summary spanning only 17 rows).

Because these datasets measure time in completely different ways, attempting to join them by a common variable like day is mathematically impossible. If we forced a join, the software would either fail or incorrectly duplicate the final cumulative economic loss across every single day of the pandemic.

The Lesson: When a mismatch in granularity prevents a dataframe join, do not force it. Instead, you must process the datasets independently and rely on advanced visualization tools (like a patchwork dashboard) to unite the metrics visually for your executive stakeholders.

Code 
# DATA PREPROCESSING (CLEANING)

# HEALTH DATA
# Clean by keeping only Day and Hospitalizations
clean_health <- health_data %>%
  select(day, new_hospitalised)

# ECONOMIC DATA
# Clean by filtering for specific economic drivers
clean_econ <- econ_data %>%
  filter(costId %in% c("gdp_closures", "gdp_absences", "education")) %>%
  mutate(
    # Convert millions to billions for executive readability
    value_billions = value / 1000, 
    
    # Rename the variables for a polished executive presentation
    costId = case_when(
      costId == "gdp_closures" ~ "GDP Loss: Business Closures",
      costId == "gdp_absences" ~ "GDP Loss: Worker Absenteeism",
      costId == "education" ~ "Future Human Capital (Education)"
    )
  )

# Audit the data frames
glimpse(clean_health)
glimpse(clean_econ)

Part II: The Clinical Ceiling (System Lens)

As health administrators, our primary operational constraint is the Clinical Ceiling (i.e., Volume Capacity), defined as the maximum surge capacity our hospital infrastructure can sustain before mortality rates spike due to maximum resource utilization.

We will use geom_line() to track new hospital demand over time, and geom_hline() to impose a strict capacity limit (e.g., 8,000 beds).

Code 
# HEALTH DATA
# Establish the clinical capacity constraint (i.e., the red line)
hospital_capacity <- 8000

# Build the Epidemiological Time-Series
ggplot(data = clean_health, aes(x = day, y = new_hospitalised + 100)) +
  
  # Map the time-series curve
  geom_line(color = "#d9534f", linewidth = 1) +
  
  # Add the Clinical Ceiling Intercept
  geom_hline(yintercept = hospital_capacity, linetype = "dashed",
             color = "#333333", linewidth = 1) +
  
  # Annotate the breach
  annotate("text", x = 400, y = 12000, label = "System Capacity (n = 8,000)",
           color = "#333333", fontface = "bold", size = 4) +
  
  # Explicit Axis Scaling
  scale_y_log10(labels = scales::comma_format()) + 
  scale_x_continuous(breaks = seq(0, 600, by = 100)) +
  
  # Executive Formatting
  labs(
    title = "Fig 1. The Clinical Ceiling (Health System Lens)",
    subtitle = "Daily Hospital Demand vs. National Surge Capacity",
    x = "Days Since Outbreak",
    y = "New Hospital Demand (Log10 Count)"
  ) +
 
  theme_minimal(base_size = 12) +
  theme(
    plot.title = element_text(face = "bold", color = "#333333"),
    panel.grid.minor = element_blank()
  )

NoteTechnical Note: Why did we add 100 Patients to hospital demand code in Part II?

The Mathematics of the “Baseline Shift”: If you look closely at the data generation code in Part I, you will notice we added + 100 to the end of our hospital_demand equation. This is a standard data engineering technique used to prevent logarithmic scales from breaking.

  • The Mathematical Trap: A logarithmic scale measures exponential magnitudes (powers of 10). Because no power of 10 can ever equal zero, the logarithm of zero is mathematically undefined: \[\log_{10}(0) = -\infty\]
  • The Problem: When ggplot2 attempts to plot a true \(0\) on a log10 scale, it evaluates it as negative infinity, throws a silent error, and simply deletes those data points from your visual. If you ever run a time-series and the tails of your curve are missing, it is usually because the data hit absolute zero.
  • The Operational Fix: By adding a baseline of 100 patients, we instituted a numeric floor. The math now works perfectly because \(\log_{10}(100) = 2\), allowing the software to render the flat sections of the timeline. In health administration, this is also operationally accurate: a national health system’s hospital demand never realistically drops to absolute zero, as there is always a steady baseline of routine admissions.

The Visual Imperative: Crucially, we had to apply this logarithmic scale (and therefore the baseline shift) to visualize our actual operational limits. On a standard linear axis, the catastrophic 1.5 million patient surge visually crushes the much smaller 8,000-bed capacity threshold flat against the zero line, making it impossible to evaluate when the health system actually breaks.

ImportantExecutive Interpretation

In a “No Closures” scenario, this plot demonstrates a major failure of the health system. Demand surpasses the hospitals’ 8,000-bed capacity almost immediately, rendering clinical care functionally unavailable for the majority of the population at the peak of the pandemic wave.

Part III: The Macroeconomic Cost (Industry Lens)

Lockdowns save lives by flattening the curve, but they generate severe downstream consequences, including economic contraction. To practice the Dual-Lens Strategy, we must visualize the exact same 600-day period through the perspective of national GDP output.

Code 
# ECONOMIC DATA
# Build the Macroeconomic Bar Chart
ggplot(data = clean_econ, aes(x = reorder(costId, value_billions), y = value_billions)) +
  
  # Map the categorical bars
  geom_col(fill = "#0275d8", width = 0.5, alpha = 1) +
  
  # Flip coordinates for readable executive labels
  coord_flip() + 
  
  # Explicit Axis Scaling (Format as billions of dollars)
  scale_y_continuous(labels = scales::dollar_format(prefix = "$")) +
  scale_x_discrete(labels = function(x) stringr::str_wrap(x, width = 20)) +

  
  # Executive Formatting
  labs(
    title = "Fig 2. Macroeconomic Contraction (Industry Lens)",
    subtitle = "Financial Losses by Sector after 600 Days",
    x = "Cost Sector",
    y = "Total Loss (Billions USD)"
  ) +
  
  theme_minimal(base_size = 12) +
  theme(
    plot.title = element_text(face = "bold", color = "#333333"),
    panel.grid.minor = element_blank()
  )

NoteHealth Economics: How Do We Measure “Cost”?

When evaluating a pandemic, “cost” does not just mean government expenditures or hospital bills. DAEDALUS uses advanced health-economic modelling to quantify the broad macroeconomic and societal impacts of a biological shock.

Here is exactly what the metrics in our Industry Lens represent:

  • GDP Loss (Worker Absenteeism): This is the tangible contraction in the economy caused by the biological shock itself. Even if a government refuses to lock down, industrial output plummets when a massive portion of the workforce is incapacitated by illness or removed via mortality.
  • GDP Loss (Business Closures): The direct financial hit to goods, services, and industrial output caused by government-mandated lockdowns. In our baseline “No Closures” scenario, this is naturally $0.
  • Future Human Capital (Education): A classic macroeconomic metric. When schools close to slow transmission, the loss of in-person learning hours is mathematically projected forward as a permanent reduction in the lifetime earning potential of that specific generation.
  • The Hidden Metric (Value of Statistical Life): DAEDALUS also calculates a massive aggregate “Total Cost” which puts a monetized dollar value on human mortality. We intentionally filtered this out of our executive dashboard to avoid masking the tangible industrial metrics, but it is a standard tool used by policymakers to weigh the true, catastrophic cost of lives lost.

Part IV: The Capstone Synthesis

For your Capstone Summit Pitch, presenting these visuals on separate slides makes it difficult for decision-makers to evaluate the trade-offs simultaneously. To set you up for success, we will explore two different R strategies for building a unified, side-by-side dashboard.

Strategy 1: Data Reshaping (Symmetrical Granularity)

Best for: Visualizing multiple metrics from the same dataset that share the exact same time scale (e.g., plotting Hospitalizations and Mortality side-by-side).

Data Reshaping: DAEDALUS exports time-series data in a wide format. We use pivot_longer() to restructure the matrix, and facet_wrap() to split the plot into synchronized panels.

Code 
# Pull both metrics from the raw time-series file and reshape
reshaped_health <- read_excel(file_path, sheet = "Time series") %>%
  select(day, new_hospitalised, new_dead) %>%
  pivot_longer(
    cols = c(new_hospitalised, new_dead), 
    names_to = "metric_type",                           
    values_to = "recorded_value"                        
  ) %>%
  mutate(
    metric_type = if_else(metric_type == "new_hospitalised",
                          "New Hospitalizations",
                          "New Mortality")
  )

# Plot using facet_wrap 
ggplot(data = reshaped_health,
       aes(x = day, y = recorded_value + 100, color = metric_type)) +
  geom_line(linewidth = 1) +
  facet_wrap(~metric_type, scales = "free_y") + 
  scale_y_log10(labels = scales::comma_format()) +
  scale_color_manual(values = c("#d9534f", "#333333")) +
  labs(title = "Strategy 1: Faceted Clinical Evaluation",
       x = "Days Since Outbreak",
       y = "Log10 Count") +
  theme_minimal(base_size = 12) +
  theme(plot.title = element_text(face = "bold", color = "#333333", hjust = 0.5)) +
  theme(legend.position = "none")

Strategy 2: The Mixed-Format Executive Dashboard

Best for: Scenarios where metrics require completely different chart types and datasets (e.g., uniting a longitudinal line chart with a categorical bar chart).

Visualizations: We can use the patchwork package to stitch our two independent visualizations from Parts II and III together.

Code 
library(patchwork)

# Store the Health Plot (from Part II)
health_panel <- ggplot(data = clean_health, aes(x = day, y = new_hospitalised + 100)) +
  geom_line(color = "#d9534f", linewidth = 1) +
  geom_hline(yintercept = hospital_capacity,
             linetype = "dashed", color = "#333333", linewidth = 1) +
  annotate("text", x = 400, y = 10000, label = "System Capacity",
           color = "#333333", fontface = "bold", size = 4) +
  scale_y_log10(labels = scales::comma_format()) +
  labs(title = "Health System Impact (Daily Demand)",
       subtitle = "",
       x = "Days",
       y = "Hospital Demand") +
  theme_minimal(base_size = 11)

# Store the Economy Plot (from Part III)
econ_panel <- ggplot(data = clean_econ, aes(x = reorder(costId, value_billions), y = value_billions)) +
  geom_col(fill = "#0275d8", width = 0.5, alpha = 1) +
  coord_flip() + 
  scale_y_continuous(labels = scales::dollar_format(prefix = "$")) +
  scale_x_discrete(labels = function(x) stringr::str_wrap(x, width = 20)) +
  labs(title = "Macroeconomic Cost (Loss by Sector)",
       subtitle = "",
       x = "",
       y = "Total Loss (Billions USD)") +
  theme_minimal(base_size = 11)

# Stitch the Dashboard Together using Patchwork
health_panel + econ_panel + 
  plot_annotation(
    title = "Strategy 2: The Dual-Lens Executive Dashboard",
    theme = theme(plot.title = element_text(face = "bold", hjust = 0.5, color = "#333333"))
  )

TipR-Visualization & Capstone Application

If you need to compare two daily time-series metrics side-by-side (e.g., Hospitalizations vs. Mortality), use Strategy 1. To unite a time-series curve with a categorical bar chart (Health vs. Economy), you must use Strategy 2.

Executive Discussion Questions

  1. Review the dual-lens plot above. In the “No Closures” scenario, the economy suffers massive compounding losses even without government intervention. Why does the macroeconomic engine model severe GDP loss when businesses are officially allowed to remain open? (Hint: Think about workforce mortality and morbidity).
  2. If you were to run a second scenario implementing a strict, immediate lockdown on Day 10, how would you expect the clinical curve and the economic bars to shift relative to one another?

Week 3 R-Laboratory Synthesis Deliverable

Based on the dual-lens visual generated in Part IV, briefly explain why attempting to optimize a pandemic response using only epidemiological transmission data (ignoring the macroeconomic data) is a high-risk administrative strategy.

{end}