Pandas extract row in range

user_221270 0
Viewed 90

I have two pandas tables:

Table1
PosA    PosB    PosC
1       100     110
1       300     350
4       100     120

Table2
PosA   PosD
1      105
2      305
4      115
5      308

I want to extract all information from Table1, if PosD in Table2 is in range PosB and PosC AND PosA of Table1 and Table2 match extactly.

So the correct output is:

PosA   PosB   PosC   PosD
1      100    110    105
4      100    120    115

How to merge this two tables, according this rules?

pandas python
2 Answers

Here df1 is your Table1 and df2 is your Table 2

import pandas as pd

Firstly make use of reindex() method in 'df1' so as to make its length equal to 'df2':-

df1=df1.reindex(df2.index)

then check your condition:-

mask=(df2['PosD'].between(df1['PosB'],df1['PosC'])) & (df2['PosA']==df1['PosA'])

And now merge them by using merge() method in pandas and pass the mask in df1 inside merge() method:-

result=pd.merge(df1[mask],df2,on='PosA').astype(int)

Now if you print **result** then you will get your expected output:-

    PosA    PosB    PosC    PosD
0   1       100     110     105
1   4       100     120     115
user_142898920

Try this:

df = pd.merge(table1, table2)
df[df['PosD'].between(df['PosB'], df['PosC'])]

Output:

    PosA    PosB    PosC    PosD
0    1      100     110     105
2    4      100     120     115
user_131313200
Related