Project 2 — Water Quality

Author

Patricio Romero

Published

October 12, 2026

Introduction

For Project 2, I reviewed the datasets presented in the Week 5 Discussion 5A posts. These posts were used to identify three different datasets that could be prepared and analyzed independently.

This report focuses on the Water Quality dataset. The dataset contains water chemistry measurements that can be used to examine environmental conditions and compare different water quality parameters.

I selected this dataset because water quality is an important environmental topic. The data may help identify differences in chemical measurements across sampling locations.

Dataset Selection

The Water Quality dataset was selected as the second of the three datasets required for Project 2.

Discussion 5A was used to identify the dataset. The original CSV file will be preserved, and the tidying, transformation, and analysis will be completed as part of Project 2.

The objective is to organize the water chemistry measurements into a tidy structure that will make the data easier to compare and analyze.

Data Source

Dataset: Water Quality — Chemistry Data
Original file: Chemistry_Data_Wide_20250715.csv
File format: CSV
Dataset category: Environmental and Water Quality Data

The original CSV file is stored in:

Data /Chemistry_Data_Wide_20250715.csv

The analysis uses the original Water Quality chemistry dataset, stored as Chemistry_Data_Wide_20250715.csv. The dataset contains water chemistry measurements collected at different sampling locations.

Approach

The purpose of this analysis is to compare water chemistry measurements across different sampling locations and identify which parameters show the greatest variation.

The original data will be imported from the CSV file. I will use dplyr to select the required variables, standardize column names, review missing values, and prepare the data for transformation.

The original wide-format dataset will be reviewed to identify the columns containing water chemistry measurements. I will then use pivot_longer() from the tidyr package to transform the measurement columns into a tidy structure.

The transformed dataset will organize the measurements into columns representing the measurement parameter and its recorded value. All analysis and visualizations will use the transformed dataset.

Business Questions

This analysis will address two questions:

  1. How do water chemistry measurements vary across different sampling locations?
  2. Which water chemistry parameters show the greatest variation?

Planned Analysis

The analysis will include:

  • A review of the available water chemistry parameters.
  • A comparison of water chemistry measurements across sampling locations.
  • A calculation of summary statistics for selected chemistry parameters.
  • An examination of which parameters show the greatest variation.
  • Two or three clearly labeled visualizations created with ggplot2.
  • A brief interpretation of the principal findings.

Expected Outcome

The analysis will show how water chemistry measurements differ across the available sampling locations and identify which parameters have the greatest variation.

It will also demonstrate how transforming a wide-format dataset into a tidy structure makes environmental data easier to organize, compare, and analyze.

Data Loading and Initial Exploration

The first step is to load the Water Quality dataset and review its structure.

This helps identify the available columns, water chemistry measurements, and missing values.

Code 
# Load the required packages
library(readr)
library(dplyr)
library(tidyr)
library(ggplot2)

# Import the original dataset
water <- read_csv(
  "Data /Chemistry_Data_Wide_20250715.csv",
  show_col_types = FALSE
)

# Check the number of rows and columns
dim(water)
[1] 3014   51
Code 
# Display column names
names(water)
 [1] "GroupCode"     "GroupName"     "UnitCode"      "UnitName"     
 [5] "SubUnitCode"   "SubUnitName"   "SiteCode"      "SiteName"     
 [9] "SiteType"      "EventDate"     "EventCode"     "Project"      
[13] "QCtype"        "LabCode"       "SampleTime"    "SampleDepth_m"
[17] "SampleType"    "pH_Lab"        "pH_Lab_Method" "AppColor_PCU" 
[21] "AppColorFlag"  "ANC_ueqL"      "ANCFlag"       "Cl_ueqL"      
[25] "ClFlag"        "SO4_ueqL"      "SO4Flag"       "NO3_ueqL"     
[29] "NO3Flag"       "NO2+NO3_mgL"   "NO2+NO3Flag"   "NO2_mgL"      
[33] "NO2Flag"       "TotDissN_mgL"  "TotDissNFlag"  "NH3_mgL"      
[37] "NH3Flag"       "TN_mgL"        "TNFlag"        "TP_ugL"       
[41] "TPFlag"        "TotDissP_ugL"  "TotDissPFlag"  "PO4_ugL"      
[45] "PO4Flag"       "ChlA_ugL"      "ChlAFlag"      "DOC_mgL"      
[49] "DOCFlag"       "Comments"      "IsEventCUI"
Code 
# Preview the data
head(water, 5)
# A tibble: 5 × 51
  GroupCode GroupName         UnitCode UnitName SubUnitCode SubUnitName SiteCode
  <chr>     <chr>             <chr>    <chr>    <chr>       <chr>       <chr>   
1 NETN      Northeast Temper… ACAD     Acadia … <NA>        <NA>        ACABIN  
2 NETN      Northeast Temper… ACAD     Acadia … <NA>        <NA>        ACABIN  
3 NETN      Northeast Temper… ACAD     Acadia … <NA>        <NA>        ACABIN  
4 NETN      Northeast Temper… ACAD     Acadia … <NA>        <NA>        ACABIN  
5 NETN      Northeast Temper… ACAD     Acadia … <NA>        <NA>        ACABIN  
# ℹ 44 more variables: SiteName <chr>, SiteType <chr>, EventDate <date>,
#   EventCode <dbl>, Project <chr>, QCtype <chr>, LabCode <chr>,
#   SampleTime <time>, SampleDepth_m <dbl>, SampleType <chr>, pH_Lab <dbl>,
#   pH_Lab_Method <chr>, AppColor_PCU <dbl>, AppColorFlag <chr>,
#   ANC_ueqL <dbl>, ANCFlag <chr>, Cl_ueqL <dbl>, ClFlag <chr>, SO4_ueqL <dbl>,
#   SO4Flag <chr>, NO3_ueqL <dbl>, NO3Flag <chr>, `NO2+NO3_mgL` <dbl>,
#   `NO2+NO3Flag` <chr>, NO2_mgL <dbl>, NO2Flag <chr>, TotDissN_mgL <dbl>, …
Code 
# Check data types
glimpse(water)
Rows: 3,014
Columns: 51
$ GroupCode     <chr> "NETN", "NETN", "NETN", "NETN", "NETN", "NETN", "NETN", …
$ GroupName     <chr> "Northeast Temperate Network", "Northeast Temperate Netw…
$ UnitCode      <chr> "ACAD", "ACAD", "ACAD", "ACAD", "ACAD", "ACAD", "ACAD", …
$ UnitName      <chr> "Acadia National Park", "Acadia National Park", "Acadia …
$ SubUnitCode   <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ SubUnitName   <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ SiteCode      <chr> "ACABIN", "ACABIN", "ACABIN", "ACABIN", "ACABIN", "ACABI…
$ SiteName      <chr> "Aunt Betty Pond Inlet", "Aunt Betty Pond Inlet", "Aunt …
$ SiteType      <chr> "Stream", "Stream", "Stream", "Stream", "Stream", "Strea…
$ EventDate     <date> 2006-06-26, 2006-08-30, 2006-08-30, 2008-05-13, 2008-08…
$ EventCode     <dbl> 484, 460, 460, 638, 601, 105, 191, 1893, 1898, 2485, 248…
$ Project       <chr> "NETN_LS", "NETN_LS", "NETN_LS", "NETN_LS", "NETN_LS", "…
$ QCtype        <chr> "ENV", "ENV", "REP", "ENV", "ENV", "ENV", "ENV", "ENV", …
$ LabCode       <chr> "NWQL", "NWQL", "NWQL", "SECRL", "SECRL", "SECRL", "SECR…
$ SampleTime    <time> 16:30:00, 12:45:00, 12:45:00, 14:50:00, 14:06:15, 14:27…
$ SampleDepth_m <dbl> 0.1, 0.1, 0.1, 0.1, 0.1, 0.2, 0.2, 0.2, 0.2, 0.5, 0.5, 0…
$ SampleType    <chr> "G", "G", "G", "G", "G", "G", "G", "G", "G", "G", "G", "…
$ pH_Lab        <dbl> NA, NA, NA, 6.35, 6.06, 6.68, 6.47, 6.20, 6.57, 6.55, 5.…
$ pH_Lab_Method <chr> NA, NA, NA, "ANCpH", "ANCpH", "ANCpH", "ANCpH", "ANCpH",…
$ AppColor_PCU  <dbl> 40, 60, 60, 41, 70, 51, 75, 52, 86, 43, 1, 64, 34, 96, 9…
$ AppColorFlag  <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ ANC_ueqL      <dbl> 172.18, 172.98, 169.26, 81.60, 101.00, 139.00, 188.00, 5…
$ ANCFlag       <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ Cl_ueqL       <dbl> NA, NA, NA, NA, NA, NA, NA, 74.00, 106.00, 91.00, 5.00, …
$ ClFlag        <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ SO4_ueqL      <dbl> NA, NA, NA, NA, NA, NA, NA, 29.00, 25.00, 31.00, NA, NA,…
$ SO4Flag       <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "<MRL 2", "<MRL …
$ NO3_ueqL      <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 2.29…
$ NO3Flag       <chr> NA, NA, NA, NA, NA, NA, NA, "<MRL 1", "<MRL 1", "<MRL 1"…
$ `NO2+NO3_mgL` <dbl> NA, NA, NA, NA, NA, 0.017, NA, NA, NA, NA, NA, NA, NA, N…
$ `NO2+NO3Flag` <chr> "<MRL 0.06", "<MRL 0.016", "<MRL 0.016", "<MRL 0.005", "…
$ NO2_mgL       <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ NO2Flag       <chr> "<MRL 0.002", "<MRL 0.002", "<MRL 0.002", "<MRL 0.005", …
$ TotDissN_mgL  <dbl> NA, NA, NA, NA, 0.321, 0.203, 0.359, NA, NA, NA, NA, NA,…
$ TotDissNFlag  <chr> NA, NA, NA, "<MRL 0.1", NA, NA, NA, NA, NA, NA, NA, NA, …
$ NH3_mgL       <dbl> 0.006, 0.015, 0.016, NA, NA, NA, NA, NA, NA, NA, NA, 0.0…
$ NH3Flag       <chr> "E, <MRL 0.01", NA, NA, "<MRL 0.08", "<MRL 0.08", "<MRL …
$ TN_mgL        <dbl> 0.430, 0.470, 0.480, 0.167, 0.318, 0.247, 0.453, 0.182, …
$ TNFlag        <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ TP_ugL        <dbl> 10.0, 14.0, 12.0, 6.0, 9.6, 13.0, 16.0, 6.6, 20.0, 6.8, …
$ TPFlag        <chr> "E, <MRL 20", NA, NA, NA, NA, NA, NA, NA, NA, NA, "<MRL …
$ TotDissP_ugL  <dbl> NA, NA, NA, 5.4, 6.6, 6.4, 7.0, NA, NA, NA, NA, NA, NA, …
$ TotDissPFlag  <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ PO4_ugL       <dbl> NA, NA, 4.0, 1.4, NA, 1.4, 1.0, NA, NA, NA, NA, NA, NA, …
$ PO4Flag       <chr> "<MRL 6", "<MRL 6", "E, <MRL 6", NA, "<MRL 1", NA, NA, N…
$ ChlA_ugL      <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ ChlAFlag      <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
$ DOC_mgL       <dbl> NA, NA, NA, NA, NA, NA, NA, 5.29, 7.23, 4.33, NA, 5.48, …
$ DOCFlag       <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "<MRL 0.5", NA, …
$ Comments      <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, "do not use YSI ph s…
$ IsEventCUI    <lgl> FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, …

Initial Dataset Summary

The dataset contains water chemistry measurements.

The next step is to identify the sampling locations and the chemistry parameters needed for the analysis.

Missing Values Analysis

Some measurements are missing. The table shows the number of available and missing values for the five selected parameters.

Code 
# Review missing measurements before transformation
missing_summary <- water %>%
  select(pH_Lab, TN_mgL, NH3_mgL, TotDissN_mgL, TP_ugL) %>%
  summarise(across(everything(),
                   list(Missing = ~sum(is.na(.x)),
                        Available = ~sum(!is.na(.x))))) %>%
  pivot_longer(
    cols = everything(),
    names_to = c("Parameter", ".value"),
    names_pattern = "^(.*)_(Missing|Available)$"
  ) %>%
  mutate(Missing_Percent = round(100 * Missing / (Missing + Available), 1))

knitr::kable(
  missing_summary,
  col.names = c("Parameter", "Missing Values", "Available Values", "Missing (%)"),
  caption = "Missing Values in Selected Water Chemistry Parameters"
)
Missing Values in Selected Water Chemistry Parameters
Parameter Missing Values Available Values Missing (%)
pH_Lab 955 2059 31.7
TN_mgL 409 2605 13.6
NH3_mgL 2669 345 88.6
TotDissN_mgL 2587 427 85.8
TP_ugL 725 2289 24.1

Missing measurements were not replaced with zero. The analysis uses available recorded values only. Some laboratory flags indicate values below reporting limits, which should be interpreted carefully.

Data Cleaning and Transformation

The data was cleaned and transformed from wide to long format. Missing measurements were excluded from the analysis.

Code 
# Select water quality measurements
water_clean <- water %>%
  select(
    SiteName, SiteType, EventDate,
    pH_Lab, TN_mgL, NH3_mgL,
    TotDissN_mgL, TP_ugL
  )

# Transform wide data into long format
water_long <- water_clean %>%
  pivot_longer(
    cols = c(
      pH_Lab, TN_mgL, NH3_mgL,
      TotDissN_mgL, TP_ugL
    ),
    names_to = "Parameter",
    values_to = "Value"
  ) %>%
  filter(!is.na(Value))

# Summary of the transformation
transformation_summary <- tibble(
  Step = c("Original Rows", "Original Columns",
           "Long Format Measurements"),
  Count = c(nrow(water), ncol(water), nrow(water_long))
)

knitr::kable(
  transformation_summary,
  col.names = c("Step", "Count"),
  caption = "Data Transformation Summary"
)
Data Transformation Summary
Step Count
Original Rows 3014
Original Columns 51
Long Format Measurements 7725

Transformation Validation

The transformation was checked to confirm that the available measurements were preserved.

Code 
# Count measurements before transformation
original_count <- water_clean %>%
  select(
    pH_Lab, TN_mgL, NH3_mgL,
    TotDissN_mgL, TP_ugL
  ) %>%
  summarise(across(everything(), ~sum(!is.na(.x)))) %>%
  unlist(use.names = FALSE) %>%
  sum()

# Count measurements after transformation
long_count <- nrow(water_long)

validation <- tibble(
  Dataset = c("Original Wide Data", "Transformed Long Data"),
  Measurements = c(original_count, long_count)
)

knitr::kable(
  validation,
  col.names = c("Dataset", "Measurements"),
  caption = "Transformation Validation"
)
Transformation Validation
Dataset Measurements
Original Wide Data 7725
Transformed Long Data 7725
Code 
stopifnot(original_count == long_count)

Both datasets contain the same number of available measurements. This confirms that no measurements were lost during transformation.

Summary Statistics

This table summarizes the available water chemistry measurements.

Code 
# Calculate summary statistics
water_summary <- water_long %>%
  group_by(Parameter) %>%
  summarise(
    Samples = n(),
    Average = mean(Value),
    Minimum = min(Value),
    Maximum = max(Value),
    .groups = "drop"
  )

knitr::kable(
  water_summary,
  col.names = c(
    "Parameter",
    "Samples",
    "Average",
    "Minimum",
    "Maximum"
  ),
  digits = 2,
  caption = "Water Quality Summary Statistics"
)
Water Quality Summary Statistics
Parameter Samples Average Minimum Maximum
NH3_mgL 345 0.03 0.00 0.91
TN_mgL 2605 0.51 0.01 17.20
TP_ugL 2289 22.89 0.50 1910.00
TotDissN_mgL 427 0.71 0.09 10.90
pH_Lab 2059 6.87 -0.73 9.60

Water Quality Analysis

Question 1: How do water chemistry measurements vary across sampling locations?

The following analysis compares average nitrogen levels across sampling locations.

Code 
# Average nitrogen by sampling location
nitrogen_by_site <- water_long %>%
  filter(Parameter == "TN_mgL") %>%
  group_by(SiteName) %>%
  summarise(
    Samples = n(),
    Average_Nitrogen = mean(Value),
    .groups = "drop"
  ) %>%
  arrange(desc(Average_Nitrogen))

knitr::kable(
  head(nitrogen_by_site, 10),
  col.names = c(
    "Sampling Location",
    "Samples",
    "Average Nitrogen (mg/L)"
  ),
  digits = 3,
  caption = "Top 10 Locations by Average Nitrogen"
)
Top 10 Locations by Average Nitrogen
Sampling Location Samples Average Nitrogen (mg/L)
Kroma Kill 43 4.540
Mill Creek Confluence 35 4.137
Upper FDR Brook 46 2.177
Lower FDR Brook 45 1.634
West Primrose Brook 15 1.081
Indian Grove Brook 47 1.049
Elm Brook 45 1.044
Seawall Pond 21 0.989
Turning Basin 36 0.907
Maritje Kill 45 0.859
Code 
# Plot average nitrogen at the top 10 locations
ggplot(
  head(nitrogen_by_site, 10),
  aes(
    x = reorder(SiteName, Average_Nitrogen),
    y = Average_Nitrogen
  )
) +
  geom_col(fill = "steelblue") +
  coord_flip() +
  labs(
    title = "Average Nitrogen by Sampling Location",
    x = "Sampling Location",
    y = "Average Nitrogen (mg/L)"
  ) +
  theme_minimal()

Average nitrogen levels differ across sampling locations. The table and chart show the locations with the highest recorded averages.

Question 2: Which water chemistry parameters show the greatest variation?

The analysis uses the coefficient of variation to compare selected chemical concentrations measured on different scales. pH is excluded because it uses a logarithmic scale.

Code 
# Calculate variation by parameter
parameter_variation <- water_long %>%
  filter(Parameter != "pH_Lab") %>%
  group_by(Parameter) %>%
  summarise(
    Samples = n(),
    Average = mean(Value),
    Standard_Deviation = sd(Value),
    CV_Percent = if (
      is.finite(mean(Value)) &&
      abs(mean(Value)) > .Machine$double.eps
    ) {
      100 * sd(Value) / abs(mean(Value))
    } else {
      NA_real_
    },
    .groups = "drop"
  ) %>%
  arrange(desc(CV_Percent))

knitr::kable(
  parameter_variation,
  col.names = c(
    "Parameter",
    "Samples",
    "Average",
    "Standard Deviation",
    "Variation (%)"
  ),
  digits = 2,
  caption = "Variation in Water Chemistry Measurements"
)
Variation in Water Chemistry Measurements
Parameter Samples Average Standard Deviation Variation (%)
TP_ugL 2289 22.89 60.00 262.08
TN_mgL 2605 0.51 1.06 206.44
NH3_mgL 345 0.03 0.07 203.89
TotDissN_mgL 427 0.71 1.03 144.68
Code 
# Compare relative variation
ggplot(
  filter(parameter_variation, !is.na(CV_Percent)),
  aes(
    x = reorder(Parameter, CV_Percent),
    y = CV_Percent
  )
) +
  geom_col(fill = "darkcyan") +
  coord_flip() +
  labs(
    title = "Variation by Water Quality Parameter",
    x = "Water Quality Parameter",
    y = "Coefficient of Variation (%)"
  ) +
  theme_minimal()

Parameters with higher variation percentages have greater relative differences among their recorded measurements.

Water pH Distribution

Code 
# Distribution of pH measurements
ggplot(
  filter(water_long, Parameter == "pH_Lab"),
  aes(x = Value)
) +
  geom_histogram(
    bins = 25,
    fill = "steelblue",
    color = "white"
  ) +
  labs(
    title = "Distribution of Water pH",
    x = "pH",
    y = "Number of Measurements"
  ) +
  theme_minimal()

This chart shows how the recorded pH values are distributed across the dataset.

Key Findings

The analysis provides two main findings based on the business questions.

Question 1: Water Quality by Location

Total nitrogen levels vary across sampling locations. Kroma Kill has the highest average nitrogen level at 4.540 mg/L, followed by Mill Creek Confluence at 4.137 mg/L.

Question 2: Variation in Water Chemistry

Total phosphorus shows the greatest relative variation at 262.08%, followed by total nitrogen at 206.44%.

These results are based on the available measurements. Missing values were excluded from the analysis.

Conclusion

The Water Quality dataset contains measurements from different sampling locations.

The analysis shows how nitrogen levels vary by location and compares the relative variation of selected chemistry parameters.

Transforming the data from wide to long format made the measurements easier to summarize and visualize.

The results describe the available samples and do not establish whether a location meets water quality standards.

Video Presentation

This video presents my Water Quality Analysis for DATA 607 Project 2. It explains the data cleaning and transformation process, answers the two research questions, and summarizes the main findings and conclusions.

YouTube Presentation:
https://youtu.be/F96TWdD-zGE

AI Use

ChatGPT was used to help interpret the assignment requirements, organize the planned approach, improve the English writing, and provide guidance for the R code. I will run, review, and verify the data transformations, analysis, visualizations, and final conclusions.