在Base R中筛选多列匹配指定向量的DataFrame行

Problem Overview #

You need to filter rows in a DataFrame where any of the specified columns (X1 to X10) contains a value from your target vector. This needs to work with:

• Numeric or alphanumeric target values (like X1425, 52546, HPO1567)
• Presence of NA values in columns
• Retaining full rows even if multiple matches exist in the same row

Optimal Base R Implementation #

Here's a step-by-step solution that's efficient, handles edge cases, and uses only Base R tools:

1. Setup Your Data and Targets

First, let's work with your sample DataFrame and target vector (we'll also cover real-world alphanumeric targets later):

# Your provided DataFrame
df <- structure(list(id = c("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50"), X1 = c(8L, 18L, 6L, 10L, 2L, 12L, 20L, 19L, 17L, 6L, 20L, 3L, 14L, 20L, 11L, 17L, 19L, 3L, 12L, 17L, 20L, 14L, 11L, 1L, 9L, 1L, 2L, 4L, 7L, 18L, 7L, 12L, 18L, 6L, 6L, 6L, 20L, 11L, 17L, 15L, 6L, 2L, 17L, 14L, 15L, 10L, 4L, 4L, 7L, 3L), X2 = c(1L, 3L, 10L, 14L, 12L, 9L, 0L, 1L, 7L, 14L, 3L, 10L, 5L, 15L, 1L, 14L, 17L, 9L, 16L, 6L, 10L, 6L, 1L, 11L, 8L, 1L, 0L, 3L, 14L, 4L, 16L, 5L, 15L, 11L, 10L, 0L, 16L, 16L, 15L, 20L, 5L, 1L, 9L, 2L, 16L, 12L, 4L, 2L, 15L, 11L), X3 = c(16L, 2L, 10L, 19L, 5L, 16L, 13L, 14L, 10L, 15L, 18L, 17L, 0L, 2L, 7L, 5L, 19L, 3L, 2L, 20L, 19L, 14L, 18L, 13L, 5L, 15L, 13L, 6L, 9L, 17L, 9L, 17L, 15L, 1L, 20L, 17L, 19L, 13L, 15L, 4L, 9L, 0L, 13L, 9L, 11L, 2L, 0L, 5L, 5L, 16L), X4 = c(14L, 16L, 6L, 2L, 2L, 10L, 13L, 5L, 9L, 16L, 15L, 3L, 11L, 8L, 2L, 17L, 1L, 1L, 5L, 18L, 0L, 14L, 18L, 19L, 6L, 17L, 15L, 11L, 19L, 13L, 2L, 12L, 8L, 4L, 17L, 14L, 9L, 18L, 10L, 19L, 14L, 14L, 15L, 15L, 7L, 16L, 2L, 19L, 12L, 13L), X5 = c(8L, 7L, 18L, 20L, 9L, 12L, 4L, 5L, 18L, 14L, 10L, 3L, 8L, 9L, 15L, 13L, 2L, 3L, 18L, 7L, 16L, 17L, 20L, 15L, 9L, 17L, 9L, 17L, 14L, 10L, 4L, 5L, 0L, 2L, 13L, 20L, 16L, 12L, 14L, 20L, 1L, 9L, 8L, 14L, 19L, 12L, 2L, 0L, 1L, 5L), X6 = c(10L, 2L, 11L, 19L, 2L, 11L, 7L, 12L, 16L, 17L, 2L, 9L, 20L, 0L, 19L, 1L, 15L, 15L, 6L, 8L, 1L, 15L, 11L, 17L, 16L, 8L, 16L, 20L, 15L, 9L, 7L, 15L, 12L, 14L, 20L, 4L, 12L, 6L, 2L, 5L, 13L, 17L, 2L, 2L, 2L, 17L, 0L, 19L, 19L, 14L), X7 = c(13L, 19L, 12L, 14L, 17L, 14L, 18L, 12L, 7L, 1L, 10L, 14L, 20L, 11L, 20L, 12L, 15L, 2L, 11L, 20L, 1L, 3L, 10L, 11L, 12L, 13L, 15L, 18L, 8L, 13L, 14L, 8L, 6L, 11L, 8L, 10L, 3L, 10L, 4L, 5L, 15L, 11L, 12L, 16L, 11L, 8L, 3L, 8L, 9L, 1L), X8 = c(7L, 17L, 7L, 17L, 17L, 6L, 18L, 11L, 14L, 17L, 1L, 4L, 18L, 9L, 15L, 20L, 12L, 8L, 5L, 20L, 6L, 15L, 8L, 3L, 12L, 1L, 14L, 12L, 6L, 0L, 8L, 13L, 20L, 0L, 20L, 20L, 13L, 9L, 0L, 17L, 1L, 2L, 15L, 10L, 2L, 1L, 20L, 11L, 15L, 11L), X9 = c(17L, 6L, 16L, 13L, 15L, 3L, 12L, 15L, 7L, 15L, 1L, 1L, 17L, 17L, 13L, 4L, 11L, 10L, 19L, 6L, 11L, 3L, 3L, 3L, 9L, 10L, 12L, 4L, 5L, 17L, 8L, 12L, 16L, 12L, 20L, 3L, 5L, 6L, 16L, 8L, 20L, 0L, 15L, 9L, 2L, 6L, 19L, 7L, 11L, 7L), X10 = c(15L, 11L, 4L, 1L, 10L, 18L, 16L, 2L, 1L, 0L, 9L, 19L, 1L, 11L, 0L, 0L, 14L, 15L, 8L, 12L, 12L, 20L, 13L, 13L, 3L, 13L, 8L, 4L, 19L, 3L, 0L, 15L, 18L, 15L, 19L, 13L, 15L, 18L, 8L, 9L, 17L, 2L, 1L, 18L, 5L, 19L, 10L, 16L, 5L, 12L)), class = "data.frame", row.names = c(NA, -50L ))

# Example target vector (numeric)
x <- 0:5

# Real-world target vector (alphanumeric)
# x <- c("X1425", "52546", "HPO1567")

# Define columns to check (X1 to X10)
target_cols <- grep("^X[1-9]$|^X10$", names(df), value = TRUE)

2. Generate Filter Logic

We'll create a logical vector indicating which rows to keep. This handles NA values by ignoring them (so NA doesn't count as a match):

# Efficient row-wise check (great for most datasets)
keep_rows <- apply(df[target_cols], 1, function(row) {
  any(row %in% x, na.rm = TRUE)
})

# Faster version for large datasets (uses vectorization instead of apply)
keep_rows <- rowSums(sapply(df[target_cols], function(col) col %in% x), na.rm = TRUE) > 0

• na.rm = TRUE ensures NA values in columns don't break the check or incorrectly exclude rows
• The vectorized version (rowSums + sapply) is significantly faster for big DataFrames since it avoids row-wise loops.

3. Filter the DataFrame

Use the logical vector to retain only matching rows:

filtered_df <- df[keep_rows, ]

Key Notes for Real-World Scenarios #

• Alphanumeric Targets: The code works exactly the same—just make sure your target vector and columns are the same data type (use as.character() on columns if needed to match alphanumeric targets).
• NA Handling: na.rm = TRUE ensures rows with NA in some columns aren't excluded unless they have a matching value in another column.
• Multiple Matches: The solution retains full rows even if multiple columns match the target vector, which aligns with your requirement.

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