groupby and sum two columns and set as one column in pandas

Question

I have the following data frame:

import pandas as pd
data = pd.DataFrame()
data['Home'] = ['A','B','C','D','E','F']
data['HomePoint'] = [3,0,1,1,3,3]
data['Away'] = ['B','C','A','E','D','D']
data['AwayPoint'] = [0,3,1,1,0,0]

i want to groupby the columns ['Home', 'Away'] and change the name as Team. Then i like to sum homepoint and awaypoint as name as Points.

     Team      Points
      A           4
      B           0
      C           4
      D           1
      E           4
      F           3

How can I do it? I was trying different approach using the following post: Link

But I was not able to get the format that I wanted.

Greatly appreciate your advice.

Thanks

Zep.

John Zwinck · Accepted Answer · 2018-06-24T08:51:05.487

A simple way is to create two new Series indexed by the teams:

home = pd.Series(data.HomePoint.values, data.Home)
away = pd.Series(data.AwayPoint.values, data.Away)

Then, the result you want is:

home.add(away, fill_value=0).astype(int)

Note that home + away does not work, because team F never played away, so would result in NaN for them. So we use Series.add() with fill_value=0.

A complicated way is to use DataFrame.melt():

goo = data.melt(['HomePoint', 'AwayPoint'], var_name='At', value_name='Team')
goo.HomePoint.where(goo.At == 'Home', goo.AwayPoint).groupby(goo.Team).sum()

Or from the other perspective:

ooze = data.melt(['Home', 'Away'])
ooze.value.groupby(ooze.Home.where(ooze.variable == 'HomePoint', ooze.Away)).sum()

Thanks John for providing me different options to go. Really appreciate your help. — Zephyr, Jun 26 '18 at 04:22

score 1 · Answer 2 · answered Jun 24 '18 at 10:51

You can concatenate, pairwise, columns of your input dataframe. Then use groupby.sum.

# calculate number of pairs
n = int(len(df.columns)/2)+1)

# create list of pairwise dataframes
df_lst = [data.iloc[:, 2*i:2*(i+1)].set_axis(['Team', 'Points'], axis=1, inplace=False) \
          for i in range(n)]

# concatenate list of dataframes
df = pd.concat(df_lst, axis=0)

# perform groupby
res = df.groupby('Team', as_index=False)['Points'].sum()

print(res)

  Team  Points
0    A       4
1    B       0
2    C       4
3    D       1
4    E       4
5    F       3

Thanks JPP for your advice. I appreciate much – Zephyr Jun 26 '18 at 04:22 — Zephyr, Jun 26 '18 at 04:22

groupby and sum two columns and set as one column in pandas

2 Answers2