CODEWITHSUNDEEP
pandas
peter_parker
peter_parker

Reputation: 133

Check if a value in one column in one dataframe is within the range between values in two columns in another dataframe

I have two data frames

df1 = pd.DataFrame({'chr':[1,1],'pos':[100, 200]})  
df2 = pd.DataFrame({'chr':[1,1,2],'start':[90,110,90],'stop':[110,120,110]})

I want to make a new dataframe with info from both dataframes if:
the value in df1['chr'] is the same is df2['chr'] and
the value df['pos'] is between the values in df2['start'] and df['stop']

From the dataframe above the result should be: 

chr  pos    start    stop  
1    100    90    110

Thank you for any help!

Upvotes: 1

Views: 493

Answers (2)

SUMANTH_09
SUMANTH_09

Reputation: 36

You can try this :

df = df1.merge(df2,on='chr',how='left')
df.loc[(df['pos'] >= df['start']) & (df['pos'] <= df['stop'])]

Upvotes: 1

anky
anky

Reputation: 75080

You can use df.merge() followed by series.between():

m=df1.merge(df2,on='chr',how='left')
m.loc[m.pos.between(m.start,m.stop)]

   chr  pos  start  stop
0    1  100     90   110

Upvotes: 1

Related Questions