Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-27T08:21:13.667Z
Answers.Text: Sorry segmentation.txt is my output from DF <- left_join(df_res1,df, by = "Start")  ?
Answers.UserDisplayName: Zizogolu
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-27T08:49:35.293Z
Answers.Text: 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?
Answers.UserDisplayName: Alex Reynolds
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-27T09:13:32.290Z
Answers.Text: 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.
Answers.UserDisplayName: Zizogolu
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-27T09:21:52.603Z
Answers.Text: 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
Answers.UserDisplayName: Zizogolu
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-27T20:14:28.643Z
Answers.Text: 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!
Answers.UserDisplayName: Alex Reynolds
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-27T00:09:09.077Z
Answers.LastActivityDate: 2020-02-27T00:09:09.077Z
Answers.UserDisplayName: Alex Reynolds
Body: <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>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-26T20:13:57.163Z
Comments.Text: What have you tried? This is a problem you have faced and solved a few times before.
Comments.UserDisplayName: Ram RS
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-26T21:10:42.820Z
Comments.Text: Thank you, I have edited main post with what I HAVE TRIED
Comments.UserDisplayName: Zizogolu
Tags: <r><bedtools><wgs><cnv>
Title: Joining columns to a sorted file
ThreadText: # 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!  
  
  ---
  
  