calculate ratio using awk

``````awk 'NR==1{\$4="ratio"}NR>1{\$4 = (\$3)/(\$2)} {print \$1 "\t" \$2 "\t" \$3 "\t" \$4}' A  B
``````

awk: cmd. line:1: (FILENAME=A FNR=18) fatal: division by zero attempted

Can anybody help with this error?

What does `NR==1` and `NR>1` mean here? It is in awk examples; I don't understand. I know NR is number of records but why we using it here?

• Sample data would get you better answers. – Sobrique Apr 19 '16 at 13:28

It looks like authors of that example imagined the format of the files A and B to be something like this:

File A:

``````X Y Z
1 2 3
4 5 6
``````

File B:

``````6 7 8
o o 0
``````

What the AWK example does is create a 4th colum. NR here represents the current line AWK is processing. On the 1st line it sets the 4th parameter to string "ratio" (column name), on all following lines it sets the 4th parameter to 3rd parameter divided by 2nd one. The result:

``````awk 'NR==1{\$4="ratio"}NR>1{\$4 = (\$3)/(\$2)} {print \$1 "\t" \$2 "\t" \$3 "\t" \$4}' A
X   Y   Z   ratio
1   2   3   1.5
4   5   6   1.2
6   7   8   1.14286
1   2   3   1.5
``````

As for the error you are getting:

awk: cmd. line:1: (FILENAME=A FNR=18) fatal: division by zero attempted

Make sure 18th line of file A contains numbers in columns 2 and 3.

You can use sed to convert 'o' characters to 0:

``````cat A B | sed 's/\bo\b/0/g'
``````

And you can check for column 2 being a 0 and acting accordingly:

``````awk 'NR==1{\$4="ratio"}NR>1{if(\$2==0) \$4 = "N/A"; else \$4 = (\$3)/(\$2)}
``````

Put together, you get:

``````cat A B | sed 's/\bo\b/0/g' | awk 'NR==1{\$4="ratio"}NR>1{if(\$2==0) \$4 = "N/A"; else \$4 = (\$3)/(\$2)} {print \$1 "\t" \$2 "\t" \$3 "\t" \$4}'
X   Y   Z   ratio
1   2   3   1.5
4   5   6   1.2
6   7   8   1.14286
0   0   0   N/A
``````
• thank u so much i just checked it is 'o' in both and it crashed here not processing the rest. how to force it to leave it as zero and move further – star Apr 19 '16 at 13:28
• try `cat A B | sed 's/\bo\b/0/g' | awk 'NR==1{\$4="ratio"}NR>1{\$4 = (\$3)/(\$2)} {print \$1 "\t" \$2 "\t" \$3 "\t" \$4}'` – Jan Dusek Apr 19 '16 at 13:35
• But make sure 2nd column is not `o` or `0`, unless you get a division by 0 again since you are dividing with it. If you want to skip all such lines, maybe add some logic to awk checking if \$2==0 and setting \$4 depending on that. – Jan Dusek Apr 19 '16 at 13:37
• thank u so much it works but what is 's/\bo\b/0/g' \bo\b here – star Apr 25 '16 at 13:02
• it substitues all characters `o` surrounded by word-separation character (so not mid-word) with `0` using `sed` – Jan Dusek Apr 26 '16 at 5:29

In the example given,

``````awk 'NR==1{\$4="ratio"}NR>1{\$4 = (\$3)/(\$2)} {print \$1 "\t" \$2 "\t" \$3 "\t" \$4}' A  B
``````

`NR` is the number of records read so far. One might use it in an example like this to skip over the header of a table like this:

``````Date        Cost     Quantity
2016/04/10  12.57    3
2016/04/19  11.74    2
``````

since you cannot divide "Cost" by "Quantity" (they are not numbers).

By the way, your data could have four (or more) columns. However, awk will allow you to assign to columns which are not in the input data. Thus, the initial `\$4="ratio"` could be altering an existing column, but more likely is adding a column to the data to reflect the computation on successive records.

The error (division by zero) is a different matter than the interpretation of `NR`. You can check if you have numeric data in `\$2` and `\$3` using a pattern, e.g., adding this check:

to produce something like

``````#!/bin/sh
awk 'function isnum(v) {
if ( v ~ /^[0-9.]+\$/ )
return 1;
else
return 0;
}
(NR==1){\$4="ratio"}
(NR>1 && NF >= 3 && isnum(\$2) && isnum(\$3) && \$3 > 0){\$4 = (\$3)/(\$2)}
{print \$1 "\t" \$2 "\t" \$3 "\t" \$4}' A  B
``````

The `isnum` function is simplified, but will allow unsigned decimal fractions.

If you prefer the script on a single line, keep in mind that awk scripts are free-format. So... you could do this:

``````awk 'function isnum(v){if(v~/^[0-9.]+\$/)return 1;else return 0;}(NR==1){\$4="ratio"}(NR>1&&NF>=3&&isnum(\$2)&&isnum(\$3)&&\$3>0){\$4=(\$3)/(\$2)}{print \$1"\t"\$2"\t"\$3"\t"\$4}' A  B
``````
• thank you so much ok now what if i want to skip header and calculate ratios like 3/12.57 it should not give error – star Apr 19 '16 at 13:17
• i just notice that my file have some fields with 0 i.e both S3 and \$2 so when it reach there it crash how to fix in above command – star Apr 19 '16 at 13:22
• very hard :( so no one-liner exist – star Apr 19 '16 at 13:34
• That is a one-liner (if you remove all of the newlines, it will work the same way). But it is more readable as I presented it. – Thomas Dickey Apr 19 '16 at 13:35