Read xts from CSV file in R

Question

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)
})