Average values between two dates by group

Question

I have two data frames, one with a value for each day and camera (df1) and another with the date range for each camera (df2). I need to average the values in df1 for each camera over the range of dates in df2.

**This is a very simplified version of my data. I have ~300 cameras with multiple date ranges per camera that I need to average values for.

df1 <- data.frame(camera=c("Cam 1","Cam 1","Cam 1","Cam 2","Cam 2","Cam 2","Cam 3","Cam 3","Cam 3"),
date=c("2011-03-15","2011-03-16","2011-03-17","2011-03-15","2011-03-16","2011-03-17","2011-03-17","2011-03-18","2011-03-19"),
value=c(1,0,2,3,1,2,2,1,0))

df1$date <- as.Date(df1$date,format='%Y-%m-%d')

df2 <- data.frame(camera=c("Cam 1","Cam 2","Cam 3"),
start_date=c("2011-03-15", "2011-03-15","2011-03-17"),
end_date=c("2011-03-17","2011-03-17","2011-03-19"))

df2$start_date <- as.Date(df2$start_date,format='%Y-%m-%d')
df2$end_date <- as.Date(df2$end_date,format='%Y-%m-%d')

The problem I am running into is that multiple cameras have values for the same dates so when I average over a date range I get the average over multiple cameras instead of just one (I need the average over that date range for just that camera). I'm using packages dplyr and tidyr and I thought grouping by camera would fix that but it hasn't. I also get the same result with the mutate or summarize functions. I have found a lot of help on averaging over a date range or by group but not both. Any help would be appreciated here! Here is my code:

 average<-df2 %>%
  group_by(camera, start_date, end_date) %>%
  mutate(avg= mean(df1$value[between(df1$date, start_date, end_date)])) %>%
  ungroup

I've been getting a table like this:

camera	start_date	end_date	avg
Cam 1	2011-03-15	2011-03-17	1.57
Cam 2	2011-03-15	2011-03-17	1.57
Cam 3	2011-03-17	2011-03-19	1.40

When I want this:

camera	start_date	end_date	avg
Cam 1	2011-03-15	2011-03-17	1
Cam 2	2011-03-15	2011-03-17	2
Cam 3	2011-03-17	2011-03-19	1

akrun akrun · Accepted Answer · 2021-06-04T18:20:58

We could use a non-equi join

library(data.table)
setDT(df1)[, .(start_date = date, end_date = date, camera,
     value)][df2, .(avg = mean(value)),
    on = .(camera, start_date >= start_date,
        end_date <= end_date), by = .EACHI]

-output

   camera start_date   end_date avg
1:  Cam 1 2011-03-15 2011-03-17   1
2:  Cam 2 2011-03-15 2011-03-17   2
3:  Cam 3 2011-03-17 2011-03-19   1

Or using tidyverse

library(dplyr)
library(fuzzyjoin)
fuzzy_left_join(df1, df2, by = c("camera", "date" = "start_date", 
       "date" = "end_date"), match_fun = list(`==`, `>=`, `<=`)) %>%
    group_by(camera = camera.x, start_date, end_date) %>% 
    summarise(avg = mean(value), .groups = 'drop')

-output

# A tibble: 3 x 4
  camera start_date end_date     avg
  <chr>  <date>     <date>     <dbl>
1 Cam 1  2011-03-15 2011-03-17     1
2 Cam 2  2011-03-15 2011-03-17     2
3 Cam 3  2011-03-17 2011-03-19     1

Average values between two dates by group

1 Answers