如何在R语言中将交叉表转换为长表？

我需要将一个包含多层表头和行头的交叉表转换为标准长表，要求包含所有Store、Item、Day、Time的组合，缺失的Sales值用NA填充。以下是示例交叉表的构造代码：

cross_table <- matrix(c(c(NA, NA, "Monday", "Monday", "Tuesday", "Tuesday"),
    c(NA, NA, "AM", "PM", "AM", "PM"),
    c("Store1", "Item1", 1, 2, 1, 3),
    c("Store1", "Item3", 2, 5, 1, 6),
    c("Store2", "Item1", 3, 8, 1, 3),
    c("Store2", "Item2", 5, 2, 5, 0)), ncol = 6)

对应的交叉表结构：

目标长表需要包含StoreID、ItemID、Day、Time、Sales五个字段，示例结构如下：

请问该如何实现这种转换？

实现思路（基于tidyverse工具链） #

针对这种多层表头/行头的交叉表，核心步骤是：先解析行头和表头的层级关系，将矩阵转换为结构化数据框，再通过宽转长、笛卡尔积补全缺失组合来得到目标长表。

完整代码实现 #

library(tidyverse)

# 1. 解析交叉表的行头和表头
# 提取行头数据（Store和Item）
row_headers <- as_tibble(cross_table[3:nrow(cross_table), 1:2]) %>%
  rename(StoreID = V1, ItemID = V2)

# 提取表头数据（Day和Time）
col_headers <- as_tibble(t(cross_table[1:2, 3:ncol(cross_table)])) %>%
  rename(Day = V1, Time = V2)

# 提取销售数据并匹配表头命名
sales_data <- as_tibble(cross_table[3:nrow(cross_table), 3:ncol(cross_table)]) %>%
  set_names(paste(col_headers$Day, col_headers$Time, sep = "_"))

# 合并行头和销售数据为宽表
wide_data <- bind_cols(row_headers, sales_data)

# 2. 将宽表转换为长表
long_data <- wide_data %>%
  pivot_longer(
    cols = starts_with(c("Monday", "Tuesday")),
    names_to = c("Day", "Time"),
    names_sep = "_",
    values_to = "Sales"
  ) %>%
  mutate(Sales = as.numeric(Sales))

# 3. 生成所有变量的笛卡尔积，补全缺失组合
all_combinations <- expand_grid(
  StoreID = unique(long_data$StoreID),
  ItemID = unique(c("Item1", "Item2", "Item3")), # 实际场景可自动提取所有Item值
  Day = unique(long_data$Day),
  Time = unique(long_data$Time)
)

# 左连接补全缺失值，得到最终长表
final_long_table <- all_combinations %>%
  left_join(long_data, by = c("StoreID", "ItemID", "Day", "Time"))

# 查看结果
print(final_long_table)

代码说明 #

1. 解析层级结构：将矩阵的行头、表头和数值数据拆分处理，确保每个层级的变量对应正确。
2. 宽转长处理：使用pivot_longer将多列销售数据转为行结构，同时拆分表头中的Day和Time信息。
3. 补全缺失组合：通过expand_grid生成所有变量的笛卡尔积，再用left_join匹配现有数据，缺失组合的Sales值自动填充为NA。
4. 通用适配：如果实际场景有7个横向变量、9个纵向变量，只需调整行头/表头的提取行数/列数，以及pivot_longer的参数即可适配。

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