3

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
2

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
| improve this answer | |
  • Awesome, thank you! – Jon Aug 26 at 2:51
0

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.

| improve this answer | |
0

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).
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.