Pandas dynamically replace nan values

Question

I have a DataFrame that looks like this:

df = pd.DataFrame({'a':[1,2,np.nan,1,np.nan,np.nan,4,2,3,np.nan], 
    'b':[4,2,3,np.nan,np.nan,1,5,np.nan,5,8]
})

   a    b
0  1.0  4.0
1  2.0  2.0
2  NaN  3.0
3  1.0  NaN
4  NaN  NaN
5  NaN  1.0
6  4.0  5.0
7  2.0  NaN
8  3.0  5.0
9  NaN  8.0

I want to dynamically replace the nan values. I have tried doing (df.ffill()+df.bfill())/2 but that does not yield the desired output, as it casts the fill value to the whole column at once, rather then dynamically. I have tried with interpolate, but it doesn't work well for non linear data.

I have seen this answer but did not fully understand it and not sure if it would work.

Update on the computation of the values
I want every nan value to be the mean of the previous and next non nan value. In case there are more than 1 nan value in sequence, I want to replace one at a time and then compute the mean e.g., in case there is 1, np.nan, np.nan, 4, I first want the mean of 1 and 4 (2.5) for the first nan value - obtaining 1,2.5,np.nan,4 - and then the second nan will be the mean of 2.5 and 4, getting to 1,2.5,3.25,4

The desired output is

    a    b
0  1.00  4.0
1  2.00  2.0
2  1.50  3.0
3  1.00  2.0
4  2.50  1.5
5  3.25  1.0
6  4.00  5.0
7  2.00  5.0
8  3.00  5.0
9  1.50  8.0

why would a/4-5 be filled with 2.5 and 3.25 rather than 2 and 3? please break down the computations — mozway, May 30 '22 at 15:34

score 2 · Accepted Answer · answered May 30 '22 at 21:50

Inspired by the @ye olde noobe answer (thanks to him!):

I've optimized it to make it ≃ 100x faster (times comparison below):

def custom_fillna(s:pd.Series):
  for i in range(len(s)):
    if pd.isna(s[i]):
      last_valid_number = (s[s[:i].last_valid_index()] if s[:i].last_valid_index() is not None else 0)
      next_valid_numer = (s[s[i:].first_valid_index()] if s[i:].first_valid_index() is not None else 0)
      s[i] = (last_valid_number+next_valid_numer)/2

custom_fillna(df['a'])
df

Times comparison:

score 1 · Answer 2 · answered May 30 '22 at 16:51

Maybe not the most optimized, but it works (note: from your example, I assume that if there is no valid value before or after a NaN, like the last row on column a, 0 is used as a replacement):

import pandas as pd

def fill_dynamically(s: pd.Series):
    for i in range(len(s)):
        s[i] = (
            (0 if s[i:].first_valid_index() is None else s[i:][s[i:].first_valid_index()]) +
            (0 if s[:i+1].last_valid_index() is None else s[:i+1][s[:i+1].last_valid_index()])
        ) / 2

Use like this for the full dataframe:

df = pd.DataFrame({'a':[1,2,np.nan,1,np.nan,np.nan,4,2,3,np.nan], 
    'b':[4,2,3,np.nan,np.nan,1,5,np.nan,5,8]
})

df.apply(fill_dynamically)

df after applying:

      a    b
0  1.00  4.0
1  2.00  2.0
2  1.50  3.0
3  1.00  2.0
4  2.50  1.5
5  3.25  1.0
6  4.00  5.0
7  2.00  5.0
8  3.00  5.0
9  1.50  8.0

In case you would have other columns and don't want to apply that on the whole dataframe, you can of course use it on a single column, like that:

df = pd.DataFrame({'a':[1,2,np.nan,1,np.nan,np.nan,4,2,3,np.nan], 
    'b':[4,2,3,np.nan,np.nan,1,5,np.nan,5,8]
})

fill_dynamically(df['a'])

In this case, df looks like that:

      a    b
0  1.00  4.0
1  2.00  2.0
2  1.50  3.0
3  1.00  NaN
4  2.50  NaN
5  3.25  1.0
6  4.00  5.0
7  2.00  NaN
8  3.00  5.0
9  1.50  8.0

Pandas dynamically replace nan values

2 Answers2