I have two columns: A and B, with values from 1 to 7 each. I need to get the maximum value between both columns EXCLUDING the value 7, how can I do that? OR In the case that A has 7 I keep the value of B, this would serve me more, for instance:
A <- c(1,1,1,3,2,4,2,5,6,7)
B <- c(7,3,6,7,4,1,6,7,3,4)
df <- data.frame(A, B)
Expected results: 1,3,6,3,4,4,6,5,6,4
One option could be:
with(df, pmax(A * (A != 7), B * (B != 7)))
[1] 1 3 6 3 4 4 6 5 6 4
To deal with missing values:
with(df, pmax(A * (A != 7), B * (B != 7), na.rm = TRUE))
Considering also negative values:
with(df, pmax(A * (A != 7)^NA, B * (B != 7)^NA, na.rm = TRUE))
Updated special thanks to dear @tmfmnk.
Maybe there is a better way of handling this problem but it instantly popped up in my mind to replace every 7 with the lowest possible value like 0 so that it does not affect the comparison. It also works with NA values.
library(dplyr)
library(purrr)
A <- c(1,1,1,3,2,4,2,5,6,7)
B <- c(7,3,6,7,4,1,6,7,3,4)
df <- tibble(A, B)
df %>%
mutate(across(everything(), ~ replace(., . == 7, 0))) %>%
mutate(ResultColumn = pmap_dbl(., ~ max(c(...), na.rm = TRUE)))
# A tibble: 10 x 3
A B ResultColumn
<dbl> <dbl> <dbl>
1 1 0 1
2 1 3 3
3 1 6 6
4 3 0 3
5 2 4 4
6 4 1 4
7 2 6 6
8 5 0 5
9 6 3 6
10 0 4 4
Here is the tidyverse example:
library(tidyverse)
A <- c(1,1,1,3,2,4,2,5,6,7)
B <- c(7,3,6,7,4,1,6,7,3,4)
df <- data.frame(A,B)
df %>%
filter(A != 7, B != 7) %>%
transform(ResultColumn = pmax(A,B))
Output:
Another base R option using pmax
> do.call(pmax, c(replace(df, df == 7, NA), na.rm = TRUE))
[1] 1 3 6 3 4 4 6 5 6 4
or
> do.call(pmax, replace(df, df == 7, -Inf))
[1] 1 3 6 3 4 4 6 5 6 4