# Joining columns to a sorted file

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")

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.

• What have you tried? This is a problem you have faced and solved a few times before. Feb 26 '20 at 20:13
• Thank you, I have edited main post with what I HAVE TRIED Feb 26 '20 at 21:10

$$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. • Sorry segmentation.txt is my output from DF <- left_join(df_res1,df, by = "Start") ? Feb 27 '20 at 8:21 • 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? Feb 27 '20 at 8:49 • 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. Feb 27 '20 at 9:13 • 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 dropbox.com/s/54923xx5ds6p52d/DF.txt?dl=0 Feb 27 '20 at 9:21
• 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! Feb 27 '20 at 20:14