[FIXED] Python/Pandas: Repeat row value for each row in column when multiple conditions are met

Issue

I have used pd.wide_to_long() to create a longer df. I want to create a new column called "Bodyweight" that takes the value from the "Load" column when the exercise == "bodyweight" and displays it for each row entry for an athlete on that date. the current layout is:

ie

Input

        Date    Player_Name Exercise    Set Reps    Load
0   Day 1   Player 1    Bench Press 1   5   120
1   Day 1   Player 1    Bench Press 2   5   120
2   Day 1   Player 1    Bench Press 3   5   120
3   Day 1   Player 1    Bodyweight  1   1   100
4   Day 2   Player 1    Squat   1   5   180
5   Day 2   Player 1    Squat   2   5   180
6   Day 2   Player 1    Squat   3   5   180
7   Day 2   Player 1    Bodyweight  1   1   101.5
8   Day 1   Player 2    Chin up 1   3   125
9   Day 1   Player 2    Bodyweight  1   1   92

Output

Date    Player_Name Bodyweight  Exercise    Set Reps    Load
0   Day 1   Player 1    100 Bench Press 1   5   120
1   Day 1   Player 1    100 Bench Press 2   5   120
2   Day 1   Player 1    100 Bench Press 3   5   120
3   Day 2   Player 1    101.5   Squat   1   5   180
4   Day 2   Player 1    101.5   Squat   2   5   180
5   Day 2   Player 1    101.5   Squat   3   5   180
6   Day 1   Player 2    92  Chin up 1   3   125

I have tried:

df["Body_Weight"] = df[df["Exercise"] == "Body Weight"]["Load"]

to create a new column but this does not repeat the value for each entry of that player on that date.

Any help would be great! Thanks, Liam

Solution

Create a boolean mask and transform to broadcast the Load value of Bodyweight to each row of the group:

m = df['Exercise'] == 'Bodyweight'
bw = df.groupby(['Date', 'Player_Name'])['Load'].transform(lambda x: x[m].max())[~m]
out = df.assign(Body_Weight=bw)[~m]
print(out)

# Output
    Date Player_Name     Exercise  Set  Reps   Load  Body_Weight
0  Day 1    Player 1  Bench Press    1     5  120.0        100.0
1  Day 1    Player 1  Bench Press    2     5  120.0        100.0
2  Day 1    Player 1  Bench Press    3     5  120.0        100.0
4  Day 2    Player 1        Squat    1     5  180.0        101.5
5  Day 2    Player 1        Squat    2     5  180.0        101.5
6  Day 2    Player 1        Squat    3     5  180.0        101.5
8  Day 1    Player 2      Chin up    1     3  125.0         92.0

Answered By - Corralien