README.TXT
eef 5/27/2024

This HydroShare resource provides supporting information for Fahrney, E.E.; Mays, D.C.; Newman, C.P. (2024), Systematic approach to prioritize wells for groundwater monitoring in the Arkansas River Headwaters Basin, Colorado, USA, Journal of HydrologyRegional Studies, https://doi.org/TBD. 

CONTENTS

1. R Script Tutorial 
2. R Script Files
3. Results

1. R SCRIPT TUTORIAL 

1.a. Tutorial

Software Download

1. Download R

We used https://mirror.las.iastate.edu/CRAN/ from Iowa State University, Ames, IA, USA, download for Windows. They recommend using the Comprehensive R Archive Network mirror closest to you: https://cran.r-project.org/mirrors.html. We used R 4.3.1 which can be found here if there are malfunctions with a current version of R: https://cran.r-project.org/bin/windows/base/old/

2. Download RStudio: https://posit.co/products/open-source/rstudio/

Folder Set-Up

1. On your computer or in cloud storage where 1.34 GB are available, create a folder to save the files below. In this tutorial, we will call that folder "PrioritizeWells".

2. From the R Script Files folder, download "RScript.R" and save it to the PrioritizeWells folder.

3. In the PrioritizeWells folder, create the following subfolders: inputs, outputs

4. Copy the input files listed below into the inputs folder.

5. In the outputs folder, create a folder titled Trial1. You will need 1.07 GB of storage for the outputs folder.

6. In the Trial1 folder, create the following folders (case must match exactly):

DataBySite, DataPaired, PredictiveWellMaps, RegressionPlots, ShapeFiles

7. Within the RegressionPlots folder, create the following folders (case must match exactly):

ProximalCorrelated, DistantCorrelatedContrasting, ProximalUncorrelated, NegativelyCorrelated

8. Within the ShapeFiles folder, create the following folders (case must match exactly):

GroupingsPolygons, SitesScored


Run RScript.R 

1. First, you will create a new project. Open RStudio. In the top right corner, click the dropdown arrow. Click New Project > Choose Folder. Choose your PrioritizeWells folder. If you created your PrioritizeWells folder in cloud storage but cannot access that folder through the Choose Folder browsing in R Studio, you will need to set up the PrioritizeWells folder in a different location. 

2. In RStudio, you should see 4 windows: the script, the environment/history, the files/plots, and the console. In the files/plots, choose the Files tab. 

3. You should see the files you have added to the PrioritizeWells folder. Open RScript.R.

5. In the script window, hold Control-A to select all. Press Control-Enter to run RScript.R.

6. Your PrioritizeWells output folder should populate with files as RScript.R runs.

Your Input

6. RScript.R will automatically ask for your input in several sections:

(Sections are designated by ####SECTION NAME------------------####. They can be quickly accessed through the drop-down menu in the bottom left of the main scripting window.) 

USER INPUT 1: Set the Trial Number to 1. If you change the trial number, you will need to add an additional output folder with subfolders.

USER INPUT 2: Set the total number of wells funded. Our number was 42. 

USER INPUT 3: You can choose to plot regressions for additional pairs not saved to the RegressionPlots folder. If desired, type YES. 

You will be prompted to enter the two letters for each well. Note these are case sensitive, so must be in all capital letters.

After Initial Run

1. You can select USER INPUT 3 section and press Control-Enter to plot additional regressions.

2. If you run from the beginning again or change RScript.R, consider creating a new folder for outputs with a new trial number.

3. You can comment out INSTALL PACKAGES section since packages only need to be installed once. Highlight the lines and press Shift-Control-C.

2. R_Script_Files

Number of files: 2 (including one subfolder).

RScript.R is the R script file described above.

Inputs FOLDER

Number of files: 5 (including one subfolder)

File name: aquifer_codes.csv
File description: Table of aquifer codes and descriptions
Source: USGS, 2023, email with Connor Newman
Records: 189 records
Column details:
1. aqfr_cd: Code for aquifer
2. Aquifer: Aquifer name

File name: ChaffeeCounty_tritium_data.csv
File description: Table of tritium results for wells in Chaffee County
Source: USGS, 2023, email with Connor Newman
Records: 27 records
Column details:
1. site_no: USGS site number
2. SAMPLE_START_DT: date of sample
3. PARM_NM: USGS parameter name, Tritium, wu is listed for all, where wu indicates water, unfiltered
4. result_pCi/L: tritium activity in pico-Curies/Liter

File name: PCA_summary_results.csv
File description: Table of water quality PCA results for wells in Chaffee County
Source: USGS, 2023, email with Connor Newman
Records: 23 records
Column details:
1. site_ID: USGS site number
2. alias: abbreviated USGS ID
3. group: from principal component analysis
4. groupPair: from principal component analysis
5. PC1: principal component 1
6. PC2: principal component 2
7. PC3: principal component 3
8. abs_sum: absolute value of PC1+PC2+PC3

File name: USGSPuebloSites.csv
File description: Table of currently monitored sites
Source: USGS, 2024, email with Connor Newman
Records: 12 records
Column details:
1. site_no: USGS site number
2. site_ID: expanded USGS site ID
3. alias: abbreviated USGS ID
4. Fahrney_ID: two letter ID created for this study
5. station_nm: USGS station name
6. site_type: type of USGS monitoring site, GW for groundwater is listed for all
7. lat: latitude
8. long: longitude
9. in_Pueblo_DS: YES indicates current monitoring is occurring

GIS_shape_files SUBFOLDER

This folder contains eight subfolders, each of which contain a variable number of files with various extensions that constitute a shapefile (for example, AllArkRiversChaffeeProject has the eight extensions .cpg, .dbf, .prj, .sbn, .sbx, .shp, .xml, and .shx). The extension .shp is the primary shapefile opened in GIS products. In addition,  the accompanying files are also needed. The column headings are not visible from the Hydroshare interface but will be visible when shapefile is opened in appropriate interface (i.e. R or with an attribute table in the GIS interface). RScript.R reads these files with the following syntax: st_read(dsn='inputs/GIS/folder_name', 'file_name'). European Petroleum Survey Group (EPSG) geodetic codes are included with the coordinate reference systems. Descriptions are not given for immaterial data columns.

Folder name: AllArkRiversChaffee26913
Root name of files: AllArkRiversChaffeeProject
Folder description: Lines for Arkansas River and South Arkansas River
Source: National Hydrography Dataset [WWW Document], 2023. The National Map, United States Geological Survey. URL https://apps.nationalmap.gov/downloader/#/ (accessed 20 January 2024).
Coordinate Reference System: North American Datum 1983, Universal Transverse Mercator 13N, EPSG 26913
Column names and descriptions:
1. permanent_: immaterial
2. fdate: immaterial
3. resolution: immaterial
4. gnis_id: immaterial
5. gnis_name: River name
6. lengthkm: Length of river stretch in kilometers
7. reachcode: immaterial
8. flowdir: immaterial
9. wbarea_per: immaterial
10. ftype: immaterial
11. fcode: immaterial
12. mainpatch: immaterial
13. innetwork: immaterial
14. visibility: immaterial
15. SHAPE_Leng: immaterial
16. Enables: immaterial
17. ObjectID: immaterial

Additional descriptions can be found in Moore et al., 2019, User's guide for the national hydrography dataset plus (NHD Plus) high resolution, Open-File Report 2019-1096, US Department of the Interior, US Geological Survey, https://doi.org/10.3133/ofr20191096.

Folder name: cb_2018_us_state_20m
Root name of files: cb_2018_us_state_20m
Folder description: Polygons of boundaries of states in the United States of America
Source: States: cb_2018_us_state_20m.zip [WWW Document], 2024. Cartographic Boundary Shapefiles, US Census. URL https://www.census.gov/geographies/mapping-files/2018/geo/carto-boundary-file.html (accessed 20 January 2024).
Coordinate Reference System: North American Datum 1983, EPSG 4269
Column names and descriptions:
1. STATESFP: immaterial
2. STATENS: immaterial
3. AFFGEOID: immaterial
4. GEOID: immaterial
5. STUSPS: immaterial
6. NAME: State name
7. LSAD: immaterial
8. ALAND: immaterial
9. AWATER: immaterial

Folder name: Colorado_City_Boundaries26913
Root name of files: Colorado_City_Boundaries_Project
Folder description: Polygons of municipalities in the state of Colorado
Source: Colorado City Boundaries [WWW Document], 2023. CDPHE Open Data, Colorado Department of Public Health and Environment. URL https://data-cdphe.opendata.arcgis.com/datasets/CDPHE::colorado-city-boundaries/about (accessed 22 October 2023).
Coordinate reference system: North American Datum 1983, Universal Transverse Mercator 13N, EPSG 26913
Column names and descriptions: 
1. OBJECTID: Numbers 1 through 458 for each row
2. GEOID10: Seven digit code specific to that polygon
3. NAME10: Name of municipality
4. NAMELSAD10: Name of municipality followed by designation as city or town

Folder name: Colorado_County_Boundaries
Root name of files: Colorado_County_Boundaries
Folder description: Polygons of county boundaries in the state of Colorado
Source: Colorado County Boundaries [WWW Document], 2023. CDPHE Open Data, Colorado Department of Public Health and Environment. URL https://data-cdphe.opendata.arcgis.com/datasets/CDPHE::colorado-county-boundaries/about (accessed 22 October 2023).
Coordinate Reference System: North American Datum 1983, EPSG 4269
Column names and descriptions:
1. OBJECTID: Numbers 1 through 64 for each row
2. COUNTY: County shortened name in all capital letters
3. FULL: Full name including the word "County"
4. LABEL: County shortened name
5. CNTY_FIPS: Federal Information Processing Standards county code
6. NUM_FIPS: Federal Information Processing Standards United States code
7. CENT_LAT: Latitude of polygon centroid
8. CENT_LONG: Longitude of polygon centroid
9. US_FIPS: Federal Information Processing Standards United States code

Folder name: Geology
Root name of files: ChaffeeGeology
Folder description: Polygons of boundaries of geologic units in Study Area formed by spatial join of geologic map with Colorado water district 11
Source: Stoeser, D.B., Green, G.N., Morath, L.C., Heran, W.D., Wilson, A.B., Moore, D.W., Van Gosen, B.S., 2005. Preliminary integrated geologic map databases for the United States Central States: Montana, Wyoming, Colorado, New Mexico, Kansas, Oklahoma, Texas, Missouri, Arkansas, and Louisiana, - the State of Colorado. Open-File Report 2005-1351.
Coordinate Reference System: North American Datum 1983, Universal Transverse Mercator 13N, EPSG 26913
Column names and descriptions:
1. OBJECTID: immaterial
2. DISTRICT: All geologic units are in Colorado water district 11
3. DIVISION: All geologic units are in Colorado water division 2
4. BASIN: All geologic units are in the Arkansas basin
5. NAME: All geologic units are in the basin named Arkansas-Headwaters to Salida
6. SHAPE_Leng: Length of District 11 in meters squared
7. SHAPE_Area: Area of District 11 in meters squared
8. AREA: Area of geologic unit
9. PERIMETER: Perimeter of geologic unit
10. COGEOL_DD_: immaterial
11. COGEOL_DD1: immaterial
12. ORIG_LABEL: Geologic unit abbreviation
13. SMGC_LABEL: immaterial
14. UNIT_LINK: immaterial
15. SOURCE: A coded reference citation indicating source material used (see co.txt).
16. UNIT_AGE: The geologic age from the source map used.
17. ROCKTYPE1: The predominant lithology found in the formation.
18. ROCKTYPE2: The second most predominant lithology in the formation.

Folder name: HUC_list
Root name of files: huc250k
Folder description: United States Geological Survey hydrologic unit polygons at 1:250,000 scale designated with 8 digits (HUC-8)
Source: Digital Spatial Data Sets: 1:250,000-scale Hydrologic Units (huc250k) [WWW Document], 2023. Hydrologic Unit Maps, Water Resources of the United States, United States Geological Survey. URL https://water.usgs.gov/GIS/huc.html (accessed 14 August 2023).
Coordinate Reference System: North American Datum 1927 Albers, EPSG 6267
Column names and descriptions:
1. AREA: hydrologic unit polygon area in meters squared
2. PERIMETER: hydrologic unit perimeter in meters
3. HUC250K_: internal identification numbers
4. HUC250K_ID: internal identification numbers
5. HUC_CODE: eight digit code assigned to hydrologic unit
6. HUC_NAME: name of hydrologic unit
7. REG: region 2-digit code for hydrologic unit
8. SUB: subregion 4-digit code for hydrologic unit
9. ACC: 6-digit code for hydrologic unit
10. CAT: 8-digit code for hydrologic unit

Folder name: Water_Districts
Root name of files: Water_Districts
Folder description: Polygons of Division of Water Resources water districts in the state of Colorado
Source: GIS Data by Category: District Boundaries [WWW Document], 2023. Colorados Decision Support Systems, Colorado Water Conservation Board / Division of Water Resources. URL https://cdss.colorado.gov/gis-data/gis-data-by-category (accessed 14 August 2023).
Coordinate Reference System: North American Datum 1983, Universal Transverse Mercator 13N, EPSG 26913
Column names and descriptions:
1. OBJECTID: Numbers 1 through 83 for each row
2. DISTRICT: Colorado water district number (1 through 80)
3. DIVISION: Colorado water division number (1 through 7)
4. BASIN: Colorado watershed name 
5. NAME: water district name
6. SHAPE_Leng: length of polygon in meters squared
7. SHAPE_Area: area of polygon in meters squared

Folder name: WaterDivisions
Root name of files: WtrDivs
Folder description: Polygons of Division of Water Resources water districts in the state of Colorado
Source: GIS Data by Category: Division Boundaries [WWW Document], 2023. . Colorados Decision Support Systems, Colorado Water Conservation Board / Division of Water Resources. URL https://cdss.colorado.gov/gis-data/gis-data-by-category (accessed 14 August 2023).
Coordinate Reference System: North American Datum 1983, Universal Transverse Mercator 13N, EPSG 26913
Column names and descriptions:
1. DIV: numbers 1 through 7 for each row
2. BASIN: Colorado watershed name 
3. Perimeter: polygon perimeter
4. Area: polygon area in square meters
5. Acres: polygon area in acres
6. Area_mi2: polygon area in square miles

3. RESULTS

Number of files: 8 (including 6 subfolders).

a.DataBySite FOLDER

Number of files: 133
Description: full groundwater elevation data for each well
File naming structure: "Data_AA.csv" where, for example, AA is the site code.
Records: Numbered 1 through length of record for that site.
Column details: 
1. Date: Year and month of groundwater level measurement
2. site_no: USGS site number
3. gwElev: groundwater elevation in meters
4. aqfr_cd: aquifer code if available
5. depth: well depth in meters
6. Aquifer: aquifer name, if available
7. Site: two-letter site code for this study
8. Mean: average groundwater elevation in meters
9. Deviation: groundwater elevation deviation from the mean in meters

b.DistanceTables FOLDER

Number of files: 2

File name: AllDistances.csv
Description: matrix of distances between each pair
Records: All sites AA through FC
Column details: 
Columns are all sites AA through FC. 

File name: DistanceAllSummary.csv
Description: table summarizing minimum and maximum distances from each well to another well
Records: numbers 1 through 133, the total number of sites
Column details: 
1. Site: two-letter site code for this study
2. Min_m: distance to nearest well in meters
3. Min_ft: distance to nearest well in feet
4. Min_km: distance to nearest well in kilometers
5. MinWith: name of nearest neighboring well
6. Max_m: distance to the well farthest from this well in meters
7. Max_ft: distance to the well farthest from this well in feet
8. Max_km: distance to the well farthest from this well in kilometers
9. MaxWith: name of well farthest from this well
10. Mean_m: mean distance from this well to all other wells in meters
10. Mean_ft: mean distance from this well to all other wells in feet
10. Mean_mi: mean distance from this well to all other wells in miles

c.DataPaired FOLDER

Number of files: 6191
Description: groundwater elevation measurements for pairs of wells with overlapping dates
File naming structure: "AA_AC_DataPaired.csv" where AA and AC are the site codes. Only one file exists for each pair. For instance, because "AA_AC_DataPaired.csv" exists, the complimentary file "AC_AA_DataPaired.csv" does not exist.
Records: Row numbers were created during the pairing in the script and are immaterial.
Column details:
1. SiteX: Two-letter code for well with Subtotal (initial score) greater than or equal to Well Y
2. SiteY: Two-letter code for well with Subtotal (initial score) less than or equal to Well X
3. SiteXSubtotal: Subtotal for well X
4. SiteYSubtotal: Subtotal for well Y
5. Date: year and month of groundwater elevation measurement
6. Dist: distance between wells in kilometers
7. SiteXgwElev: groundwater elevations in meters for well X
8. MeanX: average groundwater elevation for well X in meters based on the full dataset
9. DeviationX: groundwater elevation deviation from the mean for well X in meters 
10. SiteYgwElev: groundwater elevations in meters for well Y
11. MeanY: average groundwater elevation for well Y in meters based on the full dataset
12. DeviationY: groundwater elevation deviation from the mean for well Y in meters 

d.RegressionTables FOLDER

Number of files: 2

File name: AllRsMatrix.csv
Description: matrix of R2 values between wells
Records: All sites AA through FC
Column details: 
Columns are all sites AA through FC

File name: AllSitesRs.csv
Description: table summarizing regression analysis and well variables to compare
Records: numbers 1 through 9858
Column details: 
1. SiteX: Two-letter code for well with Subtotal (initial score) greater than or equal to Well Y
2. SiteY: Two-letter code for well with Subtotal (initial score) less than or equal to Well X
3. SiteXSubtotal: Subtotal for well X
4. SiteXGeol: geologic unit for well X
5. SiteXdepth: depth in meters for well X
6. SiteYSubtotal: Subtotal for well Y
7. SiteYGeol: geologic unit for well Y
8. SiteYdepth: depth in meters for well Y
9. Dist: distance between well X and well Y in kilometers
10. n: number of overlapping measurements
11. SiteXSkew: skew of paired site X groundwater elevations
12. SiteYSkew: skew of paired site Y groundwater elevations
13. StartDate: first year and month of overlapping data
14. EndDate: last year and month of overlapping data
15. Yint: y-intercept of linear regression model in meters
16. slope: slope of linear regression model
17. StdErr: standard error of slope of linear regression model
18. p.val: p-value is calculated in the linear regression (lm) function in R using two-sided t-distribution and the t-statistic.
19. R2: r-squared (coefficient of determination) 
20. R: Pearson's correlation coefficient (equal to the square root of R2)
21. ResidualSkew: skewness of residuals in linear regression model
22. ResidualMean: average of residual values in linear regression model
23. SiteXTotal: Total for well X equal to Subtotal plus s(W) (score of weighted degree)
24. SiteYTotal: Total for well Y equal to Subtotal plus s(W) (score of weighted degree)
25. SiteX_W: weighted degree (sum of R2 values for well X), used to prioritize wells in cases of equal Total
26. SiteY_W: weighted degree (sum of R2 values for well Y), used to prioritize wells in cases of equal Total

e.HydrographsAndRegressionPlots FOLDER

This folder contains five subfolders with details explained in Table 5 of main text.

File naming structure in each folder: "HydrographsAndRegressionPlots_AC_EA.jpg" where AC and EA are the site codes. Only one file exists for each pair. 
Description: In all plots, grey or black represents well X and orange represents well Y. Subplot A shows the two wells in the Upper Arkansas. Labels show the subtotal score for each well, the geologic unit as mapped by Stoeser et al. (2005), and the well depth in meters. Subplot B plots the groundwater elevation (GWE) versus time for each site. Subplot C plots GWE deviations from the mean (dev) versus time for each site for the months for which both wells had measurements, where means were calculated from the full dataset for each well. Subplot D plots GWE elevation for Well Y versus GWE for Well X. The linear regression of Y on X is shown in blue, and the grey band around the model represents the 95% confidence interval as calculated by predict() function in R. The p-value is based on the null hypothesis that the slope is zero.

f.PredictiveWellMaps FOLDER

Number of files: 95
Description: network maps showing all the connections to a single node (well) 
File naming structure: "MapNetwork_AA.jpg" where AC and EA are the site codes. Only one file exists for each pair. 
Network maps for all wells correlated to at least one other well with R2 values greater than or equal to 0.6. The edges were considered pointed in towards the well with the higher Subtotal. The total of all R2 values equals the weighted in-degree for that well. If correlated wells had equal scores, the edge was considered bidirectional. Predictive wells had weighted in-degrees in the top 75%. Gray lines are an abridged geologic map, so correlations among units may be discerned. R2 values are indicated by connecting line colors and thicknesses, with lighter, thicker lines representing higher values. The size of the circle represents Subtotal for each well. For each well with a network map, there is a subfolder containing the results for each well pair. Well pairs in these subfolders have identical plots shown in folder e.HydrographsAndRegressionPlots if they meet the filtering parameters in Table 5 in the main text.


g.ScoringCombined.csv

Description: Table of data and scores for all wells.
Records: numbers 1 through 133
Column naming convention: s_[variable] denotes the score for the variable, with 1 being least desirable and 3 being most desirable.
Column details: 
1. Current: Y if currently monitored, N if not
2. Site: two-letter site code for this study
3. x: distance to nearest well in kilometers
4. s_x: score for distance, with highest values scoring highest
5. n: number of measurements
6. s_n: score for number of measurements, with highest values scoring highest
7. y: number of years of record
8. s_y: score for period of record, with highest values scoring highest
9. mu: mean groundwater elevation in meters
10. s_mu: score for mean groundwater elevation, with highest values scoring highest
11. R: groundwater elevation range in meters
12. s_R: score for groundwater elevation range, with highest values scoring highest
13. d: depth of well in meters
14. s_d: score for depth of well, with highest values scoring highest
15. Aquifer: name of aquifer in which well was completed
16. a: number of wells in that aquifer
17. s_a: score for aquifer count, with lowest values scoring highest
18. GeolCode: USGS geologic unit code
19. Unit: mapped geologic unit in which well is located 
20. UNIT_AGE: geologic age of formation
21. ROCKTYPE1: The predominant lithology found in the formation.
22. ROCKTYPE2: The second most predominant lithology in the formation.
23. g: number of wells in that geologic unit
24. s_g: score for geology count, with lowest values scoring highest
25. T: tritium activity in picoCuries/Liter
26. s_T: score for tritium activity, with lowest values scoring highest
27. MinDate: oldest date in period of record
28. MaxDate: newest date in period of record
29. s_D: score for drought years, with inclusion of 2000-2012 scoring highest
30. Q: geochemical signature rating (sum from Principal Component Analysis)
31. Qmax: maximum value for each geochemical signature grouping
32. s_Q: score for water quality (geochemical signature)
33. v: notes of other priority considerations
34. s_v: score for other priority considerations
35. Subtotal: total of all previous scores
36. Deg: number of "in" degrees for each well
37. SumR2: sum of r-squared values linked "in" to each higher or equal scoring well
38. s_SumR2: score of sum of r-squareds, with highest values scoring highest
39. Total: Subtotal + s_SumR2
40. GroupID: Well grouping number. Groupings are wells within the first quartile for distance. Numbers 1-23 are grouped. Number 24 are ungrouped wells.
41. AvgSkew: Mean of groundwater elevation skewness from all datasets paired with others for that well
42. Predictive: Y for wells with s_SumR2=3, otherwise N

h.SitesFinal.csv lists the prioritized network. Details match those of g.ScoringCombined.csv.
W:\cudenver\students\Fahrney\readme-2024-04-02.docx 5/9/2024	1 of 2