1

I need to parse a price series in a pandas dataframe for occurrences of two consecutive lower lows which creates a price level we call NLBL. I'm able to do this with a simple conditional (see below) but instead of the TRUE values I need the value of the third previous candle high. PLUS I need to copy that very same level forward four more times.

This is some example data:

        Date      Time     Open     High      Low    Close
datetime                                                                     
2019-01-22 11:00:00  2019-01-22  11:00:00  2643.99  2647.47  2634.73  2634.73
2019-01-22 12:00:00  2019-01-22  12:00:00  2634.79  2638.55  2632.69  2635.94
2019-01-22 13:00:00  2019-01-22  13:00:00  2635.95  2636.35  2623.30  2631.93
2019-01-22 14:00:00  2019-01-22  14:00:00  2631.92  2632.29  2618.33  2622.66
2019-01-22 15:00:00  2019-01-22  15:00:00  2622.71  2632.90  2617.27  2625.49
2019-01-22 16:00:00  2019-01-22  16:00:00  2625.58  2633.81  2625.58  2633.81
2019-01-23 09:00:00  2019-01-23  09:00:00  2643.48  2652.44  2643.48  2650.97
2019-01-23 10:00:00  2019-01-23  10:00:00  2651.00  2653.19  2632.85  2634.47
2019-01-23 11:00:00  2019-01-23  11:00:00  2634.47  2638.55  2617.36  2617.46
2019-01-23 12:00:00  2019-01-23  12:00:00  2617.47  2627.43  2612.86  2627.31
2019-01-23 13:00:00  2019-01-23  13:00:00  2627.31  2631.70  2621.62  2629.92
2019-01-23 14:00:00  2019-01-23  14:00:00  2629.93  2635.26  2625.34  2629.21
2019-01-23 15:00:00  2019-01-23  15:00:00  2629.25  2639.22  2628.71  2636.61
2019-01-23 16:00:00  2019-01-23  16:00:00  2636.71  2639.54  2636.71  2638.60
2019-01-24 09:00:00  2019-01-24  09:00:00  2638.84  2641.03  2631.06  2636.14
2019-01-24 10:00:00  2019-01-24  10:00:00  2636.18  2647.20  2633.12  2640.49
2019-01-24 11:00:00  2019-01-24  11:00:00  2640.31  2645.37  2633.60  2644.08
2019-01-24 12:00:00  2019-01-24  12:00:00  2644.14  2644.42  2632.79  2634.31
2019-01-24 13:00:00  2019-01-24  13:00:00  2634.34  2635.16  2627.01  2633.62
2019-01-24 14:00:00  2019-01-24  14:00:00  2633.64  2638.47  2630.96  2637.04
2019-01-24 15:00:00  2019-01-24  15:00:00  2637.03  2643.21  2636.46  2642.66
2019-01-24 16:00:00  2019-01-24  16:00:00  2642.63  2643.10  2641.97  2641.99
2019-01-25 09:00:00  2019-01-25  09:00:00  2657.44  2663.57  2657.33  2661.64
2019-01-25 10:00:00  2019-01-25  10:00:00  2661.60  2671.61  2661.60  2669.49
2019-01-25 11:00:00  2019-01-25  11:00:00  2669.47  2670.50  2664.18  2669.13
2019-01-25 12:00:00  2019-01-25  12:00:00  2669.12  2672.38  2661.39  2664.88
2019-01-25 13:00:00  2019-01-25  13:00:00  2664.88  2668.49  2663.76  2667.93
2019-01-25 14:00:00  2019-01-25  14:00:00  2667.95  2669.12  2661.14  2665.27
2019-01-25 15:00:00  2019-01-25  15:00:00  2665.27  2666.52  2658.75  2663.06
2019-01-25 16:00:00  2019-01-25  16:00:00  2662.98  2664.74  2661.64  2664.14

This is how far I've come thus far:

min_data['NLBL'] = (min_data['Low'] < min_data['Low'].shift(1)) & (min_data['Low'].shift(1) < min_data['Low'].shift(2))
min_data['NLBL'] = min_data['NLBL'].shift(periods=1) # shifting downward as the trigger is valid after the close
print("\nResult:\n %s" % min_data.tail(30))

Result:
            Date      Time     Open     High      Low    Close  \
datetime                                                                        
2019-01-22 11:00:00  2019-01-22  11:00:00  2643.99  2647.47  2634.73  2634.73   
2019-01-22 12:00:00  2019-01-22  12:00:00  2634.79  2638.55  2632.69  2635.94   
2019-01-22 13:00:00  2019-01-22  13:00:00  2635.95  2636.35  2623.30  2631.93   
2019-01-22 14:00:00  2019-01-22  14:00:00  2631.92  2632.29  2618.33  2622.66   
2019-01-22 15:00:00  2019-01-22  15:00:00  2622.71  2632.90  2617.27  2625.49   
2019-01-22 16:00:00  2019-01-22  16:00:00  2625.58  2633.81  2625.58  2633.81   
2019-01-23 09:00:00  2019-01-23  09:00:00  2643.48  2652.44  2643.48  2650.97   
2019-01-23 10:00:00  2019-01-23  10:00:00  2651.00  2653.19  2632.85  2634.47   
2019-01-23 11:00:00  2019-01-23  11:00:00  2634.47  2638.55  2617.36  2617.46   
2019-01-23 12:00:00  2019-01-23  12:00:00  2617.47  2627.43  2612.86  2627.31   
2019-01-23 13:00:00  2019-01-23  13:00:00  2627.31  2631.70  2621.62  2629.92   
2019-01-23 14:00:00  2019-01-23  14:00:00  2629.93  2635.26  2625.34  2629.21   
2019-01-23 15:00:00  2019-01-23  15:00:00  2629.25  2639.22  2628.71  2636.61   
2019-01-23 16:00:00  2019-01-23  16:00:00  2636.71  2639.54  2636.71  2638.60   
2019-01-24 09:00:00  2019-01-24  09:00:00  2638.84  2641.03  2631.06  2636.14   
2019-01-24 10:00:00  2019-01-24  10:00:00  2636.18  2647.20  2633.12  2640.49   
2019-01-24 11:00:00  2019-01-24  11:00:00  2640.31  2645.37  2633.60  2644.08   
2019-01-24 12:00:00  2019-01-24  12:00:00  2644.14  2644.42  2632.79  2634.31   
2019-01-24 13:00:00  2019-01-24  13:00:00  2634.34  2635.16  2627.01  2633.62   
2019-01-24 14:00:00  2019-01-24  14:00:00  2633.64  2638.47  2630.96  2637.04   
2019-01-24 15:00:00  2019-01-24  15:00:00  2637.03  2643.21  2636.46  2642.66   
2019-01-24 16:00:00  2019-01-24  16:00:00  2642.63  2643.10  2641.97  2641.99   
2019-01-25 09:00:00  2019-01-25  09:00:00  2657.44  2663.57  2657.33  2661.64   
2019-01-25 10:00:00  2019-01-25  10:00:00  2661.60  2671.61  2661.60  2669.49   
2019-01-25 11:00:00  2019-01-25  11:00:00  2669.47  2670.50  2664.18  2669.13   
2019-01-25 12:00:00  2019-01-25  12:00:00  2669.12  2672.38  2661.39  2664.88   
2019-01-25 13:00:00  2019-01-25  13:00:00  2664.88  2668.49  2663.76  2667.93   
2019-01-25 14:00:00  2019-01-25  14:00:00  2667.95  2669.12  2661.14  2665.27   
2019-01-25 15:00:00  2019-01-25  15:00:00  2665.27  2666.52  2658.75  2663.06   
2019-01-25 16:00:00  2019-01-25  16:00:00  2662.98  2664.74  2661.64  2664.14   

          NLBL  
datetime                    
2019-01-22 11:00:00   True  
2019-01-22 12:00:00   True  
2019-01-22 13:00:00   True  
2019-01-22 14:00:00   True  
2019-01-22 15:00:00   True  
2019-01-22 16:00:00   True  
2019-01-23 09:00:00  False  
2019-01-23 10:00:00  False  
2019-01-23 11:00:00  False  
2019-01-23 12:00:00   True  
2019-01-23 13:00:00   True  
2019-01-23 14:00:00  False  
2019-01-23 15:00:00  False  
2019-01-23 16:00:00  False  
2019-01-24 09:00:00  False  
2019-01-24 10:00:00  False  
2019-01-24 11:00:00  False  
2019-01-24 12:00:00  False  
2019-01-24 13:00:00  False  
2019-01-24 14:00:00   True  
2019-01-24 15:00:00  False  
2019-01-24 16:00:00  False  
2019-01-25 09:00:00  False  
2019-01-25 10:00:00  False  
2019-01-25 11:00:00  False  
2019-01-25 12:00:00  False  
2019-01-25 13:00:00  False  
2019-01-25 14:00:00  False  
2019-01-25 15:00:00  False  
2019-01-25 16:00:00   True

So here is where I am stuck. What I need to do from here are two things:

  1. Replace each True in min_value['NLBL'] with Hight.Shift(3) - basically the highest low in the series. Also set each False to 0.

  2. Copy every min_value['NLBL'] row that is not populated with 0 forward four more times but only until it finds the next 0.

I assume a lambda expression would be appropriate but doing all this in the context of pandas has me stumped. Any ideas/insights how this can be done without resorting to a slow/ugly/annoying if loop?

This is just one example of several similar patterns I will have to implement. So solving this is a big issue for me and any help would be greatly appreciated.

Thanks in advance!

UPDATE: Someone asked for the correct output of the NLBL column:

          NLBL  
datetime                    
2019-01-22 14:00:00  2647.47  
2019-01-22 15:00:00  2638.55  
2019-01-22 16:00:00  2636.35  
2019-01-23 09:00:00  0  
2019-01-23 10:00:00  0  
2019-01-23 11:00:00  0  
2019-01-23 12:00:00  2652.44 
2019-01-23 13:00:00  2653.19  
2019-01-23 14:00:00  2653.19   
2019-01-23 15:00:00  2653.19   
2019-01-23 16:00:00  2653.19   
2019-01-24 09:00:00  2653.19   
2019-01-24 10:00:00  0  
2019-01-24 11:00:00  0  
2019-01-24 12:00:00  0  
2019-01-24 13:00:00  0  
2019-01-24 14:00:00  2645.37 
2019-01-24 15:00:00  2645.37 
2019-01-24 16:00:00  2645.37 
2019-01-25 09:00:00  2645.37 
2019-01-25 10:00:00  2645.37  
2019-01-25 11:00:00  0  
2019-01-25 12:00:00  0  
2019-01-25 13:00:00  0  
2019-01-25 14:00:00  0  
2019-01-25 15:00:00  0  
2019-01-25 16:00:00  2668.49

If it gets to a row with a TRUE value in the 'NLBL' column it'll count three rows backward, grab the 'High' value and replace TRUE with that one. It then copies that same 'High' value to the following four rows.

However if it finds a new TRUE it will stop copying forward and use the new High value.

Hope this makes sense.

Improve this question
5
  • can you clarify what you mean by "Hight.Shift(3) - basically the highest low in the series"? Commented Jan 27, 2019 at 13:35
  • can you post desired result which can help clarify above comment and #2 with forward four more times? Commented Jan 27, 2019 at 16:44
  • Okay thanks for the quick responses. @Josh - what I mean is that instead of TRUE it should show the High value from 4 rows back. So for the last row which has a 16:00 timestamp it would show 2668.49 (the 13:00) High. Hope that makes sense. Commented Jan 27, 2019 at 16:49
  • @Parfait - I have added the example output you asked for and provided a few more comments which I hope will clarify any questions. Thanks in advance for your help. Commented Jan 27, 2019 at 17:32
  • Hmmm...with that first block data, I cannot reproduce your second block of results with exact code. Click Run on this Pyfiddle demo. Be sure to set up a minimal reproducible example. Commented Jan 27, 2019 at 20:05

1 Answer 1

Reset to default
1

Thanks for the clarification, pretty sure I understood you (though if I understood correctly then there's a small error in the sample output).

Here's my solution: basically adds a helper column, swaps out 0 for NaN (if performance is a serious concern you could look into map instead of replace), and uses two fillna methods:

min_data['helper'] = min_data['High'].shift(3)
min_data.loc[min_data['NLBL'] == True, 'NLBL'] = min_data.loc[min_data['NLBL'] == True, 'helper']
min_data = min_data.drop(columns=['helper'])
min_data.NLBL = min_data.NLBL.replace(False, np.nan)
min_data['NLBL'] = min_data['NLBL'].fillna(method='ffill', limit=4)
min_data['NLBL'] = min_data['NLBL'].fillna(0)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Such an awesome and CLEVER solution!! Very nice :-) Clearly the loc calls are the ones doing the heavy lifting. I'll study this part in more detail to wrap my mind around how you did that. The rest is crystal. Thanks again.
Glad you liked it - you can mark the green tick if your issue is solved :)
I figured it out. I like how you used loc to create the mask for the NLBL column and then assign that the shifted High values. I tried that with the simple comparison but it wasn't working properly. You really saved my day, very much appreciated/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.