How to lookup a match in two data frames

November 23, 2023

I have two data frame df and df1. I would like to lookup a match (df) in row 0 to 5 in df1 and only return the matching values and range.
For example below

import collections
import numpy as np
import pandas as pd

df = pd.DataFrame([[2],
                [23],
                [14],
                [33],
                [35],
                [26]], 
                columns = ['Col1'])


df1 = pd.DataFrame([[1,2,4,5,6,8,-19],
                [5,6,20,22,23,34,50],
                [8,12,13,34,45,46,23],
                [9,10,14,29,32,33,-10],
                [1,22,13,23,33,35,2],
                [1,6,7,8,9,10,6],
                [0,2,3,5,6,8,23]], 
                columns = ['Num1','Num2','Num3','Num4','Num5','Num6','Range'])

I would like my result like this.

result = pd.DataFrame([[2,-19],
                [2,23],
                [23,50],
                [23,2],
                [14,-10],
                [33,-10],
                [33,2],
                [35,2]], 
                columns = ['Match','Range'])

>Solution :

Code:

df2 = df1.melt(id_vars='Range', value_vars=['Num1', 'Num2', 'Num3', 'Num4', 'Num5', 'Num6']) #reshape for easy filter
result = df2[df2['value'].isin(df['Col1'])] # rows based filter
result = result.rename(columns={'value': 'Match'})
print(result[['Match', 'Range']])

Output:

    Match  Range
7       2    -19
13      2     23
17     14    -10
25     23      2
29     23     50
32     33      2
38     33    -10
39     35      2