Matching two files per column

Shell Programming and Scripting
1

Hi,

I hope somebody can help me with this problem, since I would like to solve this problem using awk, but im not experienced enough with this.

I have two files which i want to match, and output the matching column name and row number.

One file contains 4 columns like this:
FILE1:

a         b         c         d
10.5    20.2    50.4    100.2
200.1  500.4   20.6    10.4

and the second file just one column with entries:
FILE2:

12
50
200

I want for the entries in FILE2, to find the value they match to in FILE1, within a certain range of 2 (so +/- 2). And output the column name and the row number

So the output would be:

12   a   1
50   c   1
200 a   2

I can do this using loops, but this takes long because of the size of the files.
Is there a way of doing this using AWK?

Sorry I don't have any code of what I tried yet. I was trying code from previous questions, but didn't really get close to what I need..

2

I don't see an advantage with AWK, you have to examine every value of both files; moreover, what if the value matches in more than one place?

3

Hi blackrageous,

Yes you are right. It can match at multiple places.
I was just hoping there was a faster way then using for loops..

Thanks for your reply :slight_smile:

4

I sometimes will use a RAM disk in an effort to speed up scripts where I have to process each line a number of times.

5

awk needs two loops but is pretty fast.

awk -v range=2 '
function adiff(a,b) {if (a>b) return a-b; else return b-a}
NR==FNR {val[$1]; next}
FNR>1 {
  for (f=1; f<=NF; f++)
    for (i in val)
      if (adiff($f,i)<=range) print i, f, FNR-1
}
' FILE2 FILE1
6

Another awk approach:-

awk '
        NR == FNR {
                A[$1]
                next
        }
        FNR == 1 {
                for ( i = 1; i <= NF; i++ )
                        R = $i
                next
        }
        {
                for ( i = 1; i <= NF; i++ )
                {
                        for ( k in A )
                        {
                                d = ( k > $i ? k - $i : $i - k )
                                if ( d >=0 && d <=2 )
                                        print k, R, FNR-1
                        }
                }
        }
' file2 file1
7

Thanks a lot! That worked!!