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

Question

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.

Erma

Answer 1

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

Diego

Error in add_column(., Date = index(weekly.xts), .before = 1, ) : could not find function "add_column" – Headstock

@RaminMelikov you probably solved this now but try updating the tibble to version 3 or latest tidyverse package. Make sure the package is loaded. Personally library(tidyverse) is useful as it loads a group of packages at once. (add_column is in tibble 3.1.6) – Kuo

Answer 2

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

Tui

Hi thank you for your answer. I was wondering what the tidyverse approach to that problem would be. In earlier versions of this package as_tibbleworked perfectly. However now they issue a warning. – Erma

Answer 3

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 ...

Hols

Answer 4

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

Mercer