how make a data table by merging three data sets?

Question

[star]

I have three tables contain variation, I would like to merge all three make together and have a table as below. I would like to consider A coordinate (chr and start) as the base of merging and if there are the same coordinate in B and C data put them beside A coordinate otherwise put '.' value instead.

A data:

chr1    19004   A   G
chr1    19858   C   T
chr1    46633   T   A
chr1    28563   A   G
chr1    66974   TG  T

B data:

chr1    19322   C   T
chr1    19858   C   T
chr1    63527   T   C
chr1    66974   TG  T

C data:

chr1    19004   T   G
chr1    46633   T   A
chr1    64548   A   C
chr1    66974   TG  T

Desired output:

A.chr A.start  A.1  A.2    B.chr  B.start  B.1  B.2   C.chr  C.start  C.1  C.2

chr1    19004   A   G       .       .       .    .    chr1    19004     A   G
chr1    19858   C   T      chr1   19858      C   T      .       .       .   .  
chr1    46633   T   A        .       .       .    .   chr1    46633     T   A
chr1    28563   A   G        .       .       .    .     .       .       .   .
chr1    63527   T   C        .       .       .    .     .       .       .   .
chr1    64548   A   C        .       .       .    .     .       .       .   .
chr1    66974   TG  T     chr1    66974      TG  T    chr1    66974     TG  T

[ATpoint]

You should really try and find answers for these kinds of problems yourself. I remember you asked this kind of question before in a different context and got many solutions. Try to understand the logic behind it. There is no point in spoon-feeding as you cannot abstract it to different problems.

[zx8754]

Relevant SO post:

• Simultaneously merge multiple data.frames in a list

Answer 1

[Alex Reynolds]

You could convert your text files to sorted BED with awk and sort-bed.

$ awk -vFS="\t" -vOFS="\t" '{ print $1, $2, ($2+1), $3, $4 }' A.txt | sort-bed - > A.bed

Repeat for B.txt and C.txt.

Then you could do:

$ bedops --everything A.bed B.bed C.bed > Union.bed
$ bedmap --echo --echo-map --delim '\t' --unmapped-val '.\t.\t.\t.\t.\t' Union.bed > Answer.bed

Pipe Answer.bed to cut to get a subset of fields of interest.