How to handle "write.xlsx" error: arguments imply differing number of rows

Question

I'm trying to write an xlsx file from a list of dataframes that I created but I'm getting an error due to missing data (I couldn't download it). I just want to write the xlsx file besides having this lacking data. Any help is appreciated.

For replication of the problem:

library(quantmod)

name_of_symbols <- c("AKER","YECO","SNOA")
research_dates <- c("2018-11-19","2018-11-19","2018-11-14")
my_symbols_df <- lapply(name_of_symbols, function(x) tryCatch(getSymbols(x, auto.assign = FALSE),error = function(e) { }))
my_stocks_OHLCV <- list()
for (i in 1:3) {
    trade_date <- paste(as.Date(research_dates[i]))
    OHLCV_data <- my_symbols_df[[i]][trade_date]
    my_stocks_OHLCV[[i]] <- data.frame(OHLCV_data)
}

And you can see the missing data down here in my_stocks_OHLCV[[2]] and the write.xlsx error I'm getting:

print(my_stocks_OHLCV)
[[1]]
           AKER.Open AKER.High AKER.Low AKER.Close AKER.Volume AKER.Adjusted
2018-11-19      2.67       3.2     1.56       1.75    15385800          1.75

[[2]]
data frame with 0 columns and 0 rows

[[3]]
           SNOA.Open SNOA.High SNOA.Low SNOA.Close SNOA.Volume SNOA.Adjusted
2018-11-14       1.1      1.14     1.01        1.1      107900           1.1

write.xlsx(my_stocks_OHLCV, "C:/Users/MICRO/Downloads/Datasets_stocks/dux_OHLCV.xlsx")

 Error in (function (..., row.names = NULL, check.rows = FALSE,
 check.names = TRUE,:arguments imply differing number of rows: 1, 0

How do I run write.xlsx even though I have this missing data?

GooJ · Accepted Answer · 2019-05-06T23:38:10.027

The main question you need to ask is, what do you want instead?

As you are working with stock data, the best idea, is that if you don't have data for a stock, then remove it. Something like this should work,

 my_stocks_OHLCV[lapply(my_stocks_OHLCV,nrow)>0]

If you want a row full of NA or 0 Then use the lapply function and for each element of the list, of length 0, replace with either NA's, vector of 0's (c(0,0,0,0,0,0)) etc...

Something like this,

condition <- !lapply(my_stocks_OHLCV,nrow)>0
my_stocks_OHLCV[condition] <- data.frame(rep(NA,6))

Here we define the condition variable, to be the elements in the list where you don't have any data. We can then replace those by NA or swap the NA for 0. However, I can't think of a reason to do this.

A variation on your question, and one you could handle inside your for loop, is to check if you have data, and if you don't, replace the values there, with NAs, and you could given it the correct headers, as you know which stock it relates to.

Hope this helps.

Good answer! but how do I do this? "Do you want an empty row? Then use the lapply function and for each element of the list, of length 0, replace with either NA's, vector of 0's (c(0,0,0,0,0,0)) etc..." — Artur Dutra, May 06 '19 at 23:19
Thanks GooJ! Really appreciate you taking the time to answer — Artur Dutra, May 06 '19 at 23:46

How to handle "write.xlsx" error: arguments imply differing number of rows

1 Answers1