Suppliment
Description
“The dataset includes raw water‑quality measurements and derived ecological indicators for lakes, ponds, and reservoirs in the Winooski River Basin. Variables include chlorophyll‑a, dissolved oxygen, Secchi depth, total nitrogen, and total phosphorus, along with Carlson Trophic State Index metrics, nutrient ratios, nutrient‑limitation categories, and risk quotients for nitrogen, phosphorus, and chlorophyll‑a. Each row corresponds to a single waterbody and includes geographic coordinates and waterbody classification. Derived variables were computed using the procedures outlined in the manuscript, while raw measurements remain unaltered.”
Files
Steps to reproduce
1. Import the raw monitoring dataset Load the original long‑format water‑quality file containing: ActivityStartDate (sampling date) MonitorLocationName (waterbody/site name) CharacteristicName (e.g., Chlorophyll‑a, TDS, Temp, DO, TN, TP) ResultMeasureValue (numeric measurement) No filtering, imputation, or modification is applied at this stage. 2. Reshape the dataset from long format to wide format Pivot the dataset so that each CharacteristicName becomes its own column, while retaining the sampling date and waterbody identity. This produces one row per waterbody with columns for: Chlorophyll‑a Dissolved oxygen Secchi depth Total nitrogen Total phosphorus Temperature Any additional measured parameters This step ensures that all variables align correctly for trophic and risk calculations. 3. Calculate Carlson Trophic State Index (TSI) metrics Using standard formulas: TSI‑Chl from chlorophyll‑a TSI‑TP from total phosphorus TSI‑SD from Secchi depth These indices quantify trophic state using independent indicators of productivity and water clarity. 4. Compute nutrient ratios and limitation categories Calculate: N:P ratio (molar or mass‑based depending on manuscript method) Assign limitation categories (e.g., phosphorus‑limited, nitrogen‑limited, co‑limited) based on established threshold values. This step identifies which nutrient most strongly constrains algal growth. 5. Compute Risk Quotients (RQ) for key variables For each waterbody, calculate: RQ(P) — phosphorus risk quotient RQ(N) — nitrogen risk quotient RQ(Chl) — chlorophyll‑a risk quotient Each RQ is computed as: 𝑅 𝑄 = Observed value Threshold value Thresholds are defined in the manuscript based on ecological criteria. 6. Assign ecological risk levels Using the calculated RQs: Determine Risk Level for each variable Compute RQmax (the highest of the three RQs) Assign a New Risk Level based on RQmax thresholds (e.g., negligible, exceedance, high exceedance) This step synthesizes multiple indicators into a single ecological risk classification. 7. Add geographic and waterbody descriptors Attach: Latitude Longitude Waterbody type (lake, pond, reservoir) These fields support spatial interpretation and mapping. 8. Export the final analysis‑ready dataset Save the completed table containing: Raw measurements Derived trophic metrics Nutrient ratios Limitation categories Risk quotients Final ecological risk classifications The exported file is the exact dataset used in the manuscript’s analyses and figures.