Can't convert data.frame object to xts object: Gives POSIXlt error. (R)

2
votes

I'm trying to convert a data.frame object originating from a .csv to an xts object. Instead of correctly converting to an xts object, it gives the error:

Error in as.POSIXlt.character(x, tz, ...) : character string is not in a standard unambiguous format

I've tries to change the Date column to the class Date, to no avail. Here is the code:

library(xts)
mydata <- read.csv("https://sites.google.com/site/jonspinney/home/mba-6693-2017/edhec.csv?attredirects=0&d=1",header=TRUE,sep=",")
mydata$Date <- as.POSIXlt(mydata$Date, format = "%m/%d/%Y")
mydata <- as.xts(mydata)
class(mydata$Date)
class(mydata)
2
rtime-seriesxts

2 Answers

4
votes

When you call the generic function as.xts on a data frame, it is the xts:::as.xts.data.frame that will be invoked. By default, it is expecting row.names of your data frame to be DateTime, while your mydata has its first column as DateTime, and row.names(mydata) gives 1, 2, 3, 4, ... which is certainly not a recognizable string format for as.POSIXlt or as.POSIXct to convert.

Since you have already arranged the Date column into DateTime format (the POSIXlt format), you can use xts directly:

oo <- xts(mydata[-1], mydata[[1]])

str(oo)
#An ‘xts’ object on 1993-12-31/2015-11-30 containing:
#  Data: num [1:264, 1:9] 100 100.4 100.5 99.5 97 ...
# - attr(*, "dimnames")=List of 2
#  ..$ : NULL
#  ..$ : chr [1:9] "CA" "SB" "EM" "EMN" ...
#  Indexed by objects of class: [POSIXlt,POSIXt] TZ: 
#  xts Attributes:  
# NULL

class(oo)
#[1] "xts" "zoo"
2
votes

You can make this a one-liner using read.zoo and as.xts:

myData <- as.xts(read.zoo("https://sites.google.com/site/jonspinney/home/mba-6693-2017/edhec.csv?attredirects=0&d=1",header=TRUE,sep=",", format = "%m/%d/%Y" ))

> str(myData)
An ‘xts’ object on 1993-12-31/2015-11-30 containing:
  Data: num [1:264, 1:9] 100 100.4 100.5 99.5 97 ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr [1:9] "CA" "SB" "EM" "EMN" ...
  Indexed by objects of class: [Date] TZ: UTC
  xts Attributes:  
 NULL