How to fill NaN with aggregate value of closest point by continous varuiable?

user_13184183 0
Viewed 25

Let's say I have the following dataset

     A    B
0    1    1
1    NaN  3
2    5    2
3    7    4
4    NaN  3
5    3    3

I want to fill NaNs with some function, for example mean, of values A of nearest neighbors by B by given threshold. For example, if threshold is 1, then

for object 0 neighbors are object 2 (because 2 - 1 <= 1)

for object 1 neighbors are objects 2, 3, 4, 5

and so on so the result would be

     A    B
0    1    1
1    5    3   # 5 = (5 + 7 + 3) / 3
2    5    2
3    7    4
4    5    3   # 5 = (5 + 7 + 3) / 3
5    3    3

If NaNs are faced in computations, they are neglected. How can I do that?

pandas python
1 Answers
THRE = 1

b = df.B.to_numpy()
isnan_a = df.A.isna()

# outer difference helps in whether neighbor or not in B
neigh_mask = np.abs(np.subtract.outer(b, b)) <= THRE

# but interested in those whose A value is NaN
neigh_mask_for_nans = neigh_mask[isnan_a]

# get neighbors to take mean to via A times the neighbor mask of True/False's
neighs_for_nan_a = df.A.to_numpy()[None] * neigh_mask_for_nans

# False's gave 0 above in mul but we want them NaN to discard in mean
neighs_for_nan_a[~neigh_mask_for_nans] = np.nan

# take the mean ignoring NaNs and fill
df.loc[isnan_a, "A"] = np.nanmean(neighs_for_nan_a, axis=1)

to get

     A  B
0  1.0  1
1  5.0  3
2  5.0  2
3  7.0  4
4  5.0  3
5  3.0  3
user_93321870
Related