Reading an excel array with missing values into R using BERT interface

Question

I am using BERT (Excel/R interface) to implement R functions into Excel. When a function such as eigen is applied to an array of numbers it works fine, but there is an error if there are missing value / strings in the Excel array. I want to remove, in the R function - not in Excel - the columns (or rows) of the array that have non numeric data. The problem of course is that BERT gives to R a matrix when the array has only numbers but a list if there are non numeric data in the Excel array. So basically I want to change that R list into the equivalent R dataframe with missing values at the right places.

So if in EXCEL I have

1 2
3 4

In the range (A1:B2) then I define this function in R.

EigenValues <- function(mat) { 
  print(mat)
  print(mode(mat))
  E<- eigen(mat)
  E$values
 }

In Excel this works just fine and the R console shows me that mat is a matrix. If in cell A1 in Excel I have a blank value, then R describes mat as follows :

[,1] [,2]
[1,] NA 2   
[2,] 3  4   
[1] "list"

An obviously I get an error in Excel. So I would like to to remove the rows (or columns) where I have an NA. I have searched a lot and found nothing that works with that kind of list. E.g. this, Converting nested list to dataframe, using the rbind.list function, does not work.

Using dplyr and rbind_all does not work either. I suspect this is because I have different number of missing values in each row or column.

This How to convert a list consisting of vector of different lengths to a usable data frame in R? does not work because the missing values are not necessarily at the end of each row.

rbind.fill(Reduce(unlist, mat))

will produce only a single NA.

algoquant · Answer 1 · 2017-06-12T18:49:06.597

If one of the matrix elements contains bad data (NA value or missing data), then BERT coerces the input matrix into a list. But the input list still has a dim attribute, so it's possible to coerce it back into a matrix. Then you can remove the rows and columns containing bad data using the function complete.cases(). The following function worked for me:

EigenValues <- function(mat) {
  mat <- matrix(unlist(mat), nrow=dim(mat)[1])
  complete_rows <- complete.cases(mat)
  complete_cols <- complete.cases(t(mat))
  mat <- mat[t(complete_rows), t(complete_cols)]
  E <- eigen(mat)
  E$values
}

Reading an excel array with missing values into R using BERT interface

1 Answers1