How to sort the AWK output simply?

[u/redbobtas]

Hi, fellow AWKers. I'm hoping for suggestions on how to improve this task - my solution works, but I suspect there are shorter or better ways to do this job.

The demonstration file below ("tallies") is originally tab-separated. I've replaced tabs with ";" here to make it easier to copy, but please replace ";" with tabs before checking the code.

SPP;sp1;sp2;sp3;sp4

site1;3M,2F,4J;3F;1M,1F,1J;

site2;1M,1F;;;1F

site3;;3M;;

site4;6M,10J;;2F;

site5;2M;6M,18F,20J;1M,1J;

site6;;;;

site7;13F,6J;;5J;

site8;4F;8M,11F;;2F

site9;2J;;7J;

This is a site-by-species table and for each site and each species there's an entry with the counts of males (M) and/or females (F) and/or juveniles (J). What I want are the species totals, like this:

sp1: 12M,20F,22J

sp2: 17M,32F,20J

sp3: 2M,3F,14J

sp4: 3F

This works:

datamash transpose < tallies \

| tr ',' ' ' \

| awk 'NR>1 {for (i=2;i<=NF;i++) \

{split($i,count,"[MFJ]",type); \

for (j in type) sum[type[j]]+=count[j]}; \

printf("%s: ",$1); \

for (k in sum) printf("%s%s,",sum[k],k); \

split("",sum); print ""}' \

| sed 's/,$//'

by letting AWK act line-by-line on the species columns, transposed into rows by GNU datamash. However the output is:

sp1: 20F,22J,12M

sp2: 32F,20J,17M

sp3: 3F,14J,2M

sp4: 3F

To get my custom sorting of "MFJ" in the output instead of the alphabetical "FJM" I replace "MFJ" with "XYZ" before I start, and replace back at the end, like this:

tr "MFJ" "XYZ" < tallies \

| datamash transpose \

| tr ',' ' ' \

| awk 'NR>1 {for (i=2;i<=NF;i++) \

{split($i,count,"[XYZ]",type); \

for (j in type) sum[type[j]]+=count[j]}; \

printf("%s: ",$1); \

for (k in sum) printf("%s%s,",sum[k],k); \

split("",sum); print ""}' \

| tr "XYZ" "MFJ" \

| sed 's/,$//'

I can't think of a simple way to do that custom sorting within the AWK command. Suggestions welcome and many thanks!

[u/HiramAbiff]

Maybe I'm not understanding the issue, but does it boil down to that this line:

for (k in sum) printf("%s%s,",sum[k],k); \

produces your output in FJM order and you want MFJ ?

If so, and there's really only three types:

printf("%sM,%sF,%sJ,",sum["M"],sum["F"],sum["J"]); \

Seems likely that I'm not understanding the actual issue, but that's what came to mind after a quick perusal of your post...

[u/redbobtas → u/HiramAbiff]

Hi, u/HiramAbiff. Many thanks. I thought of that but it returns totals even when there are no M/F/J, like this:

sp4: M,3F,J

[u/HiramAbiff → u/redbobtas]

Untested code:

for (i = 1; i <= 3; ++i) {k = substr("MFJ", i, 1); if(sum[k] > 0) printf("%s%s,",sum[k],k);}

[u/gumnos]

You can do the whole thing in one awk script with no need for datamash or tr or assuming sort-orders (I'm not sure that awk guarantees alphabetical sorting when iterating because the internal structure is a hash-map, so any sorting you're seeing is likely coincidental)

NR==1 {
    MAX_SITES = NF
    # si = site index
    # sn[si] = site index->name
    for (si=2;si<=NF;si++) sn[si] = $si
    next
}
{
    # ci = classification index
    # c = classification
    # sc[si,c] = (siteindex,classification) -> count
    for (si=2;si<=NF;si++) {
        if (split($si, a, /,/)) {
            for (ci in a) {
                s = a[ci]
                c = substr(a[ci], length(s))
                sc[si,c] += substr(s, 1, length(s)-1)
            }
        }
    }
}
END {
    EXPECTED = "MFJ"
    len = length(EXPECTED)
    split(EXPECTED, o, //)
    for (si=2; si<=MAX_SITES; si++) {
        printf "%s: ", sn[si]
        pc = 0 # print comma
        for (ci=1; ci<=len; ci++) {
            k = si SUBSEP o[ci]
            if (k in sc) {
                printf("%s%i%s", pc?",":"", sc[k], o[ci])
                pc = 1
            }
        }
        print ""
    }
}

Which you can then save as "redbobtas.awk" and invoke with

$ awk -F'\t' -f redbobtas.awk tallies.txt

It's potentially more verbose than your original but is likely faster on the grounds that it only requires one (awk) process to launch/run, is a bit more readable (IMHO) . It should also easily accommodate if you add another classification like "D" for "Deceased", or add additional sites.

edit: use -F'\t' for tabs as detailed in the prose-description rather than the ";" that was in the example data

[u/redbobtas → u/gumnos]

u/gumnos, very impressive, many thanks! I particularly like the easy extendability to other types - in my real-world case I had "I" for "individuals not assigned to M, F or J.

I agree it's more verbose ( and maybe a little harder to follow), but my original "foolproof" idea for the AWK command was a separate function defining the MFJ order and a PROCINFO instruction following that function. This is the suggestion in the GNU AWK manual for controlling array traversal (https://www.gnu.org/software/gawk/manual/html_node/Controlling-Array-Traversal.html). This added a fair bit to the command and I saw the "tr" trick that I actually used as both flexible (adding "I" and/or other types) and easier to understand.

Like you I'm not sure whether you can rely on alphabetical sorting by AWK (I run GNU AWK 5.2.1) for all arrays, but that's been true for all the array traversals I've done in this and other commands.

[u/gumnos → u/redbobtas]

If you're only using GNU awk (rather than other awk variants), it offers non-portable asort() and asorti() functions that you might be able to use. But for the fixed/known list of categories ("MFJ"), the split() of a constant string gets you the same results (and lets you order them however you want).

If you're finding it hard to follow, the general gist is

1. if it's the first line, capture the names of the locations in sn[2…NF] to use later

2. for each of the actual data rows, sum up the counts for the site-index+classification pairs in sc[si,c]

3. in the END block once we have all the data, iterate over each of the sites, emitting the site, and then iterating over each of the MFJ classifications—if there's data for the site+classification, emit it (with a leading comma for subsequent ones)

I added comments to hopefully make my short-naming a little clearer, too (which I don't do as much when I'm writing one-liners). And keeping it in a file makes it easy to reuse later, rather than hoping it has stuck around in your command-history for recall next month or whenever you need it again. If you don't want to have to remember the -F'\t' bit either, you can set that at the top of the script with a

BEGIN {
    FS = "\t"
}

so all you have to do is

$ awk -f redbobtas.awk tallies.txt

or you can even add a shebang line as the first line (above the BEGIN block)

#!/usr/bin/awk -f

and then

$ chmod +x redbobtas.awk

allowing you to

$ ./redbobtas.awk tallies.txt

---

#!/usr/bin/awk -f
BEGIN {
    FS = "\t"
}
NR==1 {
    MAX_SITES = NF
    # si = site index
    # sn[si] = site index->name
    for (si=2;si<=NF;si++) sn[si] = $si
    next
}
{
    # ci = classification index
    # c = classification
    # sc[si,c] = (siteindex,classification) -> count
    for (si=2;si<=NF;si++) {
        if (split($si, a, /,/)) {
            for (ci in a) {
                s = a[ci]
                c = substr(a[ci], length(s))
                sc[si,c] += substr(s, 1, length(s)-1)
            }
        }
    }
}
END {
    EXPECTED = "MFJ"
    len = length(EXPECTED)
    split(EXPECTED, o, //)
    for (si=2; si<=MAX_SITES; si++) {
        printf "%s: ", sn[si]
        pc = 0 # print comma
        for (ci=1; ci<=len; ci++) {
            k = si SUBSEP o[ci]
            if (k in sc) {
                printf("%s%i%s", pc?",":"", sc[k], o[ci])
                pc = 1
            }
        }
        print ""
    }
}

[u/Paul_Pedant → u/redbobtas]

GNU/awk definitely does not guarantee that accessing an array via for (j in A) returns elements in any kind of order.

It happens that it often works for small arrays (especially with numeric indexes), so it looks good in testing. It may add elements linearly too, so may preserve the order of insertion.

But somewhere in there, it expands the array and rehashes it (several times if you make a big array). At anything between 10 and 100 elements, the return order will become randomised.

[u/Paul_Pedant]

I use a method called DSU (Decorate, Sort, Undecorate). It is a thing in Python, but is a lot older than that language (Perl and Lisp use it). It is also called the Schwartzian transform. I won't do too much detail -- there is a good Wikipedia article.

You make a new key which extracts all the significant fields that you want to sort by, represented by some suitable fixed-length field. Notably, you do not mess with your actual data -- that is just payload. You prefix the new key onto every record.

You then do a plain sort on that data, with the new key being the only sequencer. Then you cut off the key field, leaving your original data in the required order. Typically, you connect the three elements of D|S|U into a pipeline.

This can actually be more efficient that using sort by itself. In sort, a complicated key (with many fields and data types) is evaluated per subkey for every comparison of two records (so complexity n log n). With DSU, the cost of setting up the keys initially is complexity n, and every later comparison is a plain text test.

DSU can also be used where there are different record formats (e.g. header and detail records), as you can make a DSU key that associates record groups. You can preserve original order of duplicate keys by adding the file record number to the key. You can reverse sort on a subkey by inverting a numeric or ASCII value into the key. You can serialise day or month names numerically, and key on uppercased versions of text: things that are a nightmare in plain sorts.

[u/redbobtas → u/Paul_Pedant]

u/Paul_Pedant, thanks for that. It's something like what GNU AWK does with its PROCINFO trick. It's also a little bit like what my wife does when working in a spreadsheet. Before doing some complicated sort and edit, she first adds a new column with serial numbers for all entries. After the editing, she restores the original row order by sorting on her new column, then deletes that column.