[datatable-help] I have been agnozing over how to do a running cummulative sum over a particular date range

[jim holtman]

try this using 'filter':

> x <- read.table(text = "    Date Value Roll.Val
+  1:    1    14       14
+  2:    2     7       21
+  3:    3     9       30
+  4:    4     5       35
+  5:    5    10       45
+  6:    6    10       55
+  7:    7    15       70
+  8:    8    14       84
+  9:    9     8       78
+ 10:   10    12       83", as.is = TRUE, header = TRUE)
>
> n <- 8  # items to include in running total
>
> # create vector to sum with leading zeros
> vec <- c(rep(0, n - 1), x$Value)
>
> # compute sum with 'filter', drop first 7 and store back
> x$mySum <- filter(vec, rep(1, n), sides = 1)[-seq(1, n - 1)]
>
>
> x
    Date Value Roll.Val mySum
1:     1    14       14    14
2:     2     7       21    21
3:     3     9       30    30
4:     4     5       35    35
5:     5    10       45    45
6:     6    10       55    55
7:     7    15       70    70
8:     8    14       84    84
9:     9     8       78    78
10:   10    12       83    83
>

Jim Holtman
Data Munger Guru

What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.


On Mon, Jul 14, 2014 at 7:27 PM, Mike.Gahan <michael.gahan at gmail.com> wrote:
> Here is an example of how I would approach this problem.  I am certainly open
> to more elegant solutions.
>
>
> require(data.table)
>
> #Build some sample data
> data <- data.table(Date=1:20,Value=rpois(20,10))
>
> #Build reference table.  This is where we keep the list of Dates and Values
> that will be referenced for
> #each individual data
> Ref <- data[,list(Compare_Value=list(I(Value)),Compare_Date=list(I(Date)))]
>
> #Use lapply to get last seven days of value by id
> data[,Roll.Val := lapply(Date, function(x) {
>                   d <- as.numeric(Ref$Compare_Date[[1]] - x)
>                   sum((d <= 0 & d >= -7)*Ref$Compare_Value[[1]])})]
>
> head(data,10)
>
>     Date Value Roll.Val
>  1:    1    14       14
>  2:    2     7       21
>  3:    3     9       30
>  4:    4     5       35
>  5:    5    10       45
>  6:    6    10       55
>  7:    7    15       70
>  8:    8    14       84
>  9:    9     8       78
> 10:   10    12       83
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/I-have-been-agnozing-over-how-to-do-a-running-cummulative-sum-over-a-particular-date-range-tp4693953p4694007.html
> Sent from the datatable-help mailing list archive at Nabble.com.
> _______________________________________________
> datatable-help mailing list
> datatable-help at lists.r-forge.r-project.org
> https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help