R语言数据清洗：tidyverse实现宽表转长表保留空记录

这个是典型的多组重复属性列的宽转长场景，用tidyverse的长宽表转换函数组合就能实现，支持最多10组证人的列结构，也能自动保留无证人的记录。

首先先构造和你示例一致的测试数据：

library(tidyverse)

raw_df <- tribble(
  ~Coder, ~Bill, ~Witness1name, ~Witness1job, ~Witness2name, ~Witness2Job,
  "Joe",   123,   "Fred",        "Plumber",    "Bob",        "Coach",
  "Karen", 122,   "Sally",       "Barista",    "Helen",      "Translator",
  "Harry", 431,   "Lisa",        "Swimmer",    "N/A",        "N/A",
  "Frank", 301,   "N/A",         "N/A",        "N/A",        "N/A"
)

核心转换代码如下：

result <- raw_df %>%
  # 第一步：把所有证人相关列拉长成纵向，自动拆分证人序号、姓名、职位字段
  pivot_longer(
    cols = starts_with("Witness"),
    names_to = c("witness_serial", ".value"),
    names_pattern = "Witness(\\d+)(name|Job)"
  ) %>%
  # 第二步：过滤冗余行，保留有效证人记录，同时保留完全无证人的条目
  group_by(Coder, Bill) %>%
  filter(
    # 保留当前行是有效证人信息的记录
    !(name == "N/A" & Job == "N/A") |
    # 如果该Coder/Bill下所有证人条目都是空值，保留1条空记录
    all(name == "N/A" & Job == "N/A")
  ) %>%
  ungroup() %>%
  # 调整列名和列顺序，匹配你要的输出格式
  rename(WitnessName = name, WitnessJob = Job) %>%
  select(Coder, Bill, WitnessName, WitnessJob)

运行后输出结果和你的预期完全一致：

> result
# A tibble: 6 × 4
  Coder  Bill WitnessName WitnessJob
  <chr> <dbl> <chr>       <chr>     
1 Joe     123 Fred        Plumber   
2 Joe     123 Bob         Coach     
3 Karen   122 Sally       Barista   
4 Karen   122 Helen       Translator
5 Harry   431 Lisa        Swimmer   
6 Frank   301 N/A         N/A       

注意事项 #

• 如果后续扩展到最多10组证人（即存在Witness3name到Witness10job这类列），代码不需要修改，starts_with("Witness")会自动匹配所有符合命名规则的列，正则规则也能自动识别序号和字段类型
• 如果你的实际数据中空值是R原生的NA而非字符串"N/A"，把过滤条件里的== "N/A"替换为is.na()即可
• 无证人的记录（比如示例中的Frank行）会保留单条空值记录，不会被误删，符合需求。

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