[R-SIG-Finance] non-standard time-format conversion from data.frame to xts

[Gabor Grothendieck]

On Wed, Dec 8, 2010 at 9:02 AM, Andres Susrud <andres.susrud at gmail.com> wrote:
> Hi,
>
> I have a conversion problem from data.frame to xts.
>
> my dataset looks like this
>> data[1:5,]
>        Time   Bid  Offer
> 1 7:10:03 AM 6118.5 6119.5
> 2 7:10:36 AM 6118.5 6119.5
> 3 7:11:07 AM 6119.5 6119.5
> 4 7:11:48 AM 6119.0 6120.0
> 5 7:12:25 AM 6119.0 6119.5
>
> Because of the lack of date, and the time format of H,M,S and AM/PM, I can't
> convert via as.xts or as.zoo etc.
>

Assuming your data frame is this we can use read.zoo to read it into
zoo giving it a chron "times" class:

DF <- structure(list(Time = structure(1:5, .Label = c("7:10:03 AM",
    "7:10:36 AM", "7:11:07 AM", "7:11:48 AM", "7:12:25 AM"), class = "factor"),
    Bid = c(6118.5, 6118.5, 6119.5, 6119, 6119), Offer = c(6119.5,
    6119.5, 6119.5, 6120, 6119.5)), .Names = c("Time", "Bid",
    "Offer"), class = "data.frame", row.names = c(NA, -5L))

library(zoo)
library(chron)
z <- read.zoo(DF, FUN = function(x) times(as.chron(paste("1970-01-01", x),
   format = "%Y-%m-%d %H:%M:%S %p")))
z

If you need it in zoo then you now have it with "times" class which
seems the most appropriate for that data.

xts does not support chron "times" class but if you need it in xts you
could convert it to some date/time class and then use as.xts. e.g.

as.xts(aggregate(z, chron))


-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com