Calculating subtotals from a table

pugsly62 has asked for the wisdom of the Perl Monks concerning the following question:

Hi,
Another simple question from a novice. I'm trying to get subtotals for the table below, both by county and by year. Do I have to run subroutines since my code has already used the '$yr_tot' variable to separate by county/year?

Thanks!
Scott

Table example:
COUNTY       2001       2002       2003       2004
------     ------     ------     ------     ------
 26005        432        505        547        555
 26021        910        984       1026       1077
 26023        225        282        272        299
 26025        711        796        809        899
 26027        250        319        318        313
 26059        196        221        242        292
 26077       1170       1371       1439       1439
 26149        355        415        346        367
 26159        414        434        504        493
[download]

Code Example:

my $records = 'scott.d3';
my $FhIn;
my $yr_01;
my $yr_02;
my $yr_03;
my $yr_04;
my $timestamp = localtime(time());

open $FhIn, '<', $records or die "Could not open $records: $!";
open(FILE, ">report.txt") or die "Can not open file";

while( my $line = <$FhIn> ) {
   my ( $county, $year_x, $yr_tot ) = split ' ', $line;
   if ( $year_x eq "1" ) {
   $yr_01 = $yr_tot;
}
   elsif ( $year_x eq "2" ) {
   $yr_02 = $yr_tot;
}
   elsif ( $year_x eq "3" ) {
   $yr_03 = $yr_tot;
}
   elsif ( $year_x eq "4" ) {
   $yr_04 = $yr_tot;
}
[download]

20050224 Janitored by Corion: Added formatting

20050224 Edit by castaway: Changed title from 'subtotals'

Comment on Calculating subtotals from a table Select or Download Code

Replies are listed 'Best First'.
Re: Calculating subtotals from a table by sh1tn (Priest) on Feb 24, 2005 at 16:06 UTC
my $data; my $years = qr{^\s*(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)}; my %map = ( 2, 2001, 3, 2002, 4, 2003, 5, 2004 ); while( <DATA> ){ /$years/ or next; $data->{country}{$1} = [ $2, $3, $4, $5 ]; $data->{years}{$map{$_}} += ${$_} for 2..5; } __DATA__ COUNTY 2001 2002 2003 2004 ------ ------ ------ ------ ------ 26005 432 505 547 555 26021 910 984 1026 1077 26023 225 282 272 299 #If print Dumper($data); $VAR1 = { 'country' => { '26149' => [ '355', '415', '346', '367' ], ..... 'years' => { '2004' => '5734', '2002' => '5327', '2001' => '4663', '2003' => '5503' } }; [download]	[reply] [d/l]
Re: Calculating subtotals from a table by artist (Parson) on Feb 24, 2005 at 16:22 UTC
use strict; use warnings; my ($county,@years) = split /\s+/, <DATA>; my $x = <DATA>; my $county_total; my $year_total; while(<DATA>){ chomp; my($county,@year_county_data) = split /\s+/; $county_total->{$county} += $_ for @year_county_data; foreach (0..$#years){ my $year= $years[$_]; my $data = $year_county_data[$_]; $year_total->{$year} += $data; } } print "County Total\n"; foreach my $county (sort keys %{$county_total}){ print "$county\t", $county_total->{$county},"\n"; } print "====\n"; print "Yearly Total\n"; foreach my $year (sort keys %{$year_total}){ print "$year\t", $year_total->{$year},"\n"; } __DATA__ COUNTY 2001 2002 2003 2004 ------ ------ ------ ------ ------ 26005 432 505 547 555 26021 910 984 1026 1077 26023 225 282 272 299 26025 711 796 809 899 26027 250 319 318 313 26059 196 221 242 292 26077 1170 1371 1439 1439 26149 355 415 346 367 26159 414 434 504 493 [download]	[reply] [d/l]
Re: Calculating subtotals from a table by Jasper (Chaplain) on Feb 24, 2005 at 18:02 UTC
perl -lp with `$t+=$_,$r{$.}+=$_,$c[$n++%4]+=$_ for((split)[1..4])x/^\s*\d/;print$_.= +" $r{$.}" }{print sprintf" %10d %10d %10d %10d $t",@c[0..3]` [download] gives me `COUNTY 2001 2002 2003 2004 ------ ------ ------ ------ ------ 26005 432 505 547 555 2039 26021 910 984 1026 1077 3997 26023 225 282 272 299 1078 26025 711 796 809 899 3215 26027 250 319 318 313 1200 26059 196 221 242 292 951 26077 1170 1371 1439 1439 5419 26149 355 415 346 367 1483 26159 414 434 504 493 1845 4663 5327 5503 5734 21227` [download]	[reply] [d/l] [select]
Re: Calculating subtotals from a table by exussum0 (Vicar) on Feb 24, 2005 at 15:27 UTC
You would benfit greatly from using a hash (datatype), also known as a dictionary. For every line, take the line in, and add it to the element for that year. i.e. `$totals{$year_x}+=$yr_tot;` update Later on, you would use `keys %totals` to get all years and you can iterate through that via some loop (for,foreach,while...) ---- Give me strength for today.. I will not talk it away.. Just for a moment.. It will burn through the clouds.. and shine down on me.	[reply] [d/l] [select]
Re: Calculating subtotals from a table by Anonymous Monk on Feb 24, 2005 at 16:24 UTC
I can't figure out what you are asking, so I just give you what I would use: #!/usr/bin/perl use strict; use warnings; # Deal with first two lines. chomp ($_ = <DATA>); print "$_ Total\n"; chomp ($_ = <DATA>); print "$_ ------\n"; my (@total, $grand_total); <DATA> for 1 .. 2; # Skip first two lines. while (<DATA>) { # Read in line by line chomp; my ($county, @years) = /\d+/g; # Extract numbers from line. my $sum; for (my $i = 0; $i < @years;$i ++) { $sum += $years[$i]; # County totals. $total[$i] += $years[$i]; # Year totals. } printf "%s %10d\n", $_, $sum; # Print line, with county total. } $grand_total += $_ for @total; # Final two lines, print yearly totals. print " ------ ------ ------ ------ ------\n +"; print " Total"; printf " %10d", $_ for @total, $grand_total; print "\n"; __DATA__ COUNTY 2001 2002 2003 2004 ------ ------ ------ ------ ------ 26005 432 505 547 555 26021 910 984 1026 1077 26023 225 282 272 299 26025 711 796 809 899 26027 250 319 318 313 26059 196 221 242 292 26077 1170 1371 1439 1439 26149 355 415 346 367 26159 414 434 504 493 [download] Running this gives me: `COUNTY 2001 2002 2003 2004 Total ------ ------ ------ ------ ------ ------ 26023 225 282 272 299 1078 26025 711 796 809 899 3215 26027 250 319 318 313 1200 26059 196 221 242 292 951 26077 1170 1371 1439 1439 5419 26149 355 415 346 367 1483 26159 414 434 504 493 1845 ------ ------ ------ ------ ------ Total 3321 3838 3930 4102 15191` [download]	[reply] [d/l] [select]