Home > awk, bash > awk – Formatting numbers to be comma separated

awk – Formatting numbers to be comma separated

Problem. How I can format numbers so that they come out comma separated?

From:
$less data.csv

Name, Day Total, Week Earnings, Ratio, Percentage(%)
abc, 12, 94090, 34.9, 558765.09
def, 14, 34802, 44.9, 558769.01
ghi, 15, 2403, 14.7, 558767.09

To:
$less results.data

Name| Day Total| Week Earnings| Ratio| Percentage(%)
abc|12|94,090|34.90|558,765.09|
def|14|34,802|44.90|558,769.01|
ghi|15|2,403|14.70|558,767.00|

Solution:

$awk -v FS="," -f comma.awk data.csv > results.data

$less comma.awk

#!/usr/bin/awk

{
    if (NR == 1) # Skip the first record which is the heading.
    {
        gsub(",","|",$0); # Replace the commas with pipes.
        print $0"|"
    }
    else
    {
        for (i=1; i <= NF; i++)
            if ( i == 1)
                # Print the operator name
                printf ("%s|", $i);
            else if ( i > 3)
                # For the Ratios and Percentages show 
                # decimal points and commas 
                # at the thousands.
                printf ("%'.2f|", $i);
            else
                # For the others show commas at the thousands.
                printf ("%'d|", $i);

            # Adds a new line to the end.
            print ""
    }
}

printf (“%’d|”, $i) – The quote after the % sign is what tells awk to add commas to the number.

  • ‘ For decimal conversion (i, d, u, f, F, g, G) the output is to be grouped with thousands’ grouping characters if the locale information indicates any. Note that many versions of gcc(1) cannot parse this option and will issue a warning. SUSv2 does not include %’F. glibc 2.2 adds.

source: http://real-world-systems.com/docs/awk.1.html

NB
If you get the following error when running the given awk command.

awk: run time error: improper conversion(number 1) in printf(“%’d|”)
FILENAME=”data.csv” FNR=2 NR=2

Check if you are running mawk. If you are install gawk.
On Ubuntu you may use:

$sudo apt-get install gawk

NB http://www.gnu.org/s/gawk/manual/html_node/Format-Modifiers.html

A single quote or apostrophe character is a POSIX extension to ISO C. It indicates that the integer part of a floating point value, or the entire part of an integer decimal value, should have a thousands-separator character in it. This only works in locales that support such characters. For example:

$ cat thousands.awk #Show source program
-| BEGIN { printf “%’d\n”, 1234567 }
$ LC_ALL=C gawk -f thousands.awk
-| 1234567 #Results in “C” locale
$ LC_ALL=en_US.UTF-8 gawk -f thousands.awk
-| 1,234,567 #Results in US English UTF locale

Trouble shooting.
If the code above does not work, try changing the locale. I am using GNU awk 3.1.5.

$ LC_ALL=en_US.utf8 env awk -v FS="," -f comma.awk data.csv > results.data
Advertisements
Categories: awk, bash Tags: , ,
  1. Philip Mann
    April 13, 2012 at 12:45 am

    Thanks for this very useful post. This feature is not well documented anywhere else that I have seen. I’ll check back for more from time to time.

    • April 16, 2012 at 5:34 am

      That is exactly why I posted it. I had a hard time getting it to work. Glad to hear that it helped.

  2. January 3, 2013 at 4:16 pm

    I had a similar problem and wrote a small function for that. see http://wp.me/p30D0b-1L

  3. thomas
    April 18, 2017 at 10:03 am

    I want to put comma and decimal on the amount R10012301

    • April 19, 2017 at 7:17 am

      Hi, could you share an example of how you would like R10012301 to be when it has commas and decimals?

      Assuming that the result you want is R10,012,301.00

      Then

      $ less data.csv
      10012301
      100123.01
      
      $ less comma.awk
      #!/usr/bin/awk
      {
           printf("R%'.2f\n",$1)
      }
      
      $ awk -f comma.awk data.csv
      R10,012,301.00
      R100,123.01
      
  1. January 3, 2013 at 4:13 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s