How can i make a calculation in pandas on a specific column if the dataframe is multi-index?

Question

So I have a multi index df which is organized by date (first level) and equity (second level) and then for each equity, there is a price for that equity at that date. The df looks like this:

]1

Now the dates go from 2019 all the way to 2020.

the calculation I want to make is on the closing_price column, and the basic calculation is as follows: (todays closing price / closing price 5 days ago) * 100

what would be a computationally efficient way of making this calculation? ive thought about using a for loop but there are over 200,000 entries so I know that would take forever.

thanks for any help you may provide.

Answer 1

With index levels called 'date' and 'equity':

import datetime as dt 
import functools as ft
import numpy as np

the_dates = [dt.date.today(),dt.date.today() - dt.timedelta(days=5)]
data[data.index.isin(the_dates,level='date')].\
     groupby(level='equity')['closing_price'].\
     apply(lambda x: ft.reduce(np.divide,x) * 100) 

You may need to re-sort your DataFrame if the operands need to be reversed.