Tell me more ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.
up vote 2 down vote favorite
1

Given a file with two columns:

Id  ht
510 69
510 67
510 65
5l0 62
510 59
601 29
601 26
601 21
601 20

I need a way to coalesce all rows with the same ID into one that has an average height. In this case, (69 + 67 + 65 + 62 + 59) / 5 = 64 and (29 + 26 + 21 + 20) / 4 = 24, so the output should be:

Id  Avg.ht
 510 64
 601 24

How can I do that using sed/awk/perl?

share|improve this question
Are the same ids grouped together as shown in the sample? – choroba Oct 1 '12 at 22:18

3 Answers

up vote 5 down vote accepted

Using awk :

The input file

$ cat FILE
Id  ht
510 69
510 67
510 65
510 62
510 59
601 29
601 26
601 21
601 20

Awk in a shell :

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

Or with Perl in a shell :

$ perl -lane '
    END {
        foreach my $key (keys(%hash)) {
            print "id avg $key = " . $hash{$key} / $count{$key};
        }
    }
    if ($. > 1) {
        $hash{$F[0]}  += $F[1];
        $count{$F[0]} += 1;
    }
' FILE

Output is :

id avg 601 = 24
id avg 510 = 64.4

And last for the joke, a Perl dark-obfuscated one-liner =)

perl -lane'END{for(keys(%h)){print"$_:".$h{$_}/$c{$_}}}($.>1)&&do{$h{$F[0]}+=$F[1];$c{$F[0]}++}' FILE
share|improve this answer
Added Perl shot – sputnick Oct 2 '12 at 1:02
up vote 2 down vote 
#!/usr/bin/perl
use strict;
use warnings;

my %sum_so_far;
my %count_so_far;
while ( <> ) {
    # Skip lines that don't start with a digit
    next if m/^[^\d]/;

    # Accumulate the sum and the count
    my @line = split();
    $sum_so_far{$line[0]}   += $line[1];
    $count_so_far{$line[0]} += 1;
}

# Dump the output
print "Id Avg.ht\n";
foreach my $id ( keys %count_so_far ) {
    my $avg = $sum_so_far{$id}/$count_so_far{$id};
    print " $id $avg\n";
}

Output:

ire@localhost$ perl make_average.pl input.txt 
Id Avg.ht
 510 64.4
 601 24

Note that your sample output is wrong. There's no way you can get an average of 52 when every value for that id is 59 or larger.

Also, you have a letter l in one of your columns, masquerading as the number 1...

share|improve this answer
up vote 1 down vote

Take a look at what is done here: http://www.sugihartono.com/programming/group-by-count-and-sorting-using-perl-script/

The essential difficult part is doing a 'group by' operation. The linked script does that using a hash.

In that link they are calculating the sum, but getting the average will not be far different.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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