Skip to content
Advertisement

implementing Excel if-like function with AWK

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
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement