Python Forum

Full Version: How to map two data frames based on multiple condition
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi,
I want to map df1 and grep the value from df2 if df1 column1(i.e ID1) value is between df2 column1(ID1) and column2(ID2). I sue below code, but I did not the answer


# importing pandas as pd
import pandas as pd
  
# creating dataframes
df1 = pd.DataFrame({'ID1': [1, 2, 3, 4], 
                    'Name': ['John', 'Tom', 'Simon', 'Jose']})
  
df2 = pd.DataFrame({'ID1': [1, 2, 3, 5],
                    'ID2': [11, 18, 23, 25],
                    'Class': ['A', 'G', 'M', 'D']})
  
# merging df1 and df2 with merge function
#df = pd.merge(df1, df2)
#print(df)
#print("="*15)
df = pd.merge(df2, df1)
#print(df)
print(f'df1:\n{df1}')
print("="*15)
print(f'df2:\n{df2}')

idx= df1.loc[(df1['ID1']>=df2['ID1']) & (df1['ID1']< df2['ID2'])]
print(f'len of index{len(idx)}')
print(f'len of index{idx}')
for example: df1 ['ID1'] is 1 which is between df2 ['ID1] & ['ID2'] 1 &11, so the corresponding value I want to grep is "A"