[R] automatic convert list to dataframe

[Kai Yang]

 Hello Rui and Bert,
This is a great help! Thank you both!
Here is my job assignment: boss accumulate many years excel reports from same lab. Each report contents multiple data sheets. Those sheets may/may not updated, and some sheets are new. Good news is, the same testing result data will be saved in a data sheet and assign the same sheet name with same fields name, but does not in same file. Boss wants me to get non.duplicate data for difference testing from those excel files. So, I want to load all data into R and assign each data frame name as filename_sheetname. I use the 2nd suggestion from Rui to get this step done! Thanks Rui again. This method save a lot of time for me. Now, I can combine data frames (same _sheetname) together and remove duplicate.
Best,
Kai
    On Monday, October 3, 2022 at 03:41:45 PM PDT, Bert Gunter <bgunter.4567 using gmail.com> wrote:  
 
 ... But why not dispense with multiple files, name juggling, and environments by simply putting everything in one list of lists (lists are recursive structures!):

all_files <- lapply(filenames, function(nm)import_list(nm,...))names(all_files) <- filenames
## Note: the ... are any optional parameters to import_list() that may be needed. 
## Perhaps none.

all_files will then be a single list, each of whose components is a list of data frames for all the sheets in the file. I would think that such a list could easily be accessed and manipulated via list indexing and *apply-family functions. Just seems like a more straightforward approach to me (assuming I haven't misunderstood, of course).
Bert




On Mon, Oct 3, 2022 at 3:04 PM Rui Barradas <ruipbarradas using sapo.pt> wrote:

Hello,

Here are two more attempts at solving the problem.

1. Instead of having 30-40 data.frames per file in the globalenv, not a 
good idea, the following code will create as many lists as you have 
files and each list is a list of df's.


temp_list <- vector("list", length = length(filenames))
for(i in seq_along(filenames)){
   xlfile <- filenames[i]
   temp_list[[i]] <- import_list(paste0(xlfile, ".xlsx"))
}
list2env(temp_list, envir = .GlobalEnv)
rm(temp_list)

Now you can access the data with code like


file1$dx1       # a data.frame, first sheet in excel file1
file1[["dx1"]]  # equivalent


2. I cannot see a reason why the following shouldn't work. It creates 
lots of data.frames in the globalenv, 30-40 per file. This makes the 
globalenv messy and is not recommended.


temp_list <- vector("list", length = length(filenames))
for(i in seq_along(filenames)){
   # import the data
   xlfile <- filenames[i]
   temp_list[[i]] <- import_list(paste0(xlfile, ".xlsx"))
   # now take care of the names
   new_names <- paste(xlfile, names(temp_list[[i]]), sep = "_")
   names(temp_list[[i]]) <- new_names
   # create the current data.frames in the globalenv
   list2env(temp_list[[i]], envir = .GlobalEnv)
}
rm(temp_list)


Hope this helps,

Rui Barradas


Às 20:51 de 03/10/2022, Kai Yang escreveu:
>   Hi Rui,
> I copied "list2env(i, envir = .GlobalEnv)" to the code, but I got the same error message of "first argument must be a named list". Maybe list2env cannot put in loop? the code works very well outside of for loop.
> One more thing, the difference file may have same sheet name. that's why I want to add file name in front of sheet name to avoid overwriting. It still works well outside of loop, but doesn't work in loop. I don't know how to fix the problems.
> Thank you,
> Kai
> 
>      On Monday, October 3, 2022 at 12:09:04 PM PDT, Rui Barradas <ruipbarradas using sapo.pt> wrote:
>   
>   Hello,
> 
> If in each iteration i is a list, try removing the call to names().
> Try, in the loop,
> 
> 
> list2env(i, envir = .GlobalEnv)
> 
> 
> The error message is telling that list2env's first argument must be a
> named list and names(i) is an unnamed vector, it's i that's the named
> list (you even changed its names in the previous instruction).
> 
> Hope this helps,
> 
> Rui Barradas
> 
> Às 18:38 de 03/10/2022, Kai Yang escreveu:
>>    Hi Rui,
>> list2env(file1, envir = .GlobalEnv) is worked very well. Thank you.
>>
>> But when I tried to put the sample code  into for loop. I got error message:
>> for(i in filenames){
>>      assign(i, import_list(paste0(i, ".xlsx", sep="")))
>>      names(i) <- paste(i, names(i), sep = "_")
>>      list2env(names(i), envir = .GlobalEnv)
>> }
>> Error in list2env(names(i), envir = .GlobalEnv) :   first argument must be a named list
>>
>> It seems I cannot put names(i) into for loop, Could you please help me to debug it?
>> Thank you,Kai    On Monday, October 3, 2022 at 10:14:25 AM PDT, Rui Barradas <ruipbarradas using sapo.pt> wrote:
>>    
>>    Hello,
>>
>>
>> list2env(file1, envir = .GlobalEnv)
>>
>>
>> will create data.frames dx1, dx2, etc, in the global environment.
>> If you really need the names file1_dx1, file1_dx2, etc, you can first
>> change the names
>>
>>
>> names(file1) <- paste("file1", names(file1), sep = "_")
>>
>>
>> and then run list2env like above.
>>
>> Hope this helps,
>>
>> Rui Barradas
>>
>> Às 16:51 de 03/10/2022, Kai Yang via R-help escreveu:
>>> Hi R team,
>>> I can use rio package to read excel file into R as a list. The excel file content multiple sheets (30 - 40 data sheets). I can convert each data elements into dataframe manually. I have multiple excel files with multiple data sheets. I need to load them into R and do the comparison for same sheet name from difference excel file. My current code is:
>>>      library(rio)   setwd ("C:/temp")
>>> filenames <- gsub("\\.xlsx$","", list.files(pattern="\\.xlsx$"))
>>> for(i in filenames){
>>>        assign(i, import_list(paste0(i, ".xlsx", sep="")))
>>> }
>>> file1_dx1     <-  file1[["dx1"]]
>>>
>>> file1_dx2     <-  file1[["dx2"]]
>>>
>>> file1_dx3     <-  file1[["dx3"]]
>>>
>>> file2_dx1     <-  file1[["dx1"]]
>>>
>>> file2_dx2     <-  file1[["dx2"]]
>>> ......
>>>
>>> I hope the code can automatic converting the list (may have 30 - 40 lists) by adding file name (such as: filename_sheetname) and put it in for loop
>>>
>>>
>>> Thank you,
>>> Kai
>>>
>>>
>>>
>>>
>>>        [[alternative HTML version deleted]]
>>>
>>> ______________________________________________
>>> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>>> and provide commented, minimal, self-contained, reproducible code.
>>      
>

______________________________________________
R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

  
	[[alternative HTML version deleted]]