How to collapse multiple rows with condition into one row using dplyr in r?-CodePudding

I'll illustrate my question with an example.

Sample data:

df <- data.frame(ID = 1:5, Description = c("'foo' is a dog", "'bar' is a dog", "'foo' is a cat", "'foo' is not a cat", "'bar' is a fish"), Category = c("A", "A", "B", "B", "C"))

> df
 ID      Description Category
1  1     'foo' is a dog        A
2  2     'bar' is a dog        A
3  3     'foo' is a cat        B
4  4     'foo' is not a cat    B
5  5     'bar' is a fish       C

What I want to do is to collapse similar description/ID for the same category, expected output:

 ID  Description          Category
1 3     B        ‘foo’ is a cat    
2 1,2   A        ‘foo,bar’ is a dog
3 5     C        ‘bar’ is a fish   
4 4     B        ‘foo’ is not a cat

I would like to start to use dplyr, but I was not able to have a complete idea how to achieve this, can anyone help me out?

df %>% 
  group_by(Category) %>% 
  ## some condition to check if content outside of single quote are the same. 
  ## If so, collapse them into one row, otherwise, leave as it is. 
  ## The regex to get the content outside of single quote 
     `gsub("^'(.*?)'.*", "\\2", x)` 
  ## then collapse 
  summarise(new description = paste())

CodePudding user response：

Just figure it out and please feel free to suggest a better solution:

df %>% 
  mutate(sec = gsub("^'.*?'(.*)", "\\1", Description),
         content = gsub("^'(.*?)'.*", "\\1", Description)) %>% 
  group_by(sec, Category) %>%
  summarise(
    ID=str_c(unique(ID), collapse=","),
    content=str_c(unique(content), collapse=",")) %>%
  mutate(Description=str_c(sQuote(content), sec)) %>%
  ungroup() %>%
  dplyr::select(ID, Category, Description)

CodePudding user response：

Here's another way to achieve the output.

library(tidyverse)

df %>%
  mutate(value = str_extract(Description, "'\\w '"), 
         Description = trimws(str_remove(Description, value))) %>%
  group_by(Description, Category) %>%
  summarise(ID = toString(ID), 
            value = sprintf("'%s'", toString(gsub("'", "", value)))) %>%
  unite(Description, value, Description, sep = ' ')

#  Description         Category ID   
#  <chr>               <chr>    <chr>
#1 'foo' is a cat      B        3    
#2 'foo, bar' is a dog A        1, 2 
#3 'bar' is a fish     C        5    
#4 'foo' is not a cat  B        4