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

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

CodePudding user response：

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

CodePudding user response：

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