Check info in each line on a specific set of columns in one variable against all lines in three specific columns in another variable using awk

Question

I've asked a very similar question today before, however I have since realised that I need to increase parameters for the command. I edited the command for a another parameter okay, but the next parameter I had less success with and I don't know why. Here is what I'm trying (and failing) to solve.

I need to check info in each line on a specific set of columns 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 from ${ListToCheckFrom}, if column 2 is >= and column 3 is <= to the corresponding columns of any one line from ${ListToCheckAgainst}. Additionally column 1 from ${ListToCheckFrom} must be identical to column 1 in ${ListToCheckAgainst}

• Input example:

ListToCheckFrom="r,2,3
C,22,24
C,12,13
C,51,59
C,15,20
C,13,18"
        
ListToCheckAgainst="C,25,50
C,22,30
C,12,18
C,15,17
C,1,12
C,60,200"

• Expected output:

C,22,24  
C,12,13
C,15,20
C,13,18

• What I have tried based from an answer (thanks to @AdminBee) to a simpler previous question I asked today:

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

I am using Ubuntu.

Answer 1

Building on the other answer you got to your previous question:

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

ListToCheckFrom="r,2,3
C,22,24
C,12,13
C,51,59
C,15,20
C,13,18"

ListToCheckAgainst="C,25,50
C,22,30
C,12,18
C,15,17
C,1,12
C,60,200"

awk '
    BEGIN { FS="," }
    NR==FNR {
        vals[NR] = $0
        next
    }
    {
        for ( nr in vals ) {
            split(vals[nr],v)
            if ( (v[1] == $1) && ( (v[2] <= $2) && ($2 <= v[3]) ) ) {
                print
                next
            }
        }
    }
' <(printf '%s\n' "$ListToCheckAgainst") <(printf '%s\n' "$ListToCheckFrom")

$ ./tst.sh
C,22,24
C,12,13
C,15,20
C,13,18

Regarding the script you asked about modifying, which was this in the answer to your previous question:

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")

The first step for you trying to enhance it should have been to make it a bit easier to read, e.g. if you run the awk part through gawk -o- and add the shell parts back around it you get this:

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")

and from there hopefully it's easy to see all you had to do to make it work for your new requirements was change it to this:

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