merge_asof with multiple columns and forward direction

user_11986167 0
Viewed 527

I have 2 dataframes:

q = pd.DataFrame({'ID':[700,701,701,702,703,703,702],'TX':[0,0,1,0,0,1,1],'REF':[100,120,144,100,103,105,106]})

    ID  TX  REF
0   700 0   100
1   701 0   120
2   701 1   144
3   702 0   100
4   703 0   103
5   703 1   105
6   702 1   106

and

p  = pd.DataFrame({'ID':[700,701,701,702,703,703,702,708],'REF':[100,121,149,100,108,105,106,109],'NOTE':['A','B','V','V','T','A','L','M']})


ID  REF NOTE
0   700 100 A
1   701 121 B
2   701 149 V
3   702 100 V
4   703 108 T
5   703 105 A
6   702 106 L
7   708 109 M

I wish to merge p with q in such way that ID are equals AND the REF is exact OR higher.

Example 1:

for p: ID=700 and REF=100 and 
for q: ID=700 and RED=100 So that's a clear match!

Example 2 for p:

1   701 0   120
2   701 1   144

they would match to:

1   701 121 B
2   701 149 V

this way:

1   701 0   120  121  B    121 is just after 120 
2   701 1   144  149  V    149 comes after 144

When I use the below code NOTE: I only indicate the REF which is wrong. Should be ID AND REF:

p = p.sort_values(by=['REF'])
q = q.sort_values(by=['REF'])
pd.merge_asof(p, q, on='REF', direction='forward').sort_values(by=['ID_x','TX'])

I get this problem:

enter image description here

My expected result should be something like this:

    ID  TX  REF REF_2   NOTE
0   700 0   100 100     A
1   701 0   120 121     B
2   701 1   144 149     V
3   702 0   100 100     V
4   703 0   103 108     T
5   703 1   105 105     A
6   702 1   106 109     L
pandas merge python
1 Answers

Does this work?

pd.merge_asof(q.sort_values(['REF', 'ID']), 
              p.sort_values(['REF', 'ID']), 
              on='REF', 
              direction='forward', 
              by='ID').sort_values('ID')

Output:

    ID  TX  REF NOTE
0  700   0  100    A
5  701   0  120    B
6  701   1  144    V
1  702   0  100    V
4  702   1  106    L
2  703   0  103    A
3  703   1  105    A
user_63615310
Related