7

I'm using Pandas and am trying to create a new column using a Python if-else statement (aka ternary condition operator) in order to avoid division by zero.

For example below, I want to create a new column C by dividing A/B. I want to use the if-else statement to avoid dividing by 0.

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.randint(0, 5, size=(100, 2)), columns=list('AB'))
df.head()
#    A  B
# 0  1  3
# 1  1  2
# 2  0  0
# 3  2  1
# 4  4  2

df['C'] = (df.A / df.B) if df.B > 0.0 else 0.0

However, I am getting an error from the last line:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

I searched on StackOverflow and found other posts about this error, but none of them involved this type of if-else statement. Some posts include:

Truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()

The truth value of a Series is ambiguous in dataframe

Error: The truth value of a Series is ambiguous - Python pandas

Any help would be appreciated.

stackoverflowuser2010
  • 38,621
  • 48
  • 169
  • 217

5 Answers5

15

What about doing 

>>> df['C'] = np.where(df.B>0., df.A/df.B, 0.)

which reads as :

where df.B is strictly positive, return df.A/df.B, otherwise return 0.

keepAlive
  • 6,369
  • 5
  • 24
  • 39
10

df.B > 0 results in a Series, e.g.:

0      True  # 4 > 0 => True
1      True  # 2 > 0 => True
2      True  # ...
3      True
4      True
5      True
6      True
7      True
8     False  # 0 is not > 0 => False
9     False  # 0 is not > 0 => False
...

Multiple values are returned which results in ambiguity (some are True while others are False).

One solution is to use np.where:

sentinel = np.nan  # Or 0 if you must...
df = df.assign(C=np.where(df['B'] != 0, df['A'] / df['B'], sentinel))
>>> df
   A  B    C
0  2  4  0.5
1  0  2  0.0
2  1  2  0.5
3  4  4  1.0
4  1  1  1.0
5  4  4  1.0
6  2  4  0.5
7  1  2  0.5
8  4  0  NaN  # NaN is assigned in cases where the value in Column `B` is zero.
9  1  0  NaN
...
Alexander
  • 105,104
  • 32
  • 201
  • 196
  • Thank you. I did not even know `np.where()` existed or why it would be needed here. – stackoverflowuser2010 Jan 06 '18 at 01:28
  • And if you don't want to import numpy just for `where`, you can use pandas's `.np` accessor: `pd.np.where(...` – robroc Mar 06 '20 at 16:34
  • @robroc To be clear, one implicitly imports numpy when importing pandas, so there is no difference in memory or performance. The only difference would be the alias, `np` vs `pd.np`. You can confirm for yourself via: `import numpy as np` `import pandas as pd` `id(pd.np) == id(np)` which results in `True` – Alexander Mar 07 '20 at 05:45
1

Based on @vaishnav proposal above on iterating over the dataframe here is a working proposal:

for index, row in df.iterrows():
    if row.B > 0:
        df.loc[index, 'C'] = row.A / row.B
    else:
        df.loc[index, 'C'] = 0

Output:

   A  B         C
0  3  4  0.750000
1  0  4  0.000000
2  4  3  1.333333
3  2  1  2.000000
4  1  0  0.000000
5  0  2  0.000000
HeyMan
  • 1,529
  • 18
  • 32
0
df['C']=df.A.div(df.B.mask(df.B.lt(0),0)).fillna(0)
df
Out[89]: 
   A  B         C
0  1  3  0.333333
1  1  2  0.500000
2  0  0  0.000000
3  2  1  2.000000
4  4  2  2.000000

With apply lambda 

df['C']=df.apply(lambda x : x['A']/x['B'] if x['B']>0 else 0,1)
df
Out[93]: 
   A  B         C
0  1  3  0.333333
1  1  2  0.500000
2  0  0  0.000000
3  2  1  2.000000
4  4  2  2.000000
BENY
  • 317,841
  • 20
  • 164
  • 234
-1

Or you could just open a for loop.

for i,j in df['a'],df['b']:
    if j>0:
        df['c']=i/j
    else:
        df['c']=0.0
DennisLi
  • 3,915
  • 6
  • 30
  • 66
vaishnav krishnan
  • 1
  • You should test your code before you post it. This raises an ValueError: too many values to unpack (expected 2). I will post a separate answer for readability following your approach iterating over the df. – HeyMan Oct 22 '19 at 18:14