Converting Data Frames to XTS

[omario]

I am working with the R programming language.

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 (dygraphs for R).

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?

Thanks!

[FJCC]

I guess you have to change the first column to numeric dates. I made each one the first day of the month. I included str() calls in the code to show how the data change.

DF <- data.frame(col_A.dates=c("2010-01","2010-02","2010-03"),
                  count=1:3,stringsAsFactors = TRUE)
str(DF)
'data.frame':	3 obs. of  2 variables:
 $ col_A.dates: Factor w/ 3 levels "2010-01","2010-02",..: 1 2 3
 $ count      : int  1 2 3
DF$col_A.dates <- paste(DF$col_A.dates,"01",sep="-")
str(DF)
'data.frame':	3 obs. of  2 variables:
 $ col_A.dates: chr  "2010-01-01" "2010-02-01" "2010-03-01"
 $ count      : int  1 2 3
DF$col_A.dates <- as.Date(DF$col_A.dates)
str(DF)
'data.frame':	3 obs. of  2 variables:
 $ col_A.dates: Date, format: "2010-01-01" "2010-02-01" ...
 $ count      : int  1 2 3

[gsapijaszko]

@omario

Can someone please show me how to fix this problem?

As @FJCC said, you have to make sure, your dates are in Date format. Other solution with lubridate:

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

Regards,
Grzegorz