Finding intervals in pandas dataframe based on values in another dataframe

Question

I have two data frames. One dataframe (A) looks like:

Name.   gender     start_coordinate    end_coordinate    ID      
Peter     M             30                  150           1      
Hugo      M            4500                6000           2      
Jennie    F             300                 700           3   

The other dataframe (B) looks like

ID_sim.  position      string      
  1         89            aa      
  4         568            bb     
  5        938437         cc

I want to accomplish two tasks here:

1. I want to get a list of indices for rows (from dataframe B) for which position column falls in the interval (specified by start_coordinate and end_coordinate column) in dataframe A. The result for this task will be:

lst = [0,1]. ### because row 0 of B falls in interval of row 1 in A and row 1 of B falls in interval of row 3 of A. 

2. The indices that I get from task 1, I want to keep it from dataframe B to create a new dataframe. Thus, the new dataframe will look like:

position     string          
89             aa 
568            bb 

I used .between() to accomplish this task. The code is as follows:

lst=dfB[dfB['position'].between(dfA.loc[0,'start_coordinate'],dfA.loc[len(dfA)-1,'end_coordinate'])].index.tolist()
result=dfB[dfB.index.isin(lst)]
result.shape

However, when I run this piece of code I get the following error:

KeyError: 0

What could possibly be raising this error? And how can I solve this?

Answer 1

We can try numpy broadcasting here

s, e = dfA[['start_coordinate', 'end_coordinate']].to_numpy().T
p = dfB['position'].to_numpy()[:, None]

dfB[((p >= s) & (p <= e)).any(1)]

---

   ID_sim.  position string
0        1        89     aa
1        4       568     bb