百万行多列大数据集含'en'子串的(row,column)定位方案咨询

Got it, let's tackle this problem efficiently—especially since you're working with a massive dataset (1M rows + 100+ columns). Below are optimized, vectorized approaches to get the exact (row, column) positions of all elements containing the substring en:

Method 1: Base R Vectorized Approach (Fastest for Large Data) #

This method leverages base R's vectorized operations to minimize processing time and memory overhead, which is critical for big datasets:

# Step 1: Create a logical matrix where TRUE marks elements containing 'en'
match_matrix <- sapply(df, function(col) grepl(pattern = "en", x = col, fixed = TRUE))

# Step 2: Extract all (row, column) indices of matches
match_positions <- which(match_matrix, arr.ind = TRUE)

Key Notes: #

• fixed = TRUE speeds up the search because we're targeting a fixed substring (not a regex pattern). Remove this if you need regex-based matching instead.
• match_positions will be a 2-column matrix: the first column is row numbers, the second is column numbers from your original dataframe.

Method 2: Tidyverse Approach (For Easier Post-Processing) #

If you prefer working with tidy data and want to analyze matched values alongside their positions, use this approach:

library(tidyverse)

match_positions <- df %>%
  # Add a row ID to track original row numbers
  mutate(row_id = row_number()) %>%
  # Convert wide dataframe to long format
  pivot_longer(-row_id, names_to = "column_name", values_to = "value") %>%
  # Filter rows where the value contains 'en'
  filter(str_detect(value, fixed("en"))) %>%
  # Convert column names back to original column indices (keep column_name if you prefer names over numbers)
  transmute(row = row_id, column = match(column_name, colnames(df)))

Why Your Previous Methods Failed #

Let's break down why the approaches you tried didn't meet your needs:

• which(df == "en", arr.ind = T): This does exact string matching, not substring matching. It only finds cells where the entire value is exactly "en", not cells that contain "en" as part of a longer string.
• grep("en", df[1:ncol(df)]): This only returns indices of columns with at least one match—it doesn't give you the specific row numbers for each individual match, so you lose critical positional data.

Example Output #

If you use the base R method, match_positions will look like this:

row col
[1,]   2   1
[2,]   5   1
[3,]   4   2
[4,]   5   3

To format it exactly like your sample output, run this extra code:

for (i in seq(nrow(match_positions))) {
  cat(sprintf("第%d个匹配位置为(%d,%d)\n", i, match_positions[i,1], match_positions[i,2]))
}

Which will print:

第1个匹配位置为(2,1)
第2个为(5,1)
第3个为(4,2)
第4个为(5,3)

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