如何在R语言中生成含正负计数的交叉汇总表

给定你的数据集，以下是几种生成目标表格的R实现方式，以及类似Tableau效果的可视化/交互式替代方案：

一、R语言生成目标表格的方法 #

1. Base R 原生实现 #

无需额外安装包，通过xtabs生成三维频数表后整理合并：

# 生成三维频数表（产品×国家×语气）
tab <- xtabs(~ product + country + tone, data = df)
# 转换为数据框并重塑宽格式
result_df <- reshape(as.data.frame(tab), idvar = "product", timevar = "country", direction = "wide")

# 合并每个单元格的pos/neg计数为字符串
for(col in grep("Freq", colnames(result_df))){
  country <- gsub("Freq\\.", "", colnames(result_df)[col])
  pos_count <- result_df[result_df$tone == "pos", col]
  neg_count <- result_df[result_df$tone == "neg", col]
  result_df[result_df$tone == "pos", col] <- paste0("pos: ", pos_count, "\nneg: ", neg_count)
}

# 整理最终表格：保留pos行，去除冗余列
final_table <- result_df[result_df$tone == "pos", !colnames(result_df) %in% "tone"]
rownames(final_table) <- final_table$product
final_table$product <- NULL

print(final_table)

2. tidyverse 工具链实现 #

用dplyr分组统计、tidyr重塑表格，逻辑更清晰：

library(dplyr)
library(tidyr)

final_table <- df %>%
  # 按产品、国家、语气分组计数
  group_by(product, country, tone) %>%
  tally() %>%
  # 先把国家转为列，填充缺失计数为0
  pivot_wider(names_from = country, values_from = n, values_fill = 0) %>%
  # 再把语气转为列，拆分每个国家的pos/neg计数
  pivot_wider(names_from = tone, values_from = c(US, UK, RUS, ARM)) %>%
  # 逐行合并每个国家的pos/neg字符串
  rowwise() %>%
  mutate(
    US = paste0("pos: ", US_pos, "\nneg: ", US_neg),
    UK = paste0("pos: ", UK_pos, "\nneg: ", UK_neg),
    RUS = paste0("pos: ", RUS_pos, "\nneg: ", RUS_neg),
    ARM = paste0("pos: ", ARM_pos, "\nneg: ", ARM_neg)
  ) %>%
  # 保留需要的列并取消分组
  select(product, US, UK, RUS, ARM) %>%
  ungroup()

print(final_table)

3. reshape2 包实现 #

适合习惯用reshape2的用户，步骤更简洁：

library(reshape2)

# 先统计每个（产品、语气）组合在各国家的计数
count_df <- dcast(df, product + tone ~ country, fun.aggregate = length, value.var = "tone")
# 合并同一产品-国家的pos/neg计数
final_table <- dcast(count_df, product ~ country, fun.aggregate = function(x) paste0("pos: ", x[2], "\nneg: ", x[1]))

print(final_table)

二、类似Tableau效果的替代方案 #

如果需要更直观的可视化或交互式体验，以下方案可以达到类似Tableau的效果：

1. ggplot2 热力图（带双计数标签） #

用热力图展示总观测数分布，同时在单元格内标注pos/neg计数，和Tableau交叉表可视化逻辑一致：

library(ggplot2)

plot_data <- df %>%
  group_by(product, country, tone) %>%
  tally() %>%
  pivot_wider(names_from = tone, values_from = n, values_fill = 0) %>%
  mutate(label = paste0("pos: ", pos, "\nneg: ", neg))

ggplot(plot_data, aes(x = country, y = product)) +
  geom_tile(aes(fill = pos + neg), color = "white") + # 颜色深浅对应总观测数
  geom_text(aes(label = label), size = 3) +
  scale_fill_gradient(low = "#f0f0f0", high = "#2c3e50") +
  labs(x = "国家", y = "产品", fill = "总观测数") +
  theme_minimal()

2. gt 包交互式表格 #

生成可交互的HTML表格，支持排序、筛选，单元格内换行正常显示，类似Tableau的交互式表格体验：

library(gt)

final_table %>%
  gt() %>%
  tab_header(title = "产品-国家正负语气计数表") %>%
  fmt_markdown(columns = everything()) # 解析字符串中的换行符

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