Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

Say I have a csv file with these columns:

timestamp,cpu,memory

An example of the shape of the actual data would be this chart:

cpu/mem csv data as chart

Having the data visualized in this way, it's easy for the human eye to make out where any of the 4 cpu intensive activities start and end.

What would be an easy way, using standard unix cmd line tools, ideally not resorting to R or octave etc, to "grep" only the 8 lines of the file that denote the onset and end of these 4 activities, based on the "cpu" column?

Obviously this is somewhat fuzzy, and there is noise in the data, but any solution that gives me 8 lines that are somewhere close to onset/end is appreciated.

A short excerpt of the csv file, showing only the first of those 4 bursts of activity:

10:44:21.310,0,53567488
10:44:22.310,1.56257,53575680
10:44:23.326,0,53854208
10:44:24.325,34.3761,57405440
10:44:25.325,73.43985,61747200
10:44:26.325,3.1251,69459968
10:44:27.325,0,69459968
10:44:28.325,0,69459968
10:44:29.325,65.6271,74756096
10:44:30.325,53.1267,77783040
...
10:50:56.450,35.93865,142700544
10:50:57.450,10.93785,142897152
10:50:58.450,1.56255,142897152
10:50:59.450,0,142897152
10:51:00.450,0,142897152
10:51:01.450,0,142897152

Ideally, this would result in the following 2 lines.

10:44:24.325,34.3761,57405440
10:50:58.450,1.56255,142897152

However, as there is no cpu activity for a couple of seconds between 10:44:26.325 and 10:44:28.325, I don't expect the answer to be that smart and do something akin to 'smoothing' the data. So if a csv snippet like the above would e.g. yield the following 4 lines, that would suffice.

10:44:24.325,34.3761,57405440
10:44:26.325,3.1251,69459968
10:44:29.325,65.6271,74756096
10:50:58.450,1.56255,142897152
share|improve this question
1  
Could you provide csv data here? –  Ivan Chau Nov 15 '13 at 1:00
add comment

3 Answers 3

up vote 5 down vote accepted

One way to do this is with awk. You can set a threshold to get the first line the threshold is hit and last line it falls below. Something like this might work:

awk -F, -vthreshold_up=20 -vthreshold_down=10 'BEGIN {
                          cur = "gt";
                        } 
                        {
                          if (cur == "gt" && $2 > threshold_up) {
                            print;
                            cur = "lt";
                          } else if (cur = "lt" && $2 < threshold_down) {
                            print;
                            cur = "gt";
                          }
                        }' file.csv
share|improve this answer
    
Won't that print all lines that are above or below the threshold? I am assuming the OP only wants the start/end of each major change. –  terdon Nov 15 '13 at 1:02
    
@terdon No, it won't print all those lines, because the variable cur stores the current state and if you check the if/else statements, you should see that it works. –  Eugene Beresovksy Nov 15 '13 at 1:52
    
this worked great (after splitting threshold into threshold_up and threshold_down)! will accept, unless something more ingenious shows up –  Eugene Beresovksy Nov 15 '13 at 1:52
    
@EugeneBeresovksy ah, yes indeed. Nice one jordanm! –  terdon Nov 15 '13 at 1:55
1  
@slm thanks! 9.9k-10k was agonizing because I was so close :). –  jordanm Nov 15 '13 at 3:01
show 1 more comment

Extending jordanm's approach, I was able to build something surprisingly robust, without having to resort to statistics. The script got a little bit longer unfortunately, but this is finished, and now I can use it as often as I need it, with only figuring out the right parameters.

I tested it on a dozen of actual data files, some as messy as this one:

chart of messy cpu and memory csv

The trick here is the use of the MIN_DURATION variable, which helps ignoring temporary spikes and slumps of up to the specified number of lines.

Usage:

grep-begin-end FIELD_SEPARATOR FIELD_INDEX THRESHOLD_UP THRESHOLD_DOWN MIN_DURATION ...

Example:

grep-begin-end , 2 30 4 5 file.csv

grep-begin-end

FIELD_SEPARATOR=$1
FIELD_INDEX=$2
THRESHOLD_UP=$3
THRESHOLD_DOWN=$4
MIN_DURATION=$5
shift 5
awk -F$FIELD_SEPARATOR -vthreshold_up=$THRESHOLD_UP -vthreshold_down=$THRESHOLD_DOWN 'BEGIN {
    cur = "gt";
}
{
    val = $'$FIELD_INDEX';
    # strip of double quotes and convert to number
    if (substr(val, 1, 1) == "\"") { val = 0 + substr(val, 2, length(val) - 3); } else { val = 0 + val; }
    buf = "";
    if (cur == "gt")
    {
        if (val >= threshold_up)
        {
            if (buf == "")
                buf = $0;
            if (duration >= '$MIN_DURATION')
            {
                print buf;
                cur = "lt";
                duration = 0;
                buf = "";
            }
            else
            {
                duration++;
            }
        }
        else
        {
            duration = 0;
        }
    }
    else if (cur == "lt")
    {
        if (val <= threshold_down)
        {
            if (buf == "")
                buf = $0;
            if (duration >= '$MIN_DURATION')
            {
                print buf;
                cur = "gt";
                duration = 0;
                buf = "";
            }
            else
            {
                duration++;
            }
        }
        else
        {
            duration = 0;
        }
    }
}' "$@"
share|improve this answer
add comment

I was thinking that you could just write a little script that prints if the current value differs from the last one by more than N. Since your data seem to be very discrete and the changes happen from one measurement to the next, that should work.

Something like

awk -F, -v thr=100 '{if ($2-last>thr || last-$2>thr){print;} last=$2;}' foo.csv
share|improve this answer
    
Your prose (N times greater/smaller than previous) doesn't match your code (difference greater/smaller than thr). That said, if what you mean by N times greater/smaller treats an increase from 1 to 2 the same as a jump from 50 to 100, then I don't think it would do the job very well. –  Eugene Beresovksy Nov 15 '13 at 7:12
    
@EugeneBeresovksy no, I meant what I wrote in awk not in the prose, thanks for pointing it out, answer edited. I'm just thinking you can specify a threshold thr and if the value changes more than thr between consecutive measurements, print. A very naive approach that will also print tiny spikes as your (much better) one will not. –  terdon Nov 15 '13 at 13:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.