Using awk to place decimal points at proper position


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Using awk to place decimal points at proper position
# 1  
Using awk to place decimal points at proper position

Hi,

I have one input file which is delimited by pipe. I want to put decimal points in this input file at particular position in particular column and also get the negative sign (if any) at start of that column.

Code:
$ cat Input_file.txt
11|10102693|1|20151202|10263204|20151127|N|0001
11|20100142-|2|20151202|21411783-|20151127|3|0005

And, There is one comma separated file which mentions "Column number","length of the column" and "position of decimal point".

Code:
$ cat decimal.csv
2,8,3
5,8,4

Expected output is as follows:
Code:
11|101.02693|1|20151202|1026.3204|20151127|N|0001
11|-201.00142|2|20151202|-2141.1783|20151127|3|0005

I have tried below awk code but, I am not getting expected output:
Code:
$ awk 'FNR == NR {A[NR]=$1;B[NR]=$2;C[NR]=$3;CNT=NR;next}{for(i=1;i<=CNT;i++) $A[i]=substr($A[i],$B[i]+1,1) substr($A[i],1,$C[i]) "." substr($A[i],$C[i]+1,$B[i]-$C[i])} {print $0}' FS="," decimal.csv FS="|" OFS="|" Input_file.txt
11|01.|1|20151202|010263204.|20151127|N|0001
11|120.100|2|20151202|721411783-.|20151127|3|0005

Can someone please help me to figure out what I am doing wrong here and correct me.
# 2  
Hello Prathmesh,

Could you please try following and let me know if this helps you.
Code:
awk 'FNR==NR{A[++i]=$0;next} {for(j=1;j<=i;j++){split(A[j], array,",");if(length($array[1])==array[2]){$array[1]=substr($array[1],1,array[3]) "." substr($array[1],array[3]+1);} else {q="-" substr($array[1],1,array[2]);$array[1]=substr(q,1,array[3]) "." substr(q,array[3]+1);}};print}' decimal.csv  FS="|" OFS="|" Input_file

Output will be as follows.
Code:
11|101.02693|1|20151202|1026.3204|20151127|N|0001
11|-20.100142|2|20151202|-214.11783|20151127|3|0005

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
# 3  
Quote:
Originally Posted by RavinderSingh13
Hello Prathmesh,

Could you please try following and let me know if this helps you.
Code:
awk 'FNR==NR{A[++i]=$0;next} {for(j=1;j<=i;j++){split(A[j], array,",");if(length($array[1])==array[2]){$array[1]=substr($array[1],1,array[3]) "." substr($array[1],array[3]+1);} else {q="-" substr($array[1],1,array[2]);$array[1]=substr(q,1,array[3]) "." substr(q,array[3]+1);}};print}' decimal.csv  FS="|" OFS="|" Input_file

Output will be as follows.
Code:
11|101.02693|1|20151202|1026.3204|20151127|N|0001
11|-20.100142|2|20151202|-214.11783|20151127|3|0005

Thanks,
R. Singh
Thanks. But, Second line of the output seems to be wrong. Output should be as follows:
Code:
11|101.02693|1|20151202|1026.3204|20151127|N|0001
11|-201.00142|2|20151202|-2141.1783|20151127|3|0005

# 4  
Hello Prathmesh,

A very minor change with code as follows may help you in same.
Code:
awk 'FNR==NR{A[++i]=$0;next} {for(j=1;j<=i;j++){split(A[j], array,",");if(length($array[1])==array[2]){$array[1]=substr($array[1],1,array[3]) "." substr($array[1],array[3]+1);} else {q="-" substr($array[1],1,array[2]);$array[1]=substr(q,1,array[3]+1) "." substr(q,array[3]+2);}};print}' decimal.csv  FS="|" OFS="|" Input_file

Output will be as follows.
Code:
11|101.02693|1|20151202|1026.3204|20151127|N|0001
11|-201.00142|2|20151202|-2141.1783|20151127|3|0005

EDIT: Adding a non-one liner form for solution on same now.
Code:
awk 'FNR==NR{
                A[++i]=$0;
                next
            }
            {
                for(j=1;j<=i;j++){
                                        split(A[j], array,",");
                                        if(length($array[1])==array[2]){
                                                                        $array[1]=substr($array[1],1,array[3]) "." substr($array[1],array[3]+1);
                                                                       }
                                        else                           {
                                                                        q="-" substr($array[1],1,array[2]);$array[1]=substr(q,1,array[3]+1) "." substr(q,array[3]+2);}};
                                                                        print
                                                                       }
    ' decimal.csv  FS="|" OFS="|" Input_file

Thanks,
R. Singh

Last edited by RavinderSingh13; 02-24-2016 at 11:36 AM.. Reason: Adding a non-one liner form of solution now.
This User Gave Thanks to RavinderSingh13 For This Post:
# 5  
Quote:
Originally Posted by RavinderSingh13
Hello Prathmesh,

A very minor change with code as follows may help you in same.
Code:
awk 'FNR==NR{A[++i]=$0;next} {for(j=1;j<=i;j++){split(A[j], array,",");if(length($array[1])==array[2]){$array[1]=substr($array[1],1,array[3]) "." substr($array[1],array[3]+1);} else {q="-" substr($array[1],1,array[2]);$array[1]=substr(q,1,array[3]+1) "." substr(q,array[3]+2);}};print}' decimal.csv  FS="|" OFS="|" Input_file

Output will be as follows.
Code:
11|101.02693|1|20151202|1026.3204|20151127|N|0001
11|-201.00142|2|20151202|-2141.1783|20151127|3|0005

Thanks,
R. Singh
Thanks a lot. Can you please explain the code so that, it will be easier to understand.
# 6  
Hello Prathmesh,

Could you please go through following and let me know if this is helpful for you.
Code:
awk 'FNR==NR{                                                                                     #### FNR==NR condition will be TRUE when first file named decimal.csv is being read.
A[++i]=$0;                                                                                        #### When above condition is TRUE then create an array named A with index of variable i whose index is increasing every occurance whenever above condition is TRUE, so means till first file is being read completly.
next                                                                                              #### skipping all other statements now by using next keyword.
}
{
for(j=1;j<=i;j++){                                                                                #### starting for loop now which will start only when second file will be read and wen first file will be completly read then we will get a final valur of variable i so running the loop from j=1 to till i's value.
split(A[j], array,",");                                                                           #### splitting the value of array A whose delimiter is comma(,). This array is having the values of decimal.csv files, which by splitting into array I wil use it later in code.
if(length($array[1])==array[2]){                                                                  #### Now I am comparing the value of field $array[1] which is field mentioned in decimal.csv and value is in file Input_file with length of field provided in decimal.csv file if they are equal then do following actions.
$array[1]=substr($array[1],1,array[3]) "." substr($array[1],array[3]+1);                          #### Here I am RE-FORMING the field let's say 2nd and 5th(as per your decimal.csv) file by using substr utility of awk. which works on substr(LINE,starting point, ending point) etc.
}
else{                                                                                             #### In case length of field is NOT equal to length provided in decimal.csv file then do following actions.
q="-" substr($array[1],1,array[2]);$array[1]=substr(q,1,array[3]+1) "." substr(q,array[3]+2);}};  #### creating a variable named q whose value I am constructing by field values by file Input_file, more important placing dot(.) here as per requirement in between field's values.
print                                                                                             #### finally printing the line, not here it will print the new formatted values of fields which we have done in above steps.
}
' decimal.csv  FS="|" OFS="|" Input_file                                                          #### mentioning first file named decimal.csv then Field separator as | and Output field separator as | for Input_file here.

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
# 7  
Quote:
Originally Posted by RavinderSingh13
Hello Prathmesh,

Could you please go through following and let me know if this is helpful for you.
Code:
awk 'FNR==NR{                                                                                     #### FNR==NR condition will be TRUE when first file named decimal.csv is being read.
A[++i]=$0;                                                                                        #### When above condition is TRUE then create an array named A with index of variable i whose index is increasing every occurance whenever above condition is TRUE, so means till first file is being read completly.
next                                                                                              #### skipping all other statements now by using next keyword.
}
{
for(j=1;j<=i;j++){                                                                                #### starting for loop now which will start only when second file will be read and wen first file will be completly read then we will get a final valur of variable i so running the loop from j=1 to till i's value.
split(A[j], array,",");                                                                           #### splitting the value of array A whose delimiter is comma(,). This array is having the values of decimal.csv files, which by splitting into array I wil use it later in code.
if(length($array[1])==array[2]){                                                                  #### Now I am comparing the value of field $array[1] which is field mentioned in decimal.csv and value is in file Input_file with length of field provided in decimal.csv file if they are equal then do following actions.
$array[1]=substr($array[1],1,array[3]) "." substr($array[1],array[3]+1);                          #### Here I am RE-FORMING the field let's say 2nd and 5th(as per your decimal.csv) file by using substr utility of awk. which works on substr(LINE,starting point, ending point) etc.
}
else{                                                                                             #### In case length of field is NOT equal to length provided in decimal.csv file then do following actions.
q="-" substr($array[1],1,array[2]);$array[1]=substr(q,1,array[3]+1) "." substr(q,array[3]+2);}};  #### creating a variable named q whose value I am constructing by field values by file Input_file, more important placing dot(.) here as per requirement in between field's values.
print                                                                                             #### finally printing the line, not here it will print the new formatted values of fields which we have done in above steps.
}
' decimal.csv  FS="|" OFS="|" Input_file                                                          #### mentioning first file named decimal.csv then Field separator as | and Output field separator as | for Input_file here.

Thanks,
R. Singh
Thanks for very good explanation.
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #307
Difficulty: Easy
The common software-programming acronym 18N comes from the term Interlocalization;.
True or False?

10 More Discussions You Might Find Interesting

1. Homework & Coursework Questions

Grep commands for numbers w/decimal points

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted! 1. The problem statement, all variables and given/known data: Is there a grep commands for numbers w/decimal points Display lines for students with GPA above 3.69 but less... (3 Replies)
Discussion started by: jetoutant
3 Replies

2. UNIX for Dummies Questions & Answers

How to set decimal place in awk?

Dear all, I have a data test.txt as below. X22.30799720_T cg03868770 -0.5645412582127 2.4084685750406e-175 X22.30781182_A cg03868770 -0.5620426397492 3.5818034129169e-172 X22.30780724_C cg03868770 -0.5616890165605 2.9765569717858e-168 what I want is: X22.30799720_T cg03868770... (3 Replies)
Discussion started by: forevertl
3 Replies

3. Shell Programming and Scripting

awk if condition match and fix print decimal place

Hi All, I have problem in the middle of implementing to users, whereby the complaint is all about the decimal place which is too long. I need two decimal places only, but the outcome from command is always fixed to 6. See the sample : before: Sort Total Site Sort SortName Parts ... (3 Replies)
Discussion started by: horsepower
3 Replies

4. UNIX for Dummies Questions & Answers

How to control the decimal points for p-values in scientific format?

Dear all, I have a txt file with only one column which contains p values. My data looks like this: 5.04726976606584e-190 2.94065711152402e-189 2.94065711152402e-189 9.19932135717279e-176 1.09472516659859e-170 1.24974648916809e-170 0.1223974648916 0.9874974648916 ... what I want... (2 Replies)
Discussion started by: forevertl
2 Replies

5. UNIX for Dummies Questions & Answers

Help with decimal points

Hi All, I would like to set decimal point to 16 in the following bash script but it has syntax error at }: awk '{printf"%.16e", (a<500,a++,$1/(a*1.1212121212121229e-02))}' input.dat >output.datHow may I set it in the correct way please? Thank you very much! (6 Replies)
Discussion started by: sxiong
6 Replies

6. Shell Programming and Scripting

Shell arithmetic : operations on decimal points

i am having a varialbe a , which is input to my file i want to multiply this input with value .43, and assign it to variable b. i tried it as below: #!/bin/sh a=$1 b=`expr $1\*0.43` echo b=$b error : expr: non-integer argument Please tell me , how to do this. Thanks (10 Replies)
Discussion started by: rishifrnds
10 Replies

7. Shell Programming and Scripting

How to sort when there is variable length decimal points.?

Hi Experts, Quick quesion: I want to sort this in the file , but not working, when using # sort file name 305.932 456.470 456.469 456.468 456.467 172.089 456.467 456.466 456.465 111.573 111.578 111.572 111.572 87.175 87.174 75.898 (4 Replies)
Discussion started by: rveri
4 Replies

8. Shell Programming and Scripting

Awk - Summation in Proper decimal Format

Hi I am executing below command to do summation on 46th coloumn. cat File1| awk -F"|" '{p += $46} END { printf"Column Name | SUM | " p}' I am getting output as Column Name | SUM | 1.01139e+10 Here I want output in Proper decimal format. Can someone tell me what change is required for same? (1 Reply)
Discussion started by: sanranad
1 Replies

9. UNIX for Dummies Questions & Answers

accuracy of output - decimal points

Is there a way when using awk to specify the number of decimal points needed for the output? (2 Replies)
Discussion started by: cosmologist
2 Replies

10. Shell Programming and Scripting

how to get rid of all chars after the last decimal points

Hi All, Here is my original string: 192.168.2.1.8088. The target string I want: 192.168.2.1, how can I use awk or sed or other command to get rid of .8088 in the string? Thanks, Ray (9 Replies)
Discussion started by: rluo
9 Replies

Featured Tech Videos