Rolling sum with double indexing in R

user_4455523 0
Viewed 149

I would like to calculate a rolling sum or a rolling count with respect to two index columns in R. The following data table has a column that shows the amount of sale and two date columns. I would like to create a 4th column that gives me the number of dates in Column index2 that are smaller than the date in Column index1, and I also want to have 5th column that gives me the sum of sales associated with those counted in the fourth column.

sales_vec <- c(2, 4, 3, 5)
index1_vec <- as.Date("2019-08-29") + c(0, 2, 5, 6)
index2_vec <- as.Date("2019-08-29") + c(-5, 2, 1, -3)
company <- tibble(
  sales = sales_vec,
  index1 = index1_vec ,
  index2 = index2_vec 
)

> print(company)
# A tibble: 4 x 3
  sales index1     index2    
  <dbl> <date>     <date>    
1     2 2019-08-29 2019-08-24
2     4 2019-08-31 2019-08-31
3     3 2019-09-03 2019-08-30
4     5 2019-09-04 2019-08-26

My outcome should look like this:

# A tibble: 4 x 5
  sales index1     index2     rollingCount rollingSum
  <dbl> <date>     <date>            <dbl>      <dbl>
1     2 2019-08-29 2019-08-24            2          7
2     4 2019-08-31 2019-08-31            3         10
3     3 2019-09-03 2019-08-30            4         14
4     5 2019-09-04 2019-08-26            4         14

The first row of rollingCount is 2 because there are two dates in Column index2 that are smaller than the first row of index1, and the sum of sales associated with those two rows are 2 + 5 =7, which shows up in the first row of rollingSum. The second row of rollingCount is 3 because there are three dates in Column index2 that are smaller than the second row of index1, and the sum of sales associated with those three rows are 2 + 5 + 3 = 10, which shows up in the second row of rollingSum. And so on.

I am familiar with the family of "slide" commands to do rolling calculations but I am struggling with this task as it has two index columns.

rolling-computation tidyverse conditional-statements r
2 Answers

Here are couple of approaches :

Using rowwise :

library(dplyr)
library(purrr)

company %>%
  rowwise() %>%
  mutate(rollingCount = sum(index1 > .$index2), 
         rollingSum = sum(.$sales[index1 > .$index2]))


#  sales index1     index2     rollingCount rollingSum
#  <dbl> <date>     <date>            <int>      <dbl>
#1     2 2019-08-29 2019-08-24            2          7
#2     4 2019-08-31 2019-08-31            3         10
#3     3 2019-09-03 2019-08-30            4         14
#4     5 2019-09-04 2019-08-26            4         14

and using map_dbl from purrr :

company %>%
   mutate(rollingCount = map_dbl(index1, ~{
                 vec <- .x > index2
                 sum(vec)
                 }),
          rollingSum = map_dbl(index1, ~sum(sales[.x > index2])))
user_39629140

In base R, we can use sapply to loop over the 'index1' column, create an logical vector with index2 column, use that to get the sum of logical vector and the sum of subset of 'sales'

cbind(company, t(sapply(company$index1, function(x) {
        i1 <- company$index2  < x
    c(rollingCount = sum(i1), rollingSum = sum(company$sales[i1])) })))
#  sales     index1     index2 rollingCount rollingSum
#1     2 2019-08-29 2019-08-24            2          7
#2     4 2019-08-30 2019-08-31            3         10
#3     3 2019-09-03 2019-08-30            4         14
#4     5 2019-09-04 2019-08-26            4         14

Or another option is tidyverse

library(dplyr)
library(purrr)
map_dfr(company$index1, ~ {
       i1 <- company$index2 < .x
       tibble(rollingCount = sum(i1), rollingSum = sum(company$sales[i1]))}) %>%
    bind_cols(company, .)
# A tibble: 4 x 5
#  sales index1     index2     rollingCount rollingSum
#  <dbl> <date>     <date>            <int>      <dbl>
#1     2 2019-08-29 2019-08-24            2          7
#2     4 2019-08-30 2019-08-31            3         10
#3     3 2019-09-03 2019-08-30            4         14
#4     5 2019-09-04 2019-08-26            4         14
user_37322710
Related