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 &lt;= 0.002):
        x=0.5
        y=2500
        z=20000
    elif (a &lt;= 0.003):
        x=0.3
        y=2500
        z=15000
    elif (a &lt;= 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&lt;=df.amax) &amp; (a&gt;=df.amin)]['dfx'].values
    y=df[(a&lt;=df.amax) &amp; (a&gt;=df.amin)]['dfz'].values
    z=df[(a&lt;=df.amax) &amp; (a&gt;=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 &gt;= row['amin']) &amp; (mPD &lt;= 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.
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


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


Method2


Don't name a dictionary dict! This is the name of the class. I've renamed your dict as param_dict below.
Again, there is no need to initialize x, y, and z.
You're running the two inequalities three times each.


Better would be to set valid_rows = (a&lt;=df.amax) &amp; (a&gt;=df.amin) or similar.

def Method2a(a,b):
    valid_rows = (a &lt;= df.amax) &amp; (a &gt;= 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.


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 &lt;= df.amax) &amp; (a &gt;= 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


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 &lt; 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 &gt; 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 &lt; 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 &gt; 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 &lt; 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 &gt; 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 &lt; 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 &gt; 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.