CODEWITHSUNDEEP
r
jenswirf
jenswirf

Reputation: 7317

Cast/transpose a data frame string column into a single row

Trying to make this:

id |               name
-----------------------
10 |        After Earth
10 | Battle of the Year
10 |   Captain Phillips
12 |             Carrie
12 |  Chernobyl Diaries

become this:

id |        big_ass_string
--------------------------
10 |  After Earth Battle of the Year Captain Phillips
12 |  Carrie Chernobyl Diaries

That is, "cast/transpose" a bunch of strings in a column onto a single row (one big string) grouped by id. I'm using the terms "cast" and "transpose" here, but that's not quite right as neither of those functions will work. Any ideas how to achieve this?

require(dplyr)

x = data_frame(id = c(10,10,10,12,12), name = c("After Earth","Battle of the Year","Captain Phillips","Carrie","Chernobyl Diaries"))

Upvotes: 2

Views: 151

Answers (3)

MikeRSpencer
MikeRSpencer

Reputation: 1316

If you're specifically after a data frame, this is less tidy than dplyr as it outputs to a list. Syntactically it's elegant though:

tapply(x$name, x$id, paste)

Upvotes: 0

Sidhha
Sidhha

Reputation: 268

aggregate(name~id, FUN='paste', x, collapse=' ')

Upvotes: 1

akrun
akrun

Reputation: 887183

I guess the OP is using dplyr. In that case, we can group by 'id' column and summarise to create the variable 'big_ass_string' by pasteing the 'name' column.

 library(dplyr)
 x %>% 
     group_by(id) %>% 
     summarise(big_ass_string=paste(name, collapse=' '))
 #  id                                  big_ass_string
 #1 10 After Earth Battle of the Year Captain Phillips
 #2 12                        Carrie Chernobyl Diaries

Or a base R option with aggregate would be using the formula method with grouping variable on the RHS of ~, 'name' variable on the LHS, and we specify the FUN as paste with collapse = ' '

 aggregate(cbind(big_ass_string=name)~id, x, FUN= paste, collapse=' ')
 #  id                                  big_ass_string
 #1 10 After Earth Battle of the Year Captain Phillips
 #2 12                        Carrie Chernobyl Diaries

Upvotes: 2

Related Questions