Whitefish Lake Institute Data Resource
Abstract
This resource contains data collected by the Whitefish Lake Institute (WLI) as well as R code used to compile and conduct quality assurance on the data. This resource reflects joint publication efforts between WLI and the Montana State University Extension Water Quality (MSUEWQ) program. All data included here was uploaded to the National Water Quality Portal (WQX) in 2022. It is the intention of WLI to upload all future data to WQX and this HydroShare resource may also be updated in the future with data for 2022 and forward. 
Data Purpose:
The Data folder of this resource holds the final data products for the extensive dataset collected by WLI between 2009 and 2021. This folder is likely of interest to users who want data for research and analysis purposes. This dataset contains physical water parameter field data collected by Hydrolab MS5 and DS5 loggers, including water temperature, specific conductance, dissolved oxygen concentration and saturation, barometric pressure, and turbidity. Additional field data that needs further quality assurance prior to use includes chlorophyll a, ORP, pH, and PAR. This dataset also contains water chemistry data analyzed at certified laboratories including total nitrogen, total phosphorus, nitrate, orthophosphate, total suspended solids, organic carbon, and chlorophyll a. The data folder also includes R scripts with code for a few examples of data visualization. This dataset can provide insight to water quality trends in lakes and streams of northwestern Montana over time.

Data Summary:
During the time-period, WLI collected water quality data for 63 lake sites and 17 stream and river sites in northwestern Montana under two separate monitoring projects. The Northwest Montana Lakes Network (NMLN) project currently visits 41 lake sites in Northwestern Montana once per summer. Field data from Hydrolabs are collected at discrete depths throughout a lake's profile, and depth integrated water chemistry samples are collected as well. The Whitefish Water Quality Monitoring Project (WWQMP) currently visits two sites on Whitefish Lake, one site on Tally Lake, and 11 stream and river sites in the Whitefish Lake and Upper Whitefish River watersheds monthly between April and November. Field data is collected at one depth for streams and many depths throughout the lake profiles, and water chemistry samples are collected at discrete depths for Whitefish Lake and streams. The final dataset for both programs includes over 112,000 datapoints of data passing quality assurance assessment and an additional 72,000 datapoints that would need further quality assurance before use. 
Workflow Purpose:
The Workflow folder of this resource contains the raw data, folder structure, and R code used during this data compilation and upload process. This folder is likely of interest to users who have similar datasets and are interested in code for automating data compilation or upload processes. The R scripts included here have code to stitch together many individual Hydrolab MS5 and DS5 logger files as well as lab electronic data deliverables (EDDs), which may be useful for users who are interested in compiling one or multiple seasons' worth of data into a single file. Reformatting scripts format data to match the multi-sheet excel workbook format required by the Montana Department of Environmental Quality for uploads to WQX, and may be useful to others hoping to automate database uploads.

Workflow Summary:
Compilation code in the workflow folder compile data from its most original forms, including Hydrolab sonde export files and lab EDDs. This compilation process includes extracting dates and times from comment fields and producing a single file from many input files. Formatting code then reformat the data to match WQX upload requirements, which includes generating unique activity IDs for data collected at the same site, date, and time then linking these activity IDs with results across worksheets in an excel workbook. Code for generating all quality assurance figures used in the decision-making process outlined in the Quality Assurance Document and resulting data removal decisions are included here as well. Finally, this folder includes code for combining data from the separate programs and reformatting it again to be more convenient for the HydroShare resource.

Contact
For questions regarding data collection or possible research collaboration, contact the Whitefish Lake Institute at  info@whitefishlake.org

Software/Hardware
This code was run in January of 2023 using R version 4.2.2 on a PC running Windows 10 64-bit operating system. R packages used for analysis include stringr, plyr, readxl, openxlsx, and lubridate. 

Rights
R packages used in the code (stringr, plyr, readxl, openxlsx, lubridate) all carry GPL-2, GPL-3, or MIT licenses.


Folder Structure and File Description
Data: Folder with Final Data and Plotting Examples
* 1_Data: Folder with 15 years of water quality data from two programs administered by the Whitefish Lake Institute
o CleanData.csv: All quality assured field and lab data from both WWQMP and NMLN programs. This file combines fields from the project, station, activity, and results sheets from the WQX upload into one file. 
o DataNeedingQA.csv: Field data for pH, ORP, chlorophyll a, and PAR. This dataset includes parameters with potential sensor failures (pH, ORP) and result values that are useful relevant to one another within a lake profile but not as absolute values (Chlorophyll a, PAR). This file combines data from both programs and relevant fields from project, station, activity, and results sheets. For more information on the status of this data, see the file titled Whitefish Lake Institute 2007-2021 Water Data Compilation and Quality Assurance. 
o DuplicateAndBlankData.csv: Data for all lab quality control duplicate and blank samples. This data is from both programs and includes relevant fields from project, station, activity, and results sheets.
o Sites.csv: Additional site data for sites in both programs.
* 2_Code: Folder with code for initial data visualization
o PlottingExample.R: Script for initial exploration of data. Renames columns, subsets data to parameters or sites, and produces visualizations that are saved to the 3_Plots folder.
* 3_Plots: Folder with plots generated by 2_Code
o Streams_TN_Boxplots.png: Boxplot for comparing total nitrogen across all stream and river sites.
o TETRAU_DO_ByDepth.png: Comparison of dissolved oxygen concentration in Tetrault Lake by depth. Symbols differentiate between summer and fall monitoring events.
o TNTP_Ratio_MARYRON-W.png: Calculates molar TN:TP ratio and plots over time for a Mary Ronan West monitoring site. Includes approximate thresholds for phosphorus and nitrogen limitation as dashed lines.
o TPvsTotalSuspendedSolidsBEAV-CRK-RR.png: Regression between total suspended solids and total phosphorus concentrations in Beaver Creek.

Workflow: Folder with Data Compilation, Formatting, and Quality Assurance Process
* 1_RawData: Folder with raw input data and formatting templates
o EnergyEDDs: folder with 193 excel files in 4 sub-folders containing all lab results from Energy Lab 
o FLBSEDDs: folder with 4 csv files with all lab results from Flathead Lake Biological Station Lab
o LakesHydrolabs: a folder with 61 subfolders and 1174 excel files containing all logger data from Hydrolabs used for lake monitoring
o TributariesHydrolabs: a folder with 157 subfolders and 1060 excel files containing all logger data from Hydrolabs used for stream monitoring
o MT-eWQX_EDD_20170221.xlsx: an excel file with the Montana DEQs template for WQX uploads, provided by MDEQ for general use by partnering data providers
o MT-eWQX_EDD_ReferenceValues_20190724.xls: an excel file with the Montana DEQs accepted values used in filling EDDs,provided by MDEQ for general use by partnering data providers 
o 2014_FLBS_Reformatting_21-12-16_mr.csv: File for lab data from FLBS in 2014. An EDD for this year could not be generated from the lab, but the lab used the same methods and reporting limits as for subsequent years. Data (not in EDD format) and known metadata were used for the manual creation of an EDD in excel.
o DifficultFiles_TribHydrolab_21-07-27_mr.csv: Hydrolab files that did not match the format required for the compilation loop in 3_Compile_HydrolabTribs.R. These files were missing setup and recovery information. Data for these few files was configured manually in excel.
o Project_Station_ForWQX.xlsx: Excel file with information on projects and stations for both NMLN and WWQMP project.
* 2_Code: Folder with all code used in this process
o 3_Compile_EnergyEDD.R: Uses EnergyEDDs folder as input data; compiles multiple lab EDDs into one file for each program (NMLN and Baseline/WWQMP); generates Energy_EDD_Baseline_22-01-11_mr.csv and Energy_EDD_NMLN_22-01-17_mr.csv
o 3_Compile_FLBSEDD.R: Uses FLBSEDDs folder as input data; compiles multiple lab EDDs into one file for each program; generates FLBS_NMLNEDD_21-12-12_mr.csv and FLBSBaselineEDD_22-01-17_mr.csv
o 3_Compile_HydrolabsLakes.R: Uses LakesHydrolabs folder as input data; compiles approximately 1100 hydrolab files for all lakes into a single file; generates LakeHydrolabCompile_22-01-28_1530_mr.csv
o 3_Compile_HydrolabsTribs.R: Uses TributariesHydrolabs folder as input data; compiles approximately 1000 hydrolab files for all streams into a single file; generates TributariesHydrolabCompile_21-12-07_mr.csv
o 4_WQX_BaselineLabs.R: Uses EnergyEDD_Baseline_22-01-11_mr.csv, FLBSBaselineEDD_22-01-17.csv, 2014_FLBS_Reformatting_21-12-16.csv, and Project_Station_ForWQX.xlsx as input; reformats lab data for Baseline program to match WQX upload format. This requires building separate excel worksheets for data relevant to the project, individual monitoring sites, activities conducted at the same site, date, and time, and the overall results. This script generates activity IDs based on site, date, and time, fills metadata for different activities (including collection methods, data type, and organization data), and links each datapoint in results to a specific activity ID. This script produces WWQMP_Lab_EDD_22-02-16_mr.xlsx and CowLabData.csv.
o 4_WQX_NMLNLab.R: Uses EnergyEDD_NMLN_22-01-17_mr.csv, FLBS_NMLNEDD_21-12-12_mr.csv, and Project_Station_ForWQX.xlsx as input; reformats lab data for NMLN program to match WQX upload format (See 4_WQX_Baseline description above for more detail); produces NMLN_Lab_EDD_22-02-16_mr.xlsx and 2011LakesLabData.csv.
o 4_WQX_HydrolabLake.R: Uses LakeCompile_22-01-18_15.csv as input; reformats field data for lakes to match WQX upload format (See 4_WQX_Baseline description above for more detail); produces NMLN_Field_EDD_22-06-30_mr.xlsx, WWQMPLakes_Field_Activity_22-02-15_mr.csv, WWQMPLakes_Field_Result_22-02-15_mr.csv, and 2011LakesFieldData.csv. This script must be run before 4_WQX_HydrolabStream.
o 4_WQX_HydrolabStream.R: Uses TributariesHydrolabCompile_21-12-07_mr.csv, WWQMPLakes_Field_Activity_22-02-15_mr.csv, and WWQMPLakes_Field_Result_22-02-15_mr.csv as inputs; reformats field data for streams to match WQX upload format and merges with WQX format baseline lakes data; produces WWQMP_Field_EDD_22-06-28_mr.xlsx and CowFieldData.csv.
o 5_QA_BaselineLab.R: Uses WWQMP_Lab_EDD_22-02-16_mr.xlsx as input; generates all quality assurance figures used in the Quality Assurance Document for WWQMP lake and stream lab parameters; saves .pngs to the TallyWF and Streams subfolders in folder 5.
o 5_QA_NMLNField.R: Uses NMLN_Field_EDD_22-06-30_mr.xlsx as input; generates all quality assurance figures used in the Quality Assurance Document for NMLN lakes field parameters; saves .pngs to the NMLNLakes subfolder in folder 5.
o 5_QA_NMLNLab.R: Uses NMLN_Lab_EDD_22-02-16_mr.xlsx as input; generates all quality assurance figures used in the Quality Assurance Document for NMLN lakes lab parameters; saves .pngs to the NMLNLakes subfolder in folder 5.
o 5_QA_StreamsField.R: Uses WWQMP_Field_EDD_22-06-28_mr.xlsx as input; generates all quality assurance figures used in the Quality Assurance Document for WWQMP streams field parameters; saves .pngs to the Streams subfolder in folder 5.
o 5_QA_TallyWhitefishField: Uses WWQMP_Field_EDD_22-06-28_mr.xlsx as input; generates all quality assurance figures used in the Quality Assurance Document for WWQMP lakes field parameters; saves .pngs to the TallyWF subfolder in folder 5.
o 6_Final_WQXField: Uses WWQMP_Field_EDD_22-06-28_mr.xlsx and NMLN_Field_EDD_22-06-30_mr.xlsx as input; removes data based on quality assurance decisions; produces final field data WQX upload for both programs (WWQMP_Field_EDD_22-08-18_mr.xlsx and NMLN_Field_EDD_22-08-17_mr.xlsx) as well as files with no quality assurance, raw data files (NMLN_RawFileOnlyData_22-08-17_mr.csv and WWQMP_RawDataFile_22-08-22_mr.csv). 
o 6_Final_WQXField: Uses WWQMP_Lab_EDD_22-02-16_mr.xlsx and NMLN_Lab_EDD_22-02-16_mr.xlsx as input; removes data based on quality assurance decisions; produces final lab data WQX upload for both programs (WWQMP_Lab_EDD_22-06-18_mr.xlsx and NMLN_Lab_EDD_22-06-16_mr.xlsx). 
o 7_Final_HydroShareUpload: Uses files in 6_FinalResults folder as well as 2011LakesLabData.csv, 2011LakesFieldData.csv, CowLabData.csv, and CowFieldData.csv as input; condenses multiple Excel worksheets from the WQX format (Activity, Results) into a single file for the streamlined flat data file uploaded to this resource under the Data folder and merges lab and field data; fills 7_HydroShareResults folder.
* 3_CompilationResults: Folder with intermediate data files after initial processing
o DataArchive: Folder with initial data compilations before site ID merge
* EnergyEDDCompilation_21-11-14_mr.csv: Initial compilation of all data from Energy labs. Includes data not being uploaded or that have already been uploaded to WLMP-COWCR or WHITEF project IDs. From this compilation, the EnergyEDDSites_21-12-16_mr.csv list of all site names used over time was generated.
* FLBSEDDCompilation_21-11-30_mr.csv: Initial compilation of all data from FLBS labs. This initial EDD includes data for very short-term sites that were not assigned Site IDs and that will not be included in future steps. From this compilation, the FLBSBaselineSites_21-12-16_mr.csv and FLBSNMLN_Sites_22-01-14_mr.csv lists of all site names used over time were generated.
* LakeCompile_22-01-08_1206_mr.csv: Initial compilation of all hydrolab logger data for lakes. This initial EDD includes data for very short-term sites that were not assigned Site IDs and that will not be included in future steps. From this compilation, the HydrolabLakeSites_22-01-28_mr.csv was generated.
* TribHydrolabCompile_21-11-15_mr.csv: Initial compilation of all hydrolab logger files for streams. From this compilation, the TribSites_21-11-15_mr.csv list of site names was generated.
o Sites: Folder with files aligning site names used over time with site IDs
* EnergyEDDSites_21-12-16_mr.csv: List of all unique site names from EnergyEDDCompilation_21-11-14_mr.csv. 
* EnergyEDDSites_Edited_21-12-16_mr.csv: All site names matched with site IDs. Adding site IDs, activity type, and program was done manually in excel.
* FLBSBaseline_Sites_22-01-14_mr.csv: List of all unique site names from FLBSEDDCompilaton_21-11-30_mr.csv for the Baseline project.
* FLBSBaseline_Sites_Edited_22-01-14_mr.csv: All site names matched with site IDs. Adding site IDs and activity type was done manually in excel.
* FLBSNMLN_Sites_22-01-14_mr.csv: List of all unique site names from the FLBSEDDCompilation_21-11-30_mr.csv for the NMLN project.
* FLBSNMLN_Sites_Edited_22-01-14_mr.csv: All site names matched with site IDs. Adding site IDs and activity type was done manually in excel.
* HydrolabLakeSites_22-01-28_mr.csv: List of unique site names generated from LakeCompile_22-01-08_1206_mr.csv.
* HydrolabLakeSites_Edited_22-01-28.csv: All site names matched with site IDs. Adding site IDs was done manually in excel.
* TribSites_21-11-15_mr.csv: List of unique site names generated from TribHydrolabCompile_21-11-15_mr.csv. 
* TribSites_Edited_21-11-15_mr.csv: All site names matched with site IDs. Adding site IDs was done manually in excel.
o EnergyEDD_Baseline_22-01-11_mr.csv: Final compilation of Energy Lab data for Baseline project. Includes activity dates, times, and depths extracted from comment fields or site names. This file is used as an input to 4_WQX_EnergyBaseline.R code.
o EnergyEDD_NMLN_22-01-17_mr.csv: Final compilation of Energy Lab data for NMLN project. Includes activity dates, times, and depths extracted from comment fields or site names. This file is used as an input to 4_WQX_EnergyNMLN.R code.
o FLBS_NMLNEDD_21-12-12_mr.csv: Final compilation of FLBS Lab data for NMLN project. Includes activity dates, times, and depths extracted from comment fields or site names. This file is used as an input 4_WQX_FLBSNMLN.R code.
o FLBS_BaselineEDD_22-01-17_mr.csv: Final compilation of FLBS Lab data for Baseline project. Includes activity dates, times, and depths extracted from comment fields or site names. This file is used as an input 4_WQX_FLBSBaseline.R code.
o LakeHydrolabCompile_22-01-18_1530_mr.csv: Final compilation of hydrolab field data for lakes in both projects. This file is used as an input to 4_WQX_HydrolabLakes.R code.
o TributariesHydrolabCompile_21-12-07_mr.csv: Final compilation of hydrolab field data for streams (Baseline project only). This file is used as an input to 4_WQX_HydrolabStream.R code.
* 4_FormattingResults: Folder with datafiles compiled and formatted for upload to WQX subsequently used for final quality assurance
o 2011LakesFieldData.csv: Field data for NMLN lakes in 2011 formatted as WQX upload results sheet. This data has been previously uploaded to WQX under NWMTLVMN project but will be included in the HydroShare resource. This file is used as an input to 7_Final_HydroShareUpload.R code.
o 2011LakesLabData.csv: Lab data for NMLN lakes in 2011 formatted as WQX upload results sheet. This data has been previously uploaded to WQX under NWMTLVMN but will be included in the HydroShare resource. This file is used as an input to 7_Final_HydroShareUpload.R code.
o CowFieldData.csv: Field data for Cow Creek (COW-CRK-PA) between 2014 and 2020 formatted as WQX upload results sheet. This data has been previously uploaded to WQX under WLMP-COWCR project but will be included in the HydroShare resource. This file is used as an input to 7_Final_HydroShareUpload.R code.
o CowLabData.csv: Lab data for Cow Creek (COW-CRK-PA) between 2014 and 2020 formatted as WQX upload results sheet. This data has been previously uploaded to WQX under WLMP-COWCR project but will be included in the HydroShare upload. This file is used as an input to 7_Final_HydroShareUpload.R code.
o NMLN_Field_EDD_22-06-30_mr.xlsx: Reformatting of NMLN Hydrolab data to match WQX requirement. Project and station sheets are from Project_Stations_ForWQX in 1_RawData folder. Activity sheet holds information on sample collection date, time, method, and organization. Results sheet holds field results. Formatting and producing an excel workbook were done by 4_WQX_HydrolabLakeR. This is used as an input to 5_QA_NMLNField.R and 6_Final_WQXField.R.
o NMLN_Lab_EDD_22-02-16_mr.xlsx: Reformatting of NMLN Lab EDD to match WQX requirement. Project and station sheets are from Project_Stations_ForWQX in 1_RawData folder. Activity sheet holds information on sample collection date, time, method, and organization. Results sheet holds lab results and metadata. Formatting and producing an excel workbook were done by 4_WQX_NMLNLab.R. This is used as an input to 5_QA_NMLNLab.R and 6_Final_WQXLab.R.
o WWQMP_Field_EDD_22-06-28_mr.xlsx: Reformatting of WWQMP hydrolab data to match WQX requirement. Data is from both stream hydrolab compilation, and the Result and Activity sheets produced by 4_WQX_HydrolabLake.R. Project and station sheets are from Project_Stations_ForWQX in 1_RawData folder. Activity sheet holds information on sample collection date, time, method, and organization. Results sheet holds hydrolab data. Formatting and producing an excel workbook were done by 4_WQX_HydrolabStream.R. This is used as an input to 5_QA_StreamsField.R, 5_QA_TallyWhitefishField.R, and 6_Final_WQXField.R.
o WWQMP_Lab_EDD_22-02-16_mr.xlsx: Reformatting of Energy Lab EDD to match WQX requirement. Project and station sheets are from Project_Stations_ForWQX in 1_RawData folder. Activity sheet holds information on sample collection date, time, method, and organization. Results sheet holds lab results and metadata. Formatting and producing an excel workbook were done by 4_WQX_BaselineLabs.R. This is used as an input to 5_QA_BaselineLab.R and 6_Final_WQXLab.R.
o WWQMPLakes_Field_Activity_22-02-15_mr.csv: File of WWQMP program lake data produced by 4_WQX_HydrolabLake.R for the WQX style Activity worksheet. This is combined with WWQMP streams Activity data in 4_WQX_HydrolabStreams.R.
o WWQMPLakes_Field_Result_22-02-15_mr.csv: File of WWQMP program lake data produced by 4_WQX_HydrolabLake.R for the WQX style Result worksheet. This is combined with WWQMP streams Result data in 4_WQX_HydrolabStreams.R.
* 5_QAResults: Plots used for data quality assurance
o NMLNLakes: Folder with 30 .png files for all quality assurance plots for NMLN program. All plots are time series for a given parameter with symbols representing different monitoring sites. These were used for assessing patterns in data and identifying concerning points or trends. Some files in this folder were not included in the Quality Assurance document because they had very few data points and therefore did not provide helpful insights.
o Streams: Folder with 24 .png files of all quality assurance plots for WWQMP program streams. All plots are time series for a given parameter with symbols representing different monitoring sites. These were used for assessing patterns in data and identifying concerning points or trends. 
o TallyWF: Folder with 38 .png files of all quality assurance plots for WWQMP program lakes. Most plots are time series by parameter with symbols representing different monitoring sites. Additional plots include regression between parameters. These were used for assessing patterns in data and identifying concerning points or trends. Some files in this folder were not included in the Quality Assurance document because they had very few data points and therefore did not provide helpful insights.
* 6_FinalResults: Data files uploaded to WQX 
o NMLN_Field_EDD_22-08-17_mr.xlsx: Final WQX upload for field data for NMLN program. 
o NMLN_Lab_EDD_22-06-16_mr.xlsx: Final WQX upload for lab data for NMLN program.
o NMLN_RawFileOnlyData_22-08-17_mr.csv: Field data without quality assurance work for NMLN program that will need QA before use. This file was not included in the WQX upload but is used for HydroShare resource 
o WWQMP_Field_EDD_22-08-18_mr.xlsx: Final WQX upload for field data for WWQMP program. 
o WWQMP_Lab_EDD_22-06-16_mr.xlsx: Final WQX upload for lab data for WWQMP program. 
o WWQMP_RawDataFile_22-08-22_mr.csv: Field data without quality assurance work for WWQMP program that will need QA before use. This file was not included in WQX upload but is used for HydroShare resource.
* 7_HydroShareResults: Data files uploaded to HydroShare
o CleanData.csv: All quality assured field and lab data from both WWQMP and NMLN programs. This file combines fields from project, station, activity, and results sheets for WQX upload into one file. It also includes 2011 lake data and Cow Creek data previously uploaded to WQX. This is the data appearing in 1_Data file in the top-level Data folder of this resource. 
o DataNeedingQA.csv: Field data for pH, ORP, chlorophyll a, and PAR. This data includes potential sensor failures and result values that are useful within a lake profile but not as absolute values. This file combines data from both programs and relevant fields from project, station, activity, and results sheets. This is the data appearing in 1_Data file in the top-level Data folder of this resource.
o DuplicateAndBlankData.csv: Data for all lab quality control duplicate and blank samples. This data is from both programs and includes relevant fields from project, station, activity, and results sheets. This is the data appearing in 1_Data file in the top-level Data folder of this resource.
o Sites.csv: Additional site data for sites in both programs. This is the data appearing in 1_Data file in the top-level Data folder of this resource.

Related Resources
National Water Quality Monitoring Council, 2023, Water Quality Portal, accessed 01, 25, 2023, NMLN Project Results, https://doi.org/10.5066/P9QRKUVJ.
National Water Quality Monitoring Council, 2023, Water Quality Portal, accessed 01, 25, 2023, WWQMP Project Results, https://doi.org/10.5066/P9QRKUVJ.