Code
library(tidyr)
library(dplyr)
library(ggplot2)Project 2 - Water Quality
Introduction
In this project, I will use R to tidy and transform a water quality dataset from the National Park Service. The dataset contains water chemistry measurements collected at different monitoring sites between 2006 and 2024. I will focus on four measurements: pH, acid neutralizing capacity, total nitrogen, and total phosphorus. My goal is to prepare the data for analysis and examine how water quality has changed over time, particularly at Eagle Lake in Acadia National Park.
Original Dataset: https://catalog.data.gov/dataset/water-quality-and-quantity-monitoring-data-package-for-measurements-collected-wi-2006-2024
Planned Approach
I will first import the original wide-format dataset from my GitHub repository and examine its structure. Then, I will select 10 relevant variables and rename the four water quality measurements to make their names more descriptive.
Next, I will use pivot_longer() to transform the measurement columns into a tidy format. I will check for missing values and examine unusual measurements.
After cleaning the data, I will calculate summary statistics for the four water quality measurements and compare average values across monitoring sites. Finally, I will focus on Eagle Lake in Acadia National Park to examine yearly trends and visualize the results using line charts.
Anticipated Challenges
One challenge is that the original dataset contains many columns that are not needed for my analysis. I will need to select the relevant variables while keeping the original raw data unchanged.
Another challenge is handling missing values and unusual measurements. Some measurements may be missing, and certain values may need further examination before analysis.
Code Base
Required Packages
I will use three R packages for this project. The tidyr package will be used to transform the dataset from wide to long format. The dplyr package will be used to select, rename, filter, and summarize the data. The ggplot2 package will be used to create visualizations.
Data Source and Import
The dataset was obtained from the National Park Service through Data.gov. It contains water quality measurements collected at different monitoring sites between 2006 and 2024.
I saved the original wide-format dataset as a CSV file in my public GitHub repository. I will import this file into R using read.csv2() while preserving the original column names and data structure.
Data Source: https://catalog.data.gov/dataset/water-quality-and-quantity-monitoring-data-package-for-measurements-collected-wi-2006-2024
Code
water_raw <- read.csv2(
"https://raw.githubusercontent.com/OzgeG01/DATA607-Assignment/refs/heads/main/Water_Quality.csv",
dec = ".",
check.names = FALSE
)
head(water_raw) GroupCode GroupName UnitCode UnitName
1 NETN Northeast Temperate Network ACAD Acadia National Park
2 NETN Northeast Temperate Network ACAD Acadia National Park
3 NETN Northeast Temperate Network ACAD Acadia National Park
4 NETN Northeast Temperate Network ACAD Acadia National Park
5 NETN Northeast Temperate Network ACAD Acadia National Park
6 NETN Northeast Temperate Network ACAD Acadia National Park
SubUnitCode SubUnitName SiteCode SiteName SiteType EventDate
1 <NA> <NA> ACABIN Aunt Betty Pond Inlet Stream 2006-06-26
2 <NA> <NA> ACABIN Aunt Betty Pond Inlet Stream 2006-08-30
3 <NA> <NA> ACABIN Aunt Betty Pond Inlet Stream 2006-08-30
4 <NA> <NA> ACABIN Aunt Betty Pond Inlet Stream 2008-05-13
5 <NA> <NA> ACABIN Aunt Betty Pond Inlet Stream 2008-08-04
6 <NA> <NA> ACABIN Aunt Betty Pond Inlet Stream 2010-05-04
EventCode Project QCtype LabCode SampleTime SampleDepth_m SampleType pH_Lab
1 484 NETN_LS ENV NWQL 16:30:00 0.1 G NA
2 460 NETN_LS ENV NWQL 12:45:00 0.1 G NA
3 460 NETN_LS REP NWQL 12:45:00 0.1 G NA
4 638 NETN_LS ENV SECRL 14:50:00 0.1 G 6.35
5 601 NETN_LS ENV SECRL 14:06:15 0.1 G 6.06
6 105 NETN_LS ENV SECRL 14:27:00 0.2 G 6.68
pH_Lab_Method AppColor_PCU AppColorFlag ANC_ueqL ANCFlag Cl_ueqL ClFlag
1 <NA> 40 <NA> 172.18 <NA> NA <NA>
2 <NA> 60 <NA> 172.98 <NA> NA <NA>
3 <NA> 60 <NA> 169.26 <NA> NA <NA>
4 ANCpH 41 <NA> 81.60 <NA> NA <NA>
5 ANCpH 70 <NA> 101.00 <NA> NA <NA>
6 ANCpH 51 <NA> 139.00 <NA> NA <NA>
SO4_ueqL SO4Flag NO3_ueqL NO3Flag NO2+NO3_mgL NO2+NO3Flag NO2_mgL NO2Flag
1 NA <NA> NA <NA> NA <MRL 0.06 NA <MRL 0.002
2 NA <NA> NA <NA> NA <MRL 0.016 NA <MRL 0.002
3 NA <NA> NA <NA> NA <MRL 0.016 NA <MRL 0.002
4 NA <NA> NA <NA> NA <MRL 0.005 NA <MRL 0.005
5 NA <NA> NA <NA> NA <MRL 0.005 NA <MRL 0.005
6 NA <NA> NA <NA> 0.017 <NA> NA <MRL 0.005
TotDissN_mgL TotDissNFlag NH3_mgL NH3Flag TN_mgL TNFlag TP_ugL
1 NA <NA> 0.006 E, <MRL 0.01 0.430 <NA> 10.0
2 NA <NA> 0.015 <NA> 0.470 <NA> 14.0
3 NA <NA> 0.016 <NA> 0.480 <NA> 12.0
4 NA <MRL 0.1 NA <MRL 0.08 0.167 <NA> 6.0
5 0.321 <NA> NA <MRL 0.08 0.318 <NA> 9.6
6 0.203 <NA> NA <MRL 0.08 0.247 <NA> 13.0
TPFlag TotDissP_ugL TotDissPFlag PO4_ugL PO4Flag ChlA_ugL ChlAFlag
1 E, <MRL 20 NA <NA> NA <MRL 6 NA <NA>
2 <NA> NA <NA> NA <MRL 6 NA <NA>
3 <NA> NA <NA> 4.0 E, <MRL 6 NA <NA>
4 <NA> 5.4 <NA> 1.4 <NA> NA <NA>
5 <NA> 6.6 <NA> NA <MRL 1 NA <NA>
6 <NA> 6.4 <NA> 1.4 <NA> NA <NA>
DOC_mgL DOCFlag Comments IsEventCUI
1 NA <NA> <NA> FALSE
2 NA <NA> <NA> FALSE
3 NA <NA> <NA> FALSE
4 NA <NA> <NA> FALSE
5 NA <NA> <NA> FALSE
6 NA <NA> <NA> FALSEData Structure Before Tidying
Before tidying the dataset, I will examine its original structure using str(). This will help me identify the number of rows and columns, variable names, data types, and sample values. Understanding the original structure is important before selecting variables and transforming the data from wide to long format.
Code
str(water_raw)'data.frame': 3014 obs. of 51 variables:
$ GroupCode : chr "NETN" "NETN" "NETN" "NETN" ...
$ GroupName : chr "Northeast Temperate Network" "Northeast Temperate Network" "Northeast Temperate Network" "Northeast Temperate Network" ...
$ UnitCode : chr "ACAD" "ACAD" "ACAD" "ACAD" ...
$ UnitName : chr "Acadia National Park" "Acadia National Park" "Acadia National Park" "Acadia National Park" ...
$ SubUnitCode : chr NA NA NA NA ...
$ SubUnitName : chr NA NA NA NA ...
$ SiteCode : chr "ACABIN" "ACABIN" "ACABIN" "ACABIN" ...
$ SiteName : chr "Aunt Betty Pond Inlet" "Aunt Betty Pond Inlet" "Aunt Betty Pond Inlet" "Aunt Betty Pond Inlet" ...
$ SiteType : chr "Stream" "Stream" "Stream" "Stream" ...
$ EventDate : chr "2006-06-26" "2006-08-30" "2006-08-30" "2008-05-13" ...
$ EventCode : int 484 460 460 638 601 105 191 1893 1898 2485 ...
$ Project : chr "NETN_LS" "NETN_LS" "NETN_LS" "NETN_LS" ...
$ QCtype : chr "ENV" "ENV" "REP" "ENV" ...
$ LabCode : chr "NWQL" "NWQL" "NWQL" "SECRL" ...
$ SampleTime : chr "16:30:00" "12:45:00" "12:45:00" "14:50:00" ...
$ SampleDepth_m: num 0.1 0.1 0.1 0.1 0.1 0.2 0.2 0.2 0.2 0.5 ...
$ SampleType : chr "G" "G" "G" "G" ...
$ pH_Lab : num NA NA NA 6.35 6.06 6.68 6.47 6.2 6.57 6.55 ...
$ pH_Lab_Method: chr NA NA NA "ANCpH" ...
$ AppColor_PCU : int 40 60 60 41 70 51 75 52 86 43 ...
$ AppColorFlag : chr NA NA NA NA ...
$ ANC_ueqL : num 172.2 173 169.3 81.6 101 ...
$ ANCFlag : chr NA NA NA NA ...
$ Cl_ueqL : num NA NA NA NA NA NA NA 74 106 91 ...
$ ClFlag : chr NA NA NA NA ...
$ SO4_ueqL : num NA NA NA NA NA NA NA 29 25 31 ...
$ SO4Flag : chr NA NA NA NA ...
$ NO3_ueqL : num NA NA NA NA NA NA NA NA NA NA ...
$ NO3Flag : chr NA NA NA NA ...
$ NO2+NO3_mgL : num NA NA NA NA NA 0.017 NA NA NA NA ...
$ NO2+NO3Flag : chr "<MRL 0.06" "<MRL 0.016" "<MRL 0.016" "<MRL 0.005" ...
$ NO2_mgL : num 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 : num NA NA NA NA 0.321 0.203 0.359 NA NA NA ...
$ TotDissNFlag : chr NA NA NA "<MRL 0.1" ...
$ NH3_mgL : num 0.006 0.015 0.016 NA NA NA NA NA NA NA ...
$ NH3Flag : chr "E, <MRL 0.01" NA NA "<MRL 0.08" ...
$ TN_mgL : num 0.43 0.47 0.48 0.167 0.318 0.247 0.453 0.182 0.456 0.202 ...
$ TNFlag : chr NA NA NA NA ...
$ TP_ugL : num 10 14 12 6 9.6 13 16 6.6 20 6.8 ...
$ TPFlag : chr "E, <MRL 20" NA NA NA ...
$ TotDissP_ugL : num NA NA NA 5.4 6.6 6.4 7 NA NA NA ...
$ TotDissPFlag : chr NA NA NA NA ...
$ PO4_ugL : num NA NA 4 1.4 NA 1.4 1 NA NA NA ...
$ PO4Flag : chr "<MRL 6" "<MRL 6" "E, <MRL 6" NA ...
$ ChlA_ugL : num NA NA NA NA NA NA NA NA NA NA ...
$ ChlAFlag : chr NA NA NA NA ...
$ DOC_mgL : num NA NA NA NA NA NA NA 5.29 7.23 4.33 ...
$ DOCFlag : chr NA NA NA NA ...
$ Comments : chr NA NA NA NA ...
$ IsEventCUI : logi FALSE FALSE FALSE FALSE FALSE FALSE ...The original dataset contains 3,014 observations and 51 variables. The variables include character, integer, and numeric data types. Some columns contain missing values (NA). The sampling date and time are stored as character variables. I will consider these data types and missing values during the tidying process.
Selecting Variables
The original dataset contains 51 variables, but I will select only the 10 variables relevant to my analysis. These variables include information about the monitoring locations, sampling dates and times, sample depth, and four water quality measurements: pH, acid neutralizing capacity, total nitrogen, and total phosphorus. I will use select() to keep these variables without modifying the original dataset.
Code
water_selected <- water_raw %>%
select(
UnitName,
SiteName,
SiteType,
EventDate,
SampleTime,
SampleDepth_m,
pH_Lab,
ANC_ueqL,
TN_mgL,
TP_ugL
)
head(water_selected) UnitName SiteName SiteType EventDate SampleTime
1 Acadia National Park Aunt Betty Pond Inlet Stream 2006-06-26 16:30:00
2 Acadia National Park Aunt Betty Pond Inlet Stream 2006-08-30 12:45:00
3 Acadia National Park Aunt Betty Pond Inlet Stream 2006-08-30 12:45:00
4 Acadia National Park Aunt Betty Pond Inlet Stream 2008-05-13 14:50:00
5 Acadia National Park Aunt Betty Pond Inlet Stream 2008-08-04 14:06:15
6 Acadia National Park Aunt Betty Pond Inlet Stream 2010-05-04 14:27:00
SampleDepth_m pH_Lab ANC_ueqL TN_mgL TP_ugL
1 0.1 NA 172.18 0.430 10.0
2 0.1 NA 172.98 0.470 14.0
3 0.1 NA 169.26 0.480 12.0
4 0.1 6.35 81.60 0.167 6.0
5 0.1 6.06 101.00 0.318 9.6
6 0.2 6.68 139.00 0.247 13.0Renaming Variables
After selecting the 10 variables, I will use rename() to give the four water quality measurements more descriptive names. I will keep the measurement units in the column names and leave the other six variables unchanged. This will make the measurements easier to identify and understand during analysis.
Code
water_renamed <- water_selected %>%
rename(
pH = pH_Lab,
AcidNeuCap_ueqL = ANC_ueqL,
TotalNitrogen_mgL = TN_mgL,
TotalPhosphorus_ugL = TP_ugL
)
head(water_renamed) UnitName SiteName SiteType EventDate SampleTime
1 Acadia National Park Aunt Betty Pond Inlet Stream 2006-06-26 16:30:00
2 Acadia National Park Aunt Betty Pond Inlet Stream 2006-08-30 12:45:00
3 Acadia National Park Aunt Betty Pond Inlet Stream 2006-08-30 12:45:00
4 Acadia National Park Aunt Betty Pond Inlet Stream 2008-05-13 14:50:00
5 Acadia National Park Aunt Betty Pond Inlet Stream 2008-08-04 14:06:15
6 Acadia National Park Aunt Betty Pond Inlet Stream 2010-05-04 14:27:00
SampleDepth_m pH AcidNeuCap_ueqL TotalNitrogen_mgL TotalPhosphorus_ugL
1 0.1 NA 172.18 0.430 10.0
2 0.1 NA 172.98 0.470 14.0
3 0.1 NA 169.26 0.480 12.0
4 0.1 6.35 81.60 0.167 6.0
5 0.1 6.06 101.00 0.318 9.6
6 0.2 6.68 139.00 0.247 13.0Transforming Wide Data to Long Format
The four water quality measurements are currently stored in separate columns. I will use pivot_longer() to transform these columns into a long format. This will create two new columns: Measurement, which identifies the type of water quality measurement, and Value, which contains the corresponding measurement value. The monitoring site and sampling information will remain unchanged.
Code
water_long <- water_renamed %>%
pivot_longer(
cols = c(
pH,
AcidNeuCap_ueqL,
TotalNitrogen_mgL,
TotalPhosphorus_ugL
),
names_to = "Measurement",
values_to = "Value"
)
head(water_long)# A tibble: 6 × 8
UnitName SiteName SiteType EventDate SampleTime SampleDepth_m Measurement
<chr> <chr> <chr> <chr> <chr> <dbl> <chr>
1 Acadia Natio… Aunt Be… Stream 2006-06-… 16:30:00 0.1 pH
2 Acadia Natio… Aunt Be… Stream 2006-06-… 16:30:00 0.1 AcidNeuCap…
3 Acadia Natio… Aunt Be… Stream 2006-06-… 16:30:00 0.1 TotalNitro…
4 Acadia Natio… Aunt Be… Stream 2006-06-… 16:30:00 0.1 TotalPhosp…
5 Acadia Natio… Aunt Be… Stream 2006-08-… 12:45:00 0.1 pH
6 Acadia Natio… Aunt Be… Stream 2006-08-… 12:45:00 0.1 AcidNeuCap…
# ℹ 1 more variable: Value <dbl>Missing Values
After transforming the dataset into long format, I will check for missing values. Some water quality measurements may not have been recorded at every monitoring site or sampling date. I will use is.na() to count the missing values for each measurement type before deciding how to handle them.
Code
missing_values <- water_long %>%
group_by(Measurement) %>%
summarise(
Missing_Count = sum(is.na(Value)),
.groups = "drop"
)
missing_values# A tibble: 4 × 2
Measurement Missing_Count
<chr> <int>
1 AcidNeuCap_ueqL 921
2 TotalNitrogen_mgL 409
3 TotalPhosphorus_ugL 725
4 pH 955The results show that all four water quality measurements contain missing values. pH has the highest number of missing values (955), followed by acid neutralizing capacity (921), total phosphorus (725), and total nitrogen (409). These missing values need to be handled before calculating summary statistics and examining water quality trends.
Removing Missing Values
After identifying the missing values, I will use drop_na() to remove rows with missing measurement values. I will not replace missing values with zero because this could affect the analysis results. I will save the cleaned data as water_tidy and use it for further analysis. The original dataset will remain unchanged.
Code
water_tidy <- water_long %>%
drop_na(Value)
head(water_tidy)# A tibble: 6 × 8
UnitName SiteName SiteType EventDate SampleTime SampleDepth_m Measurement
<chr> <chr> <chr> <chr> <chr> <dbl> <chr>
1 Acadia Natio… Aunt Be… Stream 2006-06-… 16:30:00 0.1 AcidNeuCap…
2 Acadia Natio… Aunt Be… Stream 2006-06-… 16:30:00 0.1 TotalNitro…
3 Acadia Natio… Aunt Be… Stream 2006-06-… 16:30:00 0.1 TotalPhosp…
4 Acadia Natio… Aunt Be… Stream 2006-08-… 12:45:00 0.1 AcidNeuCap…
5 Acadia Natio… Aunt Be… Stream 2006-08-… 12:45:00 0.1 TotalNitro…
6 Acadia Natio… Aunt Be… Stream 2006-08-… 12:45:00 0.1 TotalPhosp…
# ℹ 1 more variable: Value <dbl>Checking Date and Time Formats
Before checking unusual measurement values, I will examine the date and time variables. Both EventDate and SampleTime were imported as character variables. I will check their formats to determine whether they need to be converted for further analysis, especially when examining water quality trends over time.
Code
water_tidy %>%
select(EventDate, SampleTime) %>%
head()# A tibble: 6 × 2
EventDate SampleTime
<chr> <chr>
1 2006-06-26 16:30:00
2 2006-06-26 16:30:00
3 2006-06-26 16:30:00
4 2006-08-30 12:45:00
5 2006-08-30 12:45:00
6 2006-08-30 12:45:00 Converting Date and Time Formats
The sampling dates and times were originally stored as character variables. I will convert EventDate to Date format and SampleTime to a time format using as.Date() and hms::as_hms(). This will preserve the date and time information in separate columns and make them suitable for further analysis.
Code
# Convert date and time variables
water_tidy <- water_tidy %>%
mutate(
EventDate = as.Date(EventDate),
SampleTime = hms::as_hms(SampleTime)
)
head(water_tidy)# A tibble: 6 × 8
UnitName SiteName SiteType EventDate SampleTime SampleDepth_m Measurement
<chr> <chr> <chr> <date> <time> <dbl> <chr>
1 Acadia Nati… Aunt Be… Stream 2006-06-26 16:30 0.1 AcidNeuCap…
2 Acadia Nati… Aunt Be… Stream 2006-06-26 16:30 0.1 TotalNitro…
3 Acadia Nati… Aunt Be… Stream 2006-06-26 16:30 0.1 TotalPhosp…
4 Acadia Nati… Aunt Be… Stream 2006-08-30 12:45 0.1 AcidNeuCap…
5 Acadia Nati… Aunt Be… Stream 2006-08-30 12:45 0.1 TotalNitro…
6 Acadia Nati… Aunt Be… Stream 2006-08-30 12:45 0.1 TotalPhosp…
# ℹ 1 more variable: Value <dbl>Checking Unusual Values
I will check for unusual values in the four water quality measurements. I will calculate the minimum and maximum values for each measurement type to identify possible outliers or values that may need further examination. I will not remove any unusual values without first examining them.
Code
water_tidy %>%
group_by(Measurement) %>%
summarise(
MinValue = min(Value),
MaxValue = max(Value),
.groups = "drop"
)# A tibble: 4 × 3
Measurement MinValue MaxValue
<chr> <dbl> <dbl>
1 AcidNeuCap_ueqL -29.1 6390
2 TotalNitrogen_mgL 0.007 17.2
3 TotalPhosphorus_ugL 0.5 1910
4 pH -0.73 9.6The minimum and maximum values show some potentially unusual measurements. The minimum pH value is -0.73, which is unexpected for natural surface water. Acid neutralizing capacity includes negative values, while total nitrogen and total phosphorus have relatively high maximum values. These measurements require further examination before deciding whether any values should be excluded from the analysis.
Examining Unusual Measurements
Since the minimum pH value is negative, I will examine the records with negative pH measurements. I will check their monitoring locations and sampling dates to better understand these unusual values.
Code
water_tidy %>%
filter(Measurement == "pH", Value < 0) %>%
select(UnitName, SiteName, EventDate, Measurement, Value)# A tibble: 1 × 5
UnitName SiteName EventDate Measurement Value
<chr> <chr> <date> <chr> <dbl>
1 Acadia National Park Eagle Lake 2014-08-18 pH -0.73Handling Unusual pH Values
I found one negative pH measurement (-0.73) recorded at Eagle Lake in Acadia National Park on August 18, 2014. Since this value is highly unusual for natural lake water, I will exclude it from the analysis as a potentially invalid measurement. I will keep the original data unchanged and retain the other measurements.
Code
water_tidy <- water_tidy %>%
filter(!(Measurement == "pH" & Value < 0))I will check the minimum pH value after removing the unusual negative measurement.
Code
water_tidy %>%
filter(Measurement == "pH") %>%
summarise(MinValue = min(Value))# A tibble: 1 × 1
MinValue
<dbl>
1 4.09Summary Statistics
After cleaning the dataset, I will calculate summary statistics for the four water quality measurements. I will use the tidy dataset to calculate the number of observations, mean, median, minimum, and maximum values for each measurement type. These statistics will help me understand the overall distribution of water quality measurements across the monitoring sites. I will keep the high nitrogen and phosphorus values because there is not enough evidence to consider them incorrect.
Code
water_summary <- water_tidy %>%
group_by(Measurement) %>%
summarise(
Count = n(),
Mean = round(mean(Value), 2),
Median = round(median(Value), 2),
Min = round(min(Value), 2),
Max = round(max(Value), 2),
.groups = "drop"
)
water_summary# A tibble: 4 × 6
Measurement Count Mean Median Min Max
<chr> <int> <dbl> <dbl> <dbl> <dbl>
1 AcidNeuCap_ueqL 2093 625. 98.9 -29.1 6390
2 TotalNitrogen_mgL 2605 0.51 0.23 0.01 17.2
3 TotalPhosphorus_ugL 2289 22.9 10 0.5 1910
4 pH 2058 6.87 6.74 4.09 9.6The results show that the average pH is 6.87, with a median of 6.74 and values ranging from 4.09 to 9.60. The average acid neutralizing capacity is 625.43 microeq/L, with a median of 98.87 microeq/L. Total nitrogen has an average of 0.51 mg/L and a median of 0.23 mg/L, while total phosphorus has an average of 22.89 microg/L and a median of 10.00 microg/L.
The number of observations varies across the four measurements because some values were missing in the original dataset. Total nitrogen has the highest number of valid observations (2,605), while pH has the lowest (2,058). The mean values are higher than the median values, especially for acid neutralizing capacity and total phosphorus, suggesting that some high measurements affect the averages. The wide ranges in some measurements also suggest differences in water chemistry across the monitoring sites.
Comparison Across Monitoring Sites
I will compare the average water quality measurements across different monitoring sites. I will group the tidy data by national park, monitoring site, and measurement type, then calculate the number of observations and average value for each group. This will help me examine differences in water chemistry across monitoring locations.
Code
site_summary <- water_tidy %>%
group_by(UnitName, SiteName, Measurement) %>%
summarise(
Count = n(),
Mean = round(mean(Value), 2),
.groups = "drop"
)
head(site_summary)# A tibble: 6 × 5
UnitName SiteName Measurement Count Mean
<chr> <chr> <chr> <int> <dbl>
1 Acadia National Park Aunt Betty Pond Inlet AcidNeuCap_ueqL 33 146.
2 Acadia National Park Aunt Betty Pond Inlet TotalNitrogen_mgL 32 0.33
3 Acadia National Park Aunt Betty Pond Inlet TotalPhosphorus_ugL 30 13.4
4 Acadia National Park Aunt Betty Pond Inlet pH 30 6.33
5 Acadia National Park Aunt Bettys Pond AcidNeuCap_ueqL 8 146.
6 Acadia National Park Aunt Bettys Pond TotalNitrogen_mgL 29 0.39The results show some differences in water quality measurements between monitoring sites. For example, Aunt Betty Pond Inlet has an average total nitrogen level of 0.33 mg/L, while Aunt Bettys Pond has a slightly higher average of 0.39 mg/L. Both sites have the same average acid neutralizing capacity of 146 microeq/L. These results suggest that some water quality measurements vary between monitoring sites, while others are similar.
Eagle Lake Yearly Trends
I will focus on Eagle Lake in Acadia National Park to examine how water quality measurements have changed over time. I will filter the tidy dataset to select Eagle Lake observations and extract the year from the sampling date. Then, I will calculate the annual mean and number of observations for each of the four water quality measurements. This will help me identify yearly changes in water chemistry at Eagle Lake.
Code
eagle_yearly <- water_tidy %>%
filter(
UnitName == "Acadia National Park",
SiteName == "Eagle Lake"
) %>%
mutate(Year = as.integer(format(EventDate, "%Y"))) %>%
group_by(Year, Measurement) %>%
summarise(
Mean = round(mean(Value), 2),
Count = n(),
.groups = "drop"
)
eagle_yearly# A tibble: 75 × 4
Year Measurement Mean Count
<int> <chr> <dbl> <int>
1 2006 AcidNeuCap_ueqL 45.4 2
2 2006 TotalNitrogen_mgL 0.13 3
3 2006 TotalPhosphorus_ugL 5 1
4 2006 pH 6.38 2
5 2007 AcidNeuCap_ueqL 49.5 4
6 2007 TotalNitrogen_mgL 0.1 1
7 2007 TotalPhosphorus_ugL 1.65 2
8 2007 pH 6.5 4
9 2008 AcidNeuCap_ueqL 49.7 4
10 2008 TotalNitrogen_mgL 0.1 2
# ℹ 65 more rowsEagle Lake Yearly Trends Visualization
I will use line charts to visualize the annual average water quality measurements at Eagle Lake. I will create a separate panel for each measurement because they have different units and scales. This will help me examine changes in pH, acid neutralizing capacity, total nitrogen, and total phosphorus over time.
Code
ggplot(eagle_yearly, aes(x = Year, y = Mean)) +
geom_line(color = "steelblue", linewidth = 1) +
geom_point(color = "steelblue", size = 2) +
facet_wrap(~ Measurement, scales = "free_y") +
labs(
title = "Annual Water Quality Trends at Eagle Lake",
subtitle = "Acadia National Park",
x = "Year",
y = "Annual Mean"
) +
theme_minimal()
The line charts show changes in water quality measurements at Eagle Lake between 2006 and 2024. Acid neutralizing capacity generally increased during the early years but fluctuated afterward. Total nitrogen reached a higher level around 2011 and remained relatively stable at lower levels in later years. Total phosphorus showed fluctuations throughout the study period, with a noticeable decrease around 2020.
The average pH remained relatively stable, mostly between 6.4 and 6.7, although a noticeable decrease occurred around 2017. Overall, the results suggest that water chemistry at Eagle Lake has changed over time, but the patterns differ across the four measurements. Some yearly averages are based on only a few observations, so these trends should be interpreted with caution.
Conclusions
In this project, I transformed the original water quality dataset from wide to long format using tidyr and dplyr. I selected 10 relevant variables, renamed the four water quality measurements, and handled missing values. I also converted the date and time variables and excluded one unusual negative pH measurement from Eagle Lake.
The overall analysis showed an average pH of 6.87, acid neutralizing capacity of 625.43 microeq/L, total nitrogen of 0.51 mg/L, and total phosphorus of 22.89 microg/L. The results also showed differences in water chemistry across monitoring sites.
At Eagle Lake, the yearly trends showed that acid neutralizing capacity generally increased in the early years and fluctuated afterward. Total nitrogen remained relatively stable in later years, while total phosphorus showed more variation. The average pH was mostly between 6.4 and 6.7.
Overall, the analysis suggests that water chemistry at Eagle Lake has changed over time, although the patterns differ across measurements. Since some years have only a few observations, the results should be interpreted with caution. Further analysis with more consistent sampling could help identify long-term trends.
Citation
I used ChatGPT (GPT-5.6 Luna), developed by OpenAI, to help me understand some of the functions I used in this assignment and to check my work.
OpenAI. (2026). ChatGPT (GPT-5.6 Luna) [Large language model]. [https://chatgpt.com/ Accessed October 9, 2026].
Video Explainer
Link: