Need full decimals in awk sum when summing decimal values

Question

I've the following test.txt file -

var,value
a,1.1234
b,1.7896749
c,2.4982
d,1.2976232

When I use the following command -

awk -F ',' '{SUM+=$2}END{print SUM}' test.txt

it prints 6.7089

But the result with all the decimal places is 6.7088981 How can write the command so that it prints all the decimal places in the result, not just in this specific case but in general. For example, if the result has 10 decimal places, it should print all the 10 decimal places? If the result has only 5 decimal places, it should print only 5 decimal places. The OS that I'm using is Red Hat Enterprise Linux Server 7.7

Answer 1

Upon printing, non-integer numbers are converted to decimal string representation using the OFMT special variable which contains a printf format specification (by default %.6g). You can change it to %.17g to get the maximum precision of IEEE 754 double precision binary floating point numbers (as used internally by most awk implementations on most systems). Another variable (CONVFMT) is used in the other cases where a floating point numbers are implicitly converted to strings (like when you concatenate a number with something else)

You won't get more precision with those doubles, there's no point going beyond 17. Already with 17, you're likely to see some artifacts. 15 significant digits may be better if you don't need that much precision.

$ awk -v OFMT=%.17g -F ',' '{SUM+=$2};END{print SUM}' < file
6.7088981000000008
$ awk -v OFMT=%.15g -F ',' '{SUM+=$2};END{print SUM}' < file
6.7088981

While OFMT affects all printed floating point numbers, you could also use printf directly to print numbers with the required precision.

$ awk  -F ',' '{SUM+=$2};END{printf "%.15g\n", SUM}' < file
6.7088981

The GNU implementation of awk, since version 4.1.0 can also be compiled with arbitrary precision arithmetics support (see info gawk 'Arbitrary Precision Arithmetic'). If that's the case on your system, you could also do:

gawk -M -v PREC=256 -v OFMT=%.60g -F ',' '{SUM+=$2};END{print SUM}' < file

Example:

$ printf 'x,%s\n' 1 1000000000000000000000000000000000.00000000001 |
> gawk -v OFMT=%.15g -F ',' '{SUM+=$2};END{print SUM}'
999999999999999945575230987042816
$ printf 'x,%s\n' 1 1000000000000000000000000000000000.00000000001 |
> gawk -M -v PREC=256 -v OFMT=%.60g -F ',' '{SUM+=$2};END{print SUM}'
1000000000000000000000000000000001.00000000001

Another approach here could be to use bc (assuming those numbers are always expressed like that (0.001, not 1e-3 for instance)):

<file tail -n+2 | # skip header
  cut -d, -f2   | # extract second field
  paste -sd + - | # join input lines with +
  bc

The number of digits after the . will be the maximum in any input record.

Answer 2

GNU datamash displays the number in the desired precision with its default output settings:

$ datamash --header-in -t, sum 2 < test.txt
6.7088981

Or awk with a different OFMT with more precision:

$ awk -F, -v OFMT='%.10g' '{sum += $2} END { print sum }' test.txt
6.7088981

but see Is Floating-Point Math Broken?. The number of digits after the decimal point when a floating point number is displayed in base 10 doesn't always correspond to the IEEE754, base 2 representation that (most) computers use.

Answer 3

As already discussed, floating point arithmetic is the problem when trying to get the intuitive answer but if you know your input can only have up to, say, 3 digits before the "." and, say, 9 after it then you could convert your numbers to decimals using string manipulation, then sum those to avoid the floating point arithmetic issue, and then convert the result back to FP again before printing, e.g.:

$ cat tst.awk
BEGIN {
    FS = ","
    bef = 3
    aft = 9
}
NR>1 {
    split($2,f,".")
    val = sprintf("%*s%-*s",bef,f[1],aft,f[2])
    gsub(/ /,0,val)
    sum += val
}
END {
    sub(".{"aft"}$",".&",sum)
    sub(/0+$/,"",sum)
    print sum
}

$ awk -f tst.awk file
6.7088981

If 3 and/or 9 aren't large enough numbers for you, pick other numbers or do a 2-pass approach that figures out the max for each on the first pass, e.g.:

$ cat tst.awk
BEGIN { FS = "," }
FNR==1 { next }
{ split($2,f,".") }
NR==FNR {
    bef = (length(f[1]) > bef ? length(f[1]) : bef)
    aft = (length(f[2]) > aft ? length(f[2]) : aft)
    next
}
{
    val = sprintf("%*s%-*s",bef,f[1],aft,f[2])
    gsub(/ /,0,val)
    sum += val
}
END {
    sub(".{"aft"}$",".&",sum)
    sub(/0+$/,"",sum)
    print sum
}

$ awk -f tst.awk file file
6.7088981