How to split character column at divider and create new binary columns?

I have a dataframe df_cat looking like this:

dput(df_cat)

structure(list(Name = c("Name1", "Name2", "Name3", "Name4", "Name5", 
"Name6"), Category = c("App", "App, Comms_2", "App, Comms, Tech", 
"App, Tech", "Comms, Tech", "BS, Tech")), class = "data.frame", row.names = c(NA, 
-6L))

I want to split column Category at the divider "," and create new columns for each input with the original column name in front. These columns should be binary and indicate, whether a row contains this category (1) or not (0):

Name   Category_App  Category_Comms_2  Category_Tech  Category_BS
Name1  1             0                 0              0
Name2  1             1                 0              0
Name3  1             1                 1              0
Name4  1             0                 1              0
Name5  0             1                 1              0
Name6  0             0                 1              1

Any idea how I can accomplish this in a dataset, that has over 23k rows and over 100 different categories?

This can be done easily with separate_rows and pivot_wider:

library(tidyverse)
df %>%
   mutate(row = row_number()) %>%
   separate_rows(Category) %>%
   pivot_wider(names_from = Category, values_from = Category,
               names_prefix = "Category_",
               values_fn = function(x) 1, values_fill = 0) %>%
   select(-row)
# A tibble: 6 × 5
  Name  Category_App Category_Comms Category_Tech Category_BS
  <chr>        <dbl>          <dbl>         <dbl>       <dbl>
1 Name1            1              0             0           0
2 Name2            1              1             0           0
3 Name3            1              1             1           0
4 Name4            1              0             1           0
5 Name5            0              1             1           0
6 Name6            0              0             1           1

If you want to retain the original category column, add it with bind_cols:

df %>%
   mutate(row = row_number()) %>%
   separate_rows(Category) %>%
   pivot_wider(names_from = Category, values_from = Category,
               names_prefix = "Category_",
               values_fn = function(x) 1, values_fill = 0) %>%
   select(-row) %>%
   bind_cols(., df %>% select(Category))
# A tibble: 6 × 6
  Name  Category_App Category_Comms Category_Tech Category_BS Category        
  <chr>        <dbl>          <dbl>         <dbl>       <dbl> <chr>           
1 Name1            1              0             0           0 App             
2 Name2            1              1             0           0 App, Comms      
3 Name3            1              1             1           0 App, Comms, Tech
4 Name4            1              0             1           0 App, Tech       
5 Name5            0              1             1           0 Comms, Tech  
6 Name6            0              0             1           1 BS, Tech

If you know the possible categories up front, you could even just hard-code them:

library(tidyverse)

df <- tibble::tribble(
          ~Name,          ~Category,
        "Name1",              "App",
        "Name2",       "App, Comms",
        "Name3", "App, Comms, Tech",
        "Name4",        "App, Tech",
        "Name5",      "Comms, Tech",
        "Name6",         "BS, Tech"
        )

df |> 
  mutate(
    Category_App = str_detect(Category, "App"),
    Category_Comms = str_detect(Category, "Comms"),
    Category_Tech = str_detect(Category, "Tech"),
    Category_BS = str_detect(Category, "BS")
  )
#> # A tibble: 6 × 6
#>   Name  Category         Category_App Category_Comms Category_Tech Category_BS
#>   <chr> <chr>            <lgl>        <lgl>          <lgl>         <lgl>      
#> 1 Name1 App              TRUE         FALSE          FALSE         FALSE      
#> 2 Name2 App, Comms       TRUE         TRUE           FALSE         FALSE      
#> 3 Name3 App, Comms, Tech TRUE         TRUE           TRUE          FALSE      
#> 4 Name4 App, Tech        TRUE         FALSE          TRUE          FALSE      
#> 5 Name5 Comms, Tech      FALSE        TRUE           TRUE          FALSE      
#> 6 Name6 BS, Tech         FALSE        FALSE          TRUE          TRUE

^{Created on 2023-03-20 with reprex v2.0.2}

Using data.table

library(data.table)
setDT(df)

df[, unlist(strsplit(Category, "(,| )+")), by = Name
   ][, dcast(.SD, Name ~ paste0("Category_", V1), length)]

#      Name Category_App Category_BS Category_Comms Category_Tech
#    <char>        <int>       <int>          <int>         <int>
# 1:  Name1            1           0              0             0
# 2:  Name2            1           0              1             0
# 3:  Name3            1           0              1             1
# 4:  Name4            1           0              0             1
# 5:  Name5            0           0              1             1
# 6:  Name6            0           1              0             1

library(stringr)
library(magrittr)

# get vector of all the possible categories

all_categories <- df_cat$Category %>% 
                   str_split(",") %>%      # split at comma
                   unlist() %>%            # convert from list to vector
                  str_squish() %>%         # remove excess white space
                    unique()               # find unique values


# loop through all the categories 
for( category in all_categories){
  
  # create a column for each category
  # Then check for the category in the Category column
  df_cat[,category] <- str_detect(df_cat$Category,category) %>%      
                        as.numeric()               # convert TRUE/FALSE to 0/1
  
  
  
}

in base R you could do:

a <- table(stack(setNames(strsplit(df$Category, "[, ]+"), df$Name))[2:1])

data.frame(df, as.data.frame.matrix(a), row.names = NULL)

   Name         Category App BS Comms Tech
1 Name1              App   1  0     0    0
2 Name2       App, Comms   1  0     1    0
3 Name3 App, Comms, Tech   1  0     1    1
4 Name4        App, Tech   1  0     0    1
5 Name5      Comms, Tech   0  0     1    1
6 Name6         BS, Tech   0  1     0    1