reading all excel cells into a vector with R-CodePudding

I have an excel spreadsheet with a bunch of numbers in it (some empty cells as well) What I want to do is read that file in R in such a way that I can take the numbers from all the non empty cells and put it into some vector. What is the best way to do this?

CodePudding user response：

It's difficult to demonstrate a solution here without an example, but it's straightforward to create one:

library(openxlsx)

set.seed(1)

sample(c(round(rnorm(100), 2), rep("", 100)), 50, TRUE) |>
  matrix(10) |>
  as.data.frame() |>
  write.xlsx("myfile.xlsx")

In our spreadsheet software, the file looks like this:

To get the values in the spreadsheet into a single vector in R, we read it into a data frame, unlist it, convert to numeric, and remove NA values:

all_numbers <- read.xlsx("myfile.xlsx") |>
  unlist() |>
  as.numeric() |>
  na.omit() |>
  c()

all_numbers
#>  [1] -0.44 -0.57  0.82 -0.02  1.36 -1.22 -1.25 -0.04  0.76  0.58  0.88
#> [12]  1.21 -2.21 -0.04  1.12 -0.74 -0.02 -0.16 -0.71 -0.41 -0.11 -0.16
#> [23]  0.03  0.34

You will see these match the numbers in the picture of the spreadsheet.