I have two DataFrames as follows:
Ticker1 Ticker2 Ticker3 2018-01-01 NaN NaN 0.39 2018-01-02 0.8 0.73 NaN 2018-01-04 NaN NaN NaN ... ... ... ...
Spot price (weekly):
Ticker1 Ticker2 Ticker3 2018-01-01 16.95 8.54 21.05 2018-01-08 16.80 9.03 20.56 2018-01-15 16.86 9.52 19.85 ... ... ... ...
I would like to compute the weekly returns of these stocks (10Y+ historical) while taking into account the dividends. I would have just added the two dataframes and logged the returns but my dates don't line up exactly.
My current solution is to loop through the
DateTimeIndex of the spot price dataframe and find the one closest to it in the dividend dataframe using
.loc, and add it if it's not null. While it works, it's very slow even when looping though the underlying numpy arrays instead of the actual dataframe objects.
Hence, my question: is there an efficient way to get the closest last known dividend and add it to my spot price dataframe before computing the returns?
I found a pandas method I didn't know of called pandas.merge_asof, and although it's very slow it produces the expected result in pure Python and improves readability of the code base.