CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Sort and adding the amounts monthly

 



Tejas
User

Oct 9, 2013, 11:56 PM

Post #1 of 13 (1292 views)
Sort and adding the amounts monthly Can't Post

42.64 29-Jul-13 29-Jul-13
-42.64 29-Jul-13 29-Jul-13
-42.64 11-Sep-13 11-Sep-13
-13.06 27-Jun-13 27-Jun-13
-2.25 29-Jun-13 29-Jun-13
-4 26-Jul-13 26-Jul-13

I need to add the amounts monthly using perl
ex :
Jul 0
sep -42.64
jun -15.31
jul -4
I am trying out th emethods to do it.
Any fast appraoch to complete it, I will be glad.

Thank You


Zhris
Enthusiast

Oct 10, 2013, 3:36 AM

Post #2 of 13 (1290 views)
Re: [Tejas] Sort and adding the amounts monthly [In reply to] Can't Post

Hi,

The following script:


Code
#!/usr/bin/perl 
use strict;
use warnings FATAL => qw/all/;
use Data::Dumper;

my %hash;

open my $fh, '<', 'input.txt' or die "cannot open: $!";
while ( my $line = <$fh> )
{
chomp $line;
my ($amount, $date1, $date2) = split /\s+/, $line;
my ($day, $month, $year) = split /-/, $date1;
$hash{$month} += $amount;
}
close $fh;

print Dumper \%hash;


Produces:


Code
$VAR1 = { 
'Sep' => '-42.64',
'Jun' => '-15.31',
'Jul' => -4
};


This isn't exactly what you asked for, but I believe is what you meant? If not then could you explain how you came up with two Julys ( Jul = 0 and jul = -4 ). Is it anything to do with consecutive groups.

Chris


(This post was edited by Zhris on Oct 10, 2013, 3:38 AM)


Tejas
User

Oct 10, 2013, 5:09 AM

Post #3 of 13 (1280 views)
Re: [Zhris] Sort and adding the amounts monthly [In reply to] Can't Post

This is excatly what i need , but had to change it accordingly.
Thanks for the script,
$VAR1 = {
'13' => {
'AUG' => '-24053.97',
'JUN' => '-1631.53',
'MAR' => '-287.3',
'JUL' => '-1437.76',
'MAY' => '-901.210000000003',
'JAN' => '-60.3',
'SEP' => '-68722.42',
'FEB' => '-42.22'
},
'12' => {
'DEC' => '-19.9'
}
};

This is the out , whn i used hash of hashes
$hash{$year}{$month} += @_[1];

1. Can i change VAR1 to my customized name(Like Sale, Refund etc etc) , i have around 10 files
2 And th eoutput should be in format
3. its more complex to understand the output by a customer

i will try ways in the mean time , if u get to see this reply, please do help me :)
2013
MON AMOUNT

2012
MONTH AMOUNT

(This post was edited by Tejas on Oct 10, 2013, 5:18 AM)


Tejas
User

Oct 10, 2013, 5:14 AM

Post #4 of 13 (1279 views)
Re: [Tejas] Sort and adding the amounts monthly [In reply to] Can't Post

13:
AUG: -24053.97
JUN: -1631.53
MAR: -287.3
JUL: -1437.76
MAY: -901.210000000003
JAN: -60.3
SEP: -68722.42
FEB: -42.22
12:
DEC: -19.9

I could get this output by

foreach my $line (keys %hash) {
print "$line: \n";
foreach my $elem (keys %{$hash{$line}}) {
print " $elem: " . $hash{$line}->{$elem} . "\n";
}


Finally the monster :(

I have ten file,should i create ten hashes traverse through it and print the ouput to a file
Or Is there any other way for thsi

Thanks
Tejas


(This post was edited by Tejas on Oct 10, 2013, 5:35 AM)


Zhris
Enthusiast

Oct 10, 2013, 5:51 AM

Post #5 of 13 (1273 views)
Re: [Tejas] Sort and adding the amounts monthly [In reply to] Can't Post

Hi,

Glad to see you have been able to understand and modify the script I provided.


Quote
I have ten file,should i create ten hashes traverse through it and print the ouput to a file
Or Is there any other way for thsi


I think it would be best to loop through each of the 10 files and extend the same hash. The example below loops through the current working directory and processes any .txt files (untested). It generates the output you desire (you have already figured this out by yourself), which you can print to an output file if need be. You'll need to provide more details if this isn't sufficient.


Code
#!/usr/bin/perl  
use strict;
use warnings FATAL => qw/all/;

my %hash;

my @filepaths = <"*.txt">;

foreach my $filepath (@filepaths)
{
open my $fh, '<', 'input.txt' or die "cannot open: $!";
while ( my $line = <$fh> )
{
chomp $line;
my ($amount, $date1, $date2) = split /\s+/, $line;
my ($day, $month, $year) = split /-/, $date1;
$hash{$year}{$month} += $amount;
}
close $fh;
}

foreach my $year (keys %hash)
{
print "$year\n";
while ( my ($month, $amount) = each %{$hash{$year}} )
{
print "$month: $amount\n";
}
print "\n";
}


Chris


(This post was edited by Zhris on Oct 10, 2013, 5:56 AM)


Tejas
User

Oct 10, 2013, 6:50 AM

Post #6 of 13 (1265 views)
Re: [Zhris] Sort and adding the amounts monthly [In reply to] Can't Post

Here i have 10 different input files ,
Out has to be given seperately for each file.
As per this code, i think we are consolidated all the files.

Here is the code and input
Input : 705844721518,-27.5,05-DEC-12,05-DEC-12,362,381,369.666666666666666666666666666666666667,3,12,2012

my @filepaths = ($Payment_For_Sale_Missing,$Duplicate_Sale,$Sale_Vs_Payment_Mismatch,$Refund_Missing,$Payment_For_Refund_Missing,$Payment_For_Refund_DoubleBooking);
my %hash;
foreach my $filepath (@filepaths)
{
#print "$filepath \n" ;
open my $fh, '<', $filepath or die "cannot open: $!";
while ( my $line = <$fh> )
{
chomp $line;
my @row = split (",", $line);
#my ($amount, $date1, $date2) = split /\s+/, $row[2];
my ($amount, $date1, $date2) = ($row[1],$row[2],$row[3]);
#print "Values : $amount, $date1, $date2 \n";
my ($day, $month, $year) = split /-/, $date1;
$hash{$year}{$month} += $amount;
}
close $fh;
}

foreach my $year (keys %hash)
{
print "$year\n";
while ( my ($month, $amount) = each %{$hash{$year}} )
{
print "$month: $amount\n";
}
print "\n";
}

Desired output
Payment_For_Sale_Missing
APR: 1.1000000000281
MAY: -305.710000000036
JAN: 287.839999999993
SEP: 159960.149999999
FEB: -328.880000000001

Duplicate_Sale
APR: 1.1000000000281
MAY: -305.710000000036
JAN: 287.839999999993
SEP: 159960.149999999
FEB: -328.880000000001

and finally the last file

Thank You
Tejas


Tejas
User

Oct 10, 2013, 7:01 AM

Post #7 of 13 (1263 views)
Re: [Zhris] Sort and adding the amounts monthly [In reply to] Can't Post

So i had slightly changed ur code (not really) like below
foreach my $filepath (@filepaths)
{
#print "$filepath \n" ;
open my $fh, '<', $filepath or die "cannot open: $!";
while ( my $line = <$fh> )
{
chomp $line;
my @row = split (",", $line);
#my ($amount, $date1, $date2) = split /\s+/, $row[2];
my ($amount, $date1, $date2) = ($row[1],$row[2],$row[3]);
#print "Values : $amount, $date1, $date2 \n";
my ($day, $month, $year) = split /-/, $date1;
$hash{$year}{$month} += $amount;
}
close $fh;

foreach my $year (keys %hash)
{
print "$filepath \n";
print "$year\n";
while ( my ($month, $amount) = each %{$hash{$year}} )
{
print "$month: $amount\n";
}
print "\n";
}

}

And the output is
/home/tejaswi/BR_MX/Test2/Payment_For_Sale_Missing.csv

: 16.14

/home/tejaswi/BR_MX/Test2/Payment_For_Sale_Missing.csv
13
AUG: 17494.52
MAY: 31.99
JUN: 4.04
MAR: 35.95
APR: 30.31
SEP: 162803.249999999

/home/tejaswi/BR_MX/Test2/Payment_For_Sale_Missing.csv
12
DEC: 979.33

/home/tejaswi/BR_MX/Test2/Duplicate_Sale.csv

: 16.14

/home/tejaswi/BR_MX/Test2/Duplicate_Sale.csv
13
AUG: 17494.52
MAY: 31.99
JUN: 4.04
MAR: 35.95
APR: 30.31
SEP: 162803.249999999

/home/tejaswi/BR_MX/Test2/Duplicate_Sale.csv
12
DEC: 979.33

/home/tejaswi/BR_MX/Test2/Sale_Vs_Payment_Mismatch.csv

: 16.14

/home/tejaswi/BR_MX/Test2/Sale_Vs_Payment_Mismatch.csv
13
AUG: 17494.52
MAY: 31.99
JUN: 4.04
MAR: 35.95
APR: 30.31
SEP: 162803.249999999

/home/tejaswi/BR_MX/Test2/Sale_Vs_Payment_Mismatch.csv
12
DEC: 979.33


There are 3 ouputs for each file.
But i dint really get what is the first output(i mean no year , month just :number)

Am i missing some where

Thnaks in advance\


Zhris
Enthusiast

Oct 10, 2013, 7:24 AM

Post #8 of 13 (1256 views)
Re: [Tejas] Sort and adding the amounts monthly [In reply to] Can't Post

Hi,

Could you attach /home/tejaswi/BR_MX/Test2/Payment_For_Sale_Missing.csv. It appears that your recent code treats each file as a comma seperated csv that ignores the first column.

Also, you could easily add a third dimension to the hash for filepath, instead of adjusting the outer loop scope:


Code
$hash{$filepath}{$year}{$month} += $amount;


Chris


(This post was edited by Zhris on Oct 10, 2013, 7:24 AM)


Tejas
User

Oct 10, 2013, 7:54 AM

Post #9 of 13 (1249 views)
Re: [Zhris] Sort and adding the amounts monthly [In reply to] Can't Post

here is the file and the problem is with 5th line

line is 2
Values : ,,
line is 2
Values : ,,
line is 187TGDZ-PGCXWUUA,-121.29,
Values : -121.29,,
line is 187TGDZ-PGCXWUUA,-121.29,
Values : -121.29,,

It is unacble to get month and year , though it is available in main file
prob with this line 187TGDZ-PGCXWUUA,-121.29,24-SEP-13,24-SEP-13,366,366,366,1,9,2013

170KHIB-PACVF03K,-9,01-SEP-13,01-SEP-13,366,366,366,1,9,2013
17NQ5D9-PDGAP00X,-50.34,09-SEP-13,09-SEP-13,366,366,366,1,9,2013
17VY36R-PDIHPXSW,-42.86,13-SEP-13,13-SEP-13,366,366,366,1,9,2013
17RG80P-PECENFA7,-210.33,14-SEP-13,14-SEP-13,366,366,366,1,9,2013
183P1NN-PFXNUU8W,-23.85,21-SEP-13,21-SEP-13,366,366,366,1,9,2013
180ZQHH-PGCUFVYD,-85,23-SEP-13,23-SEP-13,366,366,366,1,9,2013
problem : 187TGDZ-PGCXWUUA,-121.29,24-SEP-13,24-SEP-13,366,366,366,1,9,2013
186JAN7-PGIPYB7K,-12.49,24-SEP-13,24-SEP-13,366,366,366,1,9,2013
Return:v1-AJ+EN7c=-AdCIIK4C,-54.4,25-SEP-13,25-SEP-13,366,366,366,1,9,2013
Return:v1-AJ42CYU=-AdBCI2Cd,-6.61,25-SEP-13,25-SEP-13,366,366,366,1,9,2013
Return:v1-AJ+KYTc=-AdCGjjTi,-60.89,25-SEP-13,25-SEP-13,366,366,366,1,9,2013

And the code is
foreach my $filepath (@filepaths)
{
#print "$filepath \n" ;
open my $fh, '<', $filepath or die "cannot open: $!";
while ( my $line = <$fh> )
{
chomp $line;
my @row = split (",", $line);
#my ($amount, $date1, $date2) = split /\s+/, $row[2];
my ($amount, $month, $year) = ($row[1],$row[8],$row[9]);
print "line is $line\n";
print "Values : $row[1],$row[8],$row[9] \n";
#print "Values : $amount, $date1, $date2 \n";
if($year eq '' || $month eq ''){
print "line is $line\n";
print "Values : $row[1],$row[8],$row[9] \n";
}
#my ($day, $month, $year) = split /-/, $date1;
$hash{$year}{$month} += $amount;
}
close $fh;
foreach my $year (keys %hash)
{
print "$filepath \n";
print "$year\n";
while ( my ($month, $amount) = each %{$hash{$year}} )
{
print "$month: $amount\n";
}
print "\n";
}

}

Output Using awk(Dint get Month, Year in output )

awk -F "," '{k[$9]+=$2} END { for (i in k) printf("%s => %10.2f\n", i, k) }' Payment_For_Sale_Missing.csv |sort -n
3 => 35.95
4 => 30.31
5 => 31.99
6 => 4.04
8 => 17494.52
9 => 163204.79
12 => 979.33


Zhris
Enthusiast

Oct 10, 2013, 8:40 AM

Post #10 of 13 (1247 views)
Re: [Tejas] Sort and adding the amounts monthly [In reply to] Can't Post

Hi,

I'm sorry, I don't understand your problem. Your latest code produces the output I would expect.

Could you attach 1 representative csv file to your post, this is so I can test your code as you do. Could you also wrap your code in code blocks (http://perlguru.com/gforum.cgi?do=markup_help;p), this makes it easier to read.

Chris


(This post was edited by Zhris on Oct 10, 2013, 8:44 AM)


Tejas
User

Oct 10, 2013, 8:54 AM

Post #11 of 13 (1239 views)
Re: [Zhris] Sort and adding the amounts monthly [In reply to] Can't Post

I have attached the text
And the code

foreach my $filepath (@filepaths)
{
#print "$filepath \n" ;
open my $fh, '<', $filepath or die "cannot open: $!";
while ( my $line = <$fh> )
{
chomp $line;
my @row = split (",", $line);
#my ($amount, $date1, $date2) = split /\s+/, $row[2];
my ($amount, $month, $year) = ($row[1],$row[8],$row[9]);
print "line is $line\n";
print "Values : $row[1],$row[8],$row[9] \n";
if($year eq '' || $month eq ''){
print "line is $line\n";
print "Values : $row[1],$row[8],$row[9] \n";
}
$hash{$year}{$month} += $amount;
}
close $fh;
foreach my $year (keys %hash)
{
print "$filepath \n";
print "$year\n";
while ( my ($month, $amount) = each %{$hash{$year}} )
{
print "$month: $amount\n";
}
print "\n";
}

}
Attachments: test.txt (3.06 KB)


Zhris
Enthusiast

Oct 10, 2013, 9:41 AM

Post #12 of 13 (1237 views)
Re: [Tejas] Sort and adding the amounts monthly [In reply to] Can't Post

Hi,

Your code / input generates the following at the end:


Code
test.txt  
2013
8: -1729.55
9: -1504.31


What is your output preference?

Chris


Tejas
User

Oct 10, 2013, 11:22 PM

Post #13 of 13 (1222 views)
Re: [Zhris] Sort and adding the amounts monthly [In reply to] Can't Post

Here is the code
my $filepaths = ($Payment_For_Sale_Missing);
open my $fh, '<', $filepaths or die "cannot open: $!";
while ( my $line = <$fh> )
{
chomp $line;
my @row = split (",", $line);
my ($amount, $month, $year) = ($row[1],$row[8],$row[9]);
if($year eq '' || $month eq ''){
print "line is $line\n";
print "Values : $row[1],$row[8],$row[9] \n";
}
$hash1{$year}{$month} += $amount;
}
close $fh;
foreach my $year (keys %hash1)
{
while ( my ($month, $amount) = each %{$hash1{$year}} )
{
print "$month: $amount\n";
}
print "\n";
}


And the file is attached , Though th einput file is perfect , its not able to read it and the Year and month column are getting missed while reading the file

And th eoutput is
line is 804775736516,16.14
Values : 16.14,,
: 16.14

6: 4.04
8: 17494.52
4: 30.31
3: 35.95
9: 162803.249999999
5: 31.99

12: 979.33
This line's values are 16.14,,
Month and year columns are missing while reading and this amoun is not associated with any month r year as its not read properly

Thanks
Attachments: File.zip (95.9 KB)

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives