0

I have panel data and want to do a simple arithmetic calculation over specific rolling time frames (for example -12months to current) for every observation at every month in time:

. "MRAR(1y)= ((sum(1+ER(-12,0))^-2)/12)-1". The ER data looks like this (expected output is on the very right. Please note that other observations include NAs). 

ER.01.2014  ER.02.2014  ER.03.2014  ER.04.2014  ER.05.2014  ER.06.2014  ER.07.2014  ER.08.2014  ER.09.2014  ER.10.2014  ER.11.2014  ER.12.2014  MRAR(1Y).01.2015
-22.42      -12.67      18.61       22.31       -16.81      -45.44      138.84      50.71       -29.77      1.51        -15.35      -12.65      4.405833333

My approach would be the following: 

ER <- as.data.frame(cbind(ER.08.2007, ER.09.2007, etc. 

cbind(time_series,seq(1:length(time_series)))

time_series <- c("08.2007","09.2007",etc.)

vars <- as.data.frame(cbind(names(ER),substr(names(ER),1,nchar(names(ER))-7),
                  substr(names(ER),nchar(names(ER))-6,nchar(names(ER))))) 

names(vars) <- c("ERdate","ER","date")

ERvars <- as.character(vars[vars$ER=="ER",][,"date"])

for (i in seq(1,length(time_series)))
    {
      aggregate(ER[i-36,0],df,sum)
}

..where the last line is just the inner part of the above formula.

I hope this goes in the right direction, the loop is where I run into problems.

Error in aggregate.data.frame(x, by, function(x) 0L) : 
  'by' must be a list
Wilhelm Fantastisch
  • 7
  • 3
  • 1
    Welcome to Stackoverflow, could you please use `dput(head(vars,10))` to show some sample input data and expected output. See here [how to post a reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) for more suggestions. – Silence Dogood Aug 14 '17 at 08:23
  • Hello, thanks, I have provided this above now. The output from dput is very lengthy even with droplevels(), but I hope this suffices. – Wilhelm Fantastisch Aug 14 '17 at 08:46
  • This does not yet help since we cannot see input data and expected output. Would suggest to include say maybe 12m data `(dateIndex, col1,col2,col3)` and expected output on 13th month for this subset period. – Silence Dogood Aug 14 '17 at 08:56
  • Thanks for help hNu, hope the above works for you? – Wilhelm Fantastisch Aug 14 '17 at 10:09
  • See the `rollapply` function in the `zoo` package. – ulfelder Aug 14 '17 at 10:17
  • I think there are a couple of mistakes in your `for` loop, it seems to start at row 1 - 36 = –35 and iterates over the rows in the zeroth column 0 of ER. What is the object `df`? – Niek Aug 14 '17 at 11:02
  • Thanks Niek, I am making first steps on looping here, most likely more than one mistake indeed. Not even sure the aggregation syntax is appropriate for this? I want to iterate through columns actually, as the time-series runs through columns (see data example). Do you have a solution? Thanks, Wilhelm Fantastisch. – Wilhelm Fantastisch Aug 14 '17 at 12:04

0 Answers0