long to wide format, but without condition

Question

I have data as below

ID City
x  Paris
x  Madrid
y  Paris
y  Prague
y  Moscow
z  Moscow

I have to obtain data as below

ID City
x  Paris, Madrid
y  Paris, Prague, Moscow
z  Moscow

How can I do this?

Answer 1

Here is an option with aggregate from base R

aggregate(City ~ ID, df1, toString)

-output

# ID                  City
#1  x         Paris, Madrid
#2  y Paris, Prague, Moscow
#3  z                Moscow

data

df1 <- structure(list(ID = c("x", "x", "y", "y", "y", "z"), City = c("Paris", 
"Madrid", "Paris", "Prague", "Moscow", "Moscow")), class = "data.frame",
row.names = c(NA, 
-6L))

Answer 2

You can also try reshaping:

library(dplyr)
library(tidyr)
#Code
res <- df %>% 
  mutate(var='City') %>%
  pivot_wider(values_fn = function(x) paste0(x,collapse = ', '),
                   names_from=var,values_from=City)

Output:

# A tibble: 3 x 2
  ID    City                 
  <chr> <chr>                
1 x     Paris, Madrid        
2 y     Paris, Prague, Moscow
3 z     Moscow               

But @akrun solution is more practical and only requires one function.

Some data used:

#Data
df <- structure(list(ID = c("x", "x", "y", "y", "y", "z"), City = c("Paris", 
"Madrid", "Paris", "Prague", "Moscow", "Moscow")), class = "data.frame", row.names = c(NA, 
-6L))

Answer 3

Another base R option using stack + tapply

> with(df, rev(stack(tapply(City, ID, toString))))
  ind                values
1   x         Paris, Madrid
2   y Paris, Prague, Moscow
3   z                Moscow

---

A data.table option

> setDT(df)[, lapply(.SD, toString), ID]
   ID                  City
1:  x         Paris, Madrid
2:  y Paris, Prague, Moscow
3:  z                Moscow