0

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

Viktor Kerkez
  • 45,070
  • 12
  • 104
  • 85
user2798444
  • 45
  • 1
  • 1
  • 5
  • Possible duplicate: http://stackoverflow.com/questions/18419962/how-to-compute-weighted-sum-of-all-elements-in-a-row-in-pandas – Phillip Cloud Sep 20 '13 at 12:25

1 Answers1

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)
Viktor Kerkez
  • 45,070
  • 12
  • 104
  • 85