Read All Worksheets of Excel File into List in R (Example) | Import Sheets | xlsx & readxl Packages
HTML-код
- Опубликовано: 21 авг 2024
- How to bring in all sheets of an Excel file to a list in the R programming language. More details: statisticsglob...
R code of this video:
install.packages("xlsx") # Install xlsx package
library("xlsx") # Load xlsx package
write.xlsx2(iris, "data_all.xlsx", row.names = FALSE) # Create Excel file with multiple sheets
write.xlsx2(mtcars, "data_all.xlsx", row.names = FALSE, append = TRUE, sheetName = "Sheet2")
write.xlsx2(beaver1, "data_all.xlsx", row.names = FALSE, append = TRUE, sheetName = "Sheet3")
install.packages("readxl") # Install & load readxl package
library("readxl")
sheet_names ＜- excel_sheets("data_all.xlsx") # Get sheet names
sheet_names # Print sheet names
list_all ＜- lapply(sheet_names, function(x) { # Read all sheets to list
as.data.frame(read_excel("data_all.xlsx", sheet = x)) } )
names(list_all) ＜- sheet_names # Rename list elements
head(list_all$Sheet1) # Print head of first sheet
head(list_all$Sheet2) # Print head of second sheet
head(list_all$Sheet3) # Print head of third sheet
Follow me on Social Media:
Facebook: / statisticsglobecom
LinkedIn: / statisticsglobe
Reddit: / joachimschork
Twitter: / joachimschork
Nice and crisp as usual. Would you be willing to cover some of the functions from the "glue" package. It seems like a great alternate to "paste". Thanks.
Thanks a lot for the suggestion Haraldur! I have never used the glue package, but it looks interesting. I'll put it on my to-do list for future tutorials.
Great 👍 it's very helpful video
Glad to hear that Sudhir, thank you for the nice comment!
Fantastic video as always! Thank you so much!
Do you have another video about what would be the next step if you do not have the same columns in the different Excel sheets?
Of course there should be some relation between them.
Have you written o publish a video about that topic?
By they way, I am a big fan! You always have fantastic material!
We are very grateful!
Hey, thank you so much for the very kind feedback, glad you find my videos helpful! :) Regarding your question, you may combine data sets with different column names using the bind_rows function of the dplyr package. Have a look here: statisticsglobe.com/r-combine-two-data-frames-with-different-variables-by-rows I hope that helps! Joachim
@@StatisticsGlobe Very interesting material! Thank you so much! You are the best!
Thank you so much, glad you think so! :)
Hey! Love the video ! But can you tell me how to combine all those sheets in that list to one dataframe?
Hey Shuaib, thank you, glad you find it helpful! Are you looking for this? statisticsglobe.com/merge-multiple-data-frames-of-list-in-r Regards, Joachim
Nice. Would it be possible to put each sheet in a different dataset automatically? Thanks
Thanks Giovanni! One way to do this would be to use a for-loop. I am not sure if there are more efficient ways though.
@@StatisticsGlobe
I find that:
library("readxl")
sheet_names
Glad you found a solution!
Hi,thanks for the tutorial. please what if i'm trying to view all of sheet1 for larger data sets as head and tails prints the first and last 6 data sets?
Hello Ade,
Sorry for responding late. You can simply use the View() function for this.
Regards,
Cansu
where came from the iris, mtcars and beaver1 when appending the sheets if there are no mention to it at the table you opened?
Hello Barbara,
They are all built-in datasets of R. To be able to import them you should first install the datasets package if it has not been installed yet. As follows:
install.packages("datasets")
library(datasets)
Regards,
Cansu
Hi, thank you for this video, however, I would like to import data that comes with a package, I have no idea how long the columns are but when I try to import antTraits (from the gllvm package) I get this error "Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE, :
arguments imply differing number of rows: 30, 41" Your assistance would be highly appreciated.
Hello Ditiro,
Sorry for the late response. I did not have any trouble while importing antTraits data. See the code below.
#install.packages("gllvm")
library("gllvm")
#install.packages("openxlsx") # Install xlsx package
library("openxlsx") # Load xlsx package
data(antTraits)
antTraits
write.xlsx(antTraits, "data_all.xlsx", row.names = FALSE)
#install.packages("readxl") # Install & load readxl package
library("readxl")
data
@@cansustatisticsglobe Thank you, I will try this out.
@@ditiromoloto3280 Great!