awk not merging two files based on the matching of two columns

Question

I am merging two files on matching columns values. column 3 from file1 and column 4 from file2.

I used this:

awk 'FNR==NR{a[$3]=$5" "$6" "$7" "$8" "$9;next} ($4 in a) {print $1,$2,$3,$4,a[$3]}' file1 file2

It is giving wrong result.

file1:

UTTAR_PRADESH AGRA AGRA_AERO(IAF) OBSY 27 09N 77 58E 0168 1959 yogi NA
UTTAR_PRADESH AGRA AGRA OBSY 27 10N 78 02E 0169 1862 yogi NA
MAHARASHTRA YEOTMAL YEOTMAL OBSY 20 24N 78 09E 0451 1949 yogi NA
ARUNACHAL_PRADESH LOWER_SUBANSIRI ZERO OBSY 27 35N 94 50E 1476 1965 yogi NA

file2:

RIGHT UTTAR_PRADESH AGRA AGRA_AERO(IAF) OBSY 403 27 77 18 46 720.3 11.4 1.1 11.7 1.1 7.3 1.0 6.6 0.7 15.0 1.6 53.7 3.2 223.4 11.0 239.3 11.5 116.3 5.0 23.4 1.1 6.4 0.4 5.9 0.5
RIGHT UTTAR_PRADESH AGRA AGRA OBSY 403 27 78 19 45 754.5 12.5 1.2 12.6 1.1 7.7 0.9 8.1 0.7 17.0 1.5 66.5 3.1 209.6 10.4 253.2 11.5 130.5 5.7 24.7 1.2 5.4 0.4 6.8 0.5
RIGHT MAHARASHTRA YEOTMAL YEOTMAL OBSY 314 20 78 09 49 1071.0 15.0 1.0 9.6 0.8 15.8 1.4 12.0 1.2 21.1 1.6 184.4 8.5 286.0 14.1 280.8 13.1 158.5 8.8 54.8 3.0 19.9 0.9 13.1 0.9
RIGHT ARUNACHAL_PRADESH LOWER_SUBANSIRI ZERO OBSY 502 27 94 16 29 1504.7 68.4 4.1 76.7 5.9 81.4 6.8 161.8 11.1 197.6 11.9 221.1 13.9 213.8 13.6 216.2 13.6 148.6 11.2 67.0 4.6 34.1 2.6 18.0 1.2

wrong result:

RIGHT UTTAR_PRADESH AGRA AGRA_AERO(IAF) 27 10N 78 02E 0169
RIGHT UTTAR_PRADESH AGRA AGRA 27 10N 78 02E 0169
RIGHT MAHARASHTRA YEOTMAL YEOTMAL 20 24N 78 09E 0451
RIGHT ARUNACHAL_PRADESH LOWER_SUBANSIRI ZERO

expected result:

RIGHT UTTAR_PRADESH AGRA AGRA_AERO(IAF) 27 09N 77 58E 0168
RIGHT UTTAR_PRADESH AGRA AGRA 27 10N 78 02E 0169
RIGHT MAHARASHTRA YEOTMAL YEOTMAL 20 24N 78 09E 0451
RIGHT ARUNACHAL_PRADESH LOWER_SUBANSIRI ZERO 27 35N 94 50E 1476

can someone point out my mistake.

Answer 1

You have to use the relative index of the current record, into the array for second expression. That means a[$4] instead of a[$3].

awk 'FNR==NR{a[$3]=$5" "$6" "$7" "$8" "$9;next} ($4 in a) {print $1,$2,$3,$4,a[$4]}' file1 file2
RIGHT UTTAR_PRADESH AGRA AGRA_AERO(IAF) 27 09N 77 58E 0168
RIGHT UTTAR_PRADESH AGRA AGRA 27 10N 78 02E 0169
RIGHT MAHARASHTRA YEOTMAL YEOTMAL 20 24N 78 09E 0451
RIGHT ARUNACHAL_PRADESH LOWER_SUBANSIRI ZERO 27 35N 94 50E 1476

It was a coincidence that you were getting almost correct results for this example, because most lines have identical 3rd and 4th fields.