R: sum largest n rows into a column

Question

I have a data frame that looks like this:

df <- tibble(trans_id = c(1:5),
             name = c('A', 'B', 'C', 'D', 'E'),
             Yr2020 = c(100, 200, 300, 400, 500),
             Yr2019 = c(10, 20, 30, 40, 50),
             Yr2018 = c(1, 2, 3, 4, 5),
             Yr2017 = c(1000, 2000, 3000, 4000, 5000),
             Yr2016 = c(20,30,40,50,60),
             Yr2015 = c(200,300,400,500,600),
             Yr2014 = c(2000,3000,4000,5000,6000))

# A tibble: 5 x 9
  trans_id name  Yr2020 Yr2019 Yr2018 Yr2017 Yr2016 Yr2015 Yr2014
     <int> <chr>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1        1 A        100     10      1   1000     20    200   2000
2        2 B        200     20      2   2000     30    300   3000
3        3 C        300     30      3   3000     40    400   4000
4        4 D        400     40      4   4000     50    500   5000
5        5 E        500     50      5   5000     60    600   6000

I want to sum the largest 4 numbers by row from Yr2019-Yr2014 plus Yr2020.

Expected Result:

# A tibble: 5 x 3
  trans_id name   Top5
     <int> <chr> <dbl>
1        1 A      3320
2        2 B      5530
3        3 C      7740
4        4 D      9950
5        5 E     12160

I usually do this in Excel using the SUM and LARGE function but I'm trying to speed up some of my manual tasks.

Answer 1

You can try this base R approach:

#Code
#Detect vars
index <- which(grepl(paste0(2014:2019,collapse = '|'),names(df)))
#Compute
df$Var <- apply(df[,index],1,function(x) sum(sort(x,decreasing =T)[1:4]))+df[['Yr2020']]

Output:

# A tibble: 5 x 10
  trans_id name  Yr2020 Yr2019 Yr2018 Yr2017 Yr2016 Yr2015 Yr2014   Var
     <int> <chr>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl> <dbl>
1        1 A        100     10      1   1000     20    200   2000  3320
2        2 B        200     20      2   2000     30    300   3000  5530
3        3 C        300     30      3   3000     40    400   4000  7740
4        4 D        400     40      4   4000     50    500   5000  9950
5        5 E        500     50      5   5000     60    600   6000 12160

Or the dplyr version using c_across():

library(dplyr)
#Code
df %>% rowwise(trans_id) %>% 
  mutate(Sum=sum(head(sort(c_across(Yr2019:Yr2014),decreasing = T),4))+Yr2020)

Output:

# A tibble: 5 x 10
# Rowwise:  trans_id
  trans_id name  Yr2020 Yr2019 Yr2018 Yr2017 Yr2016 Yr2015 Yr2014   Sum
     <int> <chr>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl> <dbl>
1        1 A        100     10      1   1000     20    200   2000  3320
2        2 B        200     20      2   2000     30    300   3000  5530
3        3 C        300     30      3   3000     40    400   4000  7740
4        4 D        400     40      4   4000     50    500   5000  9950
5        5 E        500     50      5   5000     60    600   6000 12160

Answer 2

dplyr:

df %>% rowwise() %>% 
  mutate(top5 = Yr2020 + sum(sort(across(Yr2018:Yr2014), decreasing = T)[1:4]))