group by one column and then calculate net value of another column 1

[jaiperl04]

Hi,

I need some help with perl code for the below:
Input data:
$Date,$Mod_Time,$Index,$Date_Inst,$Time_Inst,$Reason,$In,$Phase,$Type,$BuyorSell,$Size,$Price
20100611 090009.870000 2 20100611 85954 5 Tes C L -1 16 43.85
20100611 090009.870000 2 20100611 90009 4 Tes C L 1 16 3.85
20100611 090011.630000 2 20100611 85954 4 ASD C L -1 461 23.85
20100611 090012.630000 2 20100611 90011 24 ASD C L -1 800 43.858
20100611 090020.630000 2 20100611 90011 14 Tes C M 1 461 43.85
20100611 090027.770000 2 20100611 85959 4 ASD C L 1 198 13.715

Output required:

I want to calculate the net buy or sell from the above data in every 10 secs interval.The column BuyorSell is -1 for sell and +1 for a buy.
For example in the 1st 2 rows of input data we could say that the net buy_sell was =(-1+1)=0;
So i want to group my data in 10 sec interval and in those 10 sec intervals I want to calculate whether there was a net sell or buy
The output should be something like this:

Time NetBuy/Sell
90000-90010 0
90010-90020 -2
90020-90030 +2

SO it means:
Time NetBuy/Sell
90000-90010 Neither sell nor buy
90010-90020 Net Sell
90020-90030 Net Buy

This is what I am trying:
open(IN)
open(OUT)
while($line=<IN>)
{ chomp ($line);
($Date,$Mod_Time,$Precision,$Date_Inst,$Time_Inst,$Reason,$In,$Phase,$Type,$BuyorSell,$Size,$Price)= split(/ /,$line);
$time = "090000";
until($time == "173001")
{
if($Mod_Time>$time && $Mod_Time< ($time+10))
{
#calculate the net buy_sell->I dont know how to calculate this!
}
$time = $time+10;
}
}

I have no clue how to calculate the net buy and sell for each 10 sec group. It would be great if you could help with this pleasee.
Any help would be great..

Thanks in advance.

 

[prex1]

Assuming the file is for a single day (but I thought that the seconds in a day were 86400, how comes that you have 90000 and more?):

while(<IN>){
  (undef,$sec,undef,undef,undef,undef,undef,undef,undef,$BoS)=split;
  $rounded=int($sec/10)*10;
  $netBoS{$rounded}+=$BoS;
}
for(sort{$a<=>$b}keys%netBoS){
  while($_>$exsec&&$exsec){
    print $exsec,'-',$exsec+=10,' ',0,"\n";
  }
  print $_,'-',$exsec=$_+10,' ',$netBoS{$_},"\n";
}

Franco

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[jaiperl04]

Thanks prex1.
They are not seconds, they refer time, so 90000 means 9:00:00am..I have just changed the format for my calculation.
So 090009.87 means 09:00:09:87.
Yes it is for one day so basically I have to group into 10 secs for 1 day between 9am to 5pm..90000 to 170000.
Hope it makes sense?

Cheers

 

[prex1]

Not very much...
This means that to go from 9:00:50 to 9:01:00 you need to increase by 50, not by 10. This was not accounted for in my code above.
If you need to print all (!?) the 10 sec increments in a day the for loop above would be:

for($t=90000;$t<170000;$t+=10){
  if(exists$netBoS{$t}){
    print $t,'-',$t+10,' ',$netBoS{$t},"\n";
  }else{
    print $t,'-',$t+10," 0\n";
  }
}

Franco

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[prex1]

Oops! sorry...
The [tt]for[/tt] above is incorrect as, as I told to myself , the increment is not 10 everywhere.
Hope you'll be able to figure out yourself how to modify it.

Franco

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[jaiperl04]

Thanks prex1 again.

I might use the below:
sub snap{($hour,$minute,$second) = unpack("a2 a2 a2",$time);$second++;if($second==60){$second="00";$minute++;if($minute==60){$minute = "00";$hour++}};$time = $hour.$minute.$second;}

and then use your logic..
do you reckon it will work?
or actually i will try and let you know

thanks!

 

[prex1]

You need to write
[tt]$time=$hour.sprintf"%02d%02d",$minute,$second;[/tt]
Also unpack shouldn't work that way, as hour 9 is written with one figure.
The guy that designed that database enjoyed complicating simple things...

Franco

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[jaiperl04]

Hi prex1..

I cant make this to work... could you please help?
my code:
open(IN)
$time = "090000";$sum=0;
until($time == "173000")
{
while($line=<IN>)
{ chomp ($line);
($Date,$Mod_Time,$Precision,$Date_Inst,$Time_Inst,$Reason,$In,$Phase,$Type,$BuyorSell,$Size,$Price)= split(/ /,$line);
if($Mod_Time>$time && $Mod_Time< ($time+10))
{
$sum=$sum+$BuyorSell;
print $time,"-",$time+10,$sum, "\n";
$sum=0;
$hour,$minute,$second) = unpack("a2 a2 a2",$time);$second=$second+10;if($second==60){$second="00";$minute++;if($minute==60){$minute = "00";$hour++}};$time = $hour.$minute.$second;
}
}
}

The output is:
090000-90010-1
090010-90020-1


and then it goes on for ages!

Even when I try your code, I get the output as:
90000-90010 -1
90010-90020 1
90030-90030 0
90040-90040 0

-if you see after 10-20, it shows 30-30 and 40-40.

In your code I can see you doing the- $rounded=int($sec/10)*10; I guess you are rounding it up? Do we need this as I want to check for the whole day ie. in 1 day from 9:00:00 to 17:00:00, divide the time into 10 second intervals and then calculate from the input data whether net is buy/sell

 

[prex1]

Sorry to be frank, but your code is quite a mess. Also you should more clearly determine what you want to do before starting to code.
From what you state and from your code I understand that you want to go up to 17:30 (not 17:00, but it's a detail) and I also assume that the data file is sorted in time, so that you can print out the result as it goes.
This is a further attempt (untested, and perhaps with a clearer logic):

$time='090000';
$time10='090010';
$sum=0;
while($line=<IN>){
  ($Date,$Mod_Time,$Precision,$Date_Inst,$Time_Inst,$Reason,$In,$Phase,$Type,$BuyorSell,$Size,$Price)=split(/\s/,$line);
  while($Mod_Time>=$time10){
    print $time,"-",$time10,' ',$sum,"\n";
    $sum=0;
    $time=$time10;
    $time10=increment_by10($time);
  }
  $sum+=$BuyorSell;
}
print $time,"-",$time10,' ',$sum,"\n";
$time=$time10;
$time10=increment_by10($time);
while($time10<173000){
  print $time,"-",$time10,"0 \n";
  $time=$time10;
  $time10=increment_by10($time);
}
sub increment_by10{
  my($time)=@_;
  my$hour=substr($time,-6,2);
  my$minute=substr($time,-4,2);
  my$second=substr($time,-2);
  $second+=10;
  if($second==60){
    $second=0;
    $minute++;
    if($minute==60){
      $minute=0;
      $hour++;
    }
  }
  return $hour.sprintf("%02d%02d",$minute,$second);
}

To be noted that perl automatically strips out the leading 0 from time when making the numeric comparison. That's why there is substr instead of unpack in the sub.
And please put [ignore]

your code here

[/ignore] between code tags!

Franco

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[jaiperl04]

Thanks prex1 . It works absolutely fine.. you are a star.
My apologies for not folloiwng the norms, will put the code properly next time and try to be more neat in writing my codes
Many thanks