Is there a way to sum 2 columns by grouping 2 other columns on the same value?

Question

I'm using a data frame like this:

home_team away_team  home_score  away_score 
Scotland   England           0           0   
England   Scotland           4           2   
Scotland   England           2           1   
England   Scotland           2           2   
Scotland   England           3           0

Here's what I would like to accomplish, I'm trying to regroup all the country together whether they are home or away and have the sum total of the score.

Team    total goal
Scotland   9
England    7

Does this answer your question? [pandas convert some columns into rows](https://stackoverflow.com/questions/28654047/pandas-convert-some-columns-into-rows) — Umar.H, Jul 07 '20 at 19:58

score 4 · Accepted Answer · answered Jul 07 '20 at 20:01

4

Try this and let me know if you face any issue/error. Here you go:

df.groupby("home_team").home_score.sum()+df.groupby("away_team").away_score.sum()

answered Jul 07 '20 at 20:01

Mohak Gangwani

104
1
8

it's working my boi! Can you explain to me the logic behind ? I'm not sure what the .home_score is doing after the groupby. – yce Jul 07 '20 at 20:10
Break it down into steps to understand `df2 = df.groupby('home_team')['home_score'].sum()` `df3 = df.groupby('away_team')['away_score'].sum()` `df4 = df2 + df3` – David Erickson Jul 07 '20 at 20:12

score 0 · Answer 2 · answered Jul 07 '20 at 20:14

This should do the trick (assuming your original DataFrame is called df):

nations = ["England", "Scotland"]
tot = pd.DataFrame([(nation, 0) for nation in nations], columns=["team","total goal"])

for nation in nations:
    home_goal = sum(df[df["home_team"] == nation]["home_score"])
    away_goal = sum(df[df["away_team"] == nation]["away_score"]) 
    tot.loc[tot.team == nation, "total goal"] = home_goal + away_goal

Is there a way to sum 2 columns by grouping 2 other columns on the same value?

2 Answers2