将列折叠/连接/聚合为每个组中单个逗号分隔的字符串

我想根据两个分组变量聚合数据框架中的一列，并用逗号分隔各个值。

下面是一些数据:

data <- data.frame(A = c(rep(111, 3), rep(222, 3)), B = rep(1:2, 3), C = c(5:10))
data
#     A B  C
# 1 111 1  5
# 2 111 2  6
# 3 111 1  7
# 4 222 2  8
# 5 222 1  9
# 6 222 2 10

“ A”和“ B”对变量进行分组，“ C”是要折叠成逗号分隔的 character字符串的变量。我试过了:

library(plyr)
ddply(data, .(A,B), summarise, test = list(C))


A B  test
1 111 1  5, 7
2 111 2     6
3 222 1     9
4 222 2 8, 10

但是当我试图将测试列转换成 character时，它变成了这样:

ddply(data, .(A,B), summarise, test = as.character(list(C)))
#     A B     test
# 1 111 1  c(5, 7)
# 2 111 2        6
# 3 222 1        9
# 4 222 2 c(8, 10)

如何保持 character格式并用逗号分隔它们？例如，第1行应该只是 "5,7"，而不是 c (5,7)。

49582 次浏览
小开

Change where you put as.character:

> out <- ddply(data, .(A, B), summarise, test = list(as.character(C)))
> str(out)
'data.frame':   4 obs. of  3 variables:
$ A   : num  111 111 222 222
$ B   : int  1 2 1 2
$ test:List of 4
..$ : chr  "5" "7"
..$ : chr "6"
..$ : chr "9"
..$ : chr  "8" "10"
> out
A B  test
1 111 1  5, 7
2 111 2     6
3 222 1     9
4 222 2 8, 10

Note in this case that each item is still actually a separate character, not a single character string. That is, this is not an actual string that looks like "5, 7", but rather, two characters, "5" and "7", which R displays with a comma between them.

Compare with the following:

> out2 <- ddply(data, .(A, B), summarise, test = paste(C, collapse = ", "))
> str(out2)
'data.frame':   4 obs. of  3 variables:
$ A   : num  111 111 222 222
$ B   : int  1 2 1 2
$ test: chr  "5, 7" "6" "9" "8, 10"
> out
A B  test
1 111 1  5, 7
2 111 2     6
3 222 1     9
4 222 2 8, 10

The comparable solution in base R is, of course, aggregate:

> A1 <- aggregate(C ~ A + B, data, function(x) c(as.character(x)))
> str(A1)
'data.frame':   4 obs. of  3 variables:
$ A: num  111 222 111 222
$ B: int  1 1 2 2
$ C:List of 4
..$ 0: chr  "5" "7"
..$ 1: chr "9"
..$ 2: chr "6"
..$ 3: chr  "8" "10"
> A2 <- aggregate(C ~ A + B, data, paste, collapse = ", ")
> str(A2)
'data.frame':   4 obs. of  3 variables:
$ A: num  111 222 111 222
$ B: int  1 1 2 2
$ C: chr  "5, 7" "9" "6" "8, 10"
小开
最佳答案

Here are some options using toString, a function that concatenates a vector of strings using comma and space to separate components. If you don't want commas, you can use paste() with the collapse argument instead.

data.table 

# alternative using data.table
library(data.table)
as.data.table(data)[, toString(C), by = list(A, B)]

aggregate This uses no packages:

# alternative using aggregate from the stats package in the core of R
aggregate(C ~., data, toString)

sqldf

And here is an alternative using the SQL function group_concat using the sqldf package :

library(sqldf)
sqldf("select A, B, group_concat(C) C from data group by A, B", method = "raw")

dplyr A dplyr alternative:

library(dplyr)
data %>%
group_by(A, B) %>%
summarise(test = toString(C)) %>%
ungroup()

plyr

# plyr
library(plyr)
ddply(data, .(A,B), summarize, C = toString(C))
小开

Here's the stringr/tidyverse solution:

library(tidyverse)
library(stringr)


data <- data.frame(A = c(rep(111, 3), rep(222, 3)), B = rep(1:2, 3), C = c(5:10))




data %>%
group_by(A, B) %>%
summarize(text = str_c(C, collapse = ", "))


# A tibble: 4 x 3
# Groups:   A [2]
A     B text
<dbl> <int> <chr>
1   111     1 5, 7
2   111     2 6
3   222     1 9
4   222     2 8, 10
小开

There is a small improvement here to avoid duplicates

# 1. Original data set
data <- data.frame(
A = c(rep(111, 3), rep(222, 3)),
B = rep(1:2, 3),
C = c(5:10))


# 2. Add duplicate row
data <- rbind(data, data.table(
A = 111, B = 1, C = 5
))


# 3. Solution with duplicates
data %>%
group_by(A, B) %>%
summarise(test = toString(C)) %>%
ungroup()


#      A     B test
#   <dbl> <dbl> <chr>
# 1   111     1 5, 7, 5
# 2   111     2 6
# 3   222     1 9
# 4   222     2 8, 10


# 4. Solution without duplicates
data %>%
select(A, B, C) %>% unique() %>%
group_by(A, B) %>%
summarise(test = toString(C)) %>%
ungroup()


#    A     B test
#   <dbl> <dbl> <chr>
# 1   111     1 5, 7
# 2   111     2 6
# 3   222     1 9
# 4   222     2 8, 10

Hope it can be useful.

小开

Using collap from collapse

library(collapse)
collap(data, ~ A + B, toString)
A B     C
1 111 1  5, 7
2 111 2     6
3 222 1     9
4 222 2 8, 10

data

data <- data.frame(A = c(rep(111, 3), rep(222, 3)), B = rep(1:2, 3), C = c(5:10))

提交答案