MHIA 6160: Value & Quality in Healthcare

Week 4 R-Laboratory: Modelling with DAEDALUS Explore

Author

Dr. Cameron B. Chiarot

Laboratory Overview

Welcome to the Week 4 R-Laboratory. In Week 3, we mastered the ingestion and visualization of a single pandemic scenario, learning how to view a crisis through both a Health System Lens and an Industry Lens.

However, in executive health administration, you are rarely asked to analyze a single scenario; you are expected to weigh strategic options and recommend evidence-based interventions for stakeholders. To prepare for your Capstone, this lab transitions from descriptive visualization to Comparative Scenario Analysis.

Today, we will ingest a second dataset (representing an active policy intervention), merge it with our Week 3 baseline dataframe, and construct comparative visuals and boardroom-ready summary tables using the gt package.

Today’s session is divided into four progressive parts:

  1. Part I: Multi-Scenario Data Ingestion & Binding
  2. Part II: Flattening the Curve (Comparative Health Lens)
  3. Part III: The Economic Inverse (Comparative Industry Lens)
  4. Part IV: The Executive Summary Table

Part I: Multi-Scenario Data Ingestion & Binding

To compare policies, we must first bring both datasets into the same R environment. We will use the mutate() function to “tag” each dataset with its respective scenario name, and then use bind_rows() to stack them into master dataframes for comparative plotting.

Step 1: Data Acquisition

Navigate to the DAEDALUS Explore Web Dashboard. You will need two .xlsx exports from DAEDALUS Explore in your data subfolder:

Scenario 1 Parameters (Baseline)

  • Country: Canada
  • Pathogen: SARS 2004
  • Closures: None
  • Global vaccine investment: Low
  • Change in public behaviour: Low
  • Hospital surge capacity: 8000

Scenario 2 Parameters (Intervention)

  • Country: Canada
  • Pathogen: SARS 2004
  • Closures: Economic_closures
  • Global vaccine investment: Low
  • Change in public behaviour: Medium
  • Hospital surge capacity: 8000


Important

After selecting the parameters, click the Run button to run the simulation. When the simulation is complete, click the Export button to download the resulting .xlsx workbook to your local computer.

Step 2: Project Housekeeping & Parameter Audit

Before merging, it is best practice to audit the Parameters tab of both files to ensure you downloaded the correct simulation data. First, we will load the appropriate R libraries and call in the two datasets then begin exploring the parameters.

Code 
# Load necessary packages
library(tidyverse)
library(readxl)
library(here)
library(gt) # For Part IV

# Define the file paths dynamically 
file_path_s1 <- here("data",
                     "daedalus_CAN_low_sars_cov_1_none_low_8000.xlsx")
file_path_s2 <- here("data",
                     "daedalus_CAN_low_sars_cov_1_economic_closures_medium_8000.xlsx")

# Audit the Parameters tab to verify the scenario inputs
scenario_1 <- read_excel(file_path_s1, sheet = "Parameters")
scenario_2 <- read_excel(file_path_s2, sheet = "Parameters")

glimpse(scenario_1$value)
glimpse(scenario_2$value)

# Scenario 1 Parameter List Output:
## chr [1:6] "CAN" "low" "sars_cov_1" "none" "low" "8000"

# Scenario 2 Parameter List Output:
## chr [1:6] "CAN" "low" "sars_cov_1" "economic_closures" "medium" "8000"

Step 3: Comparative Preprocessing (Health Data)

We will read the Time series tab for both scenarios, tag them, and bind them into a single clean_health_comp dataframe.

Code 
################
# HEALTH DATA
################

# SCENARIO 1
# Process Health Data (BASELINE)
health_s1 <- read_excel(file_path_s1, sheet = "Time series") %>%
  select(day, new_hospitalised) %>%
  mutate(scenario_type = "Scenario 1: No Closures") # Creates a categorical tag

# SCENARIO 2
# Process Health Data (INTERVENTION)
health_s2 <- read_excel(file_path_s2, sheet = "Time series") %>%
  select(day, new_hospitalised) %>%
  mutate(scenario_type = "Scenario 2: Economic Closures")

# SCENARIO 1 + SCENARIO 2
# Bind rows together into a master dataframe
clean_health_comp <- bind_rows(health_s1, health_s2)

# AUDIT THE COMBINED STRUCTURE
glimpse(clean_health_comp)

Step 4: Comparative Preprocessing (Economic Data)

We repeat the exact same tagging and binding process for the macroeconomic Costs tab.

Code 
################
# ECONOMIC DATA
################

# SCENARIO 1
# Process Economic Data (BASELINE)
econ_s1 <- read_excel(file_path_s1, sheet = "Costs") %>%
  filter(costId %in% c("gdp_closures", "gdp_absences", "education")) %>%
  mutate(
    value_billions = value / 1000, 
    costId = case_when(
      costId == "gdp_closures" ~ "GDP Loss: Business Closures",
      costId == "gdp_absences" ~ "GDP Loss: Worker Absenteeism",
      costId == "education" ~ "Future Human Capital (Education)"
    ),
    scenario_type = "Scenario 1: No Closures" # Tag
  )

# SCENARIO 2
# Process Economic Data (INTERVENTION)
econ_s2 <- read_excel(file_path_s2, sheet = "Costs") %>%
  filter(costId %in% c("gdp_closures", "gdp_absences", "education")) %>%
  mutate(
    value_billions = value / 1000, 
    costId = case_when(
      costId == "gdp_closures" ~ "GDP Loss: Business Closures",
      costId == "gdp_absences" ~ "GDP Loss: Worker Absenteeism",
      costId == "education" ~ "Future Human Capital (Education)"
    ),
    scenario_type = "Scenario 2: Economic Closures" # Tag
  )

# SCENARIO 1 + SCENARIO 2
# Bind rows together into a master dataframe
clean_econ_comp <- bind_rows(econ_s1, econ_s2)

# AUDIT THE COMIBINED STRUCTURE
glimpse(clean_econ_comp)

Part II: Flattening the Curve (Comparative Health Lens)

In Week 3, our baseline “No Closures” scenario revealed a catastrophic breach of the Canadian health system’s 8,000-bed capacity. To test the efficacy of our proposed intervention (Scenario 2: Economic Closures + Medium Vaccine Investment), we must plot both epidemiological curves on the exact same axis.

Because we stacked our data and tagged it with a scenario_type column in Part I, we can map this new variable directly to the color and linetype aesthetics in ggplot2.

Code 
# Establish the clinical capacity constraint
hospital_capacity <- 8000

# Build the Comparative Epidemiological Time-Series
health_plot <- ggplot(data = clean_health_comp, aes(x = day, y = new_hospitalised + 100,
                                                    color = scenario_type,
                                                    linetype = scenario_type)) +
  
  # Map the time-series curves
  geom_line(linewidth = 1) +
  
  # Add the Clinical Ceiling Intercept
  geom_hline(yintercept = hospital_capacity, linetype = "dotted",
             color = "#333333", linewidth = 0.5) +
  
  # 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)) +
  
  # Define executive colors and line styles
  scale_color_manual(values = c("Scenario 1: No Closures" = "#d9534f", 
                                "Scenario 2: Economic Closures" = "#0275d8")) +
  scale_linetype_manual(values = c("Scenario 1: No Closures" = "solid", 
                                   "Scenario 2: Economic Closures" = "dashed")) +
  
  # Executive Formatting
  labs(
    title = "Fig 1. Flattening the Curve (Comparative Health System Lens)",
    subtitle = "Baseline (No Closures) vs. Intervention (Economic Closures)",
    x = "Days Since Outbreak",
    y = "New Hospital Demand (Log10 Count)",
    color = "Policy Scenario",             # Titles the legend
    linetype = "Policy Scenario"           # Merges linetype and color into one legend
  ) +
  
  theme_minimal(base_size = 12) +
  theme(
    plot.title = element_text(face = "bold", color = "#333333"),
    panel.grid.minor = element_blank(),
    legend.position = "bottom",       # Moves legend to the bottom for boardroom visuals
    legend.title = (element_blank())  # Removes legend title
  )
health_plot

NoteR-Visualization Note: Mapping vs. Hardcoding

In Week 3, we hardcoded our line color by placing it outside the aes() function (e.g., geom_line(color = "red")). In this comparative plot, we mapped our line color to a variable by placing it inside the aes() function (e.g., aes(color = scenario_type)). This instructs R to look inside the dataframe, identify the distinct categories, and assign a unique visual property to each one. We then used scale_color_manual() to dictate exactly which colors to use for maximum contrast.

ImportantExecutive Interpretation

This comparative visualization is the empirical proof required in a Capstone project (i.e., Written Brief and Summit Pitch). If the blue intervention curve (Economic Closures) stays significantly further below the red baseline curve (or successfully remains entirely beneath the 8,000-bed dashed line), the clinical objective of the policy has been mathematically validated.

Part III: The Economic Inverse (Comparative Industry Lens)

Lockdowns and economic closures may flatten the clinical curve, but that policy choice carries a severe macroeconomic price tag. To visualize the exact trade-off, we will plot our macroeconomic indicators side-by-side.

Because we stacked our data in Part I, we can map the scenario_type variable to the fill aesthetic and use a dodging geometry to compare the financial damage of doing nothing (Baseline) versus shutting down the economy (Intervention).

Code 
# Build the Comparative Macroeconomic Bar Chart
econ_plot <- ggplot(data = clean_econ_comp, aes(x = reorder(costId, value_billions), 
                                                y = value_billions, 
                                                fill = scenario_type)) +
  
  # Map the categorical bars, set position to dodge for side-by-side comparison
  geom_col(position = position_dodge(width = 0.8), width = 0.7, alpha = 1) +
  
  # Flip coordinates for readable executive labels
  coord_flip() + 
  
  # Explicit Axis Scaling
  scale_y_continuous(labels = scales::dollar_format(prefix = "$")) +
  scale_x_discrete(labels = function(x) stringr::str_wrap(x, width = 20)) +
  
  # 4. Define executive colors to match the Health Lens
  scale_fill_manual(values = c("Scenario 1: No Closures" = "#d9534f", 
                               "Scenario 2: Economic Closures" = "#0275d8")) +
  
  # Executive Formatting
  labs(
    title = "Fig 2. The Economic Inverse (Comparative Industry Lens)",
    subtitle = "Baseline (No Closures) vs. Intervention (Economic Closures)",
    x = "Cost Sector",
    y = "Total Loss (Billions USD)",
    fill = "Policy Scenario"
  ) +
  
  theme_minimal(base_size = 12) +
  theme(
    plot.title = element_text(face = "bold", color = "#333333"),
    panel.grid.minor = element_blank(),
    legend.title = (element_blank()),  # Removes legend title
    legend.position = "bottom"
  )
econ_plot

NoteR-Visualization Note: The Power of Position Dodge

By default, if you map a categorical variable to the fill aesthetic in a bar chart, ggplot2 will stack the bars on top of each other (position = "stack"). While stacked bars are useful for showing parts of a whole, they make it difficult to compare precise baseline and intervention values.

By adding position = position_dodge() inside the geom_col() function, we force the software to render the bars side-by-side. This is the optimal configuration for comparative executive analysis.

ImportantExecutive Interpretation

This visualization illustrates the core dilemma of health administration. Notice how the blue intervention bar for GDP Loss: Worker Absenteeism shrinks compared to the baseline (because fewer people are sick), but the blue bar for GDP Loss: Business Closures spikes massively.

The intervention did not eliminate the economic damage; it merely transferred the damage from unmitigated biological spread to state-mandated closures. Your Capstone task is to find a policy balance that minimizes both.

Part IV: The Capstone Synthesis (Figure & Table)

When presenting to stakeholders, executives expect a concise, unified summary. Your Capstone deliverable will require two distinct formats: a Master Figure (ideal for your Summit Pitch slide deck) and an Executive Summary Table (essential for your Written Brief).

The Master Figure (Dashboard)

We will use the patchwork package to stitch our Comparative Health Lens (Part II) and Comparative Industry Lens (Part III) together into a single, unified dashboard.

Code 
library(patchwork)

# Assuming you assigned your Part II plot to 'health_plot' and Part III to 'econ_plot'

# We stitch the Part II and Part III plots; we will pull them together and collect the legends so they share one unified key at the bottom.

exec_dashboard <- health_plot + econ_plot + 
  plot_layout(guides = "collect") +
  plot_annotation(
    title = "Executive Policy Dashboard: Baseline vs. Intervention",
    theme = theme(plot.title = element_text(face = "bold", size = 16, hjust = 0.5, color = "#333333"))
  ) & 
  theme(legend.position = "bottom") # The & operator must go at the very end

exec_dashboard

The Executive Summary Table

Visuals are excellent for communicating trends, but decision-makers also need the hard bottom-line numbers. We will use the group_by() and summarize() functions to calculate the peak hospital demand and the total economic loss for both scenarios, and then use the gt (Grammar of Tables) package to format these numbers into a boardroom-ready table.

Code 
# Step A: Calculate the peak hospital demand per scenario
health_summary <- clean_health_comp %>%
  group_by(scenario_type) %>%
  summarize(peak_hospital_demand = max(new_hospitalised))

# Step B: Calculate the total macroeconomic loss per scenario
econ_summary <- clean_econ_comp %>%
  group_by(scenario_type) %>%
  summarize(total_economic_loss_billions = sum(value_billions))

# Step C: Join the two summaries together
exec_summary_df <- health_summary %>%
  left_join(econ_summary, by = "scenario_type")

# Step D: Format the dataframe into a polished table using gt()
exec_summary_df %>%
  gt() %>%
  
  # Add titles and subtitles
  tab_header(
    title = md("**Executive Policy Summary**"),
    subtitle = "Peak Clinical Demand vs. Total Macroeconomic Contraction"
  ) %>%
  
  # Format numbers (Commas for beds, Currency for dollars)
  fmt_number(
    columns = peak_hospital_demand,
    decimals = 0,
    use_seps = TRUE
  ) %>%
  fmt_currency(
    columns = total_economic_loss_billions,
    currency = "USD",
    decimals = 2,
    pattern = "{x} B"
  ) %>%
  
  # Rename columns for executive presentation
  cols_label(
    scenario_type = "Policy Scenario",
    peak_hospital_demand = "Peak Hospital Demand (Beds)",
    total_economic_loss_billions = "Total Economic Loss"
  ) %>%
  
  # Add a professional source note
  tab_source_note(
    source_note = "Data generated via DAEDALUS Explore Simulation (SARS 2004, Canada)."
  ) %>%
  
  # Apply a clean, classic theme
  opt_table_font(font = "Arial") %>%
  tab_options(
    table.border.top.color = "#333333",
    table.border.bottom.color = "#333333",
    heading.background.color = "#f8f9fa"
  )
Executive Policy Summary
Peak Clinical Demand vs. Total Macroeconomic Contraction
Policy Scenario Peak Hospital Demand (Beds) Total Economic Loss
Scenario 1: No Closures 73,171 $109.19 B
Scenario 2: Economic Closures 31,770 $94.72 B
Data generated via DAEDALUS Explore Simulation (SARS 2004, Canada).
ImportantExecutive Interpretation

This table strips away the noise and presents the ultimate trade-off frontier. By reading across the rows, stakeholders can immediately quantify what the intervention achieved. They can see exactly how many peak hospital admissions were avoided (saving the health system) juxtaposed against exactly how many extra billions of dollars were lost to secure that clinical victory.

Executive Discussion Questions

  1. Look at your final gt table. Based on the hard numbers, did the “Economic Closures” intervention successfully keep peak hospital demand below the 8,000-bed national capacity?
  2. What was the marginal cost of this policy? In other words, how many additional billions of dollars did the intervention cost the economy compared to the baseline “No Closures” scenario?

Week 4 R-Laboratory Synthesis Deliverable

Using the master dashboard and the summary table generated in Part IV, write a 1-paragraph (~200 words) brief to the Minister of Health. State whether you recommend adopting Scenario 2 (Economic Closures) over Scenario I (Baseline, No Closures), and justify your recommendation using at least one specific clinical data point and one macroeconomic data point from your analysis.

{end}