r - Data.frame 使用来自另一个 data.frame 的条件(如 Excel "criteria table")转换数据

Question

我有数据框 df1和一个数据框df2 .如何使用 df2 将 df1 变异/转换为 merged data.frame 其中:列 name将填充 df2$name 上的值如果 df1$id >= df2$start 并且 <= df2$end。

df1 = data.frame(id = 1:10, c = letters[1:10])
df2 = data.frame(name = LETTERS[1:3], start = c(2, 5, 8), end = c(4,7, 9))
merged = data.frame(id = df1$id, c = df1$c, name = c(NA, "A", "A", "A", "B", "B", "B", "C", "C", NA) )

视觉上:

> df1
   id c
1   1 a
2   2 b
3   3 c
4   4 d
5   5 e
6   6 f
7   7 g
8   8 h
9   9 i
10 10 j
> df2
  name start end
1    A     2   4
2    B     5   7
3    C     8   9
> merged
   id c name
1   1 a <NA>
2   2 b    A
3   3 c    A
4   4 d    A
5   5 e    B
6   6 f    B
7   7 g    B
8   8 h    C
9   9 i    C
10 10 j <NA>

Answer 1

我们可以使用非对等连接 data.table并在满足条件连接的情况下分配一个具有“name”的相应值的新列

library(data.table)
setDT(df1)[df2, cn := name, on = .(id > start, id <= end)]
df1
#    id c   cn
# 1:  1 a <NA>
# 2:  2 b <NA>
# 3:  3 c    A
# 4:  4 d    A
# 5:  5 e <NA>
# 6:  6 f    B
# 7:  7 g    B
# 8:  8 h <NA>
# 9:  9 i    C
#10: 10 j <NA>

或者另一个选项是 fuzzyjoin

library(fuzzyjoin)
library(dplyr)
fuzzy_left_join(df1, df2, by = c('id' = 'start', 'id' = 'end'), 
         match_fun = list(`>`, `<=`)) %>%
     select(id, c, cn = name)