1

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
1

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

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.