python - Fill multi-index Pandas DataFrame with interpolation

Question

I would like to bfill and ffill a multi-index DataFrame containing NaNs (in this case the ImpVol field) using the interpolate method. A section of the DataFrame might look like this:

Expiration  OptionType  Strike    ImpVol
2014-12-26  call        140.0          NaN
                        145.0          NaN
                        147.0          NaN
                        149.0          NaN
                        150.0          NaN
                        152.5          NaN
                        155.0     0.233631
                        157.5     0.206149
                        160.0     0.149118
                        162.5     0.110867
                        165.0     0.110047
                        167.5          NaN
                        170.0          NaN
                        172.5          NaN
                        175.0          NaN
                        177.5          NaN
                        180.0          NaN
                        187.5          NaN
                        192.5          NaN
            put         132.0          NaN
                        135.0          NaN
                        140.0          NaN
                        141.0          NaN
                        142.0     0.541311
                        143.0          NaN
                        144.0     0.546672
                        145.0     0.504691
                        146.0     0.485586
                        147.0     0.426898
                        148.0     0.418084
                        149.0     0.405254
                        150.0     0.372353
                        152.5     0.311049
                        155.0     0.246892
                        157.5     0.187426
                        160.0     0.132475
                        162.5     0.098377
                        165.0          NaN
                        167.5     0.249519
                        170.0     0.270546
                        180.0          NaN
                        182.5     0.634539
                        185.0     0.656332
                        187.5     0.711593
2015-01-02  call        145.0          NaN
                        146.0          NaN
                        149.0          NaN
                        150.0          NaN
                        152.5          NaN
                        155.0     0.213742
                        157.5     0.205705
                        160.0     0.160824
                        162.5     0.143180
                        165.0     0.129292
                        167.5     0.127415
                        170.0     0.148275
                        172.5          NaN
                        175.0          NaN
                        180.0          NaN
                        182.5          NaN
                        195.0          NaN
            put         135.0     0.493639
                        140.0     0.463828
                        141.0     0.459619
                        142.0     0.442729
                        143.0     0.431823
                        145.0     0.391141
                        147.0     0.313090
                        148.0     0.310796
                        149.0     0.296146
                        150.0     0.280965
                        152.5     0.240727
                        155.0     0.203776
                        157.5     0.175431
                        160.0     0.143198
                        162.5     0.121621
                        165.0     0.105060
                        167.5     0.160085
                        170.0          NaN

For those of you not familiar with the domain, I'm interpolating missing (or bad) implied option volatilities. These need to be interpolated across strike by expiration and option type combination and cannot be interpolated across the entire population of options. For example, I have to interpolate across the 2014-12-26 call options separately than the 2014-12-26 put options.

I was previously selecting a slice of the values to interpolate with something like this:

optype = 'call'
expiry = '2014-12-26'

s = df['ImpVol'][expiry][optype].interpolate().ffill().bfill()

but the frame can be quite large and I'd like to avoid having to loop through each of the indexes. If I use the interpolate method to fill without selecting a slice (i.e. across the entire frame), interpolate will interpolate across all of the sub indexes which is what I do not want. For example:

print df['ImpVol'].interpolate().ffill().bfill()

Expiration  OptionType  Strike    ImpVol
2014-12-26  call        140.0     0.233631
                        145.0     0.233631
                        147.0     0.233631
                        149.0     0.233631
                        150.0     0.233631
                        152.5     0.233631
                        155.0     0.233631
                        157.5     0.206149
                        160.0     0.149118
                        162.5     0.110867
                        165.0     0.110047
                        167.5     0.143222
                        170.0     0.176396
                        172.5     0.209570
                        175.0     0.242744
                        177.5     0.275918
                        180.0     0.309092
                        187.5     0.342267
                        192.5     0.375441 <-- interpolates from the 2014-12-26 call...
            put         132.0     0.408615 <-- ... to the 2014-12-26 put, which is bad
                        135.0     0.441789
                        140.0     0.474963
                        141.0     0.508137
                        142.0     0.541311
                        143.0     0.543992
                        144.0     0.546672
                        145.0     0.504691
                        146.0     0.485586
                        147.0     0.426898
                        148.0     0.418084
                        149.0     0.405254
                        150.0     0.372353
                        152.5     0.311049
                        155.0     0.246892
                        157.5     0.187426
                        160.0     0.132475
                        162.5     0.098377
                        165.0     0.173948
                        167.5     0.249519
                        170.0     0.270546
                        180.0     0.452542
                        182.5     0.634539
                        185.0     0.656332
                        187.5     0.711593

The question is then, how can I fill each subsection of the multi index data frame based on the indexes?

Answer 1

I'd try to unstack the data frame at the OptionType level of index.

df.unstack(level=1)

This way you should obtain a single index dataframe which will have both call and put categories moved to columns. Maybe it's not the most elegant way of solving the problem, but it should work things out, not letting the put/call strikes to overlap.

If multi index df is the most desirable one for further computations, you can restore the original format using stack method.