awk script to sum numbers in a column over a loop not working for some iterations in the loop

user_6076137 0
Viewed 502

Sample input

12.0000 0.6000000 0.05
13.0000 1.6000000 0.05
14.0000 2.6000000 0.05
15.0000 3.0000000 0.05
15.0000 3.2000000 0.05
15.0000 3.4000000 0.05
15.0000 3.6000000 0.10
15.0000 3.8000000 0.10
15.0000 4.0000000 0.10
15.0000 4.2000000 0.11
15.0000 4.4000000 0.12
15.0000 4.6000000 0.13
15.0000 4.8000000 0.14
15.0000 5.0000000 0.15
15.0000 5.2000000 0.14
15.0000 5.4000000 0.13
15.0000 5.6000000 0.12
15.0000 5.8000000 0.11
15.0000 6.0000000 0.10
15.0000 6.2000000 0.10
15.0000 6.4000000 0.10
15.0000 6.6000000 0.05
15.0000 6.8000000 0.05
15.0000 7.0000000 0.05

Goal

  1. Print line 1 in output as 0 0
  2. For $2 = 5.000000, $3 = 0.15.
    • Print line 2 in output as 1 0.15
  3. For $2 = 4.800000 through $2 = 5.200000, sum+=$3 for each line (i.e. 0.14 + 0.15 + 0.14 = 0.43).
    • Print line 3 in output as 2 0.43.
  4. For $2 = 4.600000 through $2 = 5.400000, sum+=$3 for each line (i.e. 0.13 + 0.14 + 0.15 + 0.14 + 0.13 = 0.69).
    • Print line 4 in output as 3 0.69
  5. Continue this pattern until $2 = 5.000000 +- 1.6 (9 lines total, plus line 1 as 0 0 = 10 total lines in output)

Desired Output

0 0
1 0.15
2 0.43
3 0.69
4 0.93
5 1.15
6 1.35
7 1.55
8 1.75
9 1.85

Attempt

Script 1

#!/bin/bash

for (( i=0; i<=8; i++ )); do

awk '$2 >= 5.0000000-'$i'*0.2 {sum+=$3}
     $2 == 5.0000000+'$i'*0.2 {print '$i', sum; exit
     }' test.dat
     done > test.out

produces

0 0.15
1 0.43
2 0.69
3 0.93
4 1.15
5 1.35
6 1.55
7 1.75
8 1.85

This is very close. However, the output is missing 0 0 for line 1, and because of this, lines 2 through 10 have $1 and $2 mismatched by 1 line.

Script 2

#!/bin/bash

for (( i=0; i<=8; i++ )); do

awk ''$i'==0 {sum=0}
     '$i'>0 && $2 > 5.0000000-'$i'*0.2 {sum+=$3}
     $2 == 5.0000000+'$i'*0.2 - ('$i' ? 0.2 : 0) {print '$i', sum; exit
     }' test.dat
     done > test.out

which produces

0 0
1 0.15
2 0.43
4 0.93
5 1.15
6 1.35
7 1.55

$1 and $2 are now correctly matched. However, I am missing the lines with $1=3, $1=8, and $1=9 completely. Adding the ternary operator causes my code to skip these iterations in the loop somehow.

Question

Can anyone explain what's wrong with script 2, or how to achieve the desired output in one line of code? Thank you.

Solution

I used Ed Morton's solution to solve this. Both of them work for different goals. Instead of using the modulus to save array space, I constrained the array to $1 = 15.0000. I did this instead of the modulus in order to include two other "key" variables that I had wanted to also sum over at different parts of the input, into separate output files.

Furthermore, as far as I understood it, the script summed only for lines with $2 >= 5.0000000, and then multiplied the summation by 2, in order to include the lines with $2 <= 5.0000000. This works for the sample input here because I made $3 symmetric around 0.15. I modified it to sum them separately, though.

awk 'BEGIN { key=5; range=9}
$1 == 15.0000 {     
      a[NR] = $3
}
$2 == key { keyIdx = NR}
END {
    print (0, 0) > "test.out"
    sum = a[keyIdx]
    for (delta=1; delta<=range; delta++) {
        print (delta, sum) > "test.out"
        plusIdx = (keyIdx + delta) 
        minusIdx = (keyIdx - delta)
        sum += a[plusIdx] + a[minusIdx]
    }
    exit
}' test.dat
awk
2 Answers

Is this what you're trying to do?

$ cat tst.awk
$2 == 5 { keyNr = NR }
{ nr2val[NR] = $3 }
END {
    print 0, 0
    sum = nr2val[keyNr]
    for (delta=1; delta<=9; delta++) {
        print delta, sum
        sum += nr2val[keyNr+delta] + nr2val[keyNr-delta]
    }
}

$ awk -f tst.awk file
0 0
1 0.15
2 0.43
3 0.69
4 0.93
5 1.15
6 1.35
7 1.55
8 1.75
9 1.85

We could optimize it to only store 2*(range=9) values in vals[] (using a modulus operator NR%(2*range) for the index) and do the calculation when we hit an NR that's range lines past the line where $2 == key rather than doing it after we've read the whole of the input if it's either too slow or your input file is too big to store all in memory, e.g.:

$ cat tst.awk
BEGIN { key=5; range=9 }
{
    idx = NR % (2*range)
    nr2val[idx] = $3
}
$2 == key { keyIdx = idx; endNr = NR+range }
NR == endNr { exit }
END {
    print 0, 0
    sum = nr2val[keyIdx]
    for (delta=1; delta<=range; delta++) {
        print delta, sum
        idx = (keyIdx + delta) % (2*range)
        sum += nr2val[idx] + nr2val[idx]
    }
    exit
}

$ awk -f tst.awk file
0 0
1 0.15
2 0.43
3 0.69
4 0.93
5 1.15
6 1.35
7 1.55
8 1.75
9 1.85
user_17450010

I like your problem. It is an adequate challenge.

My approach is to put all possible into the awk script. And scan the input file only once. Because I/O manipulation is slower than computation (these days).

Do as many computation (actually 9) on the relevant input line.

The required inputs are variable F1 and text file input.txt

The execution command is:

awk -v F1=95 -f script.awk input.txt

So the logic is:

1. Initialize: Compute the 9 range markers and store their values in an array.

2. Store the 3rd input value in an order array `field3`. We use this array to compute the sum.

3. On each line that has 1st field equals 15.0000. 

3.1 If found begin marker then mark it.

3.2 If found end marker then compute the sum, and mark it.

4. Finalize: Output all the computed results

script.awk including few debug printout to assist in debugging

BEGIN {
    itrtns = 8; # iterations count consistent all over the program.
    for (i = 0; i <= itrtns; i++) { # compute range markers per iteration
        F1start[i] = (F1 - 2 - i)/5 - 14; # print "F1start["i"]="F1start[i];
        F1stop[i] = (F1 - 2 + i)/5 - 14; # print  "F1stop["i"]="F1stop[i];
        b[i] = F1start[i] + (i ? 0.2 : 0); # print "b["i"]="b[i];
    }
}
{    field3[NR] = $3;}  # store 3rd input field in ordered array.
$1==15.0000 { # for each input line that has 1st input field 15.0000
    currVal = $2 + 0; # convert 2nd input field to numeric value
    for (i = 0; i <= itrtns; i++) { # on each line scan for range markers
        # print "i="i, "currVal="currVal, "b["i"]="b[i], "F1stop["i"]="F1stop[i], isZero(currVal-b[i]), isZero(currVal-F1stop[i]);
        if (isZero(currVal - b[i])) { # if there is a begin marker
            F1idx[i] = NR; # store the marker index postion
            # print "F1idx["i"] =", F1idx[i];
        }
        if (isZero(currVal - F1stop[i])) { # if there is an end marker
            for (s = F1idx[i]; s <= NR; s++) {sum[i] += field3[s];} # calculate its sum
            F2idx[i] = NR; # store its end marker postion (for debug report)
            # print "field3["NR"]=", field3[NR];
        }
    }
}
END { # output the computed results
    for (i = 0; i <= itrtns; i++) {print i, sum[i], "rows("F1idx[i]"-"F2idx[i]")"}
}
function isZero(floatArg) { # floating point number pecision comparison
    tolerance = 0.00000000001;
    if (floatArg < tolerance && floatArg > -1 * tolerance )
        return 1;
    return 0;
}

Provided input.txt from the question.

12.0000 0.6000000 0.05
13.0000 1.6000000 0.05
14.0000 2.6000000 0.05
15.0000 3.0000000 0.05
15.0000 3.2000000 0.05
15.0000 3.4000000 0.05
15.0000 3.6000000 0.10
15.0000 3.8000000 0.10
15.0000 4.0000000 0.10
15.0000 4.2000000 0.11
15.0000 4.4000000 0.12
15.0000 4.6000000 0.13
15.0000 4.8000000 0.14
15.0000 5.0000000 0.15
15.0000 5.2000000 0.14
15.0000 5.4000000 0.13
15.0000 5.6000000 0.12
15.0000 5.8000000 0.11
15.0000 6.0000000 0.10
15.0000 6.2000000 0.10
15.0000 6.4000000 0.10
15.0000 6.6000000 0.05
15.0000 6.8000000 0.05
15.0000 7.0000000 0.05

The output for: awk -v F1=95 -f script.awk input.txt

0 0.13 rows(12-12)
1 0.27 rows(12-13)
2 0.54 rows(11-14)
3 0.79 rows(10-15)
4 1.02 rows(9-16)
5 1.24 rows(8-17)
6 1.45 rows(7-18)
7 1.6 rows(6-19)
8 1.75 rows(5-20)

The output for: awk -v F1=97 -f script.awk input.txt

0 0.15 rows(14-14)
1 0.29 rows(14-15)
2 0.56 rows(13-16)
3 0.81 rows(12-17)
4 1.04 rows(11-18)
5 1.25 rows(10-19)
6 1.45 rows(9-20)
7 1.65 rows(8-21)
8 1.8 rows(7-22)
user_62661920
Related