R语言data.table按条件替换分组行值的实现问询

需求说明 #

• 现有按id和type分组的data.table，需满足：
  • 当某行samplesize=0且period≠1时，将该行calculation替换为：同一(id,type)组内period=i-1的calculation值 × 同type、同period=i的相似组（优先id-1，若无则id+1）的mov值
• 实际数据包含277个分组，每组53个period，需避免低效循环，且相似组(id±1)至少存在一个

示例数据 #

原始数据 #

id samplesize type calculation        mov period
 1: 10603         15    1   1.1884602 -1.0236411      1
 2: 10603        105    1  -1.0809550 -1.1311796      2
 3: 10603        111    1   0.2358396 -0.5401774      3
 4: 10603        115    1   0.7322120  0.1195699      4
 5: 10603        113    1  -0.9727271 -0.4505766      5
 6: 10603        113    1   0.3711188  0.8088049      6
 7: 10604          0    1  -0.3795332 -0.2963887      1
 8: 10604          0    1   0.2203382  0.6357711      2
 9: 10604         50    1  -0.5731365 -0.6450074      3
10: 10604         54    1   0.3233726  0.3395729      4
11: 10604         53    1   0.2111071 -1.2167302      5
12: 10604         52    1   0.6702184  0.9840893      6

期望结果 #

id samplesize type calculation        mov period
 1: 10603         15    1   1.1884602 -1.0236411      1
 2: 10603        105    1  -1.0809550 -1.1311796      2
 3: 10603        111    1   0.2358396 -0.5401774      3
 4: 10603        115    1   0.7322120  0.1195699      4
 5: 10603        113    1  -0.9727271 -0.4505766      5
 6: 10603        113    1   0.3711188  0.8088049      6
 7: 10604          0    1  -0.3795332 -0.2963887      1
 8: 10604          0    1   0.4293202  0.6357711      2
 9: 10604         50    1  -0.5731365 -0.6450074      3
10: 10604         54    1   0.3233726  0.3395729      4
11: 10604         53    1   0.2111071 -1.2167302      5
12: 10604         52    1   0.6702184  0.9840893      6

尝试代码及问题 #

尝试用ifelse实现，但目标行返回NA：

test <- test[period != 1, calculation := ifelse(samplesize == 0,
                                                calculation[(period == period - 1)] * mov[id == id-1], 
                                                calculation), by = "type"]

执行后结果（第8行calculation为NA）：

id samplesize type calculation        mov period
 1: 10603         15    1   1.1884602 -1.0236411      1
 2: 10603        105    1  -1.0809550 -1.1311796      2
 3: 10603        111    1   0.2358396 -0.5401774      3
 4: 10603        115    1   0.7322120  0.1195699      4
 5: 10603        113    1  -0.9727271 -0.4505766      5
 6: 10603        113    1   0.3711188  0.8088049      6
 7: 10604          0    1  -0.3795332 -0.2963887      1
 8: 10604          0    1          NA  0.6357711      2
 9: 10604         50    1  -0.5731365 -0.6450074      3
10: 10604         54    1   0.3233726  0.3395729      4
11: 10604         53    1   0.2111071 -1.2167302      5
12: 10604         52    1   0.6702184  0.9840893      6

数据dput #

structure(list(id = c(10603L, 10603L, 10603L, 10603L, 10603L, 
10603L, 10604L, 10604L, 10604L, 10604L, 10604L, 10604L), samplesize = c(15L, 
105L, 111L, 115L, 113L, 113L, 0L, 0L, 50L, 54L, 53L, 52L), type = c("1", 
"1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1"), calculation = c(1.18846016375132, 
-1.08095498270142, 0.235839633282068, 0.732211968154904, -0.972727101272223, 
0.371118753750896, -0.379533157458354, 0.220338197196561, -0.573136536559793, 
0.323372576946197, 0.21110710320273, 0.670218361977238), mov = c(-1.02364112036935, 
-1.13117955732701, -0.540177404426532, 0.119569869902065, -0.450576581730733, 
0.808804864876457, -0.296388732980522, 0.635771118121571, -0.645007399711979, 
0.339572874056865, -1.21673020152297, 0.984089256133723), period = c(1L, 
2L, 3L, 4L, 5L, 6L, 1L, 2L, 3L, 4L, 5L, 6L)), class = c("data.table", 
"data.frame"), row.names = c(NA, -12L), .internal.selfref = <pointer: 0x0000023ad4ad1ef0>)

解决方案 #

要实现需求，需先获取两个关键值：

1. 同一(id,type)组内上一period的calculation值：可通过data.table的shift函数按(id,type)分组获取
2. 同type、同period下优先id-1的mov值：可通过自连接匹配id±1，优先取id-1的记录

完整代码 #

library(data.table)

# 1. 按(id,type)分组，获取上一period的calculation值
test[, prev_calc := shift(calculation, n=1, type="lag"), by = .(id, type)]

# 2. 自连接获取同type、同period下的id-1和id+1的mov值
test <- test[
  test[, .(id, type, period, mov)], 
  on = .(type, period), 
  `:=`(mov_minus = mov[i.id == x.id - 1], mov_plus = mov[i.id == x.id + 1])
]

# 3. 优先取id-1的mov，若无则取id+1的（题目说明至少存在一个）
test[, target_mov := fifelse(!is.na(mov_minus), mov_minus, mov_plus)]

# 4. 替换符合条件的calculation值
test[samplesize == 0 & period != 1, calculation := prev_calc * target_mov]

# 5. 清理临时列
test[, c("prev_calc", "mov_minus", "mov_plus", "target_mov") := NULL]

代码说明 #

• shift函数高效获取分组内的上一行值，避免循环
• 自连接on = .(type, period)确保匹配同类型同周期的记录，通过i.id == x.id -1筛选id-1的mov值
• fifelse快速实现优先选择逻辑
• 最后清理临时列，保持数据整洁

执行后即可得到期望结果，且效率适配大样本量（277×53的数据集完全无压力）。

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