Match between the columns

Question

I have two files. First, I want to look at the column "Variant_Type" in the first file. If it is DEL, then I should look if there is a match between three columns in two files (Chromosome, vcf_pos, Reference_Allele) and append AC and AF columns from the first file to the second file. If it is INS in a "Variant_Type", then I look for the match between another three columns in the two files (Chromosome, vcf_pos, Tumor_Seq_Allele2) and append relevant AC and AF columns from the second file. If it is SNP, then again look for the match between another three columns in the two files (Chromosome, vcf_pos, Tumor_Seq_Allele2) and append relevant AC and AF columns from the second file.

Here is the snippet from File 1

Hugo_Symbol Chromosome Start_Position End_Position Reference_Allele Tumor_Seq_Allele2 Variant_Type vcf_pos 
TMEM80      chr11      704605         704605       A                -                 DEL          704604 
OR52P1P     chr11      5726537        5726537      T                -                 DEL          5726536
UBTFL1      chr11      90086720       90086721     -                T                 INS          90086720
DCPS        chr11      126306583      126306584    -                TGGGGA            INS          126306583
DCPS        chr11      126306583      126306584    -                TGGGGAAA          INS          126306583

File 2

Chromosome vcf_pos      AF       AC      Reference_Allele  Tumor_Seq_Allele2
chr11      704604       0.2      10      A                 - 
chr11      5726536      0.35     13      T                 -
chr11      90086720     0.25     16      -                 T
chr11      126306583    0.5      29      -                 TGGGGA 
chr11      126306583    0.3      39      -                 TGGGGAAA

Desired output

Hugo_Symbol Chromosome Start_Position End_Position Reference_Allele Tumor_Seq_Allele2 Variant_Type vcf_pos   AF   AC 
TMEM80      chr11      704605         704605       A                -                 DEL          704604    0.2  10
OR52P1P     chr11      5726537        5726537      T                -                 DEL          5726536   0.35 13
UBTFL1      chr11      90086720       90086721     -                T                 INS          90086720  0.25 16
DCPS        chr11      126306583      126306584    -                TGGGGA            INS          126306583 0.5  29
DCPS        chr11      126306583      126306584    -                TGGGGAAA          INS          126306583 0.3  39

As a possible solution I was thinking about merge function in R, but might work better with awk

Answer 1

As a possible solution I was thinking about merge function in R

Indeed the R merge function together with plain indexing can do it.

t1 = read.table('File 1', T)
t2 = read.table('File 2', T)
AFAC = c('AF', 'AC')        # columns to copy
l = t1$Variant_Type=='DEL'  # rows to process
t1[l, AFAC] = merge(t1[l, c('Chromosome', 'vcf_pos', 'Reference_Allele')], t2, sort=F)[AFAC]
l = t1$Variant_Type %in% c('INS', 'SNP')
t1[l, AFAC] = merge(t1[l, c('Chromosome', 'vcf_pos', 'Tumor_Seq_Allele2')], t2, sort=F)[AFAC]
write.table(t1, 'output', F, F)

Since the match columns for INS and SNP are the same, the processing of these two Variant_Types can be combined.

Answer 2

might work better with awk

Just for comparison - a solution with awk:

awk 'BEGIN { while (getline <"File 2" > 0)      # make "dictionary" of AF, AC for ...
             { AFAC1[$1","$2","$5] = $3" "$4    # ... Chr., vcf. and Reference_Allele
               AFAC2[$1","$2","$6] = $3" "$4    # ... Chr., vcf. and Tumor_Seq_Allele2
             }
           }
     NR==1     { print $0" AF AC" }             # first line has column headers
     $7=="DEL" { print $0, AFAC1[$2","$8","$5] }# append the 1st stored AF, AC
     $7=="INS"||
     $7=="SNP" { print $0, AFAC2[$2","$8","$6] }# append the 2nd stored AF, AC
    ' "File 1"