CODEWITHSUNDEEP
pythonpandas
Mohamed Thasin ah
Mohamed Thasin ah

Reputation: 11192

How to fill NaN between two values?

I have a df which looks like below. I want to fillNA with some value between two values. 

   col1  col2  col3  col4  col5  col6  col7  col8
0   NaN    12  12.0   4.0   NaN   NaN   NaN   NaN
1  54.0    54  32.0  11.0  21.0   NaN   NaN   NaN
2   3.0    34  34.0   NaN   NaN  43.0   NaN   NaN
3  34.0    34   NaN   NaN  34.0  34.0  34.0  34.0
4   NaN    34  34.0   NaN  34.0  34.0  34.0  34.0

For Example, I dont want to fillna in first and second row, because NaN doesn't occur between values. But I want to fillna in third row at col4 and col5. because these two columns contains NaN between two values (col3 and col6).

How to do this,

Expected Output:

   col1  col2  col3  col4  col5  col6  col7  col8
0   NaN    12  12.0   4.0   NaN   NaN   NaN   NaN
1  54.0    54  32.0  11.0  21.0   NaN   NaN   NaN
2   3.0    34  34.0  -100  -100  43.0   NaN   NaN
3  34.0    34  -100  -100  34.0  34.0  34.0  34.0
4   NaN    34  34.0  -100  34.0  34.0  34.0  34.0

For this problem I can't simply use fillna, because it will fill completely, similarly I can't use ffill or bfill, because it violate at leading or trailing values. I'm clueless at this stage. any help would be appreciable.

Note: After search related to this I'm raising this question. I don't find any duplicates related to this. If you find feel free to mark it as duplicate.

Upvotes: 2

Views: 665

Answers (1)

jezrael
jezrael

Reputation: 862581

I think you need get boolean mask where are missing values without first and last one rows by 2 methods - forward fill and back fill missing values and check non missing or create cumulative sum with comparing >0:

m = df.ffill(axis=1).notnull() & df.bfill(axis=1).notnull()
#alternative mask
a = df.notnull()
m = a.cumsum(axis=1).gt(0) & a.iloc[:, ::-1].cumsum(axis=1).gt(0)
df = df.mask(m, df.fillna(-100))
print (df)
   col1  col2   col3   col4   col5  col6  col7  col8
0   NaN    12   12.0    4.0    NaN   NaN   NaN   NaN
1  54.0    54   32.0   11.0   21.0   NaN   NaN   NaN
2   3.0    34   34.0 -100.0 -100.0  43.0   NaN   NaN
3  34.0    34 -100.0 -100.0   34.0  34.0  34.0  34.0
4   NaN    34   34.0 -100.0   34.0  34.0  34.0  34.0

Detail:

print (m)
    col1  col2  col3  col4   col5   col6   col7   col8
0  False  True  True  True  False  False  False  False
1   True  True  True  True   True  False  False  False
2   True  True  True  True   True   True  False  False
3   True  True  True  True   True   True   True   True
4  False  True  True  True   True   True   True   True

Upvotes: 4

Related Questions