Creating a weighted score column from Pandas pivot table

Question

I have the following pivot table in pandas:

Rating                      1   2   3   4   5
MovieTitle                                   
1-900 (1994)                2   0   1   2   0
101 Dalmatians (1996)      15  17  46  25   6
12 Angry Men (1957)         0   1  15  49  60
187 (1997)                  5   8  13  11   4

I need to create a new column by calculating the weighted score. The formula will yield the score for the movie "101 Dalmatians (1996)" as such:

score = (15*1) + (17*2) + (46*3) + (25*4) + (6*5)

May I know how can I do that? Thanks

Answer 1

You just do exactly the same thing you specified in the formula :)

>>> (df[1] * 1) + (df[2] * 2) + (df[3] * 3) + (df[4] * 4) + (df[5] * 5)
MovieTitle
1-900 (1994)              13
101 Dalmatians (1996)    317
12 Angry Men (1957)      543
187 (1997)               124
dtype: int64

Or since the movie title is the index of the DataFrame, and you only have the rating columns, you can do:

>>> weights = np.array([1,2,3,4,5])
>>> (df * weights).sum(axis=1)
MovieTitle
1-900 (1994)              13
101 Dalmatians (1996)    317
12 Angry Men (1957)      543
187 (1997)               124
dtype: int64

You can assign that to the DataFrame if you want:

>>> df['score'] = (df * weights).sum(axis=1)