This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Aggregate multiple rows based on common values in given columns

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
• 43,902 views • link
updated by zx8754 zx8754 • written by writersblog02 • writersblog02

Cool. The way this website works is question and answer, incentivized by points. You should "upvote" my answer and "accept" it as solving the problem.

• 0 views • link
updated by Ram Ram • written by karl.stamm karl.stamm

I need that table, but as a common dataframe without inner lists, do you know how to do it?

• 0 views • link
written by ferroao • ferroao

3 answers

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
• 0 views • link
updated by Ram Ram • written by karl.stamm karl.stamm

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)))
• 0 views • link
updated by Ram Ram • written by Jimbou Jimbou

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

• 0 views • link
written by zx8754 zx8754

Log in to answer this question.

More posts like this