calculating sum and average only for selected data set only

Question

I have a dataset as below:

col-1      col-2     col-3   col-4        col-5 col-6  col-7     col-8

 0         17        215    55.7059        947 BMR_42 O22-BMR_1 O23-H23     
 1          1          1     1.0000          1 BMR_42 O23-BMR_1 O23-H23     
 2         31          3     1.0968         34 BMR_31 O22-BMR_1 O26-H26     
 3         11          2     1.0909         12 BMR_31 O13-BMR_1 O26-H26     
 4         20          5     1.8500         37 BMR_49 O22-BMR_1 O26-H26     
 5         24          4     1.7917         43 BMR_49 O23-BMR_1 O26-H26     
 6         41          2     1.0488         43 BMR_49 O12-BMR_1 O12-H12     
 7         28          2     1.0357         29 BMR_49 O22-BMR_1 O13-H13     
 8          1       1000  1000.0000       1000 BMR_49 O13-BMR_1 O13-H13     
 9          1          1     1.0000          1 BMR_22 O12-BMR_2 O22-H22     
10         50         62    18.9400        947 BMR_59 O13-BMR_2 O22-H22     
11          1          1     1.0000          1 BMR_59 O25-BMR_2 O23-H23     
12         34          5     1.1471         39 BMR_59 O13-BMR_2 O23-H23     
13          7          6     2.1429         15 BMR_59 O26-BMR_2 O24-H24     
14          6          8     3.6667         22 BMR_59 O25-BMR_2 O24-H24     
15         28          2     1.1071         31 BMR_10 O26-BMR_2 O26-H26     
16         52        121    15.1346        787 BMR_10 O25-BMR_2 O26-H26     
17         65          9     1.9231        125 BMR_10 O13-BMR_2 O26-H26     
18          4          4     2.2500          9 BMR_59 O26-BMR_2 O26-H26     
19          9          1     1.0000          9 BMR_22 O15-BMR_2 O13-H13     
20          1          1     1.0000          1 BMR_10 O11-BMR_2 O16-H16     
21          7          2     1.1429          8 BMR_53 O13-BMR_2 O16-H16     
22          2          3     2.5000          5 BMR_33 O13-BMR_3 O22-H22     
23         97         54     6.8247        662 BMR_61 O26-BMR_3 O22-H22     
24          1          1     1.0000          1 BMR_29 O26-BMR_3 O23-H23     
25         31         36     3.3226        103 BMR_29 O16-BMR_3 O23-H23     

(The real file contains over 2000 lines).

I want to select data under certain criteria and find the sum and average of that. For example I want to select lines containing O22 in column $7 and $8 and calculate the sum and average of the values in column $4.

I tried a script as below:

awk '$7 ~ /O22/ && $8 ~ /O22/ {sum += $4} END {print sum, (sum/NR) }' hhsolute.lifetime2.dat

This code could select the line correctly but when I want to calculate the average (sum/NR), I don't get the correct value.

I wish to get some help on this. How I could get the sum and average values only for the data lines I wanted?

Appreciate any help in advance.

Answer 1

Try this:

awk '$7~/O22/ && $8~/O22/{++n;sum+=$4}END{if(n) print "Sum = " (sum), "Average= "(sum/n)}' File

If 7th and 8th field both contains pattern O22, add 4th field value to variable sum, increase n. Within END block, print the sum and average.

Answer 2

awk -v tgt="O22" '
$7 ~ tgt && $8 ~ tgt { sum+=$4; cnt++ }
END { print sum+0, (cnt ? sum/cnt : 0) }
' file