# If value falls within a range print sum of values

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

• 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

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
``````
``````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
``````