Data Cleaning and Preparation

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:

Glucose: mg/dL (mmol/L × 18 = mg/dL)
Creatinine: mg/dL (umol/L ÷ 88.4 = mg/dL)
Cholesterol: mg/dL (mmol/L × 38.67 = mg/dL)

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:

MAD (Median Absolute Deviation): Robust to outliers, good for small samples
IQR (Interquartile Range): Most commonly used in clinical research
Z-score: Assumes normal distribution, use with caution for skewed data

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

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