1

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

  • How shall awk detect the header and data areas? Maybe you can pass the relevant line numbers to tha awk call, might be easier. – Hauke Laging Aug 19 '17 at 21:09
  • I tried with the column number, but I don't know how to incorporate the header factor. The file has more tables with 8 rows but not the right header, so I need to specify that. Awk may not be the right choice. – Manuel Aug 19 '17 at 21:14
  • Is the content of the header line guaranteed to be always the same? – Hauke Laging Aug 19 '17 at 21:21
  • It is guaranteed to be always the same. – Manuel Aug 19 '17 at 21:26
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
  • Worked great! I tried to adjust the command to do the same with SdS like this: awk 'f{s+=$9;f++} ($9=="SdS"){f=1} (f>4){print s;f=s=0}' But it did'nt work.. What am I doing wrong? – Manuel Aug 23 '17 at 19:40
  • Apparently the asterisc doesn't show in my comment, but I put it. – Manuel Aug 23 '17 at 19:47
  • It does work to me – αғsнιη Aug 24 '17 at 1:41
1

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
0

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, ...).

0

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

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.