How can I read a target colum from multiple CSV files into 1 file and renaming the column with the input file name into R?

Question

I have multiple CSV files with different names but all formatted exactly the same with the columns and rows in the same order and have the same labels. I would like to be able to read specific columns from each file and rename those columns by adding the filename to them into 1 file R.

For example, I would like to keep Season, reach, and weekday (which is the same for all files) and the extract Var3 and Var7 columns from each file.

File1: Season reach weekday Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8
File2: Season reach weekday Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8
File3: Season reach weekday Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8

Would be merged as:

MergedFile: Season reach weekday File1Var3 File1Var7 File2Var3 File2Var7 File3Var3 File3Var7

Is this possible?

This depends on many things. Please include a reproducible example ans some code that you've tried. — Rich Scriven, Jun 29 '14 at 22:00

score 0 · Accepted Answer · answered Jun 29 '14 at 22:08

Read them into a list while assigning names and filtering by data, with code such as this:

names <- c("File1", "File2", "File3")
d <- lapply(names, function(name) {
         df <- read.table(name, header=TRUE)

         # Remove unneeded columns
         df <- df[c('Season', 'reach', 'Var3', 'Var7')]

         # Rename columns
         varcols <- grepl('Var', names(df))
         names(df)[varcols] <- paste0(name, names(df)[varcols])
     })

Now that the columns data columns have been renamed, you can merge them together:

Reduce(merge, d)

This will match up the un-renamed columns in each data frame.

How can I read a target colum from multiple CSV files into 1 file and renaming the column with the input file name into R?

1 Answers1