codervince codervince - 2 years ago 98
Python Question

Pandas apply a change which affects 2 columns at the same time

I have the dataframe below. bet_pl and co_pl keep track of the daily changes in the 2 balances. I have updated co_balance based on co_pl and the cumsum.

init_balance = D('100.0')
co_thresh = D('1.05') * init_balance

def get_pl_co(row):
if row['eod_bet_balance'] > co_thresh:
diff = row['eod_bet_balance']- co_thresh
return Decimal('0.0')

df_odds_winloss['eod_bet_balance'] = df_odds_winloss['bet_pl'].cumsum()+initial_balance
df_odds_winloss['sod_bet_balance']= df_odds_winloss['eod_bet_balance'].shift(1).fillna(init_balance)
df_odds_winloss['co_pl'] = df_odds_winloss.apply(get_pl_co, axis=1)
df_odds_winloss['co_balance'] = df_odds_winloss['co_pl'].cumsum()

# trying this
df_odds_winloss['eod_bet_balance'] = df_odds_winloss['eod_bet_balance'] - df_odds_winloss['co_pl']

Now I want the eod_bet_balance to update with negative co_pl as it is a transfer between the 2 balances, but am not getting the right eod (end of day) balances.

Can anyone give a hint?

UPDATED: The eod_balances reflect the change in bet_pl but not the subsequent change in co_pl.


initial_balance = D('100.0')
df = pd.DataFrame({ 'SP': res_df['SP'], 'winloss': bin_seq_l}, columns=['SP', 'winloss'])
df['bet_pl'] = df.apply(get_pl_lvl, axis=1)
df['interim_balance'] = df_odds_winloss['bet_pl'].cumsum()+initial_balance
df['co_pl'] = (df['interim_balance'] - co_thresh).clip_lower(0)
df['co_balance'] = df_odds_winloss['co_pl'].cumsum()
df['post_co_balance'] = df['interim_balance'] - df['co_pl']
bf_r = D('0.05')
df['post_co_deduct_balance'] = df['post_co_balance'] - (df['post_co_balance']* bf_r)
df['sod_bet_balance'] = df['post_co_deduct_balance'].shift(1).fillna(init_balance)

The final solution

Answer Source

First, you don't need to apply a custom function to get co_pl, it could be done like so:

df['co_pl'] = (df['eod_bet_balance'] - co_thresh).clip_lower(0)

As for updating the other column, if I understand correctly you want something like this:

df['eod_bet_balance'] = df['eod_bet_balance'].clip_upper(co_thresh)

or, equivalently...

df['eod_bet_balance'] -= df['co_pl']
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download