0

I have a data.table xSet with multiple columns. I need a new table with a moving 4 row average for each column individually.

Example input output Table

Uwe
  • 41,420
  • 11
  • 90
  • 134
rickHammer
  • 1
  • 3
  • Please don't post images of data. See [this question](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) for better alternatives. – alistaire Jul 19 '18 at 01:43
  • Please, can you double check your definition of rolling window computation? It does not match neither the "center" nor the "right" window alignment options. – Uwe Jul 26 '18 at 05:24

2 Answers2

2

We could use rollapplyr from zoo

library(zoo)
library(dplyr)
df1 %>% 
  mutate_all(funs(New = rollapplyr(., FUN = mean, width = 4, partial = TRUE)))

Or similar option with data.table

library(data.table)
setDT(df1)[, paste0("New", names(df1)) := lapply(.SD, 
     function(x) rollapplyr(x, FUN = mean, width = 4, partial = TRUE))]

data

set.seed(24)
df1 <- as.data.frame(matrix(sample(0:9, 3 * 15, replace = TRUE),
       ncol = 3, dimnames = list(NULL, paste0("Col", 1:3))))
akrun
  • 874,273
  • 37
  • 540
  • 662
  • 2
    Note that the data.table solution could also be written: `DT[, cbind(.SD, New = rollapplyr(.SD, 4, mean, partial = TRUE))]` where `DT` is the input data.table. – G. Grothendieck Jul 20 '18 at 00:38
1

The answers by akrun and G. Grothendieck call the rollapplr() function which uses a right aligned window by default.

But this is in contrast to the definition the OP has shown in the image.

This can be visualised by creating some suitable input data and by using toString() instead of mean() as aggregation function:

library(data.table)
# create suitable input data
DT <- data.table(col1 = 1:15, col2 = 21:35, col3 = 41:55)

DT[, cbind(.SD, New = zoo::rollapplyr(.SD, 4, toString, partial = TRUE))]

    col1 col2 col3       New.col1       New.col2       New.col3
 1:    1   21   41              1             21             41
 2:    2   22   42           1, 2         21, 22         41, 42
 3:    3   23   43        1, 2, 3     21, 22, 23     41, 42, 43
 4:    4   24   44     1, 2, 3, 4 21, 22, 23, 24 41, 42, 43, 44
 5:    5   25   45     2, 3, 4, 5 22, 23, 24, 25 42, 43, 44, 45
 6:    6   26   46     3, 4, 5, 6 23, 24, 25, 26 43, 44, 45, 46
 7:    7   27   47     4, 5, 6, 7 24, 25, 26, 27 44, 45, 46, 47
 8:    8   28   48     5, 6, 7, 8 25, 26, 27, 28 45, 46, 47, 48
 9:    9   29   49     6, 7, 8, 9 26, 27, 28, 29 46, 47, 48, 49
10:   10   30   50    7, 8, 9, 10 27, 28, 29, 30 47, 48, 49, 50
11:   11   31   51   8, 9, 10, 11 28, 29, 30, 31 48, 49, 50, 51
12:   12   32   52  9, 10, 11, 12 29, 30, 31, 32 49, 50, 51, 52
13:   13   33   53 10, 11, 12, 13 30, 31, 32, 33 50, 51, 52, 53
14:   14   34   54 11, 12, 13, 14 31, 32, 33, 34 51, 52, 53, 54
15:   15   35   55 12, 13, 14, 15 32, 33, 34, 35 52, 53, 54, 55

col1 is equal to the row numbers, New.col1 shows the row indices which are being involved in computing rollapplyr().

Compared to OP's image, only rows 1 and 2 do match. Apparently, a right aligned window does not meet OP's definition.

We can compare OP's requirement with the other alignment options for rolling windows:

DT <- data.table(col1 = 1:15, col2 = 21:35, col3 = 41:55)
align_window <- c("center", "left", "right")
DT[, (align_window) := lapply(align_window, 
                              function(x) zoo::rollapply(
                                col1, 4, toString, partial = TRUE, align = x))]
# add OP's definition from image
DT[1:2, OP := right][3, OP := toString(2:4)][4:15, OP := center][]

    col1 col2 col3         center           left          right             OP
 1:    1   21   41        1, 2, 3     1, 2, 3, 4              1              1
 2:    2   22   42     1, 2, 3, 4     2, 3, 4, 5           1, 2           1, 2
 3:    3   23   43     2, 3, 4, 5     3, 4, 5, 6        1, 2, 3        2, 3, 4
 4:    4   24   44     3, 4, 5, 6     4, 5, 6, 7     1, 2, 3, 4     3, 4, 5, 6
 5:    5   25   45     4, 5, 6, 7     5, 6, 7, 8     2, 3, 4, 5     4, 5, 6, 7
 6:    6   26   46     5, 6, 7, 8     6, 7, 8, 9     3, 4, 5, 6     5, 6, 7, 8
 7:    7   27   47     6, 7, 8, 9    7, 8, 9, 10     4, 5, 6, 7     6, 7, 8, 9
 8:    8   28   48    7, 8, 9, 10   8, 9, 10, 11     5, 6, 7, 8    7, 8, 9, 10
 9:    9   29   49   8, 9, 10, 11  9, 10, 11, 12     6, 7, 8, 9   8, 9, 10, 11
10:   10   30   50  9, 10, 11, 12 10, 11, 12, 13    7, 8, 9, 10  9, 10, 11, 12
11:   11   31   51 10, 11, 12, 13 11, 12, 13, 14   8, 9, 10, 11 10, 11, 12, 13
12:   12   32   52 11, 12, 13, 14 12, 13, 14, 15  9, 10, 11, 12 11, 12, 13, 14
13:   13   33   53 12, 13, 14, 15     13, 14, 15 10, 11, 12, 13 12, 13, 14, 15
14:   14   34   54     13, 14, 15         14, 15 11, 12, 13, 14     13, 14, 15
15:   15   35   55         14, 15             15 12, 13, 14, 15         14, 15

None of the alignment options does completely meet OP's definition. "center" is the best match except for the first 3 rows.

Uwe
  • 41,420
  • 11
  • 90
  • 134
  • The pattern in the question seems strange but to replicate it anyways with a single rollapply define the offsets like this: `offsets <- lapply(pmax(2, 1:15), function(k) head(seq(-1, 2), k)); rollapply(1:15, offsets, toString, partial = TRUE)` – G. Grothendieck Jul 26 '18 at 15:21