I am trying to automate the way i read my tables. I have an Excel sheet I'm
reading using openxlsx package. The sheet contains over 30 sheets, i would
like to save each of them as separate objects.
my workflow for now is as such:
wb <- loadWorkbook(xlsxFile =
"Output/Up_Down_Regulated_Gene_Lists.xlsx")
NAMES <- gsub(pattern = " ", replacement = "_", x =
names(wb))
[1] "KO1_vs._WT_up" "KO2_vs._WT_down"
"KO3_vs._WT_up"
"KO1_vs._WT_down" "KO2_vs._WT_up" ...
for (i in 2:length(names(wb)) ){
tmp <- read.xlsx(wb, sheet = i)
... Here I would like to have each sheet read in and sacved as a separate
data.frame.
}
Is there a way to read for each sheet the name from names(wb) and convert
it to a name for the object?
something like this
The object KO1_vs._WT_up will save the first sheet with the same name
The object KO2_vs._WT_down will save the second sheet.
etc.
Any ideas?
thanks
Assa
[[alternative HTML version deleted]]
On Fri, 29 Mar 2019 14:07:16 +0100 Assa Yeroslaviz <frymor at gmail.com> wrote:> Is there a way to read for each sheet the name from names(wb) and > convert it to a name for the object?See `get` and `assign` functions for a way to use strings as object names. Generally, it might not be a good idea to do that (what if your spreadsheet contains a sheet named `c` or `q`, or `data.frame`?). If you want to save yourself some typing, consider using `with` or `within`, though they suffer from similar problems. -- Best regards, Ivan
I think you want ?assign Bert Gunter "The trouble with having an open mind is that people keep coming along and sticking things into it." -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) On Fri, Mar 29, 2019 at 10:39 AM Assa Yeroslaviz <frymor at gmail.com> wrote:> I am trying to automate the way i read my tables. I have an Excel sheet I'm > reading using openxlsx package. The sheet contains over 30 sheets, i would > like to save each of them as separate objects. > > my workflow for now is as such: > > wb <- loadWorkbook(xlsxFile = "Output/Up_Down_Regulated_Gene_Lists.xlsx") > NAMES <- gsub(pattern = " ", replacement = "_", x = names(wb)) > [1] "KO1_vs._WT_up" "KO2_vs._WT_down" "KO3_vs._WT_up" > "KO1_vs._WT_down" "KO2_vs._WT_up" ... > for (i in 2:length(names(wb)) ){ > tmp <- read.xlsx(wb, sheet = i) > > ... Here I would like to have each sheet read in and sacved as a separate > data.frame. > > } > > Is there a way to read for each sheet the name from names(wb) and convert > it to a name for the object? > > something like this > The object KO1_vs._WT_up will save the first sheet with the same name > The object KO2_vs._WT_down will save the second sheet. > etc. > > Any ideas? > > thanks > Assa > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at 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]]
Hello,
I recommend you save them all in a list. It's a much better idea than to
have length(names(wb)) objects in the globalenv.
Something like the following would read them all in one go.
xls_list <- lapply(seq_along(names(wb))[-1], function(i){
read.xlsx(wb, sheet = i)
})
names(xls_list) <- gsub(" ", "_", names(wb)[-1])
Then, you can process the list members with the *apply() functions, as in
result_list <- lapply(xls_list, some_function, arg2, arg3)
The first function argument is the sheet, arg2, etc, would be other
function arguments.
Hope this helps,
Rui Barradas
?s 13:07 de 29/03/2019, Assa Yeroslaviz escreveu:> I am trying to automate the way i read my tables. I have an Excel sheet
I'm
> reading using openxlsx package. The sheet contains over 30 sheets, i would
> like to save each of them as separate objects.
>
> my workflow for now is as such:
>
> wb <- loadWorkbook(xlsxFile =
"Output/Up_Down_Regulated_Gene_Lists.xlsx")
> NAMES <- gsub(pattern = " ", replacement = "_", x =
names(wb))
> [1] "KO1_vs._WT_up" "KO2_vs._WT_down"
"KO3_vs._WT_up"
> "KO1_vs._WT_down" "KO2_vs._WT_up" ...
> for (i in 2:length(names(wb)) ){
> tmp <- read.xlsx(wb, sheet = i)
>
> ... Here I would like to have each sheet read in and sacved as a separate
> data.frame.
>
> }
>
> Is there a way to read for each sheet the name from names(wb) and convert
> it to a name for the object?
>
> something like this
> The object KO1_vs._WT_up will save the first sheet with the same name
> The object KO2_vs._WT_down will save the second sheet.
> etc.
>
> Any ideas?
>
> thanks
> Assa
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at 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.
>
Seconded! On March 29, 2019 11:33:01 AM PDT, Rui Barradas <ruipbarradas at sapo.pt> wrote:>Hello, > >I recommend you save them all in a list. It's a much better idea than >to >have length(names(wb)) objects in the globalenv. >Something like the following would read them all in one go. > >xls_list <- lapply(seq_along(names(wb))[-1], function(i){ > read.xlsx(wb, sheet = i) >}) > >names(xls_list) <- gsub(" ", "_", names(wb)[-1]) > > >Then, you can process the list members with the *apply() functions, as >in > >result_list <- lapply(xls_list, some_function, arg2, arg3) > >The first function argument is the sheet, arg2, etc, would be other >function arguments. > >Hope this helps, > >Rui Barradas > >?s 13:07 de 29/03/2019, Assa Yeroslaviz escreveu: >> I am trying to automate the way i read my tables. I have an Excel >sheet I'm >> reading using openxlsx package. The sheet contains over 30 sheets, i >would >> like to save each of them as separate objects. >> >> my workflow for now is as such: >> >> wb <- loadWorkbook(xlsxFile >"Output/Up_Down_Regulated_Gene_Lists.xlsx") >> NAMES <- gsub(pattern = " ", replacement = "_", x = names(wb)) >> [1] "KO1_vs._WT_up" "KO2_vs._WT_down" "KO3_vs._WT_up" >> "KO1_vs._WT_down" "KO2_vs._WT_up" ... >> for (i in 2:length(names(wb)) ){ >> tmp <- read.xlsx(wb, sheet = i) >> >> ... Here I would like to have each sheet read in and sacved as a >separate >> data.frame. >> >> } >> >> Is there a way to read for each sheet the name from names(wb) and >convert >> it to a name for the object? >> >> something like this >> The object KO1_vs._WT_up will save the first sheet with the same >name >> The object KO2_vs._WT_down will save the second sheet. >> etc. >> >> Any ideas? >> >> thanks >> Assa >> >> [[alternative HTML version deleted]] >> >> ______________________________________________ >> R-help at 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 at 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.-- Sent from my phone. Please excuse my brevity.