Aggregating timely data efficiently in R with dplyr

user_7740319 0
Viewed 44

I have data like this:

set.seed(2020)
df_time = data.frame(Time = as.Date(1:100), value = round(runif(100, min = 0, 100)))
head(df_time)

          Time value
1   1970-01-02    65
2   1970-01-03    39
3   1970-01-04    62
4   1970-01-05    48
5   1970-01-06    14
6   1970-01-07     7
7   1970-01-08    13
8   1970-01-09    39
9   1970-01-10     0
10  1970-01-11    62

And this:

df = data.frame(from= as.Date(c(3,6, 20)),to= as.Date(c(8,7, 24)),)
head(df)

My goal is to mutate the dataframe df such that it adds the sum of the values between the two given dates (excluding the from date and including the to date), i.e.

        from         to sum_value
1 1970-01-04 1970-01-09       121
2 1970-01-07 1970-01-08        13
3 1970-01-21 1970-01-25       204

Atthe moment I am doing it with a loop but that takes way too long for the amount of data I have. Does anyone knows a better solution (e.g. with dplyr)?

dplyr dataframe r
1 Answers

You can use rowwise() in this case to tell dplyr to evaluate df row by row:

df %>% 
  rowwise() %>% 
  mutate(sum_value = df_time %>% 
           filter(Time > from, Time <= to) %>% 
           pull(value) %>% 
           sum()) %>% 
  ungroup() # ungroup is used to restore default behaviour of dplyr

#> # A tibble: 3 x 3
#>   from       to         sum_value
#>   <date>     <date>         <dbl>
#> 1 1970-01-04 1970-01-09       121
#> 2 1970-01-07 1970-01-08        13
#> 3 1970-01-21 1970-01-25       204

This is how I would do since I find the code easy to understand. But it's basically still a loop under the hood as far as I get it.

A different and potentially faster approach could be to use data.table. I'm not as familiar with the syntax so there might be a better way, but this works:

library(data.table)
# convert to data.table
df_time <- setDT(df_time)
df <- setDT(df)

# duplicate Time column since foverlaps needs two of them
df_time <- df_time[, Time2 := Time]
# Since from day should not be included
df <- df[, from := from + 1]
  
setkey(df, from, to)
res <- foverlaps(df_time, df, by.x = c("Time", "Time2"), by.y = c("from", "to"), type = "within")
res <- res[, .(value = sum(value)), keyby = .(from, to)]
res[, from := from - 1]
#>          from         to value
#> 1:       <NA>       <NA>  4622
#> 2: 1970-01-04 1970-01-09   121
#> 3: 1970-01-07 1970-01-08    13
#> 4: 1970-01-21 1970-01-25   204
user_50288410
Related