gowthamvels has asked for the wisdom of the Perl Monks concerning the following question:
I have to do a group by and sum two columns, I am able to do only for two columns. For the below code, I got the help from Monks only. I need another help to group by and sum two columns. I have grouped by using col2, and sum col6 and col7
- Input file
- col1,col2,col3,col4,col4,col5,col6,col7
- 1234,GP,20170715,0,V,97517,24,0.6
- 5678,Pack,20170715,0,V,97516,88,1.8
- 1234,GP,20170715,0,V,97517,22,0.6
- 5678,Pack,20170715,0,V,97517,183,3.9
- 1234,PRS,20170715,0,S,97517,261,5.4
- 5678,PRS,20170715,0,M,97517,36,0.9
- output should be:
- GP,46,1.2
- Pack,271,5.7
- PRS,297,6.3
#!/usr/bin/perl #use strict; #use warnings; use feature 'say'; #my %hash; print "Please input the Service Name(V/M/S/ALL):"; my $sinput = <STDIN>; chomp $sinput; print "Please input On/Off/ALL:"; my $chcat= <STDIN>; chomp $chcat; print "Start Date:"; chomp($sdate=<>); print "End Date:"; chomp($edate=<>); $file = '/var/tmp/CA_scripts/1_onnet_offnet/Onnet_offnet_repository.cs +v'; open (my $fh, '>>', 'C_n_r_'.$sinput.'.csv'); unless(open FILE, $file) { die "\nUnable to open the file\n"; } while (my $line = <FILE>) { my ($col1,$col2,$col3,$col4,$col5,$col6,$col7,$col8) = split /,/, +$line; #my $col21 = $class; if ($sdate >= $col3 && $edate <= $col3 && $sinput eq $col5 && $chcat e +q "On" && ($col6 =~ /^\Q97517\E/ || $col6 =~ /^\Q97516\E/)) { $hash{"$col2"} += $col8; #$hash{"$col2"} += $col6; } elsif ($sdate >= $col3 && $edate <= $col3 && $sinput eq $col5 && $chca +t eq "Off" && $col6 =~ /^\Q97577\E/) { $hash{"$col2"} += $col8; } elsif ($sdate >= $col3 && $edate <= $col3 && $sinput eq "ALL" && $chc +at eq "ALL") { $hash{"$col2"} += $col8; } } foreach my $key (sort { $hash{$b} <=> $hash{$a} } keys %hash) { printf $fh "%s %1f\n", $key, $hash{$key};
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: group by and sum for two columns
by hippo (Bishop) on Aug 08, 2017 at 15:24 UTC | |
Re: group by and sum for two columns
by 1nickt (Canon) on Aug 08, 2017 at 15:36 UTC | |
Re: group by and sum for two columns
by kevbot (Vicar) on Aug 09, 2017 at 04:44 UTC | |
by gowthamvels (Novice) on Aug 10, 2017 at 14:45 UTC | |
Re: group by and sum for two columns
by kcott (Archbishop) on Aug 09, 2017 at 09:40 UTC | |
Re: group by and sum for two columns
by AnomalousMonk (Archbishop) on Aug 08, 2017 at 15:32 UTC | |
by gowthamvels (Novice) on Aug 08, 2017 at 16:21 UTC | |
by AnomalousMonk (Archbishop) on Aug 08, 2017 at 23:03 UTC |
Back to
Seekers of Perl Wisdom