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).
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.
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.