Package: purrr

Function: map()

1. Read in all sheets of an Excel file as separate data frames.

View the sheets in our workbook

path <- (here::here("import-data", "data", "all-school_attendance.xlsx"))

path %>% 
  readxl::excel_sheets()
[1] "Sheet1" "Sheet2" "Sheet3"

There are several ways to read in each sheet as a data frame.

First you can read in one sheet at a time using readxl::read_excel and adding the argument sheet=, which allows you to call the name of the sheet OR the position.

df1 <- readxl::read_excel(path, sheet = "Sheet1")

df2 <- readxl::read_excel(path, sheet = "Sheet2")

df3 <- readxl::read_excel(path, sheet = "Sheet3")

Second, you can read grab all of the Excel sheets using readxl::excel_sheets() and then iterate our function (readxl::read_excel()) over each sheet using purrr::map() to create a list of data frames.

We can then use the $ to select each sheet from the list by name.

  • Note: Using purrr::set_names() we are creating a named list (giving each sheet a name)
data <- path %>% 
  readxl::excel_sheets() %>% 
  purrr::set_names() %>% 
  purrr::map(readxl::read_excel, path = path)

df1 <- data$Sheet1

df2 <- data$Sheet2

df3 <- data$Sheet3

But if we have many sheets, the method above can be time consuming. A better option may be to use base::list2env().

# Create our list of data frames
my_file_list <- here::here("import-data", "data", "all-school_attendance.xlsx") %>% 
  readxl::excel_sheets() %>% 
  purrr::set_names() %>% 
  purrr::map(readxl::read_excel, path = here::here("import-data", "data", "all-school_attendance.xlsx"))

# Create environment variables from our list
list2env(my_file_list, envir=.GlobalEnv)
<environment: R_GlobalEnv>
# Review all objects in environment
ls()
[1] "my_file_list" "Sheet1"       "Sheet2"       "Sheet3"

Return to Import Files