1

I have a CSV in the following format:

TICKER,PER,DATE,TIME,CLOSE
SYMBOL,1,20160104,1002,14180.0000000
SYMBOL,1,20160104,1003,14241.0000000

I would like to read it into a time series:

f <- function(a, b) {
    c <- paste(a, b)
    return(strptime(c, format = "%Y%m%d %H%M"))
}
d <- read.zoo("test.csv", FUN = f, index.column = list("DATE", "TIME"))

And what I get is index does not match data. Why?

Leonid
  • 1,127
  • 2
  • 15
  • 29
  • 1
    Possible duplicate of [Reading csv with date and time](http://stackoverflow.com/questions/9372211/reading-csv-with-date-and-time) – ArunK Jul 04 '16 at 14:28

2 Answers2

3

Character and numeric columns can't both be part of the time series data because the data portion of a zoo object is a matrix (and a matrix must be all numeric, all character or all other type); however, one can split to wide form on the character column using split= Also we can avoid having to specify function f by specifying format= and tz=. Further, we must specify that a header is present (header=) and that fields are separated with the "," character (sep=).

(Below we have used text = Lines for reproducibliity but in reality replace that with "test.csv".)

Lines <- "TICKER,PER,DATE,TIME,CLOSE
SYMBOL,1,20160104,1002,14180.0000000
SYMBOL,1,20160104,1003,14241.0000000"

library(zoo)

read.zoo(text = Lines, header = TRUE, sep = ",", index = c("DATE", "TIME"), 
  split = "TICKER", format = "%Y%m%d %H%M", tz = "")

giving:

                    PER CLOSE
2016-01-04 10:02:00   1 14180
2016-01-04 10:03:00   1 14241

Note: If you do want to use your function f anyways then omit format and tz and use:

read.zoo(text = Lines, header = TRUE, sep = ",", index = c("DATE", "TIME"), 
  split = "TICKER", FUN = f)

This would also work, i.e. read it into a data frame and then read the data frame into a zoo object:

DF <- read.csv(text = Lines) # read.csv defaults to header=TRUE, sep=","
read.zoo(DF, index = c("DATE", "TIME"), split = "TICKER", FUN = f)
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
1

You need to specify header = TRUE and sep = ",", since they are not the defaults for read.zoo like they are for read.csv.

d <- read.zoo(text="TICKER,PER,DATE,TIME,CLOSE
SYMBOL,1,20160104,1002,14180.0000000
SYMBOL,1,20160104,1003,14241.0000000",
  FUN = f, index.column = list("DATE", "TIME"),
  header=TRUE, sep=",")
d
#                     TICKER PER CLOSE
# 2016-01-04 10:02:00 SYMBOL 1   14180
# 2016-01-04 10:03:00 SYMBOL 1   14241
Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418