How to add title to column of the xts time series

Question

I want to download some stock details using quantmod and have successfully saved files using write.csv:

write.csv(df,file="AAPL.csv")

The problem is that there is no header for the date in the csv file.

           AAPL.Open AAPL.High AAPL.Low AAPL.Close AAPL.Volume AAPL.Adjusted
2014-10-01    100.59    100.69    98.70      99.18    51491300      97.09741
2014-10-02     99.27    100.22    98.04      99.90    47757800      97.80230
2014-10-03     99.44    100.21    99.04      99.62    43469600      97.52818
2014-10-06     99.95    100.65    99.42      99.62    37051200      97.52818
2014-10-07     99.43    100.12    98.73      98.75    42094200      96.67644
2014-10-08     98.76    101.11    98.31     100.80    57404700      98.68340
2014-10-09    101.54    102.38   100.61     101.02    77376500      98.89877

I want something like this

  Date      AAPL.Open AAPL.High AAPL.Low AAPL.Close AAPL.Volume AAPL.Adjusted
2014-10-01    100.59    100.69    98.70      99.18    51491300      97.09741
2014-10-02     99.27    100.22    98.04      99.90    47757800      97.80230
2014-10-03     99.44    100.21    99.04      99.62    43469600      97.52818

I tried this

colnames(df)[1] <- "Date"

but it changes title of AAPL.Open instead.

You cannot. The date column is not a column. It is row names. — , Jan 06 '16 at 03:20
More precisely, it is index attribute (http://stackoverflow.com/a/28532262/3710546). — , Jan 06 '16 at 03:25

score 3 · Answer 1 · answered Jan 06 '16 at 04:27

Use write.zoo instead of write.csv to write the xts objects to csv files. Set index.name = "Date" if you want the index column header to be "Date" instead of "Index" (the default).

For example:

require(quantmod)
getSymbols("AAPL")
write.zoo(AAPL, file="AAPL.csv", sep=",", index.name="Date")
readLines("AAPL.csv", n=3)
# [1] "\"Date\",\"AAPL.Open\",\"AAPL.High\",\"AAPL.Low\",\"AAPL.Close\",\"AAPL.Volume\",\"AAPL.Adjusted\""
# [2] "2007-01-03,86.289999,86.579999,81.899999,83.800002,309579900,11.146771"                            
# [3] "2007-01-04,84.050001,85.949998,83.820003,85.659998,211815100,11.394181"

You can then use read.zoo to read these types of files into zoo object (which you can convert to xts via as.xts).

I'd also suggest you use saveRDS or save to save the files in binary format, unless you absolutely must save them as plain text.

score 2 · Accepted Answer · edited May 23 '17 at 12:13

Quoting Joshua Ulrich, "getSymbols does not return a data.frame by default; it returns an xts object. xts objects do not have row names. They have an index attribute that you can access with the index function."

Then you need:

library(quantmod)
getSymbols("AAPL")
row.names(AAPL)
# NULL
AAPL <- as.data.frame(AAPL)
AAPL$Date <- row.names(AAPL)
# Move last column to first position
# AAPL <- AAPL[,c(7, 1:6)]
cln <- ncol(AAPL) # 7
AAPL <- AAPL[, c(cln, 1:(cln-1))]
row.names(AAPL) <- NULL
head(AAPL)
#         Date AAPL.Open AAPL.High AAPL.Low AAPL.Close AAPL.Volume AAPL.Adjusted
# 1 2007-01-03     86.29     86.58    81.90      83.80   309579900      11.14677
# 2 2007-01-04     84.05     85.95    83.82      85.66   211815100      11.39418
# 3 2007-01-05     85.77     86.20    84.40      85.05   208685400      11.31304
# 4 2007-01-08     85.96     86.53    85.28      85.47   199276700      11.36891
# 5 2007-01-09     86.45     92.98    85.15      92.57   837324600      12.31332
# 6 2007-01-10     94.75     97.80    93.45      97.00   738220000      12.90259

how this code can be implemented to a large number of columns(>10). I have tried like this `cln<-ncol(AAPL) AAPL <- AAPL[,c(cln+1, 1:cln)]` but its giving date as numbers — Eka, Jan 06 '16 at 03:47
It is not correct. It should be `AAPL <- AAPL[, c(cln, 1:(cln-1))]`. — , Jan 06 '16 at 03:50

score 1 · Answer 3 · answered Jan 06 '16 at 03:21

1

You can do something like this:

df$Date <- row.names(df)
row.names(df) <- NULL

answered Jan 06 '16 at 03:21

Gopala

10,363
7
45
77

How to add title to column of the xts time series

3 Answers3