Search code examples
pandasalphabetical

Create a column with maximum value (A-Z)

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?

Solution

  • 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