CODEWITHSUNDEEP
bashawk
Nikko
Nikko

Reputation: 547

Find the closest values: Multiple columns conditions

Following my first question here I want to extend the condition of find the closest value from two different files of the first and second column, and print specific columns.

File1

1 2 3 4 a1
1 4 5 6 b1
8 5 9 11 c1

File 2

1 1 3 a
1 2 5 b
1 2.1 4 c
1 4 6 d 
2 4 5 e
9 4 1 f 
9 5 2 g
9 6 2 h
11 10 14 i
11 15 5 j

So for example I need to find the closest value from $1 in file 2 for each $1 in file 1 but then search the closest also for $2.

Output:

1 2 a1*
1 2 b*
1 4 b1 
1 4 d 
8 5 c1 
9 5 g

* First column file 1 and 2nd column file 2 because for the 1st column (of file 1) the closest value (from the 1st column of file 2) is 1, and the 2nd condition is that also must be the closest value for the second column which is this case is 2. And I print $1,$2,$5 from file 1 and $1,$2,$4 from file 2

For the other output is the same procedure.

The solution to find the closest it is in my other post and was given by @Tensibai. But any solution will work. Thanks!

Upvotes: 0

Views: 461

Answers (1)

Tensibai
Tensibai

Reputation: 15784

Sounds a little convoluted but works:

function closest(array,searched) {
  distance=999999; # this should be higher than the max index to avoid returning null
  split(searched,skeys,OFS)
  # Get the first part of key
  for (x in array) { # loop over the array to get its keys
    split(x,mkeys,OFS) # split the array key
    (mkeys[1]+0 > skeys[1]+0) ? tmp = mkeys[1] - skeys[1] : tmp = skeys[1] - mkeys[1] # +0 to compare integers, ternary operator to reduce code, compute the diff between the key and the target
    if (tmp < distance) { # if the distance if less than preceding, update
      distance = tmp
      found1 = mkeys[1] # and save the key actually found closest
    }
  }
  # At this point we have the first part of key found, let's redo the work for the second part
  distance=999999;
  for (x in array) {
    split(x,mkeys,OFS)
    if (mkeys[1] == found1) { # Filter on the first part of key
      (mkeys[2]+0 > skeys[2]+0) ? tmp = mkeys[2] - skeys[2] : tmp = skeys[2] - mkeys[2] # +0 to compare integers, ternary operator to reduce code, compute the diff between the key and the target
      if (tmp < distance) { # if the distance if less than preceding, update
        distance = tmp
        found2 = mkeys[2] # and save the key actually found closest
      }

    }
  }
  # Now we got the second field, woot
  return (found1 OFS found2)  # return the combined key from out two search
}

{
   if (NR>FNR) { # If we changed file (File Number Record is less than Number Record) change array

     b[($1 OFS $2)] = $4 # make a array with "$1 $2" as key and $4 as value
   } else {
     key = ($1 OFS $2) # Make the key to avoid too much computation accessing it later
     akeys[max++] = key # store the array keys to ensure order at end as for (x in array) does not guarantee the order
     a[key] = $5 # make an array with the key stored previously and $5 as value
   }

}

END { # Now we ended parsing the two files, print the result
  for (i in akeys) { # loop over the array of keys which has a numeric index, keeping order
    print akeys[i],a[akeys[i]] # print the value for the first array (key then value)
    if (akeys[i] in b) { # if the same key exist in second file
      print akeys[i],b[akeys[i]] # then print it
    } else {
      bindex = closest(b,akeys[i]) # call the function to find the closest key from second file
      print bindex,b[bindex] # print what we found
    }
  }
}

Note I'm using OFS to combine the fields so if you change it for output it will behave properly.

WARNING: This should do with relative short files, but as now the array from second file is traversed twice it will be twice long for each searchEND OF WARNING

There's place for a better search algorithm if your files are sorted (but it was not the case on previous question and you wished to keep the order from the file). First improvement in this case, break the for loop when distance start to be greater than preceding one.

Output from your sample files:

$ mawk -f closest2.awk f1 f2
1 2 a1
1 2 b
1 4 b1
1 4 d
8 5 c1
9 5 g

Upvotes: 1

Related Questions