Convert scientific notation to decimal in multiple fields

Question

The following works great on my data in column 12 but I have over 70 columns that are not all the same and I need to output all of the columns, the converted ones replacing the scientific values.

 awk -F',' '{printf "%.41f\n", $12}' $file

Thanks

This is one line..

2012-07-01T21:59:50,2012-07-01T21:59:00,1817,22901,264,283,549,1,2012-06-24T13:20:00,2.600000000000000e+001,4.152327506554059e+001,-7.893523806678388e+001,5.447572631835938e+002,2.093000000000000e+003,5.295000000000000e+003,1,194733,1.647400093078613e+001,31047680,1152540,29895140,4738,1.586914062500000e+000,-1.150000000000000e+002,0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,3.606000000000000e+003,0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,4.557073364257813e+002,0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,11,0.000000000000000e+000,2.000000000000000e+000,0,0,0,0,4.466836981009692e-004,0.000000000000000e+000,0.000000000000000e+000,0.000000000000000e+000,8,0,840,1,600,1,6,1,1,1,5,2,2,2,1,1,1,1,4854347,0,-

UPDATE

This is working for the non converted output. I am having a bit of trouble inserting an else if statement for some reason. Everything seems to give me a syntax error in a file or on cli.

awk -F',' '{for (i=1;i<=NF;i++) {if (i <= 9||i == 16||i == 17||i == 19||i == 20||i == 21||i == 22|| i == 40|| i == 43||i == 44||i == 45||i == 46||i >= 51) printf ($i",")};}' $file

I would like to insert the following statement into the code above??

else if (i == 10) printf ("%.41f", $i)

SOLVED

Got it worked out. Thanks for all the great ideas. I can't seem to make it work in a file with awk -f but on the command line this is working great. I put this one liner in my program.

awk -F',' '{for (i=1;i<=NF;i++) {if (i <= 9||i == 16||i == 17||i >= 19&&i <= 22|| i == 40|| i >= 43&&i <= 46||i >= 51&&i <= 70) printf($i","); else if (i == 10||i == 18) printf("%.2f,", $i); else if (i == 11||i == 12) printf("%.41f,", $i); else if (i == 13) printf("%.1f,", $i); else if (i == 14||i == 15||i >= 24&&i <= 46) printf ("%d,", $i); else if (i == 23) printf("%.4f,", $i); else if (i >= 47&&i <= 50) printf("%.6f,", $i); if (i == 71) printf ($i"\n")};}'

RESULT

2012-07-01T21:59:50,2012-07-01T21:59:00,1817,22901,264,283,549,1,2012-06-24T13:20:00,26.00,41.52327506554058800247730687260627746582031,-78.93523806678388154978165403008460998535156,544.8,2093,5295,1,194733,16.47,31047680,1152540,29895140,4738,1.5869,-115,0,0,0,0,0,0,0,3606,0,0,0,455,0,0,0,11,0,2,0,0,0,0,0.000447,0.000000,0.000000,0.000000,8,0,840,1,600,1,6,1,1,1,5,2,2,2,1,1,1,1,4854347,0,-

I'd definitely not be doing this on the command line. You might want to check out @DennisWilliamson approach below, looks pretty slick — Levon, Jul 07 '12 at 19:31

Dennis Williamson · Accepted Answer · 2012-07-08T01:08:04.953

You can do regex matching in a loop to choose the format for each field since numbers are also strings in AWK:

#!/usr/bin/awk -f
BEGIN {
    d = "[[:digit:]]"
    OFS = FS = ","
}
{
    delim = ""
    for (i = 1; i <= NF; i++) {
        if ($i ~ d "e+" d d d "$") {
            printf "%s%.41f", delim, $i
        }
        else {
            printf "%s%s", delim, $i
        }
        delim = OFS
    }
    printf "\n"
}

Edit:

I've changed the version above so you can see how it would be used in a file as an AWK script. Save it (I'll call it "scinote") and set it as executable chmod u+x scinote, then you can run it like this: ./scinote inputfile

I've also modified the latest version you added to your question to make it a little simpler and so it's ready to go into a script file as above.

#!/usr/bin/awk -f
BEGIN {
    plainlist = "16 17 19 20 21 22 40 43 44 45 46"
    split(plainlist, arr)
    for (i in arr) {
        plainfmt[arr[i]] = "%s"
    }
    OFS = FS = ","
}
{
    delim = ""
    for (i = 1; i <= NF; i++) {
        printf "%s", delim
        if (i <= 9 || i in plainfmt || i >= 51) {
            printf plainfmt[i], $i
        }
        else if (i == 10) {
            printf "%.41f", $i
        }
        else if (i == 12) {
            printf "%.12f", $i
        }
        delim = OFS
    }
    printf "\n"
}

If you had more fields with other formats (rather than just one per), you could do something similar to the plainfmt array.

`cat $file | awk -f ~/bin/so.awk awk: ./so.awk:16: } awk: ./so.awk:16: ^ syntax error` I am using cygwin right now on windows if that makes a difference. — Road King, Jul 07 '12 at 19:46
@RoadKing: I forgot to close the `BEGIN` block. See my edited answer. — Dennis Williamson, Jul 07 '12 at 20:18
@RoadKing: I've edited my answer. I hope you find some of the new information useful. — Dennis Williamson, Jul 08 '12 at 01:08
Yes, this is the best solution. Got my code running. I will be incorporating this into the final code. Think I understand it. Thanks — Road King, Jul 08 '12 at 01:43

Levon · Answer 2 · 2012-07-07T19:26:59.420

0

You could always loop through all of your data fields and use them in your printf. For a simple file just to test the mechanics you could try this:

 awk '{for (i=1; i<=NF; i++) printf("%d = %s\n", i, $i);}' data.txt

Note that -F is not set here, so fields will be split by whitepace.

NF is the predefined variable for number of fields on a line, fields start with 1 (e.g., $1, $2, etc until $NF). $0 is the whole line.

So for your example this may work:

  awk -F',' '{for (i=1; i<=NF; i++) printf "%.41f\n", $i}' $file

Update based on comment below (not on a system test the syntax):

If you have certain fields that need to be treated differently, you may have to resort to a switch statement or an if-statement to treat different fields differently. This would be easier if you stored your script in a file, let's call it so.awk and invoked it like this:

  awk -f so.awk $file

Your script might contain something along these lines:

BEGIN{ FS=',' }
{ for (i=1; i<=NF; i++)
    {
      if (i == 20 || i == 22|| i == 30)
        printf( " .. ", $i)
      else if ( i == 13 || i == 24)
        printf( " ....", $i)
      etc.
    }
}

You can of course also use if (i > 2) ... or other ranges to avoid having to list out every single field if possible.

As an alternative to this series of if-statements see the switch statement mentioned above.

edited Jul 07 '12 at 19:26

answered Jul 07 '12 at 17:33

Levon

138,105
33
200
191

I changed it to this to get all the decimal points I need and reversed %d and %s but some fields are not needing to be converted so they become distorted and some fields have too many decimals....awk -F',' '{for (i=1;i<=NF;i++) printf ("%s = %.41f\n",i, $i);}' – Road King Jul 07 '12 at 17:53
@RoadKing I added some more information to my answer - hope that's helfpul. If you have additional information about the problem, it's best to update your original post since not everyone will look through all the comments, plus the formatting in comments is not very good (see code). – Levon Jul 07 '12 at 18:06
Your `if` statements will cause some fields to be printed twice or three times. Change the `printf`'s to `printf( " .. ", $i)`. – Dennis Williamson Jul 07 '12 at 19:21
Guess I don't understand this.. printf( " .. ", $i). It just prints dots for me. – Road King Jul 07 '12 at 19:41
@RoadKing You *need* to put your specific formatting instructions in there for each field that you want to print. The whole idea behind using the if-statements was that you wanted to treat i.e., print different fields differently. So since I don't know what formatting you want for a given field I just left ... place holders for you. so in some cases maybe you want just %s, in others %.24f etc .. only you know what's right :) – Levon Jul 07 '12 at 19:47
OK I see. The problem now is that I have the non converted fields outputting as expected. I can't insert an else if statement into the code above without a syntax error. Still trying – Road King Jul 07 '12 at 19:58

Convert scientific notation to decimal in multiple fields

2 Answers2

Linked

Related