I have two dataframes:
- df1 contains static data information like the ISIN and the Asset Type of an instrument;
- df2 contains time series for the same instruments and its columns are named with the ISINs
df1 is like this:
while df2 is like this:
What i need to do is the following: I need to threat in a different ways instruments of different types: if I have a Put or a Call in df1, I need to find its ISIN_ImpVol in df2 columns name and perform an operation (e.g in df1 I have IT0020779986 that is a Put, so in df2 I need to find the same isin column but containing the ending _ImpVol and performing a ln operation on it creating a new 'ISIN_ImpVol_Ret'column in df2). If in df1 I find is a stock or a Future I just have to find the same isin in df2 column names and perform a ln operation on that, creating an ISIN_Ret column.
My main issue is what function use to match the strings, any tips?
To re-create the dfs: For df1:
data = [['IT0003128367', 'Stock'], ['IT0020779986', 'Put'], ['IT0020093842', 'Future']] df1 = pd.DataFrame(data, columns=['ISIN', 'AssetType'])
For df2:
data2 = [['2023-05-12', 6.06,1.64,0.17,272.4], ['2023-05-10', 6.04,1.61,0.18,269.4,], ['2023-05-10', 6.09,1.59,0.19,268]] df2 = pd.DataFrame(data2, columns=['Date', 'IT0003128367','IT0020779986_P','IT0020779986_ImpVol','IT0020093842'])
Expected Output modifies df2:
