4

I have a file test with fields: cato, and pos.

1   7100
1   35000
1   49321
1   49759
2   44842
2   52794
2   53558
3   53859
3   54013
3   55172

I have a file db with fields: cato, start and stop.

1   6408    8000
1   11822   16373
1   18716   23389
1   27690   34330
1   36552   39191
1   39313   44565
2   44839   50247
2   60987   65017
2   65705   71523

My aim is to pick rows in file db where field pos from file test falls within the range start and stop of file db. There is the restriction that the match has to happen within a cato group. Both files are sorted by fields 1 and 2. As a side note, both my real files have many other fields as well.

With this example dataset, my expected result would be:

1   6408    8000
2   44839   50247

I have a script that I have mashed up.

k=1;
data_test=$(cat "test")
data_db=$(cat "db")
while read -r line
do
  # helps to keep count of test rows
  printf "$k \n"

  # get cato
  cato=$(echo $line | awk '{print $1}')
  # get pos
  pos=$(echo $line | awk '{print $2}')
  # get number of chars in pos (to reduce number of lines awk needs to look through later)
  pos_chr=$(echo -n $pos | wc -c)
  # get lines in db that start with cato and pos chars match start or stop
  matched=$(echo "$data_db" | grep -Ew "^$cato" | grep -Ew "[0-9]{$pos_chr}")
  #echo "$db_cat"

  # if matched is not empty
  if [ ! -z "$matched" ]; then
    # use awk to print lines in db where pos > start and pos < stop
    echo "$matched" | awk -v apos='$pos' 'BEGIN{OFS="\t"}{if(apos >= $2 && apos <= $3) print $0}'
    #check
    #echo "$matched" | awk -v apos=$pos 'BEGIN{OFS="\t"}{print apos,$0}'
  fi

  ((k=k+1))
done <<< "$data_test"

It seems like awk doesn't do the comparison in the last step. Things seem to work till the last step and then I am not sure what is wrong. Perhaps someone sees the error. Is there a better way to do this?

  • awk --version GNU Awk 3.1.7 – rmf Jan 21 '18 at 14:36
  • I found that changing apos='$pos' to apos=$pos makes it work. But, this code is way too slow for large files. – rmf Jan 21 '18 at 17:50
4

With single GNU awk program (since Gawk v4.0):

awk 'NR==FNR{ a[$1][$2]; next }
     $1 in a{ 
         for (i in a[$1]) 
             if (i >= $2 && i <= $3) { print $0; break }
     }' test db

The output:

1   6408    8000
2   44839   50247
  • I seem to get syntax error pointing at the square bracket [. – rmf Jan 21 '18 at 15:10
  • 1
    @rmf, unfortunately, gawk v.3 is pretty outdated. Array of arrays feature was implemented in gawk v.4 – RomanPerekhrest Jan 21 '18 at 15:54
  • Aha! Turns out I do have gawk 4.1.4 as well and it works on that. – rmf Jan 21 '18 at 16:15
0

Use join with a simple test:

$ cat is-between.sh
#!/bin/bash

while read cato start stop pos; do
    [ $start -le $pos -a $pos -le $stop ] && echo "$cato $start $stop"
done < <(join db test)

$ ./is-between.sh
1 6408 8000
2 44839 50247

You have relational data: key values in one file match key values in another, row-by-row. join is the tool to bring these together. Run join db test and you'll notice it's the same result as a SELECT * FROM db JOIN test on test.cato=db.cato if the data were in a database.

With this in hand, you now have rows that place start, stop, and pos on a line. Looping over these, using a test to check the range, is then easy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.