使用pandas生成分组内无序元素对并统计跨组出现次数

解决方案 #

方案1：直接生成无顺序组合（效率最优） #

直接用itertools.combinations生成分组内不考虑顺序的两两组合，从根源避免对称重复对，无需额外过滤：

import pandas as pd
from itertools import combinations

# 原始数据
df = pd.DataFrame(
    {
        "group": ["group1", "group1", "group2", "group2", "group2", "group3", "group3", "group3", "group4", "group4", "group4", "group4", "group5", "group5"],
        "letter": ["B1", "B2", "B1", "B2", "B3", "B1", "B2", "B4", "B2", "B1", "B3", "B4", "B3", "B4"]
    })

# 按分组生成无重复两两组合
pair_df = df.groupby('group')['letter']\
    .apply(lambda x: pd.DataFrame(combinations(x, 2), columns=['letter_x', 'letter_y']))\
    .reset_index(level=1, drop=True)\
    .reset_index()

# 聚合统计组合出现次数与对应分组
result = pair_df.groupby(['letter_x', 'letter_y'])\
    .agg(
        count=('group', 'count'),
        groups=('group', tuple)
    ).reset_index()

输出的result完全符合你要求的格式。

方案2：基于原有自合并代码修改 #

如果要沿用你已写的自合并逻辑，只需新增条件保证两个letter列按字典序排序，即可过滤对称重复对：

df_merge = df.merge(df, left_on='group', right_on='group', how="outer")
# 新增条件过滤同元素和对称重复对
df_merge = df_merge[(df_merge['letter_x'] != df_merge['letter_y']) & (df_merge['letter_x'] < df_merge['letter_y'])]

# 直接聚合即可得到正确结果
df_overlap = df_merge.groupby(['letter_x', 'letter_y'])\
    .agg(
        count=('group', 'count'),
        groups=('group', tuple)
    ).reset_index()

内容的提问来源于stack exchange，提问作者Moonday