# Using AWK to calculate mean and variance of columns

I have a large data file `dataset.csv` with 7 numeric columns. I have read that AWK would be the fastest/efficient way to calculate the mean and variance for each column. I need an AWK command that goes through the CSV file and outputs the results into a summary CSV. A sample dataset:

``````1   1   12  1   0   0   426530
1   1   12  2   0   0   685455
3   4   12  3   1   0   1182080
1   1   12  4   0   1   3090
2   1   13  5   0   0   386387
1   3   12  6   0   2   233430
3   1   11  7   1   0   896919
1   1   12  8   0   0   16441
``````

The resulting summary csv is seen below. The first row corresponds to the mean of each column and the second row is the variance(based on sample).

``````1.625   1.625   12  4.5 0.25    0.375   478791.5
0.839285714 1.410714286 0.285714286 6   0.214285714 0.553571429 1.74812E+11
``````

I have been able to calculate single column values however, I need it to run through all of the columns

``````awk -F' ' '{ total += \$1 } END {print total/NR}' dataset.csv > output.csv
``````
• should help.. `awk -F' ' '{ c1 += \$1; c2 +=\$2; c3 += \$3; c4 += \$4; c5 += \$5; c6 += \$6; c7 += \$7} END {print c1/NR" "c2/NR" "c3/NR" "c4/NR" "c5/NR" "c6/NR" "c7/NR}' dataset.csv` – Spike Sep 1 '16 at 9:00

you will need a loop that goes to all column

``````{ for(i=1;i<=NF;i++) ...
``````

and arrays

``````... total[i]+=\$i ; sq[i]+=\$i*\$i ; }
``````

this result in a command line like (for average)

``````awk '{ for(i=1;i<=NF;i++) total[i]+=\$i ; }
END { for(i=1;i<=NF;i++) printf "%f ",total[i]/NR ;}'
``````

# full program

I use this awk to compute mean and variance, however I don't have you result.

`````` { for(i=1;i<=NF;i++) {total[i]+=\$i ; sq[i]+=\$i*\$i ; } }
END {

for(i=1;i<=NF;i++) printf "%f ",total[i]/NR ;
printf "\n" ;
for(i=1;i<=NF;i++) printf "%f ",sq[i]/NR-(total[i]/NR)**2 ;
printf "\n" ;
}
``````
• `printf "\n"` == `print ""` – Costas Sep 1 '16 at 9:58