# Select value based on condition on dataframe

I have some data in data frame and would like to return a value based on specific conditions. It is highly time consuming.

I tried three methods:

Method 1: Without dataframe, this is the simple logic I have and it is super fast.

@numba.vectorize(['float64(float64, float64)'])
def Method1(a,b):
x=0.0
y=0.0
z=0.0

if (a <= 0.002):
x=0.5
y=2500
z=20000
elif (a <= 0.003):
x=0.3
y=2500
z=15000
elif (a <= 0.005):
x=0.2
y=1000
z=10000
else:
return 0.0

return min(max(x*b,y),z)

%timeit Method1(0.001,200000)


Method 2 - Input the condition data as a dataframe and run the function

dict = {'amin':[0.000,0.002,0.003],
'amax':[0.002,0.003,0.005],
'dfx':[0.5,0.3,0.2],
'dfy':[2500,2500,1000],
'dfz':[20000,15000,10000]}
df=pd.DataFrame(dict)

@numba.vectorize(['float64(float64, float64)'])
def Method2(a,b):
x=0.0
y=0.0
z=0.0

x=df[(a<=df.amax) & (a>=df.amin)]['dfx'].values
y=df[(a<=df.amax) & (a>=df.amin)]['dfz'].values
z=df[(a<=df.amax) & (a>=df.amin)]['dfy'].values

if (len(x)==0) or (len(y)==0) or (len(z)==0):
return 0.0
else:
return min(max(x[0]*b,y[0]),z[0])
%timeit Method2(0.001,200000)


Method 3 - looped the rows of the df

def Method3(a,b):
for index,row in df.iterrows():
if (mPD >= row['amin']) & (mPD <= row['amax']):
return min(max(row['dfx']*b,row['dfy']),row['dfz'])
return 0.0
%timeit Method3(0.001,200000)


Method 1 gets finished in 1.2 micro seconds

Method 2 takes 2.47 milli seconds (1000 times slower than the Method 1)

Method 3 takes ~80 micro seconds

Please help me how to improve the performance of Method 2 / 3. Also, please let me know why Method 3 is faster?

P.S. I plan to use Numba so cannot use lambda in the functions.

• A good title on Code Review would be a one-liner summary of the code, not a specific problem. Please read how-to-ask – janos May 24 '17 at 9:07

There is some overhead to numpy, and even more overhead to pandas. You won't be able to attain the performance of Method1 using pandas.

I'll comment on the methods one at a time:

Method1

1. There is no need to initialize x, y, and z.
2. You don't deal with the case where a is negative
3. For me, Method1 is twice as fast when I leave off the @numba decorator.

Method2

1. Don't name a dictionary dict! This is the name of the class. I've renamed your dict as param_dict below.

2. Again, there is no need to initialize x, y, and z.

3. You're running the two inequalities three times each.

Better would be to set valid_rows = (a<=df.amax) & (a>=df.amin) or similar.

def Method2a(a,b):
valid_rows = (a <= df.amax) & (a >= df.amin)
if not any(valid_rows):
return 0.0
x=df[valid_rows]['dfx'].values
y=df[valid_rows]['dfz'].values
z=df[valid_rows]['dfy'].values
return min(max(x[0]*b,y[0]),z[0])


With a setup where Method2 takes 2.25 ms, this takes 1.35 ms.

1. Accessing a dataframe at a boolean array is slower than at an index.

It's much better to find the first True index of valid_rows first.

def Method2b(a,b):
valid_rows = (a <= df.amax) & (a >= df.amin)
if not any(valid_rows):
return 0.0
idx = np.where(valid_rows)[0][0]
x=df['dfx'].iat[idx]
y=df['dfz'].iat[idx]
z=df['dfy'].iat[idx]
return min(max(x*b,y),z)


This takes 531 µs

1. Drilling down into this, just the first line takes 472 µs (try it with the method truncated after the first line, not returning anything)! That's where we can improve.

We don't really need two sets of comparisons. There's enough information in df.amin together with the final value of df.amax:

def Method2c(a,b):
idx = np.searchsorted(df.amin.values, a) - 1
# special case if a == df.amin.iat[0]
if idx < 0:
if a == df.amin.iat[0]:
idx = 0
else:
return 0.0
# special case if a is bigger than all values in df.amin
elif idx == df.shape[0] - 1:
if a > df.amax.iat[idx]:
return 0.0
x=df['dfx'].iat[idx]
y=df['dfz'].iat[idx]
z=df['dfy'].iat[idx]
return min(max(x*b,y),z)


This takes 38.6 µs. Note the idea is closer to your approach in Method1.

This is about as well as we can hope to do with pandas, as the bottle neck is now actually the three accesses:

    x=df['dfx'].iat[idx]
y=df['dfz'].iat[idx]
z=df['dfy'].iat[idx]


which actually takes 24.6 µs by itself!

Edit: Actually, using .values instead of .iat saves a good amount here, cutting the whole run time down to 19.3 µs for:

def Method2c_values(a,b):
idx = np.searchsorted(df['amin'].values, a) - 1
# special case if a == df['amin'].values[0]
if idx < 0:
if a == df['amin'].values[0]:
idx = 0
else:
return 0.0
# special case if a is bigger than all values in df.amin
elif idx == df.shape[0] - 1:
if a > df['amax'].values[idx]:
return 0.0
x=df['dfx'].values[idx]
y=df['dfz'].values[idx]
z=df['dfy'].values[idx]
return min(max(x*b,y),z)


If we go back to the dictionary (which I'm calling param_dict) we can speed it up quite a bit:

def Method2d(a,b):
idx = np.searchsorted(param_dict['amin'], a) - 1
# special case if a == df.amin.iat[0]
if idx < 0:
if a == param_dict['amin'][0]:
idx = 0
else:
return 0.0
# special case if a is bigger than all values in df.amin
elif idx == len(param_dict['amin']) - 1:
if a > param_dict['amax'][idx]:
return 0.0
x=param_dict['dfx'][idx]
y=param_dict['dfz'][idx]
z=param_dict['dfy'][idx]
return min(max(x*b,y),z)


This takes 6.91 µs. Now the bottleneck is back to the first line, which is taking 5.66 µs by itself.

We can rewrite this to do np.searchsorted ourselves, with the extra logic for the special cases worked in:

def Method2e(a,b):
idx = 0
for value in param_dict['amin']:
if a < value:
# if idx is 0, we're out of bounds
if not idx:
return 0.0
break
elif a == value:
# if idx is 0, we need to adjust by 1
if not idx:
idx = 1
break
idx += 1
else:
# a is larger than every element of param_dict['amin']
if a > param_dict['amax'][-1]:
return 0.0
idx -= 1
x=param_dict['dfx'][idx]
y=param_dict['dfz'][idx]
z=param_dict['dfy'][idx]
return min(max(x*b,y),z)


This is 823 ns. We could tweak mildly to put the idx == 0 part out of the main loop and other such things, but I'll leave it as is.

Method3

This method is fine, except that

def Method3(a,b):
for index,row in df.iterrows():
pass


takes 112 µs for me.

• Some comments here: The small nature of df helped some of these; for example, we wouldn't want to use a linear search if df had more columns. Also, despite the performance loss, you may want to stick with one of the pandas solutions just for code clarity. If you're looking for more performance, there are extremes you can go to, like switching languages, but that doesn't mean that's the right choice. – aes May 25 '17 at 5:57