I have 2 input dataFrames.

1. DatFrame one :

2. DatFrame two :

Output I want : i want to count the number of occurrences of each element from df2 in df1 : The expected output is show in this picture :

Note : It will be reader friendly if you group final dataframe according to IMEI so all Values of that particular will be grouped.

CodePudding user response：

Here is a way, step by step

>>> df1

  a1 a2
0  A  C
1  B  D
2  F  D
3  A  D

>>> df2

  emi_1 emi_2
0     A     C
1     B     L
2     C     N
3     G     D

>>> df2_melted = df2.melt(var_name="IMEI", value_name="Values")
>>> df2_melted

    IMEI Values
0  emi_1      A
1  emi_1      B
2  emi_1      C
3  emi_1      G
4  emi_2      C
5  emi_2      L
6  emi_2      N
7  emi_2      D

>>> df1_counts = df1.apply(pd.Series.value_counts)
>>> df1_counts

    a1   a2
A  2.0  NaN
B  1.0  NaN
C  NaN  1.0
D  NaN  3.0
F  1.0  NaN

>>> final = (
         df2_melted.merge(df1_counts, how="left", 
                          left_on="Values", right_index=True)
                   .fillna(0)
                   .astype({"a1":int, "a2":int})
    )

>>> final

    IMEI Values  a1  a2
0  emi_1      A   2   0
1  emi_1      B   1   0
2  emi_1      C   0   1
3  emi_1      G   0   0
4  emi_2      C   0   1
5  emi_2      L   0   0
6  emi_2      N   0   0
7  emi_2      D   0   3