Shell Programming and Scripting

I have a requirement to sum all records in a column in a file and get the total sum value .There are 15 million records in the file.Data in the file has 2 decimal places.

I use the below AWK function

Code:

cut -d ","  -f4 data.txt|awk ' {sum+=$1 } END { Printf("%.2f\n",sum)}'

I get the below answer

Code:

375173879877.18

But the expected answer is

Code:

375173879877.15

Please advice why there is a difference of 3 cents

Do i need to use any other format other than float

Check your math!

BTW, $0 is faster than $1 but why not skip the cut and awk on the 4th field?

Code:

awk -F,  ' {sum+=$4 } END { Printf("%.2f\n",sum)}' < data.txt

Mike

Thanks Mike.
But this does not solve my problem i still get 3 cent difference

BTW if i use BC by reading the entire file row by row, i get the correct answer, but it takes 6 hours to run, as there are 15 million records


total=0
for a in `cat data.txt`;do
total=`echo "scale=2;($total+$a)/1"|bc`

done
echo $total

You may find the solution in this thread:

Awk defaults to double. Must be happening in the decimal to binary conversion. Your original data is too how many decimal places?

Try multiplying by 10 or 100 or 1000 before the adding and dividing at the end. Try rounding to a certain precizsion before adding.

Mike

---------- Post updated at 08:07 PM ---------- Previous update was at 07:33 PM ----------

I do floating addition, subtraction, or comparison in BASH all the time by string multiplication and division (moving the decimal point) to convert them to integers.

With a huge file like that your key to speed is going to be avoiding creating and closing subshells in and explicit or implicit loops by calling non built-in utilities. Stick with built-ins only and you will be fast, even with huge files. AWK does not have integers so you're going to have to use BASH.

Using built-in string functions like printf zero pad everything out to the maximum precision, "multiply" by dropping the decimal point, then use integer addition and then at the end "divide" by adding the decimal point back in.

123.45 + 678.9 = (12345 + 67890)/100 All integer math in the loop!

Mike

The standards say that awk uses double precision floating point arithmetic when performing arithmetic calculations. That type gives you 15 to 17 significant digits for any particular value, but since some decimal values aren't exactly representable in binary, adding sequences of floating point values can result in values that are not accurate to a full 15 significant digits.

You didn't mention anything about the ranges of values in field 4 in your input file. If you are adding numbers like 375173879877.15 (appearing once) and .00001 (appearing 30,000 times) you need more than 17 significant digits when you add the large number and any of the small numbers.

If you need arbitrary precision decimal arithmetic, you need to use something like bc instead of awk to perform your calculations. Here is one way to do it using awk and bc:

Code:

awk '
BEGIN {	print "sum=0"}
{	print "sum += " $4
	asum += $4
}
END {	printf "sum\nscale=2\nsum=sum/1\nsum\n"
	print asum > "awk.out"
	printf("%.2f\n", asum) > "awk.out"
}' data.txt | bc

The code shown in red is not needed to use bc to perform the calculations; it is just here to demonstrate that awk has limited precision for arithmetic operations.

If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk, /usr/xpg6/bin/awk, or nawk.
If data.txt contains:

Code:

0 1 2 5
0 1 2 50
0 1 2 500
0 1 2 5000
0 1 2 50000
0 1 2 500000
0 1 2 5000000
0 1 2 50000000
0 1 2 500000000
0 1 2 5000000000
0 1 2 50000000000
0 1 2 500000000000
0 1 2 5000000000000
0 1 2 50000000000000
0 1 2 500000000000000
0 1 2 5000000000000000
0 1 2 50000000000000000
0 1 2 500000000000000000
0 1 2 5000000000000000000
0 1 2 50000000000000000000
0 1 2 500000000000000000000
0 1 2 .5
0 1 2 .05
0 1 2 .005
0 1 2 .0005
0 1 2 .00005
0 1 2 .000005
0 1 2 .0000005
0 1 2 .00000005
0 1 2 .000000005
0 1 2 .0000000005
0 1 2 .00000000005
0 1 2 .000000000005

the output produced (showing the results of using bc to perform the calculations (both with unlimited precision and truncated to two digits after the decimal point) is:

Code:

555555555555555555555.555555555555
555555555555555555555.55

and the results written to awk.out (showing the results of using awk to perform the calculations is:

Code:

555555555555555540992
555555555555555540992.00

(which shows that with these input values, awk gets 16 digits at the start of the output correct).

Thanks Mike multiplying 100 and dividing by 100 work