如何用dplyr将check列中大于任意before列的值替换为NA？

Problem Recap #

You need to replace values in check.1 to check.3 with NA if they exceed any value in before.1 to before.3 in the same row. Your initial attempt with mutate(across(check.1:check.3,~na_if(.x >before.1:before.3))) fails because the comparison isn't handled row-wise.

Sample Data #

Let’s use explicit initial and target datasets for clarity:

library(dplyr)

# Initial input data
df <- tibble(
  before.1 = c(5, 3, 7),
  before.2 = c(4, 6, 2),
  before.3 = c(6, 2, 5),
  check.1 = c(7, 4, 6),
  check.2 = c(3, 7, 8),
  check.3 = c(5, 1, 9)
)

# Expected target result
target_df <- tibble(
  before.1 = c(5, 3, 7),
  before.2 = c(4, 6, 2),
  before.3 = c(6, 2, 5),
  check.1 = c(NA, NA, 6),
  check.2 = c(3, NA, NA),
  check.3 = c(5, 1, NA)
)

Correct Implementation #

A simple, efficient approach leverages row-wise maximum values of the before columns: if a check value is greater than any before value, it will be greater than the row's maximum before value. Here’s how to implement this with dplyr:

result_df <- df %>%
  # Calculate row-wise maximum of before columns (ignore NAs if present)
  rowwise() %>%
  mutate(max_before = max(c_across(before.1:before.3), na.rm = TRUE)) %>%
  ungroup() %>%
  # Replace check values exceeding max_before with NA
  mutate(across(check.1:check.3, ~if_else(.x > max_before, NA_real_, .x))) %>%
  # Remove temporary max_before column
  select(-max_before)

# Confirm matches target
all.equal(result_df, target_df)
#> [1] TRUE

Why Your Initial Code Failed #

before.1:before.3 creates a sequence of column names, but when used in na_if(.x > before.1:before.3), it attempts to compare the entire check column vector to all three before columns at once—not a row-by-row comparison. The rowwise() + c_across() combination fixes this by processing each row individually.

Alternative for Large Datasets #

For big datasets, rowwise() can be slow. Use apply to compute row-wise max instead:

result_df <- df %>%
  mutate(max_before = apply(select(., before.1:before.3), 1, max, na.rm = TRUE)) %>%
  mutate(across(check.1:check.3, ~if_else(.x > max_before, NA_real_, .x))) %>%
  select(-max_before)

内容的提问来源于stack exchange，提问作者O Stanley