How do I calculate a monthly rate of change from a daily time series in R?

r, time-series, xts, zoo

Solution

Here's one way to do it using `plyr` and `ddply`. I use ddply sequentially, first to get the first and last rows of each month, and again to calculate the monthlyReturn. (Perhaps using xts or zoo might be easier, I am not sure.)

#Using plyr and the data in df
df$Date <- as.POSIXlt(as.Date(df$Date, "%m/%d/%Y"))
df$Month <- (df$Date$mon + 1) #0 = January

sdf <- df[,-1] #drop the Date Column, ddply doesn't like it

library("plyr")
#this function is called with 2 row data frames
monthlyReturn<- function(df) {
  (df$Value[2] - df$Value[1])/(df$Value[1])  
}

adf <- ddply(sdf, .(Month), function(x) x[c(1, nrow(x)), ]) #get first and last values for each Month   
mon.returns <- ddply(adf, .(Month), monthlyReturn)

Here's the data I used to test it out:

> df
         Date Value
1    1/1/2000  10.0
2   1/31/2000  10.1
3    2/1/2000  10.2
4   2/28/2000  11.0
5    3/1/2000  10.0
6   3/31/2000  24.1
7   5/10/2000 510.0
8   5/22/2000 522.0
9   6/04/2000 604.0
10  7/03/2000  10.1
11  7/30/2000   7.2
12 12/28/2000  11.0
13 12/30/2000   3.0

> mon.returns
  Month          V1
1     1  0.01000000
2     2  0.07843137
3     3  1.41000000
4     5  0.02352941
5     6  0.00000000
6     7 -0.28712871
7    12 -0.72727273

Hope that helps.

Problem

I'm beginning to get my feet wet with R, and I'm brand new to time series concepts. Can anyone point me in the right direction to calculate a monthly % change, based on a daily data point? I want the change between the first and last data points of each month. For example: tseries data: ``` 1/1/2000 10.00 ... 1/31/2000 10.10 2/1/2000 10.20 ... 2/28/2000 11.00 ``` I'm looking for a return data frame of the form: ``` 1/31/2000 .01 2/28/2000 .0784 ``` Ideally, I'd be able to calculate from the endpoint of the prior month to the endpoint of current month, but I'm supposing partitioning by month is easier as a starting point. I'm looking at packages zoo and xts, but am still stuck. Any takers? Thanks...

Original source