Python Create new columns based on other column conditions

Question

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

mozway · Accepted Answer · 2022-05-09 12:22:01Z

2

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

answered May 9, 2022 at 12:22

mozway

267k13 gold badges56 silver badges106 bronze badges

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

6 Comments

Omar B Over a year ago

This code only works correctly when America is away. I'm doing something wrong?

mozway Over a year ago

Do you mean that there are case where neither Home/Away is América? What should happen then?

Omar B Over a year ago

No, sorry. On all América is always one of the 2. Now when I run the code I get the correct xG when América appears away. When they are at home the xG values are reversed.

mozway Over a year ago

You mean the HomexG? Isn't it precisely what you wanted?

Omar B Over a year ago

I finally got it to work!

|

jezrael · Accepted Answer · 2022-05-09 12:23:52Z

2

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

answered May 9, 2022 at 12:23

jezrael

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

1 Comment

Omar B Over a year ago

I am getting the following error: KeyError: "None of [Index(['HomexG', 'AwayxG'], dtype='object')] are in the [columns]"

Collectives™ on Stack Overflow

Python Create new columns based on other column conditions

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related