Question: Aggregate multiple rows based on common values in given columns
4
gravatar for writersblog02
4.7 years ago by
United States
writersblog0250 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 • 37k views
ADD COMMENTlink modified 22 months ago by zx87549.4k • written 4.7 years ago by writersblog0250
7
gravatar for karl.stamm
4.7 years ago by
karl.stamm3.7k
United States
karl.stamm3.7k 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 8 months ago by RamRS28k • written 4.7 years ago by karl.stamm3.7k
7
gravatar for Jimbou
4.7 years ago by
Jimbou770
Germany
Jimbou770 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 modified 8 months ago by RamRS28k • written 4.7 years ago by Jimbou770
4
gravatar for zx8754
4.7 years ago by
zx87549.4k
London
zx87549.4k 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 4.7 years ago • written 4.7 years ago by zx87549.4k
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: 1315 users visited in the last hour