Getting the sum of values with a condition

user_8913983 0
Viewed 113

I have a pandas DataFrame which looks like this:

home_team   away_team   home_score    away_score
Spain       Albania     0             5
Albania     Spain       4             1
Albania     Portugal    1             2
Albania     US          0             2

From the first two lines we see that Spain and Albania played 2 times in total, Spain scored 1 goal, Albania scored 9 goals.

Then Albania has 1 game with US and Portugal and it's scores. I am trying to answer 'How many goals Albania has scored against each country and how many goals that country has scored against Albania'

So that I would get a DataFrame like this:

Albania  Spain      9  1
Albania  Portugal   1  2
Albania  US         0  2

When I use print(df.groupby(['away_team']).sum() + df.groupby(['home_team']).sum()) I do not get what I want and for some reason some lines are filled with NaNs. And it appears that the sums are not summing correctly.

pandas-groupby pandas python
4 Answers

You can sorting both columns teams and assign back, then swap values of scope if no match original home_team with sorted and last aggregate sum:

orig = df['home_team'].copy()
df[['home_team','away_team']] = np.sort(df[['home_team','away_team']], axis=1)
m = orig.ne(df['home_team'])
df.loc[m, ['home_score','away_score']] = df.loc[m, ['away_score','home_score']].values
print (df)
  home_team away_team  home_score  away_score
0   Albania     Spain           5           0
1   Albania     Spain           4           1
2   Albania  Portugal           1           2
3   Albania        US           0           2

df1 = df.groupby(['home_team', 'away_team'], as_index=False).sum()
print (df1)
  home_team away_team  home_score  away_score
0   Albania  Portugal           1           2
1   Albania     Spain           9           1
2   Albania        US           0           2
user_29010020

sort the home & away team columns alphabetically to generate two new team columns

add two more columns for score_for & score against.

group by the two new team columns and sum the two new score columns 

df[['team1', 'team2']] = df[['home_team', 'away_team']].apply(np.sort, axis=1, result_type='expand')

df[['score_for', 'score_against']] = df.apply(
  lambda x: [x.home_score, x.away_score] if x.team1 == x.home_team else [x.away_score, x.home_score], 
  axis=1, 
  result_type='expand')

df.groupby(['team1', 'team2'])[['score_for', 'score_against']].sum()
                  score_for  score_against
team1   team2                             
Albania Portugal          1              2
        Spain             9              1
        US                0              2
user_25702610

Swap home team and away team, as well as score. Concatenate two dataframes together and groupby.

df1 = df.T.reset_index(drop=True)

df2 = df1.rename({0:1, 1:0, 2:3, 3:2}).sort_index()

pd.concat([df1.T, df2.T]).groupby([0,1]).sum().loc[['Albania']]
user_117478610 
home = df[df.home_team == "Albania"]
home.columns = ["country","opponent","win","lost"]
away = df[df.away_team == "Albania"]
away.columns = ["opponent","country","lost","win"]

pd.concat([home,away],ignore_index=True).groupby(["country","opponent"]).sum()
user_115246280
Related