pandas如何实现多分组列唯一值两两比较并生成统计结果

1. 导入依赖包 #

import pandas as pd
import itertools

2. 预处理分组题目数据 #

先统计每个分组对应的唯一题目集合和题目总数，自动忽略重复作答的记录：

# 示例数据构造，你可以替换成自己的DataFrame
data = [
    ['a','g1',False,'abc'],
    ['a','g1',True,'abc'],
    ['a','g1',True,'xyz'],
    ['b','g1',True,'xyz'],
    ['b','g1',True,'abc'],
    ['c','g1',False,'def'],
    ['d','g2',False,'xyz'],
    ['d','g2',True,'xyz'],
    ['e','g2',True,'xyz'],
    ['f','g3',True,'abc'],
    ['f','g3',True,'www'],
    ['g','g3',False,'xyz'],
    ['h','g4',True,'www'],
    ['h','g4',True,'qqq'],
    ['i','g4',False,'xyz'],
]
df = pd.DataFrame(data, columns=['name','group','feedback','question'])

# 统计每个分组的唯一题目集合和题目数量
group_qs_set = df.groupby('group')['question'].apply(set).to_dict()
group_qs_cnt = df.groupby('group')['question'].nunique().to_dict()

3. 生成两两对比统计结果 #

通过itertools.combinations生成所有不重复的两两分组组合，逐对计算对应指标：

res_list = []
# 遍历所有两两分组组合
for t1, t2 in itertools.combinations(group_qs_set.keys(), 2):
    t1_qs = group_qs_set[t1]
    t2_qs = group_qs_set[t2]
    common_cnt = len(t1_qs & t2_qs)
    all_cnt = len(t1_qs | t2_qs)
    res_list.append({
        'Team1': t1,
        'Team2': t2,
        'NumberQuestionsT1': group_qs_cnt[t1],
        'NumberQuestionsT2': group_qs_cnt[t2],
        'AllQuestions': all_cnt,
        'CommonQuestions': common_cnt,
        'Ratio': round(common_cnt / all_cnt, 2)
    })

# 转换为DataFrame格式
res_df = pd.DataFrame(res_list)
print(res_df)

输出结果和你给出的预期完全一致，如果你需要保留更多小数位，修改round的第二个参数即可。

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