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

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


### LOAD DATA ###
DRMP_17_TIResults <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2017_Hg_Fish.xlsx', sheet='TIResults', guess_max = 30000) %>%
  mutate(SourceID = 'DRMP_2017_Hg_Fish',
         SourceRow = rownames(.),
         BlankCorrected = NA_character_,
         QACodeLab      = NA_character_,
         SFEISampleID   = NA_character_) %>%
  rename(UIDTIR = TissueResultRowID)

DRMP_17_FishComposite <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2017_Hg_Fish.xlsx', sheet='FishComposite', guess_max = 30000)
## New names:
## • `AgencyCode` -> `AgencyCode...6`
## • `AgencyCode` -> `AgencyCode...49`
DRMP_18_TIResults <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2018_Hg_Fish.xlsx', sheet='TIResults', guess_max = 30000) %>%
  mutate(SourceID = 'DRMP_2018_Hg_Fish',
         SourceRow = rownames(.),
         BlankCorrected = NA_character_,
         QACodeLab      = NA_character_,
         SFEISampleID   = NA_character_)

DRMP_18_FishComposite <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2018_Hg_Fish.xlsx', sheet='FishComposite', guess_max = 30000)


DRMP_19_TIResults <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2019_Hg_Fish.xlsx', sheet='TIResults', guess_max = 30000) %>%
  mutate(SourceID = 'DRMP_2019_Hg_Fish',
         SourceRow = rownames(.),
         ComplianceCode = NA_character_)

DRMP_19_FishComposite <- readxl::read_excel('Reeval_Impl_Goals_Linkage_Analysis/Data/DRMP FY16-19/DRMP_2019_Hg_Fish.xlsx', sheet='FishComposite', guess_max = 30000)

# TIResults
sort(names(DRMP_17_TIResults)); nrow(DRMP_17_TIResults)
##  [1] "AnalysisDate"         "AnalyteName"          "BlankCorrected"      
##  [4] "ComplianceCode"       "CompositeComments"    "CompositeID"         
##  [7] "CompositeReplicate"   "CompositeType"        "DigestExtractDate"   
## [10] "DigestExtractMethod"  "DilutionFactor"       "ExpectedValue"       
## [13] "FractionName"         "LabBatch"             "LabReplicate"        
## [16] "LabSampleID"          "MatrixName"           "MDL"                 
## [19] "MethodName"           "OrganismGroup"        "PrepPreservationDate"
## [22] "PrepPreservationName" "QACode"               "QACodeLab"           
## [25] "ResQualCode"          "Result"               "RL"                  
## [28] "SampleTypeCode"       "SFEISampleID"         "SourceID"            
## [31] "SourceRow"            "TissueResultComments" "UIDTIR"              
## [34] "UnitName"
## [1] 207
sort(names(DRMP_18_TIResults)); nrow(DRMP_18_TIResults)
##  [1] "AnalysisDate"         "AnalyteName"          "BlankCorrected"      
##  [4] "ComplianceCode"       "CompositeComments"    "CompositeID"         
##  [7] "CompositeReplicate"   "CompositeType"        "DigestExtractDate"   
## [10] "DigestExtractMethod"  "DilutionFactor"       "ExpectedValue"       
## [13] "FractionName"         "LabBatch"             "LabReplicate"        
## [16] "LabSampleID"          "MatrixName"           "MDL"                 
## [19] "MethodName"           "OrganismGroup"        "PrepPreservationDate"
## [22] "PrepPreservationName" "QACode"               "QACodeLab"           
## [25] "ResQualCode"          "Result"               "RL"                  
## [28] "SampleTypeCode"       "SFEISampleID"         "SourceID"            
## [31] "SourceRow"            "TissueResultComments" "UIDTIR"              
## [34] "UnitName"
## [1] 258
sort(names(DRMP_19_TIResults)); nrow(DRMP_19_TIResults)
##  [1] "AnalysisDate"         "AnalyteName"          "BlankCorrected"      
##  [4] "ComplianceCode"       "CompositeComments"    "CompositeID"         
##  [7] "CompositeReplicate"   "CompositeType"        "DigestExtractDate"   
## [10] "DigestExtractMethod"  "DilutionFactor"       "ExpectedValue"       
## [13] "FractionName"         "LabBatch"             "LabReplicate"        
## [16] "LabSampleID"          "MatrixName"           "MDL"                 
## [19] "MethodName"           "OrganismGroup"        "PrepPreservationDate"
## [22] "PrepPreservationName" "QACode"               "QACodeLab"           
## [25] "ResQualCode"          "Result"               "RL"                  
## [28] "SampleTypeCode"       "SFEISampleID"         "SourceID"            
## [31] "SourceRow"            "TissueResultComments" "UIDTIR"              
## [34] "UnitName"
## [1] 278
DRMP_2017_2019_TIResults <- bind_rows(DRMP_17_TIResults, DRMP_18_TIResults, DRMP_19_TIResults) %>%
  select(SourceID, SourceRow, everything())

# FishComposite
sort(names(DRMP_17_FishComposite)); nrow(DRMP_17_FishComposite)
##  [1] "Age"                                    "AgencyCode...49"                       
##  [3] "AgencyCode...6"                         "Anomaly"                               
##  [5] "CollectionDeviceName"                   "CollectionMethodCode"                  
##  [7] "CollectionTime"                         "CompositeComments"                     
##  [9] "CompositeID"                            "CompositeReplicate"                    
## [11] "CompositeRowID"                         "CompositeType"                         
## [13] "CompositeWeight"                        "EntryDateTime"                         
## [15] "EventCode"                              "ForkLength"                            
## [17] "GeometryShape"                          "HomogDate"                             
## [19] "LengthSource"                           "LifeStageCode"                         
## [21] "LocationCode"                           "OrganismGroup"                         
## [23] "OrganismID"                             "OrganismName"                          
## [25] "PartCreated"                            "PartsComments"                         
## [27] "PrepPreservationName"                   "ProcessedOrganismsExpandedFishComments"
## [29] "ProjectCode"                            "ProtocolCode"                          
## [31] "Replicate"                              "SampleComments"                        
## [33] "SampleDate"                             "SampleID"                              
## [35] "Sex"                                    "SizeDescr"                             
## [37] "StationCode"                            "TagNumber"                             
## [39] "TisSource"                              "TissueCollectionComments"              
## [41] "TissueID"                               "TissueName"                            
## [43] "TissueWeight"                           "TotalCount"                            
## [45] "TotalLength"                            "UnitCompositeWeight"                   
## [47] "UnitLengthFish"                         "UnitTissueWeight"                      
## [49] "UnitWeightFish"                         "Weight"                                
## [51] "WeightSource"
## [1] 96
sort(names(DRMP_18_FishComposite)); nrow(DRMP_18_FishComposite)
##  [1] "Age"                                    "AgencyCode"                            
##  [3] "Anomaly"                                "CollectionDeviceName"                  
##  [5] "CollectionMethodCode"                   "CollectionTime"                        
##  [7] "CompAgencyCode"                         "CompositeComments"                     
##  [9] "CompositeID"                            "CompositeReplicate"                    
## [11] "CompositeType"                          "CompositeWeight"                       
## [13] "EntryDateTime"                          "EventCode"                             
## [15] "ForkLength"                             "GeometryShape"                         
## [17] "HomogDate"                              "LengthSource"                          
## [19] "LifeStageCode"                          "LocationCode"                          
## [21] "OrganismGroup"                          "OrganismID"                            
## [23] "OrganismName"                           "PartCreated"                           
## [25] "PartsComments"                          "PrepPreservationName"                  
## [27] "ProcessedOrganismsExpandedFishComments" "ProjectCode"                           
## [29] "ProtocolCode"                           "Replicate"                             
## [31] "SampleComments"                         "SampleDate"                            
## [33] "SampleID"                               "Sex"                                   
## [35] "SizeDescr"                              "SQLServerID"                           
## [37] "StationCode"                            "TagNumber"                             
## [39] "TisSource"                              "TissueCollectionComment"               
## [41] "TissueID"                               "TissueName"                            
## [43] "TissueWeight"                           "TotalCount"                            
## [45] "TotalLength"                            "UnitCompositeWeight"                   
## [47] "UnitLengthFish"                         "UnitTissueWeight"                      
## [49] "UnitWeightFish"                         "Weight"                                
## [51] "WeightSource"
## [1] 291
sort(names(DRMP_19_FishComposite)); nrow(DRMP_19_FishComposite)
##  [1] "Age"                                    "AgencyCode"                            
##  [3] "Anomaly"                                "CollectionDeviceName"                  
##  [5] "CollectionMethodCode"                   "CollectionTime"                        
##  [7] "CompAgencyCode"                         "CompositeComments"                     
##  [9] "CompositeID"                            "CompositeReplicate"                    
## [11] "CompositeType"                          "CompositeWeight"                       
## [13] "EntryDateTime"                          "EventCode"                             
## [15] "ForkLength"                             "GeometryShape"                         
## [17] "HomogDate"                              "LengthSource"                          
## [19] "LifeStageCode"                          "LocationCode"                          
## [21] "OrganismGroup"                          "OrganismID"                            
## [23] "OrganismName"                           "PartCreated"                           
## [25] "PartsComments"                          "PrepPreservationName"                  
## [27] "ProcessedOrganismsExpandedFishComments" "ProjectCode"                           
## [29] "ProtocolCode"                           "Replicate"                             
## [31] "SampleComments"                         "SampleDate"                            
## [33] "SampleID"                               "Sex"                                   
## [35] "SizeDescr"                              "SQLServerID"                           
## [37] "StationCode"                            "TagNumber"                             
## [39] "TisSource"                              "TissueCollectionComment"               
## [41] "TissueID"                               "TissueName"                            
## [43] "TissueWeight"                           "TotalCount"                            
## [45] "TotalLength"                            "UnitCompositeWeight"                   
## [47] "UnitLengthFish"                         "UnitTissueWeight"                      
## [49] "UnitWeightFish"                         "Weight"                                
## [51] "WeightSource"
## [1] 324
DRMP_2017_2019_FishComposite <- bind_rows(DRMP_17_FishComposite, DRMP_18_FishComposite, DRMP_19_FishComposite) %>%
  distinct(CompositeID, .keep_all=T) # rows are duplicated for some CompositeIDs

## SAVE MERGED DATA##
#Save to Fish Linkage folder
writexl::write_xlsx(list(TIResults=DRMP_2017_2019_TIResults, FishComposite=DRMP_2017_2019_FishComposite),
                    path='Reeval_Impl_Goals_Linkage_Analysis/Data/Fish/DRMP_2017_2019_Hg_Fish.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 16:06:24 PST"