add columns of numbers in various ways

add.pl

#!/usr/bin/perl -w
# (C) 2003-2007 Willem Jan Hengeveld <itsme@xs4all.nl>
# Web: http://www.xs4all.nl/~itsme/
#      http://wiki.xda-developers.com/
#
# $Id$
#

use strict;

# this script tries to split the input in colums, and add
# the numbers or expressions found either by row, or by column.

# done: add option to add by group
# todo: add option to add times, hh:mm:ss  or degrees   deg 'min ''sec
my $doAddPerRow= 0;
my $doAddPerColumn= 1;
my $ColumnSeparator= "\\s+";
my $StripLeadingSpace= 1;
my $doEval= 0;
my $doStripQuotes= 0;
my $anchorednrs= "^\\s*";
my $doExtractScale= 0;
my $groupcolumn;
use Getopt::Long;
Getopt::Long::Configure ("bundling");

sub usage {
    return <<__EOF__
Usage: add [options] [files]
   -r    print row totals too
   -c    [default]  print column totals
   -q    strip quotes
   -e    evaluate cells ( allows you to put numerical expressions in cells
   -m    extract numbers embedded in text, otherwise only pure number cells are used
   -l    don't strip leading spaces
   -s    extract scale ( kilo, Mega, etc )
   -g COL specify group column
   -t RE specify column separator
__EOF__
}
GetOptions(
    "r" => \$doAddPerRow,
    "c" => \$doAddPerColumn,
    "q" => \$doStripQuotes,
    "e" => \$doEval,
    "m" => sub { $anchorednrs= ""; },
    "l!"   => \$StripLeadingSpace,
    "s" => \$doExtractScale,
    "g=s" => \$groupcolumn,
    "t=s" => sub { $ColumnSeparator= parseColumnSeparator($_[1]); },
) or die usage();

sub parseColumnSeparator {
    my ($cs)= @_;
    if ($cs =~ /\/(.*)\//) {
        return qr($1);
    }
    else {
        return eval('"$cs"');
    }
}

if (!$doAddPerRow && !$doAddPerColumn) {
    die "did not specify how to sum\n";
}
my $outputseparator;
my @sums;
my %v;
my @decimals;
while(<>) {
    chomp;
    s/^\s+// if ($StripLeadingSpace);
    if (!defined $outputseparator) {
        if (/$ColumnSeparator/) {
            $outputseparator = $&;
        }
    }
    my @cols= split($ColumnSeparator, $_);

    # todo: extract separators, to be able to print result
    # with correct separators.
    if ($doStripQuotes) {
        @cols= map { if (/^'(.*)'$/) { $1; } elsif (/^"(.*)"$/) { $1 } else { $_ } } @cols;
    }
    for my $col (0..$#cols) {
#	    if ($cols[$col] =~ /\d([,.])\d\d\d(?:[.,])\d/) {
#		    $thousandsep[$col]{$1}++;
#		    $decimalsep[$col]{$1 eq "."?",":"."}++;
#	    }
#	    elsif ($cols[$col] =~ /\d([.,](?:\d{1,2}|\d{4,})\b/) {
#		    $decimalsep[$col]{$1}++;
#		    $thousandsep[$col]{$1 eq "."?",":"."}++;
#	    }
	    $cols[$col] =~ s/,//g;	# strip 1000's separator
	    if ($cols[$col] =~ /\.(\d+)/ && (!defined $decimals[$col] || length($1) > $decimals[$col])) {
		    $decimals[$col] = length($1);
	    }
    }
    if ($doEval) {
        @cols= map { my $res=eval $_; defined $res ? $res : $_ } @cols;
    }

    if ($groupcolumn) {
        $sums[$_]{$cols[$groupcolumn]} += extract_number($cols[$_]) for (0..$#cols);
        $v{$cols[$groupcolumn]}++;
    }
    elsif ($doAddPerRow) {
        printf("%10s :%s\n", sum(@cols), $_);
    }
    elsif ($doAddPerColumn) {
        $sums[$_]+= extract_number($cols[$_]) for (0..$#cols);
    }
}
if (!defined $outputseparator) {
    $outputseparator= "";
}

if ($groupcolumn) {
    for my $group (sort keys %v) {
        for my $col (0..$#sums) {
            print $outputseparator if ($col);
            if ($col==$groupcolumn) {
                printf("%s", $group);
            }
            else {
                printf("%.*f", $decimals[$col]||0, $sums[$col]{$group});
            }
        }
        printf("\n");
    }
}
elsif ($doAddPerColumn) {
    if ($doAddPerRow) {
        printf("%10s :", sum(@sums));
    }
    for my $col (0..$#sums) {
        print $outputseparator if ($col);
        printf("%.*f", $decimals[$col]||0, $sums[$col]);
    }
    printf("\n");
}

sub sum {
    my $sum=0;
    $sum+= extract_number($_) for (@_);
    return $sum;
}
sub get_scale {
    return 1 if (!defined $_[0] || length($_[0])==0);
    my $i= index("yzafpnum.kMGTPEZY", $_[0]);
    return 1000**($i-8) if ($i>=0);
    return 0.1 if $i eq 'd';
    return 0.01 if $i eq 'c';
    return 100 if $i eq 'h';

    return 1;
}
sub extract_number {
    if ($_[0] =~ /$anchorednrs(-?\d+(?:\.\d+)?(?:[eE]\d+)?)(?:\s*([yzafpnumcdhkMGTPEZY]))?/) {
        return $1*($doExtractScale?get_scale($2):1);
    }
    return 0;
}

exit(0);