Rearrange a table

Question

[juncheng]

This is a basic question. I want to rearrange a table like this:

A    1
B    2
B    3
B    4
C    5
C    6

to:

A    1
B    2,3,4
C    5,6

Could anyone gives a hint. Either with R or python?

Answer 1

[brentp]

How about with bedtools:

$ cat t.txt
A    1
B    2
B    3
B    4
C    5
C    6

$ bedtools groupby -i t.txt -g 1 -c 2 -o collapse
A    1
B    2,3,4
C    5,6

Answer 2

[Pavel Senin]

You can try something like this:

require(plyr)

dat = read.csv(textConnection(
"A,1
B,2
B,3
B,4
C,5
C,6"),header=F)

print_V2=function(df){
  tmp=c(as.character(df[1,1]),rep(NA,9))
  tmp[2:(length(df[,2])+1)]=df[,2]
  tmp
}

ddply(dat,.(V1),print_V2)

> ddply(dat,.(V1),print_V2)
  V1 V2   V3   V4   V5   V6   V7   V8   V9  V10
1  A  1 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
2  B  2    3    4 <NA> <NA> <NA> <NA> <NA> <NA>
3  C  5    6 <NA> <NA> <NA> <NA> <NA> <NA> <NA>

Yes, you can collapse that too, as brentp suggested:

print_V2=function(df){
  c(as.character(df[1,1]),paste(df$V2, collapse=", "))
}

ddply(dat,.(V1),print_V2)

> ddply(dat,.(V1),print_V2)
  V1      V2
1  A       1
2  B 2, 3, 4
3  C    5, 6

[juncheng]

thanks, seems like do the job.

[Pavel Senin]

Sorry, was thinking that you'd need that for some further processing, so I kept values in cells -- see the updated solution.