Organizing three dimensional data with awk/sed/perl

awkbioinformaticsperlsedtext processing

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.

Best Answer

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";
}

Related Solutions

awk – Multiline Regexp with Grep, Sed, Awk, and Perl

You can do this with Awk by setting the "Record Separator" variable to be a regex matching at least two consecutive newline characters:

awk -v RS='\n\n+' '/1.*2.*3/' file.txt

You can also set the "Field Separator" to be a single newline character:

awk -v RS='\n\n+' -F '\n' '$1 == "LINE OF TEXT 1" && $2 == "LINE OF TEXT 2" && $3 == "LINE OF TEXT 3"' file.txt

Broken up for readability:

awk -v RS='\n\n+' -F '\n' '
  $1 == "LINE OF TEXT 1" &&
  $2 == "LINE OF TEXT 2" &&
  $3 == "LINE OF TEXT 3"
' file.txt

With your requirement of only printing the filename if a match is found, you can do this like so:

awk -v RS='\n\n+' -F '\n' '
  $1 == "LINE OF TEXT 1" &&
  $2 == "LINE OF TEXT 2" &&
  $3 == "LINE OF TEXT 3" {
    match++
  }
  END {
    if (match) {
      print FILENAME
    }
' file.txt

But considering you are talking about using find in combination with awk, I'd recommend just using Awk for the exit status and using find for the printing:

find . -type f -exec awk -v RS='\n\n+' -F '\n' '
  $1 ~ /LINE OF TEXT 1/ &&
  $2 ~ /LINE OF TEXT 2/ &&
  $3 ~ /LINE OF TEXT 3/ {
    exit 0
  }
  END { exit 1 }
' {} \; -print

That way, if you want to do something else before printing (some other find primary), you're already set up to do so.

Bash – Summing rows in a new column using sed, awk and perl

Perl solution:

perl -MList::Util=sum -lane 'print "@F ", sum(@F)' < data.txt

-n reads the input line by line
-l removes newlines from input and adds them to output
-a splits each input line on whitespace into the @F array
List::Util provides the sum function so you don't have to sum the numbers yourself

In sed, arithmetic is nearly impossible to implement, but you can use sed to turn spaces into pluses and use that as a source for bc to get the sums, and paste the results with the input:

paste -d ' ' data.txt <(sed -r 's/ /+/g' data.txt | bc)

Best Answer

Related Solutions

awk – Multiline Regexp with Grep, Sed, Awk, and Perl

Bash – Summing rows in a new column using sed, awk and perl

Related Question