CODEWITHSUNDEEP
rexceldataframeimport
Lady Infante
Lady Infante

Reputation: 47

how can I add the sheet name to my data frame as a variable

I have different sheets from excel file, I would like to import every sheet to R and to add the sheet name in every data frame as a variable, because then I want to make one data frame but I need to know what sheet they are from?

library(readxl)    
read_excel_allsheets <- function("datafile", tibble = false) {
sheets <- readxl::excel_sheets("datafile")
x <- lapply(sheets, function(X) readxl::read_excel("datafile", sheet = X))
if(!tibble) x <- lapply(x, as.data.frame)
names(x) <- sheets
x
}
#Integra las hojas en una sola
datos = x[[1]]
for(i in 2:3){
datoscompletos = rbind.data.frame(datos,x[[i]])
datos = datoscompletos
}

Upvotes: 1

Views: 2144

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388982

You could add a column of sheet name in lapply : 

sheets <- readxl::excel_sheets("datafile")

all_data <- do.call(rbind, lapply(sheets, function(X) 
        transform(readxl::read_excel("datafile", sheet = X), sheetname = X)))

You can do the same in tidyverse

purrr::map_df(sheets, ~dplyr::mutate(readxl::read_excel("datafile", sheet = .x), 
                       sheetname = .x))

Upvotes: 5

Related Questions