Perl Data, AnalyticalWay

Perl is an established language with lots of code examples provided on the Web. It is a powerful language (meaning you can do a lot with very little code). Perl has a rich library set, especially in regards to text processing. The language can take some getting use to because of its heavy use of control character. For example: ‘.’ means concatenate strings, and the leading character (@, $, & and %) indicates the data type being accessed (eg. $foobar is a scalar while @foobar is an array). Below is an example of a database containing two tables, customers and purchase order. The Customer table has customerid (the unique identifier for the customer), name (The customer’s name) and age (the customer’s age). The PurchaseOrder table has POID (the unique id for the purchase order), customerid (which refers back to the customer who made the purchase) and Purchase (what the purchase was).

Example:

Customer
CustomerID	Name	Age
1	Joe	23
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18

PurchaseOrder
POID	CustomerID	Purchase
1	3	Fiction
2	1	Biography
3	1	Fiction
4	2	Biography
5	3	Fiction
6	4	Fiction

SELECT

Create the file select.pl with the following code:

#!/usr/bin/perl -w

my @file_lines;

# Loop through data and print lines

while ( my $line = <> ) {

# Print Line

print $line . \n ;

# Populate next line

push @file_lines, $line; }

Then run the code:
perl select.pl customer.txt

CustomerID	Name	Age
1	Joe	23
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18

ORDER BY

Create the file orderby.pl with the following code:

#!/usr/bin/perl -w

# Open the file

open(DATA, $ARGV[0]); my $line;
my $iCnt;

# Initialize variables

$iCnt =0;
$iLoop =0;

# Loop through data and populate a array for later use.

while($line = )
{

# Copy data to an arry skipping header (first line with field names)

if ($iCnt >0)
{
$lines[$iCnt-1] = $line;
}

$iCnt ++;
}

# Close file object

close DATA;

# Sort using on custom sort based on second field

@sorted = sort{@PANa = split( \, , $a); @PANb = split( \, , $b); return $PANa[2] cmp $PANb[2] } (@lines);

# Loop through data and print lines

while($iLoop < $iCnt-1)
{
print $sorted[$iLoop];
$iLoop ++;
}

Then run the code:
perl orderby.pl customer.txt

CustomerID	Name	Age
5	Susan	18
1	Joe	23
3	Lin	34
2	Mika	45
4	Sara	56

WHERE

Create the file select_by_id.pl with the following code:

#!/usr/bin/perl -w my @file_lines;

# Loop through passed file

while ( my $line = <> ) {

# Split line using a comma

@PAN =split( \, , $line);
$out = $PAN[0];

# If ID is passed ID then print

if ($out eq 1 )
{
print $line . \n ;
}

# Populate next line

push @file_lines, $line; }

Then run the code:
perl select_by_id.pl customer.txt 1

CustomerID	Name	Age
5	Susan	18

INNER JOIN

Create the file innerjoin.pl with the following code:

#!/usr/bin/perl -w

# Open both files

open(DATA1, $ARGV[0]);
open(DATA2, $ARGV[1]); my $lineCust;
my $lineOrder;
my $iCnt;
my $iLoop;

# Initialize variables

$iCnt =0;

# Loop through data and populate a array for later use.

while($lineOrder = )
{
$orders[$iCnt] = $lineOrder;
$iCnt ++;
}

# Close file object

close DATA2;
while($lineCust = )
{

# Split line using a comma

@PAN1 =split( \, , $lineCust);
$out1 = $PAN1[0];

# Remove newline character

chop($PAN1[2]);
chop($PAN1[2]);

$iLoop =0;

while($iLoop < $iCnt)
{

# Split line using a comma

@PAN2 =split( \, , $orders[$iLoop]);
$out2 = $PAN2[1];

#If there is a match print

if ($out1 eq $out2)
{
print $PAN1[0] . , . $PAN1[1] . , . $PAN1[2] . , . $orders[$iLoop];
}

$iLoop ++;
}
}

# Close file object

close DATA1;

Then run the code:
perl innerjoin.py customer.txt orders.txt

CustomerID	Name	Age	POID	Purchase
1	Joe	23	2	Biography
1	Joe	23	3	Fiction
2	Mika	45	4	Biography
3	Lin	34	1	Fiction
3	Lin	34	5	Fiction
4	Sara	56	6	Fiction

LEFT OUTER JOIN

Create the file leftouterjoin with the following code:

#!/usr/bin/perl -w

# Open both files

open(DATA1, $ARGV[0]);
open(DATA2, $ARGV[1]); my $lineCust;
my $lineOrder;
my $iCnt;
my $iLoop;
my $iFound;

# Initialize variables

$iCnt =0;

# Loop through data and populate a array for later use.

while($lineOrder = )
{
$orders[$iCnt] = $lineOrder;
$iCnt ++;
}

# Close file object

close DATA2; while($lineCust = )
{

# Split line using a comma

@PAN1 =split( \, , $lineCust);
$out1 = $PAN1[0];

# Remove newline character

chop($PAN1[2]);
chop($PAN1[2]);

$iLoop =0;
$iFound =0;

while($iLoop < $iCnt)
{

# Split line using a comma

@PAN2 =split( \, , $orders[$iLoop]);
$out2 = $PAN2[1];

#If there is a match print

if ($out1 eq $out2)
{
print $PAN1[0] . , . $PAN1[1] . , . $PAN1[2] . , . $orders[$iLoop];
$iFound =1;
}

$iLoop ++;
}

#If there was no match print

if ($iFound ==0)
{
print $PAN1[0] . , . $PAN1[1] . , . $PAN1[2] . , . , . , . ,\n ;
}
}

# Close file object

close DATA1;

Then run the code:
perlleftouterjoin.pl customer.txt orders.txt

CustomerID	Name	Age	POID	Purchase
1	Joe	23	2	Biography
1	Joe	23	3	Fiction
2	Mika	45	4	Biography
3	Lin	34	1	Fiction
3	Lin	34	5	Fiction
4	Sara	56	6	Fiction
5	Susan	18	NULL	NULL

GROUP BY

Create the file groupby.pl with the following code:

#!/usr/bin/perl -w

#Open the file

open(DATA1, $ARGV[0]); my $line;
my $iCnt;
my $iLoop;

# Initialize variables

$iCnt =1;
$iloop =0;

# Loop through data

while($line = )
{

# Split line using a comma

@PAN1 =split( \, , $line);
$id = $PAN1[0];
$name = $PAN1[1];
$age = $PAN1[2];

# Remove newline character

chop($age);
chop($age);

if ($iloop <2 )
{
$prior_id = $id ;
$iCnt = 0;
}

if ($prior_id eq $id)
{
$iCnt ++;
}
else
{
print $prior_name . , . $prior_age . , . $iCnt . \n ;
$iCnt =1;
}

$iloop ++;
$prior_id = $id ;
$prior_name = $name ;
$prior_age = $age ; }

print $prior_name . , . $prior_age . , . $iCnt . \n ;

# Close file object

close DATA;

Then run the code:
perl groupby.pl customer.txt

Name	Age	Orders
Joe	23	1
Mika	45	2
Lin	34	2
Sara	56	1

UPDATE

Create the file update.pl with the following code:

#!/usr/bin/perl -w

# Open the file

open(DATA, $ARGV[0]);

# Loop through data

while()
{

# Split line using a comma

@PAN =split( \, , $_);
$out = $PAN[0];

# Pint if ID is not equal to passed ID otherwise update.

if ($out eq $ARGV[1])
{
print $PAN[0]. , . $PAN[1] . , . $ARGV[2] . \n ;
}
else
{
print $_ ;
}

# Close file object

close DATA;
}

Then run the code:
perl groupby.pl customer.txt 1 23

Customer
CustomerID	Name	Age
1	Joe	26
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18

INSERT

Create the file insert.pl with the following code:

#!/usr/bin/perl -w

#Open the file

open(DATA, $ARGV[0]);

# Loop through data and print lines

while()
{
print $_ ;
}

# Add new line from passed arguments

print $ARGV[1]. , . $ARGV[2] . , . $ARGV[3] . \n ;

# Close file object

close DATA;

Then run the code:
perl insert.pl customer.txt 6 Joe 34

Customer
CustomerID	Name	Age
1	Joe	23
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18
6	Terry	50

DELETE

Create the file delete.pl with the following code:

#!/usr/bin/perl -w

#Open the file

open(DATA, $ARGV[0]);

# Loop through data

while()
{

# Split line using a comma

@PAN =split( \, , $_);
$out = $PAN[0];

# If ID is not passed ID then print

if ($out ne $ARGV[1])
{
print $_ ;
} }

# Close file object

close DATA;

Then run the code:
perl delete.pl customer.txt 1

Customer
CustomerID	Name	Age
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18

Leave a Reply Cancel reply