Using conditional statement to subtract scalar from pandas df column gives ValueError: The truth value of a Series is ambiguous

Question

I'm trying to execute:

if df_trades.loc[:, 'CASH'] != 0: df_trades.loc[:, 'CASH'] -= commission

and then I get the error. df_trades.loc[:, 'CASH'] is a column of floats. I want to subtract the scalar commission from each entry in that column.

For example, df_trades.loc[:, 'CASH'] prints out

2011-01-10   -2557.0000
2011-01-11       0.0000
2011-01-12       0.0000
2011-01-13   -2581.0000

If commission is 1, I want the result:

2011-01-10   -2558.0000
2011-01-11       0.0000
2011-01-12       0.0000
2011-01-13   -2582.0000

Bharath M Shetty · Accepted Answer · 2017-10-19T16:54:38.187

2

Use np.where

commission = -1
df['CASH'] = np.where(df['CASH'] != 0, df['CASH'] + commission , df['CASH'])

or df.where i.e

df['CASH'] = df['CASH'].where(df['CASH'] == 0,df['CASH']+commission)

or df.mask

df['CASH'] = df['CASH'].mask(df['CASH'] != 0 ,df['CASH']+commission)

Date
2011-01-10   -2558.0
2011-01-11       0.0
2011-01-12       0.0
2011-01-13   -2582.0
Name: CASH, dtype: float64

%%timeit
commission = -1
df['CASH'] = np.where(df['CASH'] != 0, df['CASH'] + commission , df['CASH'])
1000 loops, best of 3: 750 µs per loop

%%timeit
df['CASH'].mask(df['CASH'] != 0 ,df['CASH']+commission)
1000 loops, best of 3: 1.45 ms per loop

%%timeit
df['CASH'].where(df['CASH'] == 0,df['CASH']+commission)
1000 loops, best of 3: 1.55 ms per loop

%%timeit
df.loc[df['CASH'] != 0, 'CASH'] += commission
100 loops, best of 3: 2.37 ms per loop

edited Oct 19 '17 at 16:54

answered Oct 19 '17 at 12:57

Bharath M Shetty

30,075
6
57
108

Is Numpy or Pandas more efficient for this? – Oct 19 '17 at 13:02
Is pshep123's solution about the same as well? – Oct 19 '17 at 13:03
@dirtysocks45 I added the timings for better speed comparision – Bharath M Shetty Oct 19 '17 at 13:08
Thanks for the timing Bharath. Didn't realize `where()` was so much faster. Off to refactor a couple thousand, `.loc[]` statements... – elPastor Oct 19 '17 at 13:11
Would also like to thank you Bharath. Do you mind sharing the Pandas way as well? – Oct 19 '17 at 13:12
@pshep123 Its because boolean indexing and setting the value is a costly process than conditionally getting array and assigning it to a column name. – Bharath M Shetty Oct 19 '17 at 13:13
@dirtysocks45 `df.where` is a pandas way of doing this. There's another option with mask too. Let me put that here. – Bharath M Shetty Oct 19 '17 at 13:14
Can you add the times of mask and df.where – Oct 19 '17 at 16:51

score 0 · Answer 2 · answered Oct 19 '17 at 13:02

0

This should do it:

df.loc[df['CASH'] != 0, 'CASH'] -= 1

answered Oct 19 '17 at 13:02

elPastor

8,435
11
53
81

Is this the most Pythonic solution given I'm using Pandas and Numpy? – Oct 19 '17 at 13:05
I'm not the arbiter on pythonic code, but it works and is clean. Nor have I tested the speed, so give them both a shot and see what works best for you. – elPastor Oct 19 '17 at 13:07

Using conditional statement to subtract scalar from pandas df column gives ValueError: The truth value of a Series is ambiguous

2 Answers2

Linked