Process CSV file through command line: Remove only middle rows between consecutive row entries if consecutive entries have the same 2nd column value

Question

I have a CSV file with only 2 columns (but lots of rows) and the occasional irregular row which always starts with an asterisk (*) character and may span more than two columns. Using just the Linux command line, the intended behavior is:

1. If 3 or more consecutive data rows have the same value for column two, delete the middle rows. Start and end rows are retained.
2. Retain the irregular rows that begin with an asterisk

For example, if I have a CSV with this content:

    0,Apple
    1,Apple
    2,Apple
    * Checkpoint
    * Another checkpoint
    3,Apple
    4,Apple
    5,Box
    6,Box
    7,Citrus
    8,Box
    9,Apple
    10,Apple
    11,Apple
    12,Dove
    13,Citrus
    * Sudden checkpoint, * Leftover checkpoint note 1, * Leftover checkpoint note N
    14,Citrus
    15,Citrus
    16,Citrus
    17,Apple
    18,Citrus

It should look like below after:

    0,Apple
    * Checkpoint
    * Another checkpoint
    4,Apple
    5,Box
    6,Box
    7,Citrus
    8,Box
    9,Apple
    11,Apple
    12,Dove
    13,Citrus
    * Sudden checkpoint, * Leftover checkpoint note 1, * Leftover checkpoint note N
    16,Citrus
    17,Apple
    18,Citrus

In the example above, lines 1 to 3, 10, and 14 to 15 were removed.

Thank you very much in advance for any answers.

Cheers

Answer 1

Using awk:

BEGIN { FS = "," }

/^[*]/ { print; next }

{
        if (NR > 1 && $2 == word) {
                tail = $0
                ++count
        } else {
                if (count) print tail
                word = $2; count = 0
                print
        }
}

END { if (count) print tail }

This awk script unconditionally prints all lines that start with *. If the line is not such a line, and if the word in the second field is a word that we have remembered, store the record in the variable tail ("tail" as in the last record of a run of records with the same word in the second field).

If the second field is not the same as in the previous, then print the tail record if there were more than one record in the previous run of records, then remember the new word and print the current record (the first record in a new run of one or more records with the same word in the second field).

Testing it on the data provided and assuming it is simple CSV (meaning no embedded delimiters or newlines etc.):

$ awk -f script file
0,Apple
* Checkpoint
* Another checkpoint
4,Apple
5,Box
6,Box
7,Citrus
8,Box
9,Apple
11,Apple
12,Dove
13,Citrus
* Sudden checkpoint, * Leftover checkpoint note 1, * Leftover checkpoint note N
16,Citrus
17,Apple
18,Citrus

---

Similar to the above but using Miller (mlr), which is CSV-aware and would be able to handle CSV records with complex quoted strings:

if (is_not_null(@word) && $2 == @word) {
        @tail = $*;
        false # omit this record for now
} else {
        is_not_null(@tail) {
                emit @tail # emit the tail record
        }
        @word = $2; @tail = null;
        true  # emit this record
}

end { is_not_null(@tail) { emit @tail } }

This is an expression for Miller's filter sub-command to include or omit records from the input data set using very similar logic to the awk code above. We can make Miller pass through the lines starting with the character * by using --pass-comments-with='*' on the command line. Using --csv with -N treats the input as header-less CSV.

$ mlr --pass-comments-with='*' --csv -N filter -f script file
0,Apple
* Checkpoint
* Another checkpoint
4,Apple
5,Box
6,Box
7,Citrus
8,Box
9,Apple
11,Apple
12,Dove
13,Citrus
* Sudden checkpoint," * Leftover checkpoint note 1"," * Leftover checkpoint note N"
16,Citrus
17,Apple
18,Citrus