20

I'd like to merge a bunch of data frames together (because it seems many operations are easier if you're only dealing w/ one, but correct me if I'm wrong).

Currently I have one data frame like this:

ID, var1, var2
A,  2,    2
B,  4,    5
.
.
Z,  3,    2

Each ID is on a single row w/ several single measurements

I also have a csv file w/ repeated measurement for each ID, like:

filename = ID_B.csv

time, var4, var5
0,    1,    2
1,    4,    5
2,    1,    6
...

What I'd like is:

ID, time, va1, var2, var4, var5
...
B,  0,    4,   5,    1,    2,
B,  1,    4,   5,    4,    5,
B,  2,    4,   5,    1,    6,
...

I don't really care about the column order. The only solution I can think of is to add the ID column to each csv file then loop through them calling merge() several times. Is there a more elegant approach?

starball
  • 20,030
  • 7
  • 43
  • 238
Peter
  • 1,155
  • 8
  • 20

1 Answers1

16

My understanding is that you need to extract the ID from the filename, and then merge the imported csv with the existing dataframe.

df1 <- read.csv(textConnection("ID, var1, var2
A,  2,    2
B,  4,    5"))

# assuming the imported csv-files are in working directory
filenames <- list.files(getwd(), pattern = "ID_[A-Z].csv")

# extract ID from filename
ids <- gsub("ID_([A-Z]).csv", "\\1", filenames)

# import csv-files and append ID
library(plyr)
import <- mdply(filenames, read.csv)
import$ID <- ids[import$Var1]
import$Var1 <- NULL

# merge imported csv-files and the existing dataframe
merge(df1, import)

Result:

ID var1 var2 time var4 var5
1  B    4    5    0    1    2
2  B    4    5    1    4    5
3  B    4    5    2    1    6
learnr
  • 6,479
  • 4
  • 27
  • 23
  • Oh! I totally missed that each csv was the measurements for a single ID. Doh! Your answer is very good. – JD Long Oct 13 '09 at 19:51
  • 6
    You only need `ldply` and not `mdply`, and you can also simplify things by doing `names(filenames) <- ids`. Also you use `.` instead of `getwd()` in `list.files`. Nice answer otherwise. – hadley Oct 13 '09 at 20:33