Group same value and concat the value in another column in R

Question

I'm trying to sequence a dataset, and I'm a little lost with this.

I would like to get this result and save it to a csv file:

Any idea on how to do this? Thanks in advance!

Answer 1

You can do it easily with data table:

library(data.table)
#create table
ID <- c("ABC","BDE", "ABC","ABC","CDE")
Movie_Code <- c("A10","A11","A12","A13","A14")
df <- data.frame(ID, Movie_Code)
#convert to data table
df<-data.table(df)
setkey(df, ID)
final_tab<-df[, paste(unique(Movie_Code), collapse=", "),by=ID]
#CSV: comma separated values

Output:

    ID            V1
1: ABC A10, A12, A13
2: BDE           A11
3: CDE           A14

From: Daniel Bachen

Answer 2

With tidyverse you can group_by your ID and then summarise. Use collapse to indicate what you want to use a separator (in this case, semicolon) for paste.

library(tidyverse)

df %>%
  group_by(ID) %>%
  summarise(MovieCode = paste(MovieCode, collapse = ";"))

Or in base R, use aggregate:

aggregate(MovieCode ~ ID, df, paste, collapse = ";")

Output

   ID   MovieCode
1 ABC A10;A12;A13
2 BDE         A11
3 CDE         A14

Data

df <- structure(list(ID = c("ABC", "BDE", "ABC", "ABC", "CDE"), MovieCode = c("A10", 
"A11", "A12", "A13", "A14")), class = "data.frame", row.names = c(NA, 
-5L))

Answer 3

Here's a base R solution:

Illustrative data:

df <- data.frame(
  v1 = c("ABA", "BCB", "ABA", "BCB", "DCD"),
  v2 = letters[1:5]
)

Solution:

df1 <- data.frame(
  v1_unique = unique(df$v1),
  v2_paste = tapply(df$v2, df$v1, paste0, collapse = ";"), row.names = NULL)

Result:

df1
  v1_unique v2_paste
1       ABA      a;c
2       BCB      b;d
3       DCD        e