How do I combine all sheets in an excel file that begin with a specific letter?-CodePudding

I have an excel file with 20 tabs. I want to import and combine all the tabs into one dataframe but only the tabs that begin with a specific series of letters. The relevant tabs all start with "AB" then have a series of numbers after. I want to combine all the tabs that start with "AB"

CodePudding user response：

You can do this combing lapply with grep and importing using the readxl package:

library(readxl)

# Since no data were given, using embedded sample data
xlsx_example <- readxl_example("datasets.xlsx") # path to sample data
  # [1] "/Library/Frameworks/R.framework/Versions/4.1/Resources/library/readxl/extdata/datasets.xlsx"

# get all the sheets:
allsheets <- excel_sheets(xlsx_example)

  # [1] "iris"     "mtcars"   "chickwts" "quakes" 

# grep with only the pattern you want 
# here, I will select sheet names with an "i" in it

sheet_list <- lapply(allsheets[grep("i",allsheets)], function(x) read_excel(xlsx_example, sheet = x))

This gives you a list with your selected sheets. If you want each element in that that list to the global environment, do;

names(sheet_list) <- allsheets[grep("i",allsheets)]

list2env(sheet_list, .GlobalEnv)

You could alternatively use a for loop to assign them directly to the global environment:

for(xx in grep("i",allsheets)){
  assign(allsheets[xx], read_excel(xlsx_example, sheet = allsheets[xx]))
}