File name as legitimate column in R data frame

Question

I am combining one sheet from all XLS files in a folder into one data frame & displaying a specific range from all of them, which works fine. However, I want to add the file name as an actual column, which isn't working right now - it displays as rows without adding a column.

A screenshot may help me make more sense. You can see that the 2nd 2 columns have headers but the first does not, so if I add in functionality to export to Excel etc. that column will be missing.

dataframe

Code:

#library
library(readxl)
library(plyr)

#define path
# setwd
my_path <- file.path("C:", "File", "Path")
setwd(my_path)

# list all files in the directory
data.files = list.files()

# list all files in the directory ending with .xls
wb <- list.files(pattern = "*.xls")

# create an empty list
dflist <- list()

# populate dflist with wb
for (i in wb){
  dflist[[i]] = data.frame(read_excel(i, sheet = "Sheet1", range = "C15:D16", col_names = FALSE, row.names(data.files)))
}

#create final data frame, bind dflist
OBJDList = do.call(what = rbind, args = dflist)

Why don't you just make the `row.names` a column? https://stackoverflow.com/questions/29511215/convert-row-names-into-first-column — M--, Apr 03 '18 at 17:57
r2evans - your solution adds the file name as a column, but it gives me Error: Illegal Column Type after the first entry is added and stops the script. I tried col_types = NULL & col_types = "text", but those additions both get me back to my original issue. Seems like it's close though, so maybe I'm doing something wrong - does it need to be placed in a specific place within the loop? — Gib999, Apr 03 '18 at 19:26

Gib999 · Accepted Answer · 2018-04-05T18:47:46.393

I got it to work. The file name is the 1st column, and I reduced the range to one cell because everything else I need is in the file name itself.

#library
library(readxl)
library(plyr)
library(xlsx)
library(data.table)

#define path
my_path <- file.path("G:", "your", "path")
setwd(my_path)

# list all files in the directory
data.files = list.files()

# list all files in the directory ending with .xls
wb <- list.files(pattern = "*.xls")

# create an empty list
dflist <- list()

# populate dflist with wb
for (i in wb){
  dflist[[i]] = data.frame(read_excel(i, sheet = "sheet1", range = "D16", col_names = FALSE, row.names(data.files)))
}



#create final data frame, bind dflist
OBJDList = do.call(what = rbind, args = dflist)

setDT(OBJDList, keep.rownames = TRUE)[]

OBJDList

print(OBJDList)

write.xlsx(OBJDList, file = "G:/your path/yourfile.xlsx",
           sheetName = "Sheet1", append = FALSE)

File name as legitimate column in R data frame

1 Answers1