R&SQL合并记录并去重

======================================================================================

 R&SQL

library(sqldf)

sqldf("
SELECT ID,
GROUP_CONCAT(distinct info1) as info1
FROM have
GROUP BY ID")

注：此种情况没能实现更改间隔符，默认为逗号。

======================================================================================

 R

--------------------------------------------------------------------------------------------------------------------------------------

require(dplyr)

METHOD1:　　have %>%　　　　group_by(ID) %>%　　　　summarise_each(funs(toString(sort(unique(info1)))))

METHOD2:　　have %>%　　　　group_by(ID) %>%　　　　summarise(name = toString(sort(unique(info1))))

--------------------------------------------------------------------------------------------------------------------------------------

require(data.table)

METHOD1:　　setDT(have)[, .(info1 = toString(sort(unique(info1)))), by = ID]
METHOD2:　　setDT(have)[ , .(info1 = paste(unique(info1), collapse = ",")),by = ID]

注：paste函数作为其他函数的参数时，不能。例如

aggregate(have[,2], by=list(have$ID), paste(unique(info1)), collapse=";")   × 

aggregate(have[,2], by=list(have$ID), paste, collapse=";")  √

setDT(have)[, lapply(.SD, paste(unique(info1)), collapse = "; "), by = ID]  ×

setDT(have)[, lapply(.SD, paste, collapse = "; "), by = ID]  √

--------------------------------------------------------------------------------------------------------------------------------------

aggregate(data=have,info1~ID,FUN = function(t) sort(unique(t)))

R&SQL合并记录并去重

标签：string   user   table   lis   info   tor   tin   code   color