-1

My data frame is called 'mydata' and it has only 2 columns and looks as follows:

Date          Sales
2016-07-01    51256
2016-08-01    75892
2016-09-01    67115
...

Running the following code str(mydata) gives me the following:

'data.frame':   28 obs. of  2 variables:
 $ DATE  : chr  "2016-07-01" "2016-08-01" "2016-09-01" ...
 $ PKGREV: num   51256 75892 67115 ...

I have the following libraries loaded in R:

library(ggplot2)
library(dplyr)
library(scales)
library(ggthemes)
library(magrittr)
library(h2o)
library(timetk)
library(tidyquant)

I understand that I need to convert the 'Date' column into dates first before doing the xts conversion. From the 2 Stackoverflow questions below (see links below), I have made these 2 attempts:

Attempt 1:

 mydata2 <- xts(mydata[,-1], order.by=mydata[,1])    


Error message: 

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

Attempt 2:

mydata2 <- xts(mydata[, -1], order.by=as.POSIXct(mydata$Date))

Error message: 

 Error in as.POSIXct.default(mydata$Date) : 
 do not know how to convert 'mydata$Date' to class “POSIXct”

StackOverflow questions consulted:

Converting a data frame to xts

Converting data.frame to xts order.by requires an appropriate time-based object

What am I missing here?

Jaap
  • 81,064
  • 34
  • 182
  • 193
user3115933
  • 4,303
  • 15
  • 54
  • 94

2 Answers2

1

You can use the ymd function from the lubridate package to convert strings to dates. Then you can use tk_xts from timetk

library(dplyr)
library(timetk)
library(lubridate)

mydata %>% 
  mutate(Date = ymd(Date)) %>% 
  tk_xts(select = Sales)
amarchin
  • 2,044
  • 1
  • 16
  • 32
  • I get the following message when running the code: Using column `DATE` for date_var. Warning message: In tk_xts_.data.frame(data = data, select = select, date_var = date_var, : Non-numeric columns being dropped: DATE – user3115933 Oct 31 '17 at 09:59
  • The warning it's not an issue. I edited the answer selecting the column so you will not see the warning anymore. – amarchin Oct 31 '17 at 10:04
  • Thanks. However my issue now is that the first column after converting to xts does not have a header. I still need the column to have the header 'Date'. Message when I now run the code: Using column `DATE` for date_var. – user3115933 Oct 31 '17 at 10:07
  • As far as I know you cannot have a name for the index of an `xts` object. why you need it? Maybe I can try to come up with a different solution. – amarchin Oct 31 '17 at 10:15
  • I need it to be able to perform a visualization on the data before proceeding with my analysis. – user3115933 Oct 31 '17 at 10:19
  • Do you need to plot the time series for different time windows? – amarchin Oct 31 '17 at 10:21
  • I need to plot the time series for the whole period and see if there are any seasonality or pattern present in the data. – user3115933 Oct 31 '17 at 10:27
  • To be clearer, I am following this example to perform some machine learning techniques on my data: https://www.r-bloggers.com/demo-week-time-series-machine-learning-with-h2o-and-timetk/ – user3115933 Oct 31 '17 at 10:29
0

A few comments on the question:

  • It seems you did not convert the time index to "Date" class -- you could alternately use "yearmon" class if the data is monthly.

  • regarding attempt 2, you should NOT use "POSIXt" for date data. That will expose you to the possibility of timezone coding errors unnecessarily. Such errors can be subtle and therefore hard to detect. If you do use "POSIXt" anyways then use "POSIXct" class and not "POSIXlt" class.

  • based on your comment there are aspects of the data that you did not show in the question. Below we have provided a self-contained reproducible example and two alternative sets of code which both work. You will need to determine how your setup differs from that.

To keep the solutions below self-contained we use Lines as defined in the Note at the end but you could replace text=Lines with your filename, e.g. "mydata.dat".

The first alternative below uses read.zoo to read the data into a "zoo" class object which is then converted to an "xts" class object. read.zoo will automatically convert the first column to "Date" class (unless you use arguments specifying otherwise). The zoo package is automatically loaded by the xts package.

The second alternative below reads the data into a data frame DF and then uses xts() converting its second argument to "Date" class.

library(xts)

# 1
z <- read.zoo(text = Lines, header = TRUE)
as.xts(z)

# 2
DF <- read.table(text = Lines, header = TRUE)
with(DF, xts(Sales, as.Date(Date)))

yearmon

If you wanted to use "yearmon" class rather than "Date" class then in the first alternative use

x <- read.zoo(text = Lines, header = TRUE, FUN = as.yearmon)

and in the second:

with(DF, xts(Sales, as.yearmon(Date)))

Note: The input, Lines, in reproducible form is:

Lines <- "
Date          Sales
2016-07-01    51256
2016-08-01    75892
2016-09-01    67115"
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341