4

I have a question which might be trivial for most of you guys. I tried a lot, didn't come to a solution, so I would be glad if somebody could give me a hint. The starting point is a weekly xts-time series.

Month    Week Value Goal
Dec 2011 W50  a     a  
Dec 2011 W51  b     mean(a,b)
Dec 2011 W52  c     mean(a,b,c)
Dec 2011 W53  d     mean(a,b,c,d)
Jan 2012 W01  e     e
Jan 2012 W02  f     mean(e,f)
Jan 2012 W03  g     mean(e,f,g)
Jan 2012 W04  h     mean(e,f,g,h)
Feb 2012 W05  i     i
Feb 2012 W06  j     mean(i,j)

Please excuse the Excel notation, but I think it makes it pretty clear what I want to do: I want to calculate a left sided moving average for the column "Value" but just for the respective month, as it is displayed in the column Goal. I experimented with apply.monthly() and period.apply(). But it didn't get me what I want. Can sombody of you give me a hint how to solve the problem? Just a hint which function I should use would be already enough!

Thank you very much!

Best regards,

Andreas

Seb
  • 5,417
  • 7
  • 31
  • 50
chameau13
  • 626
  • 7
  • 24

2 Answers2

2

I hope I got your question right. but is it that what you are looking for:

 require(plyr)
 require(PerformanceAnalytics)
 ddply(data, .(Week), summarize, Goal=apply.fromstart(Value,fun="mean"))

this should work - though a reproducible expample would have been nice.

here's what it does.

df <- data.frame(Week=rep(1:5, each=5), Value=c(1:25)*runif(25)) #sample data

require(plyr)
require(PerformanceAnalytics)

df$Goal <- ddply(df, .(Week), summarize, Goal=apply.fromstart(Value,FUN="mean"))[,2]

outcome:

    Week      Value       Goal
 1     1  0.7528037  0.7528037
 2     1  1.9622622  1.3575330
 3     1  0.3367802  1.0172820
 4     1  2.5177284  1.3923936

of course you may obtain further info via the help: ?ddply or ?apply.fromstart.

Community
  • 1
  • 1
Seb
  • 5,417
  • 7
  • 31
  • 50
  • thanks, I also tried solution and it also does it should. that of Joshua though seemed more transparent to me. however i think there was a small misunderstanding: ddply(df, .(Week), ...). I had to use an indexing vector with months created by the rownames. – chameau13 Feb 08 '12 at 12:37
2

apply.monthly will not work because it only assigns one value to the endpoint of the period, whereas you want to assign many values to each monthly period.

You can do this pretty easily by splitting your xts data by month, applying a cumulative mean function to each, and rbind'ing the list back together.

library(quantmod)
# Sample data
getSymbols("SPY")
spy <- to.weekly(SPY)
# Cumulative mean function
cummean <- function(x) cumsum(x)/seq_along(x)
# Expanding average calculation
spy$EA <- do.call(rbind, lapply(split(Cl(spy),'months'), cummean))
Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418