Pandas基于两个现有DataFrame构建指定结构新DataFrame的实现方法

实现方法 #

首先预计算每个分组下所有问题的True占比得分，再基于配对表匹配两个分组的公共问题，拼接得到最终结果，代码如下：

import pandas as pd

# 构造测试数据集
df = pd.DataFrame({
    'name':['a', 'a', 'a', 'b', 'b', 'c', 'd', 'd', 'e', 'f', 'f', 'g', 'h', 'h', 'i'],
    'group':['g1', 'g1', 'g1', 'g1', 'g1', 'g1', 'g2', 'g2', 'g2', 'g3', 'g3', 'g3', 'g4', 'g4', 'g4'],
    'feedback': [False, True, True, True, True, False, False, True, True, True, True, False, True, True, False],
    'question': ['abc', 'abc', 'xyz', 'xyz', 'abc', 'def', 'xyz', 'xyz', 'xyz', 'abc', 'www', 'xyz', 'www', 'qqq', 'xyz']
})

df_2 = pd.DataFrame({
    'Group1': ['g1', 'g1', 'g2', 'g3'],
    'Group2': ['g2', 'g3', 'g3', 'g4'],
    'Value': [0.25, 0.50, 0.25, 0.50]
})

# 预计算每个分组对应每个问题的True占比得分
group_question_score = df.groupby(['group', 'question'], as_index=False)['feedback'].mean()
group_question_score.rename(columns={'feedback': 'score'}, inplace=True)

result = []
# 遍历所有分组配对
for _, row in df_2.iterrows():
    g1, g2 = row['Group1'], row['Group2']
    # 分别提取两个分组的得分
    g1_df = group_question_score[group_question_score['group'] == g1][['question', 'score']].rename(columns={'score': 'ScoreGroup1'})
    g2_df = group_question_score[group_question_score['group'] == g2][['question', 'score']].rename(columns={'score': 'ScoreGroup2'})
    # 内连接取公共问题
    merge_tmp = pd.merge(g1_df, g2_df, on='question', how='inner')
    merge_tmp['Group1'], merge_tmp['Group2'] = g1, g2
    result.append(merge_tmp)

# 合并所有结果调整列顺序，保留两位小数匹配示例输出
final_df = pd.concat(result, ignore_index=True)[['Group1', 'Group2', 'question', 'ScoreGroup1', 'ScoreGroup2']].round(2)
print(final_df)

运行上述代码输出结果和你给出的预期一致。

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