What is the fastest way to perform concate-like operation over a data.frame in R? Suppose I have the following table:
df <- data.frame(content = c("c1", "c2", "c3", "c4", "c5"),
groups = c("g1", "g1", "g1", "g2", "g2"),
stringsAsFactors = F)
df$groups <- as.factor(df$groups)
I want to concate the content of cells in content column, by groups, efficiently, to receive the equivalent to:
df2 <- data.frame(content = c("c1 c2 c3", "c4 c5"),
groups = c("g1", "g2"),
stringsAsFactors = F)
df2 $groups <- as.factor(df2 $groups)
I would prefer some dplyr operation, but have no good idea how to apply it.
Here's a method using base's tapply
splat<-with(df, tapply(content, groups, paste, collapse=" "))
df2<-data.frame(groups=names(splat), content=splat, stringsAsFactors=F)
df2$groups <- as.factor(df2$groups)
which gives you
# groups content
# g1 g1 c1 c2 c3
# g2 g2 c4 c5
(the extra "g1/g2"s are the rownames of the data.frame)
A close relative to tapply is aggregate, which lets you do this:
aggregate(content ~ groups, df, paste, collapse = " ")
# groups content
# 1 g1 c1 c2 c3
# 2 g2 c4 c5
Factors are retained:
str(.Last.value)
# 'data.frame': 2 obs. of 2 variables:
# $ groups : Factor w/ 2 levels "g1","g2": 1 2
# $ content: chr "c1 c2 c3" "c4 c5"
Since you mention that you are looking for a dplyr approach, you can try something like this:
library(dplyr)
df %>% group_by(groups) %>% summarise(content = paste(content, collapse = " "))
# Source: local data frame [2 x 2]
#
# groups content
# 1 g1 c1 c2 c3
# 2 g2 c4 c5
Using data.table:
library(data.table)
dt = as.data.table(df)
dt[, paste(content, collapse = " "), by = groups]
# groups V1
#1: g1 c1 c2 c3
#2: g2 c4 c5
Since speed was mentioned in OP, data.table and dplyr are fairly close (the base methods are super slow, no point in testing them):
dt = data.table(content = sample(letters, 26e6, T), groups = LETTERS)
df = as.data.frame(dt)
system.time(dt[, paste(content, collapse = " "), by = groups])
# user system elapsed
# 5.37 0.06 5.65
system.time(df %>% group_by(groups) %>% summarise(paste(content, collapse = " ")))
# user system elapsed
# 7.10 0.13 7.67
