Merge the same coordinate in a table?
2
1
Entering edit mode
2.7 years ago
star ▴ 280

I like to know is there any way to merge the same coordinate in a table.

Table:

chr1    155944562   155945214   fantom_neuron   GSM1554667   9.84447    
chr1    155944562   155945214   fantom_neuron   GSM1554672   7.43630    
chr1    155944562   155945214   fantom_neuron   GSM1554678   32.77627
chr1    155945743   155946196   fantom_neuron   BAMPE        3.87072    
chr1    155945743   155946196   fantom_neuron   GSM1554666  18.14939
chr1    155945746   155946202   fantom_neuron   GSM1554655  1.14939

Expected table:

chr1    155944562   155945214   fantom_neuron   GSM1554667,GSM1554672,GSM1554678     9.84447,7.43630,32.77627   
chr1    155945743   155946196   fantom_neuron   BAMPE,GSM1554666    18.14939 ,3.87072   
chr1    155945746   155946202   fantom_neuron   GSM1554655  1.14939
merge intersect bedtools genomics • 896 views
ADD COMMENT
0
Entering edit mode

Bedtools, most probably.

ADD REPLY
3
Entering edit mode
2.7 years ago

Agreeing with Wouter, check out bedtools merge:

bedtools merge -i foo.bed -c 4,5 -o collapse
ADD COMMENT
2
Entering edit mode
2.7 years ago
zx8754 10k

Using R, and data.table package:

library(data.table)

# use fread for your data
# mydata <- fread("myFile.bed")

# example data
mydata <- fread("
chr1    155944562   155945214   fantom_neuron   GSM1554667   9.84447    
chr1    155944562   155945214   fantom_neuron   GSM1554672   7.43630    
chr1    155944562   155945214   fantom_neuron   GSM1554678   32.77627
chr1    155945743   155946196   fantom_neuron   BAMPE        3.87072    
chr1    155945743   155946196   fantom_neuron   GSM1554666  18.14939
chr1    155945746   155946202   fantom_neuron   GSM1554655  1.14939
")

# then group by paste
mydata[, lapply(.SD, toString), .SDcols = c(5:6), by = list(V1, V2, V3, V4) ]
#      V1        V2        V3            V4                                 V5                        V6
# 1: chr1 155944562 155945214 fantom_neuron GSM1554667, GSM1554672, GSM1554678 9.84447, 7.4363, 32.77627
# 2: chr1 155945743 155946196 fantom_neuron                  BAMPE, GSM1554666         3.87072, 18.14939
# 3: chr1 155945746 155946202 fantom_neuron                         GSM1554655                   1.14939
ADD COMMENT
2
Entering edit mode

or data.table::foverlaps()

ADD REPLY
1
Entering edit mode

Hmm, foverlaps is when we have 2 beds to merge. If you are thinking of merging to itself, feel free to post as answer.

ADD REPLY
0
Entering edit mode

yes, you're right! Ignored that detail :)

ADD REPLY

Login before adding your answer.

Traffic: 2861 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