Concatenate lines by first column (AWK or SED)

Question

How can I use AWK in the following situation?

I want to concatenate lines that start with the same column. Only the first column is kept after the join (in this case aaa, www, hhh).

The file may be space- or tab-separated.

Example input:

aaa bbb ccc ddd NULL NULL NULL
aaa NULL NULL NULL NULL NULL NULL
aaa bbb ccc NULL NULL NULL NULL
www yyy hhh NULL NULL NULL NULL
hhh 111 333 yyy ooo hyy uuuioooy
hhh 111 333 yyy ooo hyy NULL

Desired output:

aaa bbb ccc ddd NULL NULL NULL NULL NULL NULL NULL NULL NULL bbb ccc NULL NULL NULL NULL
www yyy hhh NULL NULL NULL NULL
hhh 111 333 yyy ooo hyy uuuioooy 111 333 yyy ooo hyy NULL

The background to this is that I want to set up a very simple file-based database, where the first column is always the identifier for the entity. All lines based on the same identifier column are concatenated.

Answer 1

To get the first columns in each line using awk you can do the following:

cat test| awk '{print $1}'
aaa
aaa
aaa
www
hhh
hhh

These are your keys for the rest of the lines. So you may create a hash table, using the first column as a key and the second column of the line as the value:

cat test| awk '{table[$1]=table[$1] $2;} END {for (key in table) print key " => " table[key];}'
www => yyy
aaa => bbbNULLbbb
hhh => 111111

To get the whole rest of the line, starting with column 2, you need to collect all columns:

cat test| awk '{line="";for (i = 2; i <= NF; i++) line = line $i " "; table[$1]=table[$1] line;} END {for (key in table) print key " => " table[key];}'
www => yyy hhh NULL NULL NULL NULL 
aaa => bbb ccc ddd NULL NULL NULL NULL NULL NULL NULL NULL NULL bbb ccc    NULL NULL NULL NULL 
hhh => 111 333 yyy ooo hyy uuuioooy 111 333 yyy ooo hyy NULL 

That's it, I hope it helps ;-)

Answer 2

Someone else can answer in awk or sed, but a Python version is straightforward and might be helpful to you.

#!/usr/bin/env python

input_file = 'input.dat'
in_fh      = open(input_file, 'r')

input_order = []
seen        = {}
for line in in_fh:    
    # Remove the newline character...
    line = line[:-1]

    # Separate the first column from the rest of the line...
    key_col, sep, rest_of_line = line.partition(" ")
    rest_of_line = sep + rest_of_line  

    # If we've seen this key already, concatenate the line...
    if key_col in seen:
        seen[key_col] += rest_of_line
    # ...otherwise, record the ordering, and store the new info
    else:
        input_order.append(key_col)
        seen[key_col] = rest_of_line

in_fh.close()

# Dump the ordered output to stdout
for unique_col in input_order:
    print unique_col + seen[unique_col]

Answer 3

This is more of an interesting application of coreutils, I suspect it's not very efficient with large input as it invokes join for each line in the input.

touch outfile
while read; do
  join -a1 -a2 outfile <(echo $REPLY) > tmp
  mv tmp outfile
done < infile

To improve it's efficiency, saving outfile and tmp to a ramdisk might help.

Edit

Or without temporary files:

out=""
while read; do
  out=$(join -a1 -a2 <(echo -n "$out") <(echo -n "$REPLY"))
done < infile

echo "$out"

Answer 4

And here's a PERL one-liner:

$ perl -e 'my %h; while(<>){chomp; @a=split(/\s+/); $k=shift(@a); $h{$k}.=join(" ", @a) . " "; } map{$h{$_}=~s/\s*$//; print "$_ $h{$_}\n}keys(%hash);' infile