Importing and Renaming multiple sheets in a excel worksbook with map_df and read_excel

Question

I have an excel workbook that has multiple sheets in it that correspond to an individual subject. All the columns in each of the sheets are formatted with the same headers and all the data in numerical text. I was able to import all the sheets together into a master sheet that combined all the rows together under the same columns with the following code:

test <- data %>% excel_sheets() %>% map_df(read_excel, path=data)

Any ideas on how I could have it so that for each sheet imported, a column is added that includes the name of the sheet.

Example: I have a workbook with sheets a, b, and c that each have columns data 1, data2, data3, can I import and combine them into 1 data frame that now have a column 4 which corresponds to the sheet name, a, b, or c?

I think making reproducible examples can be a little difficult when discussing reading in files. However, make sure you include the packages you are using and make it clear what objects like data are (in this case I'm guessing it is a file path). That will help folks help you. :) You can see some good info on how to make a reproducible example at this link. See similar, R-specific info here. — aosmith

aosmith aosmith · Accepted Answer · 2019-11-08T17:22:59

Since excel_sheets() returns a character vector, you could add these character values in a new column to each dataset as you read it in. This involves using a more complex function within map_dfr(), since you now want to do 2 tasks: 1., read the dataset in with read_excel() and 2., add a column to it containing the sheet name.

If path is your path the an Excel document and you name the new column name, the code could look something like:

library(readxl)
library(purrr)
library(dplyr)

path = "Path_to_excel_file.xlsx"
path %>% 
     excel_sheets() %>%
     map_dfr(
          function(sheet) {
               dat = read_excel(sheet, path = path)
               mutate(dat, name = sheet)
          }
     )

You can see I used an anonymous function instead of the tilde code. I often find it easier to switch to using these when I'm doing several steps within one map loop, but that is absolutely personal preference.

I added the new column with mutate(), but this can be done in other ways, as well.

Importing and Renaming multiple sheets in a excel worksbook with map_df and read_excel

1 Answers