CODEWITHSUNDEEP
rcsvdatexts
samael
samael

Reputation: 29

converting csv to xts in r

I'm trying to convert csv to xts but I don't know what I'm getting lol. When I run the first line, I am getting a data frame which I then convert to xts using the second line. But when I run the second line, the data in the date column gets duplicated in the same column! 

sti <- read.csv('C:/.../STI.csv', header = TRUE)
sti_xts <- as.xts(sti,order.by = as.Date(sti$DATE))

           DATE         OPEN  HIGH  LOW   CLOSE VOLUME          X12.prd.VMAVE  
2013-01-02 "2013-01-02" "1.0" "1.1" "1.0" "1.0" "16,194,000.00" "11,209,083.00"

2013-01-03 "2013-01-03" "1.1" "1.1" "1.0" "1.0" "12,803,000.00" "10,935,667.00"

2013-01-04 "2013-01-04" "1.0" "1.1" "1.0" "1.0" "15,133,000.00" "10,335,167.00"

2013-01-07 "2013-01-07" "1.0" "1.0" "1.0" "1.0" "13,289,000.00" "11,284,750.00"

2013-01-08 "2013-01-08" "1.0" "1.0" "1.0" "1.0" "57,941,000.00" "15,668,000.00"

2013-01-09 "2013-01-09" "1.0" "1.0" "1.0" "1.0" "81,389,000.00" "21,386,583.00"

I'm pretty new in using R and I've been tinkering with it after some tutorials but I can't seem to figure this one out.

Also, is there a way for me to format my csv files so I wouldn't encounter issues like this? Thanks!

Upvotes: 0

Views: 643

Answers (1)

shosaco
shosaco

Reputation: 6165

First, remove the thousands-seperator and convert to numeric:

sti$VOLUME <- as.numeric(gsub(",", "", sti$VOLUME))
sti$prd.VMAVE <- as.numeric(gsub(",", "", sti$prd.VMAVE))

Then, when converting to xts, the DATE column is not duplicated, but used as your time series index:

 > index(sti)
 [1] "2013-01-02" "2013-01-03" "2013-01-04" "2013-01-07" "2013-01-08" "2013-01-09"

What you want is probably to exclude the first column:

> as.xts(sti[,-1],order.by = as.Date(sti$DATE))
            OPEN HIGH  LOW CLOSE   VOLUME  prd.VMAVE      
2013-01-02  1.0  1.1     1     1 16194000  11209083
2013-01-03  1.1  1.1     1     1 12803000  10935667
2013-01-04  1.0  1.1     1     1 15133000  10335167
2013-01-07  1.0  1.0     1     1 13289000  11284750
2013-01-08  1.0  1.0     1     1 57941000  15668000
2013-01-09  1.0  1.0     1     1 81389000  21386583

Upvotes: 1

Related Questions