Working with CSV files — R

This page explains how to load and explore your delivered CSV files using R. Examples use the tidyverse, which is pre-installed on the OUH Data Science VM.

See Understanding your data first if you haven't already — it explains the folder structure and how datasets relate to each other.

---

Downloads

• explore_data.R — R script, open in RStudio or run with Rscript explore_data.R

---

Setting your data path

library(tidyverse)

# Windows
DATA_DIR <- "C:/Users/yourname/data"
# or shared storage
DATA_DIR <- "Z:/your-project/data"

# Linux
DATA_DIR <- "/home/yourname/data"

# Note: use forward slashes even on Windows

---

Loading a dataset

Each dataset lives in its own subfolder containing one CSV file. This helper function finds and loads it:

load_dataset <- function(dataset_name) {
  folder <- file.path(DATA_DIR, dataset_name)
  csv_files <- list.files(folder, pattern = "\\.csv$", full.names = TRUE)
  if (length(csv_files) == 0) {
    stop(paste("No CSV file found in", folder))
  }
  message("Loading: ", csv_files[1])
  read_csv(csv_files[1], show_col_types = FALSE)
}

Ignore the other files

Each folder also contains _SUCCESS, _committed_... and _started_... files. These are Spark metadata files — ignore them. The helper function above handles this automatically by only loading .csv files.

---

Loading demographics

demographics <- load_dataset("demographics")

nrow(demographics)
glimpse(demographics)
head(demographics)

---

Exploring a dataset

diagnosis <- load_dataset("diagnosis")

# Basic info
nrow(diagnosis)
names(diagnosis)

# Preview
head(diagnosis)

# Check for missing values
colSums(is.na(diagnosis))

# Summary
summary(diagnosis)

---

Joining datasets

All datasets link to demographics via salted_warehouse_identifier:

# Inner join — only patients present in both tables
merged <- demographics %>%
  inner_join(diagnosis, by = "salted_warehouse_identifier")

nrow(merged)
n_distinct(merged$salted_warehouse_identifier)

# Left join — all demographics rows, with diagnosis where available
merged_left <- demographics %>%
  left_join(diagnosis, by = "salted_warehouse_identifier")

---

Working with dates

demographics <- demographics %>%
  mutate(
    BIRTH_DT_TM = as.Date(BIRTH_DT_TM),
    DECEASED_DT_TM = as.Date(DECEASED_DT_TM)
  )

# Calculate age at a reference date
reference_date <- as.Date("2024-01-01")
demographics <- demographics %>%
  mutate(age = as.numeric(reference_date - BIRTH_DT_TM) / 365.25)

summary(demographics$age)

---

Basic summary statistics

# Sex distribution
demographics %>%
  count(SEX, sort = TRUE)

# Ethnicity distribution
demographics %>%
  count(ETHNIC_GROUP, sort = TRUE)

# IMD decile distribution
demographics %>%
  count(INDEX_MULTIPLE_DEPRIVATION_DECILE_2015) %>%
  arrange(INDEX_MULTIPLE_DEPRIVATION_DECILE_2015)

# Age summary by sex
demographics %>%
  group_by(SEX) %>%
  summarise(
    n = n(),
    mean_age = mean(age, na.rm = TRUE),
    median_age = median(age, na.rm = TRUE),
    sd_age = sd(age, na.rm = TRUE)
  )

---

Listing all available datasets

folders <- list.dirs(DATA_DIR, recursive = FALSE, full.names = FALSE)
for (folder in sort(folders)) {
  csv_files <- list.files(file.path(DATA_DIR, folder), pattern = "\\.csv$")
  if (length(csv_files) > 0) {
    filepath <- file.path(DATA_DIR, folder, csv_files[1])
    size_mb <- file.size(filepath) / (1024 * 1024)
    message(sprintf("%-45s %.1f MB", folder, size_mb))
  }
}

---

Saving results

summary_table <- demographics %>%
  count(SEX)

write_csv(summary_table, file.path(DATA_DIR, "sex_summary.csv"))

When you are ready to export results out of the TRE, use the airlock. See Airlock requests and Egress and disclosure control for guidance.

---

R on Windows — download method

If package installation or downloads fail with a certificate error on Windows, run this at the start of your session:

options(download.file.method = "libcurl")

This is already set system-wide on the OUH VM, but if you have overridden it in your .Rprofile you may need to add it back.