Converting Data Frames to XTS: order.by requires an appropriate time-based object

Question

I have the following data set called "my_data" (a data frame) - the dates are "factor types" and represent "year-month" (note: this data frame was created from an original data frame using the "dplyr group_by/summarise" commands - and then the "pivot_longer" command in "tidyverse" to make the data in "long format") :

head(my_data)

  col_A.dates col_A.count col_B.count col_C.count col_D.count col_E.count
1     2010-01         189         130          57          58          53
2     2010-02          63          62          25          18          30
3     2010-03          46          24          12          12          11
4     2010-04          45          17           8          16          15
5     2010-05          42          26          13          12          16

I am trying to make a time series plot of this data using the "dygraph" library (https://rstudio.github.io/dygraphs/).

To do this, it seems like you have to first convert your data frame into an "xts" type:

library(xts)

xts_data <- xts(my_data[,-1], order.by=my_data[,1])

But this returns the following error:

Error in xts(my_data[, -1], order.by = my_data[, 1]) : 
  order.by requires an appropriate time-based object

This is preventing me from creating the final graph:

library(dygraphs)
dygraph(xts_data) %>% dyRangeSelector()

Can someone please show me how to fix this problem?

References:

• Converting a data frame to xts

Answer 1

Maybe it is easy to use dummy date and convert date class. Below is an example;

library(tidyverse); library(xts)

dummy_date <- "-15"

my_data2 <- my_data %>% 
  mutate(col_A.dates = as.Date(paste0(as.character(col_A.dates), dummy_date)))

xts_data <- xts(my_data2[,-1], order.by=my_data2[,1])  # if my_data2 is tibble, order_by = my_data2[[1]]
dygraph(xts_data) %>% dyRangeSelector()

comment response

yes, if I were you, I'll convert "05-OCT-21" to date class directly and use it.

## example
# depending on you locale, it is needed to change locale. (if do so, please delete #)

# lct <- Sys.getlocale("LC_TIME")  # keep origin locale
# Sys.setlocale("LC_TIME", "C")   # change locale

as.Date(as.character("05-OCT-21"), format = "%d-%b-%y")

# Sys.setlocale("LC_TIME", lct)  # return origin locale

### expected_code
my_data2 <- my_data %>% 
  mutate(col_A.dates = as.Date(as.character(origiinal_date), format = "%d-%b-%y"))

Answer 2

?dygraph indicates that any class that is convertible to xts can be used so assuming we have the data frame shown reproducibly in the Note at the end use read.zoo to convert it to a zoo object with yearmon class index and then call dygraph.

library(zoo)
z <- read.zoo(my_data, FUN = as.yearmon)

library(dygraphs)
dygraph(z)

or to use ggplot2 (see ?autoplot.zoo for more info):

library(ggplot2)
autoplot(z, facets = NULL)

We don't really need anything more than the above but just in case if you want a Date class index, an xts object or a ts object then once we have z it is easy to convert it to many other forms.

zd <- aggregate(z, as.Date)

library(xts)
x <- as.xts(z)

as.ts(z)

Note

Lines <- "  col_A.dates col_A.count col_B.count col_C.count col_D.count col_E.count
1     2010-01         189         130          57          58          53
2     2010-02          63          62          25          18          30
3     2010-03          46          24          12          12          11
4     2010-04          45          17           8          16          15
5     2010-05          42          26          13          12          16"
my_data <- read.table(text = Lines, check.names = FALSE)
my_data[[1]] <- factor(my_data[[1]])