1

I've got two sorted tab-delimited files.

input.txt
10      282035  282125  RNA1     -
10      4134522 4134564 RNA1     -
10      5299783 5299910 RNA2     -
10      5900317 5900359 RNA1     -

ref.txt
  1 9       137792944
  1 9       137792945
  1 10      282074
  4 10      282095
  4 10      5900329

I want to print a sum on values IF certain criteria is met.

Namely:

IF ref$2==input$1

AND

ref$3 falls within a range of min==input$2 && max==input$3

Print input$0 and sum of ref$1 (as input$6) else print zero (as input$6) So the result should look like that:

10      282035  282125  RNA1     -  5
10      4134522 4134564 RNA1     -  0
10      5299783 5299910 RNA2     -  0
10      5900317 5900359 RNA1     -  4

This is what I came up with:


awk '
NR == FNR {min[NR]=$2; max[NR]=$3; chr[NR]=$1; next}
 {                
     for (id in min) 
         if (($2==chr[NR])&&(min[id] < $3 && $3 < max[id])) {
             print $0, sum+=$1
             break              
         }
}                                     
' input.txt ref.txt > output.txt

There's clearly something wrong here, since I don't get any output. Also, I'm still missing "else print zero".

Can somebody help me please?

  • You can do this with awk but it's very inefficient. If your files have more than a few hundred lines you should probably import them in a SQL database and run a join. – Satō Katsura Sep 11 '17 at 12:13
  • but 282074 and 282095 may occur in any of ranges from input.txt. This is vague logic. You should reconsider your requirements – RomanPerekhrest Sep 11 '17 at 12:13
  • I'm not sure what you mean. For clarification input$1 and ref.txt $2 are chromosome numbers.So, since 282074 and 282095 are position on chromosome 10 these can only match input.txt row 1 – Hele Sep 11 '17 at 12:36
0

This uses GNU awk, which allows nested arrays:

gawk '
  NR == FNR {
    ref[$2][$3] = $1
    next
  }
  $1 in ref {
    sum = 0
    for (key in ref[$1])
      if ($2 <= key && key <= $3)
        sum += ref[$1][key]
    print $0, sum
  }
' ref.txt input.txt
0
awk 'NR==FNR {dat[NR]=$0;next} { for ( i in dat ) { split(dat[i],arr," ");tag=arr[1];min=arr[2];max=arr[3];if ($2==tag && $3 <= max && $3 >= min) { res[i]+=$1 } } } END { for (i in dat) { print dat[i]" "(res[i]!=0?res[i]:"0") } }' input ref


awk 'NR==FNR {
 dat[NR]=$0;next
} 
{ for ( i in dat ) { 
   split(dat[i],arr," ")
   tag=arr[1];
   min=arr[2];
   max=arr[3];
   if ($2==tag && $3 <= max && $3 >= min) { 
     res[i]+=$1 
     } 
   } 
} 
END { 
  for (i in dat) { 
     print dat[i]" "(res[i]!=0?res[i]:"0") 
  } 
}' input ref

Take each line of input.txt and put it into a array. Then take each line of ref.txt and use the awk function split to extra data into the array arr. From this array we attain the variables tag, min and max. Each line of the array dat is then checked against the criteria and the array res incremented accordingly. At the end, dat is looped through printing the data along with the count from res.

result:

10      5900317 5900359 RNA1     - 4
10      282035  282125  RNA1     - 5
10      4134522 4134564 RNA1     - 0
10      5299783 5299910 RNA2     - 0

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.