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

Given the following dataset in an EXCEL file: Here are three columns: 

A1 10 20
A1 11 22
B1 08 09
A1 03 10
B1 15 32
B1 22 33
C1 24 34
C1 0  5
....

[download]

I am trying to group by first column. Thus, I am trying to get the following: 

A1 24 52
B1 45 74
C1 24 39

[download]

Finally, I want to achieve this result because I want to put this in a hash with the first column being the key. Something like this: 

my %hash
$hash{$col1} = [$col2,$col3];

[download]

More importantly, how do you group things in perl by any one column? Thanks!

Replies are listed 'Best First'.
Re: Grouping By A Column
by roboticus (Chancellor) on Jan 27, 2011 at 02:12 UTC

    rocky13

    You're on the right track. You do grouping with a hash. Each time you get a row, create the entry if it doesn't exist, or add in the data:

    my ($col1, $col2, $col3) = generate_data();
if (! defined $hash{$col1}) {
    $hash{$col1} = [ 0, 0 ]
}
$hash{$col1}[0] += $col2;
$hash{$col1}[1] += $col3;

    [download]

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
      Due to autovivification, you can half the number of lines in the snippet: 
      my ($col1, $col2, $col3) = generate_data();
$hash{$col1}[0] += $col2;
$hash{$col1}[1] += $col3;

      [download]
      will do.
[reply]
[d/l]
      That clears up a lot of things. One more question. Can you read an excel file the same way you read a .txt file?
[reply]

        In a word: 'No'. But there are many Excel related modules in CPAN. A good starting point is probably Spreadsheet::ParseExcel.

        True laziness is hard work
[reply]

Back to Seekers of Perl Wisdom