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.