用tidyr::pivot_wider实现Python pivot_table index参数的混淆矩阵效果

需求背景 #

在Python中，通过df.pivot_table(values='Count Occurences', index='Actual Label', columns='Predicted Label')可轻松从聚合表生成混淆矩阵。现需在R中用tidyr::pivot_wider实现相同效果，目前已能用xtabs函数实现，但希望找到pivot_wider的解决方案。

示例数据 #

df = data.frame(
  x = c('A', 'A', 'A', 'A', 'B', 'B', 'C', 'D'),
  y = c('A', 'B', 'C', 'D', 'B', 'C', 'D', 'D'),
  n = c(200, 150, 100, 150, 50, 100, 70, 80)
)

遇到的问题 #

• 尝试以下sparklyr代码时报错（如"名称不唯一"）：

df %>% select(x, y) %>%
group_by(x, y) %>%
count %>%
pivot_wider(id_cols = 'x', names_from = 'y', values_from = 'n')

• 错误使用pivot_wider(names_from = c('x', 'y'), values_from = 'n')会得到非混淆矩阵结果；在sparklyr表上运行还会触发如下错误：

Error in `arrange()`:
ℹ In argument: `__row_num_79a4dd05_ce72_4873_967f_5d5ac9e9f66b`
Caused by error:
! Object `__row_num_79a4dd05_ce72_4873_967f_5d5ac9e9f66b` not found.
Run `rlang::last_error()` to see where the error occurred.

解决方案 #

1. 本地DataFrame场景

示例数据已完成聚合（包含计数列n），无需重复分组计数，直接使用pivot_wider即可，同时补充values_fill参数填充缺失组合为0，更符合混淆矩阵的格式要求：

library(tidyr)
library(dplyr)

df %>%
  pivot_wider(
    id_cols = x,          # 对应Python中的index（实际标签）
    names_from = y,       # 对应Python中的columns（预测标签）
    values_from = n,      # 对应Python中的values（计数）
    values_fill = 0       # 缺失的标签组合填充为0
  ) %>%
  tibble::column_to_rownames(var = "x")  # 将实际标签转为行名，贴近混淆矩阵样式

运行结果：

A   B   C   D
A 200 150 100 150
B   0  50 100   0
C   0   0   0  70
D   0   0   0  80

2. Sparklyr表场景

sparklyr的pivot_wider对参数格式要求更严格，需注意：

• 若原始数据未聚合，先通过分组求和完成计数；
• values_fill需以列表形式指定填充值；
• Spark DataFrame不支持行名，若需本地混淆矩阵格式，需将结果收集到本地后再处理。

代码示例：

library(sparklyr)
library(dplyr)
library(tidyr)

# 假设sc为Spark连接，df_spark为Spark表
df_spark %>%
  # 未聚合时执行分组计数，已聚合可跳过此步
  group_by(x, y) %>%
  summarise(n = sum(n), .groups = "drop") %>%
  pivot_wider(
    id_cols = x,
    names_from = y,
    values_from = n,
    values_fill = list(n = 0)  # Spark环境下需用列表指定填充值
  )

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