# Sort -u (or sort | uniq) on a couple of columns but retain the info in a third column, and append it to the retained row?

I'm trying to take a table like this:

a     b     hello
a     b     goodbye
g     g     test
a     c     I say


to collapse the table down to one row per unique entry on column 1 and 2, but without losing any of the info in column 3. The values in column 3 could get appended as a comma-delimited list. The result would look this:

a     b     hello, goodbye
a     c     I say
g     g     test


I'm out of my element figuring out where to begin. Using sort -k1,1 -k2,2 -u, I get the following result:

a     b     hello
a     c     I say
g     g     test


I've lost the entry for "goodbye", but I'd like to keep it. Does anyone know how to avoid throwing away the data in column 3, as in the above example?

• How exactly are the columns of the table delimited? – steeldriver Aug 26 at 2:07
• The original table is tab-delimited, cheers. – Jon Aug 26 at 2:18

I don't know a way to do it using sort alone, but you could "collapse" the values using awk for example and then sort:

$awk -F'\t' ' BEGIN{OFS=FS} {k =$1 FS $2} {a[k] = a[k] == "" ?$3 : a[k] "," $3} END{for (k in a) print k,a[k]} ' file | sort a b hello,goodbye a c I say g g test  With a recent version of GNU awk, you can avoid the external sort by setting the array traversal order via PROCINFO: awk -F'\t' ' BEGIN{OFS=FS} {k =$1 FS $2} {a[k] = a[k] == "" ?$3 : a[k] "," $3} END{PROCINFO["sorted_in"]="@ind_str_asc"; for (k in a) print k,a[k]} ' file  Alternatively, with GNU datamash datamash groupby 1,2 collapse 3 <file  or more verbosely (but more flexibly) with Miller mlr --nidx --fs tab nest --implode --values --across-records --nested-fs , -f 3 file  • Awesome, thank you! – Jon Aug 26 at 2:51 A two-stage solution in bash that employs temporary files: $ cat table.csv
a       b       hello
a       b       goodbye
g       g       test
a       c       I say

$WD=$(mktemp -d) ; while read K1 K2 V ; do echo -n ",$V" >>$WD/$K1:$K2 ; done <table.csv

$sort -k1,1 -k2,2 -u table.csv | while read K1 K2 V ; do echo$K1 $K2$(sed 's/^.//' <$WD/$K1:\$K2) ; done
a b hello,goodbye
a c I say
g g test


The gist of this solution is using shell append redirection (>>) for gathering values for keys that are equal before sorting.

It can also be done with sed function (sorry, it's not really human-readable) :

echo "a      b       hello
a       c       goodbye
g       g       foo
a       c       bar
a       b       test
a       c       I say" | sort -k1,1 -k2,2 | sed ":a N; s/$$[^\t]*$$\t$$[^\t]*$$\t$$[^\t]*$$\n\1\t\2\t$$[^\t]*$$/\1\t\2\t\3,\4/; ta; P; D; ba;"


And the output is, as expected:

a       b       hello,test
a       c       bar,goodbye,I say
g       g       foo

• first sort, then use sed instead of uniq.
• N; will ask sed to work on 2 lines (or "one more line") instead of 1.
• s/.../.../ will merge the two lines.
• $$[^\t]*$$ will take a column (as many chars as possible, with not \t).
• \t is a tab (separator).
• \n\t\1\t\2\t checks if we have a second line with 2 similar first columns.
• /\1\t\2\t\3,\4/ transforms the 2 lines into one, with the same first and second column, and the third column is merged.
• :a ... ta; is like a while loop (do it again while s substitution worked).
• P; D; if we are here, the s failed, so we have two "different" (on first or second column) lines in pattern space. P prints the first line (with probably merged lines), and D deletes the line we printed.
• ba; loops while it can read lines (with currently 1 line in pattern space).