How do I get a daily average for data points generated every 15 minutes?

Question

This is my first question and I'm also a very new learner to r so please bear with me. I am working on some gauge data from about 4 months and it took temperature readings every 15 minutes. I am wanting to get a daily average from this data and then graph it. I have looked some stuff up and tried to run what people have suggested, but keep running into this error:

library(lubridate)
library(dplyr)

DownGauge %>% 
+   group_by(day = day(date)) %>%
+   summarise(mean_Temperature = mean(Temperature))
**Error in `group_by()`:
ℹ In argument: `day = day(date)`.
Caused by error in `as.POSIXlt.default()`:
! do not know how to convert 'x' to class “POSIXlt”**

Any help would be much appreciated. I am really struggling to find a good answer to this question. I have included the dput and head of my data below because I saw that is good policy to provide. Let me know if anything else is needed. Thanks.

dput(head(DownGauge))
structure(list(Date = structure(c(1709770500, 1709771400, 1709772300, 
1709773200, 1709774100, 1709775000), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), Temperature = c(8, 7.9, 7.9, 7.9, 7.9, 7.9)), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

head(DownGauge)
# A tibble: 6 × 2
  Date                Temperature
  <dttm>                    <dbl>
1 2024-03-07 00:15:00         8  
2 2024-03-07 00:30:00         7.9
3 2024-03-07 00:45:00         7.9
4 2024-03-07 01:00:00         7.9
5 2024-03-07 01:15:00         7.9
6 2024-03-07 01:30:00         7.9`

I tried to change the as.POSIXlt to as.POSIXct cause I thought that would fix it but when I created a new data frame with the changed date format, it just switched automatically back to as.POSIXlt. Not sure why.

Answer 1

You are interested in grouping the date. ie if you group by day(date) then 1st of every month would be grouped together and thats incorrect. Consider using the following:

DownGauge %>%
   group_by(Date = as.Date(Date)) %>%
   summarise(mean_temp = mean(Temperature))

# A tibble: 1 × 2
  Date        mean_temp
  <date>          <dbl>
1 2024-03-07       7.92

Answer 2

The United States Geological Survey provides access to their water quality database which includes 15-minute observation of water temperature. Their data can be accessed in R using the dataRetrieval package. Below is an example of how to access 4 months of 15-minute water temperature observations from one gauging station, calculate daily mean, and plot the results.

library(tidyverse)
library(dataRetrieval)

# Download 15-minute water temperature data from U.S. Geological Survey
wtemp <- readNWISuv(
  siteNumbers = '02035000', # USGS site number (Cartersville, VA)
  parameterCd = '00010', # this is the pcode for water temperature
  startDate = '2022-04-01',
  endDate = '2022-08-31',
  tz = 'America/New_York') %>%
  renameNWISColumns()

# Calculate daily mean water temperature
daily_wtemp <- wtemp %>%
  mutate(Date = date(dateTime)) %>%
  group_by(Date) %>%
  summarise(DailyMean = mean(Wtemp_Inst, na.rm = TRUE)) %>%
  ungroup()

# Plot daily mean water temperature
daily_wtemp %>%
  ggplot(aes(x = Date, y = DailyMean)) +
  geom_line() +
  geom_point() +
  labs(x = NULL,
       y = expression(Daily~Mean~Water~Temperature~(degree*C))) +
  theme_bw()

^{Created on 2024-06-26 with reprex v2.1.0}