creating running totals in DataFrame

25 views
Skip to first unread message

ryan h

unread,
May 21, 2013, 8:51:43 PM5/21/13
to pyd...@googlegroups.com

I have a dataframe like this

data = {'RoadTeam':['ATL', 'BOS', 'CHI', 'ATL', 'CHI','DAL'],
        'HomeTeam':['CHI', 'DEN', 'BOS', 'BOS', 'DAL','CHI'],
        'RoadPTS':[95,90,92,91,88,99,],
        'HomePTS': [91,88,75,69,101, 77]}
        
frame =  pd.DataFrame(data)

output RoadTeam  HomeTeam RoadPTS HomePTS

             ATL            CHI                 95          91
             BOS            DEN              90           88
             CHI             BOS              92            75
             ATL            BOS              91             69
             CHI             DAL               88            101
             DAL             CHI               99            77

I would like to keep a running total for the total points scored for each team.  The desired output would look like this.  


RoadTeam         HomeTeam       RoadPTS    HomePTS    RoadTeamCumPTS      HomeTeamCumPTS

             ATL            CHI                 95          91                  95                                   91
             BOS            DEN              90           88                 90                                    88  
             CHI             BOS              92            75                183                                 165  
             ATL            BOS              91             69                186                                 234   
             CHI             DAL               88            101              271                                  101
             DAL             CHI               99            77               200                                  348


I can keep find the running total of the RoadPTS of the RoadTeam like this frame.groupby('RoadTeam')['RoadPTS'].cumsum()  and the HomePTS of the HomeTeam by this frame.groupby('HomeTeam')['HomePTS'].cumsum()

I don't know to find the prior HomePTS scored by the RoadTeam and the prior RoadPTS scord by the HomeTeam.  

One not so elegant solution I thought of was making a new column in the dataframe for each team like this.  frame['CHIPTS'] = frame['RoadPTS'] if frame['RoadTeam'] == 'CHI' else frame['HomePTS'] if WNBA12['HomeTeam'] == 'CHI' else 0
that doesn't work and gives this error.  ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all() I don't know if it is possible to create a new column in that way.

There has to be a better method anyways but I don't know how.

Reply all
Reply to author
Forward
0 new messages