如何在R中将同一行中以逗号分隔的值拆分为不同的行？

Question

我有一些来自Google表单的数据，我想要拆分逗号分隔的答案并复制参与者的ID

• 数据看起来像这样:

> head(data)
   names         Q2               Q3                          Q4
1 PART_1     fruits    bananas, apples        brocolli, lettuce, potatoes
2 PART_2 vegetables   bananas, oranges                    brocolli
3 PART_3     fruits                        carrots, brocolli, lettuce

• 期望输出 #1 (使用 Nas 填充):

 names         Q2               Q3                          Q4
1 PART_1     fruits        bananas                brocolli  
  PART_1       NA          apples                 lettuce,
  PART_1       NA           NA                    potatoes
so on...

• 期望输出 #2（重复非多项选择题的答案（如Q1））：

 names         Q2               Q3                          Q4
1 PART_1     fruits        bananas                   brocolli  
  PART_1     fruits           apples                 lettuce,
  PART_1     fruits           NA                    potatoes
so on...

• 如果可以的话，tidyverse解决方案将不胜感激！

注：这个想法与SQL问题非常相似。 我看到了这个R问题，但我想重复参与者的名字，而不是重命名它们

• 数据：

structure(list(names = c("PART_1", "PART_2", "PART_3"), Q2 = c("fruits", 
"vegetables", "fruits"), Q3 = c("bananas, apples", "bananas, oranges", 
""), Q4 = c("brocolli, lettuce, potatoes", "brocolli", "carrots, brocolli, lettuce"
)), class = "data.frame", row.names = c(NA, -3L))

- Larissa Cury

Answer 1

您可以做以下操作：

library(tidyr)
library(dplyr)

dat %>% 
  pivot_longer(-c(Q2, names)) %>%
  separate_rows(value) %>%
  group_by(names, name) %>%
  mutate(row = row_number()) %>%
  pivot_wider() %>%
  select(-row)

# A tibble: 8 × 4
# Groups:   names [3]
  names  Q2         Q3        Q4      
  <chr>  <chr>      <chr>     <chr>   
1 PART_1 fruits     "bananas" brocolli
2 PART_1 fruits     "apples"  lettuce 
3 PART_1 fruits      NA       potatoes
4 PART_2 vegetables "bananas" brocolli
5 PART_2 vegetables "oranges" NA      
6 PART_3 fruits     ""        carrots 
7 PART_3 fruits      NA       brocolli
8 PART_3 fruits      NA       lettuce 

- Ritchie Sacramento

Answer 2

使用separate_rows目前不能直接实现此操作，即使它是tidyverse的一种方式，但您可以使用来自splitstackshape的cSplit：

library(dplyr)
library(splitstackshape)
cSplit(data, c("Q3", "Q4"), sep = ", ", "long") %>% 
  filter(if_any(c("Q3", "Q4"), complete.cases))

#    names         Q2      Q3       Q4
# 1 PART_1     fruits bananas brocolli
# 2 PART_1     fruits  apples  lettuce
# 3 PART_1     fruits    <NA> potatoes
# 4 PART_2 vegetables bananas brocolli
# 5 PART_2 vegetables oranges     <NA>
# 6 PART_3     fruits    <NA>  carrots
# 7 PART_3     fruits    <NA> brocolli
# 8 PART_3     fruits    <NA>  lettuce 

- Maël

Answer 3

使用基础R

lst1 <- lapply(data[3:4], strsplit, split = ",\\s+")
 mx <- do.call(pmax, lapply(lst1, lengths))
lst2 <- lapply(lst1, \(x) unlist(Map(`length<-`, x, mx)))
out <- cbind(data[rep(seq_len(nrow(data)), mx), 1:2], lst2)
row.names(out) <- NULL

-输出

> out
   names         Q2      Q3       Q4
1 PART_1     fruits bananas brocolli
2 PART_1     fruits  apples  lettuce
3 PART_1     fruits    <NA> potatoes
4 PART_2 vegetables bananas brocolli
5 PART_2 vegetables oranges     <NA>
6 PART_3     fruits    <NA>  carrots
7 PART_3     fruits    <NA> brocolli
8 PART_3     fruits    <NA>  lettuce

- akrun