技术求助：使用R拆分含多种分隔符的多类别列

First, let's break down the approach: we'll standardize all delimiters to a single format, split the column into individual categories, then create binary columns for each unique category indicating presence/absence in each row.

Step-by-Step Explanation #

1. Clean the TYPE column: Convert all possible delimiters (,, ;, and, spaces) to commas, remove redundant separators, and trim extra spaces.
2. Split into categories: Break the cleaned string into a list of individual category labels.
3. Unnest and pivot: Expand the list into separate rows per category, then pivot back to create binary columns for each category.

Full Code Example #

First, let's recreate your sample data to verify:

# Sample data matching your description
my_table <- data.frame(
  ID = 1:5,
  TYPE = c("a", "a,b,c", "a;b,f", "f, b and c", "b"),
  TEXT = c("blue water", "fresh water", "cold stream", "lovely sunset", "something")
)

Now, use dplyr, stringr, and tidyr to process the data:

library(dplyr)
library(stringr)
library(tidyr)

# Clean and split the TYPE column
processed_table <- my_table %>%
  # Replace all delimiters with commas, clean up redundant separators
  mutate(
    TYPE_clean = str_replace_all(TYPE, "\\s*and\\s*", ","),  # Handle "and" with any surrounding spaces
    TYPE_clean = str_replace_all(TYPE_clean, "[; ]", ","),   # Replace semicolons and spaces with commas
    TYPE_clean = str_replace_all(TYPE_clean, ",+", ","),     # Collapse multiple commas into one
    TYPE_clean = str_trim(TYPE_clean),                       # Remove leading/trailing commas/spaces
    TYPE_clean = str_split(TYPE_clean, ",")                  # Split into list of categories
  ) %>%
  # Expand list into separate rows per category
  unnest_longer(TYPE_clean) %>%
  # Mark presence of each category
  mutate(present = 1) %>%
  # Pivot to create binary columns for each category
  pivot_wider(
    names_from = TYPE_clean,
    values_from = present,
    values_fill = 0,  # Fill missing values with 0 (not present)
    id_cols = c(ID, TYPE, TEXT)  # Keep original columns
  )

# View the result
print(processed_table)

Output #

ID TYPE           TEXT          a b c f
1  1    a     blue water         1 0 0 0
2  2 a,b,c   fresh water         1 1 1 0
3  3 a;b,f    cold stream        1 1 0 1
4  4 f, b and c lovely sunset    0 1 1 1
5  5    b      something         0 1 0 0

Key Notes #

• This handles all your specified delimiters (,, ;, and, spaces) in one pass.
• The values_fill = 0 ensures rows without a category get a 0 in that column.
• If you prefer TRUE/FALSE instead of 1/0, replace present = 1 with present = TRUE and values_fill = FALSE.

内容的提问来源于stack exchange，提问作者TheLousyLinguist