Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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.

share|improve this question
1  
where did uuu line come from (in the output)? –  saeedn Sep 11 '12 at 6:45
    
Sorry, my bad. I'll edit it. –  tiny Sep 11 '12 at 7:05
add comment

4 Answers

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
share|improve this answer
add comment

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"
share|improve this answer
add comment

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 ;-)

share|improve this answer
    
Hi, yeah it really needed breakdown to hash tables. Thank you! –  tiny Sep 11 '12 at 7:34
2  
@tiny - I was assuming the ordering needed to be preserved. Is this not the case (this answer produces ordering corresponding to the hashing mechanism, not your original order)? –  ire_and_curses Sep 11 '12 at 7:36
add comment

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]
share|improve this answer
    
Very cool. With my zero experience python I even managed to edit script that it takes first argument as input file name :) –  tiny Sep 11 '12 at 7:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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