R and Data.table - applying rollapply over multiple columns

Question

I would really appreciate if you can help me do the rollapply for each column of the data.table

    time                AUD NZD   EUR GBP    USD AUD 

1   2013-01-01 20:00    0.213     -0.30467   -0.127515 
2   2013-01-01 20:05    0.21191   -0.30467   -0.127975 
3   2013-01-01 20:10    0.212185  -0.304965  -0.127935 
4   2013-01-01 20:15    0.212055  -0.30511   -0.1288 
5   2013-01-01 20:20    0.211225  -0.30536   -0.12938 
6   2013-01-01 20:25    0.211185  -0.30527   -0.129195 
7   2013-01-01 20:30    0.21159   -0.3059    -0.13043 
8   2013-01-01 20:35    0.21142   -0.304955  -0.13155 
9   2013-01-01 20:40    0.21093   -0.30419   -0.132715 
10  2013-01-01 20:45    0.2078    -0.30339   -0.13544
11  2013-01-01 20:50    0.208445  -0.30304   -0.135645
12  2013-01-01 20:55    0.208735  -0.30185   -0.1357 
13  2013-01-01 21:00    0.20891   -0.303265  -0.13722 
14  2013-01-01 21:05    0.20903   -0.30428   -0.137495
15  2013-01-01 21:10    0.209615  -0.305495  -0.13734 
16  2013-01-01 21:15    0.20981   -0.30588   -0.13772 
17  2013-01-01 21:20    0.209855  -0.306935  -0.13801
18  2013-01-01 21:25    0.209585  -0.30604   -0.138045 
19  2013-01-01 21:30    0.210105  -0.3061    -0.137765 
20  2013-01-01 21:35    0.210335  -0.30734   -0.138525

Code that works:

library("zoo")
library("data.table")

calculateAverage <- function (x,N) {
        tempDataStorage <- rollapply(out[,1], N, mean)
}

col1 <- out[,2]
col2 <- out[,3]
col3 <- out[,4]

average1 <- calculateAverage(col1, 2)
average2 <- calculateAverage(col2, 2)
average3 <- calculateAverage(col3, 2)

combine <- cbind(average1, average2, average3)
tempMatrix <- matrix(, nrow = nrow(out), ncol = ncol(out))
tempMatrix[2:nrow(out), 1:3] <- combine

Suggestion from SO:

test <- lapply(out[,with=F], function(x) rollapply(x,width=2, FUN=mean))

Challenges: 1. The code I created works, but it feels inefficient and not generic. It needs to be modified whenever the number of cols changes 2. Suggestion from SO output is list which is not useful to me

If an alternate method is suggested, I would be really appreciate it!

Thanks in advance Edit: Data table added

Please paste a sample of your data (the `out` object). Also, every `data.table` is also a list, try adding `as.data.table` after `lapply`. — m-dz, Aug 22 '17 at 10:24
can you please paste your data in a readable format instead of a screenshot? — mtoto, Aug 22 '17 at 10:41
So why didn't you try my answer on that same post you've linked? It should be much more efficient and looks generic to me. It also outputs a data.table instead of a list. — David Arenburg, Aug 22 '17 at 10:43
@m-dz, I will try and get the data to you the soonest. I tried the as.data.table and it just gave me 3 rows.. — boniface316, Aug 22 '17 at 21:14
@DavidArenburg, I didnt understand some of the code on the solution provided. I have asked the question on that link. — boniface316, Aug 22 '17 at 21:16
Well, you've asked a data.table question, you have an data.table answer there. If you don't understand the code, then read the data.table manual. I don't understand what kind of an answer you are looking for if you are unable to understand the code anyway. — David Arenburg, Aug 22 '17 at 21:58

akond · Answer 1 · 2017-08-22T16:07:20.220

0

data <- cbind(mtcars,as.Date(c("2007-06-22", "2004-02-13")))
merge(rollapply(Filter(is.numeric, data), 2, mean),
      Filter(Negate(is.numeric), data))

The first line creates data, so that there are not only numeric values in it. This is only to mimic your data, which is not available right now.

The second line filters only numeric columns and applies mean function to each of filtered columns.

edited Aug 22 '17 at 16:07

answered Aug 22 '17 at 10:47

akond

15,865
4
35
55

The solution works, but its messing with the time line. If I put the width as 20, instead of starting at 20 points later, its starting at the top. I can play around with it. – boniface316 Aug 23 '17 at 03:45

score 0 · Accepted Answer · answered Aug 25 '17 at 03:21

0

Suggestion from David Arenburg worked perfectly!

MaPrice <- function(x, N) {
    Mavg <- rollapply(x, N, mean)
    Mavg
}

SpreadMA <- out[, lapply(.SD, MaPrice, N = 20)]

answered Aug 25 '17 at 03:21

boniface316

489
3
17

in the specific case of rolling averages, you might want to look at data.table's new `frollmean`. `rollapply` is very slow when your data sets are large, which is a drawback to it. – FXQuantTrader Mar 24 '19 at 11:25

R and Data.table - applying rollapply over multiple columns

2 Answers2