Creating matrix variables from excel sheet within a for loop

General
1

I'm trying to make a loop read individual sheets from an excel file and load them into individual matrices in R. Im getting stuck on the syntax to make the loop create independent files using the counter "i". I'd like the output to be Volcano.0, Volcano.1, Volcano.2, Volcano.3 etc....

Here is what I have that doesn't work:

for (i in 0:16){
  tab  <- paste0("Cluster", " ", i)
  Volcano.[i] <- read_excel("/MyWorksheet.xlsx", sheet = tab)
}

I tried Volcano.i but that obviously just gave me Volcano.i at the end. What am I missing here?

2

I would load the data from each sheet into a list, naming each element of the list.

VolcanoList <- vector(mode = "list", length = 17)
for (i in 0:16) {
  tab  <- paste0("Cluster", " ", i)
  VolcanoList[[i]] <- read_excel("/MyWorksheet.xlsx", sheet = tab)
}
names(VolcanoList) <- paste0("Volcano.", 0:16)

That code is untested, I hope I didn't make any silly mistakes.

3

By the way, I suspect that the read_excel() function will a return a data frame, not a matrix. A data frame can have columns with different kinds of data but a matrix must be entirely of one data type.

4

I tested it it returns an error, I think it is because you are trying to assign data to the [0] element of Volcanolist?

the error specifically was:

New names:                                                                                            
* `` -> ...1
Error in VolcanoList[[i]] <- read_excel("/Users/Chris/Desktop/Work/Mouse/IMQ/Volcano Plot/DEGbyTreatmentIMQ-4.xlsx",  : 
  attempt to select less than one element in integerOneIndex
5

That makes sense. Just change

VolcanoList[[i]] <- read_excel("/MyWorksheet.xlsx", sheet = tab)

to

VolcanoList[[i+1]] <- read_excel("/MyWorksheet.xlsx", sheet = tab)
6

Another option using purrr

library(purrr)

walk2(paste0("Cluster", " ", 0:16),
      paste0("Volcano", " ", 0:16),
      ~ assign(.y,
               read_excel("/MyWorksheet.xlsx", sheet = .x),
               envir = globalenv()
               )
      )
7

Thanks so much guys its works perfectly now!

Cheers!

8

If your question's been answered (even by you!), would you mind choosing a solution? It helps other people see which questions still need help, or find solutions if they have similar problems. Here’s how to do it:

9

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.