如何利用DataFrame的行列组合在另一个DataFrame中高效取值？

需要用Data1中ID与Date的组合，作为行、列索引在Data2中提取对应数值。现有循环实现因数据集过大运行过慢，寻求优化方案。

示例数据 #

Data1 #

#   ID       Date
# 1  A 2022-02-01
# 2  B 2022-02-02
# 3  C 2022-02-03
# 4  D 2022-02-04
# 5  E 2022-02-05
# 6  F 2022-02-06
# 7  G 2022-02-07
# 8  H 2022-02-08

Data2 #

#   ID X2022.02.01 X2022.02.02 X2022.02.03 X2022.02.04 X2022.02.05 X2022.02.06 X2022.02.07 X2022.02.08
# 1  A           1           9          17          25          33          41          49          57
# 2  B           2          10          18          26          34          42          50          58
# 3  C           3          11          19          27          35          43          51          59
# 4  D           4          12          20          28          36          44          52          60
# 5  E           5          13          21          29          37          45          53          61
# 6  F           6          14          22          30          38          46          54          62
# 7  G           7          15          23          31          39          47          55          63
# 8  H           8          16          24          32          40          48          56          64

期望结果 #

#   ID       Date Value
# 1  A 2022-02-01     1
# 2  B 2022-02-02    10
# 3  C 2022-02-03    19
# 4  D 2022-02-04    28
# 5  E 2022-02-05    37
# 6  F 2022-02-06    46
# 7  G 2022-02-07    55
# 8  H 2022-02-08    64

现有低效循环代码 #

for (i in 1:nrow(Data1)) {
  a <- Data1[[1]][[i]]
  b <- Data1[[2]][[i]]
  c <- Data2[b, a]
  Data1$Value[i] <- c
}

数据定义代码 #

Data1 <- data.frame(ID=c("A", "B", "C", "D", "E", "F", "G", "H"), 
                    Date=c("2022-02-01", "2022-02-02", "2022-02-03", "2022-02-04",
                           "2022-02-05", "2022-02-06","2022-02-07", "2022-02-08"))
Data2 <- data.frame(ID=c("A", "B", "C", "D", "E", "F", "G", "H"), 
                    "2022-02-01"=c(1:8),
                    '2022-02-02'=c(9:16),
                    '2022-02-03'=c(17:24),
                    '2022-02-04'=c(25:32),
                    '2022-02-05'=c(33:40),
                    '2022-02-06'=c(41:48),
                    '2022-02-07'=c(49:56),
                    '2022-02-08'=c(57:64))

优化方案 #

方案1：Tidyverse 长格式转换+匹配 #

将Data2从宽格式转为ID、Date、Value的长格式，再与Data1合并，适合结构化数据处理：

library(tidyverse)

# 转换Data2为长格式，处理列名的X前缀和点号
Data2_long <- Data2 %>%
  pivot_longer(cols = -ID, 
               names_to = "Date", 
               values_to = "Value") %>%
  mutate(Date = str_replace(Date, "^X", "") %>% str_replace_all("\\.", "-"))

# 合并Data1与转换后的Data2
result <- Data1 %>%
  left_join(Data2_long, by = c("ID", "Date"))

方案2：Base R 向量化索引 #

利用向量化操作直接提取值，避免循环带来的性能损耗：

# 将Data2的行名设为ID，方便按ID索引
rownames(Data2) <- Data2$ID
# 把Data1的Date转换成Data2对应的列名格式（X+点号替换横杠）
Data1$col_name <- paste0("X", str_replace_all(Data1$Date, "-", "\\."))
# 向量化提取对应值
Data1$Value <- Data2[Data1$ID, Data1$col_name]
# 可选：删除临时生成的col_name列
Data1 <- Data1[, !names(Data1) %in% "col_name"]

方案3：Data.table 高效匹配 #

针对超大规模数据集，data.table的键连接性能优势显著：

library(data.table)

# 转换为data.table格式
setDT(Data1)
setDT(Data2)

# Data2转长格式并处理日期列名
Data2_long <- melt(Data2, id.vars = "ID", variable.name = "Date", value.name = "Value")
Data2_long[, Date := gsub("^X", "", Date)][, Date := gsub("\\.", "-", Date)]

# 设置键并执行匹配
setkey(Data1, ID, Date)
setkey(Data2_long, ID, Date)
result <- Data2_long[Data1]

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