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

I'm new to perl programming. I've a csv file as below. 
20100221, abc_1, 200
20100221, abc_4, 350
20100221, opq_3, 200
20100221, abc_5, 220
20100221, xyz_1, 500
20100221, abc_2, 500
20100221, abc_3, 100
20100221, xyz_2, 700
20100221, opq_2, 350
20100221, xyz_3, 100
20100221, opq_1, 230

[download]
I'm trying to get output as below, by adding matching values: Output: 
20100221, abc, 1370
20100221, xyz, 1300
20100221, opq, 780

[download]
I've started coding as below, but not able to continue due to lack of experience. 
use strict;
use warnings;

my $inputfilename  = $ARGV[0]; ### CSV file name. ###

open( my $in_fh, "<", $inputfilename ) or die "Can't open $inputfilena
+me : $!";

while (my $line = <$in_fh>) {
  chomp($line);
  my @columns = split(',', $line, 2);

[download]
Could any of you help me to accomplish my requirement? Thanks and Regards / Lokesha

Replies are listed 'Best First'.
Re: Summing column values
by Ratazong (Monsignor) on Feb 24, 2010 at 08:50 UTC

    OK - here is how you might want to proceed:

    • Obviously, you are only interested in the first three letters of the 2nd column ($columns[1]). You can extract them with substr or with a regex.
    • If it comes to grouping, a hash is the thing you want. I propose to use column 1 and the 3 letters of column 2 as a key.
    • As a value, you should add the values of column 3 each time you find it
    • At the end, just loop over the hash and print all pairs of keys/values

    A code-fragment (following your start) could look like 

     my $key = $columns[0].", ".$columns[1];   # supposed columns[1] has a
+lready been reduced to its first 3 letters
 $myHash { $key } += $columns[2];

    [download]

    HTH, Rata
[reply]
[d/l]
[select]
Re: Summing column values
by baxy77bax (Deacon) on Feb 24, 2010 at 09:41 UTC
    hi

    well as Ratazong said this probably the easiest way to go and here is an example: 

    
#!/usr/bin/perl

use strict;
#no warnings;

open (IN, "<", $ARGV[0]) || die "$!";
my %hash;

while (<IN>){
  chomp;
  my @array =split(',',$_);
  my $id = substr $array[1], 1,3;
  (defined $hash{$array[0]}{$id}) ? ($hash{$array[0]}{$id} += $array[2
+]) : ($hash{$array[0]}{$id} = $array[2])  ;
} 
close IN;

foreach my $prim (keys %hash){
  foreach my $sec (keys %{$hash{$prim}}){
    print "$prim, $sec, $hash{$prim}{$sec}\n";
  }
}

    [download]
    hope this helps

    baxy

[reply]
[d/l]

      (defined $hash{$array[0]}{$id}) 
    ? ($hash{$array[0]}{$id} += $array[2]) 
    : ($hash{$array[0]}{$id}  = $array[2]);

      [download]

      That's more complicated than is necessary. Perl's clever enough to know that if you add a number to an undefined variable, to treat that var as 0. This would suffice: 

         $hash{$array[0]}{$id} += $array[2];

      [download]
      Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
      "Science is about questioning the status quo. Questioning authority".
      In the absence of evidence, opinion is indistinguishable from prejudice.
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom