Merge two files with multiple columns by 4 common columns

Question

[User000]

Dear all,

I have two files. File1.tsv:

CHROM   POS REF ALT Allele  Consequence IMPACT  SYMBOL  Gene
chrM    3338    G   A   A   missense_variant    MODERATE    MT-ND1  ENSG00000198888     
chrM    3395    T   C   C   missense_variant    MODERATE    MT-ND1  ENSG00000198888 

Files2.tsv:

CHROM   POS REF ALT FILTER  nHet    nHomAlt nHomRef
chrM    3338    G   A   PASS    0   1   499
chrM    3395    T   C   PASS    1   2   497 

My desired output is

 chrM   3338    G   A   A   missense_variant    MODERATE    MT-ND1  ENSG00000198888    chrM 3338    G   A   PASS    0   1   499     
 chrM   3395    T   C   C   missense_variant    MODERATE    MT-ND1  ENSG00000198888    chrM 3395    T   C   PASS    1   2   497

I tired to do a simple paste, but when I do paste file1.txt files2.txt the columns of the second file start from the new line, when i do paste file2.txt file1.txt it works. I do not know the reason. Also I would like to take into considerazion the first 4 columns (CHROM, POS,REF,ALT), which should match.

[Pierre Lindenbaum]

I do not know the reason

check your files are NOT windows files with CR/LF returns with file your.vcf

[User000]

ASCII text, with CRLF line terminators

How to fix this?

[cpad0112]

Try dos2unix tool. It's present in most of distro repos.

$ tsv-join -H -f file2.txt -k 1,2,3,4  file1.txt -a CHROM,POS,REF,ALT,FILTER,nHet,nHomAlt,nHomRef

CHROM   POS REF ALT Allele  Consequence IMPACT  SYMBOL  Gene    CHROM   POS REF ALT FILTER  nHet    nHomAlt nHomRef
chrM    3338    G   A   A   missense_variant    MODERATE    MT-ND1  ENSG00000198888 chrM    3338    G   A   PASS    01499
chrM    3395    T   C   C   missense_variant    MODERATE    MT-ND1  ENSG00000198888 chrM    3395    T   C   PASS    12497

$ csvtk join --outer-join -tHf 1,2,3,4 file1.txt file2.txt                           

CHROM   POS REF ALT Allele  Consequence IMPACT  SYMBOL  Gene    FILTER  nHet    nHomAlt nHomRef
chrM    3338    G   A   A   missense_variant    MODERATE    MT-ND1  ENSG00000198888 PASS    0   1   499
chrM    3395    T   C   C   missense_variant    MODERATE    MT-ND1  ENSG00000198888 PASS    1   2   497

Note: tsv-join output looks like joined numbers at the end, but they are not. The numbers are tab separated.

[Pierre Lindenbaum]

tr -d '\r' < bad.vcf > fixed.vcf

[User000]

is it possible to integrate it directly in the command line without creating a new file? This does not work..

vep-annotation-reporter {input.vcf} Allele Consequence IMPACT SYMBOL Gene -o - | tr -d '\r' > {output.tsv}

[Pierre Lindenbaum]

it's ok. there is no problem here.

Answer 1

[Pierre Lindenbaum]

create a composite key with awk , sort and use join. Something like (not tested)

join -t $'\t' -1 1 -2 1 \
   <(awk '{printf("%s|%s|%s|%s\t%s\n",$1,$2,$3,$4,$0);}' in1.vcf | sort -t $'\t' -k1,1) \
   <(awk '{printf("%s|%s|%s|%s\t%s\n",$1,$2,$3,$4,$0);}' in2.vcf | sort -t $'\t' -k1,1) 

[Pierre Lindenbaum]

but fix your CRLF files before....