如何按维度对DataFrame聚合生成热力图用属性对平均购买汇总表？

问题概述 #

需要基于网店访问数据生成一张汇总表，展示每对属性值组合下的平均每访问购买商品数（总购买数/该组合的访问次数），用于热力图制作。汇总表规则：

• 同一属性值对比的对角线单元格为1
• 同一属性的不同值对比结果为NA
• 下三角单元格与对应上三角单元格值一致

输入数据 #

期望输出 #

已尝试代码 #

data = {
    'visit_id': ['aa', 'ab', 'ac', 'ad'],
    'age': ['young', 'young', 'old', 'old'],
    'is_website': [True, False, True, True],
    'is_US': [True, False, True, False],
    'items_bought': [0, 2, 0, 3]
}

import pandas as pd
from itertools import combinations

df1 = pd.DataFrame(data)
dim = ['age', 'is_website', 'is_US']
dim_pairs = list(combinations(dim, 2))

dfs = {}
for x in range(len(dim_pairs)):
    grouped = df1.groupby([dim_pairs[x][0], dim_pairs[x][1]]).agg(
        total_items=('items_bought', 'sum'),
        total_visits=('visit_id', 'count')
    ).reset_index()
    grouped['avg_bought'] = grouped['total_items'] / grouped['total_visits']
    pivot_df = grouped.pivot_table(
        index=dim_pairs[x][0], columns=dim_pairs[x][1], 
        values='avg_bought', aggfunc='sum'
    ).fillna(0)
    dfs[f"pivot_df{x}"] = pivot_df

完整解决方案 #

方法1：基于已有透视表合并 #

步骤1：生成统一格式的属性值标签

# 生成所有属性值的标签（如"age young"）
all_labels = []
for col in dim:
    for val in df1[col].unique():
        # 布尔值转小写字符串，统一格式
        val_str = str(val).lower() if isinstance(val, bool) else val
        all_labels.append(f"{col} {val_str}")

# 创建空汇总表
summary_df = pd.DataFrame(index=all_labels, columns=all_labels)

步骤2：填充对角线与互斥属性NA

# 对角线设为1
for label in all_labels:
    summary_df.loc[label, label] = 1

# 同一属性的不同值之间设为NA
for col in dim:
    col_labels = [lbl for lbl in all_labels if lbl.startswith(f"{col} ")]
    for i in range(len(col_labels)):
        for j in range(len(col_labels)):
            if i != j:
                summary_df.loc[col_labels[i], col_labels[j]] = pd.NA

步骤3：填充不同属性对的计算值

for pair in dim_pairs:
    col1, col2 = pair
    pivot = dfs[f"pivot_df{dim_pairs.index(pair)}"]
    
    for val1 in pivot.index:
        val1_str = str(val1).lower() if isinstance(val1, bool) else val1
        row_label = f"{col1} {val1_str}"
        for val2 in pivot.columns:
            val2_str = str(val2).lower() if isinstance(val2, bool) else val2
            col_label = f"{col2} {val2_str}"
            # 填充上三角和下三角
            summary_df.loc[row_label, col_label] = pivot.loc[val1, val2]
            summary_df.loc[col_label, row_label] = pivot.loc[val1, val2]

步骤4：处理除零错误

# 将除零产生的inf替换为指定文本
summary_df = summary_df.replace([float('inf'), -float('inf')], "null division")

方法2：直接遍历计算（更简洁） #

无需提前生成透视表，直接遍历所有属性值对计算：

import pandas as pd

data = {
    'visit_id': ['aa', 'ab', 'ac', 'ad'],
    'age': ['young', 'young', 'old', 'old'],
    'is_website': [True, False, True, True],
    'is_US': [True, False, True, False],
    'items_bought': [0, 2, 0, 3]
}
df1 = pd.DataFrame(data)
dim = ['age', 'is_website', 'is_US']

# 生成所有属性值标签
all_labels = []
for col in dim:
    for val in df1[col].unique():
        val_str = str(val).lower() if isinstance(val, bool) else val
        all_labels.append(f"{col} {val_str}")

# 创建空汇总表并填充对角线
summary_df = pd.DataFrame(index=all_labels, columns=all_labels)
summary_df.values[[range(len(all_labels))]*2] = 1

# 遍历所有标签对计算
for row_label in all_labels:
    for col_label in all_labels:
        if row_label == col_label:
            continue
        
        row_attr, row_val = row_label.split(' ', 1)
        col_attr, col_val = col_label.split(' ', 1)
        
        # 同一属性不同值：设为NA
        if row_attr == col_attr and row_val != col_val:
            summary_df.loc[row_label, col_label] = pd.NA
            continue
        
        # 不同属性：计算平均购买数
        if row_attr != col_attr:
            # 转换值类型（布尔值转回bool）
            row_val = bool(row_val) if row_val in ['true', 'false'] else row_val
            col_val = bool(col_val) if col_val in ['true', 'false'] else col_val
            
            filtered = df1[(df1[row_attr] == row_val) & (df1[col_attr] == col_val)]
            if len(filtered) == 0:
                summary_df.loc[row_label, col_label] = "null division"
            else:
                avg = filtered['items_bought'].sum() / len(filtered)
                summary_df.loc[row_label, col_label] = avg
                # 同步填充下三角
                summary_df.loc[col_label, row_label] = avg

# 处理除零错误
summary_df = summary_df.replace([float('inf'), -float('inf')], "null division")

运行后summary_df即为符合要求的汇总表。

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