This report is automatically generated with the R package knitr (version 1.40) .

source("R Functions/functions_QA data.R")


### LOAD DATA ###
exclude_cols <- c("AnalyteGroup", "AutoID", "Basis", "BlankCorrected", "Coelutions", "COMMENTS_DL", "DisplayCode", "DL_ORIG", "FieldResultBatchVerCode", "FieldResultCalibrationDate", "LabResultComments",
                  "PARAMETER_ORIG", "QACode_ORIG", "SFEIContractID", "SITE_CODE_ORIG", "Species", "StationName", "Unit_Orig" )

DRMP_16_Hg_Anc <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2016-17_Hg_Anc_Aq_Sed.xlsx', sheet=1, guess_max = 30000) %>%
  mutate(SourceID = 'DRMP_2016-17_Hg_Anc_Aq_Sed',
         SourceRow = rownames(.),
         ChemResultRowID = NA_character_,
         LabResultRowID = NA_character_) %>%
  rename(LabCollectionComments = CollectionComments,
         PrepPreservationDate = PreparationPreservationDate,
         PrepPreservationName = PreparationPreservation,
         Replicate = SampleReplicate,
         ResQualCode = ResultQualCode,
         UnitName = Unit) %>%
  select(-one_of(exclude_cols)) #Remove excluded columns not shared in the data sheets below

DRMP_17_Hg_Anc <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2017-18_Hg_Anc_Aq_Sed.xlsx', sheet='ChemResults', guess_max = 30000) %>%
  mutate(SourceID = 'DRMP_2017-18_Hg_Anc_Aq_Sed',
         SourceRow = rownames(.),
         ChemResultRowID = NA_character_)

DRMP_18_Hg    <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2018-19_Hg_Aq.xlsx', sheet='ChemResults', guess_max = 30000)%>%
  mutate(SourceID = 'DRMP_2018-19_Hg_Aq',
         SourceRow = rownames(.))

DRMP_18_Anc   <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2018-19_Anc_Aq.xlsx', sheet='ChemResults', guess_max = 30000) %>%
  mutate(SourceID = 'DRMP_2018-19_Anc_Aq',
         SourceRow = rownames(.)) %>%
  select(-AnalysisDate_original, -PrepPreservationDate_original, -DigestExtractDate_original)

DRMP_19Jul_Hg <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2019-Jul_chem_template_final.xls', sheet='ChemResults', guess_max = 30000) %>%
  mutate(SourceID = 'DRMP_19Jul_Hg',
         SourceRow = rownames(.),
         ChemResultRowID = NA_character_) %>%
  select(-CollectionTime_original)

DRMP_19Aug_Hg <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2019-Aug_chem_template_final.xls', sheet='ChemResults', guess_max = 30000) %>%
  mutate(SourceID = 'DRMP_19Aug_Hg',
         SourceRow = rownames(.),
         ChemResultRowID = NA_character_) %>%
  select(-CollectionTime_original)

DRMP_19Sep_Hg <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2019-Sep_chem_template_final.xls', sheet='ChemResults', guess_max = 30000) %>%
  mutate(SourceID = 'DRMP_19Sep_Hg',
         SourceRow = rownames(.),
         ChemResultRowID = NA_character_) %>%
  select(-CollectionTime_original)

DRMP_19Oct_Hg <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2019-Oct_chem_template_final.xls', sheet='ChemResults', guess_max = 30000) %>%
  mutate(SourceID = 'DRMP_19Oct_Hg',
         SourceRow = rownames(.),
         ChemResultRowID = NA_character_) %>%
  select(-CollectionTime_original)

sort(names(DRMP_16_Hg_Anc)); nrow(DRMP_16_Hg_Anc)
##  [1] "AgencyCode"            "AnalysisDate"          "AnalyteName"          
##  [4] "ChemResultRowID"       "CollectionDepth"       "CollectionDeviceName" 
##  [7] "CollectionMethodCode"  "CollectionTime"        "ComplianceCode"       
## [10] "DigestExtractDate"     "DigestExtractMethod"   "DilutionFactor"       
## [13] "EventCode"             "ExpectedValue"         "FractionName"         
## [16] "GeometryShape"         "LabBatch"              "LabCollectionComments"
## [19] "LabReplicate"          "LabResultRowID"        "LabSampleID"          
## [22] "LocationCode"          "MatrixName"            "MDL"                  
## [25] "MethodName"            "PositionWaterColumn"   "PrepPreservationDate" 
## [28] "PrepPreservationName"  "ProjectCode"           "ProtocolCode"         
## [31] "QACode"                "Replicate"             "ResQualCode"          
## [34] "Result"                "RL"                    "SampleComments"       
## [37] "SampleDate"            "SampleID"              "SampleTypeCode"       
## [40] "SourceID"              "SourceRow"             "StationCode"          
## [43] "UnitCollectionDepth"   "UnitName"
## [1] 394
sort(names(DRMP_17_Hg_Anc)); nrow(DRMP_17_Hg_Anc)
##  [1] "AgencyCode"            "AnalysisDate"          "AnalyteName"          
##  [4] "ChemResultRowID"       "CollectionDepth"       "CollectionDeviceName" 
##  [7] "CollectionMethodCode"  "CollectionTime"        "ComplianceCode"       
## [10] "DigestExtractDate"     "DigestExtractMethod"   "DilutionFactor"       
## [13] "EventCode"             "ExpectedValue"         "FractionName"         
## [16] "GeometryShape"         "LabBatch"              "LabCollectionComments"
## [19] "LabReplicate"          "LabResultRowID"        "LabSampleID"          
## [22] "LocationCode"          "MatrixName"            "MDL"                  
## [25] "MethodName"            "PositionWaterColumn"   "PrepPreservationDate" 
## [28] "PrepPreservationName"  "ProjectCode"           "ProtocolCode"         
## [31] "QACode"                "Replicate"             "ResQualCode"          
## [34] "Result"                "RL"                    "SampleComments"       
## [37] "SampleDate"            "SampleID"              "SampleTypeCode"       
## [40] "SourceID"              "SourceRow"             "StationCode"          
## [43] "UnitCollectionDepth"   "UnitName"
## [1] 1311
sort(names(DRMP_18_Hg)); nrow(DRMP_18_Hg)
##  [1] "AgencyCode"            "AnalysisDate"          "AnalyteName"          
##  [4] "ChemResultRowID"       "CollectionDepth"       "CollectionDeviceName" 
##  [7] "CollectionMethodCode"  "CollectionTime"        "ComplianceCode"       
## [10] "DigestExtractDate"     "DigestExtractMethod"   "DilutionFactor"       
## [13] "EventCode"             "ExpectedValue"         "FractionName"         
## [16] "GeometryShape"         "LabBatch"              "LabCollectionComments"
## [19] "LabReplicate"          "LabResultComments"     "LabSampleID"          
## [22] "LocationCode"          "MatrixName"            "MDL"                  
## [25] "MethodName"            "PositionWaterColumn"   "PrepPreservationDate" 
## [28] "PrepPreservationName"  "ProjectCode"           "ProtocolCode"         
## [31] "QACode"                "Replicate"             "ResQualCode"          
## [34] "Result"                "RL"                    "SampleComments"       
## [37] "SampleDate"            "SampleID"              "SampleTypeCode"       
## [40] "SourceID"              "SourceRow"             "StationCode"          
## [43] "UnitCollectionDepth"   "UnitName"
## [1] 632
sort(names(DRMP_18_Anc)); nrow(DRMP_18_Anc)
##  [1] "AgencyCode"            "AnalysisDate"          "AnalyteName"          
##  [4] "ChemResultRowID"       "CollectionDepth"       "CollectionDeviceName" 
##  [7] "CollectionMethodCode"  "CollectionTime"        "ComplianceCode"       
## [10] "DigestExtractDate"     "DigestExtractMethod"   "DilutionFactor"       
## [13] "EventCode"             "ExpectedValue"         "FractionName"         
## [16] "GeometryShape"         "LabBatch"              "LabCollectionComments"
## [19] "LabReplicate"          "LabResultComments"     "LabSampleID"          
## [22] "LocationCode"          "MatrixName"            "MDL"                  
## [25] "MethodName"            "PositionWaterColumn"   "PrepPreservationDate" 
## [28] "PrepPreservationName"  "ProjectCode"           "ProtocolCode"         
## [31] "QACode"                "Replicate"             "ResQualCode"          
## [34] "Result"                "RL"                    "SampleComments"       
## [37] "SampleDate"            "SampleID"              "SampleTypeCode"       
## [40] "SourceID"              "SourceRow"             "StationCode"          
## [43] "UnitCollectionDepth"   "UnitName"
## [1] 572
sort(names(DRMP_19Jul_Hg)); nrow(DRMP_19Jul_Hg)
##  [1] "AgencyCode"            "AnalysisDate"          "AnalyteName"          
##  [4] "ChemResultRowID"       "CollectionDepth"       "CollectionDeviceName" 
##  [7] "CollectionMethodCode"  "CollectionTime"        "ComplianceCode"       
## [10] "DigestExtractDate"     "DigestExtractMethod"   "DilutionFactor"       
## [13] "EventCode"             "ExpectedValue"         "FractionName"         
## [16] "GeometryShape"         "LabBatch"              "LabCollectionComments"
## [19] "LabReplicate"          "LabResultComments"     "LabSampleID"          
## [22] "LocationCode"          "MatrixName"            "MDL"                  
## [25] "MethodName"            "PositionWaterColumn"   "PrepPreservationDate" 
## [28] "PrepPreservationName"  "ProjectCode"           "ProtocolCode"         
## [31] "QACode"                "Replicate"             "ResQualCode"          
## [34] "Result"                "RL"                    "SampleComments"       
## [37] "SampleDate"            "SampleID"              "SampleTypeCode"       
## [40] "SourceID"              "SourceRow"             "StationCode"          
## [43] "UnitCollectionDepth"   "UnitName"
## [1] 119
sort(names(DRMP_19Aug_Hg)); nrow(DRMP_19Aug_Hg)
##  [1] "AgencyCode"            "AnalysisDate"          "AnalyteName"          
##  [4] "ChemResultRowID"       "CollectionDepth"       "CollectionDeviceName" 
##  [7] "CollectionMethodCode"  "CollectionTime"        "ComplianceCode"       
## [10] "DigestExtractDate"     "DigestExtractMethod"   "DilutionFactor"       
## [13] "EventCode"             "ExpectedValue"         "FractionName"         
## [16] "GeometryShape"         "LabBatch"              "LabCollectionComments"
## [19] "LabReplicate"          "LabResultComments"     "LabSampleID"          
## [22] "LocationCode"          "MatrixName"            "MDL"                  
## [25] "MethodName"            "PositionWaterColumn"   "PrepPreservationDate" 
## [28] "PrepPreservationName"  "ProjectCode"           "ProtocolCode"         
## [31] "QACode"                "Replicate"             "ResQualCode"          
## [34] "Result"                "RL"                    "SampleComments"       
## [37] "SampleDate"            "SampleID"              "SampleTypeCode"       
## [40] "SourceID"              "SourceRow"             "StationCode"          
## [43] "UnitCollectionDepth"   "UnitName"
## [1] 114
sort(names(DRMP_19Sep_Hg)); nrow(DRMP_19Sep_Hg)
##  [1] "AgencyCode"            "AnalysisDate"          "AnalyteName"          
##  [4] "ChemResultRowID"       "CollectionDepth"       "CollectionDeviceName" 
##  [7] "CollectionMethodCode"  "CollectionTime"        "ComplianceCode"       
## [10] "DigestExtractDate"     "DigestExtractMethod"   "DilutionFactor"       
## [13] "EventCode"             "ExpectedValue"         "FractionName"         
## [16] "GeometryShape"         "LabBatch"              "LabCollectionComments"
## [19] "LabReplicate"          "LabResultComments"     "LabSampleID"          
## [22] "LocationCode"          "MatrixName"            "MDL"                  
## [25] "MethodName"            "PositionWaterColumn"   "PrepPreservationDate" 
## [28] "PrepPreservationName"  "ProjectCode"           "ProtocolCode"         
## [31] "QACode"                "Replicate"             "ResQualCode"          
## [34] "Result"                "RL"                    "SampleComments"       
## [37] "SampleDate"            "SampleID"              "SampleTypeCode"       
## [40] "SourceID"              "SourceRow"             "StationCode"          
## [43] "UnitCollectionDepth"   "UnitName"
## [1] 112
sort(names(DRMP_19Oct_Hg)); nrow(DRMP_19Oct_Hg)
##  [1] "AgencyCode"            "AnalysisDate"          "AnalyteName"          
##  [4] "ChemResultRowID"       "CollectionDepth"       "CollectionDeviceName" 
##  [7] "CollectionMethodCode"  "CollectionTime"        "ComplianceCode"       
## [10] "DigestExtractDate"     "DigestExtractMethod"   "DilutionFactor"       
## [13] "EventCode"             "ExpectedValue"         "FractionName"         
## [16] "GeometryShape"         "LabBatch"              "LabCollectionComments"
## [19] "LabReplicate"          "LabResultComments"     "LabSampleID"          
## [22] "LocationCode"          "MatrixName"            "MDL"                  
## [25] "MethodName"            "PositionWaterColumn"   "PrepPreservationDate" 
## [28] "PrepPreservationName"  "ProjectCode"           "ProtocolCode"         
## [31] "QACode"                "Replicate"             "ResQualCode"          
## [34] "Result"                "RL"                    "SampleComments"       
## [37] "SampleDate"            "SampleID"              "SampleTypeCode"       
## [40] "SourceID"              "SourceRow"             "StationCode"          
## [43] "UnitCollectionDepth"   "UnitName"
## [1] 112
DRMP_2016_2019_Aq_Sed_Data <- bind_rows(DRMP_16_Hg_Anc, DRMP_17_Hg_Anc, DRMP_18_Hg, DRMP_18_Anc, DRMP_19Jul_Hg, DRMP_19Aug_Hg, DRMP_19Sep_Hg, DRMP_19Oct_Hg) %>%
  select(SourceID, SourceRow, everything())



## SAVE MERGED DATA##
#Save to Aqueous Data folder
writexl::write_xlsx(DRMP_2016_2019_Aq_Sed_Data, path='Reeval_Impl_Goals_Linkage_Analysis/Data/Aqueous/DRMP_2016_2019_Aq_Sed_Data.xlsx')
#Save to Ancillary Data folder
writexl::write_xlsx(DRMP_2016_2019_Aq_Sed_Data, path='Reeval_Impl_Goals_Linkage_Analysis/Data/Ancillary/DRMP_2016_2019_Aq_Sed_Data.xlsx')

The R session information (including the OS info, R version and all packages used):

    sessionInfo()
## R version 4.2.2 (2022-10-31 ucrt)
## Platform: x86_64-w64-mingw32/x64 (64-bit)
## Running under: Windows 10 x64 (build 22621)
## 
## Matrix products: default
## 
## locale:
## [1] LC_COLLATE=English_United States.utf8  LC_CTYPE=English_United States.utf8   
## [3] LC_MONETARY=English_United States.utf8 LC_NUMERIC=C                          
## [5] LC_TIME=English_United States.utf8    
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## other attached packages:
##  [1] mgcv_1.8-41        nlme_3.1-160       lubridate_1.8.0    plotly_4.10.0     
##  [5] readxl_1.4.1       actuar_3.3-0       NADA_1.6-1.1       forcats_0.5.2     
##  [9] stringr_1.4.1      dplyr_1.0.9        purrr_0.3.4        readr_2.1.2       
## [13] tidyr_1.2.0        tibble_3.1.8       ggplot2_3.3.6      tidyverse_1.3.2   
## [17] fitdistrplus_1.1-8 survival_3.4-0     MASS_7.3-58.1     
## 
## loaded via a namespace (and not attached):
##  [1] lattice_0.20-45     assertthat_0.2.1    digest_0.6.29       utf8_1.2.2         
##  [5] R6_2.5.1            cellranger_1.1.0    backports_1.4.1     reprex_2.0.2       
##  [9] evaluate_0.16       highr_0.9           httr_1.4.4          pillar_1.8.1       
## [13] rlang_1.0.5         lazyeval_0.2.2      googlesheets4_1.0.1 rstudioapi_0.14    
## [17] data.table_1.14.2   Matrix_1.5-1        splines_4.2.2       webshot_0.5.3      
## [21] googledrive_2.0.0   htmlwidgets_1.5.4   munsell_0.5.0       broom_1.0.1        
## [25] compiler_4.2.2      modelr_0.1.9        xfun_0.32           pkgconfig_2.0.3    
## [29] htmltools_0.5.3     tidyselect_1.1.2    viridisLite_0.4.1   fansi_1.0.3        
## [33] crayon_1.5.1        tzdb_0.3.0          dbplyr_2.2.1        withr_2.5.0        
## [37] grid_4.2.2          jsonlite_1.8.0      gtable_0.3.1        lifecycle_1.0.1    
## [41] DBI_1.1.3           magrittr_2.0.3      scales_1.2.1        writexl_1.4.0      
## [45] cli_3.3.0           stringi_1.7.8       fs_1.5.2            xml2_1.3.3         
## [49] ellipsis_0.3.2      generics_0.1.3      vctrs_0.4.1         expint_0.1-7       
## [53] tools_4.2.2         glue_1.6.2          crosstalk_1.2.0     hms_1.1.2          
## [57] yaml_2.3.5          fastmap_1.1.0       colorspace_2.0-3    gargle_1.2.0       
## [61] rvest_1.0.3         knitr_1.40          haven_2.5.1
    Sys.time()
## [1] "2024-01-04 15:50:28 PST"