Python Create new columns based on other column conditions-CodePudding

I have the following DF:

Fecha	xG	xGA	Home	Away	Score
2022-05-01	1.53	0.45	América	Cruz Azul	0:0
2022-04-24	1.46	0.47	Tigres UANL	América	0:2
2022-04-21	1.40	0.43	América	León	2:0
2022-04-16	2.44	0.65	Club Tijuana	América	1:3

I want to create two new columns named HomeXG and AwayXG where the values are taken from the xG and xGA columns under the condition that if America is the home team the xG becomes HomeXG and if they are away xG is used for AwayXG.

Expected output:

Fecha	xG	xGA	Home	Away	Score	HomeXG	AwayXG
2022-05-01	1.53	0.45	América	Cruz Azul	0:0	1.53	0.45
2022-04-24	1.46	0.47	Tigres UANL	América	0:2	0.47	1.46
2022-04-21	1.40	0.43	América	León	2:0	1.40	0.43
2022-04-16	2.44	0.65	Club Tijuana	América	1:3	0.65	2.44

CodePudding user response：

You can use where on a 2D slice of the DataFrame:

df[['HomexG', 'AwayxG']] = df[['xG', 'xGA']].where(df['Home'].eq('América'),
                                                   df[['xGA', 'xG']].values)

NB. the second argument of where must be a numpy array to avoid index alignment!

output:

        Fecha    xG   xGA          Home       Away Score  HomexG  AwayxG
0  2022-05-01  1.53  0.45       América  Cruz Azul   0:0    1.53    0.45
1  2022-04-24  1.46  0.47   Tigres UANL    América   0:2    0.47    1.46
2  2022-04-21  1.40  0.43       América       León   2:0    1.40    0.43
3  2022-04-16  2.44  0.65  Club Tijuana    América   1:3    0.65    2.44

CodePudding user response：

Use numpy.where, solution testing only Home column, it means América should me in Away for non matched rows:

df[['HomexG', 'AwayxG']] = np.where(df['Home'].eq('América').to_numpy()[:, None], 
                                    df[['xG','xGA']], df[['xGA','xG']])
print (df)
        Fecha    xG   xGA          Home       Away Score  HomexG  AwayxG
0  2022-05-01  1.53  0.45       América  Cruz Azul   0:0    1.53    0.45
1  2022-04-24  1.46  0.47   Tigres UANL    América   0:2    0.47    1.46
2  2022-04-21  1.40  0.43       América       León   2:0    1.40    0.43
3  2022-04-16  2.44  0.65  Club Tijuana    América   1:3    0.65    2.44