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!

You need to convert your time/date variable to be the rownames of the dataframe, then as.xts will work. The tibble:column_to_rownames("timevar") function will do it for you. — Simon Woodward, Apr 14 '20 at 00:46

score 83 · Answer 1 · answered Nov 28 '10 at 14:52

83

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.

answered Nov 28 '10 at 14:52

Dirk Eddelbuettel

360,940
56
644
725

3

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! – user442446 Nov 28 '10 at 15:04
27

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. – frankc Sep 28 '11 at 19:53
13

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. – atomicules Dec 05 '11 at 12:47
3

I'm really not sure that "clearly documented" can be applied here. What is clear is that xts documentation is a maze. – Damien B Jul 17 '12 at 20:05
4

@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). – Joshua Ulrich Oct 10 '12 at 14:24
2

@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. – Damien B Oct 12 '12 at 00:08
3

@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. – Joshua Ulrich Oct 12 '12 at 00:15
@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) – Damien B Oct 12 '12 at 00:32
4

@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). – Joshua Ulrich Oct 12 '12 at 00:38
@JoshuaUlrich I'm not even able to find the FAQ on http://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 :) – Damien B Oct 12 '12 at 01:11
the error is about POSIXlt but the documentation says that xts uses POSIXct among other things – MySchizoBuddy Jul 06 '15 at 19:54

score 21 · Answer 2 · answered Nov 28 '10 at 14:52

21

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.

answered Nov 28 '10 at 14:52

Dr G

3,987
2
19
25

3

Probably should be: `rownames(q) = q[[1]]` – IRTFM Oct 03 '13 at 02:36
@42, what would be the difference? – Ahmadov Sep 03 '16 at 07:39
2

@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. – IRTFM Sep 03 '16 at 16:38
You can use tibble::column_to_rownames to set the rownames in a pipe. – Simon Woodward Apr 14 '20 at 00:48

Matt Dancho · Answer 3 · 2017-03-02T19:53:58.863

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.

This function is now deprecated. Use timetk::tk_xts instead https://www.rdocumentation.org/packages/tidyquant/versions/0.5.3/topics/as_xts — psychonomics, Mar 21 '18 at 16:28

score 3 · Answer 4 · edited May 04 '19 at 22:43

3

Here is a posible solution:

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

edited May 04 '19 at 22:43

Joey

1,436
2
19
33

answered May 04 '19 at 19:10

Gregory Lopez

31
1

1

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. – chb May 04 '19 at 19:32

score 0 · Answer 5 · edited Nov 21 '19 at 23:27

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.

score 0 · Answer 6 · answered Nov 23 '19 at 13:27

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)

score 0 · Answer 7 · answered May 05 '20 at 05:42

0

Try the following

q$t<-as.xts(q, order.by = as.Date(q$t), dateFormat="POSIXct")

answered May 05 '20 at 05:42

Cricketer

399
1
3
20

James Hirschorn · Answer 8 · 2022-11-17T19:22:00.187

0

For `tibble` or `data.frame`:

As in @psychonomics comment, one can use tk_xts:

library(timetk)

qxts <- tk_xts(q)

For `data.table`:

as.xts(dt) is much faster than tk_xts(dt) for a data.table dt.

So a simple solution here is to first convert the data.frame to a data.table:

library(data.table)

qxts <- as.xts(as.data.table(q))

edited Nov 17 '22 at 19:22

answered May 18 '20 at 23:01

James Hirschorn

7,032
5
45
53

score 0 · Answer 9 · answered Sep 12 '20 at 09:52

0

You can simply do the following

qxts <- xts(q[,2],q$t)

Worked for me.

answered Sep 12 '20 at 09:52

BatmanFan

105
5

score 0 · Answer 10 · answered Apr 05 '22 at 08:50

I ran into this as well, but my data date format was slightly different: yyyy-mm-dd as opposed to the OP, which is typical for financial data you download into R.
specifically, as an example: "2022-02-28".
As a result all the suggested solutions do not work.
What works is:

as.xts(q, order.by=as.Date(rownames(q), format = "%Y%m%d"))

assuming your data is in a typical dataframe with dates as rownames (if not, just replace q appropriately with data and date column)

score 0 · Answer 11 · answered Jun 19 '23 at 12:09

0

Use read.zoo followed by as.xts. This:

does not need any additional packages other than those already being used
employs the whole object approach avoiding dealing with the internals of q

Code--

library(xts) # also pulls in zoo
as.xts(read.zoo(q))

answered Jun 19 '23 at 12:09

G. Grothendieck

254,981
17
203
341

Converting a data frame to xts

11 Answers11

For `tibble` or `data.frame`:

For `data.table`:

Linked

Related

Converting a data frame to xts

11 Answers11

For tibble or data.frame:

For data.table:

Linked

Related

For `tibble` or `data.frame`:

For `data.table`: