-1

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
Improve this question
1
  • 1
    You want an average of which column(s)? It looks like your first columns has two fields and you want the average of each of those fields for each knockout. If so, why is the first value 38 and not 37? 14+76+22)/3=37.3. Do you also want the results truncated? Rounded? Do you also want the padj column to be treated as two fields and averaged? Please edit your question and clarify your requirements. Commented May 20, 2015 at 13:54

3 Answers 3

Reset to default
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.

Improve this answer
0

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
Improve this answer
7
  • I'have tried but does not work for my purpose there is something wrong?? Commented May 21, 2015 at 10:46
  • What isn't working? Does datamash give an error? Commented May 21, 2015 at 10:49
  • The script i used is this: datamash -H -g2,1,3,4,23,30,31 mean 5 mean 6 mean 7 mean 8 mean 9 mean 10 mean 11 mean 12 mean 13 mean 14 mean 14 mean 15 mean 16 mean 17 mean 18 mean 19 mean 20 mean 21 mean 22 mean 24 mean 25 mean 26 mean 27 mean 28 mean 29 < vo_ko_count_stat_ko_separati_col1.txt > prova.txt the reference column is the column 2 and it does not give the average of the column selected Commented May 21, 2015 at 10:56
  • Well, the file vo_ko_count_stat_ko_separati_col1.txt doesn't seem to contains the data you've described in OP and I don't have access to your data, so it's hard to tell where something is wrong. Commented May 21, 2015 at 11:01
  • the example that i post it's just an example because i have an huge table how i can send you the table? Commented May 21, 2015 at 11:03
0

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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.