I've the following table

The code is as follows:

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))

Can anyone advise me how I can create new columns based on the data in Pet column by creating a new column for each animal separated by ; to look like the following table?

Thanks!

CodePudding user response：

One approach is to define a helper function that matches for a specific animal, then bind the columns to the original frame.

Note that some wrangling is done to get rid of whitespace to identify the unique animals to query.

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

Or more generalized if you don't know for sure that the separator is ;, and whitespace is present, stringi is useful:

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

CodePudding user response：

You can use separate_rows and pivot_wider from tidyr library:

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)

which will generate:

#   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:

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))