Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a tab-delimited text file containing data like

Col 1 Col 2
1     a
1     b
1     c
1     d
1     d
2     a
2     b
3     a

so on and so forth.

I would like to transform this structure into

  a b c d
1 1 1 1 2
2 1 1 1 1
3 1 0 0 0

so that, a, b, c and d become columns; 1, 2 and 3 become a single row; and the numbers represent count. E.g. 1 has one "a" and two "d"s.

How can this be accomplished using awk or similar tools?

share|improve this question
Welcome to Super User! The formatting here can definitely be confusing if you're new. Check out this guide. I fixed it for you this time. Good luck! – Pops Aug 9 '12 at 22:24
awk 'NR>1 {
    printf("%3s", "");
    for (col in cols) {
        printf("%4s", col);
    for (row in rows) {
        printf("%3d", row);
        for (col in cols) {
            printf(" %3d", count[row,col]);
}' data

Not necessarily efficient or elegant, but it should be fairly easy to read and gets the job done. Also, the rows are columns will not necessarily be printed in sorted order. The key is the use of count[row,col] to simulate a multidimensional array, which is not directly supported in awk. A Google search for "awk multidimensional arrays" will turn up several articles including this one.

share|improve this answer

Here's a PERL solution:

  perl -e '
    my (%col1, %col2); 
        @a=split(/\s+/); ## split line on whitespace
        $col2{$a[1]}++; ## Collect unique values from the 2nd column
        $col1{$a[0]}{$a[1]}++;## Count values per column/line
    my @l=sort keys %col2; 
    $"="\t"; ## Array record separator, using tabs to deal with variable size input
    print "\t@l\n"; 
    foreach my $c1 (sort keys(%col1)) {## For each column1 value
        print "$c1\t"; 
        my $str;
        for (my $i=0; $i<=$#l; $i++) {
        ## Collect the values for each position or 0 if there is none
        $col1{$c1}{$l[$i]}="0" unless defined($col1{$c1}{$l[$i]});
    chop($str); ## remove extra \t 
    print "$str\n";
    }' data   >ll
share|improve this answer

You must log in to answer this question.

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