Sum conditional on difference between timestamps

user_10035626 0
Viewed 27

Let's say we observe a video game player collecting points. Each observation reports how many points the player has collected since the last visit. Now I want to create an additional variable indicating how many points the user has collected during the last x (e.g., 90) seconds, including the current observation.

Example data:

example_da = data.frame(time = c("2015-04-11 21:24:34", "2015-04-11 21:24:50", "2015-04-11 21:25:20", "2015-04-11 21:27:52", "2015-04-11 21:27:59", "2015-04-11 21:28:13",
                                 "2015-04-11 21:30:06", "2015-04-11 21:31:05", "2015-04-11 21:31:47", "2015-04-11 21:38:01", "2015-04-11 21:39:05", "2015-04-11 21:40:06"),
                        points = c(2,3,1,6,2,5,1,1,3,5,2,4))

> example_da
                  time points
1  2015-04-11 21:24:34      2
2  2015-04-11 21:24:50      3
3  2015-04-11 21:25:20      1
4  2015-04-11 21:27:52      6
5  2015-04-11 21:27:59      2
6  2015-04-11 21:28:13      5
7  2015-04-11 21:30:06      1
8  2015-04-11 21:31:05      1
9  2015-04-11 21:31:47      3
10 2015-04-11 21:38:01      5
11 2015-04-11 21:39:05      2
12 2015-04-11 21:40:06      4

For instance, for observation 3 ("2015-04-11 21:25:20") we sum up the points of "2015-04-11 21:24:34" (= 2), "2015-04-11 21:24:50" (=3), "2015-04-11 21:25:20" (=1), since these points were all collected during the preceding 90 seconds, resulting in 6 points for our new variable "sum_points_preceding_90_seconds".

> target_da = data.frame(time = c("2015-04-11 21:24:34", "2015-04-11 21:24:50", "2015-04-11 21:25:20", "2015-04-11 21:27:52", "2015-04-11 21:27:59", "2015-04-11 21:28:13",
+                                 "2015-04-11 21:30:06", "2015-04-11 21:31:05", "2015-04-11 21:31:47", "2015-04-11 21:38:01", "2015-04-11 21:39:05", "2015-04-11 21:40:06"),
+                        points = c(2,3,1,6,2,5,1,1,3,5,2,4),
+                        sum_points_preceding_90_seconds = c(2, 5, 6, 6, 8, 13, 1, 2, 5, 5, 7, 6))
> 
> 
> target_da                    
                  time points sum_points_preceding_90_seconds
1  2015-04-11 21:24:34      2                               2
2  2015-04-11 21:24:50      3                               5
3  2015-04-11 21:25:20      1                               6
4  2015-04-11 21:27:52      6                               6
5  2015-04-11 21:27:59      2                               8
6  2015-04-11 21:28:13      5                              13
7  2015-04-11 21:30:06      1                               1
8  2015-04-11 21:31:05      1                               2
9  2015-04-11 21:31:47      3                               5
10 2015-04-11 21:38:01      5                               5
11 2015-04-11 21:39:05      2                               7
12 2015-04-11 21:40:06      4                               6
dplyr lubridate r time date
1 Answers

You can do this with the slider package using slide_index_sum(). It allows you specify an index and then create bounds before or after each element of that index to generate the sliding windows.

I think there may be an error with your expected result for 2015-04-11 21:31:47? It looks like it should result in 4 rather than 5?

You may need to adjust before depending on your exact requirements.

library(slider)
library(dplyr)

example_da <- tibble(
  time = c(
    "2015-04-11 21:24:34", "2015-04-11 21:24:50", "2015-04-11 21:25:20", 
    "2015-04-11 21:27:52", "2015-04-11 21:27:59", "2015-04-11 21:28:13",
    "2015-04-11 21:30:06", "2015-04-11 21:31:05", "2015-04-11 21:31:47", 
    "2015-04-11 21:38:01", "2015-04-11 21:39:05", "2015-04-11 21:40:06"),
  points = c(2,3,1,6,2,5,1,1,3,5,2,4)
)

example_da <- mutate(example_da, time = as.POSIXct(time, "UTC"))

# The current time + 89 seconds before it = 90 seconds total
example_da <- example_da %>%
  mutate(
    sum_points_preceding_90_seconds =
      slide_index_sum(
        x = points,
        i = time,
        before = 89
      )
  )

example_da
#> # A tibble: 12 × 3
#>    time                points sum_points_preceding_90_seconds
#>    <dttm>               <dbl>                           <dbl>
#>  1 2015-04-11 21:24:34      2                               2
#>  2 2015-04-11 21:24:50      3                               5
#>  3 2015-04-11 21:25:20      1                               6
#>  4 2015-04-11 21:27:52      6                               6
#>  5 2015-04-11 21:27:59      2                               8
#>  6 2015-04-11 21:28:13      5                              13
#>  7 2015-04-11 21:30:06      1                               1
#>  8 2015-04-11 21:31:05      1                               2
#>  9 2015-04-11 21:31:47      3                               4
#> 10 2015-04-11 21:38:01      5                               5
#> 11 2015-04-11 21:39:05      2                               7
#> 12 2015-04-11 21:40:06      4                               6
user_73947430
Related