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

Solution 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).

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

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

Solution 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)

View more solutions

Comments

[zsljulius]

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

[zsljulius]

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

[tchakravarty]

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.

[Sven Hohenstein]

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