R语言如何基于dataframe已有多列值生成新列并按location分组聚合

实现思路 #

不需要手动做独热编码，直接通过「字段拼接→分组聚合→宽表转换」三步即可完成需求，推荐使用tidyverse套件实现，代码逻辑清晰易读。

依赖包加载 #

首先加载所需套件：
library(tidyverse)

示例数据构造 #

可直接运行以下代码生成你描述的测试数据表：

df <- tibble(
  location = c("a", "a", "b", "b"),
  type = c("x", "x", "x", "y"),
  amount = c(10, 20, 50, 100),
  product = c("p1", "p2", "p5", "p10")
)

核心处理代码 #

result <- df %>%
  # 拼接product与amount字段
  mutate(p_amount = str_c(product, amount, sep = "_")) %>%
  # 按location、type分组，合并同组的拼接结果
  group_by(location, type) %>%
  summarise(merge_val = str_c(p_amount, collapse = ","), .groups = "drop") %>%
  # 转为宽表，type取值转为新列
  pivot_wider(
    id_cols = location,
    names_from = type,
    values_from = merge_val,
    values_fill = ""
  ) %>%
  # 按需求修改列名
  rename(A_P_X = x, A_P_Y = y)

运行后输出result即可得到目标格式的数据表。

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