Collapse rows with multiple fields

Question

I have this code:

awk '!seen[$1,$2]++{a[$1]=(a[$1] ? a[$1]", " : "\t") $2} END{for (i in a) print i a[i]} ' inputfile

and I would like to work to collapse rows with more than two fields, but always rely on the first field as an index.

Input file (three columns with tab delimiters):

protein_1   membrane    1e-4
protein_1   intracellular   1e-5
protein_2   membrane    1e-50
protein_2   citosol 1e-40

Desired output (three columns with tab delimiters):

protein_1   membrane, intracellular 1e-4, 1e-5
protein_2   membrane, citosol   1e-50, 1e-40

Thank!

Stack:

awk '!seen[$1,$2]++{a[$1]=(a[$1] ? a[$1]"\t" : "\t") $2};{a[$1]=(a[$1] ? a[$1]", " : "\t") $3} END{for (i in a) print i a[i]} ' 1 inputfile

Answer 1

I really hope someone will post some awk magic, but I will go ahead and throw out a longer form of perl script:

use strict;
use warnings;

my @cols = ();
my $lastprotein = '';

while (<DATA>) {
    chomp;
    my ($protein, @data) = split "\t";

    if ($protein ne $lastprotein && @cols) {
        print join("\t", $lastprotein, map {join ', ', @$_} @cols), "\n";
        @cols = ();
    }

    push @{$cols[$_]}, $data[$_] for (0..$#data);
    $lastprotein = $protein;
}

print join("\t", $lastprotein, map {join ', ', @$_} @cols), "\n";

__DATA__
protein_1   membrane    1e-4
protein_1   intracellular   1e-5
protein_2   membrane    1e-50
protein_2   citosol 1e-40

Outputs

protein_1       membrane, intracellular 1e-4, 1e-5
protein_2       membrane, citosol       1e-50, 1e-40

Answer 2

perl -lane'
  $ar = $h{shift @F} ||= [];
  push @{$ar->[$_]}, $F[$_] for 0,1;
  END {
    $" = ", ";
    print "$_\t@{$h{$_}[0]}\t@{$h{$_}[1]}" for sort keys %h;
  }
' file

Output

protein_1 membrane, intracellular 1e-4, 1e-5
protein_2 membrane, citosol 1e-50, 1e-40

Answer 3

GNU awk :

$ gawk '
{ a[$1][$2] = $3 }
END {
    for (i in a) {
        printf "%s", i
        sep = "\t"
        for (j in a[i]) {
            printf "%s%s", sep, j
            sep = ", "
        }
        sep = "\t"
        for (j in a[i]) {
            printf "%s%s", sep, a[i][j]
            sep = ", "
        }
        print ""
    }
}' file
protein_1       membrane, intracellular 1e-4, 1e-5
protein_2       membrane, citosol       1e-50, 1e-40

Answer 4

:

awk '{
    col1[$1]++
    for(fld = 2; fld <= NF; fld++) {
        line[$1,fld] = (line[$1,fld]) ? line[$1,fld] ", " $fld : $fld
    }
}
END {
    for(name in col1) {
        printf "%s\t", name
        for(item = 2; item <= NF; item++) {
            printf "%s\t", line[name,item]
        }
        print ""
    }
}' file

protein_1       membrane, intracellular 1e-4, 1e-5
protein_2       membrane, citosol       1e-50, 1e-40