Summing across specific rows with specific conditions

user_14385099 0
Viewed 29

I have a dataframe:

dat <- data.frame(X1 = c(0, NA, NA),
                  X2 = c(1, NA, NA),
                  X3 = c(1, NA, NA),
                  X4 = c(1, 5, 8),
                  X5 = c(2, 6, 4),
                  X6 = c(3, 3, 3))

I want to sum across the rows X4, X5 and X6 to get a total score. However, any number that is above 6 in those columns will have to be counted as a 7 before being added with the other columns to get a total score.

What is a concise way to go about doing this?

Thank you!

data-wrangling dplyr r
3 Answers

A possible solution:

dat$score <- rowSums(pmin(dat[4:6], 7))
dat

#>   X1 X2 X3 X4 X5 X6 score
#> 1  0  1  1  1  2  3     6
#> 2 NA NA NA  5  6  3    14
#> 3 NA NA NA  8  4  3    14
user_115644870

We subset the data by selecting the columns 'X4' to 'X6', replace any value greater than 6 to 7, and do the rowSums with na.rm = TRUE (in case of any NAs)

df$total_score <- rowSums(replace(dat[paste0("X", 4:6)],
     dat[paste0("X", 4:6)] > 6, 7), na.rm = TRUE)

Or slighly more concise would be using pmin/rowSums

df$total_score <- rowSums(pmin(dat[paste0("X", 4:6)], 7), na.rm = TRUE)
user_37322710

Another option is to use rowwise() with c_across:

library(dplyr)

dat %>% 
  rowwise() %>% 
  mutate(Score = sum(c_across(4:6), na.rm = TRUE))

     X1    X2    X3    X4    X5    X6 Score
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1     0     1     1     1     2     3     6
2    NA    NA    NA     5     6     3    14
3    NA    NA    NA     8     4     3    15
user_133216470
Related