I want to output certain rows froms a spreadsheet to a different file, if they contain a number which is one higher than in the cell above

Question

I have a large csv file looking a little like this:

SomeData,SomeData,1,SomeData  
SomeData,SomeData,1,SomeData  
SomeData,SomeData,2,SomeData  
SomeData,SomeData,3,SomeData  
SomeData,SomeData,1,SomeData  
SomeData,SomeData,1,SomeData  
SomeData,SomeData,1,SomeData  
SomeData,SomeData,1,SomeData  
SomeData,SomeData,2,SomeData  
SomeData,SomeData,3,SomeData  
SomeData,SomeData,4,SomeData  
SomeData,SomeData,5,SomeData  
SomeData,SomeData,1,SomeData  
SomeData,SomeData,1,SomeData  
SomeData,SomeData,1,SomeData  
SomeData,SomeData,1,SomeData

I want to create a new csv file which only contains rows where the 3rd value is part of a set i.e. if the value in 3rd field of the line below is one higher, then I want both those lines to be included.

So, in the example above I only want rows 2-4 and 8-12 to be saved in a new file.

I'm struggling to work out how to tell grep to look for that pattern.

Any ideas?

Thanks

Chris Davies · Answer 1 · 2017-02-02T17:02:45.443

1

Assuming the data is in the file datafile you can use awk for this processing. (You can't use grep because it only deals with single lines of context.)

awk -F, '
    # Initially we are not in a block
    BEGIN { inblock=0 }

    # If the third field is greater than that on the previous line, print
    # it. But if we were not already in a block then print the saved line
    # too - and mark that we are now in a block
    NR>1 && $3>third { if (!inblock) { print line; inblock=1 } print $0 }

    # If we are in a block and the third field is smaller that the previous
    # line value, drop out of the block
    inblock && $3<=third { inblock=0 }

    # Save the values each time around
    {third=$3; line=$0 }
' datafile
SomeData,SomeData,1,SomeData
SomeData,SomeData,2,SomeData
SomeData,SomeData,3,SomeData
SomeData,SomeData,1,SomeData
SomeData,SomeData,2,SomeData
SomeData,SomeData,3,SomeData
SomeData,SomeData,4,SomeData
SomeData,SomeData,5,SomeData

edited Feb 02 '17 at 17:02

answered Feb 02 '17 at 13:57

Chris Davies

116,213
16
160
287

My output includes all 8 lines but it changes the '1' to '2' in lines one and four. So it works for my purposes but I can't see why it changes the third column value from 1 to 2 – Andrew Jennings Feb 02 '17 at 15:55
@AndrewJennings fixed. I had a copy-paste typo where I'd written $line in the awk script but should have written line. Sorry about that. – Chris Davies Feb 02 '17 at 17:03
@Kusalananda fixed - see previous comment. – Chris Davies Feb 02 '17 at 17:03

Kusalananda · Answer 2 · 2017-02-02T14:49:42.043

CSV files are tricky. Working on the assumption that Somedata is a properly quoted data field that may contain commas, we may replace the delimiter with something that is definitely not occurring in the data, for example a tab character ($'\t' in most modern shells) (you will change this to something you know works). If the data fields are free from commas, just skip the csvformat bits here.

Using csvkit:

$ csvformat -D$'\t' data.csv
SomeData        SomeData        1       SomeData
SomeData        SomeData        1       SomeData
SomeData        SomeData        2       SomeData
SomeData        SomeData        3       SomeData
SomeData        SomeData        1       SomeData
etc.

We can then pass this to an awk script that does the actual work of finding the groups.

NR > 1 && $3  == count + 1 {
    # This line is part of the set.

    ++count;        # We expect this value on the next line.
    ++set_size;     # This is the number of lines in the set.

    # Output previous line and remember this line.
    print previous_line;
    previous_line = $0;

    # Continue with next line.
    next;
}

set_size > 0 && $3 != count + 1 {
    # This line is not part of the set, but we're currently tracking a
    # set.  This means that the set ended, so output the last line of
    # the set.

    print previous_line;
    set_size = 0;
}

{
    # This line might be part of the next set.

    count = $3;
    previous_line = $0
}

Running it:

$ csvformat -D$'\t' data.csv | awk -F$'\t' -f script.awk
SomeData        SomeData        1       SomeData
SomeData        SomeData        2       SomeData
SomeData        SomeData        3       SomeData
SomeData        SomeData        1       SomeData
SomeData        SomeData        2       SomeData
SomeData        SomeData        3       SomeData
SomeData        SomeData        4       SomeData
SomeData        SomeData        5       SomeData

Then just get it back on standard comma-delimited form:

$ csvformat -D$'\t' data.csv | awk -F$'\t' -f script.awk | csvformat -d$'\t'
SomeData,SomeData,1,SomeData
SomeData,SomeData,2,SomeData
SomeData,SomeData,3,SomeData
SomeData,SomeData,1,SomeData
SomeData,SomeData,2,SomeData
SomeData,SomeData,3,SomeData
SomeData,SomeData,4,SomeData
SomeData,SomeData,5,SomeData

If the data is free of commas inside of the data fields, you may leave csvformat out of it completely:

$ awk -F',' -f script.awk data.csv
SomeData,SomeData,1,SomeData
SomeData,SomeData,2,SomeData
SomeData,SomeData,3,SomeData
SomeData,SomeData,1,SomeData
SomeData,SomeData,2,SomeData
SomeData,SomeData,3,SomeData
SomeData,SomeData,4,SomeData
SomeData,SomeData,5,SomeData

I want to output certain rows froms a spreadsheet to a different file, if they contain a number which is one higher than in the cell above

2 Answers2