Conditional Merge: Single Indexer Out Of Bounds 'occured At Zero' Error With Pandas

I'm trying to do a conditional merge using data from two different dataframes to my original dataframe. The data for the two dataframes used for the conditional lookup are selecte

Solution 1:

The error happens because in some cases n_push_count.loc[n_push_count['mini_n'] < row['push_count']] (or the other one with e_) returns an empty dataframe. Indexing an empty dataframe with .iloc[-1] raises that IndexError.

This happens for example because the first row of your df2 has push_count equal to 0, and the values of mini_n column in e_push_count dataframe are all zeroes or positive integers.

You need to choose what to do in these cases, and that is a thing only you can decide.

A possibility could be to change the condition from lesser to lesser or equal: use <= instead of <.

In this case using your data sample you'll get:

   deal_type  push_count  push_count_score
0  Expansion           00.6427221  Expansion           30.1967212New20.2348483  Expansion           00.642722

But if you require that n_push_count['mini_n'] should be strictly smaller than row['push_count'], then you have no field for that value and you must modify the code to keep a null value. To do this, you could wrap the code of the function in a try except block:

def add_push_count(row):
    try:
        if row['deal_type'] =='New':
            return n_push_count.loc[n_push_count['mini_n'] <row['push_count']].iloc[-1]['percent_n']
        elif row['deal_type'] =='Expansion':
            return e_push_count.loc[e_push_count['mini_e'] <row['push_count']].iloc[-1]['percent_e']
    except IndexError:
        return np.NaN

Your df2 will be:

   deal_type  push_count  push_count_score
0  Expansion           0NaN1  Expansion           30.2602042        New           20.3184713  Expansion           0NaN