mergind two dataframe according to intervals in python

Question

I have two df such as

df1

          Name  start1  end1
0  OK0100087.1     187   250
1  OK0100087.1     752   830
2  OK0100087.1     380   689

df2

            Name  start2  end2
0  OK0100087.1_0  0       375
1  OK0100087.1_1  376     750
2  OK0100087.1_3  751     1000

and I would like to merge the two df and get :

          Name  start1  end1 start2 end2 Name2
0  OK0100087.1     187   250  0     375  OK0100087.1_0
1  OK0100087.1     750   830  751   1000 OK0100087.1_3
2  OK0100087.1     380   689  752   830  OK0100087.1_3

so the idea is that I check for each start1-end1 value in wich interval start2-end2 it falls for instance;

          Name  start1  end1 start2 end2 Name2
0  OK0100087.1     187   250
1  OK0100087.1     752   830
2  OK0100087.1     380   689

row0: 187-250 is between [0-375] so I add this df2 line

          Name  start1  end1 start2 end2 Name2
0  OK0100087.1     187   250 0      375  OK0100087.1_0
1  OK0100087.1     752   830
2  OK0100087.1     380   689

row1: 752-830 is between [751-1000]so I add this df2 line

          Name  start1  end1 start2 end2 Name2
0  OK0100087.1     187   250  0     375  OK0100087.1_0
1  OK0100087.1     750   830  751   1000 OK0100087.1_3
2  OK0100087.1     380   689

row2: 380-689 is between [376-750]so I add this df2 line

          Name  start1  end1 start2 end2 Name2
0  OK0100087.1     187   250  0     375  OK0100087.1_0
1  OK0100087.1     750   830  751   1000 OK0100087.1_3
2  OK0100087.1     380   689  752   830  OK0100087.1_3

Use solution from dupe answers, if something not working for you let me know. Cross join is better if not huge data, else memory consuming. Iterate method working with any data, but slowier — jezrael, Sep 21 '20 at 07:47
Well I know how to merge two dataframe but here the difficulty is that it needs to merge depending on interval values given by two columns (start2 and end2) and I do not know how to deal with that... — chippycentra, Sep 21 '20 at 07:50
Unfortuantely need cross join and then filtering or loop solution — jezrael, Sep 21 '20 at 07:50
But how to cross join since Name in df1 and df2 are not exactly the same ? — chippycentra, Sep 21 '20 at 07:52

mergind two dataframe according to intervals in python

0 Answers0