2
$\begingroup$

I have a Pandas DataFrame structured like this:

    user_id movie_id    rating
0   1       1193        5
1   2       1193        5
2   12      1193        4
3   15      1193        4
4   17      1193        5
5   18      1193        4
6   19      1193        5
7   24      1193        5
8   28      1193        3

Each row corresponds to a rating event performed by the user user_id for the movie movie_id. For instance, the first row says that user 1 rated the movie 1193 with a rating of 5.

This data comes from the MovieLens project.

My goal is to find all the users who satisfy these two conditions:

  • rated movie 588 with a rating of 5
  • rated movie 3578 with a rating of 3

I came up with two filtered DataFrame objects for each of the above conditions:

ratings_588_5 = data[(data.movie_id == 588) & (data.rating == 5]
ratings_3578_3 = data[(data.movie_id == 3578) & (data.rating == 3)]

Which result in, respectively:

>>> ratings_588_5
user_id movie_id    rating
438     588         5
758     588         5
913     588         5
1024    588         5
1214    588         5

>>> ratings_3578_3
user_id movie_id    rating
45      3578        3
321     3578        3
467     3578        3
758     3578        3
1024    3578        3
1381    3578        3

In Pandas, how can I compute a list of all user_id which appear in both DataFrames?

In this example, the result I want to obtain is:

[758, 1024]
$\endgroup$
6
$\begingroup$

you can use numpy.intersect1d() method:

In [277]: np.intersect1d(a.user_id, b.user_id).tolist()
Out[277]: [758, 1024]

or pd.core.common.intersection() method, but it seems to be slow (at least on my notebook for aa and bb DataFrames [see setup below...]):

In [307]: pd.core.common.intersection(a.user_id, b.user_id).tolist()
Out[307]: [1024, 758]

Timing for aa DF (50K rows) and bb DF (60K rows):

In [294]: aa = pd.concat([a] * 10**4, ignore_index=True)

In [295]: bb = pd.concat([b] * 10**4, ignore_index=True)

In [296]: aa.shape
Out[296]: (50000, 3)

In [297]: bb.shape
Out[297]: (60000, 3)

In [298]: %timeit aa.ix[aa.user_id.isin(bb.user_id),'user_id'].tolist()
10 loops, best of 3: 41.8 ms per loop

In [299]: %timeit np.intersect1d(aa.user_id, bb.user_id).tolist()
100 loops, best of 3: 5.36 ms per loop

In [300]: %timeit pd.merge(aa, bb, on='user_id').user_id.tolist()
...
skipped
...
MemoryError:

In [308]: %timeit pd.core.common.intersection(aa.user_id, bb.user_id).tolist()
10 loops, best of 3: 52.8 ms per loop

PS original answer

$\endgroup$
2
$\begingroup$

One possible way is to convert the user id's to plain old Python sets and check the intersection:

users_rating_588_5 = set(ratings_588_5['user_id'])
users_rating_3578_3 = set(ratings_3578_3['user_id'])
users_matching = users_rating_588_5.intersection(users_rating_3578_3)
print(users_matching) # {1024, 758}
$\endgroup$
1
$\begingroup$

I would prefer more database-like operations such as joining:

intersection = pd.merge(ratings_588_5, ratings_3578_3, on=['user_id'], how='inner') print intersection.user_id.tolist()

This would output:

[758, 1024]
$\endgroup$

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.