How can I create a python function to calculate spreads across panel data?

user_12207812 0
Viewed 682

I have a set of panel data that contains different bonds and the bond yields yields across multiple days.

I would like to create a function which, for a given day, takes two bonds and calculates the spread, and then does this for every bond pair, for every day.

The resulting dataframe will have the date, a column indicating which two bonds and then the yield spread.

Original dataframe:

data1 = {'Date':['26/10/2019', '26/10/2019', '26/10/2019', '26/10/2019', '25/10/2019', '25/10/2019', '25/10/2019', 
                '25/10/2019'],
        'Bond':['A', 'B', 'C', 'D', 'A', 'B', 'C', 'D'],
        'Yield':[1.1, 1.11, 1.2, 1.3, 1, 1.1, 1.25, 1.29]}

df1 = pd.DataFrame(data=data1)

Resulting dataframe:

data2 = {'Date':['26/10/2019', '26/10/2019', '26/10/2019', '26/10/2019','26/10/2019','26/10/2019', '25/10/2019', 
                '25/10/2019', '25/10/2019', '25/10/2019','25/10/2019','25/10/2019'],
        'Bond':['BA', 'CB', 'CA', 'DC', 'DB', 'DA', 'BA', 'CB', 'CA', 'DC', 'DB', 'DA'],
        'Yield':[0.01, 0.09, 0.1, 0.1, 0.19, 0.2, 0.1, 0.15, 0.25, 0.04, 0.19, 0.29]}

df2 = pd.DataFrame(data=data2)

Thank you in advance!

pandas numpy panel python
1 Answers

First we merge on date to get all the combinations of the bonds next to each other.

Then we drop duplicates while sorting (np.sort) on the bonds, because AB is the same as BA and CD is the same as DC.

Then finally we substract the yields from each other and take the absolute:

mrg = df1.merge(df1, on='Date').query('Bond_x != Bond_y')
mrg.loc[:, ['Bond_x', 'Bond_y']] = np.sort(mrg.loc[:, ['Bond_x', 'Bond_y']])
mrg = mrg.drop_duplicates(subset=['Date','Bond_x', 'Bond_y'])

df = pd.DataFrame({'Date': mrg['Date'],
                   'Bond': mrg['Bond_x'].str.cat(mrg['Bond_y']),
                   'Yield': mrg['Yield_x'].sub(mrg['Yield_y']).abs()}).reset_index(drop=True)

          Date Bond  Yield
0   26/10/2019   AB   0.01
1   26/10/2019   AC   0.10
2   26/10/2019   AD   0.20
3   26/10/2019   BC   0.09
4   26/10/2019   BD   0.19
5   26/10/2019   CD   0.10
6   25/10/2019   AB   0.10
7   25/10/2019   AC   0.25
8   25/10/2019   AD   0.29
9   25/10/2019   BC   0.15
10  25/10/2019   BD   0.19
11  25/10/2019   CD   0.04
user_90812670
Related