如何按分组将行转换为列？（用于Apriori关联分析数据集构建）

AI 小北

2026-8-25

问题：转换订单数据格式以适配Apriori关联分析

我需要用Apriori算法做关联分析，现有长格式的订单数据如下：

# 原始数据结构
data.frame(
  "order_number"=c("100145",  "100155", "100155", "100155", 
                   "500002", "500002", "500002", "500007"),
  "order_item"=c("27684535","15755576", 
                 "1357954","124776249","12478324","15755576","13577","27684535")
)

原始数据预览：

order_number order_item
1       100145   27684535
2       100155   15755576
3       100155    1357954
4       100155  124776249
5       500002   12478324
6       500002   15755576
7       500002      13577
8       500007   27684535

希望将其转换为以下宽格式：

# 目标数据结构
data.frame(
  "order_number"=c("100145","100155","500002","500007"), 
  "col1"=c("27684535","15755576","12478324","27684535"),
  "col2"=c(NA,"1357954","15755576",NA),
  "col3"=c(NA,"124776249","13577",NA)
)

目标数据预览：

order_number     col1     col2      col3
1       100145 27684535     <NA>      <NA>
2       100155 15755576  1357954 124776249
3       500002 12478324 15755576     13577
4       500007 27684535     <NA>      <NA>

解决方案

方法1：使用tidyverse工具包（dplyr + tidyr）

# 加载工具包
library(tidyverse)

# 定义原始数据
df <- data.frame(
  order_number = c("100145",  "100155", "100155", "100155", 
                   "500002", "500002", "500002", "500007"),
  order_item = c("27684535","15755576", 
                 "1357954","124776249","12478324","15755576","13577","27684535"),
  stringsAsFactors = FALSE
)

# 转换为宽格式
result_df <- df %>%
  group_by(order_number) %>%
  # 给每个订单内的商品生成col1、col2...的列名
  mutate(col = paste0("col", row_number())) %>%
  # 长格式转宽格式，缺失值自动填充NA
  spread(key = col, value = order_item)

# 查看结果
print(result_df)

方法2：使用Base R（无需额外安装包）

# 定义原始数据
df <- data.frame(
  order_number = c("100145",  "100155", "100155", "100155", 
                   "500002", "500002", "500002", "500007"),
  order_item = c("27684535","15755576", 
                 "1357954","124776249","12478324","15755576","13577","27684535"),
  stringsAsFactors = FALSE
)

# 给每个订单的商品添加序号后缀
df$col <- with(df, ave(order_item, order_number, FUN = function(x) paste0("col", seq_along(x))))

# 转换为宽格式
result_df <- reshape(df, idvar = "order_number", timevar = "col", direction = "wide")
# 清理列名，去掉多余的前缀
names(result_df) <- gsub("order_item\\.", "", names(result_df))

# 查看结果
print(result_df)

两种方法都能得到你需要的宽格式数据集，后续可直接用于Apriori算法的关联分析。

内容的提问来源于stack exchange，提问作者D.PARK