CODEWITHSUNDEEP
pythonpandaspandas-groupbydata-analysis
Ayush Anand
Ayush Anand

Reputation: 21

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

enter image description here

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

Upvotes: 0

Views: 136

Answers (1)

davidbilla
davidbilla

Reputation: 2222

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!!

Upvotes: 1

Related Questions