将非规整分组行转换为规整数据（Tidy Data）的实现方案

需要将半规整的数据集转换为完全规整格式，数据存在模式但不一致，不能仅依赖模式重复实现转换。数据行通过主分组因子Material关联，子组仅通过行位置关联。

原始数据 #

df <- structure(list(Material = c("M1", "M1", "M1", "M1", "M1", "M1", 
"M1", "M2", "M2", "M2"), Material_Group = c("A", "B", "B", 
"A", "B", "B", "B", "A", "B", "B"), Desc = c("Part 1", "Sub 1", 
"Sub 2", "Part 2", "Sub 1", "Sub 2", "Sub 4", "Part 3", "Sub 2", 
"Sub 6"), Qty = c(0.25, 0.2, 0.1, 0.75, 0.15, 0.25, 0.3, 1, 0.4, 
0.6)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-10L))

方法1：用dplyr的fill关联主组与子组 #

通过标记主组行并向前填充主组信息，将子组与对应的主组关联：

library(dplyr)

df_cleaned <- df %>%
  group_by(Material) %>%
  # 提取主组的描述和数量，子组行暂存为NA
  mutate(
    Main_Part = ifelse(Material_Group == "A", Desc, NA),
    Main_Qty = ifelse(Material_Group == "A", Qty, NA)
  ) %>%
  # 向下填充主组信息，直到下一个主组出现
  fill(Main_Part, Main_Qty, .direction = "down") %>%
  # 仅保留子组数据
  filter(Material_Group == "B") %>%
  # 重命名并整理列顺序
  rename(Sub_Part = Desc, Sub_Qty = Qty) %>%
  select(Material, Main_Part, Main_Qty, Sub_Part, Sub_Qty) %>%
  ungroup()

print(df_cleaned)

输出结果：

# A tibble: 7 × 5
  Material Main_Part Main_Qty Sub_Part Sub_Qty
  <chr>    <chr>        <dbl> <chr>      <dbl>
1 M1       Part 1        0.25 Sub 1       0.2 
2 M1       Part 1        0.25 Sub 2       0.1 
3 M1       Part 2        0.75 Sub 1       0.15
4 M1       Part 2        0.75 Sub 2       0.25
5 M1       Part 2        0.75 Sub 4       0.3 
6 M2       Part 3        1    Sub 2       0.4 
7 M2       Part 3        1    Sub 6       0.6 

方法2：组合pivot_longer与pivot_wider转换 #

通过创建分组ID，结合宽长格式转换实现关联：

library(tidyr)
library(dplyr)

df_cleaned_2 <- df %>%
  group_by(Material) %>%
  # 为每个主组创建唯一分组ID，主组行触发ID递增
  mutate(group_id = cumsum(Material_Group == "A")) %>%
  ungroup() %>%
  # 将描述和数量列转成长格式
  pivot_longer(cols = c(Desc, Qty), names_to = "type", values_to = "value") %>%
  # 为子组添加序号，主组行序号设为NA
  group_by(Material, group_id, type) %>%
  mutate(sub_id = ifelse(Material_Group == "B", row_number(), NA)) %>%
  ungroup() %>%
  # 宽转长拆分主组与子组信息
  pivot_wider(
    id_cols = c(Material, group_id, sub_id),
    names_from = c(Material_Group, type),
    values_from = value,
    names_glue = "{Material_Group}_{type}"
  ) %>%
  # 向下填充主组信息
  fill(A_Desc, A_Qty, .direction = "down") %>%
  # 过滤掉无有效子组的行
  filter(!is.na(sub_id)) %>%
  # 重命名并整理列顺序
  rename(
    Main_Part = A_Desc,
    Main_Qty = A_Qty,
    Sub_Part = B_Desc,
    Sub_Qty = B_Qty
  ) %>%
  select(Material, Main_Part, Main_Qty, Sub_Part, Sub_Qty) %>%
  ungroup()

print(df_cleaned_2)

输出结果与方法1完全一致。

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