'Modify code to capture values greater than - instead of exact match
The following code works well for identifying whether a value is hit or missed over following rows and giving the output column showing the time the condition was met.
import datetime,numpy as np,pandas as pd;
nan = np.nan;
a = pd.DataFrame( {'price': {datetime.time(9, 0): 1, datetime.time(10, 0): 0, datetime.time(11, 0): 3, datetime.time(12, 0): 4, datetime.time(13, 0): 7, datetime.time(14, 0): 6, datetime.time(15, 0): 5, datetime.time(16, 0): 4, datetime.time(17, 0): 0, datetime.time(18, 0): 2, datetime.time(19, 0): 4, datetime.time(20, 0): 7}, 'reversal': {datetime.time(9, 0): nan, datetime.time(10, 0): nan, datetime.time(11, 0): nan, datetime.time(12, 0): nan, datetime.time(13, 0): nan,
datetime.time(14, 0): 6.0, datetime.time(15, 0): nan, datetime.time(16, 0): nan, datetime.time(17, 0): nan, datetime.time(18, 0): nan, datetime.time(19, 0): nan, datetime.time(20, 0): nan}});
a['target_hit_time']=a['target_miss_time']=nan;
a['target1']=a['reversal']+1;
a['target2']=a['reversal']-a['reversal'];
a.sort_index(1,inplace=True);
hits = a.ix[:,:-2].dropna();
for row,hit in hits.iterrows():
forwardRows = [row]<a['price'].index.values
targetHit = a.index.values[(hit['target1']==a['price'].values) & forwardRows][0];
targetMiss = a.index.values[(hit['target2']==a['price'].values) & forwardRows][0];
if targetHit>targetMiss:
a.loc[row,"target_miss_time"] = targetMiss;
else:
a.loc[row,"target_hit_time"] = targetHit;
a
This image shows the output from the above code which can easily be reproduced by running this code:
The issue I have is that when this code is utilised on real data the price may not exactly match and/or may gap though a value. So if we look at the following image:
We see that target1 criteria would be met if we were looking for a value >= 7.5 and not just looking for the value 7.5. How can I modify the code to achieve this?
Solution 1:[1]
Some ifs and thats all :D...
import datetime,numpy as np,pandas as pd;
nan = np.nan;
a = pd.DataFrame( {'price': {datetime.time(9, 0): 1, datetime.time(10, 0): 0, datetime.time(11, 0): 3, datetime.time(12, 0): 4, datetime.time(13, 0): 7, datetime.time(14, 0): 6, datetime.time(15, 0): 5, datetime.time(16, 0): 4, datetime.time(17, 0): 2, datetime.time(18, 0): 2, datetime.time(19, 0): 4, datetime.time(20, 0): 8}, 'reversal': {datetime.time(9, 0): nan, datetime.time(10, 0): nan, datetime.time(11, 0): nan, datetime.time(12, 0): nan, datetime.time(13, 0): nan,
datetime.time(14, 0): 6.0, datetime.time(15, 0): nan, datetime.time(16, 0): nan, datetime.time(17, 0): nan, datetime.time(18, 0): nan, datetime.time(19, 0): nan, datetime.time(20, 0): nan}});
a['target_hit_time']=a['target_miss_time']=nan;
a['target1']=a['reversal']+1;
a['target2']=a['reversal']-a['reversal'];
a.sort_index(1,inplace=True);
hits = a.ix[:,:-2].dropna();
for row,hit in hits.iterrows():
forwardRows = a[a.index.values > row];
targetHit = hit['target1']<=forwardRows['price'].values;
targetMiss = hit['target2']==forwardRows['price'].values;
targetHit = forwardRows[targetHit].head(1).index.values;
targetMiss = forwardRows[targetMiss].head(1).index.values;
targetHit, targetMiss = \
targetHit[0] if targetHit else [], \
targetMiss[0] if targetMiss else [];
goMiss,goHit = False,False
if targetHit and targetMiss:
if targetHit>targetMiss: goMiss=True;
else: goHit=True;
elif targetHit and not targetMiss:goHit = True;
elif not targetHit and targetMiss:goMiss = True;
if goMiss:a.loc[row,"target_miss_time"] = targetMiss;
elif goHit:a.loc[row,"target_hit_time"] = targetHit;
print '#'*50
print a
'''
##################################################
price reversal target1 target2 target_hit_time target_miss_time
09:00:00 1 NaN NaN NaN NaN NaN
10:00:00 0 NaN NaN NaN NaN NaN
11:00:00 3 NaN NaN NaN NaN NaN
12:00:00 4 NaN NaN NaN NaN NaN
13:00:00 7 NaN NaN NaN NaN NaN
14:00:00 6 6.0 7.0 0.0 20:00:00 NaN
15:00:00 5 NaN NaN NaN NaN NaN
16:00:00 4 NaN NaN NaN NaN NaN
17:00:00 2 NaN NaN NaN NaN NaN
18:00:00 2 NaN NaN NaN NaN NaN
19:00:00 4 NaN NaN NaN NaN NaN
20:00:00 8 NaN NaN NaN NaN NaN
'''
Solution 2:[2]
Without modifying your code heavily, this is what I came up with:
import numpy as np
for row,hit in hits.iterrows():
print ("row", row)
print ("hit",hit)
forwardRows = a[a.index.values > row]
targetHit = forwardRows[(hit['target1'] <= forwardRows['price'].values)].head(1).index.values
targetMiss = forwardRows[(hit['target2'] >= forwardRows['price'].values)].head(1).index.values
if targetHit>targetMiss:
a.loc[row,"target_miss_time"] = targetMiss
else:
a.loc[row,"target_hit_time"] = targetHit
price reversal target1 target2 target_hit_time target_miss_time
09:00:00 1 NaN NaN NaN NaN NaN
10:00:00 0 NaN NaN NaN NaN NaN
11:00:00 3 NaN NaN NaN NaN NaN
12:00:00 4 NaN NaN NaN NaN NaN
13:00:00 7 NaN NaN NaN NaN NaN
14:00:00 6 6.5 7.5 0.0 [20:00:00] NaN
15:00:00 5 NaN NaN NaN NaN NaN
16:00:00 4 NaN NaN NaN NaN NaN
17:00:00 2 NaN NaN NaN NaN NaN
18:00:00 2 NaN NaN NaN NaN NaN
19:00:00 4 NaN NaN NaN NaN NaN
20:00:00 8 NaN NaN NaN NaN NaN
This is still to be improved since targetHit, targetMiss return an array and you need to check if there are any elements in array and if there are elements in both arrays - you need to compare first alements. Right now it only works if one array is empty.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 |