Ratio of [value in cell to the left] to [sum of values above cell to the left] into a new column using pandas

Question

I have a column called Bets of poker bets in a dataframe called ACTION_DATA. I want to create a new column with the Bet-to-Pot Ratio (% of Pot)

Bet-to-Pot Ratio equals the bet in the same row from the Bets column divided by the sum of all previous bets in the Bets column.The first two bets should always be labeled "Small Blind" and "Big Blind".

For example, for every Bets column, I want a Bet-to-Pot Ratio column that shows this ratio as follows:

| Bets | Bet-To-Pot-Ratio | Formula 
| -------- | -------------- |-----|
| .02    |  Small Blind     | 
| .05   |  Big Blind        | 
|.15 | 214% | .15/(.02 + .05)


| Bets | Bet-To-Pot-Ratio |Formula 
| -------- | -------------- |---| 
| .02    |  Small Blind      | 
| .05   |  Big Blind         | 
| .17 | 243% | .17/(.02 + .05)

| Bets | Bet-To-Pot-Ratio | Formula
 | -------- | -------------- |---| 
| .02    |  Small Blind      | 
| .05   |  Big Blind         | 
| .05 | 71% | .05/(.02 + .05) | 
|.05| 42% | .05/(.02 + .05 + .05)|
|.05| 29% | .05/(.02 + .05 + .05 + .05)|
|.03| 14% |.03/(.02 + .05 + .05 + .05 + .05)|

| Bets | Bet-To-Pot-Ratio | Formula
| -------- | -------------- |---| 
| .02    |  Small Blind      | 
| .05   |  Big Blind         | 
| .1 |  .1/(.02 + .05) |
|.2| 118% | .2/(.02 + .05 + .1)|
|.99| 268% | .99/(.02 + .05 + .1 + .2)|
|2.87| 211% |2.87/(.02 + .05 + .1 + .2 + .99)|

I've tried some calculating across columns as shown below but I think I need to calculate up rows instead

import pandas as pd
df = [0, 0, 0]
df = pd.DataFrame(df).transpose()
df.columns = ['Bet Size', 'Pot', '% of Pot']

df['Pot'][0] = 0
df['% of Pot'][0] = 0

for _ in range(1, 10):
  df.loc[_, 'Bet Size'] = df.loc[_-1, 'Bet Size']+1
  df.loc[_, 'Pot'] = df.loc[_, 'Bet Size']+100
  df.loc[_, '% of Pot'] = df.loc[_-1, 'Pot']+df.loc[_, 'Pot'] + 1
df

score 1 · Accepted Answer · answered Feb 01 '21 at 19:03

You can use shift() and cumsum() to get the answer:

bets = [0.02, 0.05, 0.1, 0.2, 0.99, 2.87]
df = pd.DataFrame({ "bets" : bets})

df['ratio'] = df['bets']/(df['bets'].shift(1).cumsum())

print(df)

   bets     ratio
0  0.02       NaN
1  0.05  2.500000
2  0.10  1.428571
3  0.20  1.176471
4  0.99  2.675676
5  2.87  2.110294

Ratio of [value in cell to the left] to [sum of values above cell to the left] into a new column using pandas

1 Answers1