2

Following up from this previous question; consolidating data frames in R using lapply works perfectly to read and combine multiple csv files.

My question is, say you also want to create and append an identifier to this merged dataset based on the filename you are reading in.

For example if you have 2 datasets and they are;

file 2014_1.csv;

Var1 Var2
21   140 
2    134 
3    135

file 2014_2.csv;

Var1 Var2
21   131 
2    134

I want my final table to look like this;

Var1 Var2 Period
21   140  2014_1
2    134  2014_1
3    135  2014_1
21   131  2014_2
2    134  2014_2

Is there a way to do this?

Community
  • 1
  • 1
Gokay
  • 75
  • 5

3 Answers3

2

alright, I've figured it out. the following code takes all the csv files in a folder, attaches the file name as an id variable and combines them.

files <- list.files()
files
# read the files into a list of data.frames
data.list <- lapply(files, function(.file){
     dat<-read.csv(.file, header = F)
   dat$period<-as.character(.file)
dat
})
# concatenate into one big data.frame
data.cat <- do.call(rbind, data.list)
Gokay
  • 75
  • 5
2

Using fread from data.table (which would be faster)

 files <- list.files(pattern="\\d{4}_\\d.csv")
 library(data.table)
 library(tools)


  rbindlist(
     lapply(files, function(x) cbind(fread(x), Period=file_path_sans_ext(x)) ))
  #   Var1 Var2  Period
  #1:   21  140  2014_1
  #2:    2  134  2014_1
  #3:    3  135  2014_1
  #4:   21  131  2014_2
  #5:    2  134  2014_2

Or as suggested by @Arun

 rbindlist(lapply(files, function(x) fread(x)[,Period:=file_path_sans_ext(x)]))
akrun
  • 874,273
  • 37
  • 540
  • 662
1

based on your previous post i'd suggest something like this

data.list <- cbind(lapply(files, read.csv), files)
ben au
  • 41
  • 6
  • Add in an extra column to each with the period before the cbind. i.e. `file1$Period <- "2014_1"` and the same for the other files. – JeremyS Sep 01 '14 at 02:36
  • Thanks for replying **JeremyS**. Unfortunately I have over >30 files in each folder, so I'm looking for a faster way to do it. Also, as for **ben au**'s reply, I'm not sure what would that work. – Gokay Sep 01 '14 at 03:10
  • this post with an answer also by JeremyS http://stackoverflow.com/questions/21107006/import-all-txt-files-in-folder-concatenate-into-data-frame-use-file-names-as-v shows you how to do what you need – ben au Sep 01 '14 at 03:38