How to combine specific data across multiple rows in a dataframe in R

Question

I am looking to alter (concatenate, reshape I am not sure which word is right for this scenario) the data in my data frame by combining rows data cells across 1 column where the other columns in that row are identical.

Basically, I have something like this:

    >df
    >Person_id     System_id    Category    Type    Tag
    >1A            134          1            Chr     Question
    >1A            134          1            Chr     Answer
    >1A            134          1            Chr     Evaluation
    >1A            134          1            Chr     Overall
    >1A            134          1            Chr     Analysis
    >Z4            002          1            Chr     Question
    >Z4            002          1            Chr     Answer

And get it to look something like this:

    >Person_id     System_id    Category    Type    Tag
    >1A            134          1            Chr     Question, Answer, Evaluation, Overall, Analysis
    >Z4            002          1            Chr     Question, Answer

The Tags don't have to be separated by a comma , a space is fine. Any ideas where to look for a solution like this would be helpful.

Thank you.

Does this answer your question? [Collapse / concatenate / aggregate a column to a single comma separated string within each group](https://stackoverflow.com/questions/15933958/collapse-concatenate-aggregate-a-column-to-a-single-comma-separated-string-w) — camille, Jan 08 '20 at 16:53

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

We can group by the first four columns and paste the 'Tag' elements together

library(dplyr)
df %>%
   group_by_at(1:4) %>%
   summarise(Tag = toString(Tag))
# A tibble: 2 x 5
# Groups:   Person_id, System_id, Category [2]
#  Person_id System_id Category Type  Tag                                            
#  <chr>         <int>    <int> <chr> <chr>                                          
#1 1A              134        1 Chr   Question, Answer, Evaluation, Overall, Analysis
#2 Z4                2        1 Chr   Question, Answer

Or using base R

aggregate(Tag ~ ., df, toString)

NOTE: toString is a convenient wrapper for paste(., collapse=", ")

data

df <- structure(list(Person_id = c("1A", "1A", "1A", "1A", "1A", "Z4", 
"Z4"), System_id = c(134L, 134L, 134L, 134L, 134L, 2L, 2L), Category = c(1L, 
1L, 1L, 1L, 1L, 1L, 1L), Type = c("Chr", "Chr", "Chr", "Chr", 
"Chr", "Chr", "Chr"), Tag = c("Question", "Answer", "Evaluation", 
"Overall", "Analysis", "Question", "Answer")), 
 class = "data.frame", row.names = c(NA, 
-7L))

sm925 · Answer 2 · 2020-01-08T16:41:00.290

You can use paste0 with collapse = ", " to achieve this:

library(dplyr)
    df %>%
      group_by(Person_id, System_id, Category, Type) %>%
      summarise(Tag = paste0(Tag, collapse = ", "))

#Person_id System_id Category Type  Tag                                            
#  <chr>         <int>    <int> <chr> <chr>                                          
#1 1A              134        1 Chr   Question, Answer, Evaluation, Overall, Analysis
#2 Z4                2        1 Chr   Question, Answer

How to combine specific data across multiple rows in a dataframe in R

2 Answers2

data