CODEWITHSUNDEEP
rxtstibble
Cettt
Cettt

Reputation: 11981

What is the best way to transform an xts into a tibble

As of tibble package version 2.0.1 what is the best way to convert an xts object into a tibble? Consider the following example:

library(tibble)
library(xts)

myxts <- xts(matrix(1:4, 2, 2), 
  order.by = seq.Date(from = as.Date("2019-02-26"), 
  length.out = 2, 
  by = "d"))

as_tibble(myxts)

This gives a warning:

Warning message: Calling as_tibble() on a vector is discouraged, because the behavior is likely to change in the future. Use enframe(name = NULL) instead.

However using enframe results in an error:

enframe(myxts, name = NULL)

Error: x must not have more than one dimension. length(dim(x)) must be zero or one, not 2.

I am aware of the timetk package which has a function to convert xts objects into tibbles. However, this package is orphaned so I would rather avoid it.

Thank you for you feedback.

EDIT: I would be interested in a tidyverse solution to this problem: of course it is possible to first transform the xts object into an arbitrary object (e.g. a dataframe) and then into a tibble. But shouldn't there be a direct way as well?

EDIT2: As of tibble package version 3.0.3 the warning does not appear anymore. In particular one can use the line in Yakov-vc's answer to do a 'tidy' transformation.

Upvotes: 5

Views: 4514

Answers (4)

Yakov-vc
Yakov-vc

Reputation: 78

How about as_tibble(xts) %>% add_column(day = index(xts), .before = 1)

Upvotes: 5

G. Grothendieck
G. Grothendieck

Reputation: 269654

Use fortify.zoo to convert it to a data frame and then as.tibble to convert it to a tibble.

myxts %>% fortify.zoo %>% as.tibble

giving:

# A tibble: 2 x 3
  Index          .   ..1
  <date>     <int> <int>
1 2019-02-26     1     3
2 2019-02-27     2     4

Update

Due to change in the tibble package the above no longer works if myxts has no names (or has names that tibble does not like) so use this instead:

myxts %>% fortify.zoo %>% as_tibble(.name_repair = "minimal")

Two other answers suggested using broom::tidy and although that will work for some xts objects it generates error messages when used with the myxts object in the question. Also one answer said that fortify could be dropped from ggplot2 causing problems but if we call fortify.zoo directly, as we do in the line of code above, this code would continue to work.

March 30, 2021. Just revisited this with tibble 3.1.0 and now the following does run without generating errors or warnings; however, the index is missing so the above using fortify.zoo remains the best way to convert.

as_tibble(myxts)  # index missing

Upvotes: 9

Anirudh
Anirudh

Reputation: 121

I would choose to proceed using the broom::tidy function augmenting it along the way with a nest(index, value) step to get a better view of the underlying data. Note that symbol is an example of an R xts object.

library(quantmod)
library(broom)
library(tidyverse)

symbol <- getSymbols("AAPL", 
                     src = "yahoo", 
                     from = as.Date("2014-01-01"),
                     to = as.Date("2014-12-31"), 
                     auto.assign=FALSE)

symbol_df <- broom::tidy(symbol) %>% nest(index, value)

symbol_df would now provide each category for which time series data are available in separate tibbles:

# A tibble: 6 x 2
  series        data              
  <chr>         <list>            
1 AAPL.Open     <tibble [251 x 2]>
2 AAPL.High     <tibble [251 x 2]>
3 AAPL.Low      <tibble [251 x 2]>
4 AAPL.Close    <tibble [251 x 2]>
5 AAPL.Volume   <tibble [251 x 2]>
6 AAPL.Adjusted <tibble [251 x 2]>

At this point, if you were to look up time series data corresponding to AAPL.Volume the next steps would be as follows:

symbol_df %>% 
  filter(series == "AAPL.Volume") %>% 
  pull(data) %>% as.data.frame() %>% as_tibble()

giving

# A tibble: 251 x 2
   index          value
   <date>         <dbl>
 1 2014-01-02 234684800
 2 2014-01-03 392467600
 3 2014-01-06 412610800
 4 2014-01-07 317209200
 5 2014-01-08 258529600
 6 2014-01-09 279148800
 7 2014-01-10 304976000
 8 2014-01-13 378492800
 9 2014-01-14 332561600
10 2014-01-15 391638800
# ... with 241 more rows

Upvotes: 1

Len Greski
Len Greski

Reputation: 10855

One can now use the broom::tidy() function to convert an xts object to a tibble, and this function is preferred over ggplot2::fortify() because it may be deprecated in the future, per the R help for ggplot2::fortify().

library(quantmod)
library(broom)

symbol <- getSymbols("AAPL", src = "yahoo", from = as.Date("2014-01-01"),
                     to = as.Date("2014-12-31"),auto.assign=FALSE)
symbol_df <- tidy(symbol)
str(symbol_df)

...and the output:

> str(symbol_df)
tibble [1,506 × 3] (S3: tbl_df/tbl/data.frame)
 $ index : Date[1:1506], format: "2014-01-02" "2014-01-02" ...
 $ series: chr [1:1506] "AAPL.Open" "AAPL.High" "AAPL.Low" "AAPL.Close" ...
 $ value : num [1:1506] 1.98e+01 1.99e+01 1.97e+01 1.98e+01 2.35e+08 ...

Upvotes: 3

Related Questions