# Can `awk` sum a column over a specified number of lines

I've reviewed the "Similar questions", and none seem to solve my problem:

I have a large CSV input file; each line in the file is an `x,y` data point. Here are a few lines for illustration, but please note that in general the data are not monotonic:

``````1.904E-10,2.1501E+00
3.904E-10,2.1827E+00
5.904E-10,2.1106E+00
7.904E-10,2.2311E+00
9.904E-10,2.2569E+00
1.1904E-09,2.3006E+00
``````

I need to create an output file that is smaller than the input file. The output file will contain no more than one line for every N lines in the input file. Each single line in the output file will be a `x,y` data point which is the average of the `x,y` values for N lines of the input file.

For example, if the total number of lines in the input file is 3,000, and N=3, the output file will contain no more than 1,000 lines. Using the data above to complete this example, the first 3 lines of data above would be replaced with a single line as follows:

x = (1.904E-10 + 3.904E-10 + 5.904E-10) / 3 = 3.904E-10

y = (2.1501E+00 + 2.1827E+00 + 2.1106E+00) / 3 = 2.1478E+00, or:

``````3.904E-10,2.1478E+00
``````

for one line of the output file.

I've fiddled with this for a while, but haven't gotten it right. This is what I've been working with, but I can't see how to iterate the `NR` value to work through the entire file:

``````awk -F ',' 'NR == 1, NR == 3 {sumx += \$1; avgx = sumx / 3; sumy += \$2; avgy = sumy / 3} END {print avgx, avgy}' CB07-Small.csv
``````

To complicate this a bit more, I need to "thin" my output file still further:

If the value of `avgy` (as calculated above) is close to the last value of `avgy` in the output file, I will not add this as a new data point to the output file. Instead I will calculate the next `avgx` & `avgy` values from the next N lines of the input file. "Close" should be defined as a percentage of the last value of `argy`. For example:

if the current calculated value of `avgy` differs by less than 10% from the last value of `avgy` recorded in the output file, then do not write a new value to the output file.

see edit history

Here’s a generic variant:

``````BEGIN { OFS = FS = "," }

{
for (i = 1; i <= NF; i++) sum[i] += \$i
count++
}

count % 3 == 0 {
for (i = 1; i <= NF; i++) \$i = sum[i] / count
delete sum
count = 0
if (\$NF >= 1.1 * last || \$NF <= 0.9 * last) {
print
last = \$NF
}
}

END {
if (count > 0) {
for (i = 1; i <= NF; i++) \$i = sum[i] / count
if (\$NF >= 1.1 * last || \$NF <= 0.9 * last) print
}
}

``````

I’m assuming that left-overs should be handled in a similar fashion to blocks of N lines.

• Looks awesome - thanks. A few questions to help me understand this: `1.` What does `count % 3` do; i.e. purpose? `2.` In an input file of say, 10 million lines, what is the largest array size possible; i.e. `sum[i]`? `3.` The data I'll be processing will not be monotonically increasing; increase for a bit, then decrease for a bit. Will `\$NF >= 1.1 * last` "capture" decreases? – Seamus Jan 6 at 18:36
• 1. `count % 3` calculates `count` modulo 3, i.e. the remainder of `count` divided by 3. When that’s 0, `count` is an integer multiple of 3. 2. The size of the array depends on the number of fields per record (the number of columns); it doesn’t vary with the number of lines. 3. I implemented your spec, which says “if the current calculated value of `avgy` is less than 1.1 times the last value of `avgy` recorded in the output file, then do not write a new value to the output file”; by those terms, if the average decreases, it won’t be output again. – Stephen Kitt Jan 6 at 18:53
• I apologize for my sloppy language. What I should have said is “if the current calculated value of `avgy` differs by less than 10% from the last value of `avgy` recorded in the output file, then do not write a new value to the output file”. Maybe something like, `abs(\$NF - last)` ?? – Seamus Jan 6 at 19:33
• I assumed that, since the requirement was a quote, it came from somewhere else ;-). I’ve updated the answer to account for a 10% decrease too (AWK doesn’t have `abs()`, and while it would be trivial to write as a function, I just added the decrease as a disjunction). – Stephen Kitt Jan 6 at 22:05
• Many thanks! The quote is actually original... quoted because I wanted to highlight it, and at the time, I thought it was "well written" :0 lol I'll try this on my measurement files as soon as I am able, and let you know the results. FYI, this is all directed toward creation of a PWL file from my oscilloscope measurements of contact bounce; i.e. evaluating schemes for "de-bouncing". – Seamus Jan 6 at 22:29

This checks for the line condition and the 10% rule. Keep in mind that the 10% rule has the side effect of increasing the check value linearly.

``````\$ awk -F ',' '
BEGIN{
N=3; prev_y=0
}
{
x+=\$1;
y+=\$2;
i++
}
NR%N==0 && (y/i) <= (prev_y)*1.1{ x=0; y=0; i=0 }
NR%N==0 && (y/i) > (prev_y)*1.1{
print x/i","y/i;
prev_y=y/i; x=0; y=0; i=0
}' file
``````
• +1 Thanks! Yes - you're correct about the percentage; I'll need to look at that. Perhaps an absolute value would have been better? ALSO: I'm getting different results when I calculate my test file manually. Could you try your script against my test file & let me know what you get? – Seamus Jan 7 at 1:54
• I'm getting -1.8096e-09,1.7561 -1.2096e-09,1.8014 -6.096e-10,1.8674 ... 1.5904e-09,2.2948 – Andre Wildberg Jan 7 at 2:28
• That's what I got also, but that can't be correct. Look at the first 2 `y` values: 1.7561 & 1.8014. The "disqualification range" for the 2nd point would have been 1.7561 +/- 0.1756, or 1.5805 to 1.9317. – Seamus Jan 7 at 5:34
• Right, the updated `i` was not handled correctly. Should have been put into the previous value. I've updated the code. I now get: -1.8096e-09,1.7561 -4.096e-10,1.94391 7.904e-10,2.2311 and the leftover 1.2904e-09,2.29482 – Andre Wildberg Jan 7 at 6:01
• My spreadsheet says there are three data points in this set of 19: ( -1.809600E-09, 1.756100E+00 ) ; ( -9.600000E-12, 2.027767E+00 ); ( 1.190400E-09, 2.294833E+00 ). You're welcome to try the spreadsheet; it may explain the logic better than my description. – Seamus Jan 7 at 6:59