Dereferencing Hashes of Arrays

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

I'm a beginner. I have a problem were I have a spreadsheet and I put the values of the spreadsheet in a Hashes of Arrays. For example:

spr.xls

empolyee Buffalo Cleveland
Terry 5.00 2.00
Doug 4.00 3.00
Fred 1.00 5.00
So I read the spread and like I said I put the values in Hash of Arrays like this:

$employee=$infor[ 0 ];
shift @infor;
$sheet{$employee}={data => [@infor]};
[download]

So, I keyed the hash by employee name and the value is a reference to an array of info Then I can print all the values like this:

foreach (keys $sheet){
    print "\n$_ @{$sheet{$_}->{data}}";
}
[download]

and it prints:
Fred 1.00 5.00
Terry 5.00 2.00
Doug 4.00 3.00
empolyee Buffalo Cleveland

but I want to get access to the columns and rows so I can print the total of all the columns and rows and the output would be like this:

empolyee Buffalo Cleveland Total
Terry 5.00 2.00 7.00
Doug 4.00 3.00 7.00
Fred 1.00 5.00 6.00
Total 10.00 10.00 20.00

my question is how can I get access to the Buffalo column or Terry row so I can add up for the totals?

thanks for the help

Edit by castaway - added code tags

Comment on Dereferencing Hashes of Arrays Select or Download Code

Replies are listed 'Best First'.
Re: Dereferencing Hashes of Arrays by Errto (Vicar) on Mar 13, 2005 at 05:47 UTC
A hash isn't a spreadsheet, so you can't get "access" to a column. But you can add up the arrays for each key and the values for each position in the array. However, I don't really think the data structure you have is a great one. There are two issues: You have the labels as just another value in the hash. This won't work because you need to be sure to print them first, and because you don't want to do any calculations on them. So I'm going to just not make them part of the data structure at all. For each key in the hash, you are actually not assigning an array, but a hash with a single key called "data." This doesn't help much, so I won't use it and use an array instead. Here's some code: `use strict; use warnings; my @columntotals; my %sheet = (Terry => [5, 2], Doug => [4, 3], Fred => [1, 5]); print "Employee Buffalo Cleveland Total\n"; foreach my $key (keys %sheet) { my @data = @{$sheet{$key}}; my $sum = 0; $sum += $_ for @data; push @data, $sum; print "$key @data\n"; $columntotals[$_] += $data[$_] for 0 .. $#data; } print "Total @columntotals\n";` [download]	[reply] [d/l]
Re^2: Dereferencing Hashes of Arrays by Anonymous Monk on Mar 13, 2005 at 06:42 UTC
I read the values from a excel spreadsheet and put the values in a hash of array with employee as key and value is reference to an array of infor	[reply]
Re: Dereferencing Hashes of Arrays by Roy Johnson (Monsignor) on Mar 13, 2005 at 12:51 UTC
The answer to your question is that you just add the array subscripting to what you're already dereferencing: `$sheet{$_}->{data}[$column];` [download] But I think your problem is better suited to arrays of arrays: use strict; use warnings; my @columns = split ' ', <DATA>; # First row is column headers my @data = map { [ split ' ', $_ ] } <DATA>; # Other rows are data print "@columns Total\n"; # First column is not numeric, an extra numeric column is # on the right for the total of the row totals my @column_totals = ('Total', (0) x @columns-1, 0); for my $row (@data) { my $row_total = 0; for my $col (1..$#columns) { $row_total += $row->[$col]; $column_totals[$col] += $row->[$col]; } print "@$row $row_total\n"; # Last column of column totals is sum of row totals $column_totals[$#column_totals] += $row_total; } print "@column_totals\n"; __DATA__ employee Buffalo Cleveland Terry 5.00 2.00 Doug 4.00 3.00 Fred 1.00 5.00 [download] Caution: Contents may have been coded under pressure.	[reply] [d/l] [select]