Steven G Steven G - 6 months ago 10
Python Question

multiply 2 subset of a df based on 2 other columns python pandas

I have a df such has:

df
Out[177]:
IV maturity strike
date
2000-12-29 0.435 0.083 0.600
2000-12-29 0.390 0.083 0.700
2000-12-29 0.331 0.083 0.800
2000-12-29 0.283 0.083 0.900
2000-12-29 0.391 0.250 0.600
2000-12-29 0.349 0.250 0.700
2000-12-29 0.304 0.250 0.800
2000-12-29 0.268 0.250 0.900


I am trying to create 4 new rows where maturity = 0 and IV is equal to the product of IV where maturity==0.083 IV and maturity==0.25 IV and'strike' match together such has ( example: IV for strike 0.6 and and maturity 0.083 is 0.435 and IV for maturity 0.25 for strike 0.6 us 0.391, so 0.391*0.435

Out[177]:
IV maturity strike
date
2000-12-29 0.435 0.083 0.600
2000-12-29 0.390 0.083 0.700
2000-12-29 0.331 0.083 0.800
2000-12-29 0.283 0.083 0.900
2000-12-29 0.391 0.250 0.600
2000-12-29 0.349 0.250 0.700
2000-12-29 0.304 0.250 0.800
2000-12-29 0.268 0.250 0.900
2000-12-29 0.170 0.000 0.600
2000-12-29 0.136 0.000 0.700
2000-12-29 0.101 0.000 0.800
2000-12-29 0.758 0.000 0.900


thanks for you help!

Answer

You can use pivot, then multiple first and second column selected by iloc to new column 0. Last unstack with reset_index:

df1 = (df.pivot(index='strike', columns='maturity', values='IV'))

df1.loc[:,0] = df1.iloc[:,0] * df1.iloc[:,1]

print (df1.unstack().reset_index(name='IV'))
    maturity  strike        IV
0      0.083     0.6  0.435000
1      0.083     0.7  0.390000
2      0.083     0.8  0.331000
3      0.083     0.9  0.283000
4      0.250     0.6  0.391000
5      0.250     0.7  0.349000
6      0.250     0.8  0.304000
7      0.250     0.9  0.268000
8      0.000     0.6  0.170085
9      0.000     0.7  0.136110
10     0.000     0.8  0.100624
11     0.000     0.9  0.075844