generic scoring module using python pandas
Question: Hi I'm trying to develop a generic scoring module for grading students based
on variety of attributes. I'm trying to develop a generic method using python
pandas Input: An input data frame with student ID and UG Major and attributes
for scoring (I called df_input) An input ref. data frame that contains scoring
params

Process: Based on the variable type, developing a process to calculate scores
for each attribute

Output: Input data frame with added cols that capture the attribute score
Example:

df_input

+

    
    
    ------------+-----------+----+------------+-----+------+
    | STUDENT_ID | UG_MAJOR  | C1 |     C2     | C3  |  C4  |
    +------------+-----------+----+------------+-----+------+
    |        123 | MATH      | A  | 8000-10000 | 12% | 9000 |
    |        234 | ALL_OTHER | B  | 1500-2000  | 10% | 1500 |
    |        345 | ALL_OTHER | A  | 2800-3000  | 8%  | 2300 |
    |        456 | ALL_OTHER | A  | 8000-10000 | 12% | 3200 |
    |        980 | ALL_OTHER | C  | 1000-2500  | 15% | 2700 |
    +------------+-----------+----+------------+-----+------+
    

df_ref +

    
    
    ---------+---------+---------+
    | REF_COL | REF_VAL | REF_SCR |
    +---------+---------+---------+
    | C1      | A       |      10 |
    | C1      | B       |      20 |
    | C1      | C       |      30 |
    | C1      | NULL    |       0 |
    | C1      | MISSING |       0 |
    | C1      | A       |      20 |
    | C1      | B       |      30 |
    | C1      | C       |      40 |
    | C1      | NULL    |      10 |
    | C1      | MISSING |      10 |
    | C2      | <1000   |       0 |
    | C2      | >1000   |      20 |
    | C2      | >7000   |      30 |
    | C2      | >9500   |      40 |
    | C2      | MISSING |       0 |
    | C2      | NULL    |       0 |
    | C3      | <3%     |       5 |
    | C3      | >3%     |      10 |
    | C3      | >5%     |     100 |
    | C3      | >7%     |     200 |
    | C3      | >10%    |     300 |
    | C3      | NULL    |       0 |
    | C3      | MISSING |       0 |
    | C4      | <5000   |      10 |
    | C4      | >5000   |      20 |
    | C4      | >10000  |      30 |
    | C4      | >15000  |      40 |
    +---------+---------+---------+
    
    +------------+-----------+----+------------+-----+------+--------+--------+--------+---------+
    | Req.Output |           |    |            |     |      |        |        |        |         |
    +------------+-----------+----+------------+-----+------+--------+--------+--------+---------+
    | STUDENT_ID | UG_MAJOR  | C1 | C2         | C3  | C4   | C1_SCR | C2_SCR | C3_SCR | TOT_SCR |
    | 123        | MATH      | A  | 8000-10000 | 12% | 9000 |        |        |        |         |
    | 234        | ALL_OTHER | B  | 1500-2000  | 10% | 1500 |        |        |        |         |
    | 345        | ALL_OTHER | A  | 2800-3000  | 8%  | 2300 |        |        |        |         |
    | 456        | ALL_OTHER | A  | 8000-10000 | 12% | 3200 |        |        |        |         |
    | 980        | ALL_OTHER | C  | 1000-2500  | 15% | 2700 |        |        |        |         |
    +------------+-----------+----+------------+-----+------+--------+--------+--------+---------+
    

I want to see if any thing like a function be developed to accomplish this

Thank you Pari


Answer: If I understand the question correctly, you are trying to store a collection
of rules in `df_ref` that are to be applied to `df_input` to generate scores.
While this certainly can be done, you should make sure that your rules are
well defined. This would also guide you in writing the corresponding scoring
function.

For instance, suppose one of the students gets a value of `10000` in column
`C3`. `10000` is larger than `1000`, `7000` and `9500`. This means that the
score is ambiguous. Suppose you want to choose the highest of all scores from
this particular column. Then, you need another table specifying the choice
rule for each column when multiple scores are selected.

Second, you should think about the type of Python variable stored in 'REF_VAL'
column. If `>7000` is a string, you would have to do extra work to determine
the score. Consider storing this as `7000` instead and specifying comparison
operator elsewhere.

Finally, looking at your current rules, there seems to be a pattern. Each
score is associated with `NULL`, `MISSING` or a range cutoff. This can be
captured as follows:

    
    
    import pandas as pd
    import numpy as np
    from itertools import dropwhile
    
    # stores values and scores for special values and cutoff values
    sample_range_rule = {
        'MISSING' : 0,
        'NULL'    : 0,
        'VALS'    : [
            (0, 0),
            (10, 50),
            (70, 75),
            (90, 100),
            (100, 100)
        ]
    }
    
    # takes a dict with rules and produces a scoring function
    def getScoringFunction(range_rule):
        def score(val):
            if val == 'MISSING':
                return range_rule['MISSING']
            elif val == 'NULL':
                return range_rule['NULL']
            else:
                return dropwhile(lambda (cutoff, score): cutoff < val,
                    range_rule['VALS']).next()[1]
        return score
    
    sample_scoring_function = getScoringFunction(sample_range_rule)
    
    for test_value in ['MISSING', 'NULL', 0, 12, 55, 66, 99]:
        print 'Input', test_value,
        print 'Output', sample_scoring_function(test_value)
    

After you have a dict specifying a rule for every column, you can do the
following:

`df['Ck_SCR'] = df['Ck'].apply(getScoringFunction(Ck_dct))`

Converting pandas DataFrame with two columns to a dict of this form should not
be to difficult.

