2

This is an extending question of the post Average rows with same first column

Input file:

a   12  13  14
b   15  16  17
a   21  22  23
b   24  25  26

Desired output:

a   16.5  17.5  18.5
b   19.5  20.5  21.5

The awk code in that post is:

awk '
    NR>1{
        arr[$1]   += $2
        count[$1] += 1
    }
    END{
        for (a in arr) {
            print a "\t" arr[a] / count[a]
        }
    }
'

Question: This code only works on the first row. How do I expand this code to multiple columns?

  • 3
    FWIW I'm starting to love GNU datamash after coming across it elsewhere on this site e.g. datamash --sort --whitespace groupby 1 mean 2-4 < file – steeldriver Sep 1 '16 at 0:10
  • I believe the code is like this: datamash --sort --whitespace -g 1 mean 2. And it doesn't look like it works for multiple columns. "mean 2-4" doesn't work. – Gray Sep 1 '16 at 3:09
  • 1
    the version I posted was tested in datamash version 1.1.0 – steeldriver Sep 1 '16 at 3:15
  • 1
    I'm using version 1.0.7. Maybe that's why. In this version, I have to do "datamash --whitespace -g 1 mean 2 mean 3 mean 4 < file". I'll try the new version – Gray Sep 1 '16 at 3:19
  • OK. I installed the latest version. It works perfectly. Thank you, my friend. You should make your comment the answer of my question so that I can give you credits. – Gray Sep 1 '16 at 3:27
2

Using awk, you could simulate a 2D array by constructing a composite index from the key (first column value) and column index:

awk '
  {
  c[$1]++; 
  for (i=2;i<=NF;i++) {
    s[$1"."i]+=$i};
  } 
  END {
    for (k in c) {
      printf "%s\t", k; 
      for(i=2;i<NF;i++) printf "%.1f\t", s[k"."i]/c[k]; 
      printf "%.1f\n", s[k"."NF]/c[k];
    }
  }' file
  a       16.5    17.5    18.5
  b       19.5    20.5    21.5

A similar approach may be implemented in perl more directly using a hash of arrays.


Alternatively, there's GNU datamash which (at least from version 1.1.0) supports group averages very compactly e.g.

datamash --sort --whitespace groupby 1 mean 2-4 < file
a       16.5    17.5    18.5
b       19.5    20.5    21.5

FWIW here's my attempt at a perl solution, including normalization to the global max average as requested in comments. DISCLAIMER: I'm a novice perl programmer, so it may demonstrate poor programming practices.

#!/usr/bin/perl

use strict;
use warnings;

use List::MoreUtils qw(pairwise minmax);
use Math::Round qw(nearest);

my @hdr;
my %sums = ();
my %count = ();
my $key;

while (defined($_ = <ARGV>)) {
  chomp $_;
  my @F = split(' ', $_, 0);

  # UGLY: hardcoded to expect exactly 1 header row
  if ($. == 1) {
    @hdr = @F;
    next;
  }

  # sum column-wise, grouped by first column
  $key = shift @F;
  if ( exists $sums{$key} ) {
    $sums{$key} = [ pairwise { $a + $b } @{ $sums{$key} }, @F];
  }
  else {
    $sums{$key} = \@F;
  }

  $count{$key}++;
}


my %avgs = ();
# NB should really initialize $maxavg to a suitably large NEGATIVE value
my $maxavg = 0.0;

# find the column averages, and the global max of those averages
for $key ( keys %sums ) {
  $avgs{$key} = [ map { $_ / $count{$key} } @{ $sums{$key} } ];
  # NB could use List::Util=max here, but we're alresdy using List::MoreUtils
  my ($kmin, $kmax) = minmax @{ $avgs{$key} };
  $maxavg = $kmax > $maxavg ? $kmax : $maxavg;
}

# normalize and print the results, rounded to nearest 0.01
print join "\t", @hdr, "\n";
for $key ( sort keys %avgs ) {
  print join "\t", $key, (map { nearest (0.01, $_ / $maxavg) } @{ $avgs{$key} }), "\n";
}

Saved as colavgnorm.pl and made executable, then run as

$ ./colavgnorm.pl file
K       C1      C2      C3
a       0.77    0.81    0.86
b       0.91    0.95    1

where file is

K   C1  C2  C3
a   12  13  14
b   15  16  17
a   21  22  23
b   24  25  26
  • Thank you again. One more question if you are still there. After averaging the values, how do I divide all the values with the largest value? In this case, the largest is 21.5. In real case, the largest value is not the last number. – Gray Sep 1 '16 at 15:34
  • @Gray do you mean the largest value of all - or do you want to normalize the values in the 'a' row to the max of the 'a' row and the values in the 'b' row to the max of the 'b' row? – steeldriver Sep 1 '16 at 16:11
  • I want to divide all the values in the matrix by the largest value of all. By the way, how do I add the headers back as datamash removed it when processing the file. – Gray Sep 1 '16 at 16:31
  • I don't think there's any shortcut to finding the global max - you'll just have to make a separate traversal of the whole array. Although that's undoubtedly possible in awk, it's getting to the point where a more full-featured language such as perl might be simpler. Regarding datamash headers, I don't know a way to make it re-use the original ones (it seems to want to add its own summary header - or none). – steeldriver Sep 1 '16 at 19:30
  • Thank you, steeldriver. I came up with a method to find out the global max value. datamash --sort --whitespace --header-in groupby 1 mean 2-4 < file | datamash --whitespace max 2-4 | perl -MList::Util=max -lane 'print max(@F)' – Gray Sep 1 '16 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.