print pandas dataframe diff to new column-CodePudding

I have a dataframe that looks like this. There are two rows for each id. These represent a game where the row with the highest points is the winner:

id   points
677    5
677    15
678    25
678    6

I would like to generate a new column 'win' in the dataframe so that the row with the same id with the higher points gets the value 1 and the lesser 0.

Like this:

id   points  win
677    5      0
677    15     1
678    25     1
678    6      0

I think I could do something like this, but can't figure out how you would get the diff to output a value based on the condition of greater or less and then push to a new column.

print(df.set_index('id').groupby(level=0).diff().query('points' > 0).index.unique().tolist())

CodePudding user response：

Find the max points for each id and mark it as win:

df['win'] = (df.points.groupby(df['id']).transform('max') == df.points).astype(int)
df
    id  points  win
0  677       5    0
1  677      15    1
2  678      25    1
3  678       6    0