Perform highly customized sort based on multiple columns of a CSV file?

Question

I have a four-column CSV file, using `@` as the separator, e.g.: ``` 0001 @ fish @ animal @ eats worms ``` The first column is the only column guaranteed to be unique. I need to perform four sort operations on columns 2, 3, and 4. First, column 2 is sorted alphanumerically. The important feature of this sort is it must guarantee that any duplicate entries within column 2 are next to each other, e.g.: ``` @ a @ @ @ a @ @ @ a @ @ @ a @ @ @ a @ @ @ b @ @ @ b @ @ @ c @ @ @ c @ @ @ c @ @ @ c @ @ @ c @ @ ``` Next, within the first sort, sort the lines into two categories. The first lines are those which do not contain the words “arch.”, “var.”, “ver.”, “anci.” or “fam.” anywhere within column 4. The second lines (which are sorted after), are those containing those words, e.g.: ``` @ a @ @ Does not have one of those words. @ a @ @ Does not have one of those words. @ a @ @ Does not have one of those words. @ a @ @ Does not have one of those words. @ a @ @ This sentence contains arch. @ b @ @…

Accepted Answer

TXR: ( http://www.nongnu.org/txr )

```
@(bind special-words ("arch." "var." "ver." "anci." "fam."))
@(bind ahash @(hash :equal-based))
@(repeat)
@id @@ @alpha @@ @animal @@ @words
@  (rebind words @(split-str words " "))
@  (bind record (id alpha animal words))
@  (do (push record [ahash alpha]))
@(end)
@(bind sorted-rec-groups nil)
@(do
   (defun popularity-sort (recs)
     (let ((histogram [group-reduce (hash)
                                    third (do inc @1)
                                    recs 0]))
      [sort recs > [chain third histogram]]))

(dohash (key records ahash)
     (let (contains does-not combined)
       (each* ((r records)
               (w [mapcar fourth r]))
         (if (isec w special-words)
           (push r contains)
           (push r does-not)))
       (push (append (popularity-sort does-not)                                 
                     (popularity-sort contains))                                
             sorted-rec-groups)))
   (set sorted-rec-groups [sort sorted-rec-groups :
                                [chain first second]]))
@(output)
@  (repeat)
@    (repeat)
@(rep)@{sorted-rec-groups} @@ @(last)@{sorted-rec-groups " "}@(end)
@    (end)
@  (end)
@(end)
```

Data:

```
0001 @ b @ fish @ Does not have one of those words.
0002 @ a @ bear @ Does not have one of those words.
0003 @ b @ bear @ Has the word ver.
0004 @ a @ fish @ Does not have one of those words.
0005 @ c @ bear @ Does not have one of those words.
0006 @ c @ bear @ Does not have one of those words.
0007 @ a @ fish @ Does not have one of those words.
0008 @ c @ fish @ Does not have one of those words.
0009 @ a @ fish @ Does not have one of those words.
0010 @ c @ tiger @ This sentence contains var.
0011 @ c @ bear @ This sentence contains fam.
0012 @ a @ fish @ Does not have one of those words.
0013 @ c @ tiger @ This sentence contains fam.
```

Run:

```
$ txr sort.txr data.txt 
0004 @ a @ fish @ Does not have one of those words.
0007 @ a @ fish @ Does not have one of those words.
0009 @ a @ fish @ Does not have one of those words.
0012 @ a @ fish @ Does not have one of those words.
0002 @ a @ bear @ Does not have one of those words.
0001 @ b @ fish @ Does not have one of those words.
0003 @ b @ bear @ Has the word ver.
0005 @ c @ bear @ Does not have one of those words.
0006 @ c @ bear @ Does not have one of those words.
0008 @ c @ fish @ Does not have one of those words.
0010 @ c @ tiger @ This sentence contains var.
0013 @ c @ tiger @ This sentence contains fam.
0011 @ c @ bear @ This sentence contains fam.
```

Perform highly customized sort based on multiple columns of a CSV file?

Solution

Problem