Appreciate any help on this. Let's say I have the following df with two columns:

col1 col2 
NaN  NaN
11   100  
12   110
15   115
NaN  NaN
NaN  NaN
NaN  NaN
9    142
12   144
NaN  NaN
NaN  NaN
NaN  NaN
6    155
9    156
7    161
NaN  NaN
NaN  NaN

I'd like to forward fill and replace the Nan values with the median value of the preceding values. For example, the median of 11,12,15 in 'col1' is 12, therefore I need the Nan values to be filled with 12 until I get to the next non-Nan values in the column and continue iterating the same. See below the expected df:

col1 col2 
NaN   NaN
11    100  
12    110
15    115
12    110
12    110
12    110
9     142
12    144
10.5  143
10.5  143
10.5  143
6     155
9     156
7     161
7     156
7     156

CodePudding user response：

Try:

m1 = (df.col1.isna() != df.col1.isna().shift(1)).cumsum()
m2 = (df.col2.isna() != df.col2.isna().shift(1)).cumsum()

df["col1"] = df["col1"].fillna(
    df.groupby(m1)["col1"].transform("median").ffill()
)

df["col2"] = df["col2"].fillna(
    df.groupby(m2)["col2"].transform("median").ffill()
)

print(df)

Prints:

    col1   col2
0    NaN    NaN
1   11.0  100.0
2   12.0  110.0
3   15.0  115.0
4   12.0  110.0
5   12.0  110.0
6   12.0  110.0
7    9.0  142.0
8   12.0  144.0
9   10.5  143.0
10  10.5  143.0
11  10.5  143.0
12   6.0  155.0
13   9.0  156.0
14   7.0  161.0
15   7.0  156.0
16   7.0  156.0

CodePudding user response：

IIUC, if we fill null values like so:

• Fill with Median of last 3 non-null items.
• Fill with Median of last 2 non-null items.
• Front fill values.

We'll get what you're looking for.

out = (df.combine_first(df.rolling(4,3).median())
         .combine_first(df.rolling(3,2).median())
         .ffill())
print(out)

Output:

    col1   col2
0    NaN    NaN
1   11.0  100.0
2   12.0  110.0
3   15.0  115.0
4   12.0  110.0
5   12.0  110.0
6   12.0  110.0
7    9.0  142.0
8   12.0  144.0
9   10.5  143.0
10  10.5  143.0
11  10.5  143.0
12   6.0  155.0
13   9.0  156.0
14   7.0  161.0
15   7.0  156.0
16   7.0  156.0