Question: Aggregate multiple rows based on common values in given columns
3
gravatar for writersblog02
3.9 years ago by
United States
writersblog0240 wrote:

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.

R • 32k views
ADD COMMENTlink modified 12 months ago by zx87548.2k • written 3.9 years ago by writersblog0240
7
gravatar for Jimbou
3.9 years ago by
Jimbou690
Germany
Jimbou690 wrote:

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)))
ADD COMMENTlink written 3.9 years ago by Jimbou690
6
gravatar for karl.stamm
3.9 years ago by
karl.stamm3.5k
United States
karl.stamm3.5k wrote:

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

 

ADD COMMENTlink modified 3.9 years ago • written 3.9 years ago by karl.stamm3.5k
4
gravatar for zx8754
3.9 years ago by
zx87548.2k
London
zx87548.2k wrote:

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

ADD COMMENTlink modified 3.9 years ago • written 3.9 years ago by zx87548.2k
Please log in to add an answer.

Help
Access

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.
Powered by Biostar version 2.3.0
Traffic: 882 users visited in the last hour