in reply to Calculating Column Averages From A CSV File

Here is one way to do it. I scan over all of the data loading it into $data. Then scan over that storing the sum and count of non negative items per row and appending the average to the end of the row. While doing that it also stores the per column counts and sums in array refs. Then after everything is done it appends a new row with the averages for each column. It could probably be shrunk down a bit but i figured i'd start it out verbose for you. 

#!/usr/bin/perl
use strict;
use warnings;

use Data::Dumper;

my $data;

#read all data into an AofA
while (<DATA>) {
   chomp;
   push @$data, [split (/,/, $_)];
}
my $cols = 11;
my $col_sum;
my $col_count;
my $cur_col;
for my $row (@$data) {
   my $row_sum;
   my $row_count;
   my $cur_col = 1;
   for my $val (@$row[1..$cols]) {
      if ($val != 0) {
        $row_sum += $val;
        $row_count++;
        $col_sum->[$cur_col] += $val;
        $col_count->[$cur_col]++;
      }
      $cur_col++;
   }
   push @$row, $row_sum/ $row_count;
}

#print Dumper({col_sum=>$col_sum, col_count=>$col_count});
push @$data, ["TOTAL", map { $col_sum->[$_] / $col_count->[$_] } 1..$c
+ols];

for my $row (@$data) {
   print join(",", @$row),"\n";
}


__DATA__
IMAGINGNY,1.45,0.42,1.54,1.49,1.47,1.36,1.81,0.47,1.8,0.55,0.38 
JBSQLTST,1.29,1.09,1.13,1.88,1.11,1.44,1.25,1.23,1.05,1.39,1.61 
SNYCSQL,4.58,4.24,3.87,3.9,4.13,2.04,3.34,7.6,3.58,1.26,7.45 
Snynetsrv,26,26.34,24.59,26.46,26.24,26.14,32.35,31.77,31.77,29.92,26.
+59 
W32SPLMCOR01,8.27,13.23,7.73,8.85,9.15,13.95,0,0,0,0,0 
W32SDAPSCT01,3.07,3.14,2.97,3.28,21.65,54.23,3.16,3.02,3.26,2.77,3.40 
W32SDASALM01,1.22,1.3,1.11,0.92,1.57,1.06,1.01,0.87,0.93,2.26,0.91 
W32SMSCSD02,15.38,12,22.32,23.3,19.74,46.42,2.06,1.7,2.17,2.85,1.74 
W32SPLMCOR02,13.24,13.23,7.73,8.85,9.15,13.95,0,0,0,0,0

[download]


___________
Eric Hodges

Replies are listed 'Best First'.
Re^2: Calculating Column Averages From A CSV File
by eric256 (Parson) on Aug 14, 2007 at 00:27 UTC

    And a shorter version just for kicks:

    #!/usr/bin/perl
use strict;
use warnings;

use Data::Dumper;

my $data;

#read all data into an AofA
sub avg {
        my $s;
        $s += $_ for @_;
        return $s / grep { $_ != 0 } @_;
};

while (<DATA>) {
   chomp;
   push @$data, [@$_, avg( @$_[1..@$_-1])] for ([split(/,/, $_)]);
}

push @$data, ["TOTAL", map { my $col = $_; avg( map { $_->[$col] }  @$
+data) }  1..11];

print join(",", @$_),"\n" for @$data;


__DATA__
IMAGINGNY,1.45,0.42,1.54,1.49,1.47,1.36,1.81,0.47,1.8,0.55,0.38 
JBSQLTST,1.29,1.09,1.13,1.88,1.11,1.44,1.25,1.23,1.05,1.39,1.61 
SNYCSQL,4.58,4.24,3.87,3.9,4.13,2.04,3.34,7.6,3.58,1.26,7.45 
Snynetsrv,26,26.34,24.59,26.46,26.24,26.14,32.35,31.77,31.77,29.92,26.
+59 
W32SPLMCOR01,8.27,13.23,7.73,8.85,9.15,13.95,0,0,0,0,0 
W32SDAPSCT01,3.07,3.14,2.97,3.28,21.65,54.23,3.16,3.02,3.26,2.77,3.40 
W32SDASALM01,1.22,1.3,1.11,0.92,1.57,1.06,1.01,0.87,0.93,2.26,0.91 
W32SMSCSD02,15.38,12,22.32,23.3,19.74,46.42,2.06,1.7,2.17,2.85,1.74 
W32SPLMCOR02,13.24,13.23,7.73,8.85,9.15,13.95,0,0,0,0,0

    [download]

    And a final tiny squeeze:

    #!/usr/bin/perl
use strict;
use warnings;
use List::Util qw/sum/;

my $data;
sub avg { return sum(@_) / grep { $_ != 0 } @_;};

while (<DATA>) {
   chomp;
   push @$data, [@$_, avg( @$_[1..@$_-1])] for ([split(/,/, $_)]);
}

push @$data, ["TOTAL", map { my $col = $_; avg( map { $_->[$col] }  @$
+data) }  1..11];
print join(",", @$_),"\n" for @$data;

    [download]

    ___________
    Eric Hodges
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom