I am having a data table with a date, a unique company name and its stock return that looks a bit like this:
require(data.table) DATE <- c("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") COMP <- c(replicate(60, "AAPL"), replicate(60, "INTL"), replicate(60, "GOOGL"), replicate(60, "MSFT")) RET <- rnorm(240) test1 <- data.table(DATE, COMP, RET)
And now I ty to calculate the previous average 6 same-month calendar return for each data point. So e.g. for the AAPL stock return in January 1990, I want the mean from the returns of Jan89, Jan 88, ... and Jan84 in a new column next to the Jan90 Return. I tried to get accustomed to skipping rows, but now I'm a bit stuck. Here is the code that I used to play a bit with the mechanic:
test1$new1 <- test1$RET[seq.int(from = 1L, to = 20L, by = 6L)] test1$new2 <- test1$RET[seq.int(from = -20L, to = 0L, by = 6L)] n = 6 test1$new3 <- rowMeans(test1[seq(from = 1, to = nrow(test1), by = n),])
Does anyone have an idea, how to do this? PS: I am not committed to data-table, I just enjoy this package a lot so far.