# 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` Sep 1, 2016 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 ""` Sep 1, 2016 at 9:58

The "C" approach, building from the documentation. Likely not as efficient or clean as it could be, but arguably easier to read. Computes the mean and variance across each column of data files containing NxM space delimited elements.

``````#!/bin/awk -f

BEGIN { } {
if (max_nf < NF)
max_nf = NF
max_nr = NR
for (x = 1; x <= NF; x++)
mat[x, NR] = \$x
} END {
for (x = 1; x <= max_nf; x++) {
mean = 0.0
var = 0.0
for (y = 1; y <= max_nr; y++) {
var += (y - 1) * (mat[x, y] - mean) * (mat[x, y] - mean) / y
mean += (mat[x, y] - mean) / y
}
printf("%f %f\n", mean, var / (max_nr - 1))
}
}
``````

Example run:

``````./ColVarAndMean.awk dataset.csv > output.dat && cat output.dat
1.625000 0.839286
1.625000 1.410714
12.000000 0.285714
4.500000 6.000000
0.250000 0.214286
0.375000 0.553571
478791.500000 174812449479.714294
``````
``````awk -F ',' '{print;x+=\$2}END{print "MEAN" x/NR}' filename.csv
``````

where `\$2` is the second column and `NR` is the number of records we can store the `\$2` values to the `x` variable

• That doesn't answer the question. Dec 30, 2019 at 7:03