Pandas：如何透视多个列并计算它们的总和？

Question

我有这样一个数据帧：

Team      Player      Goals       YellowCards          RedCards

Team1     Player1       2             1                    1

Team1     Player2       3             1                    0

Team2     Player3       2             2                    1

我试图计算每个团队的Goals、YellowCards和RedCards之和，并为结果创建新的数据框架。我试过：

pd.crosstab(df['Team'],[df['Goals'],df['YellowCards'],df['RedCards']], aggfunc='sum')

但它不起作用。我最好使用crosstab或pivot_table函数来实现这一点。任何建议都将不胜感激

Answer 1

我添加了列总数和总计

data=[('Team1','Player1',       2,             1,                    1),
('Team1','Player2',       3,             1,                    0),
('Team2','Player3',       2,             2,                    1)]

df=pd.DataFrame(data=data,columns=['Team','Player','Goals', 'YellowCards','RedCards'])

fp=df.pivot_table(index='Team',aggfunc='sum')
fp['Totals'] = fp.sum(axis='columns')
fp.loc[('Grand Total'), :] = fp.sum()
print(fp)

输出

 Goals  RedCards  YellowCards  Totals
 Team                                             
 Team1          5.0       1.0          2.0     8.0
 Team2          2.0       1.0          2.0     5.0
 Grand Total    7.0       2.0          4.0    13.0

Answer 2

因为需要^{}，最简单的解决方案是：

df = df.pivot_table(index='Team',aggfunc='sum')
print (df)
       Goals  RedCards  YellowCards
Team                               
Team1      5         1            2
Team2      2         1            2

像聚合一样工作sum：

df = df.groupby('Team').sum()

编辑：如果需要，请指定列：

df = df.pivot_table(index='Team',aggfunc='sum',values=['Goals','RedCards','YellowCards'])
print (df)
       Goals  RedCards  YellowCards
Team                               
Team1      5         1            2
Team2      2         1            2

工作方式如下：

df = df.groupby('Team')[['Goals','RedCards','YellowCards']].sum()