CODEWITHSUNDEEP
r
Madhu Sareen
Madhu Sareen

Reputation: 549

Reading each sheet of excel workbook in separate dataframes and rename the columns by prefixing the sheet names before them

I am trying to read sheets from an excel workbook. There are 10 sheets in that workbook and all have different names.

What I want to do:

  1. Read workbook's first sheet's name (suppose the name is "My Sheet 1")
  2. Read the data into a data.frame whose name should be My_Sheet_1
  3. Change the column names such as new column should have "My_Sheet_1_" as prefix such that if the name of column in sheet is "My Column 1" then in R's data.frame it should be renamed to "My_Sheet_1_My_Column_1"... and so forth.

I tried using:

read_allsheets_from_excel <- function(filename) {
  sheets   <- excel_sheets(filename)
  x        <- lapply(sheets, function(X) read_excel(filename, sheet = X))
  names(x) <- sheets
  x
}
# read now
read_allsheets_from_excel("DATA.XLSX")

Also tried:

My_Column_1 <- read.xlsx("DATA.XLSX", sheetIndex = 1)

colnames(My_Column_1) <- colnames(My_Column_1, do.NULL = TRUE, prefix = "My_Sheet_1")

But none is working. Neither it is a good way to automate across all the 10 different sheets. How can it be improved?

Upvotes: 2

Views: 509

Answers (1)

thothal
thothal

Reputation: 20329

You were almost there. What you need to do is to rename the columns in your loop and to use assign to create the variable in the global environment

library(readxl)

datasets <- readxl_example("datasets.xlsx")

read_allsheets_from_excel <- function(filename) {
   sheets <- excel_sheets(filename)
   setNames(lapply(sheets, function(.) {
      tbl <- read_excel(filename, sheet = .)
      names(tbl) <- paste(., names(tbl), sep = "_")
      tbl
   }), sheets)
}

# read now
allSheets <- read_allsheets_from_excel(datasets)

lapply(names(allSheets), function(nam) 
    assign(nam, allSheets[[nam]], envir = .GlobalEnv))
ls()
# [1] "allSheets"                 "chickwts"                 
# [3] "datasets"                  "iris"                     
# [5] "mtcars"                    "quakes"                   
# [7] "read_allsheets_from_excel"

Upvotes: 1

Related Questions