vlookup like function with awk

November 29, 2023

1st tab separated file1:

chr1    10031   10031   ->      chr1    10061   10061
chr1    10037   10037   ->      chr1    10047   10047
chr1    10043   10043   ->      chr1    10053   10053
chr1    10055   10055   ->      chr1    10065   10065

2nd tab separated file2:

#Chr    Start   End     Ref     Alt     X
chr1    10031   10031   T       C       0
chr1    10037   10037   T       C       2.601e-05 
chr1    10043   10043   T       C       1.168e-05

Expected separated value table is:

chr1    10061   10061   T       C       0
chr1    10047   10047   T       C       2.601e-05 
chr1    10053   10053   T       C       1.168e-05

I will try that:

awk 'NR==FNR { a[$2]=$2; b[$3]=$3; next } { if ($2 in a && $3 in b) print $1, a[$3], b[$3], $4, $5, $6 }' file1 file2

But i cant. What is my mistake? Thanks.

>Solution :

You intend to get $5,$6,$7 from file1 but are only storing $2 and $3 in your associative arrays.

You may use this awk instead:

awk '
BEGIN {FS=OFS="\t"}
{
   k = $1 FS $2 FS $3
}
NR == FNR {
    map[k] = $5 FS $6 FS $7
    next
}
k in map {
   print map[k], $4, $5, $6
}' file1 file2

chr1    10061   10061   T   C   0
chr1    10047   10047   T   C   2.601e-05
chr1    10053   10053   T   C   1.168e-05