Class 607, Assignment 3b

Author

Troy Tournat

Published

September 17, 2026

Window Functions

Find (or ask an LLM to generate!) a dataset that includes time series for two or more separate items. For example, you could use end of day stock or cryptocurrency prices since Jan 1, 2022 for several instruments. Use window functions (in SQL or dplyr) to calculate the year-to-date average and the six-day moving averages for each item.

Introduction

This dataset is real dates and categories but fake sample data (due to privacy) from WastewaterSCAN, an organization part of the WastewaterSCAN / SCAN project, a partnership between Stanford University, Emory University, and Verily funded philanthropically through a gift to Stanford University.

They publicly monitor infectious diseases across the USA through local wastewater systems. As an epidemiologist, this information is important to our work monitoring the spread of diseases. Since the new respiratory season has officially started August 1st and there has been an increase in COVID in California according to the CDC.

Tackling the problem: I will be continuing where I left off from assignment 1, and looking at last collection date and sar-cov-2 at Santa Cruz, California and Albany, New York. I will be using R rather than SQL because my data is already in R.

Anticipated data challenges: I think I have two separate items, but I anticipate I will need to adjust.

Citations:

A. B. Boehm, M. K. Wolfe, A. L. Bidwell, A. Zulli, B. J. White, B. Shelden, D. Duong. Pathogen nucleic acids data in wastewater solids from 147 treatment plants in the United States: 2024-2025. Data in Brief, 2026. Link to paper.
About CDC’s wastewater monitoring program (2026) Centers for Disease Control and Prevention. Available at: https://www.cdc.gov/wastewater/about/index.html#cdc_survey_profile_how_surveys_are_conducted-data-sources (Accessed: 03 September 2026).
Anthropic. (2025). Claude Opus 4.5 [Large language model]. (https://claude.ai/) Accessed January 8, 2026. Link to chat here.

Analysis

#Not shown: Location on computer where I am grabbing the data and creating a saved location

Code

#Install packages needed 
pacman::p_load(
  # Package Install and Management
  pacman,           # package install/load
  janitor,          #clean up data names 
  
  # Project and File Management
  readr,            # import data
  httr,             #github passkey 
  usethis,          #r environment
  RPostgres,        #SQL 
  DBI,              #SQL 
  rstuioapi,        #api
  
  # General Data Management
  dplyr,            # data management
  tidyr,            # data management
  lubridate,        # work with dates
  zoo,              # work with dates
  tidyverse,        # work with dates
  scales,           # ggplot 
  gmodels,           # freq tables SAS 
  slider           #rolling average
)

Installing package into 'C:/Users/tournat/AppData/Local/R/win-library/4.6'
(as 'lib' is unspecified)

Code

#import dataset
ww_data <- read.csv(paste0(csv_location, "wastewater-cleaned.csv"), header=TRUE, stringsAsFactors = FALSE)


##Review and clean data 
#cleaning names slightly 
ww_data <- ww_data %>% clean_names

#change to date
class(ww_data$sample_date)

[1] "character"

Code

ww_data$sample_date <- as.Date(ww_data$sample_date, format = "%Y-%m-%d")

#subset to focus on 2026
ww_data_sub <- ww_data%>% 
  filter(sample_date > "2025-12-01") %>% 
  select(sample_date, abbreviated_name, raw_concentration)

calculate the year-to-date average and the six-day moving averages for each item

Code

#ytd average 
ww_data_ytd <- ww_data_sub %>% 
  group_by(abbreviated_name) %>% 
  arrange(sample_date, .by_group = TRUE)%>% 
  filter(sample_date > "2025-12-31") %>% 
  #trying with summarise to use each abbreviated name
  summarise( 
    avg = mean(raw_concentration, na.rm = TRUE),
    .groups = "drop"
  )
ww_data_ytd

Code

#6 day moving average 
ww_data_rolling <- ww_data_sub %>% 
  group_by(abbreviated_name) %>% 
  arrange(sample_date, .by_group = TRUE)%>% #need to do in order for code to work
  filter(sample_date > "2025-12-31") %>% 
  mutate(
#using slider recommendation because want 6 day window regardless of sample collection and can deal with NAs better
     roll6 = slide_dbl(raw_concentration, mean, .before = 5, na.rm = TRUE)
  ) %>%
  ungroup()

Conclusion

I was having a hard time with the SQL to R code examples, I ended up taking a lot of time trying to calculate correctly. I would like to go back and do some double checks especially on the rolling average since usually samples are collected every week.