CODEWITHSUNDEEP
pythonpandasdataframe
Oam
Oam

Reputation: 345

Find occurences where a column from one dataframe equals another, based on condition

I have the following two dataframes, which have different size: df1 (966 rows x 2 cols), df2 (36 rows, 2 cols), where

df1:

    Video_#  Selected Joint.1
484 1        Left_shoulder  
778 1        Left_shoulder  
418 1        Right_shoulder 
964 1        Right_shoulder 
193 1        Right_shoulder 
... ... ... ... ...
285 36       Right_elbow    
267 36       Left_hand  
216 36       Shoulder_centre
139 36       Right_shoulder

df2:

    Video_#   Ann.1
0   1         Shoulder_center
1   2         Head
2   3         Right_hip 
... ... ... ... ...
33  34        Left_knee 
34  35        Right_knee    
35  36        Right_shoulder

Where Video_# goes from 1-36. In df2 the Video_# column is just a single occurrence of each, so 1-36 just once. Whereas in df1 there are multiple occurrences of each 1-36, not the same number for each 1-36 (I hope that makes sense).

What I want to check is the number of occurrences df1['Selected Joint.1'] == df2['Ann.1'] based on Video_#. So the expected output is (eg.):

Video_#   Equality Occurrences
1         3
2         5
... ... ...
36        6

Is that possible?

Upvotes: 1

Views: 49

Answers (1)

jezrael
jezrael

Reputation: 862681

Use DataFrame.merge with GroupBy.size:

df = (df1.merge(df2, 
                left_on=['Video_#','Selected Joint.1'], 
                right_on=['Video_#','Ann.1'])
         .groupby('Video_')
         .size()
         .reset_index(name='Equality Occurrences'))

Upvotes: 1

Related Questions