如何使用pivot_wider处理多组key-value对的注册数据格式转换？

可以通过先转长格式再转宽格式的两步操作实现，使用tibble和tidyr包的函数即可完成：

步骤说明 #

1. 转长格式：将分散的optionX和valueX列合并为统一的option和value列，同时保留分组标识（数字后缀）。
2. 过滤无效行：移除option为NA的无意义行。
3. 转宽格式：将option中的值作为新列名，value中的内容填充到对应列。

完整代码 #

library(tidyr)
library(dplyr)

# 原始数据
reg <- tibble(
  name = c("John","Bill","Chuck"),
  email= c("john@john.com","bill@acme.com","chuck@someplace.org"),
  option1=c("meal","meal","drink"),
  value1=c("vegetarian","vegetarian","beer"),
  option2=c(NA,"drink","room"),
  value2=c(NA,"beer","single"),
  option3=c(NA,"room",NA),
  value3=c(NA,"single",NA)
)

# 格式转换
reg_wide <- reg %>%
  pivot_longer(
    cols = -c(name, email),
    names_to = c(".value", "group"),
    names_pattern = "(option|value)(\\d+)"
  ) %>%
  filter(!is.na(option)) %>%
  pivot_wider(
    names_from = option,
    values_from = value
  ) %>%
  select(-group) %>%
  arrange(match(name, reg$name))

# 查看结果
print(reg_wide)

结果输出 #

# A tibble: 3 × 5
  name  email               meal       drink room  
  <chr> <chr>               <chr>      <chr> <chr> 
1 John  john@john.com       vegetarian NA    NA    
2 Bill  bill@acme.com       vegetarian beer  single
3 Chuck chuck@someplace.org NA         beer  single

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