Question: Aggregate multiple rows based on common values in given columns
3
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
A\t2\t3\t4
A\t2\t3\t6
B\t1\t2\t3
C\t3\t2\t1
B\t1\t3\t4
``````

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
modified 12 months ago by zx87548.2k • written 3.9 years ago by writersblog0240
7
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)))`
6
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

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```

4
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