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           0          0.642722
1  Expansion           3          0.196721
2        New           2          0.234848
3  Expansion           0          0.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           0               NaN
1  Expansion           3          0.260204
2        New           2          0.318471
3  Expansion           0               NaN