how to exclude single column from mutate max operation but retain all columns in dataframe

Question

I am trying to get both the sum across rows and the max value in a row. I obviously do not want the rowsum column to be included in the max values, nor do i want the max values included in the row sum. I need a final dataset that has both of these columns retained however.

Using dplyr I tried-

iris<- iris %>%
 mutate(readsum = rowSums(across(where(is.numeric)), na.rm=TRUE))

iris_max<- iris %>%
   rowwise()%>%
    select(-"readsum")%>%
  mutate(readmax = max(across(where(is.numeric)), na.rm=TRUE))

but this just removed readsum from the new df

I would like to get as output:

  Sepal.Length Sepal.Width Petal.Length Petal.Width Species readsum readmax
         <dbl>       <dbl>        <dbl>       <dbl> <fct>     <dbl>   <dbl>
1          5.1         3.5          1.4         0.2 setosa     10.2    5.1
2          4.9         3            1.4         0.2 setosa      9.5    4.9
3          4.7         3.2          1.3         0.2 setosa      9.4    4.7
4          4.6         3.1          1.5         0.2 setosa      9.4    4.6
5          5           3.6          1.4         0.2 setosa     10.2    5
6          5.4         3.9          1.7         0.4 setosa     11.4    5.4

i cannot share my actual data, but am using the included dataset ```iris``` — sscoresby, Dec 19 '22 at 05:57
Can you include a sample of how you want your output to look like — Yomi.blaze93, Dec 19 '22 at 06:00

Rui Barradas · Accepted Answer · 2022-12-19T07:32:07.093

Use c_across and wrap where around is.numeric.
A way to keep the new column readsum in the final result is to first create an index to the columns that already are numeric. Then create readsum.

suppressPackageStartupMessages(
  library(dplyr)
)

data(iris, package = "datasets")

i_num <- iris %>% 
  sapply(is.numeric) %>% 
  which()

iris <- iris %>%
  mutate(readsum = rowSums(across(where(is.numeric)), na.rm=TRUE))

head(iris)
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species readsum
#> 1          5.1         3.5          1.4         0.2  setosa    10.2
#> 2          4.9         3.0          1.4         0.2  setosa     9.5
#> 3          4.7         3.2          1.3         0.2  setosa     9.4
#> 4          4.6         3.1          1.5         0.2  setosa     9.4
#> 5          5.0         3.6          1.4         0.2  setosa    10.2
#> 6          5.4         3.9          1.7         0.4  setosa    11.4

iris %>%
  rowwise() %>%
  mutate(readmax = max(c_across(all_of(i_num))))
#> # A tibble: 150 × 7
#> # Rowwise: 
#>    Sepal.Length Sepal.Width Petal.Length Petal.Width Species readsum readmax
#>           <dbl>       <dbl>        <dbl>       <dbl> <fct>     <dbl>   <dbl>
#>  1          5.1         3.5          1.4         0.2 setosa     10.2     5.1
#>  2          4.9         3            1.4         0.2 setosa      9.5     4.9
#>  3          4.7         3.2          1.3         0.2 setosa      9.4     4.7
#>  4          4.6         3.1          1.5         0.2 setosa      9.4     4.6
#>  5          5           3.6          1.4         0.2 setosa     10.2     5  
#>  6          5.4         3.9          1.7         0.4 setosa     11.4     5.4
#>  7          4.6         3.4          1.4         0.3 setosa      9.7     4.6
#>  8          5           3.4          1.5         0.2 setosa     10.1     5  
#>  9          4.4         2.9          1.4         0.2 setosa      8.9     4.4
#> 10          4.9         3.1          1.5         0.1 setosa      9.6     4.9
#> # … with 140 more rows

^{Created on 2022-12-19 with reprex v2.0.2}

no no, i DO want the column readsum in the df, I just don't want it to be included in the readmax calculation — sscoresby, Dec 19 '22 at 06:15
then it includes the column ```readsum``` in deciding which is the maximum value present, which is the whole point of excluding that column — sscoresby, Dec 19 '22 at 06:55

score 1 · Answer 2 · answered Dec 19 '22 at 15:06

Here is another approach using reduce from purrr and pmax, using select to only include columns from the original data.frame:

library(tidyverse)

iris %>%
  mutate(readsum = rowSums(across(where(is.numeric)), na.rm = T),
         readmax = select(., where(is.numeric)) %>% reduce(pmax, na.rm = T))

Output

    Sepal.Length Sepal.Width Petal.Length Petal.Width    Species readsum readmax
1            5.1         3.5          1.4         0.2     setosa    10.2     5.1
2            4.9         3.0          1.4         0.2     setosa     9.5     4.9
3            4.7         3.2          1.3         0.2     setosa     9.4     4.7
4            4.6         3.1          1.5         0.2     setosa     9.4     4.6
5            5.0         3.6          1.4         0.2     setosa    10.2     5.0
6            5.4         3.9          1.7         0.4     setosa    11.4     5.4
7            4.6         3.4          1.4         0.3     setosa     9.7     4.6
8            5.0         3.4          1.5         0.2     setosa    10.1     5.0
...

how to exclude single column from mutate max operation but retain all columns in dataframe

2 Answers2