CODEWITHSUNDEEP
rlisttime-seriesxtszoo
Bryan Pham
Bryan Pham

Reputation: 45

write list of xts objects to excel

I have a list of 337 XTS objects which looks like this: 

> head(blowup.instances)
$`AERI.US.Equity`
           AERI.US.Equity
2015-04-24     -0.6363379

$SRPT.US.Equity
           SRPT.US.Equity
2013-11-12     -0.6400985
2016-01-15     -0.5485299

$PTCT.US.Equity
           PTCT.US.Equity
2016-02-23      -0.616419

When I try to write them to a csv, it does not work:

> write.csv(blowup.instances, "blowupInstances.csv")
Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE,  : 
  arguments imply differing number of rows: 1, 2, 3, 7, 4, 9, 5, 6, 18, 37, 8, 10, 78, 25, 11, 12, 20, 59, 17, 19, 27, 29, 16, 14, 31, 15, 51, 28, 54

Now I know why this doesn't work, but I want to know a solution to this other than writing out a large data frame full of NA values that I would have to remove in Excel. Any ideas?

Upvotes: 1

Views: 1509

Answers (2)

FXQuantTrader
FXQuantTrader

Reputation: 6891

It looks like blowup.instances is a list. Don't write the list to file.

Assuming this is the case, you can do either of these:

syms <- names(blowup.instances)

lapply(syms, FUN = function(sym) {
  write.zoo(x = blowup.instances[[sym]], file = paste0(sym, "-data.csv"))
})

Alternatively: 

res <- do.call(cbind, blowup.instances)
write.zoo(res, file = "crosssecdata.csv")

I recommend the first approach. Put the data for each symbol in a separate file.

Upvotes: 1

WaltS
WaltS

Reputation: 5530

If you're going to read your list of xts objects into Excel, you probably need to convert it to a flat file. The code below transforms each xts object into a data frame with each row containing the xts object name, and its dates and values. The map_dfr function from the purrr package in tidyverse is used to loop over the xts objects in the list and combine the results into a single data frame.

library(xts) 
 library(tidyverse)
#
#  combine list of xts objects into a single data frame with equity names and dates
# 

  df_out <- map_dfr(blowup.instances, function(y) data_frame(Name = names(y), Date = index(y), value=as.vector(coredata(y))) )
#
#   write as csv flat file
#
  write.csv(df_out, file="blowupInstances.csv", row.names = FALSE)

The data frame written to the file is

df_out
# A tibble: 4 x 3
  Name           Date       value
  <chr>          <date>     <dbl>
1 AERI.US.Equity 2018-06-27 -0.5 
2 SRPT.US.Equity 2018-06-26 -0.64
3 SRPT.US.Equity 2018-06-27 -0.55
4 PTCT.US.Equity 2018-06-20 -0.7

where the data a simple example set I made.

Upvotes: 0

Related Questions