R — Cleaning/Merging Excel files

Posted on Thu 05 February 2015 in Tech

This is just a useful snippet of code I've been using a lot to tidy up messy exports I've been getting lately.

Takes in a bunch of excel files, rewrites some variable column names in column 3 then outputs them as a list of dataframes. These then get merged into a single csv file.

library(gdata)
filenames <- list.files("excels", pattern="*.xlsx", full.names=TRUE)

ldf <- lapply(filenames, function(file) {
  df = read.xls(file)
  names = names(df)
  sectionName = names[3]
  names(df)[names(df)==sectionName] <- "section"
  df
})

library(reshape)
result = reshape::merge_all(ldf)
write.csv(result, "result.csv", row.names=FALSE)