Dplyr window function over time frame Instead of the amount of rows

user_4550050 0
Viewed 175

I have an R tibble which has a Time column in the following format: "2020-08-19 07:59:05 IDT" I want to create an additional column that makes some aggregation function but in the scope of the time frame, for example I have an additional column Amount and I want to create an additonal column that for each row sum all the amount from the previous 10 sec.

                 Time          Amount.  SumOverTime(10 sec)
1   2020-08-19 07:57:18             0.  0
2   2020-08-19 07:57:19             1.  1
3   2020-08-19 07:57:19             1.  2
4   2020-08-19 07:57:20             0.  2
5   2020-08-19 07:57:20             2.  4
6   2020-08-19 07:57:20             0.  4
7   2020-08-19 07:57:21             0.  4
8   2020-08-19 07:57:22             0.  4
9   2020-08-19 07:57:23             1.  5
10  2020-08-19 07:57:28             0.  5
11  2020-08-19 07:57:30             0.  3
12  2020-08-19 07:57:31             0.  1
13  2020-08-19 07:57:32             0.  1

Thanks for the help!

dplyr tidyverse r
2 Answers

Using the {slider} package

library(dplyr)
library(slider)

df %>% 
  count(Time, wt = Amount, name = "Amount") %>% 
  mutate(SumOverTime = slide_period_dbl(.x = df$Amount, .i = df$Time, 
                                        .period = "second", .before = 10,
                                        .f = sum, 
                                        .complete = FALSE))

#>                   Time Amount SumOverTime
#> 1  2020-08-19 07:57:18      0           0
#> 2  2020-08-19 07:57:19      2           2
#> 3  2020-08-19 07:57:20      2           4
#> 4  2020-08-19 07:57:21      0           4
#> 5  2020-08-19 07:57:22      0           4
#> 6  2020-08-19 07:57:23      1           5
#> 7  2020-08-19 07:57:28      0           5
#> 8  2020-08-19 07:57:30      0           3
#> 9  2020-08-19 07:57:31      0           1
#> 10 2020-08-19 07:57:32      0           1

Note that we pre-aggregate over time with count since multiple events occur within the same second, and the problem seems ill-defined otherwise, so this differs from the expected output.

user_61976490

Simulating some data here:

library(dplyr)
library(purrr)

df <- tibble(
      time = seq(lubridate::now()-10, lubridate::now(), length.out = 100) + runif(100,-10,10),
      Amount = runif(100, 0, 5)
    ) %>%
    arrange(time)

Then, you can use purrr:map and provide your full df as an extra argument. That allows you to easily filter it and pull a sum of the Amount column. Probably not super performant, but it does the job :)

df.2 <- df %>%
    mutate(
      SumOverTime = map_dbl(time, ., .f = ~{
        .y %>% filter(time >= .x-10, time <=.x) %>% pull(Amount) %>% sum(na.rm=T)
      })
    )
user_13351740
Related