Pandas table lookup

Question

I have a pandas lookup table which looks like this

Grade   Lower_Boundary  Upper_Boundary
1   -110    -96
2   -96 -91
3   -91 -85
4   -85 -81
5   -81 -77
6   -77 -72
7   -72 -68
8   -68 -63
9   -63 -58
10  -58 -54
11  -54 -50
12  -50 -46
13  -46 -42
14  -42 -38
15  -38 -34
16  -34 -28
17  -28 -18
18  -18 -11
19  -11 -11
20  -11 -9

I have another pandas dataframe that looks contains score. I want to assign 'Grade' to the score column, by looking up the look up table. So based on which interval of lower and upper boundary the score falls, the grade should be assigned from that row in the lookup table. Is there a way to do it without typing a bunch of if then else statements? I am thinking just of excel's index match.

Score   Grade
-75 6
-75 6
-60 9
-66 8
-66 8
-98 1
-60 9
-82 4
-70 7
-60 9
-60 9
-60 9
-56 10
-70 7
-70 7
-70 7
-66 8
-56 10
-66 8
-66 8

Answer 1

A one-line solution (I call your lookup table lookup):

df['Score'].apply(lambda score: lookup['Grade'][(lookup['Lower_Boundary'] <= score) & (lookup['Upper_Boundary'] > score)].values[0])

---

Explanation:

For a given score, here is how to find the grade:

score = -75
match = (lookup['Lower_Boundary'] <= score) & (lookup['Upper_Boundary'] > score)
grade = lookup['Grade'][match]

This return a series of length 1. You can get its value with, for instance:

grade.values[0]

All you need to do is apply the above to the score column. If you want a one-liner, use a lambda function:

df['Score'].apply(lambda score: lookup['Grade'][(lookup['Lower_Boundary'] <= score) & (lookup['Upper_Boundary'] > score)].values[0])

Otherwise the following would be more readable:

def lookup_grade(score):
    match = (lookup['Lower_Boundary'] <= score) & (lookup['Upper_Boundary'] > score)
    grade = lookup['Grade'][match]
    return grade.values[0]

df['Score'].apply(lookup_grade)

This approach would also make it easier to deal with cases when no match is found.