如何优雅重排R数据框：转宽格式、重命名及调整列序

原始数据 #

example <- data.frame(
  date = c("6/1/22", "6/2/22", "6/3/22",
          "6/1/22", "6/2/22", "6/3/22",
          "6/1/22", "6/2/22", "6/3/22",
          "6/1/22", "6/2/22", "6/3/22"),
  sub = c(1101, 1101, 1101, 
          1102, 1102, 1102,
          2101, 2101, 2101,
          2102, 2102, 2102),
  express_p = c("eg1", "eg2", "eg3", "eg4",
                "eg5", "eg6", "eg7", "eg8",
                "eg9", "eg10", "eg11", "eg12"),
  p_express = c("a", "b", "c", "d",
                "e", "f", "g", "h",
                "i", "j", "k", "l")
)

目标格式 #

example_clean <- data.frame(
  date = c("6/1/22", "6/2/22", "6/3/22", "6/1/22", "6/2/22", "6/3/22"),
  subA = c(1101, 1101, 1101, 1102, 1102, 1102),
  subB = c(2101, 2101, 2101, 2102, 2102, 2102),
  express_p_A = c("eg1", "eg2", "eg3", "eg7", "eg8", "eg9"),
  p_express_B = c("d", "e", "f", "j", "k", "l"),
  express_p_B = c("eg4", "eg5", "eg6", "eg10", "eg11", "eg12"),
  p_express_A = c("a", "b", "c", "g", "h", "i")
)

核心需求 #

• 按date分组，将sub列最后三位相同的记录配对到同一行
• 重命名列并调整列序，使一个对象的express_p与配对对象的p_express相邻

优雅实现（基于tidyverse） #

使用dplyr做数据清洗，tidyr做宽长格式转换，代码简洁可维护：

library(tidyverse)

example_clean <- example %>%
  # 1. 生成配对标识与分组标记
  mutate(
    pair_key = str_sub(sub, -3),  # 取sub最后三位作为配对依据
    group_id = case_when(
      str_sub(sub, 1, 1) == "1" ~ "A",
      str_sub(sub, 1, 1) == "2" ~ "B"
    )
  ) %>%
  # 2. 转宽格式：按date和配对键聚合字段
  pivot_wider(
    id_cols = c(date, pair_key),
    names_from = group_id,
    values_from = c(sub, express_p, p_express),
    names_sep = "_"
  ) %>%
  # 3. 调整列顺序，满足相邻要求
  select(
    date,
    sub_A, sub_B,
    express_p_A, p_express_B,
    express_p_B, p_express_A
  ) %>%
  # 4. 修正列名，匹配目标格式
  rename_with(~ str_remove(., "_"), starts_with("sub_")) %>%
  # 5. 移除中间辅助列
  select(-pair_key)

# 验证结果
print(example_clean)

代码说明 #

1. 配对逻辑：通过str_sub提取sub的最后三位作为配对键，同时根据首位数字标记A/B组，确保同匹配规则的记录被聚合到同一行
2. 宽格式转换：pivot_wider自动按分组生成带后缀的列，避免手动拼接字段
3. 列序控制：直接通过select指定目标列顺序，精准满足express_p与配对对象p_express相邻的要求
4. 列名修正：用rename_with批量处理列名，高效匹配目标格式

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