如何根據R中組的最大值聚合資料框

我有很多看起來像這樣的組的大資料。我想在每組中使用計數最多的水果作為中心水??果，并根據它聚合其他水果！

library(tidyverse)

df <- tibble(col1 = c("apple","apple","pple", "banana", "banana","bananna"),
             col2 = c("pple","app","app", "bananna", "banan", "banan"), 
             counts_col1 = c(100,100,2,200,200,2),
             counts_col2 = c(2,50,50,2,20,20),
             id=c(1,1,1,2,2,2))

df
#> # A tibble: 6 × 5
#>   col1    col2    counts_col1 counts_col2    id
#>   <chr>   <chr>         <dbl>       <dbl> <dbl>
#> 1 apple   pple            100           2     1
#> 2 apple   app             100          50     1
#> 3 pple    app               2          50     1
#> 4 banana  bananna         200           2     2
#> 5 banana  banan           200          20     2
#> 6 bananna banan             2          20     2

^{由reprex 包于 2022-03-16 創建(v2.0.1)}

我希望我的資料框看起來像這樣

id  central_fruit   fruits                 counts     sum_counts
 1     apple        apple,pple,app         100,50,2        152
 2    banana        banana,bananna,banan   200,20,2        222

輸出的格式不必是這樣的。這只是一個例子。它可以是字串列或只是字符。任何幫助或指導表示贊賞

uj5u.com熱心網友回復：

我們可以通過首先重塑為pivot_longer“長”格式（水果，并與計數一起計數add_countsummarisemaxpastetoStringuniqueuniquesumunique

library(dplyr)
library(stringr)
library(tidyr)
df %>%
   rename_with(~ str_c("fruit_", .x), starts_with('col')) %>% 
   pivot_longer(cols = -id, names_to = c(".value", "grp"), 
     names_pattern = "(.*)_(col\\d )") %>% 
   group_by(id, grp) %>%
   add_count(fruit) %>%
   group_by(id) %>% 
   summarise(central_fruit = fruit[which.max(n)], 
      fruits = toString(unique(fruit)), 
      sum_counts = sum(unique(counts)),
      counts = toString(sort(unique(counts), decreasing = TRUE)),
        .groups = 'drop' ) %>%
     relocate(counts, .before = 'sum_counts')

-輸出

# A tibble: 2 × 5
     id central_fruit fruits                 counts     sum_counts
  <dbl> <chr>         <chr>                  <chr>           <dbl>
1     1 apple         apple, pple, app       100, 50, 2        152
2     2 banana        banana, bananna, banan 200, 20, 2        222

注意：將“counts”的值包裝在 alist而不是pasteing 中可能會更好。即，而不是counts = toString(sort(unique(counts), decreasing = TRUE))，它將是 counts = list(sort(unique(counts), decreasing = TRUE))

uj5u.com熱心網友回復：

使用data.table，您可以執行以下操作：

代表

• 代碼

library(tidyverse) # to read your tibble
library(data.table)

setDT(df)[, .(central_fruit = col1[which.max(counts_col1)],
              fruits = .(unique(c(col1, col2))),
              counts = .(sort(unique(c(counts_col1, counts_col2)), decreasing = TRUE)),
              sum_counts = unlist(lapply(.(unique(c(counts_col1, counts_col2))), sum))), 
          by = id]

• 輸出

#>       id central_fruit               fruits      counts sum_counts
#>    <num>        <char>               <list>      <list>      <num>
#> 1:     1         apple       apple,pple,app 100, 50,  2        152
#> 2:     2        banana banana,bananna,banan 200, 20,  2        222

^{由reprex 包于 2022-03-16 創建(v2.0.1)}

標籤：r dplyr 数据表 tidyverse 蒂迪尔

上一篇：如何計算Rdplyr中不同觀察值的總和

下一篇：使用系列替換交換列時如何用mutate替換mutate_？