Sum and calculate number in table

Hello everyone,

I have some problem in calculation number using awk.

input file format :

   S1    1 : 0.003 0.031
   S2    1 : 0.020 0.095
   S3    4 : 0.088 0.012
   S4    2 : 0.010 0.090
   S5    2 : 0.244 0.066
   S6    3 : 0.249 0.751
   S7    3 : 0.010 0.990
   S8    3 : 0.230 0.566

Expected result :

1    0.012  0.063
4    0.088  0.012
2    0.127  0.078
3    0.163  0.769

Normally, I used this code below but it is not completely. I have to sum value which start in column 4 to up (some file has value in column 4 to 5 or 4 to 8) but based on column 2 and count in column 2.

awk '{a[$2]+=$4; b[$2]++;} END {for(i in a) print i"\t"a/b;}' inputfile

Could you please suggest me?
Thanks in advance.

awk '{a[$2] += $4;
  b[$2] += $5}
END {for(x in a)
    {print x "\t" a[x] "\t" b[x]}}' inputfile

If you want to maintain the input order

awk '{if(!($2 in b))
    {a[++n] = $2};
  b[$2] += $4;
  c[$2] += $5}
END {for(i = 1; i <= n; i++)
    print a "\t" b[a] "\t" c[a]}' inputfile
1 Like

Thank you very much SriniShoo. It worked :).
However, I have many files which each file contain value column more than 4 to 70 column. I will try to adjust this code for them.

For more fields per line try

awk     '       {B[$2]++
                 for (i=4; i<=NF; i++) A[$2, i]+=$i
                 N=NF}
         END    {for (j in B) {printf "%s ", j
                         for (k=4; k<=N; k++) printf " %8.3f", A [j,k]/B[j]
                         printf "\n"
                        }
                }
        ' file
1 Like

Thank you very much RudiC. Wow, it worked in many column. :slight_smile: