Write xts/zoo object to csv with index

Question

> library(PerformanceAnalytics)
> data(managers)
> class(managers)
[1] "xts" "zoo"
> head(managers)
              HAM1 HAM2    HAM3    HAM4 HAM5 HAM6 EDHEC LS EQ SP500 TR US 10Y TR US 3m TR
1996-01-31  0.0074   NA  0.0349  0.0222   NA   NA          NA   0.0340   0.00380  0.00456
1996-02-29  0.0193   NA  0.0351  0.0195   NA   NA          NA   0.0093  -0.03532  0.00398
1996-03-31  0.0155   NA  0.0258 -0.0098   NA   NA          NA   0.0096  -0.01057  0.00371
1996-04-30 -0.0091   NA  0.0449  0.0236   NA   NA          NA   0.0147  -0.01739  0.00428
1996-05-31  0.0076   NA  0.0353  0.0028   NA   NA          NA   0.0258  -0.00543  0.00443
1996-06-30 -0.0039   NA -0.0303 -0.0019   NA   NA          NA   0.0038   0.01507  0.00412

When I try to export this object to csv using write.csv, the date indices are not exported. How do I enforce the first column of the csv output to be the date indices?

"","HAM1","HAM2","HAM3","HAM4","HAM5","HAM6","EDHEC LS EQ","SP500 TR","US 10Y TR","US 3m TR"
"1",0.0074,NA,0.0349,0.0222,NA,NA,NA,0.034,0.0038,0.00456
"2",0.0193,NA,0.0351,0.0195,NA,NA,NA,0.0093,-0.03532,0.00398
"3",0.0155,NA,0.0258,-0.0098,NA,NA,NA,0.0096,-0.01057,0.00371
"4",-0.0091,NA,0.0449,0.0236,NA,NA,NA,0.0147,-0.01739,0.00428
"5",0.0076,NA,0.0353,0.0028,NA,NA,NA,0.0258,-0.00543,0.00443
"6",-0.0039,NA,-0.0303,-0.0019,NA,NA,NA,0.0038,0.01507,0.00412
"7",-0.0231,NA,-0.0337,-0.0446,NA,NA,NA,-0.0442,-0.001,0.00454
"8",0.0395,-1e-04,0.0461,0.0351,NA,NA,NA,0.0211,-0.00448,0.00451
"9",0.0147,0.1002,0.0653,0.0757,NA,NA,NA,0.0563,0.02229,0.0047
"10",0.0288,0.0338,0.0395,-0.018,NA,NA,NA,0.0276,0.02869,0.00428
"11",0.0156,0.0737,0.0666,0.0458,NA,NA,NA,0.0756,0.02797,0.00427
"12",0.0176,0.0298,0.0214,0.0439,NA,NA,NA,-0.0198,-0.02094,0.00442

Hyperventilation

What is the class of the "data frame"? – Glaciology

Have updated my answer to handle zoo. – Staciestack

Use write.zoo instead of write.csv. It handles the fact that instead of rownames xts objects have an index. – Staciestack

@fgnu You hit right on the point. Yes, write.zoo adds the column header to the output csv file! – Hyperventilation

Answer 1

The date does show up. Here is a reproducible example:

dfA = read.table(textConnection('row.name HAM1    HAM2    HAM3    HAM4    HAM5    HAM6 "EDHEC LS EQ"  SP500 "TR US 10Y" "TR US 3m TR"
1996-01-31  0.0074      NA  0.0349  0.0222      NA      NA          NA  0.034000   0.00380  0.00456
1996-02-29  0.0193      NA  0.0351  0.0195      NA      NA          NA  0.009300  -0.03532  0.00398
1996-03-31  0.0155      NA  0.0258 -0.0098      NA      NA          NA  0.009600  -0.01057  0.00371
1996-04-30 -0.0091      NA  0.0449  0.0236      NA      NA          NA  0.014700  -0.01739  0.00428
1996-05-31  0.0076      NA  0.0353  0.0028      NA      NA          NA  0.025800  -0.00543  0.00443
1996-06-30 -0.0039      NA -0.0303 -0.0019      NA      NA          NA  0.003800   0.01507  0.00412
1996-07-31 -0.0231      NA -0.0337 -0.0446      NA      NA          NA -0.044200  -0.00100  0.00454
1996-08-31  0.0395 -0.0001  0.0461  0.0351      NA      NA          NA  0.021100  -0.00448  0.00451
1996-09-30  0.0147  0.1002  0.0653  0.0757      NA      NA          NA  0.056300   0.02229  0.00470
1996-10-31  0.0288  0.0338  0.0395 -0.0180      NA      NA          NA  0.027600   0.02869  0.00428'), header = TRUE)

row.names(dfA) = as.Date(dfA$row.name, format = '%Y-%m-%d')
dfA$row.name = NULL
write.csv(dfA, file = 'delete.txt', row.names = TRUE)

Update

zoo will lead to similar handling:

library(zoo)
zooA = as.zoo(dfA, order.by = row.names(dfA))
write.csv(zooA, file = 'delete.txt', row.names = TRUE)

"","HAM1","HAM2","HAM3","HAM4","HAM5","HAM6","EDHEC.LS.EQ","SP500","TR.US.10Y","TR.US.3m.TR" "1996-01-31",0.0074,NA,0.0349,0.0222,NA,NA,NA,0.034,0.0038,0.00456 "1996-02-29",0.0193,NA,0.0351,0.0195,NA,NA,NA,0.0093,-0.03532,0.00398 "1996-03-31",0.0155,NA,0.0258,-0.0098,NA,NA,NA,0.0096,-0.01057,0.00371 "1996-04-30",-0.0091,NA,0.0449,0.0236,NA,NA,NA,0.0147,-0.01739,0.00428 "1996-05-31",0.0076,NA,0.0353,0.0028,NA,NA,NA,0.0258,-0.00543,0.00443 "1996-06-30",-0.0039,NA,-0.0303,-0.0019,NA,NA,NA,0.0038,0.01507,0.00412 "1996-07-31",-0.0231,NA,-0.0337,-0.0446,NA,NA,NA,-0.0442,-0.001,0.00454 "1996-08-31",0.0395,-1e-04,0.0461,0.0351,NA,NA,NA,0.0211,-0.00448,0.00451 "1996-09-30",0.0147,0.1002,0.0653,0.0757,NA,NA,NA,0.0563,0.02229,0.0047 "1996-10-31",0.0288,0.0338,0.0395,-0.018,NA,NA,NA,0.0276,0.02869,0.00428

Update 2

Turns out that OP has an xts object that has anindex attribute rather than a rownames attribute, which can be written out using a call to write.zoo rather than write.csv (which looks for rownames).

Staciestack

This works for your construct, but row.names(managers) returns me NULL, only index(managers) return me an array of dates in string format. – Hyperventilation

Answer 2

As @tchakravarty pointed out, write.zoo should be used. Here is what worked best for me:

write.zoo(tdata, filename, quote = FALSE, sep = ",")

Also, if the timestamps have subsecond precision you will need something like options(digits.secs = 6) for the decimal places to show in the csv file.

Weig

Answer 3

write.csv(t, "t.csv", row.names=TRUE)

row.names: either a logical value indicating whether the row names of ‘x’ are to be written along with ‘x’, or a character vector of row names to be written.

Notification

Thanks for your help, but the output is still the same. The date index does not show up. – Hyperventilation

Answer 4

You can transform your xts object into a data frame before writing the csv file with write.csv:

write.csv(as.data.frame(managers), "filename.csv", row.names = TRUE)

Glaciology

I don't think it is a zoo object, because coercion of a zoo object is automatic in write.csv. It is most probably an xts object and OP wil need to use write.zoo instead. – Staciestack

@fgnu write.zoo is the best solution for this case. You should add this to your answer. – Glaciology