这是帖子的延伸问题第一列相同的平均行
输入文件:
a 12 13 14
b 15 16 17
a 21 22 23
b 24 25 26
期望的输出:
a 16.5 17.5 18.5
b 19.5 20.5 21.5
该帖子中的 awk 代码是:
awk '
NR>1{
arr[$1] += $2
count[$1] += 1
}
END{
for (a in arr) {
print a "\t" arr[a] / count[a]
}
}
'
问题:此代码仅适用于第一行。如何将此代码扩展到多列?
答案1
使用awk
,您可以通过从键(第一列值)和列索引构造复合索引来模拟二维数组:
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
类似的方法可以perl
更直接地使用数组的散列来实现。
或者,GNU datamash
(至少从版本 1.1.0 开始)非常紧凑地支持组平均值,例如
datamash --sort --whitespace groupby 1 mean 2-4 < file
a 16.5 17.5 18.5
b 19.5 20.5 21.5
FWIW 这是我对 perl 解决方案的尝试,包括按照评论中的要求标准化为全局最大平均值。免责声明:我是一名新手 Perl 程序员,因此它可能表现出糟糕的编程实践。
#!/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";
}
另存为colavgnorm.pl
并可执行,然后运行
$ ./colavgnorm.pl file
K C1 C2 C3
a 0.77 0.81 0.86
b 0.91 0.95 1
哪里file
K C1 C2 C3
a 12 13 14
b 15 16 17
a 21 22 23
b 24 25 26