CODEWITHSUNDEEP
rdata.tablemoving-average
rickHammer
rickHammer

Reputation: 1

Moving average of 4 rows of data.table with multiple columns

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

Upvotes: 0

Views: 1356

Answers (2)

Uwe
Uwe

Reputation: 42592

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.

Upvotes: 1

akrun
akrun

Reputation: 887901

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))))

Upvotes: 2

Related Questions