How can add values in each row and column and print at the end in Perl?

Question

Below is the sample csv file

date,type1,type2,.....
2009-07-01,n1,n2,.....
2009-07-02,n21,n22,....
and so on...

I want to add the values in each row and each column and print at the end and bottom of each line. i.e.

date,type1,type2
2009-07-01,n1,n2,.....row_total1
2009-07-02,n21,n22,....row_total2
Total,col_total1,col_total1,......total

Please suggest.

Answer 1

Is this something that needs to be done for sure in a Perl script? There is no "quick and dirty" method to do this in Perl. You will need to read the file in, accumulate your totals, and write the file back out (processing input and output line by line would be the cleanest).

If this is a one-time report, or you are working with a competent user base, the data you want can most easily be produced with a spreadsheet program like Excel.

Answer 2

The following in Perl does what you want, its not elegant but it works :-) Call the script with the inputfile as argument, results in stdout.

chop($_ = <>);

print "$_,Total\n";

while (<>) {

    chop;
    split(/,/);
    shift(@_);

    $sum = 0;

    for ($n = 0; 0 < scalar(@_); $n++) {
     $c = shift(@_);
     $sum += $c;
     $sums[$n] += $c;
    }

    $total += $sum;

    print "$_,$sum\n";
}

print "Total";

for ($n = 0; $n <= $#sums; $n++) {

    print "," . $sums[$n];
}

print ",$total\n";

Edit: fixed for 0 values.

The output is like this:

date,type1,type2,type3,Total
2009-07-01,1, 2, 3,6
2009-07-02,4, 5, 6,15
Total,5,7,9,21

Answer 3

Less elegant and shorter:

$ perl -plaF, -e '$r=0;$r+=$F[$_],$c[$_]+=$F[$_]for 1..$#F;$_.=",$r";END{$c[0]="Total";print join",",@c}'

Quick and dirty, but should do the trick in basic cases. For anything more complex, use Text::CSV and an actual script.

An expanded version as it's getting a little hairy:

#! perl -plaF,
$r=0;
$r+=$F[$_], $c[$_]+=$F[$_] for 1..$#F;
$_.=",$r";
END { $c[0]="Total"; print join ",", @c }'

Answer 4

Like JB's perlgolf candidate, except prints the end line totals and labels.

#!/usr/bin/perl -alnF,
use List::Util qw(sum);
chomp;
push @F, $. == 1 ? "total" : sum(@F[1..$#F]);
print "$_,$F[-1]";
for (my $i=1;$i<@F;$i++) {
    $totals[$i] += $F[$i];
}
END {
    $totals[0] = "Total";
    print join(",",@totals);
};

Answer 5

Here is a straightforward way which you can easily build upon depending on your requirements:

use strict;
use warnings;
use 5.010;
use List::Util qw(sum);
use List::MoreUtils qw(pairwise);
use Text::ParseWords;

our ($a, $b);
my @header = parse_csv( scalar <DATA> );
my @total  = (0) x @header;
output_csv( @header, 'row_total' );

for my $line (<DATA>) {
    my @cols  = parse_csv( $line );
    my $label = shift @cols;
    push @cols, sum @cols;
    output_csv( $label, @cols );
    @total = pairwise { $a + $b } @total, @cols;
}

output_csv( 'Total', @total );

sub parse_csv { 
    chomp( my $data = shift );
    quotewords ',', 0, $data; 
}

sub output_csv { say join ',' => @_ }

__DATA__
date,type1,type2
2009-07-01,1,2
2009-07-02,21,22

Outputs the expected:

date,type1,type2,row_total
2009-07-01,1,2,3
2009-07-02,21,22,43
Total,22,24,46

Some things to take away from above is the use of List::Util and List::MoreUtils:

# using List::Util::sum
my $sum_of_all_values_in_list = sum @list;

# using List::MoreUtils::pairwise
my @two_arrays_added_together = pairwise { $a + $b } @array1, @array2;

Also while I've used Text::ParseWords in my example you should really look into using Text::CSV. This modules covers more bizarre CSV edge cases and also provides correct CSV composition (my output_csv() sub is pretty naive!).

/I3az/

Answer 6

Whenever I work with CSV, I use the AnyData module. It may add a bit of overhead, but it keeps me from making mistakes ("Oh crap, that date column is quoted and has commas in it!?").
The process for you would look something like this:

use AnyData;
my @columns = qw/date type1 type2 type3/;  ## Define your input columns.
my $input = adTie( 'CSV', 'input_file.csv', 'r', {col_names => join(',', @columns)} );
push @columns, 'total';  ## Add the total columns.
my $output = adTie( 'CSV', 'output_file.csv', 'o', {col_names => join(',', @columns)} );
my %totals;
while ( my $row = each %$input ) {
    next if ($. == 1);  ## Skip the header row.  AnyData will add it to the output.
    my $sum = 0;
    foreach my $col (@columns[1..3]) {
        $totals{$col} += $row->{$col};
        $sum += $row->{$col};
    }
    $totals{total} += $sum;
    $row->{total} = $sum;
    $output->{$row->{date}} = $row;
}
$output->{Total} = \%totals;
print adDump( $output ); ## Prints a little table to see the data.  Not required.
undef $input; ## Close the file.
undef $output;

Input:

date,type1,type2,type3
2009-07-01,1,2,3
2009-07-03,31,32,33
2009-07-06,61,62,63
"Dec 31, 1969",81,82,83

Output:

date,type1,type2,type3,total
2009-07-01,1,2,3,6
2009-07-03,31,32,33,96
2009-07-06,61,62,63,186
"Dec 31, 1969",81,82,83,246
Total,174,178,182,534