CODEWITHSUNDEEP
python-3.xpandaspandas-groupbycumulative-sum
DwayneHart
DwayneHart

Reputation: 129

calculate value based on value from previous value

I have the following dataset given, describing transactions for purchasing items in a frame (frameNo). the frame encompasses events that happened in a single minute. therefore, the "currentGold" value only depicts the value of gold a player has had entering the frame.

What I am trying to do, is to calculate how much gold is left after each transaction. grouped by the frameNo, since players can generate gold in between events (and in this very example in between frames).

           gameId platformId  frameNo  timestamp  itemId  currentGold    Cost
0       948881246        BR1      1.0       4451    2010          500      50
1       948881246        BR1      1.0       5129    1055          500     450
2       948881246        BR1      6.0     302762    1038         1300    1300
3       948881246        BR1      7.0     417640    1001          300     300
4       948881246        BR1      8.0     420211    1036          759     350
5       948881246        BR1      8.0     421285    1036          759     350
6       948881246        BR1      8.0     421904    2010          759      50
7       948881246        BR1     10.0     555882    3133         1220     310
8       948881246        BR1     10.0     557963    1018         1220     800
9       948881246        BR1     10.0     558777    2010         1220      50
10      948881246        BR1     12.0     697438    3508          850     200
11      948881246        BR1     12.0     701438    1051          850     400
12      948881246        BR1     12.0     701796    1042          850     300
13      948881246        BR1     12.0     703291    2010          850      50
14      948881246        BR1     15.0     848427    3086         1397     500
15      948881246        BR1     15.0     849077    3006         1397     500
16      948881246        BR1     15.0     851125    3363         1397       0

This is the result I would like to achieve:

           gameId platformId  frameNo  timestamp  itemId  currentGold    Cost  availGold
0       948881246        BR1      1.0       4451    2010          500      50       500
1       948881246        BR1      1.0       5129    1055          500     450       450
2       948881246        BR1      6.0     302762    1038         1300    1300      1300
3       948881246        BR1      7.0     417640    1001          300     300       300
4       948881246        BR1      8.0     420211    1036          759     350       759
5       948881246        BR1      8.0     421285    1036          759     350       409
6       948881246        BR1      8.0     421904    2010          759      50        59
7       948881246        BR1     10.0     555882    3133         1220     310      1220
8       948881246        BR1     10.0     557963    1018         1220     800       910
9       948881246        BR1     10.0     558777    2010         1220      50       110
10      948881246        BR1     12.0     697438    3508          850     200       850
11      948881246        BR1     12.0     701438    1051          850     400       650
12      948881246        BR1     12.0     701796    1042          850     300       350
13      948881246        BR1     12.0     703291    2010          850      50        50   
14      948881246        BR1     15.0     848427    3086         1397     500      1397
15      948881246        BR1     15.0     849077    3006         1397     500       897
16      948881246        BR1     15.0     851125    3363         1397       0       397

I've tried to iterate via iterrows(), however, I think it's impossible to reach the row previously and also the dependency on the (gameId, platformId, frameNo) key gives me a bit of headaches

Upvotes: 0

Views: 42

Answers (1)

Silenced Temporarily
Silenced Temporarily

Reputation: 1004

Is it possible that you have an error in your "desired" output in the following rows?

948881246        BR1     12.0     701438    1051          850     400       650
948881246        BR1     12.0     701796    1042          850     300       350
948881246        BR1     12.0     703291    2010          850      50        50

From the logic you've described and the previous rows, the final column (availGold) here should be 250 in the second row and -50 in the final row. If that's the case, you can achieve this by using cumsum and shift:

df['frameCost'] = df.groupby(['gameId', 'platformId', 'frameNo'])['Cost'].cumsum()
df['availGold'] = df['currentGold'] - df.groupby(['gameId', 'platformId', 'frameNo'])['frameCost'].shift(1).fillna(0)

Upvotes: 1

Related Questions