# Max, Mean, Min for range within column of data

I have data structured like this :-

``````X   43808873    A   14
X   43808874    G   7
X   43808875    T   15
X   43808876    A   13
X   43808877    T   4
X   43808878    C   13
X   43808879    T   11
X   43808880    C   8
X   43808881    T   3
X   43808882    C   14
X   43808883    T   15
X   43808884    C   15
``````

I'd like to be able to calculate the max, min and mean for column 4 but only between certain ranges for example between 43808877-43808882.

I have this awk code, but can't figure out how to modify it for a particular range, any suggestions are appreciated.

``````awk 'NR == 1 { min = \$4; max = \$4 }
{
sum += \$4
if (\$4 > max) {
max = \$4
}
if (\$4 < min) {
min = \$4
}
} END {
print max
print min
print sum / NR
}' file
``````

One way:

``````sort -k4n,4 file | awk '\$2>=43808877 && \$2<=43808882{tot+=\$4;cnt++;max=\$4;min=cnt==1?\$4:min}END{print min, max,tot/cnt}'
``````

First, we `sort` the file on the 4th column so that the 1st record has min, and the last has max. Using `awk`, we are summing up the numbers within the range and printing the stats

• This worked great, thanks a lot. Commented Jan 3, 2020 at 13:44
• Why does it need the sort?? (a) the file is already sorted on the key field 2, and (b) it sorts on the data field 4, and (c) it doesn't actually need to be ordered anyway, being as it range-checks every line independently. Commented Jan 3, 2020 at 14:21
• @Paul_Pedant it needs to be sorted for this solution since this particular awk script relies on the first \$4 it sees in the range to be the min value and the last \$4 to be the max. Only the sort is comparing \$4 values with each other. Commented Jan 3, 2020 at 14:34
• @edmorton. Conceded, I didn't read the complete post. However, ACGT data is a flag for DNA sequencers, so I would expect huge file sizes. I find a sort|awk solution is generally 10 times slower than a plain awk, for large data. It's that N log N vs. plain N thing. Commented Jan 3, 2020 at 16:36
• @Paul_Pedant I'm not recommending this approach, just explaining why the poster is sorting in response to your question. Commented Jan 3, 2020 at 17:07

If you're not wedded to awk, then Miller is nice for this kind of thing:

``````mlr --nidx --repifs filter '\$2 >= 43808877 && \$2 <= 43808882' then stats1 -f 4 -a max,min,mean file
``````
``````\$ cat tst.awk
\$2>=43808877 && \$2<=43808882 {
cnt++
sum += \$4
min = ( (cnt == 1) || (\$4 < min) ? \$4 : min )
max = ( (cnt == 1) || (\$4 > max) ? \$4 : max )
}
END {
print max+0
print min+0
print (cnt ? sum / cnt : 0)
}

\$ awk -f tst.awk file
14
3
8.83333
``````

Note the protection in the END section to make sure you always get numeric output and don't get a divide by zero error even if there are no records within the desired range.

In this case, it seems to make sense to split the task into 2 processes: one to select the records given the \$2 range, one to generate the stats:

``````awk '43808877 <= \$2 && \$2 <= 43808882' file |
awk '
NR == 1 {min = max = \$4}
\$4 < min {min = \$4}
\$4 > max {max = \$4}
{sum += \$4}
END {
if (NR) printf "%d\n%d\n%f\n", min, max, sum / NR
}
'
``````

Stripped down version.

``````\$ awk '/43808877/,/43808882/{t+=\$4;c++;if(\$4>x){x=\$4}if(\$4<n||!length(n)){n=\$4}}END{print x,n,t/c}' f
14 3 8.83333
\$
``````
• That would include lines where some field other that \$2 had 43808877 in it and would include lines where \$2 was 1438088771, etc. and it'd fail when all the values were negative and it'd produce a divide by zero error and non-numeric output when there were no lines that matched the criteria. Commented Jan 3, 2020 at 15:06
• Indeed. I based this code on the example dataset's needs. Commented Jan 3, 2020 at 15:09