This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Merge two files with multiple columns by 4 common columns

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.

vcf
• 2,054 views • link
updated by cpad0112 cpad0112 • written by User000 User000

I do not know the reason

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

• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum 
ASCII text, with CRLF line terminators

How to fix this?

• 0 views • link
written by User000 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}
• 0 views • link
written by User000 User000

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.

• 0 views • link
written by cpad0112 cpad0112

1 answer

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)
• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

Log in to answer this question.

More posts like this