Retrospective clinical data harmonisation Reporting Using R and Quarto
14th November 2025
whoami
Research Officer from National Heart Centre Singapore who collects, cleans and harmonises clinical data.
Taming the Data Beast from “Cleaning Medical Data with R” workshop by Shannon Pileggi, Crystal Lewis and Peter Higgins presented at R/Medicine 2023. Illustrated by Allison Horst.
About Data Harmonisation
Data harmonisation is part of data wrangling process where
- Similar variables from different datasets are identified.
- Grouped based on a generalised concept they represent.
- Transformed into unified harmonised variables for analysis.
Image from Mallya et al. Circ Cardiovasc Qual Outcomes. 2023 Nov; 16(11):e009938 doi: 10.1161/CIRCOUTCOMES.123.009938.
How it started
Cheerful Businessman designed by Iftikhar Alam from Vecteezy and Medical Doctor Man from Creazilla.
How it started
Cheerful Businessman designed by Iftikhar Alam from Vecteezy and Medical Doctor Man from Creazilla.
How it started
Cheerful Businessman designed by Iftikhar Alam from Vecteezy and Medical Doctor Man from Creazilla.
How it started
snapshot from Ready for QA | MonkeyUser 2SP Animation Video from MonkeyUser.com.
How it started
snapshot from Ready for QA | MonkeyUser 2SP Animation Video from MonkeyUser.com.
Why a harmonisation report
Cheerful Businessman designed by Iftikhar Alam from Vecteezy and Medical Doctor Man from Creazilla.
Why a harmonisation report
Some data fields just cannot be planned in advanced.
Issues
While there are 📦 to facilitate data harmonisation,
retroharmonizefor survey data.Rmonizefor epidemiological data.psHarmonizefor health and education data.
There are limited resources on how to make a data harmonisation report.
Tools to create documentation
R Programming Logo from CleanPNG and Quarto Hex Sticker from Posit.
What Did We Forget
to Teach You about ?
Image from Project Oriented Workflows slides from What They Forgot to Teach You About R.
We will share a glimpse of “Everything else” R and its friends can do.
Session
Before starts up in a given project, it will perform the following steps
- Set up Environment Variables.
- Run startup script.
- Set up the session.
We can customise Step 1 and 2 using these two main text files.
.Renviron(Contains environment variables to be set in sessions.).Rprofile(Contains code to be run in each session.)
States of 📦
📦 – Structure
Binary 📦 Installation
Go for ( | ) binary 📦 in CRAN.
- compiled ahead of time -> easiest / fastest to install
R-universe and Posit Public Package Manager
Consider installing ( | | ) binaries from
Set in your .Rprofile file.
Source 📦 Installation
Go for source 📦 if you need the latest version urgently.
only have source 📦 in CRAN.
Project Organisation
Organise your project as you go instead of waiting for “tomorrow”.
Data is cheap but time is expensive.
Research Compendium by Scriberia from The Turing Way project and Project Layout from Good enough practices in scientific computing.
Project Organisation
My harmoniation template organisation is based on the 📦 rcompendium but there are others (orderly, prodigenr and workflowr) as well.
Start Session in a “blank slate”
❌ Avoid rm(list = ls())
Slide from Project oriented workflows.
✅ Use usethis::use_blank_slate()
R console
usethis::use_blank_slate()✅ Tools -> Global Options in RStudio.
Quarto
Quarto is an open-source software that weaves narrative and programming code together to produce elegantly formatted output as documents (in HTML, Word, PDF), presentations, books, web pages, and more.
Artwork from “Hello, Quarto” keynote by Julia Lowndes and Mine Çetinkaya-Rundel, presented at RStudio Conference 2022. Illustrated by Allison Horst.
Open A Quarto Document
Create a Quarto document
Render to HTML Report
Quarto Level 1
A Quarto file is a plain text file that has the extension .qmd containing three important types of content:
Simple Quarto file example from R for Data Science (2e) Chapter 28 Quarto
Workflow with collaborators
Collaborator can send the raw data once and you keep updating the cleaned data for harmonsation.
Dataset vectors by Vectora Artworks from Vecteezy.
Workflow with collaborators
Cheerful Businessman designed by Iftikhar Alam from Vecteezy and Medical Doctor Man from Creazilla.
Workflow with collaborators
Cheerful Businessman designed by Iftikhar Alam from Vecteezy and Medical Doctor Man from Creazilla.
Workflow with collaborators
Collaborator can update the raw data. For example, adding new clinical data, add more patients, correct errors.
Dataset vectors by Vectora Artworks from Vecteezy.
Workflow with collaborators
New version means new bugs or reopen issues to fix. Is there an automated way to catch warnings/issues when reading these updated files ?
Dataset vectors by Vectora Artworks from Vecteezy. Anticipate from MonkeyUser.com
Automated capture of warnings (csv)
Is there an automated way to catch warnings/issues when reading csv files ?
Automated capture of warnings (csv)
Here is a case with no issues.
# A tibble: 0 × 5
# ℹ 5 variables: row <int>, col <int>, expected <chr>, actual <chr>, file <chr>
Automated capture of warnings (Excel)
Is there an automated way to catch warnings/issues when reading Excel files ?
Automated capture of warnings (Excel)
We can read the Excel file with testthat::expect_no_condition.
Error: Expected `... <- NULL` to run without any conditions.
ℹ Actually got a <simpleWarning> with text:
Expecting numeric in B7 / R7C2: got 'missing'
Automated capture of warnings (Excel)
However, this method means that you will lose the pipe workflow.
testthat::expect_no_condition(
cohort_data_excel <- readxl::read_excel(
path = here::here("data-raw", "Cohort_Excel",
"data_to_harmonise_age_issue_fixed.xlsx"),
sheet = "Sheet1",
col_types = c("text", "numeric")
)
)
cohort_data_excel <- cohort_data_excel |>
# Check if Serial Number is unique
pointblank::rows_distinct(
columns = "Serial Number",
)
Variable Mapping
Variable Mapping
Let the reader know how the collaborator’s data Smoke History is going to be mapped.
Variable Mapping
smoking_data <- cohort_csv_data |>
dplyr::select(c("cohort_unique_id",
"Smoke History")) |>
dplyr::mutate(
smoke_current = dplyr::case_when(
is.na(.data[["Smoke History"]]) ~ "-1",
.data[["Smoke History"]] == "non-smoker" ~ "0",
.data[["Smoke History"]] == "past smoker" ~ "0",
.data[["Smoke History"]] == "current smoker" ~ "1",
.default = NA_character_
),
smoke_current = forcats::fct_relevel(
.data[["smoke_current"]],
c("0", "1")),
smoke_past = dplyr::case_when(
is.na(.data[["Smoke History"]]) ~ "-1",
.data[["Smoke History"]] == "non-smoker" ~ "0",
.data[["Smoke History"]] == "past smoker" ~ "1",
.data[["Smoke History"]] == "current smoker" ~ "0",
.default = NA_character_
),
smoke_past = forcats::fct_relevel(
.data[["smoke_past"]],
c("0", "1")),
`Smoke History` = forcats::fct(
.data[["Smoke History"]]
)
)
Variable Mapping
smoking_data <- smoking_data |>
pointblank::col_vals_in_set(
columns = c("smoke_current", "smoke_past"),
set = c("0", "1", "-1")
) |>
pointblank::col_vals_expr(
expr = pointblank::expr(
(.data[["smoke_current"]] == "1" & .data[["smoke_past"]] == "0") |
(.data[["smoke_current"]] == "-1" & .data[["smoke_past"]] == -"1") |
(.data[["smoke_current"]] == "0" & .data[["smoke_past"]] %in% c("0", "1"))
)
)
Variable Mapping
Make use of Quarto’s parameters, conditional content and !expr knitr engine syntax to choose what code/items to run/display on your html, pdf or word report.
Variable Mapping
Html Output
Variable Mapping
Pdf Output
Variable Mapping
Comparing Datasets
Use 📦 daff to compare different version of harmonised datasets.
Daff Comparison: 'data1' vs. 'data2'
B:A A:B C:-
! : ---
@@ col1 Name col2
1:1 1 P1 11
2:2 -> 2->3 P2 13
-:3 +++ 3 P6 <NA>
3:- --- 3 P3 14
4:4 -> 4->6 P4 15
5:5 -> 5->9 P5 17 
Technical Report Challenge
One variable mapping report takes at least one page.
On average, a clinical trial will have a few hundred variables.
- One hundred columns for clinical and demographics.
- Two hundred columns for medication.
Harmonisation report can have at least a few hundreds pages for each cohort.
There is a need to automate the creation of these reports.
Businessman in pile of documents asking for help by Amonrat Rungreangfangsai
Quarto Level 3
To make a Quarto book or website, we need a _quarto.yml and index.qmd file
Harmonisation Report Types
Collaborator wants different ways to report how data harmonisation is done.
The documentation system by Divio
Harmonisation Report Types
Collaborator wants different ways to report how data harmonisation is done.
Automated Technical Report (Reference)
We create a _quarto.yml file and relevant Quarto files for each cohort.
Automated Technical Report (Reference)
We create an index.qmd file for each kind of report.
Automated Technical Report (Reference)
Create a script to generate technical reports in pdf, word and html for each cohort.
Automated Technical Report (Reference)
Create a script to generate technical reports in pdf, word and html for each cohort.
copy_and_render <- function(
cohort
) {
# Copy quarto.yml file
# for each cohort
quarto_yml_file <- paste0(
"_quarto_",
cohort,
".yml"
)
fs::file_copy(
path = here::here(
"templates",
"quarto-yaml",
quarto_yml_file),
new_path = here::here("_quarto.yml"),
overwrite = TRUE
)
# Render each cohort
quarto::quarto_render(
as_job = FALSE
)
}
cohort_name <- c("Cohort_A",
"Cohort_B")
purrr::walk(
.x = cohort_name,
.f = ~copy_and_render(
cohort = .x
)
)
Automated Technical Report (Reference)
Automated Summary Report (How-to-Guide)
A similar method is done to create a summary report in word using 📦 flextable.
Overview Diagrams
How many variables can each cohort provide ?
How many variables can be harmonised ?
demographic_list <- list(
A = c("Age", "Sex",
"Hypertension", "Dyslipidemia", "Family Hx CAD", "Diabetes",
"Smoke Current", "Smoke Past",
"Have Chest Pain", "Chest Pain Character",
"Dyspnea",
"BMI", "Height", "Weight"),
B = c("Age", "Sex",
"Hypertension", "Dyslipidemia", "Family Hx CAD", "Diabetes",
"Smoke Current", "Smoke Past",
"Have Chest Pain", "Chest Pain Character",
"Dyspnea",
"HDL", "Total Cholesterol",
"Triglyceride", "LDL"),
C = c("Age", "Sex",
"Hypertension", "Dyslipidemia", "Family Hx CAD", "Diabetes",
"Smoke Current", "Smoke Past",
"Have Chest Pain", "Chest Pain Character",
"Dyspnea",
"BMI", "Height", "Weight",
"HDL", "Total Cholesterol",
"Triglyceride", "LDL")
)
cohort_a_label_name <- "Cohort A\n(n=1000)"
cohort_b_label_name <- "Cohort B\n(n=2000)"
cohort_c_label_name <- "Cohort C\n(n=1500)"
demographic_venn_data <- demographic_list |>
ggVennDiagram::Venn() |>
ggVennDiagram::process_data()
demographic_venn_regionedge_data <- demographic_venn_data |>
ggVennDiagram::venn_regionedge() |>
dplyr::mutate(
name = stringr::str_replace_all(
.data[["name"]],
"/",
" and "
),
name = stringr::str_wrap(.data[["name"]], width = 10),
name = forcats::fct_reorder(.data[["name"]],
nchar(.data[["id"]]))
) |>
dplyr::rename(`Cohort` = "name")
demographic_venn_label_data <- demographic_venn_data |>
ggVennDiagram::venn_setlabel() |>
dplyr::mutate(
name = dplyr::case_match(
.data[["name"]],
"A" ~ cohort_a_label_name,
"B" ~ cohort_b_label_name,
"C" ~ cohort_c_label_name
)
) |>
dplyr::rename(`Cohort` = "name")
demographic_venn_regionlabel_data <- demographic_venn_data |>
ggVennDiagram::venn_regionlabel() |>
dplyr::mutate(
name = stringr::str_replace_all(
.data[["name"]],
"/",
" and "
),
name = stringr::str_wrap(.data[["name"]], width = 10),
name = forcats::fct_reorder(.data[["name"]],
nchar(.data[["id"]]))
) |>
dplyr::rename(`Cohort` = "name")
demographic_venn_edge_data <- demographic_venn_data |>
ggVennDiagram::venn_setedge()
demographic_venn_diagram <- ggplot2::ggplot() +
# 1. region count layer
ggplot2::geom_polygon(
data = demographic_venn_regionedge_data,
mapping = ggplot2::aes(
x = .data[["X"]], y = .data[["Y"]],
fill = .data[["Cohort"]],
group = .data[["id"]])
) +
# 2. set edge layer
ggplot2::geom_path(
data = demographic_venn_edge_data,
mapping = ggplot2::aes(
x = .data[["X"]], y = .data[["Y"]],
colour = "black",
group = .data[["id"]]
),
show.legend = FALSE
) +
# 3. set label layer
ggplot2::geom_text(
data = demographic_venn_label_data,
mapping = ggplot2::aes(
x = .data[["X"]], y = .data[["Y"]],
label = .data[["Cohort"]]),
size = 5.5
) +
# 4. region label layer
ggplot2::geom_label(
data = demographic_venn_regionlabel_data,
mapping = ggplot2::aes(
x = .data[["X"]], y = .data[["Y"]],
label = .data[["count"]]),
size = 7,
fill = "white"
) +
ggplot2::scale_x_continuous(
expand = ggplot2::expansion(mult = 0.2)
) +
ggplot2::theme_void() +
ggplot2::theme(
text = ggplot2::element_text(size = 20)
)
Venn diagram does not work for many (> 10) cohorts.
Overview Diagrams
Upset plots are too complicated for clinicians.
demographic_venn <- tibble::tibble(
column_name = c("Age", "Sex",
"Hypertension", "Dyslipidemia", "Family Hx CAD", "Diabetes",
"Smoke Current", "Smoke Past",
"Have Chest Pain", "Chest Pain Character",
"Dyspnea",
"BMI", "Height", "Weight",
"HDL", "Total Cholesterol",
"Triglyceride", "LDL"),
`Cohort A` = c(1, 1,
1, 1, 1, 1,
1, 1,
1, 1,
1,
1, 1, 1,
0, 0,
0, 0),
`Cohort B` = c(1, 1,
1, 1, 1, 1,
1, 1,
1, 1,
1,
0, 0, 0,
1, 1,
1, 1),
`Cohort C` = c(1, 1,
1, 1, 1, 1,
1, 1,
1, 1,
1,
1, 1, 1,
1, 1,
1, 1),
`Cohort D` = c(1, 1,
1, 1, 1, 1,
1, 1,
1, 1,
0,
1, 0, 0,
1, 1,
1, 1),
`Cohort E` = c(1, 1,
1, 1, 1, 1,
1, 1,
1, 1,
0,
1, 1, 1,
1, 1,
0, 0),
`Cohort F` = c(1, 1,
1, 1, 1, 1,
1, 1,
1, 1,
0,
1, 1, 1,
0, 0,
0, 0),
)
cohort_a_upset_col_name <- "Cohort A\n(n=1000)"
cohort_b_upset_col_name <- "Cohort B\n(n=2000)"
cohort_c_upset_col_name <- "Cohort C\n(n=1500)"
cohort_d_upset_col_name <- "Cohort D\n(n=500)"
cohort_e_upset_col_name <- "Cohort E\n(n=1000)"
cohort_f_upset_col_name <- "Cohort F\n(n=2500)"
demographic_upset_data <- demographic_venn |>
dplyr::rename(
!!cohort_a_upset_col_name := "Cohort A",
!!cohort_b_upset_col_name := "Cohort B",
!!cohort_c_upset_col_name := "Cohort C",
!!cohort_d_upset_col_name := "Cohort D",
!!cohort_e_upset_col_name := "Cohort E",
!!cohort_f_upset_col_name := "Cohort F"
)
upset_plot <- ComplexUpset::upset(
demographic_upset_data,
c(cohort_a_upset_col_name,
cohort_b_upset_col_name,
cohort_c_upset_col_name,
cohort_d_upset_col_name,
cohort_e_upset_col_name,
cohort_f_upset_col_name),
base_annotations = list(
`Intersection size` =
ComplexUpset::intersection_size(
text = list(size = 9, nudge_y = 0.5),
text_colors = c(on_background='black',
on_bar='white')
) +
ggplot2::annotate(
size = 5.5,
geom = 'text',
x = Inf,
y = Inf,
label = paste('Total:', nrow(demographic_venn)),
vjust = 1,
hjust = 1
) +
ggplot2::theme(
axis.title.y = ggplot2::element_text(angle = 0, size = 20),
axis.title.x = ggplot2::element_text(angle = 0, size = 20),
text = ggplot2::element_text(size = 20)
) +
ggplot2::labs(y = "",
title = "Intersection size")
),
set_sizes = (
ComplexUpset::upset_set_size() +
ggplot2::geom_text(
size = 5.5,
mapping = ggplot2::aes(label= ggplot2::after_stat(.data[["count"]])),
hjust = 1.5,
stat = 'count') +
ggplot2::expand_limits(y = 30) +
ggplot2::theme(
axis.title.y = ggplot2::element_text(angle = 0, size = 15),
axis.title.x = ggplot2::element_text(angle = 0, size = 15),
text = ggplot2::element_text(size = 15)
) +
ggplot2::labs(y = "Number of variables")
),
sort_intersections_by = "degree",
sort_sets = FALSE,
name = "",
width_ratio = 0.25,
themes = ComplexUpset::upset_default_themes(
text = ggplot2::element_text(size = 15)
)
)
Cannot answer follow-up questions:
How many cohorts provide patient’s blood lipid information and how many patients have them ?
Overview Diagrams
Create a “heatmap”using Microsoft PowerPoint.
Summary
Summary
Thank you
Harmonisation project template: https://github.com/JauntyJJS/harmonisation/
