How to lag date-index in a time-series in R?

Question

REWRITTEN QUESTION HERE:

I've made some progress but am getting odd behaviour from R...

Here's the xts I'm starting with

<no title>  Value   Value2  Value3
2002-08-21  21      2       27
2003-09-10  22      42      87
2004-02-12  23      62      67
2005-04-13  24      13      73
2006-05-13  25      4       28
2007-08-14  20      68      25
2008-03-06  19      82      22

What I want to produce:

 <no title> Value   Value2  Value3  ThisDate    NextDate
    2002-08-21  21      2       27      2002-08-21  2003-09-10
    2003-09-10  22      42      87      2003-09-10  2004-02-12
    2004-02-12  23      62      67      2004-02-12  2005-04-13
    2005-04-13  24      13      73      2005-04-13  2006-05-13
    2006-05-13  25      4       28      2006-05-13  2007-08-14
    2007-08-14  20      68      25      2007-08-14  2008-03-06
    2008-03-06  19      82      22      2008-03-06  NA

I've written a function like this:

StackUpAdjacentDates <- function(sourceTimeSeries)
{
    returnValue <- sourceTimeSeries

    thisDate <- as.character(index(sourceTimeSeries))
    nextDate <- c(as.character(thisDate[2:length(thisDate)]),NA)

    thisDate <- as.Date(strptime(thisDate, "%Y-%m-%d"))
    nextDate <- as.Date(strptime(nextDate, "%Y-%m-%d"))

    # set up thisDate in a new column
    if ("thisDate" %in% colnames(returnValue) )
    {
        returnValue<-returnValue[,-which(colnames(returnValue)=="thisDate")]
    }
    returnValue <- cbind(returnValue, thisDate)
    colnames(returnValue)[ncol(returnValue)] <- "thisDate"
    returnValue$thisDate <- thisDate

    # add nextDate in a new column
    if ("nextDate" %in% colnames(returnValue) )
    {
        returnValue<-returnValue[,-which(colnames(returnValue)=="nextDate")]
    }
    returnValue <- cbind(returnValue,nextDate)
    colnames(returnValue)[ncol(returnValue)] <- "nextDate"
    #returnValue$nextDate <- nextDate

}

This successfully adds thisDate (running the code step-wise at the command-line). But the bit that adds nextDate seems to over-write it! I also seem to get an unexpected row of NAs. Still working on this...

<no title>  Value   Value2  Value3  nextDate
2002-08-21  21      78      76      12305
2003-09-10  22      70      23      12460
2004-02-12  23      84      22      12886
2005-04-13  24      97      28      13281
2006-05-13  25      26      97      13739
2007-08-14  20      59      22      13944
2008-03-06  19      64      98      NA
<NA>        NA      NA      NA      NA

I've put "no title" in the first column to indicate that it's the xts date-index rather than actually a part of the vector/matrix.

The bit about removing the extra row is because I've not yet solved the over-write problem and was experimenting. It doesn't need to be there in the final answer but is where I am up to at present.

And lastly, when I interrogate this result and try to convert nextDate to a date I get....

> as.Date(returnValue$nextDate)
Error in as.Date.default(returnValue$nextDate) : 
  do not know how to convert 'returnValue$nextDate' to class "Date"

So I'm in a bit of a muddle...

ORIGINAL QUESTION BELOW:

I have a time-series in R (which I am learning fast, but clearly not fast enough!) like this

             Value
2002-08-21    21
2003-09-10    22
2004-02-12    23
2005-04-13    24
2006-05-13    25
2007-08-14    20
2008-03-06    19

I want to create a derivative of it with the date-index in the NEXT row in a new column in each row:

              Value    NextDate
2002-08-21    21       2003-09-10
2003-09-10    22       2004-02-12
2004-02-12    23       2005-04-13
2005-04-13    24       2006-05-13
2006-05-13    25       2007-08-14
2007-08-14    20       2008-03-06
2008-03-06    19       [...]

It's pretty easy to do for Value (using Lag) but not for the date-index iteself.

I can probably work out how to do it using various lookups and the like, but it is messy. You have to match on some other field, or fiddle around with row-numbers which doesn't feel very "true to R".

Is there a nice, neat, elegant way to do it?

I'm pretty sure I'll go "D'OH!" as soon as someone gives the answer! But so far I haven't found an answer on this site for lagging the date-index.

The reason I want to do this is I then want to use each pair of dates in a row to interrogate another series. So there might be a better way to do this.

Answer 1

I believe what you are looking for is:

dayDifff <- function(X)
{
    as.numeric(as.Date(index(X))) - c(NA, as.numeric(as.Date(index(X[-nrow(X)]))))
}

Where X is an xts object. I've converted the native POSIXct times into dates, and added an NA to the head and taken off the final date with X[-nrow(X)].

If you have times in seconds etc, you'll need to keep the second precision of POSIXct, but you should be able to get from the date/integer case above to that with a moment's effort.

Answer 2

I think this is similar to what you actually want to do:

library(xts)
#create example xts
times <- seq(as.Date('2002-08-21'),as.Date('2002-09-06 '),by="day")
myts <- xts(x=1:length(times),order.by=times)

#second xts, with start and end times
times2 <- c("2002-08-21","2002-08-31","2002-09-06")    
myts2 <- myts[times2] 

#get start and end times
ix <- index(myts2)

#get positions in myts
ep <- which(index(myts) %in% ix)-1

#calculate means
period.apply(myts,ep,mean) 

Note: This includes the starting time and excludes the end time, when calculating the period mean.

Answer 3

I'm not sure xts is the best thing for what your trying to do, but for what its worth here is how to take your xts object, make a dataframe and create the extra time column you want and then convert it to a time format.

 data(sample_matrix)
 x <- as.xts(sample_matrix)
 head(x)
 df <-as.data.frame(x)
 head(df)
 newdates<-rownames(df)

 df$nextdates<-c(newdates[2:length(newdates)],"NA")
 df$nextdates<-as.POSIXct(strptime(df$nextdates, "%Y-%m-%d"))
 head(df)