riseR:

# LIMS QC

#######################################
#COMPARE WITH LIMS


# match with all samples collected in the field
x <- feces.merge %>%
  select (settlement_barcode, extract_house_no, barcode, today) %>%
  rename (feces.recd = barcode) %>%
  rename (today.feces = today)
y <- child %>%
  select (settlement_barcode, extract_house_no, barcode_edta, edta_yn, edta_vol,
          barcode_serum, serum_yn, serum_vol, barcode_bloodspot, blood_spot_no, today, feces_kit_barcode)
samples <- full_join(y, x, by = c("settlement_barcode" = "settlement_barcode",
                                  "extract_house_no" = "extract_house_no",
                                  "feces_kit_barcode" = "feces.recd"))

# match will all sample surveys that were started in the field
x2 <- feces.merge2 %>%
  select (settlement_barcode, extract_house_no, barcode, today) %>%
  rename (feces.recd = barcode) %>%
  rename (today.feces = today)
samples2 <- full_join(y, x2, by = c("settlement_barcode" = "settlement_barcode",
                                  "extract_house_no" = "extract_house_no",
                                  "feces_kit_barcode" = "feces.recd"))
rm(x, x2, y)

blood <- samples %>%
  select (settlement_barcode, extract_house_no, today, barcode_edta, edta_yn, edta_vol,
          barcode_serum, serum_yn, serum_vol)
blood_spot <- samples %>%
  select (settlement_barcode, extract_house_no, today, barcode_bloodspot, blood_spot_no)
feces_sample <- samples %>%
  select (settlement_barcode, extract_house_no, today, feces_kit_barcode, today.feces)
feces_sample2 <- samples2 %>%
  select (settlement_barcode, extract_house_no, today, feces_kit_barcode, today.feces)
rm(samples, samples2)

#merge with lab data
edta_check <- full_join(blood, blood_lab,
                         by = c("barcode_edta" = "Barcode_sample")) %>%
  filter (!is.na(barcode_edta))
serum_check <- full_join(blood, blood_lab,
                        by = c("barcode_serum" = "Barcode_sample")) %>%
  filter (!is.na(barcode_serum))
bloodspot_check <- full_join(blood_spot, bloodspot_lab,
                             by = c("barcode_bloodspot" = "Barcode_sample")) %>%
  filter (!is.na(barcode_bloodspot) & blood_spot_no > 0)
feces_check <- full_join(feces_sample, feces_lab,
                         by = c("feces_kit_barcode" = "Barcode_sample")) %>%
  filter (!is.na(feces_kit_barcode))
feces_check2 <- full_join(feces_sample2, feces_lab,
                         by = c("feces_kit_barcode" = "Barcode_sample"))

rm(blood, blood_spot, blood_lab, bloodspot_lab, feces_lab, feces_sample, feces_sample2)


#################################
#Lab data
#################################
LIMS_KK1 <- read_excel("Obj3_Data.xlsx", sheet = "Feces_KK1") %>%
  rename (date_KK1 = Date_process,
          time_KK1 = Time_process) %>%
  select (-Lab_tech, -Comments)
LIMS_KK2 <- read_excel("Obj3_Data.xlsx", sheet = " Feces_KK2") %>%
  rename(date_KK2 = Date_process,
         time_start_KK2 = Start_time,
         time_end_KK2 = End_time) %>%
  select (date_KK2, time_start_KK2, time_end_KK2, Barcode_KKslide)

##################################
##################################
#4. MERGE  LIMS DATA
##################################
##################################

#4.1: merge KK1 and KK2
KK_samples <- full_join (LIMS_KK1, LIMS_KK2, by = c("Barcode_kk_slide" = "Barcode_KKslide")) %>%
  mutate (days_to_KK2 = date(date_KK2) - date(date_KK1))

KK_samples2 <- KK_samples %>%
  group_by(Barcode_sample) %>%
  summarise (date_KK1 = max(date_KK1),
             Barcode_kk_slide = max(Barcode_kk_slide),
             Barcode_kk_slide_check = min(Barcode_kk_slide),
             max_days_to_KK2 = max(days_to_KK2),
             reads = n()) %>%
  mutate (check = ifelse(Barcode_kk_slide == Barcode_kk_slide_check, "ok", "error")) %>%
  select (-Barcode_kk_slide_check)


#4.2:  merge sample kits with with laboratory sample reception
feces_merge2 <- left_join (feces_check, sample_reception, by = c("feces_kit_barcode" = "Barcode_sample"))
# merge with KK samples
feces_merge3 <- full_join (feces_merge2, KK_samples2, by = c("feces_kit_barcode" = "Barcode_sample"))

rm(LIMS_KK1, LIMS_KK2, feces_merge2, KK_samples)

#check for duplicates
feces_merge3[duplicated(feces_merge3$feces_kit_barcode),] #1  duplicate - 498
sample_duplicates <- feces_merge3[duplicated(feces_merge3$feces_kit_barcode),]

# #######################
# BLOOD
# #######################
blood_lab <- sample_reception %>%
  filter (Sample_type == "Blood-Both" | Sample_type == "Blood-EDTA" |
            Sample_type == "Blood-SST") %>%
  select (Barcode_sample, Date_receipt, Sample_type)

#EDTA whole blood - aliquots for whole blood;
edta.whole <- full_join (blood_lab, blood_EDTA,
                            by = c("Barcode_sample" = "Barcode_sample"))  %>%  #3 with no aliquots
  select (-Lab_tech, -Comments)

# write_csv(edta.whole, path = "S:/R-MNHS-SPHPM-EPM-IDEpi/RISE/2. Laboratory QA/LIMS/2. reports/edta.whole.csv")
# write_csv(edta.whole, path = "Z:/Data Files/Data Files Objective 3/Reports/Child Sampling/Data Output/edta.whole.csv")

#centrifuge
blood_centrifuge <- blood_centrifuge %>%
  rename (date_centrifuge = Date_process) %>%
  select (-Lab_tech, -Comments)

centrifuge <- full_join (blood_lab, blood_centrifuge,
                         by = c("Barcode_sample" = "Barcode_sample"))  #
#EDTA aliquots
edta.aliq <- full_join (centrifuge, blood_EDTA_aliq,
                       by = c("Barcode_sample" = "Barcode_sample")) %>%  #
  select (-Lab_tech, -Comments)
#write_csv(edta.aliq, path = "S:/R-MNHS-SPHPM-EPM-IDEpi/RISE/2. Laboratory QA/LIMS/2. reports/edta.aliq.csv")
# write_csv(edta.aliq, path = "Z:/Data Files/Data Files Objective 3/Reports/Child Sampling/Data Output/edta.aliq.csv")

#SST aliquots
sst.aliq <- full_join (centrifuge, blood_SST_aliq,
                        by = c("Barcode_sample" = "Barcode_sample")) %>%  #
  select (-Lab_tech, -Comments)
#write_csv(sst.aliq, path = "S:/R-MNHS-SPHPM-EPM-IDEpi/RISE/2. Laboratory QA/LIMS/2. reports/sst.aliq.csv")
# write_csv(sst.aliq, path = "Z:/Data Files/Data Files Objective 3/Reports/Child Sampling/Data Output/sst.aliq.csv")

#merge SST aliquots, EDTA aliquots, EDTA.whole for QC check - Jeff 29/10/2019
blood_aliquots_temp <- full_join (edta.aliq, sst.aliq,
                             by = c("Barcode_sample" = "Barcode_sample")) %>%
  select(Barcode_sample, Date_receipt.x, Sample_type.x, Date_process.x,
         Barcode_plasma_1, Date_process.y,  Barcode_SST_1) %>%
  rename(EDTA_date = Date_process.x, SST_date = Date_process.y,
         Date_receipt = Date_receipt.x, Sample_type = Sample_type.x)

blood_aliquots <- full_join (blood_aliquots_temp, edta.whole,
                             by = c ("Barcode_sample" = "Barcode_sample")) %>%
  select(Barcode_sample, Date_receipt.x, Sample_type.x, EDTA_date,
         Barcode_plasma_1, SST_date, Barcode_SST_1, Date_process,
         Barcode_whole_blood) %>%
  rename(Date_receipt = Date_receipt.x, Sample_type = Sample_type.x,
         WholeBlood_date = Date_process)

# #######################
# BLOODSPOTS
# #######################
bloodspot_lab <- sample_reception %>%
  filter (Sample_type == "Filter Paper") %>%
  select (Barcode_sample, Date_receipt)


bloodspot_filter <- full_join (bloodspot_lab, blood_filter,
                        by = c("Barcode_sample" = "Barcode_sample")) %>%  #
  select (-Lab_tech, -Comments)
#write_csv(bloodspot_filter, path = "S:/R-MNHS-SPHPM-EPM-IDEpi/RISE/2. Laboratory QA/LIMS/2. reports/bloodspot_filter.csv")
# write_csv(bloodspot_filter, path = "Z:/Data Files/Data Files Objective 3/Reports/Child Sampling/Data Output/bloodspot_filter.csv")


# #######################
# FECES
# #######################

feces_lab <- sample_reception %>%
  filter (Sample_type == "Human Feces") %>%
  select (Barcode_sample, Date_receipt, Sample_type)

#feces aliquot
feces_aliquot <- full_join (feces_lab, feces_aliq,
                        by = c("Barcode_sample" = "Barcode_sample")) %>%  #
  select (-Lab_tech, -Comments)
#write_csv(feces_aliquot, path = "S:/R-MNHS-SPHPM-EPM-IDEpi/RISE/2. Laboratory QA/LIMS/2. reports/feces_aliquot.csv")
# write_csv(feces_aliquot, path = "Z:/Data Files/Data Files Objective 3/Reports/Child Sampling/Data Output/feces_aliquot.csv")


#KK1
feces_kk1 <- full_join (feces_lab, KK1,
                               by = c("Barcode_sample" = "Barcode_sample")) %>%  #
  select (-Lab_tech, -Comments)
#add KK2
feces_kk <- full_join (feces_kk1, KK2,
                        by = c("Barcode_kk_slide" = "Barcode_KKslide")) %>%
  #select (-Lab_tech) %>%
  rename (date.kk1 = Date_process.x,
          date.kk2 = Date_process.y)
#write_csv(feces_kk, path = "S:/R-MNHS-SPHPM-EPM-IDEpi/RISE/2. Laboratory QA/LIMS/2. reports/feces_kk2.csv")
# write_csv(feces_kk, path = "Z:/Data Files/Data Files Objective 3/Reports/Child Sampling/Data Output/feces_kk2.csv")

#Merge feces_kk with child data for Autiko's report - Jeff
feces_kk_final <- inner_join (child.merge, feces_kk,
                         by = c("feces" = "Barcode_sample")) %>%
  select (settlement_barcode, extract_house_no, starttime, child_name, child_dob,
          child_gender, feces, Date_receipt, date.kk1, Barcode_kk_slide,
          date.kk2, AscarisLumbricoides, AscarisLumbricoides_comment,
          Hookworm, Hookworm_comment, TrichurisTrichura, TrichurisTrichura_comment,
          StrongyloidesStercoralis, StrongyloidesStercoralis_comment, TaeniaSpss,
          TaeniaSpss_comment, GeneralComments, Finalized)  %>%
  rename (settlement = settlement_barcode,
          house_no = extract_house_no,
          feces_barcode = feces,
          date_sample_reception = Date_receipt,
          date_of_pickup = starttime,
          Sex = child_gender)

feces_kk_summary <- feces_kk_final %>%
  select (-child_name, -child_dob, -Sex, -AscarisLumbricoides_comment, -Hookworm_comment,
          -TrichurisTrichura_comment, -StrongyloidesStercoralis_comment, -TaeniaSpss_comment) %>%
  mutate (AscarisLumbricoides = ifelse(AscarisLumbricoides > 0, 1, 0),
          Hookworm = ifelse(Hookworm > 0, 1, 0),
          TaeniaSpss = ifelse(TaeniaSpss > 0, 1, 0),
          TrichurisTrichura = ifelse(TrichurisTrichura > 0, 1, 0),
          StrongyloidesStercoralis = ifelse(StrongyloidesStercoralis > 0, 1, 0),
          GeneralComments = ifelse(!is.na(GeneralComments) & GeneralComments > 0, 1, 0)) %>%
  group_by (settlement) %>%
  summarise (AscarisLumbricoides = sum(AscarisLumbricoides, na.rm = TRUE),
             Hookworm = sum(Hookworm, na.rm = TRUE),
             TaeniaSpss = sum(TaeniaSpss, na.rm = TRUE),
             TrichurisTrichura = sum(TrichurisTrichura, na.rm = TRUE),
             StrongyloidesStercoralis = sum(StrongyloidesStercoralis, na.rm = TRUE),
             Other = sum(GeneralComments, na.rm = TRUE))


#macconkey
feces_mac_1 <- full_join (feces_lab, feces_mac1,
                        by = c("Barcode_sample" = "Barcode_sample")) %>%  #
  select (-Lab_tech, -Comments)
feces_mac <- full_join (feces_mac_1, feces_mac2,
                          by = c("Barcode_macconkey" = "Barcode_macconkey")) %>%  #
  select (-Lab_tech, -Comments)
#write_csv(feces_mac, path = "S:/R-MNHS-SPHPM-EPM-IDEpi/RISE/2. Laboratory QA/LIMS/2. reports/feces_mac.csv")
# write_csv(feces_mac, path = "Z:/Data Files/Data Files Objective 3/Reports/Child Sampling/Data Output/feces_mac.csv")

#compare feces aliquot to kk1 and mac1 QC
aliquotQC_1 <- full_join (feces_aliquot, feces_kk1,
                          by = c("Barcode_sample" = "Barcode_sample"))
aliquotQC <- full_join (aliquotQC_1, feces_mac_1,
                        by = c ("Barcode_sample" = "Barcode_sample")) %>%
  select (Barcode_sample, Date_receipt.x, Sample_type.x, Date_receipt.y,
          Barcode_kk_slide, Date_receipt, Barcode_macconkey) %>%
  rename (Sample_reception_date = Date_receipt.x, Sample_type = Sample_type.x,
          KK_slide_date = Date_receipt.y, Mac1_date = Date_receipt)