3

I have this file (a sparse matrix):

PC.354 OTU1 6
PC.354 OTU2 1
PC.356 OTU0 4
PC.356 OTU2 7
PC.356 OTU3 3

I want an output like this (dense matrix -classic .biom table):

OTU_ID PC.354  PC.355  PC.356
OTU0   0   0   4
OTU1   6   0   0
OTU2   1   0   7
OTU3   0   0   3

How can I do this with awk/perl/sed? I found a similar question about R package (xtabs/tidyr), but I'm not used to it.

4
  • Seems it's just a question of reading the values into an array, and printing it out. More of a programming exercise than anything specifically about Unix.
    – ilkkachu
    May 25 '16 at 19:38
  • hmm..could be that I'm a layman but your dense matrix appears to be a sparse matrix to me and the other way round
    – iruvar
    May 25 '16 at 20:22
  • Its a bioinformatic subject.. I'm just using the nomenclature as they use for it (biom-format.org/documentation/biom_format.html)... but, anyway, its not the point of the question.
    – Lika
    May 25 '16 at 20:27
  • 2
    this is similar to stackoverflow.com/questions/37116011/…
    – Jay jargot
    May 25 '16 at 21:15
4

In perl:

#!/usr/bin/perl

my (%hotu, %hpc)=();
while(<>){
  my($pc,$otu,$v)=split;
  $hpc{$pc}=1;
  ($hotu{$otu} or $hotu{$otu}={})->{$pc}+=$v;
}
#headers
my @apc = sort keys %hpc;
print join ("\t", 'OTU_ID', @apc) . "\n";
#values
foreach my $otu (sort keys %hotu) {
  print join ("\t", $otu, map {$_=0 unless defined; $_} @{$hotu{$otu}}{@apc}) . "\n";
}
3

In awk:

{ data[$2, $1] = $3; }
END {
    split("OTU0 OTU1 OTU2 OTU3", rows);
    split("OTU_ID PC.354 PC.355 PC.356", cols);
    for (i = 1; i <= 4; i++) {
        printf("%10s", cols[i]);
    }
    print "";
    for (i = 1; i <= 4; i++) {
        printf("%-10s", rows[i]);
        for (j = 2; j <= 4; j++) {
            item = data[rows[i], cols[j]];
            if (!item) { item = "0" };
            printf("%10s", item);
        }
        print "";
    }
}

Note that I have explicitly included all the rows and columns from your sample output. That is not necessary if the data actually includes all the rows and columns, which your sample data does not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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