0

I'm new to R and cannot figure out the following problem in dynamically assigning multiple values to variables in a double loop:

I want to generate variables that differ in a) their names that are already stored in var_names b) put data from an excel file into them by looping over the sheet names

Assume an excel file with 3 sheets; I compiled one for this example; you can find it here.

var_names <- c("a", "b", "c")
cell_ranges <- c("A1:A2", "B1:B2", "C1:C2")
sheet_names <- c("sheet_1", "sheet_2", "sheet_3")

For a single variable x, the code would look like that:

x <- c()
for (sheet in sheet_names) {
    x<-c(x, read_excel("file.xls", sheet = sheet, range="cell range that
                        fits x; e.g., A1:A2"))
}

But I don't want to do that for each single variable; I want a loop over these to be computed variables, something similar to that:

for (i in 1:length(cell_ranges)) {
  for(sheet in sheet_names){
    assign(paste(var_names[i], "", sep=""),
           read_excel("file.xls", sheet = sheet,
                      range=cell_ranges[i]))
  }
}

Needless to say, it doesn't work because only the value from the last excel sheet is stored in the respective variable.

I tried to change the assign code like that but it didn't work

assign(paste(var_names[i], "", sep=""),
       c(var_names[i],
         read_excel("file.xls", sheet = sheet, range=cell_ranges[i])))

How can I change my loop so that my final variables contain the respective values of all sheets? With the sample sheet the result should look like a list of 3 for each of a, b, and c. I'm aware that being new means that I might have not expressed my needs clearly enough; please let me know and I'll try to explain in more detail. Thanks!

da_the
  • 31
  • 1
  • 3
  • 2
    Hi, could you please try to make a more [reproducible example](https://stackoverflow.com/help/mcve)? You'll have a much better chances of getting a great answer! – jay.sf Feb 12 '19 at 11:34
  • 1
    Forget `assign`. It's a function for experts and not to be misused by beginners. Just put all these variables in a list, e.g., `L <- list(); L[[var_names[i]]] <- ...`. – Roland Feb 12 '19 at 11:44
  • Thanks, Roland. I understand that you warn me about using assign. Yet, I cannot figure out how a list would solve my problem. – da_the Feb 12 '19 at 12:01

1 Answers1

0

The trick was to store the data first in an additional variable "a" and then use "a" to assign the data to the final variable

for (i in 1:length(cell_ranges)) {
  a <- c()
  for(sheet in sheet_names) {
    a <- c(a, read_excel("file.xlsx", sheet = sheet,range=cell_ranges[i]))
    assign(var_names[i], a)
  }
}
da_the
  • 31
  • 1
  • 3