Can Awk deal with irregular columns? i.e. I have a file like below, each columns may have different number of values, how to get the average of all the positive values? Please note, the incomplete rows does not include blank/space, so you can not use "sed" to replace those blank.

1 2 3 7
4 5
5 0
0 8 9

I know how to get average for regular files like below,but it will not work if the final row is not complete (4 numbers). Plus the code below can only print he average for each column separately, and I need an average for all non-zero numbers, not each column. How to use awk or python to do that? My expected output is one number:4.888888889 ((1+2+3+7+4+5+5+8+9)/9=4.888888889) Thank you for help.

awk '{for (i=1;i <= NF; i++) if ($i>0) { sum[i] += $i; num[i]++; } } END { for(i =1;i <= NF; i++) print i, sum[i]/num[i]}' $infile
  • 1
    post the expected result – RomanPerekhrest May 30 '18 at 16:52
  • You could loop over the elements of sum instead i.e. END { for(i in sum) . . . – steeldriver May 30 '18 at 16:58
  • @ RomanPerekhrest, just added, thank you for reminder. – kelly May 30 '18 at 17:14

If you want the average of all numbers in all columns, then this is simple

awk '   { for (i=1;i<=NF  ;i++){ sum+=$i;num++} }
     END{ print(sum/num) } ' infile

If you want the average per column, just keep track of the maximum number of columns:

awk '   {for (i=1;i<=NF  ;i++){ if(maxF<NF){maxF=NF};sum[i]+=$i;num[i]++} }
     END{for (i=1;i<=maxF;i++){ print(i,sum[i]/num[i]) } } ' infile

GNU awk approach:

awk -v FPAT='[1-9][0-9]*' '{ n += NF; for (i=1;i<=NF;i++) sum += $i }END{ print sum/n }' file

The output:


I'd convert the spaces to newlines and have a single number per line:

tr ' ' '\n' <"$infile" | awk '$1 > 0 {n++; sum+=$1} END {if (n>0) print sum/n}'

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.