[jordan.trachtenberg]

I have some open-ended comment data that has been coded with a delimiter “, “. Each comment could have one or several tags. I’d like to be able to split this column by the delimiter and then pivot the data longer so that I can count the tag frequency associated with the comments (see sample data attached)

I’ve looked through this method (https://stackoverflow.com/questions/4350440/split-data-frame-string-column-into-multiple-columns), but my function is not working correctly, and it seems like a clunky solution.

Function:

# Function that splits a column by a particular delimiter
split_by_delimiter <- function(column, pattern, into_prefix) {
  cols <- str_split_fixed(column, pattern, n = Inf)
  cols[which(cols == "")] <- NA
  cols <- as_tibble(cols)
  # name the 'cols' tibble as 'into_prefix_1', 'into_prefix_2', ..., 'into_prefix_m'
  # where m = # columns of 'cols'
  m <- dim(cols)[2]
  names(cols) <- paste(into_prefix, 1:m, sep = "_")
}

# Applying function to data
delimited_data <- clean_data %>% 
  select(respondent_id, tags) %>% 
  bind_cols(split_by_delimiter(.$tags, pattern = ", ", into_prefix = "tag"))%>% 
  select(respondent_id, starts_with("tag_"))

Error message:

Error: Can't recycle ..1 (size 1998) to match ..2 (size 5).
Run rlang::last_error() to see where the error occurred.
> rlang::last_error()
<error/vctrs_error_incompatible_size>
Can't recycle ..1 (size 1998) to match ..2 (size 5).
Backtrace:
 1. %>%(...)
 4. vctrs::stop_incompatible_size(...)
 5. vctrs:::stop_incompatible(...)
 6. vctrs:::stop_vctrs(...)

sample_data_delimiter.csv 1 KB CSV — Click to view

• Copy Download Link

respondent_id,question_number,comment,tags,sentiment 12299210937,q1_open,Test comment 1,"Online Learning, Improved Teaching",Neutral 12299210937,q2_open,Test comment 2,"FAC Authority, Personal Impacts",Negative 12299210937,q3_open,Test comment 3,"Biased Survey, Personal Impacts",Negative 12293027241,q1_open,Other comment 1,"High Faculty Effort, Work-Life Balance",Neutral 12293027241,q2_open,Other comment 2,High Faculty Effort,Negative 12293027241,q3_open,Other comment 3,"Teaching Eval Alternative, Low Student Effort, High Faculty Effort",Negative 12290417487,q1_open,Another comment 1,"Online Learning, Improved Teaching",Positive 12290417487,q2_open,Another comment 2,"Connections with Students, Personal Impacts, Professional Development",Negative 12290417487,q3_open,Another comment 3,"Department Support, Institute Support",Positive 12286774660,q1_open,skipped,NA,NA 12286774660,q2_open,skipped,NA,NA 12286774660,q3_open,skipped,NA,NA 12285154862,q1_open,skipped,NA,NA 12285154862,q2_open,skipped,NA,NA 12285154862,q3_open,Comment 3,Dependent Care,Negative 12283796688,q1_open,Text 1,Collaboration,Positive 12283796688,q2_open,skipped,NA,NA 12283796688,q3_open,skipped,NA,NA

Click to expand

[David]

Great question, Jordan! One I had myself not too long ago. I recorded a video to show you how I’d solve this. Let me know if this makes sense!

[jordan.trachtenberg]

Thank you, David. Your solution is about 1000x easier. I’m used to working in Power Query to clean data, and I always have to split into columns before pivoting longer, so that’s what I thought I’d have to do in R.

This was my second attempt at a solution, but it means I would manually have to update the column names if I had more than 5 tags, which is horrible.

separate(col = tags,
           into = c("tag1", "tag2", "tag3", "tag4", "tag5"), # Added 5 columns for now, may need more
           sep = ", ", 
           extra = "merge",
           fill = "right") %>% 
  pivot_longer(cols = c(contains("tag") & !contains("delimited")), 
               names_to = "tag_number",
               values_to = "tag_value") %>% 
  drop_na(tag_value)

# Checking to see if data table has any more delimiters ", "
# If so, need to add more column names to the separate() function above
clean_data %>% 
  select(tag_value) %>% 
  filter(grepl(", ", tag_value))

I will definitely use this instead!

separate_rows(tags, sep = ", ")

[David]

Glad to help!