在R中通过指定列索引提取行值的高效实现方案求助

Problem Context #

I have two data frames:

• DF_A holds the raw values I need to pull from:

PARTY_ID O1 O2 O3 O4
P1 0 0 1 0
P2 2 1 0 1
P3 0 0 0 0
P4 2 1 1 1
P5 1 0 0 1

• DF_B defines which columns (positions relative to the O* columns in DF_A) to extract for each PARTY_ID:

PARTY_ID POS_1 POS_2
P1 1 2
P2 2 1
P3 3 1
P4 2 1
P5 1 4

My goal is to create a result data frame that pulls the corresponding values from DF_A using the positions in DF_B, like this:

PARTY_ID V1 V2
P1 0 0
P2 1 2
P3 0 0
P4 1 2
P5 1 1

I tried using which() without success, and since my actual dataset has over 100,000 rows, I need the most efficient implementation possible.

Solution #

Great question—when dealing with large datasets, vectorized operations are your best bet because they avoid the overhead of slow per-row loops. Here are two high-performance approaches, ordered by speed:

1. Base R Matrix Indexing (Fastest Option) #

This method leverages R's optimized matrix indexing, which is lightning-fast even for 100k+ rows:

# Convert the value columns of DF_A to a matrix (exclude PARTY_ID)
value_matrix <- as.matrix(DF_A[, -1])

# Create index pairs (row, column) for each value we need to extract
# Rows match the row numbers of DF_B, columns are the POS values from DF_B
v1_indices <- cbind(seq(nrow(DF_B)), DF_B$POS_1)
v2_indices <- cbind(seq(nrow(DF_B)), DF_B$POS_2)

# Build the final result data frame
result_df <- data.frame(
  PARTY_ID = DF_A$PARTY_ID,
  V1 = value_matrix[v1_indices],
  V2 = value_matrix[v2_indices]
)

2. Tidyverse with dplyr (Readable, Still Performant) #

If you prefer tidyverse syntax, this approach is more readable while still efficient enough for 100k rows:

library(dplyr)

result_df <- DF_A %>%
  # Combine with position data (drop duplicate PARTY_ID column)
  bind_cols(DF_B %>% select(-PARTY_ID)) %>%
  # Temporarily process rows individually
  rowwise() %>%
  # Extract values using the position indices
  mutate(
    V1 = c_across(O1:O4)[POS_1],
    V2 = c_across(O1:O4)[POS_2]
  ) %>%
  # Revert to fast vectorized operations
  ungroup() %>%
  # Keep only the columns we need in the output
  select(PARTY_ID, V1, V2)

Why These Work #

• Matrix Indexing: This is a low-level R operation that directly accesses matrix elements via row-column pairs. It has minimal overhead and is optimized for speed—you’ll get results in milliseconds even for 100k rows.
• Tidyverse Approach: c_across() collects all O* columns into a vector per row, then we index into that vector using positions from DF_B. ungroup() is critical here to switch back to fast vectorized processing after the row-wise step.

Performance Tip #

For 100k rows, the base R matrix method will be significantly faster (roughly 10-20x quicker than the tidyverse approach). If raw speed is your top priority, stick with the base R solution.

内容的提问来源于stack exchange，提问作者Valeria Lobos Ossandón