15

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.

4
  • 1
    where did uuu line come from (in the output)?
    – saeedn
    Commented Sep 11, 2012 at 6:45
  • 1
    Sorry, my bad. I'll edit it.
    – tiny
    Commented Sep 11, 2012 at 7:05
  • Seems to be some confusion here: in a comment you say you're happy with a hash solution, which would leave you with either randomized keys or sorted keys (e.g. alphabetical). What's your preference? Or no preference? Commented Jul 22, 2023 at 21:48
  • Why not use tag words for a simple task like this. You can then pull out all lines with the same tag by a whole variety of tools (grep, sed etc) for further processing.
    – user167612
    Commented Jul 23, 2023 at 6:54

6 Answers 6

10

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 
2
  • 1
    Hi, yeah it really needed breakdown to hash tables. Thank you!
    – tiny
    Commented Sep 11, 2012 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)? Commented Sep 11, 2012 at 7:36
3

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]
2
  • Very cool. With my zero experience python I even managed to edit script that it takes first argument as input file name :)
    – tiny
    Commented Sep 11, 2012 at 7:30
  • Helpful, but the question very clearly states awk or sed, and is tagged awk and sed. A well-commented AWK script is just as easy to understand as what you have here!
    – Kevin E
    Commented Jul 22, 2023 at 11:19
2

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"
0
2

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
1
  • Syntax error when run, and doesn't retain the input order of the keys. And remember: ”Awk has to be better for something." ;)
    – Kevin E
    Commented Jul 22, 2023 at 13:30
0

Assuming the OP wanted to retain the input order as per the Desired output, then associative (string-indexed) arrays can't be used here. Output order is unpredictable when iterating over their keys with for (var in array), just like Perl's hashes or Python dictionaries prior to 3.6.

Here's a well-commented, plain-AWK solution which keeps the original order of the keys, as they first appear in the input file. Removing duplicates in the remaining columns would be a different challenge, but that doesn't seem to be required here.

This was tested on macOS with the BSD version of AWK, and should work with whatever awk you have. If desired, you can run it as a standalone shell script*—no one being the wiser that it was written in AWK—if you first chmod +x concat-by-first-col in order to mark it as an executable program.

#!/usr/bin/awk -f
##  concat-by-first-col
##    concatenate values from lines beginning with the same first column
##
##  usage:
##    $ chmod +x concat-by-first-col
##    $ ./concat-by-first-col inputfile > outputfile

BEGIN {
    # default output separator *is* a space, but if you wanted to change it…
    OFS = " "
}

{
    # assuming EVERY input record has AT LEAST the key…
    # append to `keys` if we haven’t seen this key before
    if (!($1 in values))
        keys[length(keys)+1] = $1

    # append second and subsequent columns to what we already have
    for (i = 2; i <= NF; i++)
        # insert an OFS *only* if there’s an existing value for this key
        values[$1] = (values[$1] ? values[$1] OFS : "") $i
}

END {
    # for all the keys, in the order they appeared in the input…
    for (i = 1; i <= length(keys); i++) {
        key = keys[i]
        # a comma stands in for OFS in AWK’s `print` statement
        print key, values[key]
    }
}

As an alternative to the standalone script, you can feed the program text to AWK with the -f switch instead:

awk -f concat-by-first-col inputfile > outputfile

If you also omit the BEGIN block, you then can specify OFS on the command line:

awk -v OFS='\t' -f concat-by-first-col inputfile > outputfile

Does it match the desired output? Check:

echo "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" > input

echo "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" > expected

# uses Bash’s process substitution**; expect no output
diff expected <(./concat-by-first-col input)

*For reasons, you may need to tweak the "shebang" line on your system, if you are on Linux and/or your version of AWK is not at /usr/bin/awk.

**https://www.gnu.org/software/bash/manual/html_node/Process-Substitution.html

0

Using Raku (formerly known as Perl_6)

~$ raku -ne 'BEGIN my %h;   \
             my @a = .split: / \s+ /;  %h.append: @a[0] => @a[1..*];  \
             END .put for %h.sort;'   file

Raku is a programming language in the Perl-family. Correspondingly, the Raku code herein bears some resemblance to the excellent Perl answer posted by @terdon.

Raku is called at the command line with the -ne non-autoprinting, linewise flags. Because a hash will be used to accumulate, a BEGIN statement is used to declare the %h hash but once. Columns are split on whitespace, and stored in the @a array. Then the elements of @a are appended into the %h hash, with @a[0] as key and @a[1..*] as value (the => is Raku's pairs syntax). at the END of reading all lines, the sorted %h hash is output.

Sample 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

Sample Output 1 (alphabetic key order):

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
www yyy hhh NULL NULL NULL NULL

Edit:

Raku code to keep original order of keys, as encountered.
(Hint: add %g hash with a $++ auto-incrementing line counter):

~$ raku -e 'my (%g, %h, @h);  for lines() { my @a = .split(/ \s+ /);  
            %g.append: @a[0] => $++;  %h.append: @a[0] => @a[1..*]
            };  @h = %h.sort(*.key)>>.kv; 
           .put for @h[ %g.sort.map( *.values>>.min).pairs.sort(*.values)>>.keys.flat ];'   file

Sample Output 2 (first-column key order):

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

https://docs.raku.org/language/hashmap#Mutable_hashes_and_immutable_maps
https://raku.org

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .