R function to extract top n scores from a dataframe and find their average using ```apply` or dplyr-CodePudding

The dataframe looks like this

df = data.frame(name = c("A","B","C"),
               exam1 = c(2,6,4),
               exam2 = c(3,5,6),
               exam3 = c(5,3,3),
               exam4 = c(1,NA,5))

I want to extract the top 3 exam scores for each 'name' and find their average using apply() or dplyr rowwise() functions.

CodePudding user response：

With apply, use MARGIN = 1, to loop over the rows on the numeric columns, sort, get the head/tail depending on decreasing = TRUE/FALSE and return with the mean in base R

apply(df[-1], 1, FUN = function(x) mean(head(sort(x, decreasing = TRUE), 3)))
[1] 3.333333 4.666667 5.000000

Or with dplyr/rowwise

library(dplyr)
df %>%
  rowwise %>%
  mutate(Mean = mean(head(sort(c_across(where(is.numeric)), 
       decreasing = TRUE), 3))) %>% 
  ungroup
# A tibble: 3 × 6
  name  exam1 exam2 exam3 exam4  Mean
  <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 A         2     3     5     1  3.33
2 B         6     5     3    NA  4.67
3 C         4     6     3     5  5

CodePudding user response：

Here is an alternative approach with pivoting and using top_n: This will give back only the top 3:

library(dplyr)
library(tidyr)
df %>% 
  pivot_longer(
    -name,
    names_to = "exam",
    values_to = "value"
  ) %>% 
  group_by(name) %>% 
  top_n(3, value) %>% 
  mutate(mean = mean(value)) %>% 
  pivot_wider(
    names_from = exam, 
    values_from = value
  )

  name   mean exam1 exam2 exam3 exam4
  <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 A      3.33     2     3     5    NA
2 B      4.67     6     5     3    NA
3 C      5        4     6    NA     5

OR:

library(tidyr)
df %>% 
  pivot_longer(
    -name,
    names_to = "exam",
    values_to = "value"
  ) %>% 
  group_by(name) %>% 
  top_n(3, value) %>% 
  summarise(mean = mean(value))

 name   mean
  <chr> <dbl>
1 A      3.33
2 B      4.67
3 C      5

CodePudding user response：

Using purrr::pmap_dfr:

library(tidyverse)

df = data.frame(name = c("A","B","C"),
                exam1 = c(2,6,4),
                exam2 = c(3,5,6),
                exam3 = c(5,3,3),
                exam4 = c(1,NA,5))

df %>% 
  pmap_dfr(~ list(means = mean(sort(c(..2,..3,..4,..5), decreasing=T)[1:3]))) %>%
  bind_cols(df,.)

#>   name exam1 exam2 exam3 exam4    means
#> 1    A     2     3     5     1 3.333333
#> 2    B     6     5     3    NA 4.666667
#> 3    C     4     6     3     5 5.000000

Another possible solution, based on tidyr::pivot_longer and without using rowwise:

library(tidyverse)

df = data.frame(name = c("A","B","C"),
                exam1 = c(2,6,4),
                exam2 = c(3,5,6),
                exam3 = c(5,3,3),
                exam4 = c(1,NA,5))

df %>% 
  pivot_longer(cols = 2:5, names_to = "names") %>% 
  group_by(name) %>% 
  slice_max(value, n=3) %>% 
  summarise(mean = mean(value)) %>% 
  inner_join(df)

#> Joining, by = "name"
#> # A tibble: 3 × 6
#>   name   mean exam1 exam2 exam3 exam4
#>   <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 A      3.33     2     3     5     1
#> 2 B      4.67     6     5     3    NA
#> 3 C      5        4     6     3     5

CodePudding user response：

I went back to the question and tried using basic dplyr manipulation of 'df' which also works, much like some of the really helpful solutions in earlier posts.

df_long <- df %>% 
  pivot_longer(cols = -name,
               names_to = "exam",
               values_to = "score")
df_long %>%
group_by(name) %>% 
  arrange(desc(score)) %>% 
  slice(1:3) %>% 
  summarise(mean_score = mean(score))

@Paul Smith nice idea to add inner_join(df)