在R中扩展两列逗号分隔数据，为重复值填充NA

原始数据框 #

structure(list(A = c("1,2,3", "1,2", "1,4"), B = c("X,Y", "X", 
"X"), ID = c(1, 2, 3), D = c(1, 2, 3)), class = "data.frame", row.names = c(NA, 
-3L))

目标结果 #

structure(list(A = c("1", "2", "3", "NA", "NA", "1", "2", "NA", 
"1", "4", "NA"), B = c("NA", "NA", "NA", "X", "Y", "NA", "NA", 
"X", "NA", "NA", "X"), ID = c(1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3
), D = c(1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3)), class = "data.frame", row.names = c(NA, 
-11L))

实现代码 #

使用tidyverse工具包可以快速完成需求，具体步骤如下：

1. 加载工具包

library(tidyverse)

2. 定义原始数据（如果已存在可跳过）

df <- structure(list(A = c("1,2,3", "1,2", "1,4"), B = c("X,Y", "X", 
"X"), ID = c(1, 2, 3), D = c(1, 2, 3)), class = "data.frame", row.names = c(NA, 
-3L))

3. 分别处理A、B列并合并结果

# 拆分A列，对应B列填充NA
df_a <- df %>%
  separate_rows(A, sep = ",") %>%
  mutate(B = NA_character_)

# 拆分B列，对应A列填充NA
df_b <- df %>%
  separate_rows(B, sep = ",") %>%
  mutate(A = NA_character_)

# 合并数据，排序后将NA替换为字符串"NA"
result <- bind_rows(df_a, df_b) %>%
  arrange(ID) %>%
  mutate(across(c(A, B), ~replace_na(.x, "NA")))

运行上述代码后，result即为目标数据框。

内容的提问来源于stack exchange，提问作者Gabriel G.