parse_fred.R

library(tidyverse)
library(magrittr)
library(lubridate)


read_sample_list_and_qc_meas <- function(path1 = "Input/Sample_info_ic_qc_table.xlsx", path2 = "Input//IC_QC_Table_full.xlsx") {
  

  samp <- 
    
    readxl::read_xlsx(path1) %>% 
    
    mutate(across(c(Date, Time), as.character)) %>% 
    mutate(
      Time = map_chr(strsplit(Time, " "), last),
      Timestring = paste(Date, Time, `Time Unite`),
      Timestamp = lubridate::parse_date_time(Timestring, "%y-%m-%d %I:%M:%S %p") 
    ) %>% 
    select(SAMPLE_ID = Sample, Timestamp) %>% 
    arrange(Timestamp) 
  
  meas <-
    readxl::read_xlsx(path2, guess_max = 1e5) %>% 
    select(SAMPLE_ID = sample, FEATURE_ID = compoundId, PEAK_AREA = peakArea, everything()) %>% 
    semi_join(samp, by = "SAMPLE_ID")
  
  
  samp %<>% 
    semi_join(meas, by = "SAMPLE_ID") %>% 
    mutate(
      i = seq(n()),
      day = lubridate::day(Timestamp),
      month = lubridate::month(Timestamp),
      year = lubridate::year(Timestamp) %>% as.integer(),
      hour = lubridate::hour(Timestamp),
      minute = lubridate::minute(Timestamp),
      Stamp2 = sprintf("%02d::%d-%02d-%02d %02d:%02d", i, year, month, day, hour, minute),
      year = year - 2000,
      across(c(i, day, month, year, hour, minute), ~sprintf("%02d", .x))
      )
    
  
  meas %<>% 
    left_join(samp %>% select(SAMPLE_ID, Stamp2), by = "SAMPLE_ID") %>% 
    mutate(SAMPLE_ID = Stamp2) %>% 
    select(-Stamp2) %>% 
    arrange(SAMPLE_ID, FEATURE_ID)
  
  samp %<>% 
    mutate(SAMPLE_ID = Stamp2) %>% 
    select(-Stamp2) %>% 
    rename(RUN_ORDER = i)
  
  
  feat <- 
    meas %>% 
    select(FEATURE_ID) %>% 
    unique() %>% 
    mutate(
      BASE_COMPOUND = strsplit(FEATURE_ID, "_M") %>% map_chr(first),
      M = strsplit(FEATURE_ID, "_M") %>% map_chr(last)
    )
  
  
  # ggplot(
  #   data = meas2,
  #   mapping = aes(x = Timestamp, y = peakArea, colour = FEATURE_ID)
  # ) +
  #   geom_line() +
  #   scale_y_log10()
  
  
  write_rds(
    list(
      samp = samp, 
      meas = meas,
      feat = feat
      ), 
    "Input/ic.rds"
    )
  
}
 

read_sample_list_and_qc_meas_bz <- function(path1 = "Input/Sample_info_Bz.xlsx", path2 = "Input/BZ_QC_Table_Batch_all.xlsx") {
  
  
  samp <- 
    
    readxl::read_xlsx(path1) %>% 
    
    mutate(across(c(Date, Time), as.character)) %>% 
    mutate(
      Time = map_chr(strsplit(Time, " "), last),
      Timestring = paste(Date, Time, `Time Unite`),
      Timestamp = lubridate::parse_date_time(Timestring, "%y-%m-%d %I:%M:%S %p") 
    ) %>% 
    select(SAMPLE_ID = Sample, Timestamp) %>% 
    arrange(Timestamp) 
  
  meas <-
    readxl::read_xlsx(path2, guess_max = 1e5) %>% 
    select(SAMPLE_ID = sample, FEATURE_ID = compoundId, PEAK_AREA = peakArea, everything()) %>% 
    semi_join(samp, by = "SAMPLE_ID") 
    
    
  meas %<>%
    mutate(
      FEATURE_ID = sub("_M", "_", FEATURE_ID),
      isotopeLabel = ifelse(isotopeLabel == "C12 PARENT", "C13-label-0", isotopeLabel),
      M = map_chr(strsplit(isotopeLabel, "-"), last) %>% as.integer(),
      FEATURE_ID = sprintf("%s_M%02d", FEATURE_ID, M)
    ) 
  
  
  
  samp %<>% 
    semi_join(meas, by = "SAMPLE_ID") %>% 
    mutate(
      i = seq(n()),
      day = lubridate::day(Timestamp),
      month = lubridate::month(Timestamp),
      year = lubridate::year(Timestamp) %>% as.integer(),
      hour = lubridate::hour(Timestamp),
      minute = lubridate::minute(Timestamp),
      Stamp2 = sprintf("%02d::%d-%02d-%02d %02d:%02d", i, year, month, day, hour, minute),
      year = year - 2000,
      across(c(i, day, month, year, hour, minute), ~sprintf("%02d", .x))
    )
  
  
  meas %<>% 
    left_join(samp %>% select(SAMPLE_ID, Stamp2), by = "SAMPLE_ID") %>% 
    mutate(SAMPLE_ID = Stamp2) %>% 
    select(-Stamp2) %>% 
    arrange(SAMPLE_ID, FEATURE_ID)
  
  samp %<>% 
    mutate(SAMPLE_ID = Stamp2) %>% 
    select(-Stamp2) %>% 
    rename(RUN_ORDER = i)
  
  
  feat <- 
    meas %>% 
    select(FEATURE_ID) %>% 
    unique() %>% 
    mutate(
      BASE_COMPOUND = strsplit(FEATURE_ID, "_M") %>% map_chr(first),
      M = strsplit(FEATURE_ID, "_M") %>% map_chr(last)
    )
  
  
  # ggplot(
  #   data = meas2,
  #   mapping = aes(x = Timestamp, y = peakArea, colour = FEATURE_ID)
  # ) +
  #   geom_line() +
  #   scale_y_log10()
  
  
  write_rds(
    list(
      samp = samp, 
      meas = meas,
      feat = feat
    ), 
    "Input/bz2.rds"
  )
  
}