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


##</code><code>##

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

##</code><code>##

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;

##</code><code>##

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


##</code><code>##

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

##</code><code>##

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


##</code><code>##

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