4

apply.quarterly() only gives the end month of each quarter as the index, see the following example:

library(quantmod)
getSymbols.FRED("CPILFESL")
apply.quarterly(CPILFESL,mean)

        CPILFESL
1957-03-01  28.60000
1957-06-01  28.83333
1957-09-01  29.03333
1957-12-01  29.26667
1958-03-01  29.40000
1958-06-01  29.53333
...

What I really want is:

        CPILFESL
1957-01-01  28.60000
1957-04-01  28.83333
1957-07-01  29.03333
1957-10-01  29.26667
1958-01-01  29.40000
1958-04-01  29.53333
...

So how can I set the first month of each quarter as the index after applying apply.quarterly()?

Thanks

L.J
  • 1,066
  • 2
  • 13
  • 28
  • Why do you think you need to do this? There's probably an easier way to accomplish whatever you're trying to do. – Joshua Ulrich Oct 14 '13 at 02:58
  • I have several time series to deal with, some of which are quarterly and some are not. So, first I have to do the aggregation for the monthly data as I was trying to do in the example. But meanwhile I also have to set the same start and end dates to extract part of the data for all the time series. Different indexing methods causes some problem (different numbers of observations extracted for data of different frequencies.) Your comment did make me to think more about the problem. I just tune the date a bit to get it done (specifically: end.date<-2012-3-31 instead of end.date<-2012-01-01).THX! – L.J Oct 14 '13 at 04:00
  • Even though I have got my work done by tuning the end date as I mentioned above, I still think it might be helpful if one can have some more general methods to do this (if any) for some more general purposes, considering that there are still some inconsistency in the indexing of the time series. – L.J Oct 14 '13 at 04:06

3 Answers3

4

If x is the result of apply.quarterly then:

time(x) <- as.Date(as.yearqtr(time(x)))

as.yearqtr comes from the zoo package which the quantmod package depends on so it should be loaded already.

G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • +1, neat. Can you get to mid month of the quarter using a similar trick? Is there something similar for changing dates to the first or last day of the month? – ricardo Oct 14 '13 at 20:58
  • 2
    @ricardo, The above is the same as `time(x) <- as.Date(as.yearqtr(time(x)), frac = 0)`. Change the 0 to 0.5 for mid quarter or 1 for end of quarter. – G. Grothendieck Oct 14 '13 at 23:06
1

I have had a bunch of these problems -- typically they have arisen when i'm merging data from a number of sources, and the easiest thing is to change the output in an R aggregate step.

To do this i have a bunch of helper functions. Here's a simple function that changes the months for you. 

library(quantmod)
getSymbols("CPILFESL", src = 'FRED')
qd <- apply.quarterly(CPILFESL,mean)

monthSwitch <- function(index, monChng) {
    index_LT <- as.POSIXlt(index)
    index_LT$mon <- index_LT$mon + monChng
    as.Date(index_LT)
}

index(qd) <- monthSwitch(index(qd), -2)

The basic intuition is this: change to POSIXlt, edit the month value directly, and then change back to a simple integer date using as.Date.

ricardo
  • 8,195
  • 7
  • 47
  • 69
0

One can also edit the xts index vector itself!

library(xts)
library(lubridate)
test_daily <- xts(1:365, Sys.Date()+1:365)
test_monthly <- apply.monthly(test_daily,mean)
index(test_monthly) <- floor_date(index(test_monthly),"month")
test_monthly

I'm using lubridate to convert the dates but I'm sure there are plenty of other ways to edit the index vector, as well.

Crucially for me, this works even when the xts object is a dataframe.

My reason for doing this is simply to get the axis labels on a plot to land where they intuitively belong.

Carolina Mattsson
  • 31
  • 2