Clinical Data Manipulation

From Raw Data to Analysis-Ready

Clinical trial data isn't like other datasets. It's structured around patients, visits, and specific measurements taken over time. Your job is to transform this raw data into a clean, organized format that’s ready for statistical analysis. This process bridges the gap between general R programming and the specific demands of clinical data science, forming the backbone of the CDISC SDTM and ADaM data pipelines.

The Tidyverse, particularly the dplyr package, provides a powerful 'grammar' for this manipulation. We'll focus on applying these tools to challenges unique to clinical data, like tracking patient-level changes and handling data collected at different times.

Grouping and Merging

In clinical trials, the fundamental unit is the patient, identified by a unique subject ID, or USUBJID. Nearly every calculation happens at the patient level. The group_by() function is your primary tool for this. By grouping a dataset by USUBJID, you tell R to perform subsequent operations—like summaries or transformations—independently for each person in the trial.

# Load the necessary library
library(dplyr)

# Group vital signs data by patient and find the max heart rate for each
vitals_data %>%
  group_by(USUBJID) %>%
  summarise(MAX_HR = max(HR_PULSE, na.rm = TRUE))

Data rarely comes in a single file. You'll often have a demographics file (dm), a lab results file (lb), and an exposure file (ex), among others. To connect them, you use a 'mutating join'. A left_join() is the most common choice. It takes two data frames and merges them based on a shared key, like USUBJID, adding columns from the right-hand data frame to the left.

Deriving Key Variables

Simply combining data isn't enough. We need to derive new variables that measure a treatment's effect. One of the most common is 'Change from Baseline' (CHG). This requires us to first identify the 'baseline'—the last measurement taken before a patient starts treatment. In standardized datasets, a baseline flag (ABLFL == 'Y') often marks this record.

CHG = \text{AVAL} - \text{BASE}

To calculate CHG for every visit, you need to make the baseline value available on every row for a given patient. This is a perfect use case for a left_join. You first create a small data frame containing only the baseline value for each patient, then join it back to the full dataset.

# 1. Create a data frame with only baseline values
baseline_df <- lab_data %>%
  filter(ABLFL == 'Y') %>%
  select(USUBJID, BASE = AVAL) # Rename AVAL to BASE for clarity

# 2. Join baseline value back to the full lab dataset
lab_data_with_base <- lab_data %>%
  left_join(baseline_df, by = 'USUBJID')

# 3. Calculate CHG (Change from Baseline)
final_lab_data <- lab_data_with_base %>%
  mutate(CHG = AVAL - BASE)

A similar variable is 'Percent Change from Baseline' (PCHG), which standardizes the change relative to the starting point. This is particularly useful when the baseline values vary widely among patients.

\text{PCHG} = \frac{(\text{AVAL} - \text{BASE})}{\text{BASE}} \times 100

Tidying and Handling Issues

Clinical data doesn't always arrive in a 'tidy' format. A common problem is 'wide' data, where measurements for different tests or time points are stored in separate columns. For example, you might have columns for WBC_C1D1, WBC_C2D1, etc. Tidy data principles state that these should be in a single WBC column, with another column specifying the VISIT.

The pivot_longer() function from the tidyr package is designed to solve exactly this problem. It takes multiple columns and collapses them into two: one for the names of the original columns (the 'key') and one for their values.

# `wide_data` has columns USUBJID, HR_VISIT1, HR_VISIT2

tidy_data <- wide_data %>%
  pivot_longer(
    cols = c(HR_VISIT1, HR_VISIT2), 
    names_to = 'VISIT', 
    values_to = 'HEART_RATE'
  )

Finally, you'll encounter missing data. In clinical trials, a patient might miss a visit or a lab sample might be lost. Historically, a method called Last Observation Carried Forward (LOCF) was used, where a missing value was filled with the patient's previously recorded value. This approach is now known to introduce bias.

Modern analyses prefer more sophisticated methods like Multiple Imputation (MI), which creates several plausible replacements for the missing data and pools the results. While the implementation of MI is a complex statistical topic, the key takeaway for data manipulation is to preserve missingness. Do not arbitrarily fill in missing values with 0 or the mean. Your job is to clean and structure the data as it was recorded, leaving explicit handling of missing data to the statistical modeling phase.

Quiz Questions 1/5

In the context of clinical trial data analysis using dplyr, what is the primary purpose of the group_by(USUBJID) function?

Quiz Questions 2/5

You need to calculate CHG (Change from Baseline) for a lab value. The standard approach involves creating a separate dataframe with only baseline values and then joining it back to the main lab dataset. Which join function is most appropriate for this task?

By mastering these Tidyverse patterns, you can build robust, traceable, and efficient workflows to prepare any clinical dataset for analysis.

R for Clinical Research and Drug Development