6

I have a text file with numbers in second column and the numbers have thousand-separator and decimal-separator:

2014-01     2,277.40
2014-02     2,282.20
2014-03     3,047.90
2014-04     4,127.60
2014-05     5,117.60    

I need to sum the numbers, but the following awk syntax does not work:

awk '{X=X+$2} END {print X}' file

It returns 16, which is obviously wrong. How can I tell awk to read the number as 2277.40 instead of 2,277.40?

  • 2
    Why not simply say: tr -d ',' < file | awk '{X=X+$2} END {print X}'? – devnull Feb 16 '14 at 9:52
  • In fact, gawk (which is aliased as awk on many systems today) has an option --use-lc-numeric which works for parsing the decimal point, so in fact you could use LC_NUMERIC=en_US gawk --use-lc-numeric .... Unfortunatelly this does NOT work for parsing thousand separator, despite it is also part of the LC_NUMERIC setting (quite confusing). So you have to resort to one of "dirty" workarounds below. I would consider this an gawk bug though. – Tomas Feb 19 '14 at 6:27
7
+50

Note that although you need to remove the commas from your input before adding the values to your total, but awk is happy to print your results with or without thousands separators. As an example, if you use the following code: look at fmt variable defined in code.

Your Input :

$ cat file
2014-01     2,277.40
2014-02     2,282.20
2014-03     3,047.90
2014-04     4,127.60
2014-05     5,117.60    

Awk Code :

$ awk '{gsub(/,/,"",$2);sum+=$2}END{printf(fmt,sum)}' fmt="%'6.3f\n" file

Resulting :

16,852.700

If you want to try this on a Solaris/SunOS system, change awk at the start of this script to /usr/xpg4/bin/awk ,/usr/xpg6/bin/awk , or nawk .

Hope this will be useful.

  • Very nice awk formatting trick. – erik Feb 19 '14 at 1:49
  • I get following error on my Debian machine: awk: run time error: improper conversion(number 1) in printf("%'6.3f – Martin Vegter Feb 19 '14 at 23:11
11

Just remove the commas from the field you're parsing:

awk '{sub(",", "", $2); X+=$2} END {print X}' file

This started as a quick-and-dirty solution, but it seems like there actually is no other way. From the AWK Manual:

A string is converted to a number by interpreting a numeric prefix of the string as numerals: "2.5" converts to 2.5, "1e3" converts to 1000, and "25fix" has a numeric value of 25. Strings that can't be interpreted as valid numbers are converted to zero.

And while the format for number-to-string conversion can be set via the CONVFMT variable, there is no such option for string-to-number conversions.[1]


Even gawk's strtonum(), while honoring the decimal separator defined by the system's locale, ignores (i.e. cuts off at) thousand-separators.

  • I was going to comment with that same awk manual snippet, when I noticed the "This post has been edited" on top. +1, I think this answer should be accepted. – GnP Feb 14 '14 at 21:07
  • this doesn't work for many locales – Neil McGuigan Aug 19 '16 at 18:23
3

I like using bc for things like this. Granted you're only adding but this pattern is useful if you need to do more sophisticated things that require bc for doing the calculations.

$ paste -s -d+ <(awk '{gsub(/,/,"",$2); print $2}' sample.txt) | bc
16852.70
  • Why invoke awk to remove the comma and not have it do the math? What complicated math can bc do that awk (or gnu awk) can't? – Ricky Beam Feb 19 '14 at 1:05
  • @RickyBeam - base conversions is a big one. I find awk a sloppy language being another, and I use Perl daily. When developing my code in bc it's a lot easier to debug it vs. debugging an awk script. This is obviously my opinion, but the above pattern is easier to use to solve a variety of problems vs. having to custom craft awk solutions that feel more one off to me. Is that enough to justify your DV? – slm Feb 19 '14 at 1:16
2

@Martin Vegter it must be mawk so you are getting error, see I can produce same error

[akshay@aix tmp]$ awk -vfmt="%'6.3f\n" 'BEGIN{sum=12052255;printf(fmt,sum)}'
12,052,255.000

[akshay@aix tmp]$ mawk -vfmt="%'6.3f\n" 'BEGIN{sum=12052255;printf(fmt,sum)}'
mawk: run time error: improper conversion(number 1) in printf("%'6.3f")
FILENAME="" FNR=0 NR=0

[akshay@aix tmp]$ which awk
/bin/awk

[akshay@aix tmp]$ which mawk
/usr/bin/mawk

Install gawk and try

  • yes, it is /usr/bin/mawk on my system. Is there any way to make it work with mawk rather that changing to gawk ? – Martin Vegter Feb 20 '14 at 9:48
  • @MartinVegter : Actually mawk is a minimal-featured awk designed for speed of execution over functionality. You should not expect it to behave exactly the same as gawk or a POSIX awk, infact mawk has got lots of bug also, if ubuntu user you can easily install using sudo apt-get install gawk if rhel/centos/fedora you can use yum or rpm packages – Akshay Hegde Feb 20 '14 at 11:00

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.