Pandas - Get feature values which appear in two distinct dataframes

Question

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]

Answer 1

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

Answer 2

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}

Answer 3

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]