基于行中由R中特定字符分隔的数据创建新列

Question

我有下表

宠物	Housing_Type
A	猫；狗兔子	3
B	狗；兔子	2
C	猫	2
D	猫；兔子	3
E	猫；鱼	1

一种方法是定义一个匹配特定动物的辅助函数，然后将列绑定到原始框架。

请注意，为了消除空白来识别要查询的独特动物，需要进行一些争论。

f <- Vectorize(function(string, match) {
ifelse(grepl(match, string), "Y", "N")
}, c("match"))
df %>%
bind_cols(
f(df$Pets, unique(unlist(strsplit(trimws(as.character(df$Pets)), ";"))))
)
Owner            Pets House_Type Cats Dog Rabbit Fish
1     A Cats;Dog;Rabbit          3    Y   Y      Y    N
2     B      Dog;Rabbit          2    N   Y      Y    N
3     C           Cats           2    Y   N      N    N
4     D     Cats;Rabbit          3    Y   N      Y    N
5     E       Cats;Fish          1    Y   N      N    Y

或者更广义地说，如果您不确定分隔符是;，并且存在空白，则stringi是有用的：

dplyr::bind_cols(
df,
f(df$Pets, unique(unlist(stringi::stri_extract_all_words(df$Pets))))
)

您可以使用tidyr库中的separate_rows和pivot_wider：

library(tidyr)
library(dplyr)
Data_Pets %>%
separate_rows(Pets , sep = ";") %>%
mutate(Pets = trimws(Pets)) %>% 
mutate(temp = row_number()) %>% 
pivot_wider(names_from = Pets, values_from = temp) %>% 
mutate(across(c(Cats:Fish), function(x) if_else(is.na(x), "N", "Y"))) %>% 
dplyr::relocate(House_Type, .after = Fish)

将生成：

#   Owner Cats  Dog   Rabbit Fish  House_Type
# <fct> <chr> <chr> <chr>  <chr>      <int>
# 1 A     Y     Y     Y      N            3
# 2 B     N     Y     Y      N            2
# 3 C     Y     N     N      N            2
# 4 D     Y     N     Y      N            3
# 5 E     Y     N     N      Y            1

数据：

Data_Pets = structure(list(Owner = structure(1:5, .Label = c("A", "B", "C", "D",
"E"), class = "factor"), Pets = structure(c(2L, 5L, 1L,4L, 3L), .Label = c("Cats ",
"Cats;Dog;Rabbit", "Cats;Fish","Cats;Rabbit", "Dog;Rabbit"), class = "factor"), 
House_Type = c(3L,2L, 2L, 3L, 1L)), class = "data.frame", row.names = c(NA, -5L))