Count occurrences of values from one dataframe in another

Question

I need to create another column in df1 that will tell me a count of occurrences. Note, that another dataframe does not have exact match but does contain the values.

df1:

user1
user2
user3
user4
user5

df2:

user1,user2
user2, user1
user1, user5
user4,user5

I tried:

df1['Newcount'] = df1['df1:'].map(df2['df2:'].value_counts())

But this counts exact matches.

The desired output should be:

user1  3
user2  2
user3  0
user4  1
user5  2

Answer 1

Does this work:

df1 = pd.DataFrame({'col': ['user1', 'user2', 'user3', 'user4', 'user5']})
df2 = pd.DataFrame({'col': ['user1;user2', 'user2;user1', 'user1;user5', 'user4;user5']})

df1.merge(
    df2.assign(
        col=df2['col'].str.split(';')
        ).explode('col').value_counts().reset_index(),
    how='left'
    ).fillna(0)


    col     0
0   user1   3.0
1   user2   2.0
2   user3   0.0
3   user4   1.0
4   user5   2.0