---
title: "Module 3 Solution — Joins and Summaries"
format:
  html:
    css: "../dark-theme.css"
    include-in-header: |
      <script>
        // Immediate theme application to prevent flash
        (function() {
          const savedTheme = localStorage.getItem('theme') || 'light';
          document.documentElement.setAttribute('data-bs-theme', savedTheme);
        })();
      </script>
    include-after-body: |
      <button class="theme-toggle" onclick="toggleTheme()" title="Toggle theme">🌙</button>
      <script>
        function toggleTheme() {
          const html = document.documentElement;
          const currentTheme = html.getAttribute('data-bs-theme');
          const newTheme = currentTheme === 'dark' ? 'light' : 'dark';

          html.setAttribute('data-bs-theme', newTheme);
          localStorage.setItem('theme', newTheme);

          // Update button icon
          const button = document.querySelector('.theme-toggle');
          button.innerHTML = newTheme === 'dark' ? '☀️' : '🌙';
        }

        // Initialize theme on load
        document.addEventListener('DOMContentLoaded', function() {
          const savedTheme = localStorage.getItem('theme') || 'light';
          document.documentElement.setAttribute('data-bs-theme', savedTheme);
          const button = document.querySelector('.theme-toggle');
          if (button) {
            button.innerHTML = savedTheme === 'dark' ? '☀️' : '🌙';
          }
        });
      </script>
editor: visual
---
# 📝 Module 3 Solution Guide

*Complete solutions for Module 3 exercises based on R4DS Chapters 12 and 13*

## Setup

```{r}
#| message: false
# Load required packages
library(tidyverse)  # Includes dplyr, tidyr for joins and pivots
library(lubridate)  # Date manipulation

# Check setup
packageVersion("tidyverse")
```

------------------------------------------------------------------------

## Exercise 1: Tidy Data Practice (R4DS Ch. 12)

```{r}
# Create wide format lab dataset (NOT TIDY)
lab_wide <- tibble(
  USUBJID = c("001-001", "001-002", "001-003", "001-004"),
  ALT_BL = c(25, 30, 28, 35),
  AST_BL = c(22, 28, 26, 32),
  BILI_BL = c(0.8, 1.2, 0.9, 1.1),
  ALT_W2 = c(27, 32, 30, 38),
  AST_W2 = c(24, 30, 28, 35),
  BILI_W2 = c(0.9, 1.1, 1.0, 1.2),
  ALT_W4 = c(26, 29, 31, 36),
  AST_W4 = c(23, 27, 29, 33),
  BILI_W4 = c(0.8, 1.0, 1.1, 1.0)
)

# 1a. Convert to long format (TIDY) using pivot_longer()
lab_long <- lab_wide %>%
  pivot_longer(
    cols = -USUBJID,
    names_to = c("PARAM", "VISIT"),
    names_sep = "_",
    values_to = "AVAL"
  ) %>%
  mutate(
    PARAM = case_when(
      PARAM == "ALT" ~ "Alanine Aminotransferase",
      PARAM == "AST" ~ "Aspartate Aminotransferase",
      PARAM == "BILI" ~ "Bilirubin"
    ),
    VISIT = case_when(
      VISIT == "BL" ~ "Baseline",
      VISIT == "W2" ~ "Week 2",
      VISIT == "W4" ~ "Week 4"
    )
  )

lab_long

# 1b. Create summary table
lab_summary <- lab_long %>%
  group_by(PARAM, VISIT) %>%
  summarise(
    n = n(),
    mean_val = round(mean(AVAL), 2),
    .groups = "drop"
  )

lab_summary
```

**Key Points:**
- `pivot_longer()` converts wide data to long (tidy) format
- Use `names_sep` to split column names into multiple variables
- Long format is better for analysis and visualization
- Always clean up variable values after pivoting

------------------------------------------------------------------------

## Exercise 2: Set Up Clinical Domains (R4DS Ch. 13)
adae <- derive_vars_dt(
  dataset = adae,
  dtc = AESTDTC,
  new_vars_prefix = "A"
```{r}
# Create sample clinical datasets
dm <- tibble(
  USUBJID = c("001-001", "001-002", "001-003", "001-004", "001-005", "001-006"),
  AGE = c(45, 67, 52, 71, 34, 58),
  SEX = c("M", "F", "M", "F", "M", "F"),
  ARMCD = c("TRT", "PBO", "TRT", "TRT", "PBO", "TRT"),
  ARM = c("Treatment", "Placebo", "Treatment", "Treatment", "Placebo", "Treatment"),
  RFSTDTC = c("2024-01-15", "2024-01-16", "2024-01-17", "2024-01-18", "2024-01-19", "2024-01-20")
) %>%
  mutate(ELDERLY = ifelse(AGE >= 65, "Y", "N"))

ae <- tibble(
  USUBJID = c("001-001", "001-001", "001-002", "001-004", "001-007", "001-007"),
  AESEQ = c(1, 2, 1, 1, 1, 2),
  AEDECOD = c("HEADACHE", "NAUSEA", "FATIGUE", "DIZZINESS", "HEADACHE", "VOMITING"),
  AESEV = c("MILD", "MODERATE", "MILD", "SEVERE", "MILD", "MODERATE"),
  AESTDTC = c("2024-01-20", "2024-01-22", "2024-01-21", "2024-01-25", "2024-02-01", "2024-02-03")
)

vs <- tibble(
  USUBJID = rep(c("001-001", "001-002", "001-003", "001-004"), each = 2),
  VSTESTCD = rep(c("SYSBP", "DIABP"), 4),
  VSSTRESN = c(120, 80, 135, 85, 115, 75, 140, 90),
  VISIT = rep("Baseline", 8)
)

# Display datasets
dm
ae
vs
```

**Key Points:**
- Clinical data is naturally relational (connected by USUBJID)
- Each domain serves a specific purpose (DM=demographics, AE=adverse events, VS=vital signs)
- Note that some subjects appear in some domains but not others

------------------------------------------------------------------------

## Exercise 3: Basic Join Operations (R4DS Ch. 13)

```{r}
# 3a. LEFT JOIN: Add demographics to adverse events
ae_with_demo <- ae %>%
  left_join(dm, by = "USUBJID")

ae_with_demo

# 3b. INNER JOIN: Keep only AEs for subjects with complete demographics
ae_complete <- ae %>%
  inner_join(dm, by = "USUBJID")

ae_complete

# 3c. Count subjects in each dataset
cat("Subject counts:\n")
cat("DM subjects:", n_distinct(dm$USUBJID), "\n")
cat("AE subjects:", n_distinct(ae$USUBJID), "\n")
cat("AE with demo (left join):", n_distinct(ae_with_demo$USUBJID), "\n")
cat("AE complete (inner join):", n_distinct(ae_complete$USUBJID), "\n")
```

**Key Points:**
- `left_join()` preserves all rows from the left dataset (AE)
- `inner_join()` keeps only rows that exist in both datasets
- Subject 001-007 exists in AE but not DM, so gets NA values in left_join
- Subject 001-007 is excluded from inner_join results

------------------------------------------------------------------------

## Exercise 4: Filtering Joins (R4DS Ch. 13)

```{r}
# 4a. SEMI JOIN: Find subjects who had any adverse events
subjects_with_ae <- dm %>%
  semi_join(ae, by = "USUBJID")

subjects_with_ae

# 4b. ANTI JOIN: Find subjects who had NO adverse events
subjects_without_ae <- dm %>%
  anti_join(ae, by = "USUBJID")

subjects_without_ae
```

**Key Points:**
- `semi_join()` returns rows from dm that have matches in ae (but doesn't add ae columns)
- `anti_join()` returns rows from dm that have NO matches in ae
- These are useful for creating population flags and data validation

------------------------------------------------------------------------

## Exercise 5: Group By and Summarise Practice

```{r}
# 5a. AE summary by treatment group
ae_by_treatment <- ae_with_demo %>%
  filter(!is.na(ARMCD)) %>%  # Exclude subjects not in DM
  group_by(ARMCD) %>%
  summarise(
    n_subjects = n_distinct(USUBJID),
    n_events = n(),
    mean_age = round(mean(AGE, na.rm = TRUE), 1),
    .groups = "drop"
  )

ae_by_treatment

# 5b. AE summary by elderly status and severity
ae_by_elderly_severity <- ae_with_demo %>%
  filter(!is.na(ELDERLY)) %>%
  group_by(ELDERLY, AESEV) %>%
  summarise(
    n_events = n(),
    n_subjects = n_distinct(USUBJID),
    .groups = "drop"
  ) %>%
  arrange(ELDERLY, AESEV)

ae_by_elderly_severity
```

**Key Points:**
- Always filter out missing values before grouping
- `n_distinct()` counts unique values (subjects)
- `n()` counts total rows (events)
- Use `.groups = "drop"` to avoid warnings

------------------------------------------------------------------------

## Exercise 6: Complex Analysis Pipeline

```{r}
# Comprehensive analysis pipeline
clinical_summary <- ae %>%
  # Step 1: Join with demographics
  left_join(dm, by = "USUBJID") %>%
  # Step 2: Filter to complete cases
  filter(!is.na(ARMCD)) %>%
  # Step 3: Calculate study day and severity flag
  mutate(
    AESTDT = ymd(AESTDTC),
    RFSTDT = ymd(RFSTDTC),
    AEDY = as.numeric(AESTDT - RFSTDT) + 1,
    SEVERE_AE = ifelse(AESEV == "SEVERE", "Y", "N")
  ) %>%
  # Step 4: Group and summarise
  group_by(ARMCD, ELDERLY, SEVERE_AE) %>%
  summarise(
    n_subjects = n_distinct(USUBJID),
    n_events = n(),
    mean_onset_day = round(mean(AEDY, na.rm = TRUE), 1),
    .groups = "drop"
  ) %>%
  # Step 5: Arrange results
  arrange(ARMCD, ELDERLY, SEVERE_AE)

clinical_summary
```

**Key Points:**
- Pipelines combine multiple operations in logical sequence
- Use `ymd()` to convert character dates to Date objects
- Calculate study days as numeric difference + 1
- Complex groupings reveal detailed patterns in clinical data

------------------------------------------------------------------------

## Exercise 7: Pivot Practice with Vital Signs

```{r}
# 7a. Create summary of vital signs by treatment (long format)
vs_summary <- vs %>%
  left_join(dm %>% select(USUBJID, ARMCD), by = "USUBJID") %>%
  group_by(ARMCD, VSTESTCD) %>%
  summarise(mean_val = round(mean(VSSTRESN), 1), .groups = "drop")

vs_summary

# 7b. Pivot to wide format for reporting
vs_wide <- vs_summary %>%
  pivot_wider(
    names_from = VSTESTCD,
    values_from = mean_val
  )

vs_wide
```

**Key Points:**
- `pivot_wider()` converts long data to wide format
- Wide format is often better for clinical reports and tables
- Use `names_from` to specify column headers and `values_from` for cell values

------------------------------------------------------------------------

## Exercise 8: Missing Data Analysis

```{r}
# 8a. Identify subjects in AE but not in DM
missing_subjects <- ae %>%
  anti_join(dm, by = "USUBJID") %>%
  distinct(USUBJID)

missing_subjects

# 8b. Create data quality report
data_quality <- tibble(
  Dataset = c("DM", "AE", "VS"),
  N_Subjects = c(
    n_distinct(dm$USUBJID),
    n_distinct(ae$USUBJID),
    n_distinct(vs$USUBJID)
  ),
  N_Records = c(
    nrow(dm),
    nrow(ae),
    nrow(vs)
  )
)

data_quality
```

**Key Points:**
- Use `anti_join()` to find missing relationships between datasets
- Data quality checks are essential in clinical programming
- Always validate data completeness before analysis

------------------------------------------------------------------------

## 🎯 Learning Objectives Met

By completing these exercises, you have successfully mastered:

✅ **R4DS Chapter 12**: Applied tidy data principles with `pivot_longer()` and `pivot_wider()`
✅ **R4DS Chapter 13**: Used all types of joins for relational data analysis

### Clinical Programming Skills Gained:
- Reshaping clinical data between wide and long formats
- Joining multiple clinical domains (DM, AE, VS)
- Creating comprehensive clinical analysis pipelines
- Data quality assessment and missing data identification
- Complex grouping and summarization for clinical reporting

### Next Steps:
1. Apply these concepts to real clinical trial datasets
2. Practice with more complex multi-domain joins
3. Move to Module 4: Advanced data manipulation
4. Explore tidyr functions for complex reshaping scenarios

**You are now ready for advanced clinical data relationships and complex analyses!** 🚀
