awk if statement with simple math

Question

I'm just trying to do some basic calculations on a CSV file.

Data:

31590,Foo,70
28327,Bar,291
25155,Baz,583
24179,Food,694
28670,Spaz,67
22190,bawk,4431
29584,alfred,142
27698,brian,379
24372,peter,22
25064,weinberger,8

Here's my simple awk script:

#!/usr/local/bin/gawk -f
BEGIN { FPAT="([^,]*)|(\"[^\"]+\")"; OFS=","; OFMT="%.2f"; }
    NR > 1
END { if ($3>1336) $4=$3*0.03; if ($3<1336) $4=$3*0.05;}1**

Wrong output:

31590,Foo,70
28327,Bar,291
28327,Bar,291
25155,Baz,583
25155,Baz,583
24179,Food,694
24179,Food,694
28670,Spaz,67
28670,Spaz,67
22190,bawk,4431
22190,bawk,4431
29584,alfred,142
29584,alfred,142
27698,brian,379
27698,brian,379
24372,peter,22
24372,peter,22
25064,weinberger,8
25064,weinberger,8

Excepted output:

31590,Foo,70,3.5
28327,Bar,291,14.55
25155,Baz,583,29.15
24179,Food,694,34.7
28670,Spaz,67,3.35
22190,bawk,4431,132.93
29584,alfred,142,7.1
27698,brian,379,18.95
24372,peter,22,1.1
25064,weinberger,8,.04

Simple math is if field $3 > 1336 = $3*.03 and results in field $4 field $3 < 1336 = $3*.05 and results in field $4

Answer 1

There's no need to force awk to recompile every record (by assigning to $4), just print the current record followed by the result of your calculation:

awk 'BEGIN{FS=OFS=","; OFMT="%.2f"} {print $0, $3*($3>1336?0.03:0.05)}' file

Answer 2

You shouldn't have anything in the END block

BEGIN {
    FS = OFS = ","
    OFMT="%.2f"
}
{
    if ($3 > 1336) 
        $4 = $3 * 0.03
    else 
        $4 = $3 * 0.05        
    print
}

This results in

31590,Foo,70,3.5
28327,Bar,291,14.55
25155,Baz,583,29.15
24179,Food,694,34.7
28670,Spaz,67,3.35
22190,bawk,4431,132.93
29584,alfred,142,7.1
27698,brian,379,18.95
24372,peter,22,1.1
25064,weinberger,8,0.4

Answer 3

$ awk -F, -v OFS=, '{if ($3>1336) $4=$3*0.03; else $4=$3*0.05;} 1' data
31590,Foo,70,3.5
28327,Bar,291,14.55
25155,Baz,583,29.15
24179,Food,694,34.7
28670,Spaz,67,3.35
22190,bawk,4431,132.93
29584,alfred,142,7.1
27698,brian,379,18.95
24372,peter,22,1.1
25064,weinberger,8,0.4

Discussion

The END block is not executed at the end of each line but at the end of the whole file. Consequently, it is not helpful here.

The original code has two free standing conditions, NR>1 and 1. The default action for each is to print the line. That is why, in the "wrong output," all lines after the first were doubled in the output.

Answer 4

With awk:

awk -F, -v OFS=, '$3>1336?$4=$3*.03:$4=$3*.05' file

The conditional-expression ? action1 : action2 ; is the much shorter terinary operator in awk.

Answer 5

mawk '$4 = (2 * ((_ = .01 * $3) < 13.36) + 3)*_' FS=, OFS=,

---

31590,Foo,70,3.5
28327,Bar,291,14.55
25155,Baz,583,29.15
24179,Food,694,34.7
28670,Spaz,67,3.35
22190,bawk,4431,132.93
29584,alfred,142,7.1
27698,brian,379,18.95
24372,peter,22,1.1
25064,weinberger,8,0.4