Adding new column to an existing dataframe column base on a given condition

Question

I have a Dataframe with the below column names, and I want to create a new column based on a given condition. The condition are to create a new column Race for each unique EVENT_ID in the dataframe check for rows in the WIN_LOSE that has 1 as value, take the SELECTION_TRAP value that corespond to that WIN_LOSE row and create the Race.

I tried using the pandas where function but I an not getting it right

df['Race'] = df.where(df['WIN_LOSE']==1,df['SELECTION_TRAP'],axis = 0)

EVENT_ID    SELECTION_TRAP  WIN_LOSE
174331755       1             0
174331755       2             0
174331755       7             1
174331755       4             0
174331755       3             0
174331755       6             0
174331755       8             0
174329130       5             0
174329130       7             1
174329130       1             0
174329130       4             0
174329130       2             0
174329130       8             0

My expected output should look this.

EVENT_ID    SELECTION_TRAP  WIN_LOSE  RACE
174331755       7             1        7
174329130       7             1        7

Whats happen if no 1 per group?

jezrael
– jezrael

2021-09-22 09:45:38 +00:00
Commented Sep 22, 2021 at 9:45 — jezrael
– jezrael, Commented Sep 22, 2021 at 9:45
Whats happens if multiple 1 per group ?

jezrael
– jezrael

2021-09-22 09:45:55 +00:00
Commented Sep 22, 2021 at 9:45 — jezrael
– jezrael, Commented Sep 22, 2021 at 9:45

jezrael · Accepted Answer · 2021-09-22 09:50:45Z

1

First filter by 1 in WIN_LOSE by boolean indexing and then assign new column.

df1 = df[df['WIN_LOSE']==1].copy()
df1['RACE'] = df1['SELECTION_TRAP']
print (df1)
    EVENT_ID  SELECTION_TRAP  WIN_LOSE  RACE
2  174331755               7         1     7
8  174329130               7         1     7

edited Sep 22, 2021 at 9:50

answered Sep 22, 2021 at 9:45

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Comments

sophocles · Accepted Answer · 2021-09-22 09:45:51Z

1

IIUC, you can create your column called 'RACE', when 'WIN_LOSE' equals 1, and then filter your df:

df.loc[df['WIN_LOSE'].eq(1),'RACE'] = df['SELECTION_TRAP']
df.loc[df.RACE.notnull()]

Prints back:

    EVENT_ID  SELECTION_TRAP  WIN_LOSE  RACE
2  174331755               7         1   7.0
8  174329130               7         1   7.0

answered Sep 22, 2021 at 9:45

sophocles

13.9k3 gold badges18 silver badges37 bronze badges

Collectives™ on Stack Overflow

Adding new column to an existing dataframe column base on a given condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related