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.

Answer 1

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".

Answer 2

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