[FIXED] How to replace NaN in pandas dataframe with calculated value from other columns

Issue

I have below dataframe where I added last row as latest data.

df.tail()

            Open    High    Low     Close   %K      %D
Date                        
2022-06-22  23.71   25.45   23.55   24.29   21.74   18.01
2022-06-23  24.94   25.57   24.17   25.33   31.30   25.15
2022-06-24  26.11   28.04   26.05   27.98   51.99   35.01
2022-06-27  28.35   28.50   27.00   27.32   47.54   43.61
2022-06-28  27.46   28.21   24.76   24.83   NaN     NaN

I want to fill NaN values with calculated values with data from other columns.

I can do it with below code,

df['14-high'] = df['High'].rolling(14).max()
df['14-low'] = df['Low'].rolling(14).min()
df['%K'] = (df['Close'] - df['Low'].rolling(14).min())*100/(df['14-high'] - df['14-low'])
df['%D'] = df['%K'].rolling(3).mean()

df.drop(columns=['14-high', '14-low'], inplace=True)

Is there a better way to do it without re calculating columns %K and %D? i.e calculate only NaN cells instead of the whole column.

Solution

One way to do this would be to use the pandas fillna() method.
You will still need the first calculations:

df['14-high'] = df['High'].rolling(14).max()
df['14-low'] = df['Low'].rolling(14).min()

But then you can only update the NaN-values in the %K and %D columns:

df['%K'].fillna((df['Close'] - df['Low'].rolling(14).min())*100/(df['14-high'] - df['14-low']), inplace=True)
df['%D'].fillna(df['%K'].rolling(3).mean(), inplace=True)

Hope I could help!

Answered By - Quantum