CODEWITHSUNDEEP
rdplyrtidyr
pachadotdev
pachadotdev

Reputation: 3765

Maximum rolling sum in data frame

I want to obtain the largest possible sum for a time period.

For example, I have a data frame with units in store for a certain product:

library(tidyverse)

data <- tibble(
    date = paste("Day", 1:5),
    units_in_store = c(10,3,-2,1,-1)
)

cumsum(data$units_in_store) would return 11 but the largest possible sum in that case is 13, and summing one by one the values would be 13, 11, 12 and 11.

Is there a way to compute the maximum moving/rolling sum?

In other words: How can I compute the largest possible sum with

Day 1 + Day2

Day 1 + Day 2 + Day 3

Day 1 + Day 2 + Day 3 + Day 4

etc?

This would be the expected output:

data %>% mutate(units2 = something(units_in_store))

# A tibble: 5 x 3
  date  units_in_store units2
  <chr>          <dbl>  <dbl>
1 Day 1             10     13
2 Day 2              3     13
3 Day 3             -2     13
4 Day 4              1     13
5 Day 5             -1     13

Upvotes: 1

Views: 92

Answers (2)

Jilber Urbina
Jilber Urbina

Reputation: 61154

We can use Reduce to get the result "summing one by one":

> Reduce("+",data$units_in_store, accumulate = TRUE)[-1]
[1] 13 11 12 11

This is an alternative to cumsum see @doviod´s answer. Then you can select the max value.

Upvotes: 1

iod
iod

Reputation: 7592

You mean something like this?

data<-mutate(data,units2=max(cumsum(units_in_store)))

output:

  date  units_in_store units2
  <chr>          <dbl> <dbl>
1 Day 1             10    13
2 Day 2              3    13
3 Day 3             -2    13
4 Day 4              1    13
5 Day 5             -1    13

Upvotes: 1

Related Questions