Replace NaN with existing value of the group

user_7256873 0
Viewed 52
    Name     Value
0   AA        33
1   AA        24
2   BB        23
3   BB        NaN
4   CC        NaN
5   CC        23
6   CC        45

How can I replace these NaN with existing values by looking at column Name? For CC I would like to get the max (but if it is too convoluted, then I am fine with either 23 or 45). The expected output:

    Name     Value
0   AA        33
1   AA        24
2   BB        23
3   BB        23
4   CC        45
5   CC        23
6   CC        45

Thanks!

pandas nan python-3.x replace
2 Answers

You can groupby and transform with max then fillna:

df['Value'] = df['Value'].fillna(df.groupby("Name")['Value'].transform('max'))
print(df)

  Name  Value
0   AA   33.0
1   AA   24.0
2   BB   23.0
3   BB   23.0
4   CC   45.0
5   CC   23.0
6   CC   45.0
user_98406370

You can also use lambda with transform

df["Value"] = df.groupby('Name').transform(lambda x:x.fillna(x.max()))
df
  Name  Value
0   AA   33.0
1   AA   24.0
2   BB   23.0
3   BB   23.0
4   CC   45.0
5   CC   23.0
6   CC   45.0
user_60756990
Related