Aggregate multiple rows based on common values in given columns

Question

[writersblog02]

I have a table:

  W X Y Z
A 2 3 4
A 2 3 6
B 1 2 3
C 3 2 1
B 1 3 4
B 1 2 2


# reproducible data
table <- read.table(textConnection("W\tX\tY\tZ
                                   A\t2\t3\t4
                                   A\t2\t3\t6
                                   B\t1\t2\t3
                                   C\t3\t2\t1
                                   B\t1\t3\t4
                                   B\t1\t2\t2"), header = TRUE)

I want to combine all rows with same value in Column W, so that it looks like

W   X  Y     Z
A   2  3     2,6
B   1  2,3   3,4,2
C   3  2     1

Note: All different values of column W entries appear as comma separated.

Answer 1

[karl.stamm]

You're looking for the R package 'plyr'

 X1 <- read.table(textConnection("W\tX\tY\tZ
 A\t2\t3\t4
 A\t2\t3\t6
 B\t1\t2\t3 
 C\t3\t2\t1
 B\t1\t3\t4
 B\t1\t2\t2"),header=T)

 library(plyr)

 X2 <- ddply(X1, .(W), summarize,
        Xc=paste(X,collapse=","), 
        Zc=paste(Z,collapse=",") ,
        Yc= paste(Y,collapse=","))


 X2

  W    Xc    Zc    Yc
1 A   2,2   4,6   3,3
2 B 1,1,1 3,4,2 2,3,2
3 C     3     1     2


X2u <- ddply(X1, .(W), summarize,
   Xc=paste(unique(X),collapse=","), 
   Zc=paste(unique(Z),collapse=",") ,
   Yc= paste(unique(Y),collapse=","))

X2u
  W Xc    Zc  Yc
1 A  2   4,6   3
2 B  1 3,4,2 2,3
3 C  3     1   2

Answer 2

[Jimbou]

You can also use the aggregate function in combination with the unique function

aggregate(table[,2:4], list(table[,1]), function(x) paste0(unique(x)))

Answer 3

[zx8754]

Maybe aggregate( .~ W, df, function(x) toString(unique(x))) - also pure R questions are more suitable for http://stackoverflow.com/questions/tagged/r