2

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?

3
  • your given output doesn't the one from the given input, why the value of the current fields changed? see the differences when applying the command in my answer below and your given output. Oct 9, 2021 at 12:11
  • 1
    What should the average be given input of foo,2,,4 (you have different answers making different assumptions about that)? If that can't happen in your input then state that in your question.
    – Ed Morton
    Oct 9, 2021 at 16:36
  • There is no similar structure in my input data, thanks for your feedback. @EdMorton Oct 10, 2021 at 19:25

1 Answer 1

3

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.