Create a column with maximum value (A-Z)-CodePudding

I have below dataframe, I want to the create a new column that shows the highest score for the ID. Score is from A-Z. A = low score, Z = high score.

ID	Score
001	A
001	B
001	C
002	A
002	A
A003	E
A003	G
A003	G

Output

ID	Score	New Column
001	A	C
001	B	C
001	C	C
002	A	A
002	A	A
A003	E	G
A003	G	G
A003	G	G

Is there any way to accomplish this easily?

CodePudding user response：

Group by ID and transform by max:

df['New Column'] = df.groupby('ID').transform('max')

Output:

>>> df
     ID Score New Column
0   001     A          C
1   001     B          C
2   001     C          C
3   002     A          A
4   002     A          A
5  A003     E          G
6  A003     G          G
7  A003     G          G