library(readxl)
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(janitor)##
## Attaching package: 'janitor'## The following objects are masked from 'package:stats':
##
## chisq.test, fisher.testfile_path <- "Clinical data for Proteomics data_original.xlsx"
clinical_raw <- read_excel(file_path, sheet = "Sheet3") %>%
clean_names()
dim(clinical_raw)## [1] 233 49The accompanying feature dictionary (Sheet2) lists 60
candidate clinical features. The actual dataset (Sheet3)
contains 49 columns. Comparing the two directly: 2 columns present in
the data are identifiers not covered by the dictionary
(Patient.ID, Linked ID), and 13 features
listed in the dictionary were not present in this extract, predominantly
the HIV sub-panel (CD4%, CD4 count, viral load, CD45+ count), tumour
markers (PSA, CEA), pancreatic enzymes (amylase, lipase), and two
additional infectious disease screens (Hepatitis A, syphilis). Core
panels: full blood count, blood chemistry, and liver function tests are
complete. This is consistent with these being optional/specialised tests
not administered to every patient in the study, rather than a
data-loading error; the count (60 − 13 + 2 = 49) reconciles exactly with
dim(clinical_raw).
clinical_raw %>% tabyl(pathology)## pathology n percent
## BBP 83 0.35622318
## CP 6 0.02575107
## HC 42 0.18025751
## LAPC 13 0.05579399
## MPC 10 0.04291845
## RPC 79 0.33905579No whitespace-related duplicate categories were found -
readxl::read_excel() trims whitespace from character
columns by default (trim_ws = TRUE), which already resolved
a spacing inconsistency present in the raw Excel file
("RPC " vs "RPC", "LAPC " vs
"LAPC") before this check ran.
missing_summary <- clinical_raw %>%
summarise(across(everything(), ~ sum(is.na(.)))) %>%
tidyr::pivot_longer(everything(), names_to = "variable", values_to = "n_missing") %>%
mutate(pct_missing = round(100 * n_missing / nrow(clinical_raw), 1)) %>%
arrange(desc(n_missing))
missing_summary## # A tibble: 49 × 3
## variable n_missing pct_missing
## <chr> <int> <dbl>
## 1 date_of_death 197 84.5
## 2 followup 195 83.7
## 3 hiv_status 193 82.8
## 4 smoking 193 82.8
## 5 drinker 193 82.8
## 6 employment_status 193 82.8
## 7 date_of_last_visit 193 82.8
## 8 hepatitis_b 191 82
## 9 hepatitis_c 191 82
## 10 dead_alive 175 75.1
## # ℹ 39 more rowsclinical_raw %>%
select(patient_id, hiv_status, smoking, drinker, employment_status, date_of_last_visit) %>%
mutate(all_present = if_all(-patient_id, ~ !is.na(.))) %>%
count(all_present)## # A tibble: 2 × 2
## all_present n
## <lgl> <int>
## 1 FALSE 193
## 2 TRUE 40class(clinical_raw$date_of_test)## [1] "character"clinical_raw %>% count(is.na(date_of_test))## # A tibble: 2 × 2
## `is.na(date_of_test)` n
## <lgl> <int>
## 1 FALSE 170
## 2 TRUE 63missing_summary
clinical_raw %>%
filter(!is.na(date_of_test)) %>%
distinct(date_of_test) %>%
slice_head(n = 15)## # A tibble: 15 × 1
## date_of_test
## <chr>
## 1 43593
## 2 43609
## 3 43655
## 4 43683
## 5 43626
## 6 43634
## 7 43642
## 8 43705
## 9 43662
## 10 43671
## 11 43711
## 12 43714
## 13 43742
## 14 43760
## 15 43851clinical_clean <- clinical_raw %>%
mutate(
date_of_test_parsed = case_when(
grepl("^[0-9]+$", date_of_test) ~ janitor::excel_numeric_to_date(as.numeric(date_of_test)),
TRUE ~ lubridate::dmy(date_of_test)
)
)## Warning: There were 2 warnings in `mutate()`.
## The first warning was:
## ℹ In argument: `date_of_test_parsed = case_when(...)`.
## Caused by warning in `janitor::excel_numeric_to_date()`:
## ! NAs introduced by coercion
## ℹ Run `dplyr::last_dplyr_warnings()` to see the 1 remaining warning.# sanity check: did every non-missing original value get a real date?
clinical_clean %>%
filter(!is.na(date_of_test), is.na(date_of_test_parsed)) %>%
select(patient_id, date_of_test, date_of_test_parsed)## # A tibble: 0 × 3
## # ℹ 3 variables: patient_id <chr>, date_of_test <chr>,
## # date_of_test_parsed <date>saveRDS(clinical_clean, "clinical_clean.rds")
list.files(pattern = ".rds")## [1] "clinical_clean.rds" "demographics_age_summary.rds"
## [3] "demographics_gender_summary.rds" "harmonized_metabolomics.rds"
## [5] "harmonized_multiomics_dataset.rds" "harmonized_proteomics.rds"
## [7] "matched_cohort_ids.rds" "metabolomics_clean.rds"
## [9] "pathology_mismatches.rds" "proteomics_clean.rds"
## [11] "table1_clinical_parameters.rds"clinical_raw %>% tabyl(bilirubin_index)## bilirubin_index n percent valid_percent
## 1+ 33 0.14163090 0.3707865
## 2+ 21 0.09012876 0.2359551
## 3+ 10 0.04291845 0.1123596
## none 11 0.04721030 0.1235955
## trace 14 0.06008584 0.1573034
## <NA> 144 0.61802575 NAclinical_clean <- clinical_clean %>%
mutate(bilirubin_index_clean = trimws(as.character(bilirubin_index)))
# flag any value that isn't a recognised grade
clinical_clean %>%
filter(!bilirubin_index_clean %in% c("none", "trace", "1+", "2+", "3+", NA)) %>%
select(patient_id, bilirubin_index_clean)## # A tibble: 0 × 2
## # ℹ 2 variables: patient_id <chr>, bilirubin_index_clean <chr>clinical_raw %>% count(followup == "#VALUE!")## # A tibble: 2 × 2
## `followup == "#VALUE!"` n
## <lgl> <int>
## 1 FALSE 38
## 2 NA 195clinical_raw %>%
filter(patient_id %in% c("CHBDK167", "CHBDK174")) %>% # placeholder check, see below
select(patient_id, followup)## # A tibble: 2 × 2
## patient_id followup
## <chr> <dbl>
## 1 CHBDK167 NA
## 2 CHBDK174 NAclinical_clean <- clinical_clean %>%
mutate(followup_missing_reason = case_when(
patient_id %in% c("RPC 22", "CP 1") ~ "Excel formula error (#VALUE!) in source file",
is.na(followup) ~ "Not part of follow-up sub-study",
TRUE ~ NA_character_
))
clinical_clean %>% count(followup_missing_reason)## # A tibble: 3 × 2
## followup_missing_reason n
## <chr> <int>
## 1 Excel formula error (#VALUE!) in source file 2
## 2 Not part of follow-up sub-study 193
## 3 <NA> 38class(clinical_raw$date_of_death)## [1] "character"class(clinical_raw$date_of_last_visit)## [1] "character"clinical_raw %>%
filter(!is.na(date_of_death)) %>%
distinct(date_of_death) %>%
slice_head(n = 5)## # A tibble: 5 × 1
## date_of_death
## <chr>
## 1 43709
## 2 44039
## 3 43656
## 4 43877
## 5 43754clinical_raw %>%
filter(!is.na(date_of_last_visit)) %>%
distinct(date_of_last_visit) %>%
slice_head(n = 5)## # A tibble: 5 × 1
## date_of_last_visit
## <chr>
## 1 43488
## 2 44013
## 3 43837
## 4 43655
## 5 43650clinical_clean <- clinical_clean %>%
mutate(
date_of_death_parsed = case_when(
grepl("^[0-9]+$", date_of_death) ~ janitor::excel_numeric_to_date(as.numeric(date_of_death)),
TRUE ~ lubridate::dmy(date_of_death)
),
date_of_last_visit_parsed = case_when(
grepl("^[0-9]+$", date_of_last_visit) ~ janitor::excel_numeric_to_date(as.numeric(date_of_last_visit)),
TRUE ~ lubridate::dmy(date_of_last_visit)
)
)## Warning: There were 4 warnings in `mutate()`.
## The first warning was:
## ℹ In argument: `date_of_death_parsed = case_when(...)`.
## Caused by warning in `janitor::excel_numeric_to_date()`:
## ! NAs introduced by coercion
## ℹ Run `dplyr::last_dplyr_warnings()` to see the 3 remaining warnings.# sanity check both columns again
clinical_clean %>%
filter(!is.na(date_of_death), is.na(date_of_death_parsed)) %>%
select(patient_id, date_of_death, date_of_death_parsed)## # A tibble: 0 × 3
## # ℹ 3 variables: patient_id <chr>, date_of_death <chr>,
## # date_of_death_parsed <date>clinical_clean %>%
filter(!is.na(date_of_last_visit), is.na(date_of_last_visit_parsed)) %>%
select(patient_id, date_of_last_visit, date_of_last_visit_parsed)## # A tibble: 0 × 3
## # ℹ 3 variables: patient_id <chr>, date_of_last_visit <chr>,
## # date_of_last_visit_parsed <date>saveRDS(clinical_clean, "clinical_clean.rds")
list.files(pattern = ".rds")## [1] "clinical_clean.rds" "demographics_age_summary.rds"
## [3] "demographics_gender_summary.rds" "harmonized_metabolomics.rds"
## [5] "harmonized_multiomics_dataset.rds" "harmonized_proteomics.rds"
## [7] "matched_cohort_ids.rds" "metabolomics_clean.rds"
## [9] "pathology_mismatches.rds" "proteomics_clean.rds"
## [11] "table1_clinical_parameters.rds"clinical_clean <- clinical_clean %>%
mutate(date_of_test_parsed = if_else(
patient_id == "CHBDK174",
as.Date("2022-09-30"),
date_of_test_parsed
))
# confirm the fix applied correctly
clinical_clean %>%
filter(patient_id == "CHBDK174") %>%
select(patient_id, date_of_test, date_of_test_parsed)## # A tibble: 1 × 3
## patient_id date_of_test date_of_test_parsed
## <chr> <chr> <date>
## 1 CHBDK174 30/09/2022 2022-09-30