[Python] Counting co-occurrence across three columns while ignoring duplicates

I have the following dataframe (a lot larger in reality).

Family1 Family2 Family3
A B B
B A B
B C C

Now there are three requirements:

• I have to count the co-occurrence of the values from Family1, Family2 and Family3.
• The co-occurrence should not be duplicated.
• Reverse of values' co-occurrence should not be added as unique, so A-B is the same as B-A.

For the above case, I want family and related family with their co-occurrence. So the output should be like

family related_family co-occurrence
B A 2
C B 1

Based on these three cases I have written a query.

output_one = sqldf("""
select family, related_family, sum(cooccurence) as Cooccurence from
(
select Family1 family, Family2 related_family, count(*) cooccurence
from ds_neighbours
WHERE Family1!= Family2 and Family2 != Family1
GROUP BY Family1, Family2
UNION
select Family2 family, Family3 related_family, count(*) cooccurence
from ds_neighbours
WHERE Family2 != Family3 and Family3 != Family2
GROUP BY Family2, Family3
UNION
select Family1 family, Family3 related_family, count(*) cooccurence
from ds_neighbours
WHERE Family1 != Family3 and Family3 != Family1
GROUP BY Family1, Family3
)
WHERE cooccurence > 25 group by family, related_family
ORDER BY cooccurence DESC
""")


Now my query is not able to handle case 3 where occurrence of family and related_family skills in reverse is counted as unique. Ex - linux as family and cplusplus as related_family, and cplusplus as family and linux as related_family is counted as unique and vice versa.

And my requirement is that it should not be a unique record. How do I do this?

Continue reading...