Conditional Merging: I wish to perform conditional merging below is my dataframe named df. I wish merge reference df df_reference with df. I wish to merge over weight so for row 1 weight is 62.2, this need to be merged with weight 60 and weight 65

import pandas as pd
import io

data = """
name    weight
Arash   62.2
Bash    98.2
Kim     88.2
Dim     92.1
Ghst    63.2
"""

df = pd.read_table(io.StringIO(data), delim_whitespace=True)

Reference table

reference = """
weight  performance
60   100
65    95
70    93
75    92
80    90
85    85
90    79
95    75
"""

df_reference = pd.read_table(io.StringIO(reference), delim_whitespace=True)




output = """
name    weight   weight_l    weight_r   performance_l  performance_r
Arash   62.2       60          65          100           95
Bash    91.2       90          95           79           75
Kim     88.2       85          90           85           79 
Dim     92.1       90          95           79           75
Ghst    63.2       60          65          100           95
"""

df_expectation = pd.read_table(io.StringIO(output), delim_whitespace=True)

I am not sure if this kind of merging is even possible or not in pandas. I have only done merge over exact match so I don't have any attempt on this ...

Read more here: https://stackoverflow.com/questions/66278090/conditional-merge-between-two-pandas-df-when-the-column-dont-match-exactly