Joining two files matching two columns with mismatches and in each matching line, substitute second column from file 1 into 6th column in file 2

Question

I'm struggling with this task:

I have two files:
file1 looks like:

102 13.342
103 7.456
105 6.453
107 3.567
108 4.210

file2 looks like:

0 098 0 0 0 -9 x
0 099 0 0 0 -9 x
0 100 0 0 0 -9 x
0 101 0 0 0 -9 x
0 102 0 0 0 -9 x
0 103 0 0 0 -9 x
0 104 0 0 0 -9 x
0 105 0 0 0 -9 x
0 106 0 0 0 -9 x
0 106 0 0 0 -9 x
0 107 0 0 0 -9 x
0 108 0 0 0 -9 x

And I want a file3 that looks like

 0 098 0 0 0 -9 x
 0 099 0 0 0 -9 x
 0 100 0 0 0 -9 x
 0 101 0 0 0 -9 x
 0 102 0 0 0 13.342 x
 0 103 0 0 0 7.456 x
 0 104 0 0 0 -9 x
 0 105 0 0 0 6.453 x

Basically, I want to join file1 and file2 by matching first and second fields in file1 and file2 respectively, keeping mismatches, and also, substituting the value of the sixth field in file2 with the value of the second field in file1 in each matching line...

I know this task is related with the use of: join -a1 -a2 -o 1.2 whatsoever. But cannot figure it out how to continue... Also considering awk

Answer 1

When there are so many fields involved I tend to prefer awk:

$ awk 'NR==FNR{a[$1]=$2; next}{if($2 in a){$6=a[$2]}}1;' file1 file2 
0 098 0 0 0 -9 x
0 099 0 0 0 -9 x
0 100 0 0 0 -9 x
0 101 0 0 0 -9 x
0 102 0 0 0 13.342 x
0 103 0 0 0 7.456 x
0 104 0 0 0 -9 x
0 105 0 0 0 6.453 x
0 106 0 0 0 -9 x
0 106 0 0 0 -9 x
0 107 0 0 0 3.567 x
0 108 0 0 0 4.210 x

Explanation

• NR==FNR{a[$1]=$2; next} : NR is the current line number and FNR is the current line number of the current file. When processing more than one file, the two will be equal only while the 1st file is being read. a[$1]=$2 uses the st field as a key to an array whose value is the 2nd field. Thenextskips to the next line. So, this will save all values fromfile1into the arraya`.

• if($2 in a){$6=a[$2]} : now we're reading the 2nd file. If the second field of this line is present in the array a, set the 6th field ($6) to be whatever was stored in a for the second field.

• 1; : this is shorthand for "print this line".

Answer 2

If both files are sorted by key-field (and 6th field usually -9)

join -1 2 -a 1 -e '-9' -o 1.1 1.2 1.3 1.4 1.5 2.2 1.7 file2 file1

do the job (thanx to steeldriver for correction)
But more correct can be with sed`s formatting:

join -1 2 -a 1 2 1 -o 1.1,1.2,1.3,1.4,1.5,1.6,2.2,1.7 |
sed 's/ \S*\( \S\+\)/\1/3'

If you prefer awk and sure that there are all keys from file1 in file2

awk '{
    a=0
    do {
        if (a)
           print a
        getline a <"file2"
        split(a,A)
        }
    while($1 != A[2])
    i=0
    A[6]=$2
    for(i in A)
        printf("%s ",A[i++])
    print ""
    }' file1