python - 在 Pandas DF 的不同列中计算运行总计

Question

我有一个像这样的 Pandas 数据框。

frame =  pd.DataFrame({'home' : ['CHI', 'ATL', 'SEA', 'DET', 'STL','HOU' ,'CHI','CHI'],
                   'away' : ['DET', 'CHI', 'HOU', 'TOR', 'DAL', 'STL', 'MIA', 'SEA']})

感谢 unutbu，我可以像这样记录每支球队的总比赛数。

awayGP = collections.Counter()
homeGP = collections.Counter()

def count_games():
    for idx, row in frame.iterrows():
        homeGP[row['home']] +=1
        awayGP[row['away']] +=1
        test = homeGP + awayGP
        yield awayGP[row['away']], awayGP[row['home']], , homeGP[row['away']], homeGP[row['home']]

frame['awayteamAwayGP'] , frame['hometeamAwayGP'], frame['awayteamHomeGP'],          frame['hometeamHomeGP'] = zip(*list(count_games()))                                                             
frame['awayteamGames'] =   frame['awayteamAwayGP'] + frame['awayteamHomeGP']                                                                                       
frame['hometeamGames'] =   frame['hometeamAwayGP'] + frame['hometeamHomeGP']        
del frame['awayteamAwayGP'] , frame['hometeamAwayGP'], frame['awayteamHomeGP'],    frame['hometeamHomeGP']  

我希望能够计算每支球队的总得分。

frame['awayPTS'] = [88, 75, 105, 99, 110, 85, 95, 100]
frame['homePTS'] = [92, 88, 95, 97, 100, 74, 98, 110]

这是期望的输出。

away  home  awayteamGP  hometeamGP awayPTS  homePTS awayteam_totalPTS hometeam_totalPTS 
 DET   CHI     1             1       88        92          88               92
 CHI   ATL     2             1       75        88          180              88
 HOU   SEA     1             1       105       95          105              95
 TOR   DET     1             2       99        97          99               187
 DAL   STL     1             1       110       100         110              100
 STL   HOU     2             2       85        74          185              179
 MIA   CHI     1             3       95        98          95               265
 SEA   CHI     2             4       100       110         195              375

Answer 1

我认为做 groupby 是有意义的然后 cumsum 每组。值得注意的是，当您的表中有更多项目时，此方法将显着快于 Counter/defaultdict 解决方案(我看到它快两倍 100 行，快五十倍有 10000 行)。

首先我们必须stack通过这种方式，我们可以(在外/在家)独立完成此操作:

In [10]: frame.columns = [['away', 'away', 'home', 'home'],
                          ['team', 'PTS', 'team', 'PTS']]

In [11]: frame  # with nice descriptive column labels
Out[11]: 
  away  away home  home
  team   PTS team   PTS
0  DET    88  CHI    92
1  CHI    75  ATL    88
2  HOU   105  SEA    95
3  TOR    99  DET    97
4  DAL   110  STL   100
5  STL    85  HOU    74
6  MIA    95  CHI    98
7  SEA   100  CHI   110

In [12]: frame_stacked = frame.stack(0)

In [13]: frame_stacked
Out[13]: 
        PTS team
0 away   88  DET
  home   92  CHI
1 away   75  CHI
  home   88  ATL
2 away  105  HOU
  home   95  SEA
3 away   99  TOR
  home   97  DET
4 away  110  DAL
  home  100  STL
5 away   85  STL
  home   74  HOU
6 away   95  MIA
  home   98  CHI
7 away  100  SEA
  home  110  CHI

现在我们可以在这里对球队进行分组(cumsum 将包括他们的客场和主场比赛):

In [14]: total_pts = frame_stacked.groupby('team')['PTS'].cumsum()

In [15]: total_pts
Out[15]: 
0  away     88
   home     92
1  away    167
   home     88
2  away    105
   home     95
3  away     99
   home    185
4  away    110
   home    100
5  away    185
   home    179
6  away     95
   home    265
7  away    195
   home    375
dtype: int64

最后，我们只需将这些插入到具有正确命名列的框架中:

In [16]: frame[('home', 'totalPTS')] = total_pts[:, 'home']

In [17]: frame[('away', 'totalPTS')] = total_pts[:, 'away']

In [18]: frame
Out[18]: 
  away  away home  home     away     home
  team   PTS team   PTS totalPTS totalPTS
0  DET    88  CHI    92       88       92
1  CHI    75  ATL    88      167       88
2  HOU   105  SEA    95      105       95
3  TOR    99  DET    97       99      185
4  DAL   110  STL   100      110      100
5  STL    85  HOU    74      185      179
6  MIA    95  CHI    98       95      265
7  SEA   100  CHI   110      195      375