Dataframes are created but column names are not changing when reading from excel workbook

Question

I am trying to read an excel workbook in R and for each sheet will create a dataframe.

In the next step, i want to read that created dataframe and use sheet name along with under score before each of the column in the respective dataframe.

Here is what I am doing:

library(readxl)

# Store Sheet Names in a vector
sheet <- excel_sheets("D:/OTC/JULY DATA.XLSX")

# Trim any of the Trailing White Spaces
sheet_trim_trailing <- function (x) sub("\\s+$", "", x)
sheet <- sheet_trim_trailing(sheet)

# Read each of the sheets in the workbook and create a 
# dataframe using respective names of the sheets

for(i in 1:length(sheet)){
  # this read a sheet and create the dataframe using its name
  assign(sheet[i], read.xlsx("DATA.XLSX", sheetIndex = i))
  # store dataframe name into a vector
  sname <- sheet[i]
  # use vector to change the col names in the respective dataframe
  colnames(sname) <- gsub("^", paste0(sname,"_"), colnames(sname))
}

Dataframes are created but column names are not changing?

I dont know where I am wrong?

Answer 1

I think I was looking for something like this one, which does the same as above.

Try This Alternative:

library(readxl)

# function to read all the sheets from excel workbook
read_all_sheets <- function(xlsfile) {
  sheets <- excel_sheets(xlsfile)
  setNames(lapply(sheets, function(.) {
    tbl <- read_excel(xlsfile, sheet = .)
    # this will change the col names with sheet name 
    #  and underscore as prefix
    names(tbl) <- paste(., names(tbl), sep = "_")
    tbl
  }), sheets)
}

## create dataframes from sheets
# first read all the sheets are list
List_of_All_Sheets <- read_all_sheets("Location/of/the/file.xlsx")
# then create dataframes
lapply(names(List_of_All_Sheets), 
       function(nams) assign(nams, List_of_All_Sheets[[nams]], 
                             envir = .GlobalEnv))

Answer 2

What you need to do is something like

colnames(get(sheet[i])) <- gsub("^", paste0(sname,"_"), colnames(get(sheet[i])))

But this will give an error

target of assignment expands to non-language object

A workaround is to use a temporary variable to change column names

Reproducible example

temp <- mtcars[1:5,]
d <- get("temp")
colnames(d) <- sub("y", " ", colnames(d))
assign("temp", d)

Try this

for(i in 1:length(sheet)){
  assign(sheet[i], read.xlsx("DATA.XLSX", sheetIndex = i))
  t <- get(sheet[i])
  colnames(t) <- gsub("^", paste0(sheet[i],"_"), colnames(t))
  assign(sheet[i], t)
}