I have a question about Excel-if-function implementation with AWK. I have a csv file having eight columns like below:
3,3,1,2,2,4,1,3 3,4,2,4,2,5,4,1 3,2,1,1,6,5,4,3
Each field can take only integer values from 1 to 6. I would like to give one point if the first column
is 1. In other cases, the score would be zero. In this MWE case, the point for all three row is zero because everyone answered as 3, not 1. If I wish to do this with MS Excel, I usually insert
one column righthand side and write a function like =IF(A1=1,1,0)
. And then, by repeating
this procedures for all the eight columns, I can calculate the total score by summing up all the
inserted columns. In Excel language, this corresponds to sum(I1:P1)
. If I assume that the correct answer is 1, for example, for all eight columns, the expected transposed output is (2,1,2)
. I should also learn how to sum up the last eight columns in a row. I wonder how I can implement this type of function with awk
. Thanks in advance for any advice.
Advertisement
Answer
As there was no expected output, I’m gonna go with my mental image of what it would look like in Excel (thank god haven’t used that in ages):
$ awk ' BEGIN { FS=OFS="," # need separators as there are no boxes to fill } { nf=NF # securing NF for future abuse for(i=1;i<=nf;i++) # automatically repeating the procedure $(nf+1)+=($i==1) # the procedure s+=$NF # total sum print # output } END { print s # output the total sum after records }' file
Output:
3,3,1,2,2,4,1,3,2 3,4,2,4,2,5,4,1,1 3,2,1,1,6,5,4,3,2 5