Check numbers in each line on a specific column in one variable against all lines in two specific columns in another variable using awk

Question

I need to check numbers in each line on a specific column in one variable against all lines in two specific columns in another variable using awk, keeping lines in the first variable that meet parameters.

Attempts I have made so far to do this in one powerful awk command have failed. I can obviously do this in an external loop, but it would be very slow as I have 100's of thousands of lines to check. I appreciate any and all help with solving this, and I am always looking to improve my use of awk, so if you have a solution it would be great to have an explanation so I can learn and improve myself.

Here is an example:

• Lets say I want to print only the lines in column 2 from ${ListToCheckFrom}, if the number there is > column 2 and < column 3 in any line from ${ListToCheckAgainst}

• Input example:

  ListToCheckFrom="C,2  
  C,22  
  C,12  
  hr,15"
  
  ListToCheckAgainst="C1,25,50  
  hr1,22,30  
  r,12,18  
  C,15,44"  
  

• Expected output:

  C,22  
  hr,15
  

Answer 1

Since you have tagged the question with bash, you can make use of process substitution to read the shell variables like input files. The following script snippet should do:

#!/bin/bash

ListToCheckFrom="C,2  
C,22  
C,12  
hr,15"

ListToCheckAgainst="C1,25,50  
hr1,22,30  
r,12,18  
C,15,44"

awk -F',' 'list=="constr"{n++; low[n]=$2;high[n]=$3;next}
           {for (i=1;i<=n;i++) {if ($2>low[i]&&$2<high[i]) {print;next};}}' \
           list=constr <(echo "$ListToCheckAgainst") \
           list=chk <(echo "$ListToCheckFrom")

This will specify the echo'd content of $ListToCheckAgainst as first input file, and the echo'd content of $ListToCheckFrom as second input file. It will set an awk variable list to either constr or chk before "opening" each file, so that awk can internally distinguish which of the "files" is currently being processed.

• When processing the "constraints" from $ListToCheckAgainst, it simply stores the "lower" and "upper" bound, as specified in columns 2 and 3, in arrays low and high, respectively. Apart from that, it skips processing immediately to the next input line.
• When processing the list to check from $ListToCheckFrom, it scans all ranges registered previously, and if it finds that column 2 falls within any one of them, prints it (and immediately skips processing to the next input line).

If your data is stored in "physical" files rather than shell variables, you can simply use the filenames instead of the process substitutions as command-line arguments.

Answer 2

$ cat tst.sh
#!/usr/bin/env bash

ListToCheckFrom='C,2
C,22
C,12
hr,15'

ListToCheckAgainst='C1,25,50
hr1,22,30
r,12,18
C,15,44'

awk '
    BEGIN { FS="," }
    NR==FNR {
        begs2ends[$2] = $3
        next
    }
    {
        for ( beg in begs2ends ) {
            beg += 0
            end = begs2ends[beg]+0
            if ( (beg < $2) && ($2 < end) ) {
                print
                next
            }
        }
    }
' <(printf '%s\n' "$ListToCheckAgainst") <(printf '%s\n' "$ListToCheckFrom")

$ ./tst.sh
C,22
hr,15