在R 3.3.0 Dplyr v 0.5.0中聚合为字符串并对与聚合相关的值求和

Question

问题:

我想简化以下数据框

Fruit <-  c("Apple","Apple","Orange","Orange","Banana","Banana")
Farmer <- c("Bob","Ben","Bill","Bob","George","Bob")
Tons.Jan <- c(20,40,10,20,35,15)
Tons.Feb <- c(30,40,20,15,25,30)
Tons.Mar <- c(10,10,15,10,20,30)
Tons.Apr <- c(15,20,15,30,30,30)
Tons.May <- c(20,5,20,20,20,10)

df <- cbind(Fruit,Farmer)
df <- cbind(df,Tons.Jan)
df <- cbind(df,Tons.Feb)
df <- cbind(df,Tons.Mar)
df <- cbind(df,Tons.Apr)
df <- tbl_df(cbind(df,Tons.May))

我希望能够将农民总结为一个单独的逗号，并将吨与观察结果相加，使其看起来像以下内容

我想谈以下几点

Fruit2 <- c("Apple","Orange","Banana")
Farmer2 <- c("Bob,Ben","Bill,Bob","George,Bob")
Tons.Jan2 <- c(60,30,50)
Tons.Feb2 <- c(70,35,55)
Tons.Mar2 <- c(20,25,50)
Tons.Apr2 <- c(35,45,60)
Tons.May2 <- c(25,40,30)

df2 <- cbind(Fruit2,Farmer2)
df2 <- cbind(df2,Tons.Jan2)
df2 <- cbind(df2,Tons.Feb2)
df2 <- cbind(df2,Tons.Mar2)
df2 <- cbind(df2,Tons.Apr2)
df2 <- tbl_df(cbind(df2,Tons.May2))

我所尝试的：

我尝试过使用dplyr函数组，并在下面对每个函数进行总结

df <- df %>% group_by(Fruit) %>%
   summarise_each_(funs(toString))

然而，我不确定如何集成求和数值，而不使用摘要函数专门调用每一列，

感谢您的帮助。

Answer 1

最好不要做数据。frame（cbind（或tbl_df（cbind作为cbind将向量s绑定到矩阵，矩阵只能容纳一个类，因此当我们将矩阵更改为数据时，frame（默认选项为stringsAsFactors=TRUE），如果存在任何字符向量，矩阵将是所有字符类列，这会变得更糟，因为这些列现在是系数类和数据。帧转换。因此，我们不必要地必须按要求执行。数值（如.character（用于更改数值列的类型。最好将“data.frame”构造为

data.frame(Fruit, Farmer, Tons.Jan, ...)

一个data.table解决方案将是

library(data.table)
setDT(df)[,  Farmer :=  toString(Farmer), by = Fruit][ , 
     lapply(.SD, function(x) sum(as.numeric(as.character(x)))) , .(Fruit, Farmer)]
#    Fruit                   Farmer Tons.Jan Tons.Feb Tons.Mar Tons.Apr Tons.May
#1:  Apple       Bob, Ben, Bob, Ben       60       70       20       35       25
#2: Orange     Bill, Bob, Bill, Bob       30       35       25       45       40
#3: Banana George, Bob, George, Bob       50       55       50       60       30

此外，这可以通过“水果”分组（基于OP的输出）一步完成

setDT(df)[, c(Farmer = toString(Farmer), lapply(.SD[, 
   setdiff(names(.SD), "Farmer"), with = FALSE], 
       function(x) sum(as.numeric(as.character(x))))), .(Fruit)]
#    Fruit      Farmer Tons.Jan Tons.Feb Tons.Mar Tons.Apr Tons.May
#1:  Apple    Bob, Ben       60       70       20       35       25
#2: Orange   Bill, Bob       30       35       25       45       40
#3: Banana George, Bob       50       55       50       60       30

Answer 2

library(dplyr)

# Convert the relevant columns to numeric
df <- mutate_each(df, funs(as.numeric), -Fruit, -Farmer)

# or as mentioned in the comments by jazzurro
df <- mutate_at(df, vars(starts_with("Tons")), as.numeric)

df %>% 
    group_by(Fruit) %>% 
    mutate(Farmer = toString(Farmer)) %>%
    group_by(Fruit, Farmer) %>%
    summarise_all(funs(sum))


#Source: local data frame [3 x 7]
#Groups: Fruit [?]
#
#   Fruit      Farmer Tons.Jan Tons.Feb Tons.Mar Tons.Apr Tons.May
#   <chr>       <chr>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>
#1  Apple    Bob, Ben       60       70       20       35       25
#2 Banana George, Bob       50       55       50       60       30
#3 Orange   Bill, Bob       30       35       25       45       40