CODEWITHSUNDEEP
rdplyrdata.table
Fernando Brito Lopes
Fernando Brito Lopes

Reputation: 101

Get top_n rows by group on multiple columns

I was wondering if there is a more elegant solution than my approach below. I have a data frame and I would like to get mean for each column based on top values from each group.

set.seed(123)
df <- data.frame(
  A = sample(c("A","B","C"), 20, replace=TRUE),
  B = rnorm(60, 5, 2),
  C = rnorm(60, 0, 2),
  D = rnorm(60, 10, 2))

library("dplyr")
top <- 5
top.B <- df %>% group_by(A) %>% top_n(n=top, wt=B) %>% summarize(top.A=mean(B))
top.C <- df %>% group_by(A) %>% top_n(n=-top, wt=C) %>% summarize(top.C=mean(C))
top.D <- df %>% group_by(A) %>% top_n(n=top, wt=D) %>% summarize(top.D=mean(D))
top5 <- merge(top.B, top.C, by="A")
top5 <- merge(top5, top.D, by="A")

I am able to accomplish it by merging the data frames. And the result looks like:

  A    top.A     top.C    top.D
1 A 7.663078 -1.986632 12.62946
2 B 6.926882 -2.186245 13.18132
3 C 7.548887 -2.255001 12.15677

I wonder if it is possible to do that without creating those new data frame. Note that the column C the average is from the bottom values, or the top using the decreasing order.

Thank you.

Upvotes: 1

Views: 2039

Answers (4)

akrun
akrun

Reputation: 886938

Here is one option with map

library(tidyverse)
map(names(df)[-1], ~ 
          df %>% 
             select(A, .x) %>%
             group_by(A) %>%
             top_n(n = top, wt = !! rlang::sym(.x)) %>% 
             summarise(!! str_c('top.', .x) := mean(!! rlang::sym(.x)))) %>%
     reduce(inner_join, by = 'A')
# A tibble: 3 x 4
#  A     top.B top.C top.D
#  <fct> <dbl> <dbl> <dbl>
#1 A      6.10  3.20  12.8
#2 B      7.94  2.17  12.3
#3 C      8.19  1.18  12.9

Or using frank from data.table with summarise_all (similar to an option in @tmfmnk's post)

library(data.table)
df %>%
    group_by(A) %>% 
    summarise_all(list( ~ mean(.[frank(-.) <= 5])))
# A tibble: 3 x 4
#  A         B     C     D
#  <fct> <dbl> <dbl> <dbl>
#1 A      6.10  3.20  12.8
#2 B      7.94  2.17  12.3
#3 C      8.19  1.18  12.9

Or using order

df %>% 
    group_by(A) %>%
    summarise_all(list(~ mean(.x[order(-.)][1:5])))
# A tibble: 3 x 4
#  A         B     C     D
#  <fct> <dbl> <dbl> <dbl>
#1 A      6.10  3.20  12.8
#2 B      7.94  2.17  12.3
#3 C      8.19  1.18  12.9

Upvotes: 1

s_baldur
s_baldur

Reputation: 33498

A data.table option:

To get average of top 5

get_mean_top5 <- function(x) -mean(sort(-x, partial = 1:5)[1:5])
df[, lapply(.SD, get_mean_top5), keyby = A, .SDcols = c("B", "D")]
#    A        B        D
# 1: A 6.097723 12.75887
# 2: B 7.942064 12.33379
# 3: C 8.190137 12.93201

Average if bottom 5:

get_mean_bot5 <- function(x) mean(sort(x, partial = 1:5)[1:5])
df[, lapply(.SD, get_mean_bot5), keyby = A, .SDcols = c("C")]

To get the full table in one step:

setDT(df, key = "A")
df[, lapply(.SD, get_mean_top5), keyby = A, .SDcols = c("B", "D")
   ][df[, lapply(.SD, get_mean_bot5), keyby = A, .SDcols = c("C")]]

Upvotes: 3

tmfmnk
tmfmnk

Reputation: 39858

One dplyr possibility could be:

df %>%
 group_by(A) %>%
 summarise_all(list(~ mean(.[dense_rank(desc(.)) <= 5])))

  A         B     C     D
  <fct> <dbl> <dbl> <dbl>
1 A      7.66  2.16  12.6
2 B      6.93  1.79  13.2
3 C      7.55  2.23  12.2

If you want the bottom 5 observations for column C:

df %>%
 group_by(A) %>%
 summarise(B = mean(B[dense_rank(desc(B)) <= 5]),
           C = mean(C[dense_rank(C) <= 5]),
           D = mean(D[dense_rank(desc(D)) <= 5]))

  A         B     C     D
  <fct> <dbl> <dbl> <dbl>
1 A      7.66 -1.99  12.6
2 B      6.93 -2.19  13.2
3 C      7.55 -2.26  12.2

Upvotes: 2

MrNetherlands
MrNetherlands

Reputation: 940

Somehow, I get different values than you but this approach should work

library(dplyr)
df %>% 
  gather(key, value, -A) %>%
  group_by(A, key) %>%
  top_n(5, value) %>%
  summarise(m = mean(value)) %>%
  ungroup() %>%
  spread(key, m)

# A tibble: 3 x 4
  A         B     C     D
  <fct> <dbl> <dbl> <dbl>
1 A      6.10  3.20  12.8
2 B      7.94  2.17  12.3
3 C      8.19  1.18  12.9

Here the data:

set.seed(123)
df <- data.frame(
  A = sample(c("A","B","C"), 20, replace=TRUE),
  B = rnorm(60, 5, 2),
  C = rnorm(60, 0, 2),
  D = rnorm(60, 10, 2))

Upvotes: 1

Related Questions