<p>Rearrange your text file into a sorted BED file:</p>

<pre><code><span class="math-container">$ tail -n+2 segmentation.txt | awk -vFS="\t" -vOFS="\t" '{ print $2, $3, $4, $</span>1 }' | sort-bed - &gt; segmentation.bed
</code></pre>

<p>Use BEDOPS <code>bedmap</code> with <code>--echo-map-id-uniq</code> and <code>--count</code> operators on merged regions:</p>

<pre><code>$ bedmap --delim '\t' --echo --count --echo-map-id-uniq --fraction-map 1 &lt;(bedops --merge segmentation.bed) segmentation.bed &gt; answer.bed
</code></pre>

<p>The file <code>answer.bed</code> will contain merged intervals from <code>segmentation.bed</code> in the first three columns, the number of intervals from <code>segmentation.bed</code> which are contained entirely within the merged interval in the fourth column, and a unique listing of IDs of those contained intervals in the fifth column.</p>

CC BY-SA 4.0
2020-02-27T08:21:13.667Z
Sorry segmentation.txt is my output from DF <- left_join(df_res1,df, by = "Start")  ?
Zizogolu
CC BY-SA 4.0
2020-02-27T08:49:35.293Z
Sorry, you said you were using command-line tools, so I assumed that was the case here. Can you use `write.table` or similar to write data out of R to a text file?
Alex Reynolds
CC BY-SA 4.0
2020-02-27T09:13:32.290Z
I have already written DF dataframe drown by DF <- left_join(df_res1,df, by = "Start") as a txt file and uploaded that in where terminal can find that.
Zizogolu
CC BY-SA 4.0
2020-02-27T09:21:52.603Z
Sorry says [fi1d18@cyan01 examplefiles]$ tail -n+2 segmentation.txt | awk -vFS="\t" -vOFS="\t" '{ print $2, $3, $4, $1 }' | sort-bed - > segmentation.bed
Non-numeric start coordinate.  See line 312 in -.
(remember that chromosome names should not contain spaces.)
[fi1d18@cyan01 examplefiles]$    This is the link of my whole DF file  https://www.dropbox.com/s/54923xx5ds6p52d/DF.txt?dl=0
Zizogolu
CC BY-SA 4.0
2020-02-27T20:14:28.643Z
Maybe start with exporting data before the left join step, i.e., the data you posted at the top of your question? While I can make an informed guess, I'm not 100% sure what `left_join` does in this case, and I probably don't have time to dig into R and debug its syntactic idiosyncrasies to solve this there. I do think the command-line approach that I suggested will help you solve your problem, as described, however. Good luck and I hope this helps!
Alex Reynolds
CC BY-SA 4.0
2020-02-27T00:09:09.077Z
2020-02-27T00:09:09.077Z
Alex Reynolds
<p>I have had a segmentation file (copy number) like below</p>

<pre><code>  Sample      Chromosome  Start      End          Segment_Mean
LP6008334-DNA_C02   1     10583      62271      -0.992452416
LP6008334-DNA_C02   1   17201069    17230953    -0.992452416
LP6008334-DNA_C02   1   17235034    17235196     0.592510084
LP6008334-DNA_C02   1   142781738   142967188   -0.992452416
</code></pre>

<p>I was using bedtools to count the number of markers in above genomic range (Start, End) when I got as below</p>

<pre><code>1   10583   62271   6
1   10583   62271   6
1   10583   750153  75
1   14907   1062025 106
1   121009  245455  13
1   404187  5719467 532
1   732809  5726842 500
1   821926  1120377 31
1   952428  1179983 23
1   1181751 1469134 29
1   3458681 143542402   14010
1   5726844 5736229 2
1   5735836 16832446    1111
1   5736682 8081290 236
1   8353020 8476455 13
</code></pre>

<p>Basically the same files but the second one has extra last column for the number of marker but sorted one I then I can not matched this with the above sample names and Segment_Mean</p>

<p>How I can add the corresponding sample names and Segment_Mean to my second file?</p>

<p>I have tried this where I am getting this mess</p>

<pre><code>DF &lt;- left_join(df_res1,df, by = "Start")

&gt; head(DF)
           Sample.x Chromosome.x     Start     End.x Num_Probes          Sample.y Chromosome.y     End.y
1 LP6008334-DNA_C02            1     10583     62271          9 LP6008334-DNA_C02            1     62271
2 LP6008334-DNA_C02            1     10583     62271          9 LP6008337-DNA_A07            1     62271
3 LP6008334-DNA_C02            1     10583     62271          9 LP6008460-DNA_F02            1    750153
4 LP6008334-DNA_C02            1  17201069  17230953         20 LP6008334-DNA_C02            1  17230953
5 LP6008334-DNA_C02            1  17235034  17235196          1 LP6008334-DNA_C02            1  17235196
6 LP6008334-DNA_C02            1 142781738 142967188         40 LP6008334-DNA_C02            1 142967188
&gt; 
</code></pre>

<p>Also with this I have some overlaps like</p>

<pre><code>300 segment overlaps detected in file '/temp/hgig/fi1d18/man/examplefiles/res1.txt'.
</code></pre>

<p>First overlap detected between segments at lines 1 and 9.</p>

CC BY-SA 4.0
2020-02-26T20:13:57.163Z
What have you tried? This is a problem you have faced and solved a few times before.
Ram RS
CC BY-SA 4.0
2020-02-26T21:10:42.820Z
Thank you, I have edited main post with what I HAVE TRIED
Zizogolu
<r><bedtools><wgs><cnv>
Joining columns to a sorted file
# Joining columns to a sorted file

(Asked by: username_0 on 2020-02-26)

I have had a segmentation file (copy number) like below
  Sample      Chromosome  Start      End          Segment_Mean
LP6008334-DNA_C02   1     10583      62271      -0.992452416
LP6008334-DNA_C02   1   17201069    17230953    -0.992452416
LP6008334-DNA_C02   1   17235034    17235196     0.592510084
LP6008334-DNA_C02   1   142781738   142967188   -0.992452416

I was using bedtools to count the number of markers in above genomic range (Start, End) when I got as below
1   10583   62271   6
1   10583   62271   6
1   10583   750153  75
1   14907   1062025 106
1   121009  245455  13
1   404187  5719467 532
1   732809  5726842 500
1   821926  1120377 31
1   952428  1179983 23
1   1181751 1469134 29
1   3458681 143542402   14010
1   5726844 5736229 2
1   5735836 16832446    1111
1   5736682 8081290 236
1   8353020 8476455 13

Basically the same files but the second one has extra last column for the number of marker but sorted one I then I can not matched this with the above sample names and Segment_Mean
How I can add the corresponding sample names and Segment_Mean to my second file?
I have tried this where I am getting this mess
DF <- left_join(df_res1,df, by = "Start")

> head(DF)
           Sample.x Chromosome.x     Start     End.x Num_Probes          Sample.y Chromosome.y     End.y
1 LP6008334-DNA_C02            1     10583     62271          9 LP6008334-DNA_C02            1     62271
2 LP6008334-DNA_C02            1     10583     62271          9 LP6008337-DNA_A07            1     62271
3 LP6008334-DNA_C02            1     10583     62271          9 LP6008460-DNA_F02            1    750153
4 LP6008334-DNA_C02            1  17201069  17230953         20 LP6008334-DNA_C02            1  17230953
5 LP6008334-DNA_C02            1  17235034  17235196          1 LP6008334-DNA_C02            1  17235196
6 LP6008334-DNA_C02            1 142781738 142967188         40 LP6008334-DNA_C02            1 142967188
> 

Also with this I have some overlaps like
300 segment overlaps detected in file '/temp/hgig/fi1d18/man/examplefiles/res1.txt'.

First overlap detected between segments at lines 1 and 9.

**Tags:** r, bedtools, wgs, cnv
> **Comments:**
> **username_1**: What have you tried? This is a problem you have faced and solved a few times before.  
> **username_0**: Thank you, I have edited main post with what I HAVE TRIED  

---

## 1 answers

### Answer by username_2 on 2020-02-27. Score: 2

Rearrange your text file into a sorted BED file:
$ tail -n+2 segmentation.txt | awk -vFS="\t" -vOFS="\t" '{ print $2, $3, $4, $1 }' | sort-bed - > segmentation.bed

Use BEDOPS bedmap with --echo-map-id-uniq and --count operators on merged regions:
$ bedmap --delim '\t' --echo --count --echo-map-id-uniq --fraction-map 1 <(bedops --merge segmentation.bed) segmentation.bed > answer.bed

The file answer.bed will contain merged intervals from segmentation.bed in the first three columns, the number of intervals from segmentation.bed which are contained entirely within the merged interval in the fourth column, and a unique listing of IDs of those contained intervals in the fifth column.

> **Comments:**
> **username_0**: Sorry segmentation.txt is my output from DF <- left_join(df_res1,df, by = "Start")  ?  
> **username_2**: Sorry, you said you were using command-line tools, so I assumed that was the case here. Can you use `write.table` or similar to write data out of R to a text file?  
> **username_0**: I have already written DF dataframe drown by DF <- left_join(df_res1,df, by = "Start") as a txt file and uploaded that in where terminal can find that.  
> **username_0**: Sorry says [fi1d18@cyan01 examplefiles]$ tail -n+2 segmentation.txt | awk -vFS="\t" -vOFS="\t" '{ print $2, $3, $4, $1 }' | sort-bed - > segmentation.bed Non-numeric start coordinate.  See line 312 in -. (remember that chromosome names should not contain spaces.) [fi1d18@cyan01 examplefiles]$    This is the link of my whole DF file  https://www.dropbox.com/s/54923xx5ds6p52d/DF.txt?dl=0  
> **username_2**: Maybe start with exporting data before the left join step, i.e., the data you posted at the top of your question? While I can make an informed guess, I'm not 100% sure what `left_join` does in this case, and I probably don't have time to dig into R and debug its syntactic idiosyncrasies to solve this there. I do think the command-line approach that I suggested will help you solve your problem, as described, however. Good luck and I hope this helps!  

---

