CODEWITHSUNDEEP
pythonpandasgroup-byapply
user3451026
user3451026

Reputation: 61

How to use pandas groupby and shift together

I'm sure I need to use some sort of apply functionality but I'm struggling to create one that accomplishes this. I have a dataframe with stock tickers and monthly returns. I need to calculate the forward 3 month returns. It's structured such that for every month, there will be 500 (made that number up) rows with all the stocks and the returns for that month. I've been trying something like this but it isn't working. 

mr['Quarterly_Returns'] = mr.groupby('ticker')['monthly_returns'].apply(mr['monthly_returns']+mr['monthly_returns'].shift(-1)+mr['monthly_returns'].shift(-2))

And advice?

Upvotes: 2

Views: 1646

Answers (2)

Victor Chubukov
Victor Chubukov

Reputation: 1375

You might also consider using the rolling functions.

mr.groupby('ticker')['monthly_returns'].rolling(3).sum()

A more complete example:

df=pd.concat([pd.DataFrame(index=pd.date_range('1/1/2016','12/31/2016',freq='M'),data={'ticker':x,'return':np.random.rand(12)}) for x in list('ABCD')])
df.groupby('ticker')['return'].rolling(3).sum().unstack('ticker')

ticker  A   B   C   D
2016-01-31  NaN     NaN     NaN     NaN
2016-02-29  NaN     NaN     NaN     NaN
2016-03-31  2.062552    1.508062    1.317836    1.051874
2016-04-30  1.727587    1.856383    1.308263    1.113360
2016-05-31  1.602858    2.112790    1.533763    1.039221
2016-06-30  1.716985    2.403718    1.850741    1.726469
2016-07-31  1.828597    1.809054    1.543079    1.569896
2016-08-31  2.003484    1.531877    1.376907    1.852235
2016-09-30  1.854642    1.319289    1.438446    0.946304
2016-10-31  1.308001    1.718987    1.764252    1.157938
2016-11-30  0.962660    2.255580    1.489076    0.493370
2016-12-31  0.949810    1.753511    1.321650    1.377429

Upvotes: 1

jezrael
jezrael

Reputation: 862441

You need lambda x and in apply instead column names use x, because works only with column monthly_returns:

mr['Quarterly_Returns'] = mr.groupby('ticker')['monthly_returns']
                            .apply(lambda x: x+x.shift(-1)+x.shift(-2))

Upvotes: 0

Related Questions