R中迭代验证报价的低效循环优化及逻辑修正技术问询

Hey there! Let's work through your R problem—both the logical bug and the performance slowdown you're seeing.

First, The Core Logical Issue #

Your current code has a critical flaw: when you delete a row, you increment i anyway, which means you'll skip the next row that just shifted into the deleted row's position. Worse, even when you don't delete a row, you're using i-1 as the "previous valid row"—but if any rows before i were deleted, i-1 in the modified dataframe isn't actually the last valid row. For example, if row 3 is invalid and deleted, your code would process row 2, delete row 3, then increment i to 4. But after deletion, the original row 4 becomes the new row 3, and i=4 exceeds the new row count, so you never check that row at all. That's why your logic isn't working as expected.

Also, every time you run df <- df[-i,], you're creating an entirely new dataframe. R uses immutable objects, so this copy operation gets extremely slow as your dataset grows.

Fixing the Logic & Boosting Performance #

Instead of modifying the dataframe as you go, we'll track valid rows with a logical vector, and keep a reference to the last valid row's index. This avoids copying the dataframe over and over, and ensures we always use the actual last valid row for calculations.

Here's the revised code:

# Initialize a vector to track valid rows
valid <- logical(nrow(df))
valid[1] <- TRUE  # Assume first row is valid, as you did
last_valid_idx <- 1  # Keep track of the last valid row's position

# Loop through each row starting from the second
for (i in 2:nrow(df)) {
  # Calculate metrics using the LAST VALID row, not just i-1
  df$dBidQuoteABS_V[i] <- abs(as.numeric(df$BidQuote[i] - df$BidQuote[last_valid_idx]))
  df$BConditionI[i] <- ifelse(df$dBidQuoteABS_V[i] < 0.25, 1, 0)
  
  # Calculate time difference from last valid row
  df$dT[i] <- as.numeric(df$DateTime[i] - df$DateTime[last_valid_idx]) / (24 * 60 * 60)
  df$BConditionII[i] <- ifelse(df$dBidQuoteABS_V[i] < 2 * df$spread[last_valid_idx] + 0.18 * df$dT[i], 1, 0)
  
  # Calculate the other conditions (these only depend on current row)
  df$SConditionI[i] <- ifelse(0.0001 < df$spread[i] & df$spread[i] < 0.025, 1, 0)
  df$SConditionII[i] <- ifelse(df$dBidQuoteABS_V[i] < 1.3 + 45 * df$dT[i], 1, 0)
  df$SConditionIII[i] <- ifelse(df$dBidQuoteABS_V[i] < 4, 1, 0)
  
  # Check if all conditions are met
  is_current_valid <- all(
    df$BConditionI[i] == 1,
    df$BConditionII[i] == 1,
    df$SConditionI[i] == 1,
    df$SConditionII[i] == 1,
    df$SConditionIII[i] == 1
  )
  
  valid[i] <- is_current_valid
  # Update last valid index only if current row is valid
  if (is_current_valid) {
    last_valid_idx <- i
  }
}

# Finally, filter the dataframe to keep only valid rows
df_filtered <- df[valid, ]
# Reset row names for cleanliness
rownames(df_filtered) <- seq(nrow(df_filtered))

Why This Works Better #

1. Correct Logic: We use last_valid_idx to always reference the most recent valid row, regardless of how many rows were skipped/deleted. In your example where row 3 is invalid, last_valid_idx stays at 2 when processing row 4, so calculations for row 4 use row 2's data—exactly what you need.
2. Faster Performance: Instead of copying the entire dataframe every time we delete a row, we just update a small logical vector. This reduces memory overhead and speeds up the loop drastically, especially for large datasets.
3. No Skipped Rows: We loop through every original row exactly once, so no rows get missed after deletions.

Quick Additional Tips #

• If your dataframe is very large, consider pre-allocating the columns dBidQuoteABS_V, BConditionI, etc., before the loop (e.g., df$dBidQuoteABS_V <- numeric(nrow(df))). This avoids R having to resize the columns dynamically during the loop, which adds extra overhead.
• The all() function is cleaner than chaining multiple |/& conditions, and it's just as fast.

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