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!

Solution 1

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.

Solution 2

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.

Solution 3

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.

Solution 4

Here is a posible solution:

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

View more solutions

Comments

[user442446]

Thanks! You helped me a lot, although the answer was so simple! (It wasn't obvious to me, because I haven't used any of the time series packages before and somehow didn't get that from the documentation). But, thanks again!

[frankc]

I don't think it's all that obvious either considering that that the 'xts: Extensible Time Series' paper clearly claims that conversion from data.frame is possible.

[atomicules]

Add me as another who doesn't think it's that "clearly documented" actually. But thanks to this answer I managed to figure something else out unrelated to the original question.

[Damien B]

I'm really not sure that "clearly documented" can be applied here. What is clear is that xts documentation is a maze.

[Joshua Ulrich]

@DamienB (and other commenters and up-voters), we are more than happy to accept contributions / patches that make the documentation clearer and/or less of a maze. The great thing about OSS is that you can make a difference (but voicing your opinion, without action, doesn't count).

[Damien B]

@JoshuaUlrich please don't "OSS" us, the difference we make here is that we tell Dirk that no, "clearly documented" doesn't apply when you first use xts, and because it's the first use and the doc is not readable, we have no idea about how to make it more approachable. The one who can contribute on the documentation is the person who has understood AND is able to go back to his/her "learner state of mind". If we comment here about something "so basic", we have clearly not reached that level, and your lecturing will certainly not help it.

[Joshua Ulrich]

@DamienB: I never said it's clearly documented. I only said that the people who didn't understand and didn't find the documentation clear, but who now understand can help make the documentation clear. Otherwise, you're asking me to imagine a "learner state of mind" in order to write better documentation. Your comment that "the documentation is a maze" and your lecturing aren't helpful.

[Damien B]

@JoshuaUlrich personally (even if it's by chance only that I was the target of the @ :-)), I still haven't wrapped my head around xts, and I've used xts only because it was kind of unifying dozens of examples about time series and it was "understood" in ggplot2. Being able to read some data and finally output a plot doesn't make us confident about what we really understood and whether we made the right thing. (And reading the code I wrote 4 months ago, and not having touched R in-between, it really appears as black magic to me :-D)

[Joshua Ulrich]

@DamienB: I "@"ed you because you were most recent and you can only "@" one person in a comment (which sucks). I'm happy to work with contributors to help them really understand, but I cannot have their perspective on what needs clarification. Honest attempts, however imperfect, are greatly appreciated (e.g. see the xts FAQ on R-Forge, written by a SO question-asker).

[Damien B]

@JoshuaUlrich I'm not even able to find the FAQ on r-forge.r-project.org/projects/xts (first result with Google for "xts FAQ on R-Forge") :) So far, the only thing I can give is my case: I had to plot a time series with some flexibility, I had done that with gnuplot in the past, and read some blog posts about how cool R was, so I tried. I didn't try for maps, I didn't try for what R was about, I did try because I had a CSV with timestamps and needed a 5min aggregation (I ended up with aggregate(qxts, time(qxts)-as.numeric(time(qxts))%%300, mean)). Not even sure where xts fits there :)

[IRTFM]

Probably should be: rownames(q) = q[[1]]

[MySchizoBuddy]

the error is about POSIXlt but the documentation says that xts uses POSIXct among other things

[Ahmadov]

@42, what would be the difference?

[IRTFM]

@Ahmedov q[1] would be list containing a vector. q[[1]] would just be the vector. Might not make a difference if [<-.rownames accepts a list but even if it does, not all class-specific assignment functions do so.

[psychonomics]

This function is now deprecated. Use timetk::tk_xts instead rdocumentation.org/packages/tidyquant/versions/0.5.3/topics/‌​…

[chb]

Hi, welcome to Stack Overflow. When answering a question that already has a few answers, please be sure to add some additional insight into why the response you're providing is substantive and not simply echoing what's already been vetted by the original poster. This is especially important in "code-only" answers such as the one you've provided.

[Simon Woodward]

You can use tibble::column_to_rownames to set the rownames in a pipe.