Rearrange a table
2
2
Entering edit mode
8.2 years ago
juncheng ▴ 210

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?

R • 1.5k views
ADD COMMENT
4
Entering edit mode
8.2 years ago
brentp 24k

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
ADD COMMENT
2
Entering edit mode
8.2 years ago
Pavel Senin ★ 1.9k

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
ADD COMMENT
1
Entering edit mode

thanks, seems like do the job.

ADD REPLY
0
Entering edit mode

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

ADD REPLY

Login before adding your answer.

Traffic: 906 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6