如何从另一个数据框中获取值的行列名称

给定一个范围表(start, end):

name     blue         green          yellow        purple              
a        1, 5                        654, 678       11, 15
b                     88761, 88776  
c        1211, 1215                  38, 47    
d        89, 95                                     1567, 1578

和一个数据框df:

Supplier        colour                   
Abi             1                               
John            678          
Smith           120               
Tim             1570 
Don             87560                       

我怎样才能过滤df仅包含其值在colour列是否在表中提供的范围内？

并创建第三列，将列中的值关联起来colour to the row-column范围表中的名称。我想要决赛df看起来像这样：

Supplier        colour    Source                  
Abi             1         a-blue                      
John            678       a-yellow                   
Tim             1570      d-purple

谢谢你！

s = df2.stack()
print(s)

Gives:

a  1,      name          5
           blue       654,
           green       678
           yellow      11,
           purple       15
b  88761,  name      88776
c  1211,   name       1215
           blue        38,
           green        47
d  89,     name         95
           blue      1567,
           green      1578
dtype: object

And:

s = df2.stack()
s = s.str.split("[, ]+").apply(lambda x: pd.Interval(*map(int, x), closed="both"))
print(s)

Gives:

Traceback (most recent call last):
  File "/Users/PycharmProjects/sup.py", line 12, in <module>
    s = s.str.split("[, ]+").apply(lambda x: pd.Interval(*map(int,x), closed="both"))
  File "/Users/.conda/envs/lib/python3.8/site-packages/pandas/core/series.py", line 4200, in apply
    mapped = lib.map_infer(values, f, convert=convert_dtype)
  File "pandas/_libs/lib.pyx", line 2402, in pandas._libs.lib.map_infer
  File "/Users/PycharmProjects/sup.py", line 12, in <lambda>
    s = s.str.split("[, ]+").apply(lambda x: pd.Interval(*map(int,x), closed="both"))
TypeError: 'float' object is not iterable

一种方法是制作一个series with pd.IntervalIndex :

s = df2.stack()
s = s.str.split("[, ]+").apply(lambda x: pd.Interval(*map(int, x), closed="both"))
s.index = ["-".join(i) for i in s.index]
s = pd.Series(s.index, index=s)

这样你就有了：

[1, 5]              a-blue
[654, 678]        a-yellow
[11, 15]          a-purple
[88761, 88776]     b-green
[1211, 1215]        c-blue
[38, 47]          c-yellow
[89, 95]            d-blue
[1567, 1578]      d-purple
dtype: object

Now you map它到原来的df and dropna:

df["Source"] = df["colour"].map(s)
df.dropna()

Output:

  Supplier  colour    Source
0      Abi       1    a-blue
1     John     678  a-yellow
3      Tim    1570  d-purple