CODEWITHSUNDEEP
r
user3022875
user3022875

Reputation: 9018

create data frame with multiple columns that are all rolling averages of different windows

I have a "mydata" data frame that is 2 columns: a date column and a value column. I would like to append columns to the data frame that are moving average of the "value" column at various windows.

Currently mydata has 2 columns

Column 1 - date
Column 2 - value

I'd like column 3 to be a 2 period moving average of the value column, column 4 to be a 3 period moving average of the value column,etc. until the last column is a moving average of 9 periods

so I'd like to add these columns:

Column 3 - SMA(value, 2)
Column 4 - SMA(value, 3)
Column 5 - SMA(value, 4)
Column 6 - SMA(value, 5)
Column 7 - SMA(value, 6)
Column 8 - SMA(value, 7)
Column 9 - SMA(value, 8)
Column 10 - SMA(value, 9)

here is the code

library(TTR)
date = seq(as.Date("2016-01-01"),as.Date("2016-01-10"),"day")
value =c(1,2,3,4,5,6,7,8,9,10)
mydata = data.frame (date, value)
z = zoo(value, date)
SMA(z, n=2)# this would be 3rd column of data frame
SMA(z, n=3)# this would be 4th column of data frame

etc.....

thank you.

Upvotes: 2

Views: 851

Answers (1)

Jaap
Jaap

Reputation: 83225

You could achieve this with the data.table package and the rollmeanr function (or the rollmean function) from the zoo package:

library(data.table)
library(zoo)
# method 1:
setDT(mydata)[, paste0('MA',2:9) := lapply(2:9, function(x) rollmeanr(value, x, fill = NA))][]
# method 2:
setDT(mydata)[, paste0('MA',2:9) := lapply(2:9, rollapplyr, data = value, mean, fill = NA)][]

which both give:

> mydata
          date value MA2 MA3 MA4 MA5 MA6 MA7 MA8 MA9
 1: 2016-01-01     1  NA  NA  NA  NA  NA  NA  NA  NA
 2: 2016-01-02     2 1.5  NA  NA  NA  NA  NA  NA  NA
 3: 2016-01-03     3 2.5   2  NA  NA  NA  NA  NA  NA
 4: 2016-01-04     4 3.5   3 2.5  NA  NA  NA  NA  NA
 5: 2016-01-05     5 4.5   4 3.5   3  NA  NA  NA  NA
 6: 2016-01-06     6 5.5   5 4.5   4 3.5  NA  NA  NA
 7: 2016-01-07     7 6.5   6 5.5   5 4.5   4  NA  NA
 8: 2016-01-08     8 7.5   7 6.5   6 5.5   5 4.5  NA
 9: 2016-01-09     9 8.5   8 7.5   7 6.5   6 5.5   5
10: 2016-01-10    10 9.5   9 8.5   8 7.5   7 6.5   6

In response to your comment, you can add the log values by chaining a similar operation:

setDT(mydata)[, paste0('MA',2:9) := lapply(2:9, function(x) rollmeanr(value, x, fill = NA))
              ][, paste0('ln',2:9) := lapply(.SD, log), .SDcols = 3:10]

which gives:

> mydata
          date value MA2 MA3 MA4 MA5 MA6 MA7 MA8 MA9       ln2       ln3       ln4      ln5      ln6      ln7      ln8      ln9
 1: 2016-01-01     1  NA  NA  NA  NA  NA  NA  NA  NA        NA        NA        NA       NA       NA       NA       NA       NA
 2: 2016-01-02     2 1.5  NA  NA  NA  NA  NA  NA  NA 0.4054651        NA        NA       NA       NA       NA       NA       NA
 3: 2016-01-03     3 2.5   2  NA  NA  NA  NA  NA  NA 0.9162907 0.6931472        NA       NA       NA       NA       NA       NA
 4: 2016-01-04     4 3.5   3 2.5  NA  NA  NA  NA  NA 1.2527630 1.0986123 0.9162907       NA       NA       NA       NA       NA
 5: 2016-01-05     5 4.5   4 3.5   3  NA  NA  NA  NA 1.5040774 1.3862944 1.2527630 1.098612       NA       NA       NA       NA
 6: 2016-01-06     6 5.5   5 4.5   4 3.5  NA  NA  NA 1.7047481 1.6094379 1.5040774 1.386294 1.252763       NA       NA       NA
 7: 2016-01-07     7 6.5   6 5.5   5 4.5   4  NA  NA 1.8718022 1.7917595 1.7047481 1.609438 1.504077 1.386294       NA       NA
 8: 2016-01-08     8 7.5   7 6.5   6 5.5   5 4.5  NA 2.0149030 1.9459101 1.8718022 1.791759 1.704748 1.609438 1.504077       NA
 9: 2016-01-09     9 8.5   8 7.5   7 6.5   6 5.5   5 2.1400662 2.0794415 2.0149030 1.945910 1.871802 1.791759 1.704748 1.609438
10: 2016-01-10    10 9.5   9 8.5   8 7.5   7 6.5   6 2.2512918 2.1972246 2.1400662 2.079442 2.014903 1.945910 1.871802 1.791759

Upvotes: 5

Related Questions