Re: compare values while value on other column remains the same

[CountZero]

I turned your data into a tab-separated CSV file and added a line of headers (fieldnames). Then I could use DBI to attack your problem with SQL.

use Modern::Perl;
use DBI;

my $dbh = DBI->connect(
    "dbi:CSV:",
    "", "",
    {
        f_dir        => './DB',
        csv_sep_char => "\t",
    }
) or die "Cannot connect: $DBI::errstr";

my $sql =
'SELECT id, count(*) AS number, sum(tumor) AS tumorscore, sum(normal) 
+AS normalscore, sum(tumor - normal) AS difference, avg(tumor - normal
+) AS averagedifference FROM methyl WHERE (tumor - normal) <=20 or (tu
+mor - normal) >=20 GROUP BY id ORDER BY id';

my $sth = $dbh->prepare($sql);
$sth->execute;
while ( my $row = $sth->fetchrow_hashref ) {
    say
"id: $row->{'id'}, Count: $row->{'number'}, Score tumor: $row->{'tumor
+score'}, Score normal: $row->{'normalscore'}, Difference: $row->{'dif
+ference'}, Average Difference: $row->{'averagedifference'}";
}

Results:

id: cg00381604, Count: 3, Score tumor: 99, Score normal: 174, Differen
+ce: -75, Average Difference: -25
id: cg03130891, Count: 1, Score tumor: 55, Score normal: 84, Differenc
+e: -29, Average Difference: -29
id: cg12045430, Count: 3, Score tumor: 129, Score normal: 174, Differe
+nce: -45, Average Difference: -15
id: cg13869341, Count: 1, Score tumor: 908, Score normal: 913, Differe
+nce: -5, Average Difference: -5
id: cg14008030, Count: 1, Score tumor: 688, Score normal: 776, Differe
+nce: -88, Average Difference: -88
id: cg20253340, Count: 1, Score tumor: 560, Score normal: 593, Differe
+nce: -33, Average Difference: -33
id: cg20826792, Count: 3, Score tumor: 171, Score normal: 174, Differe
+nce: -3, Average Difference: -1
id: cg21870274, Count: 1, Score tumor: 791, Score normal: 809, Differe
+nce: -18, Average Difference: -18

To exclude as much as possible any rounding-of errors I did not divide the figures by 1000.