Converting a data frame to xts

Question

I'm trying to convert a data frame to xts object using the as.xts()-method. Here is my input dataframe q:

q
                      t x  
1  2006-01-01 00:00:00  1  
2  2006-01-01 01:00:00  2  
3  2006-01-01 02:00:00  3

str(q)
    'data.frame':   10 obs. of  2 variables:
 $ t: POSIXct, format: "2006-01-01 00:00:00" "2006-01-01 01:00:00" "2006-01-01 02:00:00" "2006-01-01 03:00:00" ...  
 $ x: int  1 2 3 4 5 6 7 8 9 10

The result is:

> as.xts(q)
Error in as.POSIXlt.character(x, tz, ...) : 
  character string is not in a standard unambiguous format

This is the simplest example I can think of, so it's quite frustrating not getting it to work... Any help is appreciated!

Answer 1

Here's a solution using the tidyquant package, which contains a function as_xts() that coerces a data frame to an xts object. It also contains as_tibble() to coerce xts objects to tibbles ("tidy" data frames).

Recreate the data frame (note that the date-time class is used in "tidy" data frames, but any unambiguous date or date time class can be used):

> q
# A tibble: 3 × 2
                    t     x
               <dttm> <dbl>
1 2006-01-01 00:00:00     1
2 2006-01-01 01:00:00     2
3 2006-01-01 02:00:00     3

Use as_xts() to convert to "xts" class. Specify the argument, date_col = t, to designate the "t" column as the dates to use as row names:

> library(tidyquant)
> as_xts(q, date_col = t)
                    x
2006-01-01 00:00:00 1
2006-01-01 01:00:00 2
2006-01-01 02:00:00 3

The return is an xts object with the proper date or date-times as row names.

Answer 2

The reason, why it did not work now seems clear, xts does not accept tibbles and even if columns are selected they are still stored as Tibbles. Either the core data may be transformed to matrix ore a vector.The following code works: xls.tbl <- xls(tbl$x, order.by = tbl$t)

Answer 3

This is clearly documented --- xts and zoo objects are formed by supplying two arguments, a vector or matrix carrying data and Date, POSIXct, chron, ... type supplying the time information (or in the case of zoo the ordering).

So do something like

 qxts <- xts(q[,-1], order.by=q[,1])

and you should be set.

Answer 4

I defined an index with the length equal to the number of rows of my tibble. Only after defining the time sequence separately as shown with the example:

ti= seq(from = ymd_hm("2000-01-01 00:00"),
to = ymd_hm("2000-01-02 01:00"), by =  "30 min", tz = "UTC")

tbl <- tibble(t =ti,
    x = 1:length(t))
)

This code worked:

xts.tbl <- xts(tbl[,-1], order.by = ti)

However all data transformed into characters.

Answer 5

Here is a posible solution:

library(timetk)
q <- xts::xts(q[,-1], order.by = q$t)

Answer 6

Well, as.xts assumes by default that the dates are stored in the rownames of the data.frame. Hence the error message. A quick and dirty fix is:

rownames(q) = q[1]
as.xts(q)

But you get an extra column with the dates string. Ideally you would construct the data.frame with the dates as rownames to start with.