I am working with movielens 100k, which is a dataset with 100,000 ratings of movies, rated by 943 users on 1640 movies. I need to convert these ratings to pairwise preferences between movies for every user.For example, if a user u1 rated movies m1 and m2 as 1 and 3,respectively, then I would have the corresponding pair m2>m1 for the user u1. Similarly, we can think of making all such pairs for each user based on his/her ratings. Now, the idea is to select the best way to create such pairs, since for instance if a user has rated 20 movies, then we have (20,2)=190 possible pairs possible, but for the purpose of data collection, we can expect a user to rate 20 movies, but we cannot expect a user to provide 190 pairwise preferences to us. The reason for this whole exercise is to work on pairwise preference data instead of ratings data, but currently we do not have access to pairwise data, so any help on creating pairwise data from movies ratings would be helpful. Also, the objective is not to get an exhaustive list of pairs, but to have a list of just the important pairs.


You can create pairs using cross join in SQL- TO create pairwise data Join 'user-movie-movie_rating' table to 'user-movie-movie_rating' table (self join) on userId only. This will create all possible combinations of movies. Note that all combinations will be created using this (Movie 1 Movie 1 & also Movie 2 Movie 1). You can remove this with additional conditions.

Coming to deciding which movies are important - This can be based on number of ratings threshold(based on data or business case) and/or minimum rating of the movie.

Edit - Based on my understanding the objective is to predict which movie user will like more & not just recommend it.

The number of pairs can be selected based on number of users who have watched it. You can set minimum threshold of say 10% users (it's completely based on how much data you want)

For the combinations that are left, either you can take actual difference in rating or have flags as '1' for first movie, 3 for 2nd movies or 2 for third based on rating.

So new data can look like UserId || MoviePairId || Rating(1/2/3)

  • $\begingroup$ Thanks for answering! I don't want to know HOW to join the ratings in SQL; rather I'm looking for the approach to pair up the ratings. So deciding which pairs are important is useful; the straightforward combination gives me far too many pairs. $\endgroup$ – user3676846 Jul 18 '16 at 13:18
  • $\begingroup$ Edited the answer. Please elaborate on the objective of this if possible. I'm working on keyword recommendation system & would love to knwo more about new approaches. $\endgroup$ – wololo Jul 18 '16 at 19:27
  • $\begingroup$ "For the combinations that are left, either you can take actual difference in rating or have flags as '1' for first movie, 3 for 2nd movies or 2 for third based on rating." Could you elaborate on this ? I actually want to work on pairs on movies, and objective is to recommend top N movies to a user. $\endgroup$ – user3676846 Jul 19 '16 at 13:25
  • $\begingroup$ Why do you want pairs to recommend top N movies?. Regarding the clarification, once you create pair you need to have one value against it for algorithm (same as when you have rating for single movie). This can be coded as m1>m2 as '1' m2==m1 as '2' and m1<m2 as '3'. The idea is capture user preference between the two movies. Having said this if the objective is Top N none of this is required & it will actually give you different results $\endgroup$ – wololo Jul 19 '16 at 14:04
  • $\begingroup$ oh you meant 1,2,3 in that sense! I am actually storing movie pairs as userid || m1 || m2 || weight, where m1 is preferred to m2 movie, and weight is some weight which I assign to show how strongly m1 is preferred to m2. I need pairs to recommend movies since I am working under the assumption that movie ratings don't actually represent user preferences, so it is better to have users just compare a pair of movies, instead of individually giving ratings to movies. Then after getting pairwise preferences, I rank the movies, and recommend the top N movies. $\endgroup$ – user3676846 Jul 19 '16 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.