Pandas: count identical values in columns but from different index

Question

I have a data frame representing the customers ratings of restaurants. rating_year is the year the rating was made, first_year is the year the restaurant opened and last_year is the last business year of a restaurant.

• What i want to do is calculate the number of restaurants that opened in the same year as the restaurant in question, so with the same first_year.

The problem from what i did here is that i group restaurant_id and first_year and do the count, but i dont exclude the rest with the same id's. I dont know the syntax do to this. Can anyone help?

data = {'rating_id': ['1', '2','3','4','5','6','7','8','9'],
        'user_id': ['56', '13','56','99','99','13','12','88','45'],
        'restaurant_id':  ['xxx', 'xxx','yyy','yyy','xxx','zzz','zzz','eee','eee'],
        'star_rating': ['2.3', '3.7','1.2','5.0','1.0','3.2','1.0','2.2','0.2'],
        'rating_year': ['2012','2012','2020','2001','2020','2015','2000','2003','2004'],
        'first_year': ['2012', '2012','2001','2001','2012','2000','2000','2001','2001'],
        'last_year': ['2020', '2020','2020','2020','2020','2015','2015','2020','2020'],
        }


df = pd.DataFrame (data, columns = ['rating_id','user_id','restaurant_id','star_rating','rating_year','first_year','last_year'])
df['star_rating'] = df['star_rating'].astype(float)

df['nb_rating'] = (
    df.groupby('restaurant_id')['rating_id'].transform('count')
)



#here
df['nb_opened_sameYear'] = (
    df.groupby('restaurant_id')['first_year']
    .transform('count')
)

df.head(10)

Answer 1

IIUC, you want to groupby first_year and transform with nunique on the column restaurant_id. try:

df['nb_opened_sameYear'] = (
    df.groupby('first_year')['restaurant_id']
    .transform('nunique')
)