python pandas check value comming under range in another dataframe

Question

my first dataframe

dataframe1=

id   number  count1 count2  
1   5225    10       30 
2   2222    3        40
3   7121    30        50

second dataframe

dataframe2=

id  value1  value2 
1   4000     6000
3   2500     3300
3   7000     8000

output

id  value1  value2 number count1 count2
1   4000     6000  5225  10       30
3   7000     8000  7121  30       50

I tried

dataframe2['a']=(dataframe1['number']>= dataframe2['value1']) & (dataframe1['number'] <= dataframe2['value2'])

it gave True False values in column a, What I want to do is this: For each value in the Dataframe1 "number" column, search if it is equal to or between ANY of the "value1" and "value2" pair values of Dataframe2. Additionally, for this "number1" and "number2" pair values, its respective "id" must match the "id" in Dataframe. If this is all true, then I want to insert along with number, count1 and count2 in dataframe2

mboss · Accepted Answer · 2020-02-14 19:34:12Z

1

merged = df1.join(df2, on="id", how="inner")
result = merged.query("value1 <= number <= value2")
result

    number  count1  count2  value1  value2
id                                        
1     5225      10      30    4000    6000
3     7121      30      50    7000    8000

answered Feb 14, 2020 at 19:34

mboss

811 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hemant c naik Over a year ago

Thanks for your ansr mboss, without join on id can we do? let's assume there is no ID column

Josiah · Accepted Answer · 2020-02-14 19:39:43Z

1

First, let's create the dataframes from your example:

DataFrame 1

import pandas as pd  

d1 = {'id': [1, 2, 3],
     'number': [5225, 2222, 7121],
     'count1': [10, 3, 30],
     'count2': [30, 40, 50]}
df1 = pd.DataFrame(data = d1)
print(df1)

id  number  count1  count2
1   5225    10      30
2   2222    3       40
3   7121    30      50

DataFrame 2

d2 = {'id': [1, 3, 3],
     'value1': [4000, 2500, 7000],
     'value2': [6000, 3300, 8000]}
df2 = pd.DataFrame(data = d2)
print(df2)

id  value1  value2
1   4000    6000
3   2500    3300
3   7000    8000

One possible solution is to first join the two tables together:

df3 = df1.join(other = df2.set_index('id'),
              on = 'id')
print(df3)

id  number  count1  count2  value1  value2
1   5225    10      30      4000.0  6000.0
2   2222    3       40      NaN     NaN
3   7121    30      50      2500.0  3300.0
3   7121    30      50      7000.0  8000.0

Then filter by multiple conditions:

df3[(df3['number'] >= df3['value1']) & (df3['number'] <= df3['value2'])]


id  number  count1  count2  value1  value2
1   5225    10      30      4000.0  6000.0
3   7121    30      50      7000.0  8000.0

answered Feb 14, 2020 at 19:39

Josiah

263 bronze badges

5 Comments

hemant c naik Over a year ago

Thank you Josiah, worked well .. thank you for your answer

hemant c naik Over a year ago

hi Josaih, without join on ID can we do this one

hemant c naik Over a year ago

There is a one more value 2222 which is comming under value1(2500) and value2(3300) i want that type values also

Josiah Over a year ago

You can join without join on ID, however, the output is different than your desired outcome. You'll have ID value1 & value2 for numbers 5225 and 7121 will result in NaNs.

Josiah Over a year ago

To add value 2222 with value1 as 2500 and value2 as 3300: d2 = {'id': [1, 3, 3, 2], 'value1': [4000, 2500, 7000, 2500], 'value2': [6000, 3300, 8000, 3300]}

Collectives™ on Stack Overflow

python pandas check value comming under range in another dataframe

my first dataframe

second dataframe

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

my first dataframe

second dataframe

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related