How to Fill Numeric missing Values In Pandas Based On Another Column

Question

Here I need to Impute Numeric Columns in pandas

Sample Data:

Age   Time_of_service

42       4

24       5

nan      27

26       4

31       5

54       21

21       2

Nan      32

45       18

19       0

65      35

nan       3

Here Both Age & Time_of_Service columns are highly correlated. Based on below Conditions I need to impute the missing Values

Time_of_Service >30
age = 60

Time_of_Service in (20,30)
age = 45

Time_of_Service in (10,20)
age = 35

Time_of_Service in (0,10)
age = 25

How to impute missing values based on above conditions using Python ?

Answer 1

Use cut for binning, then convert output to integers and replace missing values in Age column by Series.fillna:

bins = [0,10,20,30,np.inf]
labels = [25,35,45,60]
new = pd.cut(df['Time_of_service'], bins=bins, labels=labels, include_lowest=True)
df['Age'] = df['Age'].fillna(new.astype(int))
print (df)

     Age  Time_of_service
0   42.0                4
1   24.0                5
2   45.0               27
3   26.0                4
4   31.0                5
5   54.0               21
6   21.0                2
7   60.0               32
8   45.0               18
9   19.0                0
10  65.0               35
11  25.0                3