Read xts from CSV file in R

Question

Hi,

I'm trying to read time series from CSV file and save them as xts to be able to process them with quantmod. The problem is that numeric values are not parsed.

CSV file:

name;amount;datetime
test1;3;2010-09-23 19:00:00.057
test2;9;2010-09-23 19:00:00.073

R code:

library(xts)
ColClasses = c("character", "numeric", "character")
Data <- read.zoo("c:\\dat\\test2.csv", index.column = 3, sep = ";", header = TRUE, FUN = as.POSIXct, colClasses = ColClasses)
as.xts(Data)

Result:

                    name    amount
2010-09-23 19:00:00 "test1" "3"   
2010-09-23 19:00:00 "test2" "9"   

See amount column contains character data but expected to be numeric. What's wrong with my code?

Answer 1

The internal data structure of both zoo and xts is matrix, so you cannot mix data types.

---

Just read in the data with read.table:

Data <- read.table("file.csv", sep=";", header=TRUE, colClasses=ColClasses)

I notice your data have subseconds, so you may be interested in xts::align.time. This code will take Data and create one object with a column for each "name" by seconds.

NewData <- do.call( merge, lapply( split(Data,Data$name), function(x) {
  align.time( xts(x[,"amount"],as.POSIXct(x[,"datetime"])), n=1 )
}) )

If you want to create objects test1 and test2 in your global environment, you can do something like:

lapply( split(Data,Data$name), function(x) {
  assign(x[,"name"], xts(x[,"amount"],as.POSIXct(x[,"datetime"])),envir=.GlobalEnv)
})

Answer 2

You cannot mix numeric and character data in a zoo or xts object; however, if the name column is not intended to be time series data but rather is intended to distinguish between multiple time series, one for test1, one for test2, etc. then you can split on column 1 using split=1 to cause such splitting as shown in the following code. Be sure to set the digits.secs or else you won't see the sub-seconds on output (although they will be there in any case):

options(digits.secs = 3)
z <- read.zoo("myfile.csv", sep = ";", split = 1, index = 3, header = TRUE, tz = "")
x <- as.xts(z)