I am trying to find the return of my tickers on a daily basis using :
((close - previous close)/previous close)
I created the Return column in dataframe with
DF['Return']=((DF.Close - DF.Close.shift(1))/DF.Close.shift(1)).
The thing is, my first row is correct for the ABM stock because there is no previous close, however the second row should also return NaN because there shouldn't be previous close for MMM. How do I resolve this.
Date Ticker Close Div High Low Open Splits Volume Return
0 2019-06-18 ABT 80.868233 0.0 81.033031 79.414117 79.927903 0.0 6253000.0 NaN
1 2019-06-18 MMM 160.269012 0.0 160.651364 156.268355 156.529469 0.0 3623000.0 0.981854
2 2019-06-19 ABT 81.750397 0.0 81.915195 80.509552 80.848845 0.0 4113400.0 -0.489918
3 2019-06-19 MMM 159.233871 0.0 161.154934 159.038029 160.287664 0.0 2777400.0 0.947805
4 2019-06-20 ABT 82.341743 0.0 82.710117 81.740706 82.244796 0.0 6436400.0 -0.482888
.....
My OUTPUT should be like this:
Date Ticker Close Div High Low Open Splits Volume Return
0 2019-06-18 ABT 80.868233 0.0 81.033031 79.414117 79.927903 0.0 6253000.0 NaN
1 2019-06-18 MMM 160.269012 0.0 160.651364 156.268355 156.529469 0.0 3623000.0 NaN
2 2019-06-19 ABT 81.750397 0.0 81.915195 80.509552 80.848845 0.0 4113400.0 0.010909
3 2019-06-19 MMM 159.233871 0.0 161.154934 159.038029 160.287664 0.0 2777400.0 -0.00646
4 2019-06-20 ABT 82.341743 0.0 82.710117 81.740706 82.244796 0.0 6436400.0 0.007234
.....
You need to first use groupby
and then use shift
like this:
DF['Return']=((DF.Close - DF.groupby('Ticker').Close.shift(1))/DF.groupby('Ticker').Close.shift(1))
I don't know all the code you have previously, so let me do a full example just in case:
import pandas as pd
df = pd.DataFrame({'A': [1, 2, 1, 2], 'B': [4, 5, 6, 7]})
df['C'] = (df['B'] - df.groupby('A')['B'].shift())/df.groupby('A')['B'].shift()