Search code examples
pythonpandascalculated-columnsstock

How to find the return of stock Tickers in rows of dataframe?


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

.....


Solution

  • 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()