How to arrange elements belonging to one group in a single line

Question

My data is like this-

p1  x1
p2  x2
p1  x3
p7  x4
p5  x5
p2  x6
p5  x7
p7  x8
p1  x9
p2  x10

I want to arrange it like this-

p1  x1,x2,x9
p2  x2,x6,x10
p7  x4,x8
p5  x5,x7

How do I do this using R/ the command-line or Excel?

Answer 1

We can use aggregate

aggregate(col2 ~ col1, df1, toString)

if there are duplicate elements, get the unique rows and do the aggregate

aggregate(col2 ~ col1, unique(df1), toString)

Answer 2

You can use dpylr to group_by the first column, and then paste the second column by group:

library(dplyr)

group_by(df, c1) %>% summarise(c2 = paste(c2, collapse = ","))

#### OUTPUT ####

# A tibble: 4 x 2
  c1    c2       
  <fct> <chr>    
1 p1    x1,x3,x9 
2 p2    x2,x6,x10
3 p5    x5,x7    
4 p7    x4,x8