Mdthbs

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.

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

< testfile 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:

< testfile 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:

< testfile 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 

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]

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"

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