The always helpful GNU datmash to the rescue!
$ sort -k1,2 -u input.txt |
awk -v OFS="\t" '$2 < $1 { tmp = $1; $1 = $2; $2 = tmp } { print $1, $2, $3 }' |
sort -k1,2 |
datamash groupby 1,2 collapse 3 |
tr ',' ' '
ABC MNH 1 4
LOI PUQ 5
LOI QWE 3
LOI UHR 2 8
Broken down, this:
- Sorts the input file based on the first two columns and removes duplicates.
- If the second column is less than the first column, swaps the two (So
MNH ABC 6 becomes ABC MNH 6), and outputs tab-separated columns (Which is what datamash works with by default).
- Sorts that so all the transformed rows are in order (But this time keeping duplicates).
- Uses
datamash to produce a single line for all the duplicate first two columns, with a comma-separated list of the values of the third columns as the third column of the output (Like ABC MNH 1,4)
- Turns those commas into spaces.
Most memory-efficient solutions will require the data to be sorted, and while the sort program is quite good at doing that, it'll still use a bunch of temporary files so you'll need 2-3 or so terabytes of free disk space.
If you're going to be doing a lot of stuff with the same data, it's probably worth sorting it once and reusing that file instead of sorting it every time as the first step of a pipeline:
$ sort -k1,2 -u input.txt > unique_sorted.txt
$ awk ... unique_sorted.txt | ...
If there's enough duplicates and enough RAM that it's feasible to hold the results in memory, it can be done in one pass through the input file removing duplicates as it goes and then iterating through all the remaining pairs of values:
#!/usr/bin/perl
use warnings;
use strict;
use feature qw/say/;
my %keys;
while (<>) {
chomp;
my ($col1, $col2, $col3) = split ' ';
$keys{$col1}{$col2} = $col3 unless exists $keys{$col1}{$col2};
}
$, = " ";
while (my ($col1, $sub) = each %keys) {
while (my ($col2, $col3) = each %$sub) {
next unless defined $col3;
if ($col1 lt $col2 && exists $keys{$col2}{$col1}) {
$col3 .= " $keys{$col2}{$col1}";
$keys{$col2}{$col1} = undef;
} elsif ($col2 lt $col1 && exists $keys{$col2}{$col1}) {
next;
}
say $col1, $col2, $col3;
}
}
This produces output in arbitrary unsorted order for efficiency's sake.
And an approach using sqlite (Also requires lots of extra free disk space, and that the columns are separated by tabs, not arbitrary whitespace):
#!/bin/sh
input="$1"
sqlite3 -batch -noheader -list temp.db 2>/dev/null <<EOF
.separator \t
PRAGMA page_size = 8096; -- Make sure the database can grow big enough
CREATE TABLE data(col1, col2, col3, PRIMARY KEY(col1, col2)) WITHOUT ROWID;
.import "$input" data
SELECT col1, col2, group_concat(col3, ' ')
FROM (
SELECT col1, col2, col3 FROM data WHERE col1 < col2
UNION ALL
SELECT col2, col1, col3 FROM data WHERE col2 < col1
)
GROUP BY col1, col2
ORDER BY col1, col2;
EOF
rm -f temp.db
