Finding the max value in accordance with other columns

Question

I have students names, scores in different subjects, subjects names. I want to add a column to the data frame, which contains the subject in which each student had the highest score. Here is the data:

Input data would be:

Output data (the result data frame) would be :

My try at this (didn't work obviously):

Data['Subject with highest score'] = Data.groupby(['Names','Subject'])[['Scores']].transform(lambda x: x.max())

Does this answer your question? [Get the row(s) which have the max value in groups using groupby](https://stackoverflow.com/questions/15705630/get-the-rows-which-have-the-max-value-in-groups-using-groupby) — Adam.Er8, May 29 '22 at 10:51
^ the solution there that assigns `idx` should get you very close — Adam.Er8, May 29 '22 at 10:52
The answers there are a bit confusing. According to their solutions I was able to add a column for the highest score, but how do I add the subject of that score? — Programming Noob, May 29 '22 at 11:04

score 1 · Accepted Answer · answered May 29 '22 at 11:07

Sort the values by Scores, then group the dataframe by Names and transform the column Subject with last

df['S(max)'] = df.sort_values('Scores').groupby('Names')['Subject'].transform('last')

Alternatively, we can group the dataframe by Names then transform Scores with idxmax to broadcast the indices corresponding to row having max Score, then use those indices to get the corresponding rows from Subject column

df['S(max)'] = df.loc[df.groupby('Names')['Scores'].transform('idxmax'), 'Subject'].tolist()

      Names  Scores    Subject     S(max)
0       Dan      98       Math       Math
1       Dan      88    English       Math
2       Dan      90    Biology       Math
3       Bob      80       Math  Chemistry
4       Bob      93  Chemistry  Chemistry
5       Bob      70     Sports  Chemistry
6       Bob      85     French  Chemistry
7   Michael     100    History    History
8    Sandra      67     French     French
9   Michael      89       Math    History
10  Michael      74     Sports    History
11    Jacky      65    Biology    Physics
12    Jacky     100    Physics    Physics
13    Jacky      90   Geometry    Physics
14    Jacky      87  Geography    Physics
15    Jacky      69       Math    Physics
16      Dan      73     Sports       Math
17   Sandra      50    History     French

I see what you did there, didn't think about the sort. Thank you really helpful! — Programming Noob, May 29 '22 at 11:10

Finding the max value in accordance with other columns

Input data would be:

Output data (the result data frame) would be :

My try at this (didn't work obviously):

1 Answers1