Abhishek Sharma February 2016

"Index has bad entries at data rows" error when reading CSV

I have a .csv file containing stock prices in the below format

Date    Open    High    Low Close
3/7/2014 10:15  228.28  236.58  228.23  236.18
3/7/2014 11:15  236.23  241.27  236.09  241.14
3/7/2014 12:15  241.1   246.12  241.1   242.96
3/7/2014 13:15  242.84  243.92  242     242.32

When I am running the below script:

test <- as.xts(read.zoo(mytest.csv', header=T, fill=T, index.column = 1, row.names=NULL,stringsAsFactors = FALSE, sep="",format="%m/%d/%y %H:%M", tz=""))

The below error is shown

Error in read.zoo("mydata.csv", header = T, fill = T, index.column = 1, : index has bad entries at data rows:1 2 3 4 5 6 7 8

Answers


ekstroem February 2016

I'm not sure where you get the as.xts function from but I see two small fixes you need to get the data into R.

First you should add an extra variable name for the time since the read.zoo function uses the same delimiter as read.table so if you want it to work with a header then you need a name for each column.

Date     Time   Open    High    Low     Close
3/7/2014 10:15  228.28  236.58  228.23  236.18
3/7/2014 11:15  236.23  241.27  236.09  241.14
3/7/2014 12:15  241.1   246.12  241.1   242.96
3/7/2014 13:15  242.84  243.92  242     242.32

Second, you can read in the file above as follows

library(zoo)
indata <- read.zoo("mydata.csv", header=TRUE, index.column = 1:2, format="%m/%d/%Y %H:%M", tz="CET")

Note that two indices are given since the date/time is split over two columns. Also, I think a tz is needed for the date/time conversion to work. In any case the above gives

> indata
                      Open   High    Low  Close
2014-03-07 10:15:00 228.28 236.58 228.23 236.18
2014-03-07 11:15:00 236.23 241.27 236.09 241.14
2014-03-07 12:15:00 241.10 246.12 241.10 242.96
2014-03-07 13:15:00 242.84 243.92 242.00 242.32

which can be passed to other R functions.

Post Status

Asked in February 2016
Viewed 1,167 times
Voted 9
Answered 1 times

Search




Leave an answer