Say I have a dataframe:
a b
-5 1
4 -2
0 0
1 0
0 3
And I want to divide: x = df.a / df.b
Obviously I'll get division by zero errors or inf as a result. But I want to use such an algorithm for division (pseudo code):
def CalcRatio(a, b):
ratio = a / b
if (isinf(ratio) or isnan(ratio)):
ratio = (1 + a) / (1 + b)
return ratio
How can I do this with pandas? Thanks.
You can use np.isinf and np.isnan in your code to do what you want using apply row-wise:
In [207]:
def CalcRatio(a, b):
ratio = a / b
if (np.isinf(ratio) or np.isnan(ratio)):
ratio = (1 + a) / (1 + b)
return ratio
df.apply(lambda x: CalcRatio(x['a'],x['b']), axis=1)
Out[207]:
0 -5.0
1 -2.0
2 1.0
3 2.0
4 0.0
dtype: float64
A vectorised method would be to use np.where and pass the conditions in the True case to return the alternate result, otherwise to perform division as before:
In [208]:
np.where(np.isinf(df['a']/df['b']) | pd.isnull(df['a']/df['b']), (1 + df['a']) / (1 + df['b']), df['a']/df['b'])
Out[208]:
array([-5., -2., 1., 2., 0.])
timings
For a 5K row df:
In [213]:
%timeit df.apply(lambda x: CalcRatio(x['a'],x['b']), axis=1)
%timeit np.where(np.isinf(df['a']/df['b']) | pd.isnull(df['a']/df['b']), (1 + df['a']) / (1 + df['b']), df['a']/df['b'])
1 loops, best of 3: 225 ms per loop
1000 loops, best of 3: 1.32 ms per loop
We can see here that the vectorised method scales much better than apply which is just iterating over each row, here ~170x faster, I expect the numpy method to scale much better for large datasets
new timings
In [218]:
%%timeit
d1 = df.a / df.b
d2 = df.a.add(1) / df.b.add(1)
d1.replace(np.inf, np.nan).fillna(d2)
1000 loops, best of 3: 1.06 ms per loop
In [219]:
%%timeit
d1 = df.add(df.b == 0, 0)
d1.a / d1.b
1000 loops, best of 3: 691 µs per loop
The above are @piRSquared's answers which are noticeably faster
You can take this approach
d1 = df.a / df.b
d2 = df.a.add(1) / df.b.add(1)
d1.replace(np.inf, np.nan).fillna(d2)
0 -5.0
1 -2.0
2 1.0
3 2.0
4 0.0
dtype: float64
Another approach
df.b == 0 evaluates to True when b is zero (obviously). But when you add this column, it only adds 1 to rows where b is zero. Then you do the division.
d1 = df.add(df.b == 0, 0)
d1.a / d1.b
1000 loops, best of 3: 346 µs per loop +1 about 4X faster, this is timed just on the replace call though, if we time the whole thing: 1000 loops, best of 3: 1.06 ms per loop so marginally faster but faster still by 0.30ms1000 loops, best of 3: 691 µs per loop even faster for last one