Identify overlap in group membership

I have a data frame that has state-years as rows and organizations as columns. Every column shows whether a state was a member of an organization in a given year (1) or not (0).

enter image description here

I have a second directed dyadic data frame that has the dyads of all organizations from the first data frame and years as rows. So one row/case is the dyad of two organizations in a given year.

enter image description here

Now, I would like to create a new variable/column in this dyadic data frame the gives the number of states (from the first data frame) that both organizations have in common in a given year. For example, when Mexico is in 1996 a member of both APEC and BIS the new variable should count this as 1 overlapping member state for the dyad APEC.BIS and BIS.APEC. When both organizations have in 1996 5 overlapping members, the new variable should give me this number.

Because I have 80 organizations and about 100 years, there are more than 500,000 observations on the dyadic data frame. Hence, I need a solution that does the job for many comparisons.

I have been searching the web for quite some time now. But I have no clue how to do this. Hence, every bit of advice is highly appreciated.


Does anyone have an example using AWS S3 in Visual Studio 2022 with Xamarin Forms

Spotify API Driven Music Web app, that allows to listen and control and share songs