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, union
library(stringr)

clinical_clean <- readRDS("clinical_clean.rds")
proteomics_clean <- readRDS("proteomics_clean.rds")
metabolomics_clean <- readRDS("metabolomics_clean.rds")

dim(clinical_clean)
## [1] 233  54
dim(proteomics_clean)
## [1]  175 1223
dim(metabolomics_clean)
## [1] 81 39
batch_info <- readxl::read_excel("NMR_Metabolomics_Data__main_.xlsx", sheet = "Sheet2") %>%
  janitor::clean_names()

metabolomics_clean <- metabolomics_clean %>%
  left_join(batch_info %>% select(nmr_id, id, batch), by = "nmr_id")

metabolomics_clean %>% select(nmr_id, id, batch, group) %>% slice_head(n = 5)
## # A tibble: 5 × 4
##   nmr_id          id    batch   group
##   <chr>           <chr> <chr>   <chr>
## 1 CRS-20221027-01 P41   Batch 1 RPC  
## 2 CRS-20221027-02 P64   Batch 1 RPC  
## 3 CRS-20221027-03 P34   Batch 1 RPC  
## 4 CRS-20221027-04 P66   Batch 1 MPC  
## 5 CRS-20221027-05 P51   Batch 1 RPC
standardize_id <- function(id) {
  id <- str_trim(id)
  prefix <- str_extract(id, "^[A-Za-z]+")
  number <- str_extract(id, "\\d+")
  paste0(toupper(prefix), sprintf("%03d", as.integer(number)))
}

clinical_std <- clinical_clean %>%
  mutate(id_std = standardize_id(linked_id)) %>%
  select(patient_id, linked_id, id_std, pathology)

proteomics_std <- proteomics_clean %>%
  mutate(id_std = standardize_id(linked_id)) %>%
  select(linked_id, id_std, pathology)

metabolomics_std <- metabolomics_clean %>%
  mutate(id_std = standardize_id(id)) %>%
  select(nmr_id, id, id_std, group)

# quick look at each, side by side
clinical_std %>% slice_head(n = 5)
## # A tibble: 5 × 4
##   patient_id linked_id id_std pathology
##   <chr>      <chr>     <chr>  <chr>    
## 1 RPC1       P001      P001   RPC      
## 2 RPC2       P002      P002   RPC      
## 3 RPC3       P003      P003   RPC      
## 4 RPC4       P004      P004   RPC      
## 5 RPC5       P005      P005   RPC
proteomics_std %>% slice_head(n = 5)
## # A tibble: 5 × 3
##   linked_id id_std pathology
##   <chr>     <chr>  <chr>    
## 1 BP001     BP001  Bmass    
## 2 BP003     BP003  Bmass    
## 3 BP004     BP004  Bmass    
## 4 BP006     BP006  Bmass    
## 5 BP010     BP010  Bmass
metabolomics_std %>% slice_head(n = 5)
## # A tibble: 5 × 4
##   nmr_id          id    id_std group
##   <chr>           <chr> <chr>  <chr>
## 1 CRS-20221027-01 P41   P041   RPC  
## 2 CRS-20221027-02 P64   P064   RPC  
## 3 CRS-20221027-03 P34   P034   RPC  
## 4 CRS-20221027-04 P66   P066   MPC  
## 5 CRS-20221027-05 P51   P051   RPC
clinical_ids <- clinical_std$id_std
proteomics_ids <- proteomics_std$id_std
metabolomics_ids <- metabolomics_std$id_std

overlap_summary <- tibble::tibble(
  comparison = c(
    "Clinical only (total)",
    "Proteomics only (total)",
    "Metabolomics only (total)",
    "Clinical + Proteomics",
    "Clinical + Metabolomics",
    "Proteomics + Metabolomics",
    "All three"
  ),
  n_patients = c(
    length(unique(clinical_ids)),
    length(unique(proteomics_ids)),
    length(unique(metabolomics_ids)),
    length(intersect(clinical_ids, proteomics_ids)),
    length(intersect(clinical_ids, metabolomics_ids)),
    length(intersect(proteomics_ids, metabolomics_ids)),
    length(Reduce(intersect, list(clinical_ids, proteomics_ids, metabolomics_ids)))
  )
)

overlap_summary
## # A tibble: 7 × 2
##   comparison                n_patients
##   <chr>                          <int>
## 1 Clinical only (total)            233
## 2 Proteomics only (total)          175
## 3 Metabolomics only (total)         81
## 4 Clinical + Proteomics            175
## 5 Clinical + Metabolomics           81
## 6 Proteomics + Metabolomics         64
## 7 All three                         64
matched_cohort_ids <- Reduce(intersect, list(clinical_ids, proteomics_ids, metabolomics_ids))

saveRDS(matched_cohort_ids, "matched_cohort_ids.rds")
length(matched_cohort_ids)
## [1] 64
matched_clinical <- clinical_std %>%
  filter(id_std %in% matched_cohort_ids) %>%
  rename(pathology_clinical = pathology)

matched_proteomics <- proteomics_std %>%
  filter(id_std %in% matched_cohort_ids) %>%
  rename(pathology_proteomics = pathology)

matched_metabolomics <- metabolomics_std %>%
  filter(id_std %in% matched_cohort_ids) %>%
  rename(group_metabolomics = group)

# confirm all three pathology/group labels for the same 64 patients, side by side
matched_clinical %>%
  select(id_std, pathology_clinical) %>%
  left_join(matched_proteomics %>% select(id_std, pathology_proteomics), by = "id_std") %>%
  left_join(matched_metabolomics %>% select(id_std, group_metabolomics), by = "id_std") %>%
  count(pathology_clinical, pathology_proteomics, group_metabolomics)
## # A tibble: 4 × 4
##   pathology_clinical pathology_proteomics group_metabolomics     n
##   <chr>              <chr>                <chr>              <int>
## 1 LAPC               PDAC                 LAPC                  12
## 2 MPC                PDAC                 MPC                    7
## 3 MPC                PDAC                 RPC                    1
## 4 RPC                PDAC                 RPC                   44
mismatches <- matched_clinical %>%
  select(id_std, pathology_clinical) %>%
  left_join(matched_proteomics %>% select(id_std, pathology_proteomics), by = "id_std") %>%
  left_join(matched_metabolomics %>% select(id_std, group_metabolomics), by = "id_std") %>%
  filter(pathology_clinical != group_metabolomics)

mismatches
## # A tibble: 1 × 4
##   id_std pathology_clinical pathology_proteomics group_metabolomics
##   <chr>  <chr>              <chr>                <chr>             
## 1 P035   MPC                PDAC                 RPC
saveRDS(mismatches, "pathology_mismatches.rds")
write.csv(mismatches, "pathology_mismatches.csv", row.names = FALSE)

Following correction of the clinical pathology labels for the five previously-identified discordant patients (P036, P046, P081, P064, P074), re-validation against metabolomics classifications confirmed all five now agree. However, one new discordance was identified: patient P035 (clinical: MPC; metabolomics: RPC), not present in the original five-patient discordance list. This is likely attributable to the corrected clinical source file itself reclassifying a small number of patients independently of the metabolomics-based corrections applied here. This patient has been flagged to the supervisor for confirmation of the correct diagnosis prior to inclusion in any pathology-stratified analysis.