Rolling Sum by Another Variable in R

by

If your data is big, you might want to check out this solution which uses data.table. It is pretty fast. If you need more speed, you can always change mapply to mcmapply and use multiple cores.

#Load data.table and convert to data.table object
require(data.table)
setDT(data)[,ID2:=.GRP,by=c("ID")]

#Build reference table
Ref <- data[,list(Compare_Value=list(I(USD)),Compare_Date=list(I(Date))), by=c("ID2")]

#Use mapply to get last seven days of value by id
data[,Roll.Val := mapply(RD = Date,NUM=ID2, function(RD, NUM) {
                  d <- as.numeric(Ref$Compare_Date[[NUM]] - RD)
                  sum((d <= 0 & d >= -7)*Ref$Compare_Value[[NUM]])})]

More Related Contents:

  1. How to create a lag variable within each group?
  2. data.table objects assigned with := from within function not printed
  3. .EACHI in data.table?
  4. Use a value from the previous row in an R data.table calculation
  5. How to efficiently calculate distance between pair of coordinates using data.table :=
  6. Sample random rows within each group in a data.table
  7. Faster way to read fixed-width files
  8. Melt using patterns when variable names contain string information – avoid coercion to numeric
  9. Error: package or namespace load failed for ggplot2 and for data.table
  10. Why apply() returns a transposed xts matrix?
  11. No RTools compatible with R version 3.5.0 was found
  12. knitr gets tricked by data.table `:=` assignment
  13. Add multiple columns to R data.table in one function call?
  14. Subsetting data.table using variables with same name as column
  15. data.table equivalent of tidyr::complete()
  16. data.table merge based on date ranges
  17. Reason behind speed of fread in data.table package in R
  18. best way to transpose data.table
  19. data.table row-wise sum, mean, min, max like dplyr?
  20. Select NA in a data.table in R
  21. merging a large list of xts objects
  22. Using data.table i and j arguments in functions
  23. R data.table breaks in exported functions
  24. Applying a function to each row of a data.table
  25. Union of intersecting vectors in a list in R
  26. Empty factors in “by” data.table
  27. data.table is not handling integer64 in by statement
  28. How to use data.table within functions and loops?
  29. What you can do with a data.frame that you can’t with a data.table?
  30. data.table: anonymous function in j

Leave a Comment