Using awk to calculate average of each row with different number of columns

Question

Is it possible to use awk to calculate the average of each row (with different columns in each row). I have a file like the following, the first column is the names, and I like to calculate the average of each row and print the result in the last column of the input file:

Input-file (data1.csv):

EMPLOYEE1,0.395314,0.384513,,
EMPLOYEE2,5.4908,5.2921,,
EMPLOYEE3,0.0002323,0.00022945,0.00023238,0.00022931
EMPLOYEE4,0.00335516,0.00328432,0.00340309,0.00327163
EMPLOYEE5,1.4816,1.4367,1.4854,1.4353
EMPLOYEE6,7.89E-06,7.93E-06,7.95E-06,7.87E-06
EMPLOYEE7,3.724E-06,3.8745E-06,3.9428E-06,3.7227E-06
EMPLOYEE8,0.699498,0.688892,0.704256,0.683486
EMPLOYEE9,33.5195,31.9736,33.6779,31.742

Desired output:

EMPLOYEE1,0.395314,0.384513,,,0.3899135
EMPLOYEE2,5.4908,5.2921,,,5.39145
EMPLOYEE3,0.0002323,0.00022945,0.00023238,0.00022931,0.00023086
EMPLOYEE4,0.00335516,0.00328432,0.00340309,0.00327163,0.00332855
EMPLOYEE5,1.4816,1.4367,1.4854,1.4353,1.45975
EMPLOYEE6,7.89E-06,7.93E-06,7.95E-06,7.87E-06,7.91E-06
EMPLOYEE7,3.72E-06,3.87E-06,3.94E-06,3.72E-06,3.82E-06
EMPLOYEE8,0.699498,0.688892,0.704256,0.683486,0.694033
EMPLOYEE9,33.5195,31.9736,33.6779,31.742,32.7282

I tried awk like the following, but it doesn't calculate average for rows with columns less than maximum NF.

awk  -F',' '{ s = 0; for (i = 2; i <= NF; i++) s += $i; print $1, (NF > 1) ? s / (NF - 1) : 0; }'  data1.csv

and

 awk -F','  '{sum=0; for (i=2;i<=NF;i++)sum+=$i; print $0,sum/(NF-1)}'  data1.csv

But my code doesn't change NF row. is it possible to change NF for each row and get desired output?

Answer 1

Here's one way:

$ awk -F',' -v OFS=',' '{ 
        s=0; 
        numFields=0; 
        for(i=2; i<=NF;i++){ 
            if(length($i)){ 
                s+=$i; 
                numFields++
            } 
        } 
        print $0, (numFields ? s/numFields : 0)}' data1.csv 
EMPLOYEE1,0.395314,0.384513,,,0.389914
EMPLOYEE2,5.4908,5.2921,,,5.39145
EMPLOYEE3,0.0002323,0.00022945,0.00023238,0.00022931,0.00023086
EMPLOYEE4,0.00335516,0.00328432,0.00340309,0.00327163,0.00332855
EMPLOYEE5,1.4816,1.4367,1.4854,1.4353,1.45975
EMPLOYEE6,7.89E-06,7.93E-06,7.95E-06,7.87E-06,7.91e-06
EMPLOYEE7,3.724E-06,3.8745E-06,3.9428E-06,3.7227E-06,3.816e-06
EMPLOYEE8,0.699498,0.688892,0.704256,0.683486,0.694033
EMPLOYEE9,33.5195,31.9736,33.6779,31.742,32.7282

Note that awk prints 0.389914 as the result of 0.779827/2 which means that the average on the first line will be 0.389914 and not 0.389915. This is because awk will round to the nearest even number and its default print mode (controlled by the OFMT variable) is %0.6g. If you require more accuracy, you can do:

$ awk -F',' -v OFS=',' -v OFMT='%0.7g' '{ 
        s=0; 
        numFields=0; 
        for(i=2; i<=NF;i++){ 
            if(length($i)){ 
                s+=$i; 
                numFields++
            } 
        } 
        print $0, (numFields ? s/numFields : 0)}' data1.csv 
EMPLOYEE1,0.395314,0.384513,,,0.3899135
EMPLOYEE2,5.4908,5.2921,,,5.39145
EMPLOYEE3,0.0002323,0.00022945,0.00023238,0.00022931,0.00023086
EMPLOYEE4,0.00335516,0.00328432,0.00340309,0.00327163,0.00332855
EMPLOYEE5,1.4816,1.4367,1.4854,1.4353,1.45975
EMPLOYEE6,7.89E-06,7.93E-06,7.95E-06,7.87E-06,7.91e-06
EMPLOYEE7,3.724E-06,3.8745E-06,3.9428E-06,3.7227E-06,3.816e-06
EMPLOYEE8,0.699498,0.688892,0.704256,0.683486,0.694033
EMPLOYEE9,33.5195,31.9736,33.6779,31.742,32.72825