print pandas dataframe diff to new column

Question

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())

akuiper · Accepted Answer · 2022-11-25 02:42:21Z

1

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

answered Nov 25, 2022 at 2:42

akuiper

216k33 gold badges363 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

print pandas dataframe diff to new column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related