Calculate Average Total Range from given Total Range in python pandas?

Question

In this sheet I need to add a column Av. TR and I want to calculate Av. TR. For Av. TR calculation:-

First 10 days are reference.

So for 10th day Av. TR would be:-

Av. TR= average of first 10 days TR and for subsequent days av. TR would be

FORMULA: Av. TR = [(previous day ATR * 9) + (that day TR)]/10.

I have to group by Av. TR according to the "SYMBOL" also. How to do this? i tried rolling function in pandas but couldn't achieve the result.

 INSTRUMENTS  SYMBOL           TIMESTAMP   TR 
 FUTIDX       BANKNIFTY        6/1/2020    729.8
 FUTIDX       BANKNIFTY        6/2/2020    834
 FUTIDX       BANKNIFTY        6/3/2020    1145.2
 FUTIDX       BANKNIFTY        6/4/2020    846.7 
 FUTIDX       BANKNIFTY        6/5/2020    812.5
 FUTIDX       BANKNIFTY        6/8/2020    904.6
 FUTIDX       BANKNIFTY        6/9/2020    1014
 FUTIDX       BANKNIFTY        6/10/2020   660
 FUTIDX       BANKNIFTY        6/11/2020   796
 FUTIDX       BANKNIFTY        6/12/2020   1173
 FUTIDX       BANKNIFTY        6/15/2020   969
 FUTIDX       BANKNIFTY        6/16/2020   271
 FUTIDX       NIFTY            6/1/2020    207
 FUTIDX       NIFTY            6/2/2020    230
 FUTIDX       NIFTY            6/3/2020    177.7
:             :                :            :
:             :                :            :
:             :                :            :

I want to add a column Av. TR. For calculating Av. TR I mentioned formula above and I want it to be grouped by SYMBOL.

So the new column ATR would be like this:-

           ATR   
row1       NAN
row2       NAN
row3       NAN
row4       NAN
row5       NAN
row6       NAN
row7       NAN
row8       NAN
row9       NAN
row10      (Average of first 10 rows of TR)
row11      (Refer FORMULA above)
row12      (Refer FORMULA above) 
(so on)    (so on)

It has to grouped by SYMBOL

can you please copy and paste the data instead of a screenshot? See: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — David Erickson, Jun 16 '20 at 09:04
you can just copy some sample rows into stack overflow. Use the { } feature and/or add 4 spaces to beginning of each row when you paste it in. Please check out the link for more details on how to do this. — David Erickson, Jun 16 '20 at 09:15
@AyushAnand Can you add expected result for the dataset you have? What will be the Av.TR for the first 9 rows - should they be blank or the avg of previous rows and the current row? — davidbilla, Jun 16 '20 at 10:40
No first 9 rows will be blank. I am using first 9 rows of every SYMBOL as reference. — Ayush Anand, Jun 16 '20 at 11:04

davidbilla · Answer 1 · 2020-06-16T12:01:00.627

1

You should be able to apply rolling transform on each group. Something like this should be able to achieve this.

df['Av.TR'] = df.groupby('SYMBOL')['TR'].transform(lambda x: x.rolling(10, 1).mean())

If you want the first 10 rows blank then

df['Av.TR'] = df.groupby('SYMBOL')['TR'].transform(lambda x: x.rolling(10).mean())

I am not so sure, if this is how you want it. but combining the above and then applying the formula using the previous row value should get there.

df['Av.TR'] = df.groupby('SYMBOL')['TR'].transform(lambda x: x.rolling(10).mean())
df['Av.TR'] = np.where(df.shift(1)['Av.TR'].isna(), np.NaN,
                     (df.shift(1)['Av.TR'] * 9 + df['TR']) / 10)

Maybe there is a better way to do it!!

edited Jun 16 '20 at 12:01

answered Jun 16 '20 at 10:37

davidbilla

2,120
1
15
26

This code is doing average on every rows. I want first 9 rows of every SYMBOL to be blank and I want av. on only 10th row and after 10th row there is a different formula for ATR calculation which I mentioned in the question. – Ayush Anand Jun 16 '20 at 11:08
1

@AyushAnand If you want the first 10 rows blank then you can remove the `min_period=1` parameter of `.rolling()` function. What do you mean by the ATR calculation formula - where do you get the 'previous day ATR'?? Is 'ATR' same as the 'Av.TR'? – davidbilla Jun 16 '20 at 11:17
ya ATR is same as Av. TR and after 10th row a different formula is there. so after every SYMBOL's 10th row we have to apply that formula. Av. TR = [(previous day ATR * 9) + (that day TR)]/10. – Ayush Anand Jun 16 '20 at 11:26

Calculate Average Total Range from given Total Range in python pandas?

1 Answers1