Rowwise Mean in columns contain word except last column

November 30, 2021

I am trying to get the mean of columns that contain a especific word in name except last column with contain the same word in name, example

df <- data.frame( ABC_1 = runif(3),
            ABC_2 = runif(3),
            ABC_3 = runif(3),
            ABC_4 = runif(3) )

Here I get the value for the last column that contain word: ABC, in col: max

df2=df %>%  
rowwise() %>%
mutate_at(vars(last(contains('ABC'))), funs(max= max(., na.rm = TRUE))) 


      ABC_1 ABC_2 ABC_3 ABC_4   max
      <dbl> <dbl> <dbl> <dbl> <dbl>
    1 0.191 0.486 0.455 0.246 0.246
    2 0.523 0.728 0.812 0.517 0.517
    3 0.134 0.937 0.992 0.899 0.899

With the same logic, now I tried to get the mean of all column with name ABC, except last column:

df3=df %>%  
rowwise() %>%
mutate_at(vars(last(contains('ABC'))), funs(max= max(., na.rm = TRUE))) %>%
mutate_at(vars(-last(contains('ABC'))), funs(mean= mean(., na.rm = TRUE)))

But lamentably I dont get the result expected:

      ABC_1 ABC_2 ABC_3 ABC_4   max ABC_1_mean ABC_2_mean ABC_3_mean max_mean
      <dbl> <dbl> <dbl> <dbl> <dbl>      <dbl>      <dbl>      <dbl>    <dbl>
    1 0.191 0.486 0.455 0.246 0.246      0.191      0.486      0.455    0.246
    2 0.523 0.728 0.812 0.517 0.517      0.523      0.728      0.812    0.517
    3 0.134 0.937 0.992 0.899 0.899      0.134      0.937      0.992    0.899

>Solution :

One option could be:

df %>%
    mutate(ABC_mean = rowMeans(across(head(starts_with("ABC"), -1))))

      ABC_1     ABC_2     ABC_3     ABC_4  ABC_mean
1 0.5957359 0.7201537 0.1304605 0.1697986 0.4821167
2 0.6865635 0.9463447 0.8447037 0.4149000 0.8258706
3 0.2364415 0.8335135 0.6342009 0.4410836 0.5680520