Compute z-score for all columns using awk

user_4495740 0
Viewed 315

I have a file containing a first column of IDs and all other columns are numerical values which I want to compute z-scores. I know that there are lots of posts to calculate z-score using Python and R. I am not familiar with Python and I do not want to use R. I already have a way to calculate mean and standard-deviation of all my columns (I have 30 columns), but I need to calculate the z-scores for each column, and I am not sure how to do it, or if it is possible using awk.

My data is tab delimited, for example:

ID  W   A    
BR_400  1005.98 19.35   
FG_50   434.89  2.987    
DS_195_At   39.86   0.567 
ES_23_Md    41.45   19.55

My command to calculate mean and std for all columns:

cat input.txt | awk '{for(i=1;i<=NF;i++) {sum[i] += $i; sumsq[i] += ($i)^2}} END {for (i=1;i<=NF;i++) {printf "%f %f \n", sum[i]/NR, sqrt((sumsq[i]-sum[i]^2/NR)/NR)}}' > mean_std.txt

The z-scores formula:

z = (x – mean) / std

Any suggestions?

The expected output has only z-scores for each column:

ID W zscore A zscore  
BR_400  1.370068724 0.852212191
FG_50   0.119047359 -0.743935933
DS_195_At   -0.746299556    -0.979997685
ES_23_Md    -0.742816526    0.871721427
statistics awk unix
1 Answers

You may use this awk:

awk 'BEGIN {
   FS=OFS="\t"
}
NR == 1 {
   print
   next
}
NR == FNR {
   ++n
   for(i=2;i<=NF;i++) {
      sum[i] += $i
      sumsq[i] += ($i)^2
   }
   next
}
FNR == 1 { # compute mean and std values here
   for (i=2;i<=NF;i++) {
      mean[i] = sum[i]/n
      std[i] = sqrt( (sumsq[i] - sum[i]^2/n) / (n-1) )
   }
   next
}
{
   printf "%s", $1 OFS
   for (i=2;i<=NF;i++)
      printf "%f%s", ($i - mean[i]) / std[i], (i < NF ? OFS : ORS)
}' file file | column -t


ID         W          A-scor
BR_400     1.370069   0.852212
FG_50      0.119047   -0.743936
DS_195_At  -0.746300  -0.979998
ES_23_Md   -0.742817  0.871721
user_5482250
Related