Round off Number in File

Shell Programming and Scripting
1

Hi Guys,

i am having a csv file where i need to round off numerical column to 2 decimal precision in specific columns. i need to ignore the first two line i.e the header columns and manipulate rest of the lines of the csv file. My columns are specific i.e i need to round off only 2nd,4th and 6th field of the file without affecting other fields.

heading
head1,head2,head3,head4,head5,head6
aa,12,bb,-123,cc,0
bb,-12.456666,zz,-123.23333,cc,12.1

i need the below output

heading
head1,head2,head3,head4,head5,head6
aa,12.00,bb,-123.00,cc,0.00
bb,-12.45,zz,-123.23,cc,12.10

I have tried with below

head -2 file.csv>file.tmp

awk 'BEGIN{OFS=FS=","}
{
 $2=sprintf("%.2f",$2) 
$4=sprintf("%.2f",$4)
$6=sprintf("%.2f",$6)  
}1' file.csv >file_conv.tmp

tail +2 file_conv.tmp>>file.tmp
mv file.tmp > file.csv
2

And what did it do?

3

Try

awk -F, 'NR>2{ for ( i = 2 ; i<=6; i=i+2 ) { $i=sprintf("%.2f",$i);   }}1' OFS="," file
4

Try this small change to your own code:

awk 'BEGIN{OFS=FS=","}
NR>2 {
  $2=sprintf("%.2f",$2) 
  $4=sprintf("%.2f",$4)
  $6=sprintf("%.2f",$6)  
}1' file.csv
5

Just a few suggestion its just a sample file my csv is having more than 6 fields so 6th field is not the last field and also my file size is huge having lines of more than 2 lakh will it be faster using awk

---------- Post updated at 12:45 PM ---------- Previous update was at 12:43 PM ----------

Hi is it way to perform in better way i mean taking performance in do consideration since i would process more than 2 lakh line records

6

I didnt get your point, if you want all the even number fields to be rounded, just replace the 6 in the for loop by NF , ,, awk is the fastest in these type of manipulations as I know.. It doesnt matter how many fields you have,, this script should work as long as you know which fields to round...

7

Hi rohit_shinez, 200,000 lines isn't that much. I would just try it out and see if it is fast enough...

8

ideally its not all the even fields i would say its like some specific fields in my file but not the 6th field as last filed

for taking consider the below file

heading
head1,head2,head3,head4,head5,head6
aa,12,bb,-123,cc,0,aa,12,bb,-1231231.111,cc,0,aa,12,bb,-123,cc,abnnn

---------- Post updated at 12:51 PM ---------- Previous update was at 12:50 PM ----------

I mean will there be any better approach apart from mine

9

It seems like a good approach to me..

10

Is it possible to make use of the snippet in generic function way like

calling the function name say round_number 0.2decimal col2 col4 col5 col7 ....

11

Try also

awk     'BEGIN          {OFS=FS=","}
         NR>2           {$2+=0.0001
                         $4+=0.0001
                         $6+=0.0001
                        }
         1
        ' CONVFMT="%.2f" file
heading
head1,head2,head3,head4,head5,head6
aa,12.00,bb,-123.00,cc,0.00
bb,-12.46,zz,-123.23,cc,12.10

---------- Post updated at 19:23 ---------- Previous update was at 19:14 ----------

Strange enough, this is slower than the explicit sprint approach. Of course, it has to run through the format conversion routines after each of the addition operations...

12

Thanks rudic

As I said in my previous post will it be able to make it in generic function based script like

Round_function decimalvalue col1 col2 ..... ...

So that I can use wat decimal precision to round off and which all columns needs to be changed

13

Try

awk     'BEGIN          {OFS=FS=","}

         function roundit(FIELDS)
                        {split (FIELDS, TMP)
                         for (t in TMP) $(TMP[t])+=0.0001
                        }

         NR>2           {roundit("2 4 6")
                        }
         1
        ' CONVFMT="%.2f" file

Surprisingly enough, this nearly cuts the time consumed in half ...!

14

Round it function needs to called like this

Roundit decimal_precision fields Filename

So that i can use it in generic way of how many decimal precision you need and which all columns you want to make the changes

awk     'BEGIN          {OFS=FS=","}

         function roundit(FIELDS)
                        {split (FIELDS, TMP)
                         for (t in TMP) $(TMP[t])+=0.0001
                        }

         NR>2           {roundit("$1 $2 $3")
                        }
         1
        ' CONVFMT="%.2f" $4