Menu
How do I succinctly import selected sheets, preferably using readxl, from a .xlsx-workbook?
With the below code (approach # 1) I can import all sheets in a single .xlsx-workbook, but how do I filter() or select() ? The second bit of code, with the map_dfr(), approach # 2, is kinda more succinct, but using that approach the sheet vector looses it's names and becomes a 1, 2, ctc.
Say I only want to import the sheets iris and mtcars
sh_to_impt <- c('iris', 'mtcars')
packages needed,
library(readxl)
library(tidyverse)
library(purrr)
path <- readxl_example("datasets.xlsx")
datasets_data <- readxl::excel_sheets(path = path) %>%
purrr::set_names() %>% select(mtcars) %>%
purrr::map_dfr(
~ readxl::read_excel(path = path, sheet = .x)
, .id = "sheet"
)
datasets_data
# A tibble: 1,253 x 24
sheet Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<chr> <dbl> <dbl> <dbl> <dbl> <chr>
1 iris 5.1 3.5 1.4 0.2 setosa
2 iris 4.9 3 1.4 0.2 setosa
3 iris 4.7 3.2 1.3 0.2 setosa
4 iris 4.6 3.1 1.5 0.2 setosa
5 iris 5 3.6 1.4 0.2 setosa
6 iris 5.4 3.9 1.7 0.4 setosa
7 iris 4.6 3.4 1.4 0.3 setosa
8 iris 5 3.4 1.5 0.2 setosa
9 iris 4.4 2.9 1.4 0.2 setosa
10 iris 4.9 3.1 1.5 0.1 setosa
# ... with 1,243 more rows, and 18 more variables: mpg <dbl>,
# cyl <dbl>, disp <dbl>, hp <dbl>, drat <dbl>, wt <dbl>,
# qsec <dbl>, vs <dbl>, am <dbl>, gear <dbl>, carb <dbl>,
# weight <dbl>, feed <chr>, lat <dbl>, long <dbl>, depth <dbl>,
# mag <dbl>, stations <dbl>
I can get around it like this, BUT then the sheet vector looses it's names and becomes a 1, 2, ctc.
map_dfr(sh_to_impt, ~ read_excel(path, sheet = .x), .id = "sheet")
# A tibble: 182 x 17
sheet Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<chr> <dbl> <dbl> <dbl> <dbl> <chr>
1 1 5.1 3.5 1.4 0.2 setosa
2 1 4.9 3 1.4 0.2 setosa
3 1 4.7 3.2 1.3 0.2 setosa
4 1 4.6 3.1 1.5 0.2 setosa
5 1 5 3.6 1.4 0.2 setosa
6 1 5.4 3.9 1.7 0.4 setosa
7 1 4.6 3.4 1.4 0.3 setosa
8 1 5 3.4 1.5 0.2 setosa
9 1 4.4 2.9 1.4 0.2 setosa
10 1 4.9 3.1 1.5 0.1 setosa
# ... with 172 more rows, and 11 more variables: mpg <dbl>,
# cyl <dbl>, disp <dbl>, hp <dbl>, drat <dbl>, wt <dbl>,
# qsec <dbl>, vs <dbl>, am <dbl>, gear <dbl>, carb <dbl>
I've looked at this answer, thinking it might hold the key.
I’m looking for a succinct solution. A, to me, obvious not so succinct solution could be,
map_dfr(sh_to_impt, ~ read_excel(path, sheet = .x), .id = "sheet") %>%
mutate(sheet = recode(sheet, `1` = sh_to_impt[1], `2` = sh_to_impt[2]))
# A tibble: 182 x 17
sheet Sepal.Length Sepal.Width Petal.Length Petal.Width Species mpg
<chr> <dbl> <dbl> <dbl> <dbl> <chr> <dbl>
1 iris 5.1 3.5 1.4 0.2 setosa NA
2 iris 4.9 3 1.4 0.2 setosa NA
3 iris 4.7 3.2 1.3 0.2 setosa NA
4 iris 4.6 3.1 1.5 0.2 setosa NA
5 iris 5 3.6 1.4 0.2 setosa NA
6 iris 5.4 3.9 1.7 0.4 setosa NA
7 iris 4.6 3.4 1.4 0.3 setosa NA
8 iris 5 3.4 1.5 0.2 setosa NA
9 iris 4.4 2.9 1.4 0.2 setosa NA
10 iris 4.9 3.1 1.5 0.1 setosa NA
# ... with 172 more rows, and 10 more variables: cyl <dbl>, disp <dbl>,
# hp <dbl>, drat <dbl>, wt <dbl>, qsec <dbl>, vs <dbl>, am <dbl>,
# gear <dbl>, carb <dbl>
589
A "tidy" way to represent an Excel workbook in R is as a nested data frame, e.g.:
# A tibble: 2 x 2
sheet data
<chr> <list>
1 iris <tibble [150 × 5]>
2 mtcars <tibble [32 × 11]>
So I would simplify your first approach by storing the sheet name in a column, reading the data as an additional column, and then unnesting it:
library("readxl")
library("dplyr")
library("purrr")
library("tidyr")
path <- readxl_example("datasets.xlsx")
sh_to_impt <- c("iris", "mtcars")
tibble(sheet = sh_to_impt) %>%
mutate(data = map(sheet, ~read_xlsx(path, .))) %>%
unnest(data)
#> # A tibble: 182 x 17
#> sheet Sepal.Length Sepal.Width Petal.Length Petal.Width Species mpg cyl
#> <chr> <dbl> <dbl> <dbl> <dbl> <chr> <dbl> <dbl>
#> 1 iris 5.1 3.5 1.4 0.2 setosa NA NA
#> 2 iris 4.9 3 1.4 0.2 setosa NA NA
#> 3 iris 4.7 3.2 1.3 0.2 setosa NA NA
#> 4 iris 4.6 3.1 1.5 0.2 setosa NA NA
#> 5 iris 5 3.6 1.4 0.2 setosa NA NA
#> 6 iris 5.4 3.9 1.7 0.4 setosa NA NA
#> 7 iris 4.6 3.4 1.4 0.3 setosa NA NA
#> 8 iris 5 3.4 1.5 0.2 setosa NA NA
#> 9 iris 4.4 2.9 1.4 0.2 setosa NA NA
#> 10 iris 4.9 3.1 1.5 0.1 setosa NA NA
#> # … with 172 more rows, and 9 more variables: disp <dbl>, hp <dbl>, drat <dbl>,
#> # wt <dbl>, qsec <dbl>, vs <dbl>, am <dbl>, gear <dbl>, carb <dbl>
If you didn't know the sheets you wanted beforehand, or wanted different subsets for different analyses, you could also import all of them and filter before unnesting:
tibble(sheet = excel_sheets(path)) %>%
mutate(data = map(sheet, ~read_xlsx(path, .))) %>%
filter(sheet %in% sh_to_impt) %>%
unnest(data)
#> # A tibble: 182 x 17
#> sheet Sepal.Length Sepal.Width Petal.Length Petal.Width Species mpg cyl
#> <chr> <dbl> <dbl> <dbl> <dbl> <chr> <dbl> <dbl>
#> 1 iris 5.1 3.5 1.4 0.2 setosa NA NA
#> 2 iris 4.9 3 1.4 0.2 setosa NA NA
#> 3 iris 4.7 3.2 1.3 0.2 setosa NA NA
#> 4 iris 4.6 3.1 1.5 0.2 setosa NA NA
#> 5 iris 5 3.6 1.4 0.2 setosa NA NA
#> 6 iris 5.4 3.9 1.7 0.4 setosa NA NA
#> 7 iris 4.6 3.4 1.4 0.3 setosa NA NA
#> 8 iris 5 3.4 1.5 0.2 setosa NA NA
#> 9 iris 4.4 2.9 1.4 0.2 setosa NA NA
#> 10 iris 4.9 3.1 1.5 0.1 setosa NA NA
#> # … with 172 more rows, and 9 more variables: disp <dbl>, hp <dbl>, drat <dbl>,
#> # wt <dbl>, qsec <dbl>, vs <dbl>, am <dbl>, gear <dbl>, carb <dbl>
Here's the solution I worked out. Please flee free to improve or criticizs,
sh_to_impt <- c('iris', 'mtcars')
path <- readxl_example("datasets.xlsx")
path %>%
excel_sheets() %>%
set_names() %>% .[sh_to_impt] %>%
map_df(read_excel,
path = path,
.id = "sheet")
# A tibble: 182 x 17
sheet Sepal.Length Sepal.Width Petal.Length Petal.Width Species mpg
<chr> <dbl> <dbl> <dbl> <dbl> <chr> <dbl>
1 iris 5.1 3.5 1.4 0.2 setosa NA
2 iris 4.9 3 1.4 0.2 setosa NA
3 iris 4.7 3.2 1.3 0.2 setosa NA
4 iris 4.6 3.1 1.5 0.2 setosa NA
5 iris 5 3.6 1.4 0.2 setosa NA
6 iris 5.4 3.9 1.7 0.4 setosa NA
7 iris 4.6 3.4 1.4 0.3 setosa NA
8 iris 5 3.4 1.5 0.2 setosa NA
9 iris 4.4 2.9 1.4 0.2 setosa NA
10 iris 4.9 3.1 1.5 0.1 setosa NA
# ... with 172 more rows, and 10 more variables: cyl <dbl>, disp <dbl>,
# hp <dbl>, drat <dbl>, wt <dbl>, qsec <dbl>, vs <dbl>, am <dbl>,
# gear <dbl>, carb <dbl>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With