Read xts from CSV file in R
Solution 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)
})
Solution 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)
Stas
Developing ultra low latency trading system in modern C++ and more. Github: https://github.com/artemkin Twitter: https://twitter.com/sartemkin
Updated on June 18, 2022Comments
-
Stas almost 2 years
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?
-
Stas over 13 yearsWhich structure should I use to read from CSV and then be able to create zoo with only numeric data? Thanks
-
Stas over 13 yearsAnd could you recommend some good R book? It seems I need something to read for a start.
-
Joshua Ulrich over 13 years@user194635: See my edits for the answer to your second question. Regarding good R books: search SO for ([r] [books]) and you'll find many answers.