CODEWITHSUNDEEP
pythonpandasdataframe
HABLOH
HABLOH

Reputation: 470

Subtraction between columns and several rows

I would like to expose my problem. I have a dataset of this type as you can see below. I would like to add a column to the dataset called balance_required_for_next_bet, is a variable calculated as follows:

(value of the 'balance' variable of the row) - (value of the 'bet' variable of the next line)

          Date      bet     current loss      current win    balance  
0   2016-08-20        8                8             0.00      -8.00       
1   2016-08-21       32               28            18.00     -18.00
2   2016-08-27       14               14             0.00     -32.00

here is an example of the new column to be added to the dataset with the calculation:

     balance required for next bet  (balance - next bet)
0           -8 - 32 ===> -40       
1          -18 - 14 ===> -32
2           -32 - 0 ===> -32

Thanks for the help.

Upvotes: 1

Views: 39

Answers (1)

Engineero
Engineero

Reputation: 12928

You can do this with a shift operation on one of the rows. For example:

import pandas as pd

df = pd.DataFrame({'bet': [8, 32, 14], 'balance': [-8, -18, -32]})
df['next_bet'] = df.balance - df.bet.shift(-1).fillna(0)
#    bet  balance  next_bet
# 0    8       -8     -40.0
# 1   32      -18     -32.0
# 2   14      -32     -32.0

Shifting the df.bet column by -1 effectively moves each entry up a row. By subtracting this shifted column from your df.balance column, you are subtracting the next bet from the current balance for each row. I add the fillna(0) to make sure you do not get any NaN values.

Upvotes: 2

Related Questions