Need to find number of matches in Python

Question

I want to compare 2 data columns and see if I can find a match. When I get a match, I want to show how many occurrences of that match was found. For instance

df1
Col_A  Col_B
A0     B0
A1     B1
A2     B2


df2
Col_A   Col_B
A0      B0
A1      B1
A0      B0
A4      B4

I want to check the df2 Col A against Col_A in df1. If I find a match, I should include them in my output table. Then I should have a count on number of times it matched by comparing. The Output should be

Col_A     Col_B    Result
A0        B0       1
A1        B1       1
A0        B0       2

How to achieve this in Python?

Answer 1

merge and cumcount

df2.assign(Result=df2.groupby([*df2]).cumcount() + 1).merge(df1)

  Col_A Col_B  Result
0    A0    B0       1
1    A0    B0       2
2    A1    B1       1