Search code examples
unixawkexponential

get the sum of a column in dat file in unix in exponential form

I have a dat file for which sample is given below:

NYEFP 191800034103    20130225912131 JPYCNTG1 JPY 20130225POSTING from GMI                        QTY*MULT  -676500000.00SYS EFPSP                                      
NYEFP 291800034103    20130225912131 JPYCNTR1 JPY 20130225POSTING from GMI                        -(QTY*MU   676500000.00SYS EFPSP                                      
NYFX  191800034103    20130225912131 AUDCNTG2 AUD 20130225POSTING from GMI                        QTY*MULT -1200000000.00SYS FWDLL                                      
NYFX  291800034103    20130225912131 AUDCNTR2 AUD 20130225POSTING from GMI                        -(QTY*MU  1200000000.00SYS FWDLL

I want to get the sum of 10th field numeric part for all negative values together and for all positive values together. In this case SUM(1200000000+676500000) and negative sums. Also I want this is exponential form.

Any ideas will be of great value

Solution

  • This can be a way:

    awk '{if ($10+0>0) {a+=$10+0} else {b+=$10+0}} 
      END{printf "positive: %e \nnegative: %e\n", a, b}' file

    Where %e stands for exponential format.

    Note that $10+0 expression is done to get rid of the text contained in the field. Hence, 676500000.00SYS becomes 676500000.00.

    Test

    $ awk '{if ($10+0>0) {a+=$10+0} else {b+=$10+0}} 
  END{printf "positive: %e \nnegative: %e\n", a, b}' a
positive: 1.876500e+09 
negative: -1.876500e+09