List sorted on 1st field, how can I join 2nd field on lines where 1st field is the same?

Question

I have several lists with two fields - first field contain an URL, 2nd field an email-address (an account). The 2nd field is the same for all entries in a list.

I concatenate the lists to one list, and sort it by the 1st field. Most entries are unique, but some are duplicates or triplicates (ie. the URL was in the list for multiple accounts).

Is there a command or script that I can use to join the duplicates, so the 2nd field became a list of accounts when required?

For example:

url1   acct2
url2   acct1
url3   acct1
url3   acct2
url4   acct2
url4   acct3
url4   acct5
...

Should become:

url1   acct2
url2   acct1
url3   acct1 acct2
url4   acct2 acct3 acct5
...

Answer 1

With sort + awk pipeline:

sort -k1,1 file \
| awk 'url && $1 != url{ print url, acc }
      { acc = ($1 == url? acc FS:"") $2; url = $1 }END{ print url, acc }' OFS='\t'

Sample output:

url1    acct2
url2    acct1
url3    acct1 acct2
url4    acct2 acct3 acct5

Answer 2

Using GNU datamash:

$ datamash -W -g 1 collapse 2 < input.txt
url1    acct2
url2    acct1
url3    acct1,acct2
url4    acct2,acct3,acct5

Options:

• -W use spaces/tabs as field separator
• -g 1 group on the first field
• collapse 2 comma-separated list of values of the second field

You could convert the commas to spaces with tr:

$ datamash -W -g 1 collapse 2 < input.txt | tr ',' ' '
url1    acct2
url2    acct1
url3    acct1 acct2
url4    acct2 acct3 acct5

If you should need to sort the output on the first field, add -s:

datamash -s -W -g 1 collapse 2 < input.txt | tr ',' ' '