How do you index and use current and previous column values to calculate the next column value in a pandas.apply function?

jantarktic Published at Dev

jantarktic

I'm trying to build a function that calculates trailing stop values using Close and ATR values from a pandas dataframe.

The dataframe for reference is as follows:

            High    Low     Open    Close       ATR 
Date                                
2020-06-01  5.88    5.67    5.73    5.87        0.210000    
2020-06-02  6.00    5.83    5.96    5.90        0.207143    
2020-06-03  6.27    5.92    5.99    6.19        0.218776    
2020-06-04  6.58    6.12    6.20    6.57        0.236006    
2020-06-05  7.50    7.02    7.24    7.34        0.285577    
2020-06-08  7.74    7.37    7.53    7.53        0.293750    
2020-06-09  7.44    7.05    7.22    7.24        0.307053    
2020-06-10  7.34    6.77    7.33    6.81        0.325835    
2020-06-11  6.46    6.04    6.07    6.13        0.357561

What I want it to look like:


            High    Low     Open    Close       ATR         ATR_TS
Date                                
2020-06-01  5.88    5.67    5.73    5.87        0.210000    5.135000
2020-06-02  6.00    5.83    5.96    5.90        0.207143    5.175000
2020-06-03  6.27    5.92    5.99    6.19        0.218776    5.424286
2020-06-04  6.58    6.12    6.20    6.57        0.236006    5.743980
2020-06-05  7.50    7.02    7.24    7.34        0.285577    6.340481
2020-06-08  7.74    7.37    7.53    7.53        0.293750    6.501876
2020-06-09  7.44    7.05    7.22    7.24        0.307053    6.501876
2020-06-10  7.34    6.77    7.33    6.81        0.325835    6.501876
2020-06-11  6.46    6.04    6.07    6.13        0.357561    7.381464

My pseudo-function/logic at the moment looks like this:

def atr_ts(close, atr):
    bigatr = atr*3.5
    buysell = 1 
    stop[i-1] = 0  
    
    if buysell > 0:
        stop = close - bigatr
        stop = max(stop, stop[i-1])
        if close < stop:
            stop = close + bigatr
            buysell = -1

    elif buysell < 0:
        stop = close + bigatr
        stop = min(stop, stop[i-1])
        if close > stop:
            stop = close - bigatr
            buysell = 1
        
            
    return stop

df['ATR_TS'] = df.apply(lambda col: atr_ts(col['Close'], col['ATR']), axis = 1)

So my question is, how do I index the previous stop (ATR_TS) values that are calculated by this function in order to calculate the next stop value, with the first stop value being 0? If anyone sees a better solution to this problem without using pandas.apply, do also share.

I'm new to programming in general so my apologies if clarity is lacking.

Thank you so much.

David Erickson

This won't be the final solution, as I am confused as to why the last value of ATR_TS = 7.381464, although I know how you calculated the value. I created a number of columns to visualize the "pandonic" way of doing some of the calculations using .shift() and .cumsum() for row-wise comparisons. Please take a look at the columns and my screenshot and explain how to get the last value, but in the final solution there is absolutely no need for all of these columns:

df['bigatr'] = (df['ATR'] * 3.5)
df['Stop1a'] = df['Close'] - (df['ATR'] * 3.5)
df['Stop2a'] = df.shift()['Close'] - (df.shift()['ATR'] * 3.5)
df['Stop3a'] = df[['Stop1a','Stop2a']].max(axis=1)
df['Stop1b'] = df['Close'] + (df['ATR'] * 3.5)
df['Stop2b'] = df.shift()['Close'] + (df.shift()['ATR'] * 3.5)
df['Stop3b'] = df[['Stop1b','Stop2b']].min(axis=1)
df['cuma'] = (df['Stop1a'] > df.shift()['Stop1a']).cumsum()
df['cumb'] = (df['Stop1b'] < df.shift()['Stop1b']).cumsum()
df['ATR_TSa'] = df.groupby((df['Stop1a'] > df.shift()['Stop1a']).cumsum())['Stop1a'].transform('first')
df['ATR_TSb'] = df.groupby((df['Stop1b'] < df.shift()['Stop1b']).cumsum())['Stop1b'].transform('first')
df

As you can see, the final solution is all of the ['ATR_TSa'] values circled in red and the df['ATR_TSb'] value circled in blue in the last row.

EDIT #1 - Per OP's comment, the final logic to solve the above is to add:

 df['ATR_TS'] = np.where((df['Close'] < df['ATR_TSa']), df['ATR_TSb'], df['ATR_TSa'])

Now, below here, I will provide a more concise solution:

df['Stop1a'] = df['Close'] - (df['ATR'] * 3.5)
df['Stop1b'] = df['Close'] + (df['ATR'] * 3.5)
a = df.groupby((df['Stop1a'] > df.shift()['Stop1a']).cumsum())['Stop1a'].transform('first')
b = df.groupby((df['Stop1b'] <= df.shift()['Stop1b']).cumsum())['Stop1b'].transform('first')
df['ATR_TS'] = np.where((df['Close'] < a), b, a)
df = df.drop(['Stop1a','Stop1b'], axis=1)
df


Out[1]: 
         Date  High   Low  Open  Close       ATR    ATR_TS
0  2020-06-01  5.88  5.67  5.73   5.87  0.210000  5.135000
1  2020-06-02  6.00  5.83  5.96   5.90  0.207143  5.175000
2  2020-06-03  6.27  5.92  5.99   6.19  0.218776  5.424284
3  2020-06-04  6.58  6.12  6.20   6.57  0.236006  5.743979
4  2020-06-05  7.50  7.02  7.24   7.34  0.285577  6.340480
5  2020-06-08  7.74  7.37  7.53   7.53  0.293750  6.501875
6  2020-06-09  7.44  7.05  7.22   7.24  0.307053  6.501875
7  2020-06-10  7.34  6.77  7.33   6.81  0.325835  6.501875
8  2020-06-11  6.46  6.04  6.07   6.13  0.357561  7.381463

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-12-17

Comments

0 comments

From Dev

How to apply masking while creating next row value which is based on previous row's value and another column in Python Pandas?

From Dev

Related Related

Article