Sum all values in a column with an especified header

Question

I have a file which has sections with different tables. I want to sum all the values contained within each column with the header N*dN

For example:

omega (dN/dS) = 999.00000

dN & dS for each branch

 branch          t       N       S   dN/dS      dN      dS  N*dN  S*dS

  11..6      0.000   532.0   266.0 999.0000  0.0000  0.0000   3.0   0.0
  11..7      0.000   532.0   266.0 999.0000  0.0000  0.0000   4.0   0.0
  11..12     0.000   532.0   266.0 999.0000  0.0000  0.0000   0.5   0.0

omega (dN/dS) = 1.00000

dN & dS for each branch

 branch          t       N       S   dN/dS      dN      dS  N*dN  S*dS

  11..6      0.000   532.0   266.0 999.0000  0.0000  0.0000   7.0   0.0
  11..7      0.000   532.0   266.0 999.0000  0.0000  0.0000   2.0   0.0
  11..12     0.000   532.0   266.0 999.0000  0.0000  0.0000   0.4   0.0

Desired output:

7.5
9.4

Maybe some modification of

awk '{s+=$8}END{print s}'

where I can specify the title of the column and make it print the separate results of each sum, not the sum of the sums...

Answer 1

You can use awk in this way (assuming the content are always followed by the matched header and same counts in all + no empty lines happened between them).

awk 'f{s+=$8;f++} ($8=="N*dN"){f=1} (f>4){print s;f=s=0}' infile

Or in general case, use it as like below. It will sum till next N*dN seen in column#8:

awk 'f{s+=$8} (f &&$8=="N*dN"){print s;f=s=0} ($8=="N*dN"){f=1} END{print s}' infile

Answer 2

Another awk approach:

awk '$8=="N*dN"{ r=NR; if(s) print s; s=0 }r && NR-r<5{ s+=$8 }END{ print s }' file

The output:

7.5
9.4

Answer 3

Well, it is not funny thing to with the eyes of a programmer, but it is really quick and it is okay for simple tasks.

As we can see, all of the interesting rows contain the string .., so we can filter them with a grep:

grep -F '..' <input.txt|awk 'BEGIN{s=0}{s+=$8}END{print s}'

To do this with multiple columns, you have to use multiple variables (for example, $a, $b, $c, ...).

Answer 4

You can use something like this:

(awk '{if($8=="N*dN")next}{print 0$8}' data.txt | tr '\n' '+'; echo 0) | bc -l

{if($8=="N*dN")next} discard useless headers

{print 0$8} prints the 8th field with a leading '0' (will replace empty fields with 0).

tr '\n' '+'; echo 0 will replace end of lines with '+' and and a trailing 0

bc -l will sum all that