summarise input data using Perl
summarise input data using Perl
am 13.07.2011 18:50:44 von Mohan L
--0016e64ddbe2af6f4504a7f63755
Content-Type: text/plain; charset=ISO-8859-1
Dear All,
I have the tab separated csv file with below data.
http://pastebin.com/iDvuhjCc
"Asset" "West" "pws"
"Asset" "West" "pws"
"Asset" "West" "pws"
"Asset" "West" "pws"
"Asset" "West" "pws"
"OnCall" "West" "pws"
"OnCall" "West" "pws"
"OnCall" "South" "pws"
"OnCall" "South" "pws"
"OnCall" "South" "pws"
"OnCall" "South" "Open"
"Onsite" "South" "Open"
"Onsite" "South" "Open"
"Onsite" "South" "Hold"
"Onsite" "East" "Hold"
"Onsite" "East" "Hold"
"Remote" "East" "Open"
"Remote" "East" "Open"
"Remote" "East" "Open"
"Remote" "East" "Open"
"Remote" "North" "Open"
"Ven" "North" "Open"
"Ven" "North" "Open"
"Ven" "North" "Hold"
"Ven" "North" "Hold"
"Ven" "North" "Hold"
"Ven" "North" "Hold"
"Remote" "North" "Hold"
"Onsite" "North" "Hold"
"Asset" "North" "Hold"
I have to summarise above date like this:
+--------+-----------+-----+------+------+-----+---------+-- -----+
| reg_id | region_id | pws | open | hold | pwu | re_open | total |
+--------+-----------+-----+------+------+-----+---------+-- -----+
| 0 | 1 | 0 | 4 | 2 | 0 | 0 | 6 |
| 1 | 1 | 0 | 3 | 7 | 0 | 0 | 10 |
| 2 | 1 | 0 | 3 | 1 | 0 | 0 | 4 |
| 3 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
+--------+-----------+-----+------+------+-----+---------+-- -----+
Where reg_id is :
0 -East
1 -North
2 -South
3 -West
I am a C programmer, but very beginner to perl. I wrote the below lengthy
script to summarise data in the above format and inserting into mysql
database.
The function "ByRegion" does the aggregation and summarise data. I think
there may other way I will achieve it using perl way.
If the below array size increase, my code also will increase:
my @Region=('East','North','South','West');
my @Status_String=('Pws','Open','Hold','Pwu','Reopen');
I think, I am doing very bad code in function "ByRegion" for aggrication. I
need someone guide to achive this perl way. Any help will be really
apricated.
#!/usr/bin/perl
use Text::CSV;
use DBI;
# CONFIG VARIABLES
my $platform = "mysql";
my $database = "new";
my $host = "localhost";
my $port = "3306";
my $tablename = "by_region";
my $username = "root";
my $password = "root123";
# DATA SOURCE NAME
my $dsn = "dbi:$platform:$database:$host:$port";
my @Region=('East','North','South','West');
my @Status_String=('Pws','Open','Hold','Pwu','Reopen');
my @EastCount =(0,0,0,0,0);
my @NorthCount =(0,0,0,0,0);
my @SouthCount =(0,0,0,0,0);
my @WestCount =(0,0,0,0,0);
my @Region_Total=(0,0,0,0);
sub ByRegion
{
my @columns=@_;
if($columns[1] =~ /^$Region[0]$/)
{
$EastCount[0]++ if $columns[2] =~ /^$Status_String[0]$/;
$EastCount[1]++ if $columns[2] =~ /^$Status_String[1]$/;
$EastCount[2]++ if $columns[2] =~ /^$Status_String[2]$/;
$EastCount[3]++ if $columns[2] =~ /^$Status_String[3]$/;
$EastCount[4]++ if $columns[2] =~ /^$Status_String[4]$/;
}
elsif($columns[1] =~ /^$Region[1]$/)
{
$NorthCount[0]++ if $columns[2] =~ /^$Status_String[0]$/;
$NorthCount[1]++ if $columns[2] =~ /^$Status_String[1]$/;
$NorthCount[2]++ if $columns[2] =~ /^$Status_String[2]$/;
$NorthCount[3]++ if $columns[2] =~ /^$Status_String[3]$/;
$NorthCount[4]++ if $columns[2] =~ /^$Status_String[4]$/;
}
elsif($columns[1] =~ /^$Region[2]$/)
{
$SouthCount[0]++ if $columns[2] =~ /^$Status_String[0]$/;
$SouthCount[1]++ if $columns[2] =~ /^$Status_String[1]$/;
$SouthCount[2]++ if $columns[2] =~ /^$Status_String[2]$/;
$SouthCount[3]++ if $columns[2] =~ /^$Status_String[3]$/;
$SouthCount[4]++ if $columns[2] =~ /^$Status_String[4]$/;
}
elsif($columns[1] =~ /^$Region[3]$/)
{
$WestCount[0]++ if $columns[2] =~ /^$Status_String[0]$/;
$WestCount[1]++ if $columns[2] =~ /^$Status_String[1]$/;
$WestCount[2]++ if $columns[2] =~ /^$Status_String[2]$/;
$WestCount[3]++ if $columns[2] =~ /^$Status_String[3]$/;
$WestCount[4]++ if $columns[2] =~ /^$Status_String[4]$/;
}
}
#############
# reg_id maps:
# 0 -East
# 1 -North
# 2 -South
# 3 -West
############
sub InsertByRegion
{
$Region_Total[0]+=$_ foreach @EastCount;
$Region_Total[1]+=$_ foreach @NorthCount;
$Region_Total[2]+=$_ foreach @SouthCount;
$Region_Total[3]+=$_ foreach @WestCount;
my $region_id=1;
my @data = (
['0',$region_id,$EastCount[0],$EastCount[1],$EastCount[2],$E astCount[3],$EastCount[4],$Region_Total[0]],
['1',$region_id,$NorthCount[0],$NorthCount[1],$NorthCount[2] ,$NorthCount[3],$NorthCount[4],$Region_Total[1]],
['2',$region_id,$SouthCount[0],$SouthCount[1],$SouthCount[2] ,$SouthCount[3],$SouthCount[4],$Region_Total[2]],
['3',$region_id,$WestCount[0],$WestCount[1],$WestCount[2],$W estCount[3],$WestCount[4],$Region_Total[3]],
);
## PERL DBI CONNECT
my $connect = DBI->connect($dsn, $username, $password);
### PREPARE THE QUERY
my $query = "INSERT INTO by_region
(reg_id,region_id,pws,open,hold,pwu,re_open,total) VALUES
(?,?,?,?,?,?,?,?)";
my $query_handle = $connect->prepare($query);
### EXECUTE THE QUERY
for my $datum (@data) {
$query_handle->execute(@$datum);
}
}
# Over All Pending data File
my $oapdata='sample.csv';
my $csv=Text::CSV->new({ sep_char => "\t" });
open(CSV,"<",$oapdata) or die $!;
while () {
if ($csv->parse($_))
{
my @columns = $csv->fields();
ByRegion(@columns);
}
else
{
my $err = $csv->error_input;
print "Failed to parse line: $err";
}
}
InsertByRegion;
print "The END!!!\n";
close CSV;
Thanks for your time.
- Mohan L
--0016e64ddbe2af6f4504a7f63755--
summarise input data using Perl
am 13.07.2011 19:33:14 von Mohan L
--0016363b8df0a3824104a7f6cf4e
Content-Type: text/plain; charset=ISO-8859-1
Dear All,
I have the tab separated csv file with below data.
http://pastebin.com/iDvuhjCc
"Asset" "West" "pws"
"Asset" "West" "pws"
"Asset" "West" "pws"
"Asset" "West" "pws"
"Asset" "West" "pws"
"OnCall" "West" "pws"
"OnCall" "West" "pws"
"OnCall" "South" "pws"
"OnCall" "South" "pws"
"OnCall" "South" "pws"
"OnCall" "South" "Open"
"Onsite" "South" "Open"
"Onsite" "South" "Open"
"Onsite" "South" "Hold"
"Onsite" "East" "Hold"
"Onsite" "East" "Hold"
"Remote" "East" "Open"
"Remote" "East" "Open"
"Remote" "East" "Open"
"Remote" "East" "Open"
"Remote" "North" "Open"
"Ven" "North" "Open"
"Ven" "North" "Open"
"Ven" "North" "Hold"
"Ven" "North" "Hold"
"Ven" "North" "Hold"
"Ven" "North" "Hold"
"Remote" "North" "Hold"
"Onsite" "North" "Hold"
"Asset" "North" "Hold"
I have to summarise above date like this:
+--------+-----------+-----+------+------+-----+---------+-- -----+
| reg_id | region_id | pws | open | hold | pwu | re_open | total |
+--------+-----------+-----+------+------+-----+---------+-- -----+
| 0 | 1 | 0 | 4 | 2 | 0 | 0 | 6 |
| 1 | 1 | 0 | 3 | 7 | 0 | 0 | 10 |
| 2 | 1 | 0 | 3 | 1 | 0 | 0 | 4 |
| 3 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
+--------+-----------+-----+------+------+-----+---------+-- -----+
Where reg_id is :
0 -East
1 -North
2 -South
3 -West
I am a C programmer, but very beginner to perl. I wrote the below lengthy
script to summarise data in the above format and inserting into mysql
database.
The function "ByRegion" does the aggregation and summarise data. I think
there may other way I will achieve it using perl way.
If the below array size increase, my code also will increase:
my @Region=('East','North','South','West');
my @Status_String=('Pws','Open','Hold','Pwu','Reopen');
I think, I am doing very bad code in function "ByRegion" for aggrication. I
need someone guide to achive this perl way. Any help will be really
apricated.
#!/usr/bin/perl
use Text::CSV;
use DBI;
# CONFIG VARIABLES
my $platform = "mysql";
my $database = "new";
my $host = "localhost";
my $port = "3306";
my $tablename = "by_region";
my $username = "root";
my $password = "root123";
# DATA SOURCE NAME
my $dsn = "dbi:$platform:$database:$host:$port";
my @Region=('East','North','South','West');
my @Status_String=('Pws','Open','Hold','Pwu','Reopen');
my @EastCount =(0,0,0,0,0);
my @NorthCount =(0,0,0,0,0);
my @SouthCount =(0,0,0,0,0);
my @WestCount =(0,0,0,0,0);
my @Region_Total=(0,0,0,0);
sub ByRegion
{
my @columns=@_;
if($columns[1] =~ /^$Region[0]$/)
{
$EastCount[0]++ if $columns[2] =~ /^$Status_String[0]$/;
$EastCount[1]++ if $columns[2] =~ /^$Status_String[1]$/;
$EastCount[2]++ if $columns[2] =~ /^$Status_String[2]$/;
$EastCount[3]++ if $columns[2] =~ /^$Status_String[3]$/;
$EastCount[4]++ if $columns[2] =~ /^$Status_String[4]$/;
}
elsif($columns[1] =~ /^$Region[1]$/)
{
$NorthCount[0]++ if $columns[2] =~ /^$Status_String[0]$/;
$NorthCount[1]++ if $columns[2] =~ /^$Status_String[1]$/;
$NorthCount[2]++ if $columns[2] =~ /^$Status_String[2]$/;
$NorthCount[3]++ if $columns[2] =~ /^$Status_String[3]$/;
$NorthCount[4]++ if $columns[2] =~ /^$Status_String[4]$/;
}
elsif($columns[1] =~ /^$Region[2]$/)
{
$SouthCount[0]++ if $columns[2] =~ /^$Status_String[0]$/;
$SouthCount[1]++ if $columns[2] =~ /^$Status_String[1]$/;
$SouthCount[2]++ if $columns[2] =~ /^$Status_String[2]$/;
$SouthCount[3]++ if $columns[2] =~ /^$Status_String[3]$/;
$SouthCount[4]++ if $columns[2] =~ /^$Status_String[4]$/;
}
elsif($columns[1] =~ /^$Region[3]$/)
{
$WestCount[0]++ if $columns[2] =~ /^$Status_String[0]$/;
$WestCount[1]++ if $columns[2] =~ /^$Status_String[1]$/;
$WestCount[2]++ if $columns[2] =~ /^$Status_String[2]$/;
$WestCount[3]++ if $columns[2] =~ /^$Status_String[3]$/;
$WestCount[4]++ if $columns[2] =~ /^$Status_String[4]$/;
}
}
#############
# reg_id maps:
# 0 -East
# 1 -North
# 2 -South
# 3 -West
############
sub InsertByRegion
{
$Region_Total[0]+=$_ foreach @EastCount;
$Region_Total[1]+=$_ foreach @NorthCount;
$Region_Total[2]+=$_ foreach @SouthCount;
$Region_Total[3]+=$_ foreach @WestCount;
my $region_id=1;
my @data = (
['0',$region_id,$EastCount[0],$EastCount[1],$EastCount[2],$E astCount[3],$EastCount[4],$Region_Total[0]],
['1',$region_id,$NorthCount[0],$NorthCount[1],$NorthCount[2] ,$NorthCount[3],$NorthCount[4],$Region_Total[1]],
['2',$region_id,$SouthCount[0],$SouthCount[1],$SouthCount[2] ,$SouthCount[3],$SouthCount[4],$Region_Total[2]],
['3',$region_id,$WestCount[0],$WestCount[1],$WestCount[2],$W estCount[3],$WestCount[4],$Region_Total[3]],
);
## PERL DBI CONNECT
my $connect = DBI->connect($dsn, $username, $password);
### PREPARE THE QUERY
my $query = "INSERT INTO by_region
(reg_id,region_id,pws,open,hold,pwu,re_open,total) VALUES
(?,?,?,?,?,?,?,?)";
my $query_handle = $connect->prepare($query);
### EXECUTE THE QUERY
for my $datum (@data) {
$query_handle->execute(@$datum);
}
}
# Over All Pending data File
my $oapdata='sample.csv';
my $csv=Text::CSV->new({ sep_char => "\t" });
open(CSV,"<",$oapdata) or die $!;
while () {
if ($csv->parse($_))
{
my @columns = $csv->fields();
ByRegion(@columns);
}
else
{
my $err = $csv->error_input;
print "Failed to parse line: $err";
}
}
InsertByRegion;
print "The END!!!\n";
close CSV;
Thanks for your time.
- Mohan L
--0016363b8df0a3824104a7f6cf4e--
Re: summarise input data using Perl
am 13.07.2011 20:00:33 von Leo Susanto
Mohan,
I would suggest you learning perl hash: http://perl101.org/hashes.html
#!/usr/bin/perl
use Text::CSV;
use DBI;
use Data::Dumper;
# CONFIG VARIABLES
....
# DATA SOURCE NAME
....
my @regions =3D ('east','north','south','west');
my @statuses_string =3D ('pws','open','hold','pwu','reopen');
my %region_data;
foreach my $region (@regions) {
foreach my $status_string (@statuses_string) {
$region_data{$region}{status}{$status_string}{count} =3D 0;
}
$region_data{$region}{total_count} =3D 0;
}
print Dumper \%region_data; # inspect the pristine data structure
sub ByRegion
{
my @columns=3D@_;
$regions{ lc($columns[1]) }{status}{ lc($columns[2]) }c++;
$regions{ lc($columns[1]) }{total_count}++;
}
#############
# reg_id maps:
# 0 -East
# 1 -North
# 2 -South
# 3 -West
############
sub InsertByRegion
{
... I'll leave this to you ...
}
On Wed, Jul 13, 2011 at 10:33 AM, Mohan L wrote:
> Dear All,
>
> I have the tab separated csv file with below data.
>
> http://pastebin.com/iDvuhjCc
>
> "Asset" =A0 =A0 =A0 =A0"West" =A0 =A0 =A0 =A0"pws"
> "Asset" =A0 =A0 =A0 =A0"West" =A0 =A0 =A0 =A0"pws"
> "Asset" =A0 =A0 =A0 =A0"West" =A0 =A0 =A0 =A0"pws"
> "Asset" =A0 =A0 =A0 =A0"West" =A0 =A0 =A0 =A0"pws"
> "Asset" =A0 =A0 =A0 =A0"West" =A0 =A0 =A0 =A0"pws"
> "OnCall" =A0 =A0"West" =A0 =A0 =A0 =A0"pws"
> "OnCall" =A0 =A0"West" =A0 =A0 =A0 =A0"pws"
> "OnCall" =A0 =A0"South" =A0 =A0 =A0 =A0"pws"
> "OnCall" =A0 =A0"South" =A0 =A0 =A0 =A0"pws"
> "OnCall" =A0 =A0"South" =A0 =A0 =A0 =A0"pws"
> "OnCall" =A0 =A0"South" =A0 =A0 =A0 =A0"Open"
> "Onsite" =A0 =A0"South" =A0 =A0 =A0 =A0"Open"
> "Onsite" =A0 =A0"South" =A0 =A0 =A0 =A0"Open"
> "Onsite" =A0 =A0"South" =A0 =A0 =A0 =A0"Hold"
> "Onsite" =A0 =A0"East" =A0 =A0 =A0 =A0"Hold"
> "Onsite" =A0 =A0"East" =A0 =A0 =A0 =A0"Hold"
> "Remote" =A0 =A0"East" =A0 =A0 =A0 =A0"Open"
> "Remote" =A0 =A0"East" =A0 =A0 =A0 =A0"Open"
> "Remote" =A0 =A0"East" =A0 =A0 =A0 =A0"Open"
> "Remote" =A0 =A0"East" =A0 =A0 =A0 =A0"Open"
> "Remote" =A0 =A0"North" =A0 =A0 =A0 =A0"Open"
> "Ven" =A0 =A0 =A0 =A0"North" =A0 =A0 =A0 =A0"Open"
> "Ven" =A0 =A0 =A0 =A0"North" =A0 =A0 =A0 =A0"Open"
> "Ven" =A0 =A0 =A0 =A0"North" =A0 =A0 =A0 =A0"Hold"
> "Ven" =A0 =A0 =A0 =A0"North" =A0 =A0 =A0 =A0"Hold"
> "Ven" =A0 =A0 =A0 =A0"North" =A0 =A0 =A0 =A0"Hold"
> "Ven" =A0 =A0 =A0 =A0"North" =A0 =A0 =A0 =A0"Hold"
> "Remote" =A0 =A0"North" =A0 =A0 =A0 =A0"Hold"
> "Onsite" =A0 =A0"North" =A0 =A0 =A0 =A0"Hold"
> "Asset" =A0 =A0 =A0 =A0"North" =A0 =A0 =A0 =A0"Hold"
>
> I have to summarise above date like this:
>
> +--------+-----------+-----+------+------+-----+---------+-- -----+
> | reg_id | region_id | pws | open | hold | pwu | re_open | total |
> +--------+-----------+-----+------+------+-----+---------+-- -----+
> | =A0 =A0 =A00 | =A0 =A0 =A0 =A0 1 | =A0 0 | =A0 =A04 | =A0 =A02 | =A0 0 =
| =A0 =A0 =A0 0 | =A0 =A0 6 |
> | =A0 =A0 =A01 | =A0 =A0 =A0 =A0 1 | =A0 0 | =A0 =A03 | =A0 =A07 | =A0 0 =
| =A0 =A0 =A0 0 | =A0 =A010 |
> | =A0 =A0 =A02 | =A0 =A0 =A0 =A0 1 | =A0 0 | =A0 =A03 | =A0 =A01 | =A0 0 =
| =A0 =A0 =A0 0 | =A0 =A0 4 |
> | =A0 =A0 =A03 | =A0 =A0 =A0 =A0 1 | =A0 0 | =A0 =A00 | =A0 =A00 | =A0 0 =
| =A0 =A0 =A0 0 | =A0 =A0 0 |
> +--------+-----------+-----+------+------+-----+---------+-- -----+
>
> Where reg_id is :
> 0 -East
> 1 -North
> 2 -South
> 3 -West
>
> I am a C programmer, but very beginner to perl. I wrote the below lengthy
> script to summarise data in the above format and inserting into mysql
> database.
>
> The function "ByRegion" does the aggregation and summarise data. I think
> there may other way I will achieve it using perl way.
>
> If the below array size increase, my code also will increase:
>
> my @Region=3D('East','North','South','West');
> my @Status_String=3D('Pws','Open','Hold','Pwu','Reopen');
>
> I think, I am doing very bad code in function "ByRegion" for aggrication.=
I
> need someone guide to achive this perl way. =A0Any help will be really
> apricated.
>
> #!/usr/bin/perl
> use Text::CSV;
> use DBI;
>
> # CONFIG VARIABLES
> my $platform =3D "mysql";
> my $database =3D "new";
> my $host =3D "localhost";
> my $port =3D "3306";
> my $tablename =3D "by_region";
> my $username =3D "root";
> my $password =3D "root123";
>
> # DATA SOURCE NAME
> my $dsn =3D "dbi:$platform:$database:$host:$port";
>
> my @Region=3D('East','North','South','West');
> my @Status_String=3D('Pws','Open','Hold','Pwu','Reopen');
>
> my @EastCount =(0,0,0,0,0);
> my @NorthCount =3D(0,0,0,0,0);
> my @SouthCount =3D(0,0,0,0,0);
> my @WestCount =(0,0,0,0,0);
> my @Region_Total=3D(0,0,0,0);
>
> sub ByRegion
> {
> =A0 =A0my @columns=3D@_;
>
> =A0 =A0if($columns[1] =3D~ /^$Region[0]$/)
> =A0 =A0{
> =A0 =A0 =A0 =A0$EastCount[0]++ =A0 =A0if $columns[2] =3D~ /^$Status_Strin=
g[0]$/;
> =A0 =A0 =A0 =A0$EastCount[1]++ =A0 =A0if $columns[2] =3D~ /^$Status_Strin=
g[1]$/;
> =A0 =A0 =A0 =A0$EastCount[2]++ =A0 =A0if $columns[2] =3D~ /^$Status_Strin=
g[2]$/;
> =A0 =A0 =A0 =A0$EastCount[3]++ =A0 =A0if $columns[2] =3D~ /^$Status_Strin=
g[3]$/;
> =A0 =A0 =A0 =A0$EastCount[4]++ =A0 =A0if $columns[2] =3D~ /^$Status_Strin=
g[4]$/;
>
> =A0 =A0}
> =A0 =A0elsif($columns[1] =3D~ /^$Region[1]$/)
> =A0 =A0{
> =A0 =A0 =A0 =A0$NorthCount[0]++ =A0 =A0if $columns[2] =3D~ /^$Status_Stri=
ng[0]$/;
> =A0 =A0 =A0 =A0$NorthCount[1]++ =A0 =A0if $columns[2] =3D~ /^$Status_Stri=
ng[1]$/;
> =A0 =A0 =A0 =A0$NorthCount[2]++ =A0 =A0if $columns[2] =3D~ /^$Status_Stri=
ng[2]$/;
> =A0 =A0 =A0 =A0$NorthCount[3]++ =A0 =A0if $columns[2] =3D~ /^$Status_Stri=
ng[3]$/;
> =A0 =A0 =A0 =A0$NorthCount[4]++ =A0 =A0if $columns[2] =3D~ /^$Status_Stri=
ng[4]$/;
>
> =A0 =A0}
> =A0 =A0elsif($columns[1] =3D~ /^$Region[2]$/)
> =A0 =A0{
> =A0 =A0 =A0 =A0$SouthCount[0]++ =A0 =A0if $columns[2] =3D~ /^$Status_Stri=
ng[0]$/;
> =A0 =A0 =A0 =A0$SouthCount[1]++ =A0 =A0if $columns[2] =3D~ /^$Status_Stri=
ng[1]$/;
> =A0 =A0 =A0 =A0$SouthCount[2]++ =A0 =A0if $columns[2] =3D~ /^$Status_Stri=
ng[2]$/;
> =A0 =A0 =A0 =A0$SouthCount[3]++ =A0 =A0if $columns[2] =3D~ /^$Status_Stri=
ng[3]$/;
> =A0 =A0 =A0 =A0$SouthCount[4]++ =A0 =A0if $columns[2] =3D~ /^$Status_Stri=
ng[4]$/;
>
> =A0 =A0}
> =A0 =A0elsif($columns[1] =3D~ /^$Region[3]$/)
> =A0 =A0{
> =A0 =A0 =A0 =A0$WestCount[0]++ =A0 =A0if $columns[2] =3D~ /^$Status_Strin=
g[0]$/;
> =A0 =A0 =A0 =A0$WestCount[1]++ =A0 =A0if $columns[2] =3D~ /^$Status_Strin=
g[1]$/;
> =A0 =A0 =A0 =A0$WestCount[2]++ =A0 =A0if $columns[2] =3D~ /^$Status_Strin=
g[2]$/;
> =A0 =A0 =A0 =A0$WestCount[3]++ =A0 =A0if $columns[2] =3D~ /^$Status_Strin=
g[3]$/;
> =A0 =A0 =A0 =A0$WestCount[4]++ =A0 =A0if $columns[2] =3D~ /^$Status_Strin=
g[4]$/;
>
> =A0 =A0}
>
> }
>
> #############
> # reg_id maps:
> # 0 -East
> # 1 -North
> # 2 -South
> # 3 -West
> ############
>
> sub InsertByRegion
> {
> =A0 =A0$Region_Total[0]+=3D$_ =A0 =A0foreach @EastCount;
> =A0 =A0$Region_Total[1]+=3D$_ =A0 =A0foreach @NorthCount;
> =A0 =A0$Region_Total[2]+=3D$_ =A0 =A0foreach @SouthCount;
> =A0 =A0$Region_Total[3]+=3D$_ =A0 =A0foreach @WestCount;
> =A0 =A0 my $region_id=3D1;
>
> my @data =3D (
> ['0',$region_id,$EastCount[0],$EastCount[1],$EastCount[2],$E astCount[3],$=
EastCount[4],$Region_Total[0]],
> ['1',$region_id,$NorthCount[0],$NorthCount[1],$NorthCount[2] ,$NorthCount[=
3],$NorthCount[4],$Region_Total[1]],
> ['2',$region_id,$SouthCount[0],$SouthCount[1],$SouthCount[2] ,$SouthCount[=
3],$SouthCount[4],$Region_Total[2]],
> ['3',$region_id,$WestCount[0],$WestCount[1],$WestCount[2],$W estCount[3],$=
WestCount[4],$Region_Total[3]],
> );
>
> ## PERL DBI CONNECT
> my $connect =3D DBI->connect($dsn, $username, $password);
>
> ### PREPARE THE QUERY
> my $query =3D "INSERT INTO =A0by_region
> (reg_id,region_id,pws,open,hold,pwu,re_open,total) VALUES
> (?,?,?,?,?,?,?,?)";
>
> my $query_handle =3D $connect->prepare($query);
> ### EXECUTE THE QUERY
> for my $datum (@data) {
> =A0 =A0 =A0 =A0$query_handle->execute(@$datum);
> =A0 =A0}
>
> }
>
> # Over All Pending data File
> my $oapdata=3D'sample.csv';
> my $csv=3DText::CSV->new({ sep_char =3D> "\t" });
> open(CSV,"<",$oapdata) or die $!;
> while () {
> =A0 =A0if ($csv->parse($_))
> =A0 =A0{
> =A0 =A0 =A0 =A0my @columns =3D $csv->fields();
> =A0 =A0 =A0 =A0ByRegion(@columns);
> =A0 =A0}
> =A0 =A0else
> =A0 =A0{
> =A0 =A0 =A0 =A0my $err =3D $csv->error_input;
> =A0 =A0 =A0 =A0print "Failed to parse line: $err";
> =A0 =A0}
>
>
> }
>
> InsertByRegion;
>
> print "The END!!!\n";
>
> close CSV;
>
>
>
> Thanks for your time.
>
> - Mohan L
>
--
To unsubscribe, e-mail: beginners-unsubscribe@perl.org
For additional commands, e-mail: beginners-help@perl.org
http://learn.perl.org/
Re: summarise input data using Perl
am 13.07.2011 20:00:51 von Shlomi Fish
Hi Mohan,
Let me comment on your code below.
On Wed, 13 Jul 2011 22:20:44 +0530
Mohan L wrote:
> Dear All,
>=20
> I have the tab separated csv file with below data.
>=20
> http://pastebin.com/iDvuhjCc
>=20
> "Asset" "West" "pws"
> "Asset" "West" "pws"
> "Asset" "West" "pws"
> "Asset" "West" "pws"
> "Asset" "West" "pws"
> "OnCall" "West" "pws"
> "OnCall" "West" "pws"
> "OnCall" "South" "pws"
> "OnCall" "South" "pws"
> "OnCall" "South" "pws"
> "OnCall" "South" "Open"
> "Onsite" "South" "Open"
> "Onsite" "South" "Open"
> "Onsite" "South" "Hold"
> "Onsite" "East" "Hold"
> "Onsite" "East" "Hold"
> "Remote" "East" "Open"
> "Remote" "East" "Open"
> "Remote" "East" "Open"
> "Remote" "East" "Open"
> "Remote" "North" "Open"
> "Ven" "North" "Open"
> "Ven" "North" "Open"
> "Ven" "North" "Hold"
> "Ven" "North" "Hold"
> "Ven" "North" "Hold"
> "Ven" "North" "Hold"
> "Remote" "North" "Hold"
> "Onsite" "North" "Hold"
> "Asset" "North" "Hold"
>=20
> I have to summarise above date like this:
>=20
> +--------+-----------+-----+------+------+-----+---------+-- -----+
> | reg_id | region_id | pws | open | hold | pwu | re_open | total |
> +--------+-----------+-----+------+------+-----+---------+-- -----+
> | 0 | 1 | 0 | 4 | 2 | 0 | 0 | 6 |
> | 1 | 1 | 0 | 3 | 7 | 0 | 0 | 10 |
> | 2 | 1 | 0 | 3 | 1 | 0 | 0 | 4 |
> | 3 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
> +--------+-----------+-----+------+------+-----+---------+-- -----+
>=20
> Where reg_id is :
> 0 -East
> 1 -North
> 2 -South
> 3 -West
>=20
> I am a C programmer, but very beginner to perl. I wrote the below lengthy
> script to summarise data in the above format and inserting into mysql
> database.
>=20
> The function "ByRegion" does the aggregation and summarise data. I think
> there may other way I will achieve it using perl way.
>=20
> If the below array size increase, my code also will increase:
>=20
> my @Region=3D('East','North','South','West');
> my @Status_String=3D('Pws','Open','Hold','Pwu','Reopen');
>=20
> I think, I am doing very bad code in function "ByRegion" for aggrication.=
I
> need someone guide to achive this perl way. Any help will be really
> apricated.
>=20
> #!/usr/bin/perl
Always start with "use strict;" and "use warnings;" :=20
* http://perl-begin.org/tutorials/bad-elements/
> use Text::CSV;
> use DBI;
>=20
> # CONFIG VARIABLES
> my $platform =3D "mysql";
> my $database =3D "new";
> my $host =3D "localhost";
> my $port =3D "3306";
> my $tablename =3D "by_region";
> my $username =3D "root";
> my $password =3D "root123";
>=20
> # DATA SOURCE NAME
> my $dsn =3D "dbi:$platform:$database:$host:$port";
>=20
> my @Region=3D('East','North','South','West');
> my @Status_String=3D('Pws','Open','Hold','Pwu','Reopen');
>=20
> my @EastCount =3D(0,0,0,0,0);
> my @NorthCount =3D(0,0,0,0,0);
> my @SouthCount =3D(0,0,0,0,0);
> my @WestCount =3D(0,0,0,0,0);
> my @Region_Total=3D(0,0,0,0);
This is the varvarname anti-pattern:
http://perl-begin.org/tutorials/bad-elements/#varvarname
You really should use a hash here.
>=20
> sub ByRegion
> {
> my @columns=3D@_;
>=20
> if($columns[1] =3D~ /^$Region[0]$/)
You probably want:
if ($columns[1] eq $Region[0])
instead.
But this will be a given using a hash:
http://perl-begin.org/topics/hashes/
> {
> $EastCount[0]++ if $columns[2] =3D~ /^$Status_String[0]$/;
> $EastCount[1]++ if $columns[2] =3D~ /^$Status_String[1]$/;
> $EastCount[2]++ if $columns[2] =3D~ /^$Status_String[2]$/;
> $EastCount[3]++ if $columns[2] =3D~ /^$Status_String[3]$/;
> $EastCount[4]++ if $columns[2] =3D~ /^$Status_String[4]$/;
Again map the @Status_String to their numeric values using a hash and do:
$EastCount[$status_string_to_index{$columns[2]}]++;
[More of the same snipped].
> #############
> # reg_id maps:
> # 0 -East
> # 1 -North
> # 2 -South
> # 3 -West
> ############
>=20
> sub InsertByRegion
> {
> $Region_Total[0]+=3D$_ foreach @EastCount;
> $Region_Total[1]+=3D$_ foreach @NorthCount;
> $Region_Total[2]+=3D$_ foreach @SouthCount;
> $Region_Total[3]+=3D$_ foreach @WestCount;
This will be better using a hash. And @Region_Total should not be global.
> my $region_id=3D1;
>=20
> my @data =3D (
> ['0',$region_id,$EastCount[0],$EastCount[1],$EastCount[2],$E astCount[3],$=
EastCount[4],$Region_Total[0]],
> ['1',$region_id,$NorthCount[0],$NorthCount[1],$NorthCount[2] ,$NorthCount[=
3],$NorthCount[4],$Region_Total[1]],
> ['2',$region_id,$SouthCount[0],$SouthCount[1],$SouthCount[2] ,$SouthCount[=
3],$SouthCount[4],$Region_Total[2]],
> ['3',$region_id,$WestCount[0],$WestCount[1],$WestCount[2],$W estCount[3],$=
WestCount[4],$Region_Total[3]],
> );
>=20
> ## PERL DBI CONNECT
Why is the comment in all-capital-letters?
> my $connect =3D DBI->connect($dsn, $username, $password);
>=20
> ### PREPARE THE QUERY
> my $query =3D "INSERT INTO by_region
> (reg_id,region_id,pws,open,hold,pwu,re_open,total) VALUES
> (?,?,?,?,?,?,?,?)";
>=20
Your indentation here is misleading.
> my $query_handle =3D $connect->prepare($query);
> ### EXECUTE THE QUERY
> for my $datum (@data) {
> $query_handle->execute(@$datum);
> }
>=20
> }
>=20
> # Over All Pending data File
> my $oapdata=3D'sample.csv';
> my $csv=3DText::CSV->new({ sep_char =3D> "\t" });
> open(CSV,"<",$oapdata) or die $!;
> while () {
> if ($csv->parse($_))
> {
> my @columns =3D $csv->fields();
> ByRegion(@columns);
> }
> else
> {
> my $err =3D $csv->error_input;
> print "Failed to parse line: $err";
> }
>=20
>=20
> }
>=20
> InsertByRegion;
>=20
> print "The END!!!\n";
>=20
> close CSV;
>=20
>=20
> Thanks for your time.
>=20
Regards,
Shlomi Fish
--=20
------------------------------------------------------------ -----
Shlomi Fish http://www.shlomifish.org/
Funny Anti-Terrorism Story - http://shlom.in/enemy
When a FLOSS developer says they will work on something, he or she means
â=9Cmaybeâ=9D.
Please reply to list if it's a mailing list post - http://shlom.in/reply .
--
To unsubscribe, e-mail: beginners-unsubscribe@perl.org
For additional commands, e-mail: beginners-help@perl.org
http://learn.perl.org/
Re: summarise input data using Perl
am 14.07.2011 10:37:51 von Mohan L
--20cf3005dc34d0b3ae04a80372bd
Content-Type: text/plain; charset=ISO-8859-1
On Wed, Jul 13, 2011 at 11:30 PM, Leo Susanto wrote:
> Mohan,
>
> I would suggest you learning perl hash: http://perl101.org/hashes.html
>
Yes. I will learn the perl hash.
>
> #!/usr/bin/perl
> use Text::CSV;
> use DBI;
> use Data::Dumper;
>
> # CONFIG VARIABLES
> ...
> # DATA SOURCE NAME
> ...
>
Sorry, I messed some thing. Can you please explain the code? . You
explanation will give more idea to learn the below hash usage.
>
> my @regions = ('east','north','south','west');
> my @statuses_string = ('pws','open','hold','pwu','reopen');
> my %region_data;
> foreach my $region (@regions) {
> foreach my $status_string (@statuses_string) {
> $region_data{$region}{status}{$status_string}{count} = 0;
> }
> $region_data{$region}{total_count} = 0;
> }
> print Dumper \%region_data; # inspect the pristine data structure
> sub ByRegion
> {
> my @columns=@_;
> $regions{ lc($columns[1]) }{status}{ lc($columns[2]) }c++;
> $regions{ lc($columns[1]) }{total_count}++;
> }
>
>
And also gives the below error.
Bareword found where operator expected at ./demo.pl line 25, near "}c"
(Missing operator before c?)
syntax error at ./demo.pl line 25, near "}c"
syntax error at ./demo.pl line 29, near "File
Thanks & Rg
Mohan L
--20cf3005dc34d0b3ae04a80372bd--
Re: summarise input data using Perl
am 14.07.2011 11:00:28 von Mohan L
--20cf30334c0dba948804a803c3dc
Content-Type: text/plain; charset=ISO-8859-1
>
> Sorry, I messed some thing. Can you please explain the code? . You
> explanation will give more idea to learn the below hash usage.
>
>>
>> my @regions = ('east','north','south','west');
>> my @statuses_string = ('pws','open','hold','pwu','reopen');
>> my %region_data;
>> foreach my $region (@regions) {
>> foreach my $status_string (@statuses_string) {
>> $region_data{$region}{status}{$status_string}{count} = 0;
>> }
>> $region_data{$region}{total_count} = 0;
>> }
>> print Dumper \%region_data; # inspect the pristine data structure
>
>
>> sub ByRegion
>> {
>> my @columns=@_;
>> $regions{ lc($columns[1]) }{status}{ lc($columns[2]) }c++;
>> $regions{ lc($columns[1]) }{total_count}++;
>> }
>>
>>
> And also gives the below error.
>
> Bareword found where operator expected at ./demo.pl line 25, near "}c"
> (Missing operator before c?)
> syntax error at ./demo.pl line 25, near "}c"
> syntax error at ./demo.pl line 29, near "File
>
>
Hi Leo,
Sorry . The below line works fine. It is {count}++
$region_data{ lc($columns[3]) }{status}{ lc($columns[0]) } { count}++;
$region_data{ lc($columns[3]) }{total_count}++;
Thanks for you time !!!.
Thanks & Rg
Mohan L
--20cf30334c0dba948804a803c3dc--
Re: summarise input data using Perl
am 14.07.2011 11:36:52 von Shlomi Fish
Hi Leo,
I'm commenting on your code below because it exhibits some bad elements.
On Wed, 13 Jul 2011 11:00:33 -0700
Leo Susanto wrote:
> Mohan,
>
> I would suggest you learning perl hash: http://perl101.org/hashes.html
>
Also see:
http://perl-begin.org/topics/hashes/
> #!/usr/bin/perl
> use Text::CSV;
> use DBI;
> use Data::Dumper;
There is no "use strict;" and "use warnings;" at the beginning of the file:
http://perl-begin.org/tutorials/bad-elements/
>
> # CONFIG VARIABLES
> ...
> # DATA SOURCE NAME
> ...
Why is this in all-capital-letters?
>
> my @regions = ('east','north','south','west');
> my @statuses_string = ('pws','open','hold','pwu','reopen');
Why did you put them in lowercase letters? This will allow people to enter
"sOUth" or other stuff like that. Better check for the existence of the exact
string, and if so throw an exception.
BTW, "@statuses_string" should be "@status_strings" probably.
> my %region_data;
> foreach my $region (@regions) {
> foreach my $status_string (@statuses_string) {
> $region_data{$region}{status}{$status_string}{count} = 0;
> }
> $region_data{$region}{total_count} = 0;
> }
You can write this as:
my %region_data = (map { $_ => { total_count => 0,
status => { map { $_ => { count => 0 } } @status_strings },
} } @regions);
> print Dumper \%region_data; # inspect the pristine data structure
>
> sub ByRegion
> {
> my @columns=@_;
If you're passing the columns you should pass them as an array reference.
> $regions{ lc($columns[1]) }{status}{ lc($columns[2]) }c++;
> $regions{ lc($columns[1]) }{total_count}++;
1. The "c++" here is a typo.
2. You should unpack the columns:
my (undef, $region, $status) = @$columns;
3. You reference $regions{lc($columns[1])} twice.
Regards,
Shlomi Fish
--
------------------------------------------------------------ -----
Shlomi Fish http://www.shlomifish.org/
Why I Love Perl - http://shlom.in/joy-of-perl
Stray XSLT code causes more deaths than road accidents.
Please reply to list if it's a mailing list post - http://shlom.in/reply .
--
To unsubscribe, e-mail: beginners-unsubscribe@perl.org
For additional commands, e-mail: beginners-help@perl.org
http://learn.perl.org/
Re: summarise input data using Perl
am 14.07.2011 17:52:14 von Leo Susanto
On Thu, Jul 14, 2011 at 2:36 AM, Shlomi Fish wrote=
:
> Hi Leo,
>
> I'm commenting on your code below because it exhibits some bad elements.
>
> On Wed, 13 Jul 2011 11:00:33 -0700
> Leo Susanto wrote:
>
>> #!/usr/bin/perl
>> use Text::CSV;
>> use DBI;
>> use Data::Dumper;
>
> There is no "use strict;" and "use warnings;" at the beginning of the fil=
e:
>
> http://perl-begin.org/tutorials/bad-elements/
>
Shlomi is correct here, always add use strict.
>
>> my %region_data;
>> foreach my $region (@regions) {
>> =A0 =A0 foreach my $status_string (@statuses_string) {
>> =A0 =A0 =A0 =A0 $region_data{$region}{status}{$status_string}{count} =3D=
0;
>> =A0 =A0 }
>> =A0 =A0 $region_data{$region}{total_count} =3D 0;
>> }
>
> You can write this as:
>
> my %region_data =3D (map { $_ =3D> { total_count =3D> 0,
> =A0 =A0 =A0 =A0status =3D> { map { $_ =3D> { count =3D> 0 } } @status_str=
ings },
> =A0 =A0 =A0 =A0} } @regions);
>
personally, I find "map" function more confusing to read compared to
"foreach", "foreach" is easier on the brain since it a closer syntax
to "for".
but on certain cases, that involves simple 1 liner data manipulation,
I prefer to use map. But apparently, many problems that I encountered
can not be reduced to 1 simple line of data manipulation.
--
To unsubscribe, e-mail: beginners-unsubscribe@perl.org
For additional commands, e-mail: beginners-help@perl.org
http://learn.perl.org/