[Python] How to map scores from one table to another when the cell contains operators

I performed OLS regression on a dataset and I have the predicted Diagnostic_Score but the mapping table (norms) can have two operators - e.g. >= and <=. Is there a way to map the predicted score to the percentile?

My first thought was to map the scores that I can match and the ones that do not match I know must to be associated with a percentile that has an operator in the Diagnostic_Score column. I could then use numpy.select and create the conditions and choices. Does that approach make sense or is there an easier way to map the test percentile to the predicted score without having to manually create a 108 conditions and 108 choices for numpy.select?

Here is a sample

import pandas as pd

d = {'percentile': {0: 1, 1: 1, 2: 1, 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 1, 9: 2, 10: 2, 11: 2, 12: 2, 13: 2, 14: 2, 15: 2, 16: 2, 17: 2}, 'Subject': {0: 'Math', 1: 'Math', 2: 'Math', 3: 'Math', 4: 'Math', 5: 'Math', 6: 'Math', 7: 'Math', 8: 'Math', 9: 'Math', 10: 'Math', 11: 'Math', 12: 'Math', 13: 'Math', 14: 'Math', 15: 'Math', 16: 'Math', 17: 'Math'}, 'Term': {0: 'Fall', 1: 'Fall', 2: 'Fall', 3: 'Fall', 4: 'Fall', 5: 'Fall', 6: 'Fall', 7: 'Fall', 8: 'Fall', 9: 'Fall', 10: 'Fall', 11: 'Fall', 12: 'Fall', 13: 'Fall', 14: 'Fall', 15: 'Fall', 16: 'Fall', 17: 'Fall'}, 'Grade_Level': {0: 0, 1: 1, 2: 2, 3: 3, 4: 4, 5: 5, 6: 6, 7: 7, 8: 8, 9: 0, 10: 1, 11: 2, 12: 3, 13: 4, 14: 5, 15: 6, 16: 7, 17: 8}, 'Diagnostic_Score': {0: '<=296', 1: '<=322', 2: '<=352', 3: '<=372', 4: '<=390', 5: '<=405', 6: '<=412', 7: '<=423', 8: '<=434', 9: '297', 10: '323', 11: '353', 12: '373', 13: '391', 14: '406', 15: '413', 16: '424', 17: '435'}}

norms = pd.DataFrame(d)

df = pd.DataFrame({'Term': ['Fall', 'Fall', 'Fall', 'Fall'],
'Subject': ['Math', 'Math', 'Math', 'Math'],
'Grade_Level': [0, 3, 5, 7],
'Predicted Score': [290, 300, 406, 424]})


My expected output is

Term Subject Grade_Level Predicted Score Percentile
0 Fall Math 0 290 1
1 Fall Math 3 300 1
2 Fall Math 5 406 2
3 Fall Math 7 424 2


norms table

percentile Subject Term Grade_Level Diagnostic_Score
0 1 Math Fall 0 <=296
1 1 Math Fall 1 <=322
2 1 Math Fall 2 <=352
3 1 Math Fall 3 <=372
4 1 Math Fall 4 <=390
5 1 Math Fall 5 <=405
6 1 Math Fall 6 <=412
7 1 Math Fall 7 <=423
8 1 Math Fall 8 <=434
9 2 Math Fall 0 297
10 2 Math Fall 1 323
11 2 Math Fall 2 353
12 2 Math Fall 3 373
13 2 Math Fall 4 391
14 2 Math Fall 5 406
15 2 Math Fall 6 413
16 2 Math Fall 7 424
17 2 Math Fall 8 435
...
99 Math Spring 8 >=585

Continue reading...