Pandas conditional lookup based on columns from a different dataframe-CodePudding

I have searched but found no answers for my problem. My first dataframe looks like:

df1

Item    Value
1   23
2   3
3   45
4   65
5   17
6   6
7   18
…   … 
500 78
501 98

and the second lookup table looks like

df2

L1  H1  L2  H2  L3  H3  L4  H4  L5  H5  Name
1   3   5   6   11  78  86  88  90  90  A
4   4   7   10  79  85  91  99  110 120 B
89  89  91  109 0   0   0   0   0   0   C
...

What I am trying to do is to get Name from df2 to df1 when Item in df1 falls between the Low (L) and High (H) columns. Something (which does not work) like:

df1[Name]=np.where((df1['Item']>=df2['L1'] & df1['Item']<=df2['H1'])|
(df1['Item']>=df2['L2'] & df1['Item']<=df2['H2']) |
(df1['Item']>=df2['L3'] & df1['Item']<=df2['H3']) |
(df1['Item']>=df2['L4'] & df1['Item']<=df2['H4']) |
(df1['Item']>=df2['L5'] & df1['Item']<=df2['H5']) |
(df1['Item']>=df2['L6'] & df1['Item']<=df2['H6']), df2['Name'], "Other")

So that the result would be like:

Item    Value   Name
1   23  A
2   3   A
3   45  A
4   65  B
5   17  A
6   6   A
7   18  A
…   …   …
500 78  K
501 98  Other

If you have any guidance for my problem to share, I would much appreciate it! Thank you in advance!

CodePudding user response：

Try:

Transform df2 using wide_to_long
Create lists of numbers from "L" to "H" for each row using apply and range
explode to have one value in each row
map each "Item" in df1 using a dict created from ranges with the structure {value: name}

ranges = pd.wide_to_long(df2, ["L","H"], i="Name", j="Subset")
ranges["values"] = ranges.apply(lambda x: list(range(x["L"], x["H"] 1)), axis=1)
ranges = ranges.explode("values").reset_index()

df1["Name"] = df1["Item"].map(dict(zip(ranges["values"], ranges["Name"])))

>>> df1
   Item  Value Name
0     1     23    A
1     2      3    A
2     3     45    A
3     4     65    B
4     5     17    A
5     6      6    A
6     7     18    B
7   500     78  NaN
8   501     98  NaN