determine the range of a value using a look up table

user_7455917 0
Viewed 564

I have a df with numbers:

numbers = pd.DataFrame(columns=['number'], data=[
50,
65,
75,
85,
90
])

and a df with ranges (look up table):

ranges = pd.DataFrame(
columns=['range','range_min','range_max'],
data=[
['A',90,100],
['B',85,95],
['C',70,80]
]
)

I want to determine what range (in second table) a value (in the first table) falls in. Please note ranges overlap, and limits are inclusive. Also please note the vanilla dataframe above has 3 ranges, however this dataframe gets generated dynamically. It could have from 2 to 7 ranges.

Desired result:

numbers = pd.DataFrame(columns=['number','detected_range'], data=[
[50,'out_of_range'],
[65, 'out_of_range'],
[75,'C'],
[85,'B'],
[90,'overlap'] * could be A or B *
])

I solved this with a for loop but this doesn't scale well to a big dataset I am using. Also code is too extensive and inelegant. See below:

numbers['detected_range'] = nan
for i, row1 in number.iterrows():
    for j, row2 in ranges.iterrows():
        if row1.number<row2.range_min and row1.number>row2.range_max:
             numbers.loc[i,'detected_range'] = row1.loc[j,'range']
        else if (other cases...):
              ...and so on...

How could I do this?

pandas python
2 Answers

You can use a bit of numpy vectorial operations to generate masks, and use them to select your labels:

import numpy as np

a = numbers['number'].values   # numpy array of numbers
r = ranges.set_index('range')  # dataframe of min/max with labels as index

m1 = (a>=r['range_min'].values[:,None]).T  # is number above each min
m2 = (a<r['range_max'].values[:,None]).T   # is number below each max
m3 = (m1&m2)                               # combine both conditions above
# NB. the two operations could be done without the intermediate variables m1/m2

m4 = m3.sum(1)                             # how many matches?
                                           # 0 -> out_of_range
                                           # 2 -> overlap
                                           # 1 -> get column name

# now we select the label according to the conditions
numbers['detected_range'] = np.select([m4==0, m4==2], # out_of_range and overlap
                                      ['out_of_range', 'overlap'],
                                      # otherwise get column name
                                      default=np.take(r.index, m3.argmax(1))
                                     )

output:

   number detected_range
0      50   out_of_range
1      65   out_of_range
2      75              C
3      85              B
4      90        overlap

edit:

It works with any number of intervals in ranges

example output with extra['D',50,51]:

   number detected_range
0      50              D
1      65   out_of_range
2      75              C
3      85              B
4      90        overlap
user_163434640

Pandas IntervalIndex fits in here; however, since your data has overlapping points, a for loop is the approach I'll use here (for unique, non-overlapping indices, pd.get_indexer is a fast approach):

intervals = pd.IntervalIndex.from_arrays(ranges.range_min, 
                                         ranges.range_max, 
                                         closed='both')

box = []
for num in numbers.number:
    bools = intervals.contains(num)
    if bools.sum()==1:
        box.append(ranges.range[bools].item())
    elif bools.sum() > 1:
        box.append('overlap')
    else:
        box.append('out_of_range')

numbers.assign(detected_range = box)
 
   number detected_range
0      50   out_of_range
1      65   out_of_range
2      75              C
3      85              B
4      90        overlap
user_71757130
Related