The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

Data Cleaning and Preparation

library(clinpubr)
library(dplyr)

Introduction

Clinical data from Electronic Health Records (EHR) often contains inconsistencies, missing values, outliers, and formatting issues that must be addressed before analysis. This vignette demonstrates a structured cleaning workflow using clinpubr:

  1. Data Overview — Assess data quality
  2. Format Cleaning — Extract numbers, convert dates, check non-numeric values
  3. Unit Standardization — Harmonize measurement units
  4. Outlier Detection — Identify and manage outliers (after standardization)
  5. Missing Value Handling — Filter or handle missing data
  6. Data Transformation — Create derived variables

Why this order? Format cleaning and unit standardization must precede outlier detection — mixed units (e.g., mg/dL vs mmol/L) can cause false outlier flags.

Creating Realistic Messy Lab Data

Let’s create a sample laboratory dataset mimicking real-world EHR data with common quality issues. This dataset will be used throughout the vignette. Each patient contributes 1–4 visits, and all tests are measured on the same date within a visit, so that the wide-format cohort remains analyzable after reshaping.

set.seed(123)
n_patients <- 60

# Patient IDs and number of visits per patient (longitudinal cohort)
visits_per_patient <- sample(1:4, n_patients, replace = TRUE, prob = c(0.25, 0.4, 0.25, 0.1))
patient_ids <- rep(paste0("P", sprintf("%03d", 1:n_patients)), visits_per_patient)
n_visits <- length(patient_ids)

# Helper: generate a mixture of clinically plausible values
make_vals <- function(n, props, means, sds) {
  grp <- sample(seq_along(means), n, replace = TRUE, prob = props)
  round(rnorm(n, means[grp], sds[grp]), 2)
}

# Glucose: mix of mg/dL and mmol/L (1 mmol/L = 18 mg/dL)
glucose_units <- sample(c("mg/dL", "mmol/L", NA), n_visits,
  replace = TRUE, prob = c(0.6, 0.3, 0.1)
)
glucose_vals <- make_vals(
  n_visits, c(0.55, 0.25, 0.15, 0.05),
  c(100, 150, 230, 5.7), c(12, 18, 30, 0.9) # mg/dL values
)
mmol_glu_idx <- !is.na(glucose_units) & glucose_units == "mmol/L"
glucose_vals[mmol_glu_idx] <- make_vals(
  sum(mmol_glu_idx), c(0.55, 0.25, 0.15, 0.05),
  c(5.6, 8.3, 12.8, 0.32), c(0.7, 1, 1.7, 0.05) # mmol/L values
)

# Creatinine: mix of mg/dL and umol/L (1 mg/dL = 88.4 umol/L)
creatinine_units <- sample(c("mg/dL", "umol/L", NA), n_visits,
  replace = TRUE, prob = c(0.55, 0.35, 0.1)
)
creatinine_vals <- make_vals(
  n_visits, c(0.5, 0.25, 0.25),
  c(0.86, 1.7, 4.5), c(0.15, 0.3, 1.2) # mg/dL values
)
umol_cre_idx <- !is.na(creatinine_units) & creatinine_units == "umol/L"
creatinine_vals[umol_cre_idx] <- make_vals(
  sum(umol_cre_idx), c(0.5, 0.25, 0.25),
  c(76, 150, 400), c(13, 27, 110) # umol/L values
)

# Cholesterol: mix of mg/dL and mmol/L (1 mmol/L = 38.67 mg/dL)
cholesterol_units <- sample(c("mg/dL", "mmol/L", NA), n_visits,
  replace = TRUE, prob = c(0.6, 0.3, 0.1)
)
cholesterol_vals <- make_vals(
  n_visits, c(0.55, 0.3, 0.15),
  c(180, 230, 330), c(25, 20, 40) # mg/dL values
)
mmol_chol_idx <- !is.na(cholesterol_units) & cholesterol_units == "mmol/L"
cholesterol_vals[mmol_chol_idx] <- make_vals(
  sum(mmol_chol_idx), c(0.55, 0.3, 0.15),
  c(4.7, 6, 8.5), c(0.6, 0.5, 1) # mmol/L values
)

# A modest amount of missing values in each test
glucose_vals[sample(n_visits, round(n_visits * 0.08))] <- NA
creatinine_vals[sample(n_visits, round(n_visits * 0.08))] <- NA
cholesterol_vals[sample(n_visits, round(n_visits * 0.08))] <- NA

# Test dates: one date per visit, with various formats and invalid entries
visit_dates <- rep(as.Date(NA), n_visits)
for (p in seq_len(n_patients)) {
  p_idx <- which(patient_ids == sprintf("P%03d", p))
  visit_dates[p_idx] <- Sys.Date() - sample(1:365, length(p_idx), replace = FALSE)
}
date_strings <- ifelse(runif(n_visits) < 0.5,
  format(visit_dates, "%Y-%m-%d"), # ISO format
  format(visit_dates, "%Y/%m/%d") # Slash format
)
invalid_idx <- sample(n_visits, round(n_visits * 0.1))
date_strings[invalid_idx] <- sample(
  c("N/A", "unknown", "pending", "", NA_character_),
  length(invalid_idx), replace = TRUE
)

# Create long-format lab data: all tests are recorded at each visit
lab_data <- data.frame(
  patient_id = rep(patient_ids, each = 3),
  test = rep(c("Glucose", "Creatinine", "Cholesterol"), times = n_visits),
  value = as.character(c(rbind(glucose_vals, creatinine_vals, cholesterol_vals))),
  unit = c(rbind(glucose_units, creatinine_units, cholesterol_units)),
  test_date = rep(date_strings, each = 3)
)

# Add messy string values that need cleaning
# Glucose with text annotations and European decimal commas
messy_glucose_idx <- sample(which(lab_data$test == "Glucose"), 15)
lab_data$value[messy_glucose_idx] <- sample(c(
  "<40", ">500", "5.2", "6.8", "120 mg/dL", "89 mmol/L",
  "5.2 (fasting)", "180 (post-meal)", "N/A", "pending",
  "6..5", "7..2", "8.5.1", "normal range", "see comment"
), length(messy_glucose_idx), replace = TRUE)

# Creatinine with text annotations
messy_creatinine_idx <- sample(which(lab_data$test == "Creatinine"), 12)
lab_data$value[messy_creatinine_idx] <- sample(c(
  "<0.5", ">5.0", "1.1", "0.9", "1.2 mg/dL", "110 umol/L",
  "1.5 (dialysis)", "2.8 (critical)", "N/A", " hemolyzed",
  "1.3 ", " 1.4", "(1.2)", "1.0*", "see note"
), length(messy_creatinine_idx), replace = TRUE)

# Cholesterol with text annotations
messy_cholesterol_idx <- sample(which(lab_data$test == "Cholesterol"), 10)
lab_data$value[messy_cholesterol_idx] <- sample(c(
  "<100", ">400", "4.5", "5.2", "200 mg/dL", "5.5 mmol/L",
  "180 (fasting)", "250 (borderline)", "N/A", "lipemic"
), length(messy_cholesterol_idx), replace = TRUE)

knitr::kable(head(lab_data[sample(nrow(lab_data)), ], 10),
  caption = "Original Messy Lab Data (10 random rows)"
)
Original Messy Lab Data (10 random rows)
patient_id test value unit test_date
336 P050 Cholesterol >400 mmol/L 2025-11-26
143 P021 Creatinine 83 umol/L 2026/04/17
61 P010 Glucose 115.16 mg/dL 2026-08-08
379 P057 Glucose NA mg/dL 2026/07/02
117 P017 Cholesterol 183.67 mg/dL 2026/02/26
152 P023 Creatinine NA umol/L 2026/01/02
387 P059 Cholesterol 173.19 mg/dL
290 P043 Creatinine 2.8 (critical) mg/dL 2026-09-07
299 P044 Creatinine 1.53 mg/dL 2026-07-23
168 P024 Cholesterol 5.47 mmol/L 2025/12/20

Step 1: Data Overview

Data used: lab_data (the messy lab data created above)

Use data_overview() to get a comprehensive diagnostic report of data quality issues:

overview <- data_overview(lab_data)
#> === Data Overview Summary ===
#> Dataset: 393 rows, 5 columns
#> 
#> Variable Types:
#>   character : 5 variables
#> 
#> Found 3 potential quality issues:
#>   numeric_as_character     : 1 cases
#>   missing_values           : 2 cases
#> 
#> Recommendations:
#>   - Consider converting these character variables to numeric: value
#>   - Variables with < 50 % missing values: unit, value - consider imputation

print(overview$variable_types)
#> $character
#> [1] "patient_id" "test"       "value"      "unit"       "test_date"
print(overview$summary_stats)
#> $character
#>     variable   n missing missing_pct unique      top_value top_freq top_pct
#> 1 patient_id 393       0        0.00     60           P005       12    3.05
#> 2       test 393       0        0.00      3    Cholesterol      131   33.33
#> 3      value 365      28        7.12    334 2.8 (critical)        5    1.37
#> 4       unit 358      35        8.91      3          mg/dL      224   62.57
#> 5  test_date 390       3        0.76    113            N/A       18    4.62
print(overview$quality_issues$missing_values)
#>  unit value 
#>  8.91  7.12

Check for unit conflicts across different test types:

knitr::kable(unit_view(lab_data, subject_col = "test", value_col = "value", unit_col = "unit"),
  caption = "Unit Conflicts by Test Type"
)
Unit Conflicts by Test Type
subject unit label count nvalid mean sd median q_0.025 q_0.975
Cholesterol mg/dL NA 72 63 208.839048 49.842307 205.420 134.68100 333.28100
Cholesterol mmol/L NA 44 34 5.534706 1.143888 5.465 3.76400 7.63825
Cholesterol NA NA 15 15 218.484667 63.022379 199.740 142.48750 337.37050
Creatinine mg/dL NA 72 63 1.937619 1.584282 1.330 0.62650 5.56850
Creatinine umol/L NA 49 44 158.944318 140.062163 89.005 1.40000 491.02875
Creatinine NA NA 10 6 1.951667 1.819158 1.000 0.76500 5.01625
Glucose mg/dL NA 80 64 118.192969 56.800089 107.045 5.13625 235.12925
Glucose mmol/L NA 41 35 7.055143 3.290111 6.210 0.33800 12.92000
Glucose NA NA 10 9 131.460000 85.571461 95.030 21.29000 259.04800

Step 2: Format Cleaning

Data used: lab_data

Format cleaning is essential before any numerical analysis. Lab data often contains mixed formats, text annotations, and inconsistent date representations.

2.1 Checking for Non-numeric Values

First, identify which entries cannot be converted to numeric values:

nonnum_df <- df_view_nonnum(lab_data)
knitr::kable(head(nonnum_df, 15), caption = "Non-numeric Entries by Variable")
Non-numeric Entries by Variable
patient_id test value unit test_date
P001 Glucose 5.5 mmol/L mmol/L 2026/02/20
P002 Creatinine pending mg/dL unknown
P003 Cholesterol >500 umol/L 2026-06-16
P004 NA 180 (post-meal) NA 2025-12-17
P005 NA 200 mg/dL NA 2026/07/24
P006 NA <100 NA 2026-08-13
P007 NA 8.5.1 NA 2026-03-09
P008 NA hemolyzed NA 2026/07/26
P009 NA see note NA 2026-05-01
P010 NA 2.8 (critical) NA 2025-11-17
P011 NA 7..2 NA 2026-09-12
P012 NA N/A NA 2026-03-21
P013 NA normal range NA 2026-10-16
P014 NA >400 NA 2026/07/10
P015 NA lipemic NA 2025/12/08

2.2 Extracting Numeric Values

Use extract_num() to clean messy numeric strings. This function handles: - Text annotations (e.g., “120 mg/dL” → 120) - Range indicators (e.g., “<40”, “>500”) - Extra decimal points (e.g., “5..2” → 5.2) - Extra whitespace and punctuation

# Create a copy for cleaning
lab_data_cleaned <- lab_data

# Extract numeric values from the messy value column
lab_data_cleaned$value_numeric <- extract_num(lab_data$value)

# Show before/after comparison
comparison <- data.frame(
  test = lab_data$test,
  original = lab_data$value,
  cleaned = lab_data_cleaned$value_numeric,
  unit = lab_data$unit
) %>%
  dplyr::filter(original != cleaned | is.na(cleaned)) %>%
  head(15)

knitr::kable(comparison, caption = "Value Cleaning: Before vs After")
Value Cleaning: Before vs After
test original cleaned unit
Creatinine NA NA mg/dL
Cholesterol 5.5 mmol/L 5.5 mmol/L
Glucose pending NA mmol/L
Cholesterol NA NA mmol/L
Cholesterol NA NA mmol/L
Glucose >500 500.0 mmol/L
Glucose 180 (post-meal) 180.0 mg/dL
Cholesterol 200 mg/dL 200.0 mmol/L
Cholesterol <100 100.0 mmol/L
Glucose 8.5.1 8.5 mg/dL
Creatinine hemolyzed NA umol/L
Creatinine see note NA NA
Cholesterol NA NA mg/dL
Glucose NA NA mmol/L
Creatinine 2.8 (critical) 2.8 mg/dL

2.3 Date Conversion

Lab data often contains test dates in various formats. Use to_date() to standardize them:

# Convert various date formats to standard Date objects
lab_data_cleaned$test_date_clean <- to_date(lab_data$test_date)

# Show date conversion results
date_comparison <- data.frame(
  original = lab_data$test_date,
  cleaned = lab_data_cleaned$test_date_clean
) %>%
  dplyr::filter(!is.na(original)) %>%
  head(15)

knitr::kable(date_comparison, caption = "Date Conversion: Before vs After")
Date Conversion: Before vs After
original cleaned
2026/02/20 2026-02-20
2026/02/20 2026-02-20
2026/02/20 2026-02-20
unknown NA
unknown NA
unknown NA
2026-06-16 2026-06-16
2026-06-16 2026-06-16
2026-06-16 2026-06-16
2025-12-17 2025-12-17
2025-12-17 2025-12-17
2025-12-17 2025-12-17
2026/07/24 2026-07-24
2026/07/24 2026-07-24
2026/07/24 2026-07-24

Check for invalid dates that couldn’t be parsed:

invalid_dates <- lab_data_cleaned %>%
  dplyr::filter(!is.na(test_date) & is.na(test_date_clean)) %>%
  dplyr::select(patient_id, test, test_date) %>%
  head(10)

knitr::kable(invalid_dates, caption = "Invalid Date Entries (Could Not Be Parsed)")
Invalid Date Entries (Could Not Be Parsed)
patient_id test test_date
P001 Glucose unknown
P001 Creatinine unknown
P001 Cholesterol unknown
P005 Glucose unknown
P005 Creatinine unknown
P005 Cholesterol unknown
P008 Glucose N/A
P008 Creatinine N/A
P008 Cholesterol N/A
P010 Glucose N/A

2.4 Update Data for Next Steps

Replace the original messy columns with cleaned versions:

lab_data <- lab_data_cleaned %>%
  dplyr::mutate(
    value = value_numeric,
    test_date = test_date_clean
  ) %>%
  dplyr::select(-value_numeric, -test_date_clean)

Step 3: Unit Standardization

Data used: lab_data

Unit standardization must be performed before outlier detection. This is critical when merging data from different laboratories. We will standardize all values to conventional units:

change_rules <- list(
  list(subject = "Glucose", target_unit = "mg/dL", units2change = "mmol/L", coeffs = 18),
  list(subject = "Creatinine", target_unit = "mg/dL", units2change = "umol/L", coeffs = 1 / 88.4),
  list(subject = "Cholesterol", target_unit = "mg/dL", units2change = "mmol/L", coeffs = 38.67)
)

lab_data_std <- unit_standardize(
  lab_data,
  subject_col = "test", value_col = "value",
  unit_col = "unit", change_rules = change_rules
)

knitr::kable(head(lab_data_std, 15), caption = "Lab Data After Unit Standardization")
Lab Data After Unit Standardization
patient_id test value unit test_date
P001 Glucose 219.7800000 mg/dL 2026-02-20
P001 Cholesterol 205.4200000 mg/dL 2026-02-20
P001 Glucose 4.5700000 mg/dL NA
P001 Creatinine 0.6100000 mg/dL NA
P001 Cholesterol 212.6850000 mg/dL NA
P002 Glucose 208.3400000 mg/dL 2026-06-16
P002 Creatinine 1.5200000 mg/dL 2026-06-16
P002 Cholesterol 326.6800000 mg/dL 2026-06-16
P003 Glucose 217.4900000 mg/dL 2025-12-17
P003 Creatinine 0.7371041 mg/dL 2025-12-17
P003 Cholesterol 245.8000000 mg/dL 2025-12-17
P003 Creatinine 1.5500000 mg/dL 2026-07-24
P003 Glucose 107.6400000 mg/dL 2026-08-13
P003 Creatinine 0.8995475 mg/dL 2026-08-13
P003 Cholesterol 165.2000000 mg/dL 2026-08-13

Verify that units are now standardized:

knitr::kable(unit_view(lab_data_std, subject_col = "test", value_col = "value", unit_col = "unit"),
  caption = "Units After Standardization"
)
Units After Standardization
subject unit label count nvalid mean sd median q_0.025 q_0.975

Step 4: Outlier Detection

Data used: lab_data_std (unit-standardized lab data)

Important: Outlier detection is performed after unit standardization to ensure consistent scales across all measurements.

The package provides three methods, each with different strengths:

Detect outliers for each test type using the IQR method:

# Split data by test type for outlier detection
lab_data_clean <- lab_data_std

# Detect outliers for each test
for (test_name in c("Glucose", "Creatinine", "Cholesterol")) {
  test_data <- lab_data_std$value[lab_data_std$test == test_name]

  outlier_res <- detect_outliers(test_data, method = "iqr")

  cat("\n", test_name, ":\n")
  cat("  Total values:", length(test_data), "\n")
  cat("  Outliers detected:", sum(outlier_res$outlier_mask, na.rm = TRUE), "\n")
  cat("  Missing values:", sum(is.na(test_data)), "\n")
}
#> 
#>  Glucose :
#>   Total values: 119 
#>   Outliers detected: 4 
#>   Missing values: 0 
#> 
#>  Creatinine :
#>   Total values: 118 
#>   Outliers detected: 5 
#>   Missing values: 0 
#> 
#>  Cholesterol :
#>   Total values: 119 
#>   Outliers detected: 6 
#>   Missing values: 0

Compare outlier detection methods for Glucose:

glucose_values <- lab_data_std$value[lab_data_std$test == "Glucose"]

knitr::kable(data.frame(
  Method = c("MAD", "IQR", "Z-score"),
  Outlier_Count = c(
    sum(mad_outlier(glucose_values, threshold = 3), na.rm = TRUE),
    sum(iqr_outlier(glucose_values, threshold = 1.5), na.rm = TRUE),
    sum(zscore_outlier(glucose_values, threshold = 3), na.rm = TRUE)
  )
), caption = "Outlier Detection Results by Method (Glucose)")
Outlier Detection Results by Method (Glucose)
Method Outlier_Count
MAD 35
IQR 4
Z-score 1

Handling Outliers

For this demonstration, we will set outliers to NA. In practice, outliers should be manually verified — they may represent true extreme values (e.g., severe hyperglycemia) rather than data errors.

# Create a copy for outlier handling
lab_data_final <- lab_data_std

# Set outliers to NA for each test type
for (test_name in c("Glucose", "Creatinine", "Cholesterol")) {
  test_idx <- lab_data_std$test == test_name
  test_values <- lab_data_std$value[test_idx]

  outlier_res <- detect_outliers(test_values, method = "iqr")

  # Set outliers to NA
  lab_data_final$value[test_idx][outlier_res$outlier_mask] <- NA
}

# Compare before and after outlier handling
comparison_df <- data.frame(
  Test = rep(c("Glucose", "Creatinine", "Cholesterol"), each = 2),
  Stage = rep(c("Before", "After"), 3),
  N = c(
    sum(!is.na(lab_data_std$value[lab_data_std$test == "Glucose"])),
    sum(!is.na(lab_data_final$value[lab_data_final$test == "Glucose"])),
    sum(!is.na(lab_data_std$value[lab_data_std$test == "Creatinine"])),
    sum(!is.na(lab_data_final$value[lab_data_final$test == "Creatinine"])),
    sum(!is.na(lab_data_std$value[lab_data_std$test == "Cholesterol"])),
    sum(!is.na(lab_data_final$value[lab_data_final$test == "Cholesterol"]))
  ),
  Mean = c(
    mean(lab_data_std$value[lab_data_std$test == "Glucose"], na.rm = TRUE),
    mean(lab_data_final$value[lab_data_final$test == "Glucose"], na.rm = TRUE),
    mean(lab_data_std$value[lab_data_std$test == "Creatinine"], na.rm = TRUE),
    mean(lab_data_final$value[lab_data_final$test == "Creatinine"], na.rm = TRUE),
    mean(lab_data_std$value[lab_data_std$test == "Cholesterol"], na.rm = TRUE),
    mean(lab_data_final$value[lab_data_final$test == "Cholesterol"], na.rm = TRUE)
  )
)

knitr::kable(comparison_df, caption = "Before vs After Outlier Handling", digits = 2)
Before vs After Outlier Handling
Test Stage N Mean
Glucose Before 119 194.12
Glucose After 115 113.56
Creatinine Before 118 1.90
Creatinine After 113 1.72
Cholesterol Before 119 563.37
Cholesterol After 113 210.28

Clinical Note: Always verify outliers before removal. Some “outliers” may be clinically significant (e.g., glucose > 400 mg/dL in diabetic ketoacidosis). Consider creating an outlier indicator variable for sensitivity analyses rather than deleting values.

Step 5: Missing Value Handling

Data used: lab_data_final (after standardization and outlier handling)

For missing data filtering by row/column missing rate, use get_valid_subset() to obtain a complete-enough subset for analysis. First, let’s convert to wide format to create a longitudinal cohort dataset where each row represents a patient-test date combination:

# Convert to wide format preserving all test dates
# This creates a longitudinal cohort dataset
lab_wide <- lab_data_final %>%
  filter(!is.na(test_date)) %>% # Remove records without valid dates
  select(patient_id, test_date, test, value) %>%
  tidyr::pivot_wider(
    names_from = test,
    values_from = value,
    names_sort = TRUE
  )

# Sort by patient and date for longitudinal view
lab_wide <- lab_wide %>%
  arrange(patient_id, test_date)

test_cols <- c("Glucose", "Creatinine", "Cholesterol")

knitr::kable(head(lab_wide, 10), caption = "Longitudinal Lab Data (First 10 records)")
Longitudinal Lab Data (First 10 records)
patient_id test_date Cholesterol Creatinine Glucose
P001 2026-02-20 205.4200 NA 219.78
P002 2026-06-16 326.6800 1.5200000 208.34
P003 2025-12-17 245.8000 0.7371041 217.49
P003 2026-07-24 NA 1.5500000 NA
P003 2026-08-13 165.2000 0.8995475 107.64
P004 2026-03-09 210.7515 4.7207014 93.60
P005 2025-11-17 NA 0.7100000 97.42
P005 2026-05-01 150.7600 1.9100000 6.30
P005 2026-07-26 133.6800 0.9100000 167.58
P006 2026-03-21 199.7400 0.8200000 98.10

View the cohort structure - number of tests per patient:

cohort_summary <- lab_wide %>%
  group_by(patient_id) %>%
  summarise(
    n_visits = n(),
    first_date = min(test_date),
    last_date = max(test_date)
  ) %>%
  head(10)

knitr::kable(cohort_summary, caption = "Cohort Structure: Visits per Patient")
Cohort Structure: Visits per Patient
patient_id n_visits first_date last_date
P001 1 2026-02-20 2026-02-20
P002 1 2026-06-16 2026-06-16
P003 3 2025-12-17 2026-08-13
P004 1 2026-03-09 2026-03-09
P005 3 2025-11-17 2026-07-26
P006 2 2026-03-21 2026-09-12
P007 3 2025-12-08 2026-10-16
P009 3 2026-04-29 2026-07-03
P010 1 2026-08-08 2026-08-08
P011 4 2026-06-26 2026-08-23

Assess missing data patterns:

missing_summary <- data.frame(
  Variable = test_cols,
  Missing_Count = sapply(lab_wide[test_cols], function(x) sum(is.na(x))),
  Missing_Percent = round(sapply(lab_wide[test_cols], function(x) {
    sum(is.na(x)) / length(x) * 100
  }), 2)
)

knitr::kable(missing_summary, caption = "Missing Data Summary by Test")
Missing Data Summary by Test
Variable Missing_Count Missing_Percent
Glucose Glucose 15 12.71
Creatinine Creatinine 17 14.41
Cholesterol Cholesterol 16 13.56

Filter records with too much missing data. Only the three test-result columns are passed to get_valid_subset(), so the record identifiers and dates are always retained; they are re-attached after the valid rows and columns have been selected:

valid_idx <- get_valid_subset(
  lab_wide[test_cols],
  row_na_ratio = 0.5, # Allow up to 50% missing per record
  col_na_ratio = 0.3, # Allow up to 30% missing per test type
  return_index = TRUE
)

lab_wide_clean <- lab_wide[
  valid_idx$rows,
  c("patient_id", "test_date", test_cols[valid_idx$cols])
]

cat(
  "Dimensions:", nrow(lab_wide), "records",
  "->", nrow(lab_wide_clean), "records\n"
)
#> Dimensions: 118 records -> 112 records
cat(
  "Unique patients:", length(unique(lab_wide$patient_id)),
  "->", length(unique(lab_wide_clean$patient_id)), "\n"
)
#> Unique patients: 57 -> 56

Step 6: Data Transformation

Data used: lab_wide_clean (cleaned longitudinal cohort data)

Creating Clinical Categories

Create categorical variables from continuous lab values using clinical cut-points. In longitudinal data, each visit can have different classifications:

# Glucose categories
lab_wide_clean$glucose_category <- cut_by(
  lab_wide_clean$Glucose,
  breaks = c(100, 126),
  labels = c("Normal", "Prediabetes", "Diabetes"),
  label_with_range = FALSE
)

# Creatinine categories (eGFR approximation)
lab_wide_clean$renal_function <- cut_by(
  lab_wide_clean$Creatinine,
  breaks = c(1.2, 2.0, 4.0),
  labels = c("Normal", "Mild", "Moderate", "Severe"),
  label_with_range = FALSE
)

# Cholesterol categories
lab_wide_clean$cholesterol_category <- cut_by(
  lab_wide_clean$Cholesterol,
  breaks = c(200, 240),
  labels = c("Desirable", "Borderline", "High"),
  label_with_range = FALSE
)

# Summary of categories across all visits
cat_summary <- data.frame(
  Category = c(
    rep("Glucose", length(table(lab_wide_clean$glucose_category))),
    rep("Renal Function", length(table(lab_wide_clean$renal_function))),
    rep("Cholesterol", length(table(lab_wide_clean$cholesterol_category)))
  ),
  Level = c(
    names(table(lab_wide_clean$glucose_category)),
    names(table(lab_wide_clean$renal_function)),
    names(table(lab_wide_clean$cholesterol_category))
  ),
  Count = c(
    as.vector(table(lab_wide_clean$glucose_category)),
    as.vector(table(lab_wide_clean$renal_function)),
    as.vector(table(lab_wide_clean$cholesterol_category))
  )
)

knitr::kable(cat_summary, caption = "Clinical Category Distributions (All Visits)")
Clinical Category Distributions (All Visits)
Category Level Count
Glucose Normal 40
Glucose Prediabetes 30
Glucose Diabetes 31
Renal Function Normal 53
Renal Function Mild 15
Renal Function Moderate 20
Renal Function Severe 10
Cholesterol Desirable 46
Cholesterol Borderline 32
Cholesterol High 23

Longitudinal View Example

View a single patient’s trajectory over time:

# Select the patient with the most visits for demonstration
patient_with_most_visits <- lab_wide_clean %>%
  count(patient_id, sort = TRUE) %>%
  slice(1) %>%
  pull(patient_id)

patient_trajectory <- lab_wide_clean %>%
  filter(patient_id == patient_with_most_visits) %>%
  select(
    patient_id, test_date, Glucose, Creatinine, Cholesterol,
    glucose_category, renal_function
  ) %>%
  arrange(test_date)

knitr::kable(patient_trajectory, caption = "Example: Single Patient's Lab Trajectory")
Example: Single Patient’s Lab Trajectory
patient_id test_date Glucose Creatinine Cholesterol glucose_category renal_function
P005 2025-11-17 97.42 0.71 NA Normal Normal
P005 2026-05-01 6.30 1.91 150.76 Normal Mild
P005 2026-07-26 167.58 0.91 133.68 Diabetes Normal

Complete Cleaning Pipeline

Here’s a complete workflow from raw lab data to analysis-ready data:

# Step 1: Data Overview
overview <- data_overview(lab_data)
#> === Data Overview Summary ===
#> Dataset: 393 rows, 5 columns
#> 
#> Variable Types:
#>   numeric   : 1 variables
#>   character : 3 variables
#>   date      : 1 variables
#> 
#> Found 4 potential quality issues:
#>   outliers                 : 1 cases
#>   missing_values           : 3 cases
#> 
#> Recommendations:
#>   - Review outliers in these numeric variables: value
#>   - Variables with < 50 % missing values: test_date, value, unit - consider imputation

# Step 2: Format Cleaning - Extract numeric values and convert dates
clean <- lab_data
clean$value <- extract_num(lab_data$value)
# `lab_data` has already been cleaned above, so coerce to character before re-parsing
clean$test_date <- to_date(as.character(lab_data$test_date))

# Step 3: Unit Standardization
change_rules <- list(
  list(subject = "Glucose", target_unit = "mg/dL", units2change = "mmol/L", coeffs = 18),
  list(subject = "Creatinine", target_unit = "mg/dL", units2change = "umol/L", coeffs = 1 / 88.4),
  list(subject = "Cholesterol", target_unit = "mg/dL", units2change = "mmol/L", coeffs = 38.67)
)

clean <- unit_standardize(
  clean,
  subject_col = "test", value_col = "value",
  unit_col = "unit", change_rules = change_rules
)

# Step 4: Outlier Detection and Handling
for (test_name in c("Glucose", "Creatinine", "Cholesterol")) {
  test_idx <- clean$test == test_name
  test_values <- clean$value[test_idx]
  outlier_res <- detect_outliers(test_values, method = "iqr")
  clean$value[test_idx][outlier_res$outlier_mask] <- NA
}

# Step 5: Convert to wide format (longitudinal cohort data)
clean_wide <- clean %>%
  filter(!is.na(test_date)) %>%
  select(patient_id, test_date, test, value) %>%
  tidyr::pivot_wider(names_from = test, values_from = value) %>%
  arrange(patient_id, test_date)

# Step 6: Missing value filtering (only the test-result columns)
valid_idx <- get_valid_subset(
  clean_wide[test_cols],
  row_na_ratio = 0.5, col_na_ratio = 0.3,
  return_index = TRUE
)
clean_wide <- clean_wide[
  valid_idx$rows,
  c("patient_id", "test_date", test_cols[valid_idx$cols])
]

# Step 7: Create clinical categories
clean_wide$glucose_category <- cut_by(
  clean_wide$Glucose,
  breaks = c(100, 126),
  labels = c("Normal", "Prediabetes", "Diabetes")
)

# Step 8: Final check
final_overview <- data_overview(clean_wide)
#> === Data Overview Summary ===
#> Dataset: 112 rows, 6 columns
#> 
#> Variable Types:
#>   numeric   : 3 variables
#>   character : 2 variables
#>   date      : 1 variables
#> 
#> Found 7 potential quality issues:
#>   outliers                 : 3 cases
#>   missing_values           : 4 cases
#> 
#> Recommendations:
#>   - Review outliers in these numeric variables: Glucose, Creatinine, Cholesterol
#>   - Variables with < 50 % missing values: Creatinine, Glucose, Cholesterol, glucose_category - consider imputation
knitr::kable(final_overview$summary_stats, caption = "Final Data Quality Overview")
Final Data Quality Overview
variable n missing missing_pct mean median min max sd
Glucose 101 11 9.82 115.51 106.52 4.59 236.63 58.24
Creatinine 98 14 12.50 1.76 1.01 0.01 5.34 1.35
Cholesterol 101 11 9.82 212.12 211.14 122.58 335.70 48.55
variable n missing missing_pct unique top_value top_freq top_pct
patient_id 112 0 0.00 56 P005 3 2.68
glucose_category 101 11 9.82 3 Normal 40 39.60 

cat(
  "\nOriginal records:", nrow(lab_data),
  "| Final records:", nrow(clean_wide),
  "| Removed:", nrow(lab_data) - nrow(clean_wide), "\n"
)
#> 
#> Original records: 393 | Final records: 112 | Removed: 281
cat(
  "Unique patients:", length(unique(lab_data$patient_id)),
  "->", length(unique(clean_wide$patient_id)), "\n"
)
#> Unique patients: 60 -> 56

Summary

Step Function Purpose Data Used
1. Data Overview data_overview() Comprehensive data quality assessment Raw lab data
2. Format Cleaning extract_num(), to_date(), df_view_nonnum() Clean messy strings, convert dates Raw lab data
3. Unit Standardization unit_standardize() Standardize measurement units Format-cleaned data
4. Outlier Detection detect_outliers(), iqr_outlier() Detect and handle outliers Standardized data
5. Missing Values get_valid_subset() Filter by missing rate Post-outlier data
6. Data Transformation cut_by() Create derived variables Cleaned data

Key Principles

  1. Standardize before outlier detection: Mixed units cause false outlier flags
  2. Verify outliers clinically: Some extreme values are clinically meaningful
  3. Track data loss: Document how many observations are removed at each step
  4. Preserve raw data: Never overwrite original data; create cleaned copies

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.