CODEWITHSUNDEEP
pythonpandasnumpy
ukanafun
ukanafun

Reputation: 155

Filling NaN on conditions

I have the following input data:

df = pd.DataFrame({"ID" : [1, 1, 1, 2, 2, 2, 2],
                  "length" : [0.7, 0.7, 0.7, 0.8, 0.6, 0.6, 0.7],
                  "height" : [7, 9, np.nan, 4, 8, np.nan, 5]})
df

    ID  length  height
0   1   0.7     7
1   1   0.7     9
2   1   0.7     np.nan
3   2   0.8     4
4   2   0.6     8
5   2   0.6     np.nan
6   2   0.7     5

I want to be able to fill the NaN if a group of "ID" all have the same "length", fill with the maximum "height" in that group of "ID", else fill with the "height" that correspond to the maximum length in that group.

Required Output:

    ID  length  height
0   1   0.7     7
1   1   0.7     9
2   1   0.7     9
3   2   0.8     4
4   2   0.6     8
5   2   0.6     4
6   2   0.7     5

Thanks.

Upvotes: 4

Views: 38

Answers (1)

BENY
BENY

Reputation: 323236

You could try with sort_value then we use groupby find the last

#last will find the last not NaN value

df.height.fillna(df.sort_values(['length','height']).groupby(['ID'])['height'].transform('last'),inplace=True)
df
Out[296]: 
   ID  length  height
0   1     0.7     7.0
1   1     0.7     9.0
2   1     0.7     9.0
3   2     0.8     4.0
4   2     0.6     8.0
5   2     0.6     4.0
6   2     0.7     5.0

Upvotes: 2

Related Questions