Remove rows from two DFs that have uncommon column value

Question

I have these two DFs

Active:

Customer_ID | product_No| Rating
7           | 111       | 3.0
7           | 222       | 1.0
7           | 333       | 5.0
7           | 444       | 3.0

User:

Customer_ID | product_No| Rating
9           | 111       | 2.0
9           | 222       | 5.0
9           | 666       | 5.0
9           | 555       | 3.0

I want to find the ratings of the common products that both users rated (e.g. 111,222) and remove any uncommon products (e.g. 444,333,555,666). So the new DFs should be like this:

Active:

Customer_ID | product_No| Rating
7           | 111       | 3.0
7           | 222       | 1.0

User:

Customer_ID | product_No| Rating
9           | 111       | 2.0
9           | 222       | 5.0

I do not know how to do this without for loops. Can you help me, please

This is the code I have so far:

import pandas as pd
ratings = pd.read_csv("ratings.csv",names['Customer_ID','product_No','Rating'])
active=ratings[ratings['UserID']==7]
user=ratings[ratings['UserID']==9]

Answer 1

You can firstly get the common product_No using set intersection and then use isin method to filter on the original data frames:

common_product = set(active.product_No).intersection(user.product_No)

common_product
# {111, 222}

active[active.product_No.isin(common_product)]

#Customer_ID   product_No   Rating
#0         7          111      3.0
#1         7          222      1.0

user[user.product_No.isin(common_product)]

#Customer_ID   product_No   Rating
#0         9          111      2.0
#1         9          222      5.0

Answer 2

Use query referencing the other dataframes

Active.query('product_No in @User.product_No')

   Customer_ID  product_No  Rating
0            7         111     3.0
1            7         222     1.0

User.query('product_No in @Active.product_No')

   Customer_ID  product_No  Rating
0            9         111     2.0
1            9         222     5.0