make average row with the same pattern [duplicate]

Question

I have a huge table (10 columns and 6000 rows) and I have to make an average of all the rows only if they have the same pattern in column 3 (ko). My table contains numbers and strings.

Here's an example:

pvalue  padj    ko  pathway
17,14   0,01    ko00620 Pyruvatemetabolism
15,76   0,01    ko00620 Pyruvatemetabolism
13,22   0,00    ko00620 Pyruvatemetabolism
12,40   0,00    ko00051 Fructoseandmannosemetabolism
12,03   0,01    ko00051 Fructoseandmannosemetabolism

I would like to obtain something like this

pvalue  padj    ko  pathway
15,38   0,01    ko00620 Pyruvatemetabolism
12,22   0,00    ko00051 Fructoseandmannosemetabolism

Answer 1

Probably not the answer you're looking for, but what the heck, it's fun:

#!/usr/bin/env perl

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect(
    'dbi:CSV:',
    undef, undef, {
        f_ext           => '.csv/r',
        csv_sep_char    => "\t",
        csv_quote_char  => undef,
        csv_escape_char => undef,
    }
);

my $sth = $dbh->prepare(
    'SELECT AVG(pvalue), AVG(padj), ko, pathway
    FROM data GROUP BY ko, pathway'
);
$sth->execute();

while ( my $row = $sth->fetchrow_arrayref ) {
    printf "%.2f\t%.2f\t%s\t%s\n", @$row;
}

$dbh->disconnect;

The above assumes your data live in tab-separated CSV file named data.csv.

Answer 2

Maybe GNU's datamash is something for you:

$ datamash -H -g3,4 mean 1 mean 2 < file
GroupBy(ko) GroupBy(pathway)    mean(pvalue)    mean(padj)
ko00620 Pyruvatemetabolism  15,373333333333 0,0066666666666667
ko00051 Fructoseandmannosemetabolism    12,215  0,005

Answer 3

with awk

awk '
    NR == 1 {print;next} 
    {
        n[$3]++
        val[$3] += $1
        adj[$3] += $2
        path[$3] = $4
    } 
    END {
        for (ko in n) 
            printf "%.2f %.2f %s %s\n", val[ko]/n[ko], adj[ko]/n[ko], ko, path[ko]
    }
' file | column -t