0

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?

Reason, why it is different from other questions: The key point here is to only take the previous values while accounting for only a certain company. Moreover, it should just be added as a new column.

PS: I am not committed to data-table, I just enjoy this package a lot so far.

Ddaengyu
  • 33
  • 7
  • See function `rollmean` in package `zoo`. – Rui Barradas Jun 13 '18 at 14:26
  • 2
    Possible duplicate of [Calculate the mean of every 13 rows in data frame](https://stackoverflow.com/questions/30359427/calculate-the-mean-of-every-13-rows-in-data-frame) – Rui Barradas Jun 13 '18 at 14:29
  • Hey, could you help me out a little bit on how to adjust the code? I saw the solution earlier, but 1. I am not sure, how to do code that it should take only previous values and 2. how to seperate each company? Thank you! – Ddaengyu Jun 13 '18 at 16:07

1 Answers1

0

It is a matter to adapt the accepted answer in the duplicate question to make of it a function.
Then, first split the input dataset by "COMP", compute the means and combine everything back to one dataset using function bind_rows from package dplyr.

library(dplyr)

fun <- function(DF, col = "RET", n = 6){
  aggregate(DF[[col]], list(rep(1:(nrow(test1)%/%n + 1), each = n, len = nrow(DF))), mean)
}

sp <- split(test1, test1$COMP)
res <- lapply(sp, fun)
res <- bind_rows(res, .id = "id")
Rui Barradas
  • 70,273
  • 8
  • 34
  • 66