I would like to compute the excess returns of dataframe of stock returns, where excess returns are defined as the difference between the stock returns and the market, where market it represented by the ticker SPY. Below is min-example
dict0 = {'date': [1/1/2020,1/1/2020,1/1/2020,1/2/2020,1/2/2020,1/2/2020,,1/3/2020,1/3/2020,1/3/2020], 'ticker': ['SPY','AAPL','SPY','AAPL', 'MSFT','SPY','AAPL','MSFT'], 'returns': [1,2,3,4,5,6,7,8,9]}
df = pd.DataFrame(dict0)###
df.set_index(['date','ticker'])
The goal is to create excess returns (that is, everything in excess of the market, which is equal to the ticker SPY)
df['excess_returns']= [0,1,2,0,1,2,0,1,2]
The last line of code where you do set_index, you should assign the dataframe back to itself or do it inplace. Remaining you can do it as follows:
def func(row):
date, asset = row.name
return df.loc[(date, asset), 'returns'] - df.loc[(date, 'SPY'), 'returns']
dict0 = {'date': ['1/1/2020', '1/1/2020', '1/1/2020', '1/2/2020', '1/2/2020',
'1/2/2020', '1/3/2020', '1/3/2020', '1/3/2020'],
'ticker': ['SPY', 'AAPL', 'MSFT', 'SPY', 'AAPL', 'MSFT', 'SPY', 'AAPL', 'MSFT'],
'returns': [1, 2, 3, 4, 5, 6, 7, 8, 9]}
df = pd.DataFrame(dict0) ###
df = df.set_index(['date', 'ticker'])
df['excess_returns'] = df.apply(func, axis=1)