R语言data.table中，寻找满足后续值条件的首个a列目标值

Let's break down how to solve this problem step by step. Your initial approach only checks for the first row where b < 1, but we need to add two critical constraints: no subsequent rows can have b >= 1, and all subsequent b values must be ≤ the current row's b.

Approach #

We need to validate three conditions for each row:

1. The row's b value is less than 1.
2. All rows after it have b < 1 (no future values exceed 1).
3. All rows after it have b values ≤ the current row's b (future values don't rise above the current value).

To efficiently compute these in data.table, we'll use vectorized operations (no slow loops!) to create helper flags and metrics:

• Flag for future values ≥1: Reverse the data, compute cumulative max of b >=1 (converted to integers), then reverse back to mark rows where any subsequent value exceeds 1.
• Max future b value: Reverse the data, compute cumulative max of b, reverse back, then shift left to get the maximum b value for all rows after the current one.

Code Implementation #

library(data.table)

# Your original data
stack <- data.table(
  a = as.numeric(seq(1,10,1)), 
  b = as.numeric(c(1.54, 1.17, 0.75, 1.65, 0.61, 0.31, 0.90, 0.07, 0.04, 0.01)), 
  ID = as.numeric(rep(seq(1,2,1),5))
)

# Calculate helper columns
stack[, `:=`(
  # True if ALL subsequent rows have b < 1
  all_after_b_lt1 = rev(cummax(rev(as.integer(b >= 1)))) == 0,
  # Maximum b value in all rows after current
  max_b_after = shift(rev(cummax(rev(b))), type = "lead")
)]

# Find the first row that meets all conditions
target_a <- stack[
  b < 1 & all_after_b_lt1 & (is.na(max_b_after) | max_b_after <= b),
  a[1]
]

print(target_a)
# Output: 7

Explanation #

1. all_after_b_lt1: This flag is TRUE only if no row after the current one has b >=1. We use rev(cummax(rev(...))) to propagate the "has future value ≥1" condition backward through the dataset.
2. max_b_after: This gives the highest b value in all rows after the current one. Shifting the reversed cumulative max left ensures we exclude the current row's b value.
3. Final Filter: We select rows where b <1, no future values exceed 1, and all future values are ≤ current b. The [1] picks the first matching row's a value.

Verification #

For your example, this code correctly identifies row 7 (a=7) as the first row meeting all criteria:

• b=0.90 <1
• All subsequent rows (8-10) have b <1
• All subsequent b values (0.07, 0.04, 0.01) are ≤ 0.90

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