Create xts object from CSV

Question

I'm trying to generate an xts from a CSV file. The output looks okay as a simple vector i.e. Date and Value columns are character and numeric, respectively.

However, if I want to make it into an xts, the output seems dubious I'm wondering what is the output on the furthest left column on the xts?

> test <- read.csv("Test.csv", header = TRUE, as.is = TRUE)
> test
    Date Value
1 1/12/2014   1.5
2 2/12/2014   0.9
3 1/12/2015  -0.1
4 2/12/2015  -0.3
5 1/12/2016  -0.7
6 2/12/2016   0.2
7 7/12/2016  -1.0
8 8/12/2016  -0.2
9 9/12/2016  -1.1
> xts(test, order.by = as.POSIXct(test$Date), format = "%d/%m/%Y")
           Date        Value 
0001-12-20 "1/12/2014" " 1.5"
0001-12-20 "1/12/2015" "-0.1"
0001-12-20 "1/12/2016" "-0.7"
0002-12-20 "2/12/2014" " 0.9"
0002-12-20 "2/12/2015" "-0.3"
0002-12-20 "2/12/2016" " 0.2"
0007-12-20 "7/12/2016" "-1.0"
0008-12-20 "8/12/2016" "-0.2"
0009-12-20 "9/12/2016" "-1.1"

I'd simply like to set an xts ordered by Date, rather than the mystery column on the left. I've tried as.Date for the xts as well but have the same results.

The column on the left is in fact not a column but rather the names of the rows ( or rather `row.names` in R). You [cannot really get rid of it except changing the names displayed usually](http://stackoverflow.com/questions/24428051/removing-display-of-r-row-names-from-data-frame]. By the I think you are using the package `xts` , so perhaps you should add that you use it, in your question so we can identify quicker what you are referring to. — Pierre Chevallier, Dec 19 '16 at 10:27
Try this `testXts = xts(test[,-1], order.by = as.Date(test[,1], format = "%d/%m/%Y"))`. Note the typo in your statement `format` should be parameter of `as.Date` and not `xts` function. — Silence Dogood, Dec 19 '16 at 10:32

score 3 · Answer 1 · answered Jan 21 '17 at 15:23

I recommend you use read.zoo to read the data from CSV, then convert the result to xts using as.xts.

Text <- "Date,Value
1/12/2014,1.5
2/12/2014,0.9
1/12/2015,-0.1
2/12/2015,-0.3
1/12/2016,-0.7
2/12/2016,0.2
7/12/2016,-1.0
8/12/2016,-0.2
9/12/2016,-1.1"
z <- read.zoo(text=Text, sep=",", header=TRUE, format="%m/%d/%Y", drop=FALSE)
x <- as.xts(z)
#            Value
# 2014-01-12   1.5
# 2014-02-12   0.9
# 2015-01-12  -0.1
# 2015-02-12  -0.3
# 2016-01-12  -0.7
# 2016-02-12   0.2
# 2016-07-12  -1.0
# 2016-08-12  -0.2
# 2016-09-12  -1.1

Note that you will need to omit text = Text from your actual call, and replace it with file = "your_file_name.csv".

score 1 · Answer 2 · answered Dec 19 '16 at 10:44

The issue appears to be twofold. One, there is a misplaced parenthesis in one of your calls; two, the left most column is the index, making the Date column superfluous.

df <- read.table(text="
  Date Value
  1/12/2014   1.5
  2/12/2014   0.9
  1/12/2015  -0.1
  2/12/2015  -0.3
  1/12/2016  -0.7
  2/12/2016   0.2
  7/12/2016  -1.0
  8/12/2016  -0.2
  9/12/2016  -1.1",
  header=TRUE)

df$Date <- as.Date(df$Date, format="%d/%m/%Y")

library(xts)
xts(df[-1], order.by=df[,1])

#            Value
# 2014-12-01   1.5
# 2014-12-02   0.9
# 2015-12-01  -0.1
# 2015-12-02  -0.3
# 2016-12-01  -0.7
# 2016-12-02   0.2
# 2016-12-07  -1.0
# 2016-12-08  -0.2
# 2016-12-09  -1.1

Create xts object from CSV

2 Answers2