6

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

8

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.

10
  • 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
    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
  • 1
    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
1

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
8
  • +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

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.