Reading in Multiple Excel Sheets with lapply and {readxl}
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
Intruduction
Reading in an Excel file with multiple sheets can be a daunting task, especially for users who are not familiar with the process. In this blog post, we will walk through a sample function that can be used to read in an Excel file with multiple sheets using the R programming language.
Function
The function we will be using is called excel_sheet_reader(). This function takes one argument: filename, which is the name of the Excel file we want to read in. This function, since it is using the {readxl} package will automatically read that data to a tibble.
Example
Here is the function:
excel_sheet_reader <- function(filename) {
sheets <- excel_sheets(filename)
x <- lapply(sheets, function(X) read_excel(filename, sheet = X))
names(x) <- sheets
x
}
The first thing the excel_sheet_reader() function does is to determine the names of all the sheets in the Excel file using the excel_sheets function from the readxl package. This function returns a character vector containing the names of all the sheets in the Excel file.
sheets <- excel_sheets(filename)
Next, the function uses the lapply function to loop through all the sheet names and read in each sheet using the read_excel() function, also from the readxl package. This function takes two arguments: filename, which is the name of the Excel file, and sheet, which is the name of the sheet we want to read in. The lapply function returns a list containing all the sheets.
x <- lapply(sheets, function(X) read_excel(filename, sheet = X))
Finally, the function uses the names function to assign the sheet names to the list of sheets and returns the list.
names(x) <- sheets x
Now that we have explained the excel_sheet_reader() function, let’s use it to read in the iris and mtcars datasets.
library(healthyR)
library(dplyr)
library(writexl)
library(readxl)
iris |>
named_item_list(Species) |>
write_xlsx(path = "iris.xlsx")
mtcars |>
named_item_list(cyl) |>
write_xlsx(path = "mtcars.xlsx")
iris_sheets <- excel_sheet_reader("iris.xlsx")
mtcars_sheets <- excel_sheet_reader("mtcars.xlsx")
Now lets see the structure of each file.
iris_sheets
$setosa
# A tibble: 50 × 5
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<dbl> <dbl> <dbl> <dbl> <chr>
1 5.1 3.5 1.4 0.2 setosa
2 4.9 3 1.4 0.2 setosa
3 4.7 3.2 1.3 0.2 setosa
4 4.6 3.1 1.5 0.2 setosa
5 5 3.6 1.4 0.2 setosa
6 5.4 3.9 1.7 0.4 setosa
7 4.6 3.4 1.4 0.3 setosa
8 5 3.4 1.5 0.2 setosa
9 4.4 2.9 1.4 0.2 setosa
10 4.9 3.1 1.5 0.1 setosa
# ℹ 40 more rows
$versicolor
# A tibble: 50 × 5
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<dbl> <dbl> <dbl> <dbl> <chr>
1 7 3.2 4.7 1.4 versicolor
2 6.4 3.2 4.5 1.5 versicolor
3 6.9 3.1 4.9 1.5 versicolor
4 5.5 2.3 4 1.3 versicolor
5 6.5 2.8 4.6 1.5 versicolor
6 5.7 2.8 4.5 1.3 versicolor
7 6.3 3.3 4.7 1.6 versicolor
8 4.9 2.4 3.3 1 versicolor
9 6.6 2.9 4.6 1.3 versicolor
10 5.2 2.7 3.9 1.4 versicolor
# ℹ 40 more rows
$virginica
# A tibble: 50 × 5
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<dbl> <dbl> <dbl> <dbl> <chr>
1 6.3 3.3 6 2.5 virginica
2 5.8 2.7 5.1 1.9 virginica
3 7.1 3 5.9 2.1 virginica
4 6.3 2.9 5.6 1.8 virginica
5 6.5 3 5.8 2.2 virginica
6 7.6 3 6.6 2.1 virginica
7 4.9 2.5 4.5 1.7 virginica
8 7.3 2.9 6.3 1.8 virginica
9 6.7 2.5 5.8 1.8 virginica
10 7.2 3.6 6.1 2.5 virginica
# ℹ 40 more rows
Now mtcars_sheets
mtcars_sheets
$`4`
# A tibble: 11 × 11
mpg cyl disp hp drat wt qsec vs am gear carb
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 22.8 4 108 93 3.85 2.32 18.6 1 1 4 1
2 24.4 4 147. 62 3.69 3.19 20 1 0 4 2
3 22.8 4 141. 95 3.92 3.15 22.9 1 0 4 2
4 32.4 4 78.7 66 4.08 2.2 19.5 1 1 4 1
5 30.4 4 75.7 52 4.93 1.62 18.5 1 1 4 2
6 33.9 4 71.1 65 4.22 1.84 19.9 1 1 4 1
7 21.5 4 120. 97 3.7 2.46 20.0 1 0 3 1
8 27.3 4 79 66 4.08 1.94 18.9 1 1 4 1
9 26 4 120. 91 4.43 2.14 16.7 0 1 5 2
10 30.4 4 95.1 113 3.77 1.51 16.9 1 1 5 2
11 21.4 4 121 109 4.11 2.78 18.6 1 1 4 2
$`6`
# A tibble: 7 × 11
mpg cyl disp hp drat wt qsec vs am gear carb
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 21 6 160 110 3.9 2.62 16.5 0 1 4 4
2 21 6 160 110 3.9 2.88 17.0 0 1 4 4
3 21.4 6 258 110 3.08 3.22 19.4 1 0 3 1
4 18.1 6 225 105 2.76 3.46 20.2 1 0 3 1
5 19.2 6 168. 123 3.92 3.44 18.3 1 0 4 4
6 17.8 6 168. 123 3.92 3.44 18.9 1 0 4 4
7 19.7 6 145 175 3.62 2.77 15.5 0 1 5 6
$`8`
# A tibble: 14 × 11
mpg cyl disp hp drat wt qsec vs am gear carb
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 18.7 8 360 175 3.15 3.44 17.0 0 0 3 2
2 14.3 8 360 245 3.21 3.57 15.8 0 0 3 4
3 16.4 8 276. 180 3.07 4.07 17.4 0 0 3 3
4 17.3 8 276. 180 3.07 3.73 17.6 0 0 3 3
5 15.2 8 276. 180 3.07 3.78 18 0 0 3 3
6 10.4 8 472 205 2.93 5.25 18.0 0 0 3 4
7 10.4 8 460 215 3 5.42 17.8 0 0 3 4
8 14.7 8 440 230 3.23 5.34 17.4 0 0 3 4
9 15.5 8 318 150 2.76 3.52 16.9 0 0 3 2
10 15.2 8 304 150 3.15 3.44 17.3 0 0 3 2
11 13.3 8 350 245 3.73 3.84 15.4 0 0 3 4
12 19.2 8 400 175 3.08 3.84 17.0 0 0 3 2
13 15.8 8 351 264 4.22 3.17 14.5 0 1 5 4
14 15 8 301 335 3.54 3.57 14.6 0 1 5 8
And that’s it! Hope this has been helpful!
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.