[R] automatic convert list to dataframe

[Bert Gunter]

... 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]]