Home Forward Fill NA values with discount rate conditional on sign of previous value

Questions

Forward Fill NA values with discount rate conditional on sign of previous value

December 6, 2021

I try to forward fill NaN values in a DataFrame with a discount rate conditional on the sign of the previous value. So far, I was able to include a discount rate in the forward fill of the NaN values. Here would be a simple example dataset df1:

df1 = pd.DataFrame({
                    'Column1':[1.255, 'NaN', 3.558, 'NaN'], 
                    'Column2':[5, 6, 7, 8], 
                    'Column3':[-9, -10, 'NaN', 'NaN'], 
                    'Column4':[13, 15, 'NaN', 'NaN']
                    })
df1 = df1.replace('NaN',np.nan)

df1: 
    Column1 Column2 Column3 Column4
0   1.255   5       -9.0    13.0
1   NaN     6       -10.0   15.0
2   3.558   7       NaN     NaN
3   NaN     8       NaN     NaN

I included a discount rate of 0.9 for the forward fill.

rate = 0.9
groups = df1.notna().cumsum()
exp = df1.apply(lambda col: col.isna().groupby(groups[col.name]).cumsum())
df2 = df1.ffill().mul(rate ** exp)

df2:

    Column1 Column2 Column3 Column4
0   1.2550  5.0     -9.0    13.00
1   1.1295  6.0     -10.0   15.00
2   3.5580  7.0     -9.0    13.50
3   3.2022  8.0     -8.1    12.15

How would I need to adjust the code to have a discount rate of 0.9 for positive numbers (as in the example) but a discount rate of 0.7 for negative numbers?

Thanks a lot!

>Solution :

You could use a mask on the negative and positive values, something like this should work:

groups = df1.notna().cumsum()
exp = df1.apply(lambda col: col.isna().groupby(groups[col.name]).cumsum())
df2 = df1.ffill()
rate_p = 0.9
rate_n = 0.7
mask_p = df2 > 0
mask_n = df2 < 0
df2 *= (rate_p ** exp) * mask_p + (rate_n ** exp) * mask_n