Collapsing text in a single line in a database

Question

Collapsing text in a single line in a database

Navigation

#1 by (3 votes)
#2 by (2 votes)

4

Suppose I have the following data

tabela<-structure(list(nome = structure(c(2L, 9L, 6L, 1L, 8L, 3L, 4L, 
                                       5L, 7L, 10L, 11L), .Label = c("12 Anos de EscravidÃ£o", "A CaÃ§a", 
                                                                     "Ela", "Gravidade", "O Hobbit: A DesolaÃ§Ã£o de Smaug", "O Lobo de Wall Street", 
                                                                     "Os Suspeitos", "Rush: No Limite da EmoÃ§Ã£o", "The Avengers - Os Vingadores", 
                                                                     "The Grand Budapest Hotel", "Uma Aventura LEGO"), class = "factor"), 
                    ano = c(2012L, 2012L, 2013L, 2013L, 2013L, 2013L, 2013L, 
                            2013L, 2013L, 2014L, 2014L)), .Names = c("nome", "ano"), row.names = 240:250, class = "data.frame")

How do I collapse the "name" text column so that my date frame looks like this:

    ano  quantidade nomes
    2012    2       A Caça, The Avengers - Os Vingadores
    2013    7       O Lobo de Wall Street, 12 Anos de Escravidão, Rush: No Limite da Emoção, Ela, Gravidade, O Hobbit: A Desolação de Smaug, Os Suspeitos
    2014    2       The Grand Budapest Hotel, Uma Aventura LEGO

r data.frame

asked by anonymous 02.04.2014 / 18:52

2 answers

2

You can also use the sqldf package

library(sqldf)
tabela2 <- sqldf("SELECT ano, count(nome) as quantidade, group_concat(nome) as nomes 
             FROM tabela 
             GROUP by ano")

03.04.2014 / 21:06

How to put one element before another with jQuery? Give height and overflow-y in a div

score 3 · Accepted Answer

You can do with dplyr using the collapse option of paste :

library(dplyr)
tabela2<-tabela %>% group_by(ano) %>% summarise(quantidade = n(),
                                               nomes = paste(nome,collapse=", "))

Out of curiosity I'll also put plyr (but dplyr is faster).

library(plyr)
tabela2<- ddply(tabela, .(ano), summarise, quantidade=length(nome), nomes=paste(nome, collapse=","))