Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Re: Data Set Combination

by injunjoel (Priest)
on Aug 27, 2005 at 04:05 UTC ( #487089=note: print w/replies, xml ) Need Help??


in reply to Data Set Combination

Greetings,
#!/usr/bin/perl -w use strict; use DBI; my $pair1 = shift; my $pair2 = shift; my $dbh = DBI->connect("DBI:CSV:") or die "Cannot connect: " . $DBI::errstr; $dbh->{RaiseError} = 1; my $query1 = "SELECT * FROM $pair1"; my $query2 = "SELECT * FROM $pair2"; my $getpair1 = $dbh->prepare($query1); my $getpair2 = $dbh->prepare($query2); my %pair1_hash = do{ $getpair1->execute(); while($_ = $getpair1->fetchrow_hashref()){ $_{$_->{Date}} = $_; } %_; }; my %pair2_hash = do{ $getpair2->execute(); while($_ = $getpair2->fetchrow_hashref()){ $_{$_->{Date}} = $_; } %_; }; my %combo_hash = do{ map{ if(!defined $pair1_hash{$_}){ $_, $pair2_hash{$_}; }elsif(!defined $pair2_hash{$_}){ $_, $pair1_hash{$_}; }else{ #computations here to combine the contents of #%pair1_hash and %pair2_hash. Utilize the fact that #the last statement evaluated will be the return value. #just as and example my %t; #reference to specific fields within each hash referred to #by $pair1_hash{$_} and $pair2_hash respectively $t{Open} = sprintf("%.2f",($pair1_hash{$_}->{Open} + $pair +2_hash{$_}->{Open} / 2)); $t{High} = sprintf("%.2f",($pair1_hash{$_}->{High} + $pair +2_hash{$_}->{High} / 2)); #So finally we return the key and a reference to #our computed hash. $_,\%t; } }do{ undef @_{keys %pair1_hash, keys %pair2_hash}; sort keys %_; }; };

The above is untested but the important parts are the creation of hashes keyed by dates (%pair1_hash & %pair2_hash). With our do block we utilize a hash to get all unique values from the list of keys of both hashes, in effect giving us all the unique dated between both hashes. Then with our map statement we take advantage of the fact that the last statement evaluated is the return value so we do some return logic with our if/elsif/else construct. This works because the key must be present in at least one of the hashes that contributed to the list from our do block. I did a sprintf on the average of two of the example columns from your posting above but you will need to do whichever calculations make sense for your purposes. The strategy though is to construct an intermediate hash to hold your computed values then return a reference to it under the key (in effect date) that our map is currently set to, from the list returned from our do block. whew!
Is that what you are looking for?

-InjunJoel
"I do not feel obliged to believe that the same God who endowed us with sense, reason and intellect has intended us to forego their use." -Galileo

Replies are listed 'Best First'.
Re^2: Data Set Combination
by Anonymous Monk on Aug 29, 2005 at 14:23 UTC
    THIS looks like what I had in mind. Because I am more familiar with this code, I will work with this before trying the SQL table join above. Thank you for all the replies, I will try this out now and let you know how it turns out.

    Peace be unto you.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://487089]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (4)
As of 2019-07-20 03:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?