Question: Aggregate multiple rows based on common values in given columns
3
gravatar for writersblog02
3.3 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 • 27k views
ADD COMMENTlink modified 5 months ago by zx87546.8k • written 3.3 years ago by writersblog0240
7
gravatar for Jimbou
3.3 years ago by
Jimbou670
Germany
Jimbou670 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.3 years ago by Jimbou670
6
gravatar for karl.stamm
3.3 years ago by
karl.stamm3.4k
United States
karl.stamm3.4k 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.3 years ago • written 3.3 years ago by karl.stamm3.4k
4
gravatar for zx8754
3.3 years ago by
zx87546.8k
London
zx87546.8k 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.3 years ago • written 3.3 years ago by zx87546.8k
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: 2243 users visited in the last hour