finding the closest value from another file with awk

Question

I got 2 files.

file1:

123 pattern1   
452 pattern2
601 pattern3

file2:

12  a
34  b
88  ee
120 f
333 qw
400 oo
566 i
993 o

What I would like is to find the closest value to each line in the first column of file1 from the first column of file2 and then add the corresponding 2nd column to file2 to file1 (in a new file)

There are no repeated values in either of the files.

desired output:

123 pattern1    f   
452 pattern2    oo
601 pattern3    i

I was attempting to use awk, but it is far from working :/

awk 'NR==FNR { seq[$2]=$1; next }
{
    d = $1 - seq[$2];
    d = d < 0 ? -d : d;
    v = $1;
    next
}
{
    m = $1 - seq[$2];
    m = m < 0 ? -m : m
}
m < d {
    d = m;
    v = $1
}
{ print $0 }' file1.txt file2.txt

Answer 1

Bodo has already pointed out the issues with your code and corrected them. I'm giving alternative answers.

Using awk only. This is essentially the same as in Bodo's answer, but it does not split the data from the first file into individual arrays. It also does not use a magic numerical constant (-1 in their code) to initialise the smallest distance seen but instead uses the fact that an unset value in awk is empty when interpreted as a string.

awk '
    NR == FNR { num[$0] = $1; next }
    {
        for (a in num) {
            d = (num[a]-$1)^2
            if (min[a] == "" || d < min[a]) {
                min[a] = d; symb[a] = $2
            }
        }
    } 
    END { for (a in symb) print a, symb[a] }' file1 file2

---

Other ways:

join -1 3 -2 3 file1 file2 |
awk '{ print ($1-$3)^2, $0 }' |
sort -k 1,1n | sort -su -k 2,3  |
awk '{ print $2, $3, $5 }'

The above first use join on non-existing fields to create a relational cross-product of all the input data (it combines each line of one file with all the lines of the other file).

That data will look like this:

 123 pattern1 12 a
 123 pattern1 34 b
[etc.]
 601 pattern3 566 i
 601 pattern3 993 o

It then calculates a distance metric and inserts this as a new first column:

12321  123 pattern1 12 a
7921  123 pattern1 34 b
[etc.]
1225  601 pattern3 566 i
153664  601 pattern3 993 o

The two calls to sort first order this data numerically on the distance metric, then it uses the data from the first file as the sorting key and does a stable unique sort. This will discard all but the first instance of the data from the first file, leaving, in this case, three lines:

9  123 pattern1 120 f
2704  452 pattern2 400 oo
1225  601 pattern3 566 i

From this, we pick out the columns we're interested in using awk:

123 pattern1 f
452 pattern2 oo
601 pattern3 i

Answer 2

There are several problems in your awk script. The second block of actions has no condition and ends with next. That's why the other blocks will never be executed.

Using the field 2 from the second file as an index for seq does not make sense because the values in field 2 differ between file1 and file2.

awk 'NR==FNR {
    seq[NR] = $1;
    name[NR] = $2;
    delta[NR] = -1;
    count = NR;
    next
}
{
    for(i = 1; i <= count; i++) {
       d = $1 - seq[i];
       d = d < 0 ? -d : d;
       if((delta[i] < 0) || (d < delta[i])) {
            delta[i] = d;
            val[i] = $2;
       }
    }
}
END {
    for(i = 1; i <= count; i++) {
       printf "%s %s\t%s\n", seq[i], name[i], val[i]
    }
}' file1.txt file2.txt

prints

123 pattern1    f
452 pattern2    oo
601 pattern3    i

Answer 3

A little more compact if you change the order of the files.

awk '
NR==FNR {A[$1]=$2; next}
{arg=1000
        for(i in A){
                cmp = sqrt(($1-i)^2)
                if(cmp < arg)
                        {arg=cmp; third=A[i]}
                }
        print $0, third}
' file2 file1

partten of expression sqrt(X^2) performs the function of absolute value