尝试使用在多列中显示的相同值创建数据帧

Question

我尝试使用groupby获取累积和，其中累积和应用于包含相同值的多个列

import pandas as pd
import numpy as np

df = pd.DataFrame([['Jazz', 'Clippers', 89, 100],
                              ['Clippers' , 'Jazz', 101, 97],
                              ['Bucks' , 'Jazz', 99, 112],
                              ['Jazz' , 'Bucks', 109, 88]],
                      columns=['home_team', 'away_team', 'home_points', 'away_points'])
print(df)

这将生成输出为的数据帧

  home_team away_team  home_points  away_points
0      Jazz  Clippers           89          100
1  Clippers      Jazz          101           97
2     Bucks      Jazz           99          112
3      Jazz     Bucks          109           88

我想做的是得到主客场球队的累计总分，这将说明每个球队都出现在主客场两栏中，但我所能计算出的是按球队名称分组的累计总分，即主客场球队的总分，如下图所示

df["home_cumulative_points"]= df.groupby(["home_team"])["home_points"].cumsum() 
df["away_cumulative_points"]= df.groupby(["away_team"])["away_points"].cumsum() 
print(df)

产生

  home_team away_team  home_points  away_points  home_cumulative_points  away_cumulative_points
0      Jazz  Clippers           89          100                      89                     100
1  Clippers      Jazz          101           97                     101                      97
2     Bucks      Jazz           99          112                      99                     209
3      Jazz     Bucks          109           88                     198                      88

是否有任何方法可以让groupby在主客场栏中记录同一球队的累计积分，以使连续积分加上球队积分，而不管他们是主客场？所以最后一行的理想输出是

  home_team away_team  home_points  away_points  home_cumulative_points  away_cumulative_points
3      Jazz     Bucks          109           88                     407                      187

我猜我可能需要做一个for循环或其他什么，但我不确定如何最好地去做。提前感谢您的反馈

Answer 1

想法是只选择必要的列，用_分割MultiIndex，用^{}重塑，这样就可以对两个列同时使用cumsum：

cols = ['home_team', 'away_team', 'home_points', 'away_points']

df1 = df[cols].copy()
df1.columns = df1.columns.str.split('_', expand=True)
df1 = df1.stack(0).rename_axis(['lev1','lev2'])
df1["cumulative_points"]= df1.groupby(["team", 'lev1'])["points"].cumsum() 

df2 = df1.unstack()
df2.columns = df2.columns.map(lambda x: f'{x[1]}_{x[0]}')
print(df2)
      away_points  home_points away_team home_team  away_cumulative_points  \
lev1                                                                         
0             100           89  Clippers      Jazz                     100   
1              97          101      Jazz  Clippers                      97   
2             112           99      Jazz     Bucks                     112   
3              88          109     Bucks      Jazz                      88   

      home_cumulative_points  
lev1                          
0                         89  
1                        101  
2                         99  
3                        109  

或：

df["home_cumulative_points"]= df1.loc['home', 'cumulative_points']
df["away_cumulative_points"]= df1.loc['away', 'cumulative_points']

另一种方法是使用^{}和rename进行重塑：

f = lambda x: x.split('_')[1]
df1 = pd.concat([df[['home_team', 'home_points']].rename(columns=f),
                 df[['away_team', 'away_points']].rename(columns=f)], keys=('home','away'))
df1 = df1.rename_axis(['lev1','lev2'])
df1["cumulative_points"]= df1.groupby(["team", 'lev1'])["points"].cumsum()

df["home_cumulative_points"]= df1.loc['home', 'cumulative_points']
df["away_cumulative_points"]= df1.loc['away', 'cumulative_points']
print(df)
  home_team away_team  home_points  away_points  home_cumulative_points  \
0      Jazz  Clippers           89          100                      89   
1  Clippers      Jazz          101           97                     101   
2     Bucks      Jazz           99          112                      99   
3      Jazz     Bucks          109           88                     198   

   away_cumulative_points  
0                     100  
1                      97  
2                     209  
3                      88