Get columns from another file for match in col 2 in 1st file

Shell Programming and Scripting
1

Hi,
My first file has

592155 9 rs16916098 1
592156 19 rs7249604 1
592157 4 rs885156 1
592158 5 rs350067 1

2nd file has

9       rs16916098      0       113228129       2       4
19      rs7249604       0       58709070        4       2
2       rs17042833      0       113558750       4       2
4       rs10517059      0       24534680        2       1
4       rs885156        0       140779838       1       2

You will notice that Col3 of file1 = Col2 of file2.
I want to get for each value of Col2 in file 1 the equivalent matching Col 4 from file 2 and append it to the last column of the first file like this..

result_file

592155     9     rs16916098     1    113228129
592156     19     rs7249604     1  58709070
592157     4     rs885156     1  140779838

Trying to get this done with awk. Found this old thread with a similar problem

But I am not smart enough to figure the way awk works with the provided solution..

awk -F, 'NR==FNR{a[$1$2]=$3;next}a[$1$2]{$4=a[$1$2];print}' OFS="," file1 file2

It would be very helpful to de-mystify the above code for the solution in the link.

Thanks
~GH

2

Thank you very much and especially for explaining the rationale.
It worked on the sample data. But I dont get output with my real data. I am trying to see if the format is messed up in the 1 million lines somewhere :frowning: