Re: Consolidating biological data into occurance of each biological unit per sample in table

[CountZero]

in reply to Consolidating biological data into occurance of each biological unit per sample in table

One split can get you your data:

use Modern::Perl;

while (<DATA>) {
    chomp;
    my ($id, $sample_id, undef, $animal, undef, $color) = split /[\t_;
+]+/;
    say "$id ($sample_id) is a $animal colored $color";
}

__DATA__
occurence1    A    a__bear;c__black
occurence2    B    a__wolf;c__grey
occurence3    A    a__wolf;c__white
occurence4    A    a__bear;c__
occurence5    C    a__wolf;c__grey
occurence6    C    a__bear;c__brown

Output:

occurence1 (A) is a bear colored black
occurence2 (B) is a wolf colored grey
occurence3 (A) is a wolf colored white
occurence4 (A) is a bear colored 
occurence5 (C) is a wolf colored grey
occurence6 (C) is a bear colored brown

Now that we have the data, why not putting it into a database such as SQLite?

use Modern::Perl;
use DBI;

my $dbh = DBI->connect(
"dbi:SQLite:dbname=c:/data/strawberry-perl/perl/script-chrome/animals.
+sqlite",
    "", ""
) or die "Could not open database";
my $statement =
q{CREATE TABLE IF NOT EXISTS "main"."sightings" ("id" TEXT PRIMARY KEY
+ NOT NULL, "sampleid" TEXT, "animal" TEXT, "color" TEXT)};
my $rows = $dbh->do($statement) or die $dbh->errstr;

$dbh->{AutoCommit} = 0;
$dbh->do('BEGIN TRANSACTION');
my $sth = $dbh->prepare(
    q{INSERT INTO sightings (id, sampleid, animal, color) VALUES (?, ?
+, ?, ?)})
  or die "Could not prepare INSERT statement";
while (<DATA>) {
    chomp;
    my ( $id, $sample_id, undef, $animal, undef, $color ) = split /[\t
+_;]+/;
    $color ||= 'unknown';
    $sth->execute( $id, $sample_id, $animal, $color );
}
$dbh->commit;

Now we can interrogate the database and use aggregate functions to get your answers:

SELECT sampleid, animal, count(*) FROM sightings GROUP BY sampleid, an
+imal

will return

"A","bear","6"
"A","wolf","4"
"B","bear","2"
"B","wolf","7"
"C","bear","6"
"C","wolf","5"

And

SELECT sampleid, color, count(*) FROM sightings GROUP BY sampleid, col
+or

will give you

"A","black","1"
"A","brown","3"
"A","grey","1"
"A","red","1"
"A","unknown","3"
"A","white","1"
"B","brown","1"
"B","grey","5"
"B","red","1"
"B","unknown","2"
"C","black","1"
"C","brown","4"
"C","grey","3"
"C","unknown","2"
"C","white","1"